Date Dimension: use date in YYYYMMDD format if your granular level is a single date

It still surprises me how many people set up the DateDimID as Identity(1, 1).

If you use YYYYMMDD format, then on some reports, you can even display the date without navigating to the Date dimension to show label.  In the same deal, you can use YYYYMM for a monthly date dimension, and YYYY for a yearly date dimension.

[Update 2012-10-16]
I now realize that what I said before was a clunky way to build dimension (hey I didn't say I was the expert.  :D)  And using only that argument going up against Kimball's teaching earned me a black eye.  However, here is the second argument I heard:

Using yyyyMMdd format improves ETL performance before you no longer need to join to the Date Dim staging table to get the Dim key, you just need to do YEAR(@date) *10000 + MONTH(@date) * 100 + DAY(@date)

Now that's a much better argument.  :)

Print | posted on Wednesday, March 14, 2012 11:39 AM


No comments posted yet.

Your comment:


Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski