Slowly Changing Dimension - Type II

Slowly Changing Dimension - SCD Type II

The historical data is tracked/maintained by creating multiple rows for the Natural Key in the Dimension Table by

1. Creating separate Surrogate Key
2. Creating different version number

Here instead of creating different version number we will create ,

EFF_START_DT
EFF_END_DT
CHANGED_FLAG

1 . Import the source table in Source Analyzer and target table in the Target Designer.
2. In the Mapping Designer,

1. Drag and drop the source table and the Source Qualifier is automatically created.
2. Create a lookup Transformation based on the Target table and put a condition based on the ProductID
3. Now create a Router and group the condition for Insert/Update.

• Group A INSERT : IIF(ISNULL(PDT_ID_TRG),true,false)
• Group B UPDATE: IIF(NOT ISNULL(PDT_ID_TRG) AND PRODUCT_NAME != PDT_NAME_TRG AND MOD_FLAG = 'Y',true,false)

GROUP A : It is used to insert the new records to the target table with the below values,

SURR_KEY  = SeqenceGenerator.NextVal();
EFF_START_DT = SYSDATE
EFF_END_DT = TO_DATE('30-12-2099','DD-MM-YYYY')
CHANGED_FLAG = 'Y'

Group B, There are 2 different pipelines, 

1. Add the modified row as a new row and set the values are below

SURR_KEY  = SeqenceGenerator.NextVal();
EFF_START_DT = SYSDATE
EFF_END_DT = TO_DATE('30-12-2099','DD-MM-YYYY')
CHANGED_FLAG = 'Y'

2. Update the modified row as below

EFF_END_DT = SYSDATE
CHANGED_FLAG = 'N'

4. Create Update Strategy Transformation for the respective groups from Router and finally map it to the target tables.

Mapping Designer Screen Shot

Output Data