Post reply

Best approach

  • October 14, 2010 at 2:44 pm

    #241722

    Here is my situation for Designing a Data Warehouse Star Schema

    For each transactional (Fact) data the , account(dimension , but a fact table as it may change the attributes ) has different dimensions which can be changed during the course of time (eg : Customer is moved from one location to another).

    Q1. Whats the best method to keep track of Account History ( changes from one location to another or from one status to another and so on )

    Slowly Changing Dimenions with either Type 2 or Type 6 is an option here, but what is the impliciation of creating a cube with a Main/history table or all history in one table !!

    Q2. How feasible to use this history table approach to create cubes on top of DWH

  • October 15, 2010 at 1:18 pm

    #1237828

    mpradeesh (10/14/2010)

    Here is my situation for Designing a Data Warehouse Star Schema

    For each transactional (Fact) data the , account(dimension , but a fact table as it may change the attributes ) has different dimensions which can be changed during the course of time (eg : Customer is moved from one location to another).

    Q1. Whats the best method to keep track of Account History ( changes from one location to another or from one status to another and so on )

    Slowly Changing Dimenions with either Type 2 or Type 6 is an option here, but what is the impliciation of creating a cube with a Main/history table or all history in one table !!

    Q2. How feasible to use this history table approach to create cubes on top of DWH

    If tracking changes is a business requirement my personal preference is to go with the Type 2 SCD treatment, just add an ACTIVE_FLAG as well as VALID_FROM_DATE and VALID_UNTIL_DATE columns and everything will fall into place.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 15, 2010 at 1:36 pm

    #1237842

    Thanks, but if the records are keep on updating, so I will end up in 100s of records for the same Account in the same table and its too difficult to query. Do you foreseen that I can use a History table to keep track ( Like a fact-less table) so that the master tables can be used for better performing queries. ?

  • October 15, 2010 at 1:48 pm

    #1237846

    You could potentially use a second fact table, but is that easier to query? I mean you'll have multiple rows of the same data there.

    I think having Paul's advice works, and update all queries to use that table as some type of "as of" marker for data.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 15, 2010 at 1:52 pm

    #1237850

    mpradeesh (10/15/2010)

    Thanks, but if the records are keep on updating, so I will end up in 100s of records for the same Account in the same table and its too difficult to query. Do you foreseen that I can use a History table to keep track ( Like a fact-less table) so that the master tables can be used for better performing queries. ?

    How many times would a Customer move to a different location?

    That's why the "S" in SCD stands for "Slowly" 🙂

    I really don't see queries getting more complicated.

    In my personal experience SCD Type 2 works just fine but there are another ways to track changes - feel free to choose the one that fits your circumstances the better.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 15, 2010 at 1:56 pm

    #1237857

    Thanks Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply