none
Accumulating Snapshot Fact Table RRS feed

  • Question

  • I have to create an Accumulating Snapshot Fact Table using SSIS to track the performance of bowlers on teams. The fact table consists of 4 foreign keys and 2 fact metrics. I have started the ETL for the fact table, but I am not sure how to finish it. I don't know how to get the fact metrics loaded into the fact table. 
    Wednesday, October 31, 2018 9:26 PM

All replies

  • Hi,

    That can be done in two ways:

    1. SCD type 2 in the dimension itself i.e. In insurance industry a claim status gets changed for example claim investigation---> claim review ---> claim decision --->claim payment. so we can maintain history in dimension itself
    2. Accumulating snapshot fact table are used to show the activity of progress through a well-defined business process and are most often used to research the time between milestones.  These fact tables are updated as the business process unfolds and each milestone is completed. As in image below where we have let's say business key (Claim_Key,Customer_Key,Policy_key), different dates and other fact metrics i.e. Days_to_review etc.


    In your case it is accumulating snapshot table so you can design the ETL load as below


    • Extract newly created or updated records based on if any of date (i.e. Claim_date,Investigation_date etc) gets change in the source table 
    • Load these records into the staging table
    • Calculates the metrics (Days_to_review ,Days_to_playments etc) based on dates  on Stage table data
    • Upsert (Insert/update) the transformed data into the target fact table based on the business key

    Cheers,






    • Edited by Amit-Tomar Thursday, November 1, 2018 5:09 AM
    • Proposed as answer by Yang.Z Thursday, November 1, 2018 8:25 AM
    Thursday, November 1, 2018 2:19 AM
  • Thank you for responding. I am new at creating data warehouses so I don't quite understand or know all of the terminology or methods yet. 

    So could you explain more about the staging table? Would the staging table be created in the data flow tab for the fact table ETL or on the control flow tab?

    • Edited by elle23 Thursday, November 1, 2018 9:56 PM
    Thursday, November 1, 2018 9:54 PM
  • Hi elle23, 

    Please check these links for the details about creating Accumulating Snapshot Fact Table: 

    SSIS Design pattern for Accumulating Snapshot Fact table

    How to Populate a Fact Table using SSIS (part 1)

    Data Warehouse Design Techniques – Accumulating Snapshot Fact Tables


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 6, 2018 5:48 AM