Just for best practices you should have a date dimension with a key that reflects the value of the date. That is what they say. The biggest issue I have always had is you would have to calculate it as you inserted it and all ETL would have to use the same calculation. This leads to the possibility of bad data being inserted into your key.

Example: 1/6/2012 1:34:32 pm = 201201061334

As you can see here I have cut off seconds as the minute is the lowest level I will be drilling down to.

My biggest issue has always been that you can not set date_id to be a primay key. I used a calculation on the date_id and then added a unique idex and in BIDS add date_id as a logical primary key


One thing to note if because of the way I round the seconds if I were to do a regular SQL query SELECT COUNT(*) FROM orders WHERE date BETWEEN ‘1/1/2012’ AND ‘1/2/2012’ I would count orders that areĀ rounded out by the second in my date dimension. So I would loose order less than ‘1/1/2012 12:00:50’ and greater than ‘1/2/2012 23:59:50’. they would be rounded to previous day andĀ ‘1/3/2012’