Managing Fact Table Reload for SCD Type 2


  • Hi Everyone,

    I am currently working on a data warehouse project. One of my dimension tables is loaded using the type 2 SCD. 

    Now I have a need to truncate and reload my fact table. My challenge now is how to reload it in such a way that data values before the changed attribute in my dimension will be captured as well as the new ones too.

    Please advise.



    martedì 5 giugno 2018 17:11


  • When you load the fact for getting the surrogate key pointing to Type 2 dimension you need to join based on business key as well as based on transaction date. This will ensure it picks up the correct Type 2 entry for the dimension

    For example if its say product unit price and unit price is a Type 2 attribute in product dimension then a typical product will have entry like this in dimension

    name         validfrom   validto             price
    product1     20160101   20160601             $125.00
    product1     20160602   20160815             $150.00
    product1     20160816   99991231             $160.00

    then your join will look like

    UPDATE f
    SET ProductKey = p.Key
    FROM facttable f
    JOIN productdim p
    ON p.productid = f.productid
    AND f.trandate BETWEEN p.ValidFrom and p.ValidTo

    the columns are all assumed but you will get base idea from the above

    like this wherever Type2 attributes are involved join will also include the date range to ensure you associate correct entry as per the period


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Contrassegnato come risposta lilutchay martedì 5 giugno 2018 22:44
    martedì 5 giugno 2018 17:33