OBIEE_ChangeMaxYear

How To Change the Max Year in BI Apps for OBIEE
ODI 11.1.1.7.0 with ODI BI Apps 11.1.1.8.1

Purpose : In OBIEE the available maximum date is too low
for the needed sélection in OBIEE
Solution by Eric Tremblay
Final document by Eric Tremblay eric.tremblay@data-warehouse.ca

Before the change the available date
in OBIEE is too low for our needs.

After the change the available date
will be 2040 which covers our need.

Steps Before:
Before the modification, run the selects and take note of the results.

—Change END_DATE from 2020 to 2040
—Verify Before the changes

SELECT MAX(ROW_WID) FROM W_DAY_D;

-- 20201231 Before Change

SELECT MAX(CAL_YEAR) FROM W_DAY_D;

-- 2020 Before Change

SELECT TO_CHAR(MIN(DAY_DT), 'DD-MM-YYYY') MIN_DAY_DT,
TO_CHAR(MAX(DAY_DT), 'DD-MM-YYYY') MAX_DAY_DT
FROM W_DAY_D
WHERE ROW_WID <> 0;

-- Before Change
-- "MIN_DAY_DT" "MAX_DAY_DT"
-- "01-01-2000" "31-12-2020"

-- Verify the data before and after the Update
select TARGET_TABLE_NAME, ETL_LOAD_DATE, COMMITTED from W_ETL_LOAD_DATES
WHERE TARGET_TABLE_NAME IN
('W_DAY_D'); -- Name of the Dimension Staging Table

-- Verify the data before and after the Update
select TARGET_TABLE_NAME, ETL_LOAD_DATE, COMMITTED from W_ETL_LOAD_DATES
WHERE TARGET_TABLE_NAME IN
('W_DAY_D'); -- Name of the Dimension Staging Table

SELECT 'W_DAY_D' TABLE_NAME,
TO_CHAR(MIN(DAY_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(DAY_DT), 'YYYY-MM-DD') MAX_DT
FROM W_DAY_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_WEEK_D' TABLE_NAME,
TO_CHAR(MIN(CAL_WEEK_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_WEEK_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_WEEK_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_MONTH_D' TABLE_NAME,
TO_CHAR(MIN(CAL_MONTH_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_MONTH_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_MONTH_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_QTR_D' TABLE_NAME,
TO_CHAR(MIN(CAL_QTR_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_QTR_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_QTR_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_YEAR_D' TABLE_NAME,
TO_CHAR(MIN(CAL_YEAR_START_DT), 'YYYY-MM-DD') MIN_DT,

TO_CHAR(MAX(CAL_YEAR_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_YEAR_D
WHERE ROW_WID <> 0;

-- Before Change
-- "TABLE_NAME" "MIN_DT" "MAX_DT"
-- "W_DAY_D" "2000-01-01" "2020-12-31"
-- "W_MONTH_D" "2000-01-01" "2020-12-31"
-- "W_QTR_D" "2000-01-01" "2020-12-31"
-- "W_WEEK_D" "2000-01-01" "2020-12-31"
-- "W_YEAR_D" "2000-01-01" "2020-12-31"

In BIACM - Oracle BI Applications Configuration Manager
change the END DATE to 2040.

Click to enlarge image.

For the changes to take effect, you need to execute the update below and to do an Incremental or a Full Load depending on your needs.

Incremental
In order to update the table with the new END DATE that
we changed in BIACM Oracle BI Applications Configuration Manager,
we need to ONLY reset the Staging table this will Trick ODI into
reloading the Staging data and update the values in incremental mode.

Full Load
For a Full Load, the table is Trucated automatically which we don't want to do that here.

update W_ETL_LOAD_DATES
SET COMMITTED = 0
WHERE TARGET_TABLE_NAME IN
('W_DAY_D'); -- Name of the Dimension Staging Table
commit;

When the next time you run an Incremental or Full Load, run the selects below to validate the changes.

Steps After:
Run the following select and take note of the results.
After the modifications, run the selects again
to validante the changes.

—Change END_DATE from 2020 to 2040
—Verify after the changes

SELECT MAX(ROW_WID) FROM W_DAY_D;
-- 20401231 After Change

SELECT MAX(CAL_YEAR) FROM W_DAY_D;
-- 2040 After Change

SELECT TO_CHAR(MIN(DAY_DT), 'DD-MM-YYYY') MIN_DAY_DT,
TO_CHAR(MAX(DAY_DT), 'DD-MM-YYYY') MAX_DAY_DT
FROM W_DAY_D
WHERE ROW_WID <> 0;
-- After Change
-- "MIN_DAY_DT" "MAX_DAY_DT"
-- "01-01-2000" "31-12-2040"

-- Verify the data before and after the Update
select TARGET_TABLE_NAME, ETL_LOAD_DATE, COMMITTED from W_ETL_LOAD_DATES
WHERE TARGET_TABLE_NAME IN
('W_DAY_D'); -- Name of the Dimension Staging Table

-- Verify the data before and after the Update
select TARGET_TABLE_NAME, ETL_LOAD_DATE, COMMITTED from W_ETL_LOAD_DATES
WHERE TARGET_TABLE_NAME IN
('W_DAY_D'); -- Name of the Dimension Staging Table

SELECT 'W_DAY_D' TABLE_NAME,
TO_CHAR(MIN(DAY_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(DAY_DT), 'YYYY-MM-DD') MAX_DT
FROM W_DAY_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_WEEK_D' TABLE_NAME,
TO_CHAR(MIN(CAL_WEEK_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_WEEK_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_WEEK_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_MONTH_D' TABLE_NAME,
TO_CHAR(MIN(CAL_MONTH_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_MONTH_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_MONTH_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_QTR_D' TABLE_NAME,
TO_CHAR(MIN(CAL_QTR_START_DT), 'YYYY-MM-DD') MIN_DT,
TO_CHAR(MAX(CAL_QTR_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_QTR_D
WHERE ROW_WID <> 0
UNION
SELECT 'W_YEAR_D' TABLE_NAME,
TO_CHAR(MIN(CAL_YEAR_START_DT), 'YYYY-MM-DD') MIN_DT,

TO_CHAR(MAX(CAL_YEAR_END_DT), 'YYYY-MM-DD') MAX_DT
FROM W_YEAR_D
WHERE ROW_WID <> 0;

-- After Change
-- "TABLE_NAME" "MIN_DT" "MAX_DT"
-- "W_DAY_D" "2000-01-01" "2040-12-31"
-- "W_MONTH_D" "2000-01-01" "2040-12-31"
-- "W_QTR_D" "2000-01-01" "2040-12-31"
-- "W_WEEK_D" "2000-01-01" "2040-12-31"
-- "W_YEAR_D" "2000-01-01" "2040-12-31"