DW_logo



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.

Menu_Max_2020


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

Menu_Max_2040



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.


BIACM
BIACM_2
     Click to enlarge image.





For the changes to take effect, you need to do an Incremental or a Full Load

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.

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

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


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"






FluidBlocks_Icon32
Designed with Fluid Blocks