This content has been marked as final. Show 9 replies
A common problem occurs when you add a new column to an existing table posted by an SCD.
If you define the new column as 'add row on change' in the model, every row coming in will be a new row, because the existing row will have an null for the new column value. This will double the entries in your table.
So, before the first time you run your SCD interface after adding a new column, you should define the new column as 'update on change' in the model. This is will post the incoming data to the new column but not create a new current row.
After the SCD interface runs for the first time, you can then change the new column definition in the model to 'add row on change'. Then only true changes will create a new current row.
basic SCD2 implementation of Slowly changing dimension in ODI is very good, not perfect, but running...best thinkg is, that you are able to fully modify functionality for your conventions...
I clone this IKM and create SCD3 IKM, which is based on best practise using on 3-level BI/DWH (for L1 consolidated layer).
I have actual data table on example VEHICLES. To this table I integrate by interfaces data from 3 sources...at the end I have in this dimension consolidated actual state.
Historical table (for example VEHICLES_H) I have historized (I use only add row on change pattern, because SCD1 properties I get from actual state (last actual value).
I modify SCD2 pattern - modify minimal and maximal date of validity (we use only date validity, not with time part)
On this, you only define SCD behavioral rules on target H table (identify surrogate and natural key, current flag, time validity columns), and create simple interface 1:1 from actual state table to H table.
Audit columns is modificated only on needed scenerios.
Query performance is very good (hundred thousand records in a few minutes). You can optimize IKM for partitioning support, parallel support, and I recommend rewrite some parts of IKM to better performance (such as update flag update clause with for use exists etc..)
If you need some support, I can send you modified IKM ans some simple rules for using...
The steps to implement SCD2:
1)Need to create 4 columns in target table 1)surrogate seq 2)Current Flag 3)Start date 4)end date
2)Import the source and target tables into ODI model and set the OLAP property of the table to **SLOWLY CHANGING DIMENSION**
3)Now in designer Navigator set each column property of target table.
4)Now create a new interface and drag source and target to mapping and do Auto Map.
5)The four new columns should be executed on target.For surrogate se you can use db Seq or ODI seq.Start date as sysdate and enddate as any defauly date.Hardcode flag as 0
5)Make the surrogate seq column to only insert and uncheck the update property for surrogate seq.
6)Select the appropriate LKM and IKM(Oracle SLowly changing DImension) and CKM