• Samuel Vella (7/15/2010)


    There is logic behind not using the date as the key to the date dimension... it stops developers from falling into the easy trap of using the key as the date and not bothering to join onto the date dimension table!

    True, however you do loose the ability to maintain a sequential set of date information, as its possible that 02-Jan-2011 is not the next key in sequence to 01-Jan-2011 thus it slows performance in not only the ETL process but also the reporting of the data.

    The idea behind a Key on a table is a unique reference, and time can never have more than one occurance thus it makes sense to use the date in a data warehouse as the key on both the date dimension and also on the Fact table as it cannot have more than one entry.

    You then only have to lookup to the date column to get any flags such as Public Holidays, Seasons, Financial Periods (Open, Closed, current), etc.

    One way to alieveate this is to convert the Datetime to an INT, however this has a few draw backs, the main one being due rounding when cast a date that has gone over Midday, it gets rounded up to the next day, but as long as you know the pitfalls its pretty easy to avoid.

    edit : dates wrong in the first paragraph

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices