Slowly changing dimention - Type III

Slowly changing dimention - Type III

The historical data is tracked/maintained by creating a new column and maintaining the old value in the new column.

Only the last 2 updates can be maintained,

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,true,false)

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

SURR_KEY  = SeqenceGenerator.NextVal();

Group B, It is used to update the values 

PRODUCT_NAME = SOURCE.PRODUCT_NAME
OLD_PRODUCT_NAME = TARGET.PRODUCT_NAME
UPDATED_DT = SYSDATE

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

Mapping Designer

Output Data