Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sins of SQL: The Time Table


Sins of SQL: The Time Table

Author
Message
Martin Mason
Martin Mason
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 114
I couldn't disagree with an article more. The author should read Chapter 2 of Kimball's The Data Warehouse Toolkit for a proper way to handle dates and times in a dimensional model. If I could give zero stars, I would.
ducon
ducon
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 286
Thank you! Just what I was telling myself while reading this (CTE/Scalability).

Also, when you design a warehouse for a customer that is not necessarily very versed in T-SQL, CTEs etc. telling them that you can have a dimension that describes the specifics of a date and use that as a FK can be very helpful. Seasonal activity, weekday/weekend activity etc. is much easier I think with such a concept. I try to have the best of both worlds: I keep the full datetime in my fact table and I also link it to a date dimension and to a time dimension. This way it's easy to query either way Smile
RonKyle
RonKyle
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1505 Visits: 3393
I agree with the comment that the date and time dimension need to be seperated. I've seen the combined design, and it rapidly becomes unwieldy. It also makes it harder to cross join the date and time to produce a very readable pivot of times as the days go along. I would not recommend following this method.



ducon
ducon
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 286
The advantage of this method is also that the dimensions are very small (I generate 15 years of days and time, well, by the hour has 24 rows Wink )
bob.probst+sqlservercentral
bob.probst+sqlservercentral
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 50
I'm glad I'm not the only one to balk at this article.

I tend to use the 2 dimension approach: One for date only and another for time (when needed) and never run into the unwieldy queries he's describing.

Still, it's nice to know the approach if a problem requiring that solution is ever called for.
dan_public
dan_public
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 74
Besides the many issues raised about this approach, another issue not addressed by this approach is auditing. The Time dim (the most important dimension, IMO) is dynamic. It's members are ephemeral and only created on the fly. There's nothing to audit.

Auditing is less important when using when using a simple "textbook" example like the one chosen. OTOH, real-world Time dims can be much more robust and will take time to create, audit, and maintain.

IMO, this is an example of a nice theoretical discussion of what you CAN do but probably should NOT do.

Regards,

Dan.

One star.
ducon
ducon
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 286
I agree, in theory the concept is interesting but in practice, it's well... impractical in my opinion. Simple SSIS lookup operations etc. work really well with dimensions, especially small ones.

I have read the same kind of arguments here a while ago: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/eliminate-date-dimension-surrogate-key-t334.htm

While it was an interesting read, I think these dimensions are very valuable nonetheless. I can easily ask what activity happened between hours etc. for all mondays etc. and use indexes while doing it. I know in Oracle for example you can index on a function but still...

Anyways, I think there are arguments for and against. But I think that providing both is a good thing. I can't really imagine somebody would put a date dimension and time dimension key in a fact without the datetime itself so having all of them kinda covers most scenarios I can imagine.
sjsubscribe
sjsubscribe
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 595
Yes CTEs have their role but I'm wondering if it's a replacement in an highly optimized star schema warehousing solution.

On the plus side, the date format in the CTE function can take any number of formats -- a wonderful feature for ETL applications. I use this method in almost all ETL code.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6911 Visits: 8375
For day-specific values I have for years helped clients refactor their report queries to completely eliminate the need for a time table altogether. Properly formated integers can be used to allow very fast date manipulations/filtering without the need for joining to the time dimension, leading to tremendous speed improvements.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22786 Visits: 18261
I am of the same thinking as others on this one. I think I would use the numbers table, or a calendar table in lieu of the cte approach. In my experiences, the calendar table outperformed the recursive CTE on a consistent basis.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search