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

  • February 26, 2014 at 9:33 pm

    #1692522

    Lempster (1/29/2014)

    If I want to populate a ReasonForChange column in a dimension table for Type 2 changes, is it best practice to populate that column in the new row (the one with an EndDate of 9999-12-31 and IsCurrent = 'Y') or the old row (with EndDate = the date the attribute changed and IsCurrent = 'N')?

    Or both perhaps?

    I'm fine with writing the code to identify the ReasonForChange, just not sure where to write the value.

    Thanks

    Lempster

    You don't need the pain of maintaining an "IsCurrent" column for Type 2 SCDs. If you have both a start and end date per TYPE 2 SCDs, the dates are good enough to tell you what is current especially since you were smart enough to NOT use a NULL end date. I'll also recommend that you don't actually use 9999-12-31 as an end date. Instead, use 9999-12-30 or even just '9999' (which will auto-magically convert to 9999-01-01) so you have at least 1 day of "headroom" for certain range calculations.

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

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