ODI Trick for Fact
In this case we changed the logic of an attribute and we want this change to be reflected in the Fact. In order to reload everything without doing a Full load of all the data warehouse, we trick ODI into only reloading this Fact.
Since this is a Fact, the keys will change but it has no importance since we are at the end of the data-warehouse process.
What we need to do is SET COMMITTED = 0 on the Fact we wish to refresh.
In this case we are going to SET COMMITTED = 0 on both the _FS and the _F of our Fact.
Then we run ODI an incremental load this Fact and only this Fact will load in FULL Initial Load Mode.
PL/SQL Source Code
-- Verify the data before and after the Update
select * from W_ETL_LOAD_DATES
WHERE TARGET_TABLE_NAME IN (
’W_ACTIVITY_FS'
,'W_ACTIVITY_F');
-- We want to Reload both
-- the Staging and the Fact Table
-- Replace W_ACTIVITY with the
-- Name of your Fact
UPDATE W_ETL_LOAD_DATES
SET COMMITTED = 0
WHERE TARGET_TABLE_NAME IN
('W_ACTIVITY_FS','W_ACTIVITY_F’);
ODI Trick for Dimension
The same trick can be done with a Dimension except in the case of a dimension we do not want to lose our keys since these Keys are also found and used in our Facts.
We need to do two updates to achieve this.
PL/SQL Source Code
-- 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_XACT_TYPE_DS'); -- Name of the Dimension Staging Table
-- We need to ONLY reset the Staging table
-- This will Trick ODI into reloading the Staging data in incremental mode
-- In a Full Load the table is Trucated automatically which we don't want to do here.
--
update W_ETL_LOAD_DATES
SET COMMITTED = 0
WHERE TARGET_TABLE_NAME IN
('W_XACT_TYPE_DS'); -- Name of the Dimension Staging Table
commit;
-- Verify the data before and after the Update
select XACT_TYPE_CODE, CHANGED_ON_DT from W_XACT_TYPE_D;
-- This is only done on the Dimension
-- Changing the date will trick ODI
-- into updating only this Dimension.
-- If the dates stay the same ODI will do nothing...
-- Changing the date, tricks ODI into doing a refresh.
-- Preserving our Keys for our Fact.
--
update
W_XACT_TYPE_D -- Name of the Dimension we want to Update
set CHANGED_ON_DT = (select sysdate from dual);
commit;
;)
;)
