Natural date key

I was looking at the date dimension spreadsheet in the DWT resources, and have a question regarding it's date key column. Is there an advantage to using an integer versus using date?

In my case, I decided to use hourly granularity for dates and was considering using date/time type. Is using an integer in form YYYYMMDDHH better than a date/time type?

Thanks!

If time is required, then usually it would have its own dimension, resulting in both DIM_Date and DIM_Time.

The surrogae dimensional key is typically an integer. There are numerous debates on whether it should be a smart/meanignful key or not.

You do not use a date data type to hold the natural key for a date dimension in order to allow for invalid or unknown dates. Using a non-date type (number or string) allows you to accomodate bad dates.

Thank you once again for a very clear and concise answer.

So, with bad dates in mind, it would be better to just assign an identity rather than a smart key? I would imagine that if I gave them a smart key, some developers won't be able to resist using it as a date. It would be safer to answer question about the sanity of the design than making sure they don't do that. Is that the controversy?

Yes - that is the debate.

I've always tended to avoid smart-keys. I want consistancy for all my dimensions - if you want an attribute, you get it from the dimension, not some derivation of the key.

Also, As ngalemmo mentioned, having a true surrogate key allows you to easily acommodate missing/invalid dates.

Additionally, i've found that always using the date dimension for attributes allows you to change things like display formats in one place, rather that in each report/query.

Thank you for that explanation, LAndrews.

As for what you said about the Time dimension, I've given that some additional thought and briefly consulted with our analysts. One attribute of the Date dimension is giving me pause on separating it from the Time. It's the IsWeekend flag. Our weekend starts on Friday night and if I separated date and time, I would need to put IsWeekend into the fact table as another degenerate dimension along with IsPeak. At least, that's what it looks like to me at this point. It's all lines on paper so far, so I'm willing to be convinced that I'm committing dimensional heresy.

I would create a second dimension, date_hour, to cover those attributes. Facts that are time dependent would reference both dimensions. If you try to do it in a single table you would run into issues when trying to create date level aggregates or dealing with facts that are not time dependent.

Would it make sense to have a Date Time dimension as a snowflake to the Date Dimension and the Time dimension?