SCD Type-1

SCD Type 1: In SCD 1, new information overwrites the current information

There will be no history in SCD 1 dimension.

It is used when it is not necessary for DWH to keep track of historical changes.

It’s works like “Update Else Insert”

Below is a sample source table "SRC_SCD1" and SCD1 Target Dimension "TGT_SCD1"

 

 Now let’s create mapping “m_SCD1” and import source & target definition in mapping.

Let’s lookup on SCD1 dimension table for this we will create lookup transformation and keep required ports which is (SURRKEY_01, EMPNO, CURR_SAL) here EMPNO port used to compare between source and lookup table and CURR_SAL for value comparison based on this ROUTER takes decision to send for INSERT or UPDATE.

Now will create ROUERT T/R with two group one for INSERT and another one for UPDATE.

For INSERT condition will be ISNULL (SURRKEY_01)

For UPDATE condition will be NOT  ISNULL (SURRKEY_01) AND (SAL != CURR_SAL)

Next will create Update Strategy T/R for update will use DD_UPDATE in Update Strategy expression, here will take necessary port which we want’s to update

Now will look for INSERT instance where we create Sequence Generator T/R for generating Surrogate Key in dimension table and drag and drop all ports from Router INSERT group to Target Insert instance.

Now will create session where we specify all source, target and lookup db. connections and in session properties will specify Treat Source Rows as Data Driven as we are using Update Strategy T/R.

Below is created SCD Type-1 Mapping

Will Run Session, below is target data after first Run.

Now let’s update and insert data into source

Again will re-run session and verify target to see data got updated with latest value.

 
With this i'm done with SCD type-1, please let me know if you have any question and provide your comment below to help me for improving things in future.
I will be explaining SCD Type-2 in Next coming Tutorial.