Slowly Changing Dimension-3

SCD-3 (Slowly Changing Dimension)

It is used to store partial history data into dimension table, which oldest and latest data.

Whenever any changes happen in any existing records in source updated data will be updated in update column in SCD-3 dimension in data warehouse.

Here I’m taking example of sample employee table (SRC_EMP_SCD3) and loading into target dimension (TGT_EMP_SCD3).

After creating source and target table into database, then import source and target definition into mapping (m_EMP_SCD3).

Let us create Lookup transformation on target dimension table and keep only required ports which is (EMPNO, SurrKey_001, Curr_sal) in my case.

Now let’s Lookup on Curr_sal port which will use to compare with Source sal port in router transformation, in lookup will write condition to compare the data with source which is SRC_EMPNO=TGT_EMPNO.

Now will create router transformation to send data for update and insert in different pipeline.

For INSERT: ISNULL (SURRKEY_001)

For update: NOT ISNULL (SURRKEY_001) AND (SAL!= CURR_SAL)

Now will create update strategy transformation for update, and will drag required column into update strategy transformation which is SurrKey_001 and Sal column received from source.

Here we will write DD_UPDATE into update strategy expression, because we are sending this for update and drag these column to another target instance.

For Insert we need to create sequence transformation for unique key generation will map this NEXTVAL column to target SurrKey_001 column and rest column from router from Insert group.

Below is developed mapping for same.

Target after first run

In Source one record getting inserted and one record updated, now let's run again and see difference in target.

After second run target look like below one record updated and one record inserted.

With this i'm done with SCD-3 implementation.

Hope you enjoyed by reading this, please write your comment below