Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Sins of SQL: The Time Table Expand / Collapse
Author
Message
Posted Tuesday, June 1, 2010 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:47 PM
Points: 14, Visits: 110
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.
Post #930591
Posted Tuesday, June 1, 2010 5:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:19 PM
Points: 122, Visits: 273
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 :)
Post #930621
Posted Tuesday, June 1, 2010 5:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:47 PM
Points: 814, Visits: 2,008
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.


Post #930627
Posted Tuesday, June 1, 2010 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:19 PM
Points: 122, Visits: 273
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 ;) )
Post #930662
Posted Tuesday, June 1, 2010 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 11:51 AM
Points: 7, Visits: 49
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.
Post #930692
Posted Tuesday, June 1, 2010 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 4:38 PM
Points: 9, Visits: 71
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.
Post #930695
Posted Tuesday, June 1, 2010 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:19 PM
Points: 122, Visits: 273
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.
Post #930723
Posted Tuesday, June 1, 2010 8:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
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.
Post #930752
Posted Tuesday, June 1, 2010 9:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 4,343, Visits: 6,148
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
Post #930798
Posted Tuesday, June 1, 2010 9:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #930822
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse