Skip to Content

Here we will learn how to implement Slowly Changing Dimension of Type 3 using SAP Data Services. SCD type 3 design is used to store partial history. Here we are only interested to maintain the “current value” and “previous value” of an attribute. That is, even though the value of that attribute may change numerous times, at any time we are only concerned about its current and previous values.

Let us consider we have customer source data with Address details. We are interested to capture only the current and previous address information for each customer. Find below the implementation Data Flow.

SCD Type3 – Data Flow

1.JPG

Lets assume we are using an ETL batch load control table to log the last extraction date for customer records from source. Next we assign the last extraction date to a global variable to capture the Changed Data from the source.

2.JPG

Next we lookup the target dimension table using the source customer key and get the surrogate key,current address from the dimension table as return values from function call lookup_ext.

3.JPG

Next we check if the incoming source record exists in the dimension table or not. If the record does not exist in the dimension table we flag it for INSERT. If the record exist in the dimension table but the Present address is not same as the current address coming from source system we flag it for UPDATE. Else we mark the record as REJECT.

4.JPG

Next for the records marked for Insertion i.e WHERE REC_TYPE=’I’, we map the PRESENT_ADDRESS to the incoming ADDRESS from the source and make the PREVIOUS_ADDRESS as NULL.

5.JPG

Next we use a Key_Generation transform to generate the surrogate key for the incoming new records from the source and insert into the target Dimension table.

6.JPG

Now for the UPDATE path Query transform for those records marked for Update i.e WHERE REC_TYPE=’U’, we map the PRESENT_ADDRESS to the incoming ADDRESS from the source. Also we map the PREVIOUS_ADDRESS to the PRESENT_ADDRESS coming from the lookup dimension table.

7.JPG

Next we use a Map_Operation transform to changed the OPCODE for the incoming NORMAL records to type UPDATE and map it to the target dimension table. 

8.JPG

This is how our target dimension table data for SCD Type 3 implementation looks like.

9.JPG

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Ravi Kashyap

    Hi

    We can implement SCD 3 concept by comparing the source and target table and store the previous value and the new value if there is any change in the data of the comparing field..

    This comparison is done at column level… which is easy to implement using the query..

    (0) 

Leave a Reply