Home > SSIS > Slowly Changing Dimension (SCD) Transformation

Slowly Changing Dimension (SCD) Transformation


Dear SSIS folks,

What is SCD?
Dimension table which changes slowly in Data management and Data warehouse. In other terms, Slowly Changing Dimension is a concept to keep the changes (History) data for the Dimension (master) tables.
Concept:
In DWH scenario there are different types of SCD available based on a need of the business and various facts.  Different types of SCDs are Type 0,1,2,3,4,…
How to implement SCD in SSIS?
We can implement type 1 and 2 using SCD transformation with SSIS.
My Demo
Scenario:
In my business, I have to maintain the customer details for supporting decision making people. In my case my boss told me to keep track  changes in the customer telephone numbers. Now, my task is to implement it using SSIS.
Assumption:
I have a source table, which is a OLTP source. I have to move that data to a Dimension table in the Data warehouse.
Solution:
In this case, we have to use SCD type 2. Because, new telephone number of the customers must be shown in report and have to maintain the old numbers for the history.
  • Structure of the Source table

  • Sample data of the source table
  • Structure of the destination dimension table

Note: Destination table is empty now for the fresh upload
Steps to implement SCD type 2 in SSIS:
Step 1: Open an Integration Services project in BIDS
Step 2: Drag and drop a Data flow task to control flow and move on to data flow layout
Step 3: Open a connection with OLE DB source task and configure with the source table
Step 4: Place a slowly changing dimension task in to data flow environment and configure as given below
·        Connect OLE DB Source to SCD task and Double click on the SCD task to open the wizard.
·         Now, it will welcome you to the SCD wizard with message dialogue. Kindly read it. Click next
·         In the 2nd step,  we have to map source fields with DimCustomer  fields and set the business key as shown in the below picture
·         In the 3rd step, we have to set the historic field. In our case, mobile number is the changing field. Click next
·         In the 4th step, we have to set the change tracking field or attribute. I have used Boolean field to track the active mobile number. If the active field value is 1 then it is the latest mobile number else old/inactive.  In other ways, we can also track the date on which the number is updated. Click next
·         In the 5th step, uncheck the option as mentioned in the below picture. Click next
·         In the 6th step, click finish and execute the package
Package execution
Data in the DimCustomer is shown in the picture
A task for readers:
Update a mobile number of a customer in the source table and execute this package again. Now, look in to Dimcustomer table values.
Thanks for reading this article. Please comment this article with your questions or opinions.
Thanks,
Ayyappan
Advertisements
Categories: SSIS Tags: ,
  1. May 15, 2011 at 6:06 pm

    >Nice one, Thanks for sharing!

  2. May 15, 2011 at 8:22 pm

    >Thank you for your comment Mega.

  3. September 30, 2011 at 10:56 am

    Whoa, Very nice explanation 🙂
    Thanks a ton.

  4. tawargeri
    October 2, 2011 at 12:45 am

    Nice Article,
    Do you have any article which talks about handling Startdate,Enddate in SCD type 2
    Is this the best way of Handling SCD’d
    Please mail me at tawargerip@hotmail.com

    Regards,
    tawargeri

  5. sreeram
    May 24, 2012 at 6:44 pm

    nice article ayyapan..if u have any other similar plz..do mail me
    iammalliksreeram@gmail.com

    thanx
    sreeram

  6. Akiko S
    March 29, 2013 at 5:25 pm

    I am just now learning about SSIS and found this to be a good example of SCD. Thank you for sharing!

  7. March 29, 2013 at 10:45 pm

    It is my pleasure. Keeping coming and share your valuable comment

  8. Mallikarjun
    July 11, 2013 at 8:19 am

    Ayyappan I never Find these much clear Explantion ,simply Am a Fan of you.

  9. Sreedhar
    September 16, 2013 at 7:57 am

    very nice article. Thanks Ayyappan for sharing this useful info.

  10. JQ
    February 9, 2014 at 5:13 pm

    How about a SCD Type 4? We are currently using historical tables to log the changes. Can this SSIS tool handle that?

    • February 9, 2014 at 10:26 pm

      SSIS scd task does not have that option. But we can use the task to design SCD type 2 and move the expired records with date to historical table.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: