Continuing on my SCD series in ODI, I am implementing SCD Type 2 today. As compared to SCD Type 1, implementing Type 2 was much simpler. There are few things that one has to take care of. Apart from that, it was a walk in the park.
First things first, we need to setup the underlying table to support SCD Type 2 modifications. I’ll update the same SAMP_OFFICES_D table that I mentioned in my previous post. So, I am adding three columns to the table – START_DATE, END_DATE and ACTIVE_FLAG.
For SCD Type 2, we need to use IKM Slowly Changing Dimension and use database sequences for the surrogate key, just like SCD 1. Following the similar steps of adding the data stores and then reverse engineering the model, we move ahead to the important bit.
In the Definition tab of your data store, select Slowly Changing Dimension.
Figure 1: Setting the type of the data store to Slowly Changing Dimension
Define SCD behaviour for every column
Once we set the data store to Slowly Changing Dimension, we then need to set the SCD type of every column. (NOTE: every column).
Figure 2: Setting the SCD behaviour of all table columns
Setting the values
Set the following values in the target data store.
Figure 3: Target Data store mappings
The above mentioned steps are not that detailed but are the most important ones. Good luck SCDing.
Side Note
We can check the database table SNP_COL to check the SCD behaviour of all the columns (refer Figure 2). As you can see below, I have intentionally left COMPANY_KEY undefined.
Figure 4: Table in the work repository where we can check the SCD behaviour settings of individual columns
When we run the interface, we will get the following error as we have not defined the SCD behaviour for COMPANY_KEY. So, for all the 20 rows that the interface tries to insert into the dimension table, we get 20 errors as shown below in the error table E$_SAMP_OFFICES_D.
Figure 5: Not defining the SCD behaviour for any column will give an error at runtime
My Tech Accounts 