Capturing the Reason For Change for Type 2 changes

  • EricEyster (3/3/2014)


    ... but the Kimball design assumes your users are getting data from the relational engine.

    I disagree with that statement. Sure, the relational data warehouse containing the dimension and fact tables is what the multidimensonal Measures and Dimensions (i.e. Cubes)are built on, but users query the data stored in Cubes directly from SSAS whether that be via Excel (pivot tables), PowerView/PowerPivot, MDX queries, SSRS or third party products.

  • EricEyster (3/3/2014)

    --------------------------------------------------------------------------------

    ... but the Kimball design assumes your users are getting data from the relational engine.

    I disagree with that statement. Sure, the relational data warehouse containing the dimension and fact tables is what the multidimensonal Measures and Dimensions (i.e. Cubes)are built on, but users query the data stored in Cubes directly from SSAS whether that be via Excel (pivot tables), PowerView/PowerPivot, MDX queries, SSRS or third party products.

    I agree with the disagreement. The statement is misleading because the USERS aren't getting the data from the relational engine. SSAS is getting data from the relational engine when it processes. The users then get it from SSAS through some front end.

  • Whatever you folks do, don't forget to set the correct attribute relationship types (rigid for type 2 dimension columns and flexible for type 1 and type 3 columns). 😀

  • 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

    My incliniation is that it doesn't matter where the inserts are coming from. A leading or singular column in an index with such low selectivity is going to cause massive extent splitting that will slow any process down.

    Although I'm also inclined to go with what experts say, it does cause me concern when the best practices of one expert or group of experts is contrary to the best practices of another. Testing would be a good thing here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

    For some reason, I am drawing a massive blank about why this would happen (no coffee) . Is this because it is a non clustered index on a low cardinality column?

  • @ronkyle,

    For the standard western collations, NULL appears at the top of the sort values so when you end date the record it will change it's place in the index with the consequential overhead of re-paging and page splits/sparsely populated index pages. If you put a default date of the end of all time, when you update it, its place in the index is not changed

  • @Aaron.reese

    I will have to reconsider how I do that. Thanks for the information.

Viewing 7 posts - 16 through 22 (of 22 total)

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