Home Forums Data Warehousing Strategies and Ideas Capturing the Reason For Change for Type 2 changes RE: Capturing the Reason For Change for Type 2 changes

  • March 3, 2014 at 6:32 am

    #1693535

    Lempster (3/3/2014)

    Jeff Moden (2/28/2014)

    Put an index on the "Current-Flag" and watch your GUI's timeout when they try to do an INSERT because of the massive extent splits that will occur. 😉 I recommend just doing the dates correctly.

    I'm talking about a Data Warehouse so there aren't going to be any GUIs to timeout, certainly not any doing inserts. There will of course be inserts on a daily basis due to the ETL process. The relational tables in the Data Warehouse will have multidimensional cubes built on them and it will be the cubes that are queried by end users, not the relational tables directly.

    I will of course undertake extensive testing, but at this point I'm inclined to follow Kimball best practice.

    Regards

    Lempster

    Things change a little if you are going to use SSAS. The DW becomes little more than a data store to facilitate the ETL processes. Sure, you need enough to also support your debugging when things go bump in the night, but the Kimball design assumes your users are getting data from the relational engine.

    If you want to display the isCurrent flag for testing or for ease of loading to SSAS, create a view to calculate the isCurrent flag using a case statement on the endDate.