Sunday, April 7, 2013

SSIS : Method 1- Using two lookups instead of SCD (Slowly Changing Dimension )

  
Method 1: Using two lookups instead of SCD (Slowly Changing Dimension )

Here the OLEDdb Source is ‘tmpdimDest’ table.
·         First lookup points to ‘dimDest’ table and compares the business key in ‘dimDest’ table with matching column in the ‘tmpdimDest’.
·         For ‘Lookup no match output’- means, the rows in ‘tmpdimDest’(source) table for which business key doesn’t match with ‘dimDest’ table, that means those rows are new in ‘tmpdimDest’ table , so insert operation is performed in ‘dimDest’, which I have confirgured in OleDB destination.
·         For ‘Lookup Match Output’ – means, the rows for which Business key matches in both table ‘dimDest’ and ‘tmpdimDest’ .But it’s possible that some modification or updation has been made in the information of some Business keys. So those rows should be updated or modified in ‘dimDest table.
·         Second look up again points to ‘dimDest’ table and compares all the columns in ‘dimDest’ with all the columns in ‘tmpdimDest’ table as shown below

For ‘Lookup No Match output’ the rows are updated by mapping the business key column in the OLeDB command as shown below
Update dimDest
dd.coulmn1=tdd.Column1
dd.column2=tdd.Column2
From dimDest dd , tmpdimDest tdd
Where dd.Busineskey=tdd.BusinessKey

This method is one of the alternatives of SCD.
For more information on SCD
For implementation of SCD type1 step by step
For 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.

6 comments:

  1. Hi Ragini

    I have a doubt regarding two look ups. It can be done with one lookup. All Matching rows goes to Update Command and Not matching rows goes to Insert Command.

    I am not sure why you need 2nd Lookup?

    ReplyDelete
    Replies
    1. Hi sanjay , yes it can be done through one lookup if you dont want to compare other columns data, but some times u might want some of the columns value should not be updated or modified , in such case second lookup is used to compare the columns excluding business key.

      Delete
  2. Hi Ragini

    I think it's work for only incremental load not for inital load.

    Thanks,
    Shreyans

    ReplyDelete
  3. Hi Ragini,

    i appreciate your efforts, one doubt i have is will this help to reach SCD 2 functionality


    Thanks,
    Avinash

    ReplyDelete
  4. I think you will not need a lookup transformation if you use SSIS Upsert. Although this is a third-party product but I still think it will be useful.

    SSIS Upsert

    ReplyDelete