data update in fact and insert in dimension table - Late arriving dimension


  • Dimension Table
    Customer_Id Customer_ID_SK Customer_name
    1 -1 #Unknown
    2 1 Ravi
    3 2 Kannan
    4 3 Henning
    Fact Table
    Sales_Id Customer_ID_SK Sales Amount
    1 1 1000
    2 2 1500
    3 3 1750
    4 -1 1900
    5 -1 2000
    Customer_ID_SK is be the primary Key, refer as FK for fact table
    I want to update the Dimension Table and the Curresponding fact data once I get the Dimension Datas as below
    Customer_Id Customer_ID_SK Customer_name
    5 4 Selvi
    6 5 Rani
    Sales_Id Customer_ID_SK Sales Amount
    4 4 1900
    5 5 2000
    Kindly suggest me the steps to do the same


    Wednesday, February 13, 2013 10:55 AM

All replies

  • Based on the reference of sales ID you will come to know the dummy customer ID you updated right? So you can update the Dimension with the original customer details
    Wednesday, February 13, 2013 12:22 PM
  • I dont see any hassle updating Dimension table, you could use SDC or Merge method to update.

    For, fact table, just a thought
    SELECT both the new customers, adding ROW_NUMBER to the query. So you will have 1 & 2 Row Numbers
    SELECT fact data where Customer_ID_SK = -1. Add Row_Number here in this statement too.
    Use Merge to join using RowNumbers and Update fact data.

    Hope this is helpful for the above context.

    Wednesday, February 13, 2013 1:10 PM
  • The Above is sample data for my query... I need to know the standard format used where in i can fix this for over any scenario


    Wednesday, February 13, 2013 1:22 PM
  • This link explains how to load late arriving dimensions

    Wednesday, February 13, 2013 4:13 PM
  • This link explains how to load late arriving dimensions

    Hi Varsha, 

    Looks fine..

    Could you let me know more on the SCD 0, Point 3 

    3.  Fact Cleanup:  All facts with failed lookups should be reloaded.  If the foreign key is not part of the fact table primary key such as an accumulated snapshot fact table, then the facts can be reloaded without first deleting/disabling the current record.  Otherwise, the current records should be deleted/disabled prior to reloading.  Often this step involves marking the records as not processed in a consolidation area such as PSA. 



    Thursday, February 14, 2013 5:05 AM