Chiming in with my agreement with other BI/Warehouse guys, this is a 10 year old practice with time dimensions. It makes perfect sense in almost any system where rows are generated at a specific interval of time.

As far as the type of key, I remember one instance were an int IDENTITY(1,1) key was the appropriate choice because our time dimension was at the hourly granularity, and we had to take into consideration DST. We had an extra hour in the fall with the exact same time stamp. In this regard, the auto-incrementing ID kept the two separated in the fact table as two unique time periods even though logically they were the exact same point in time. We could have solved this using a datetime field by using UTC, but since our system didn't care what the UTC was, we left the integer field in as ID.

At that point if we wanted to constrain by time period, we would just put a join on D_Time, like I find happens in most queries anyway in a warehouse (between this month and that). So far warehouses I've worked with have not had a lot to gain out of using the datetime as a primary column, usually because I'm already joining on D_Time for a separate constraint or to get a time reference for the time I'm working with (ex: end of previous month when doing comparisons between today and previous month end).

Another thing to consider about using datetime as key and as constraint is if you put any converts or dateparts on your datetime field in the where or join clause, as far as I know and have tested, SQL will not use the index associated with that field. So it is better for performance if you join on an appropriate field in the time dimension without changing the raw data in the field and also then putting an index on that column if it is used in many queries.

From my experience, unless you're working with a very large chunk of time (greater than 5 years) and have a very tiny time granularity (less than an hour), a join on a time table really doesn't affect performance significantly, again assuming the appropriate indexes have been created.

If anyone thinks to the contrary, please post. I've never learned so much I think I have nothing left to learn. =D

Correction: It will do an index scan rather than index seek, because it will convert every time using the function you've given it before it uses the index.