how to implement DWH audit columns in IKM Oracle SCD, deleted_flag
765170May 6 2010 — edited May 26 2010Hello, we are using ODI to implement DataWareHouse project. I need to populate dimensional table using SCD2, but need to carry right values in standard DWH audit columns
I have slowly changing columns defined on the table
surrogate key
natural key
overwrite on change (SCD1)
add row on change (SCD2)
current record flag
starting ts
ending ts
"undefined" is used for standard DWH audit columns
I customized build-in IKM oracle slowly changing dimensions module to meet our requirements:
1) fixed SCD values of current flag and future date are now otions
current flag as new option (Y,N not 1,0)
future date as new option (1.1.3000 not 1.1.2400)
2) population of standard DWH audit columns- they ARE marked as "undefined" SCD behavior in models section
INSERTED_DATE
UPDATED_DATE
INSERTED_ETL_ID
UPDATED_ETL_ID
DELETED_FLAG
then by using UD checkboxes (UD1=insert, UD2=update) in interface I populate these columns depending on action (I modified KM steps)
What I need is to find how to manipulate DELETED_FLAG DWH audit column when populating dimension. I think a new step in KM is needed....
DELETED flag means that the row was deleted on source, it is not comming now, so we need to mark all rows (matching natural_key) in dimensional table as DELETED_FLAG = Y
Has anybody developed such KM step?
Edited by: kubis on 6.5.2010 1:50