Sins of SQL: The Time Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Steve Gray » Sins of SQL: The Time Table

84 posts, Page 2 of 9

««1 2 3 4 5 »»»

Sins of SQL: The Time Table

Rate Topic

Display Mode

Topic Options

Author

Message

Martin Mason

Posted Tuesday, June 01, 2010 3:32 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:07 AM
Points: 13, Visits: 96

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

ducon

Posted Tuesday, June 01, 2010 5:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 9:24 AM
Points: 118, Visits: 253

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

RonKyle

Posted Tuesday, June 01, 2010 5:31 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 671, Visits: 1,505

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

ducon

Posted Tuesday, June 01, 2010 6:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 9:24 AM
Points: 118, Visits: 253

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

bob.probst+sqlservercentral

Posted Tuesday, June 01, 2010 7:55 AM

Forum 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

dan_public

Posted Tuesday, June 01, 2010 7:59 AM

Forum 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

ducon

Posted Tuesday, June 01, 2010 8:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 9:24 AM
Points: 118, Visits: 253

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

sjsubscribe

Posted Tuesday, June 01, 2010 8:56 AM

SSC 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

TheSQLGuru

Posted Tuesday, June 01, 2010 9:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 3,575, Visits: 5,115

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

SQLRNNR

Posted Tuesday, June 01, 2010 9:56 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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 »

84 posts, Page 2 of 9

««1 2 3 4 5 »»»

Permissions