Sunday, April 7, 2013

SSIS : Alternatives of Slowly Changing Dimension - SCD

SSIS – SCD (Slowly Changing Dimension ) - OleDb Command
Using: SQL Server 2008, BIDS 2008 (Business Intelligence Development studio)
I was working on a scenario, which was as below:
  • There was a dimension table say ‘dimDest’ with around 600 columns, which gets populated every hour from the source through a scheduled job.
  • The job gets all updated data from the source, and stores the data in the stage table say ‘tmpdimDest’
  •  Job loads ‘dimDest’ table from ‘tmpdimDest’ table through SCD (Slowly changing dimension) type 1.
  •  For updating the table, I used OleDBCommnad transformation, for insertion it was directly inserting the row into OleDB destination, on the basis of the availability of Business Key in the destination table
The below is the snapshot of the SCD method I

As we all know , that SCD is very slow , when it comes to update the existing records, so when I executed the DFT for loading ‘tmpdimDest’, It was taking lot of time to do the update and insert operation. As update operation was involving comparison of all 600 columns and updating all the 600 columns.
The question comes here, is why the SCD or OleDB Command becomes slow while update operation?
And the Answer is “Because OleDB command performs operation (like Insert, update delete) for each row, so when there is huge number of records then it takes long time to perform the operations”
Question: So what are the alternatives of the combination SCD and OleDb Command operation?
Answer: There are multiple alternatives; some of them are as below:
3.       Using Execute SQL Task  and look up task again instead of SCD (Slowly Changing Dimension)
4.    For more information on SCD
5.   implementation of SCD type1 step by step
6.   implementation of SCD type2 step by step

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

4 comments:

  1. very good article.
    thanks

    ReplyDelete
  2. Very excellent post, which alternative gives better performance.....

    ReplyDelete
  3. Merge is one of the alternative solution for SCD .... and merge statement is faster than SCD

    ReplyDelete