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 «««56789»»»

Sins of SQL: The Time Table Expand / Collapse
Author
Message
Posted Thursday, June 3, 2010 1:12 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
deleted -- multiple posted due to blackberry issues



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932361
Posted Thursday, June 3, 2010 1:12 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
deleted -- multiple posted due to blackberry issues



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932362
Posted Thursday, June 3, 2010 1:12 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
deleted -- multiple posted due to blackberry issues



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932363
Posted Thursday, June 3, 2010 1:13 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
deleted -- multiple posted due to blackberry issues



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932364
Posted Thursday, June 3, 2010 1:21 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
All, please ignore the multiple posts above by me. Issues posting from my BlackBerry.

Steve, if you could delete the dups, that would be nice.

Lynn



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932367
Posted Thursday, June 3, 2010 1:30 PM
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
Lynn,

OK. Now I'm confused too.

Dan.
Post #932376
Posted Thursday, June 3, 2010 5:03 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
dan_public (6/3/2010)
Lynn,

OK. Now I'm confused too.

Dan.


That's okay, I just went through this thread and I can't find anywhere where I critisized any of your responses. I have a feeling you have confused me with someone else in this thread unless you can find the post where I did this.

Lynn



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #932484
Posted Thursday, June 3, 2010 5:28 PM
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
Lynn Pettis (6/3/2010)
dan_public (6/3/2010)
Lynn,

OK. Now I'm confused too.

Dan.


That's okay, I just went through this thread and I can't find anywhere where I critisized any of your responses. I have a feeling you have confused me with someone else in this thread unless you can find the post where I did this.

Lynn

Lynn,

My apologies. I got you confused with YSL Guru. The only thing I can think of is that I'm not used to the format of this forum software and simply looked in the wrong place.

Again, my apologies.

Dan.
Post #932492
Posted Thursday, June 3, 2010 5:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:52 AM
Points: 14, Visits: 111
I'd like to apologize to the author because I'll confess, I read the title and the first paragraph and was immediately enraged. This approach should never replace the Date and Time dimension tables in a data warehouse as the title of the article suggests. However, it could be used to build the date and time dimension tables (Though, I believe approaches using SSIS to be vastly superior and much, much more flexible.) This pure SQL approach is kinda cool though.

Enough of the compliments because I still believe this article is garbage. First, stored date AND time dimensions are critical to a data warehouse projects success. If you don't need to account for holidays, workdays, special other time periods, multiple date/time hierarchies etc now, you will later and without an architecture that's able to adapt to changing business and reporting requirements efficiently and effectively, you'll fail. Word of advice. Every issue about dimensional modeling described in The Data Warehouse Toolkit, follow it. Though it doesn't touch on everything you need to know, the foundation of everything is there.

Second, about SSAS on which this article seemed to be focused on, I can't stress enough that the Date and Time dimensions should never, ever, ever, ever be combined in a single dimension in a multidimensional database. More later on why not if someone doesn't beat me to the issue. Gotta go.
Post #932497
Posted Thursday, June 3, 2010 8:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:52 AM
Points: 14, Visits: 111
To continue on my previous post,

Second, point reasoning. You ideally want your multidimensional structures to be dense as possible. Every combination of every member of every attribute hierarchy to be referenced somewhere in your partition stores. That's not going to happen obviously as for a retail system, not every customer buys every product on every day (and in Steve's Case, every second). But to combine date and time just increases the sparcity of the multidimensional database along a dimension that is most likely going to be used to partition a database of any size whatsoever. It would just be a huge mistake to do so. Like said earlier, separate date and time dimensions for 10 years worth of data at the minute resolution results in a dimension with over 5 million (where'd I get 13M from?) stored members while separate dimensions results in a date dimension with 3650 members and a time dimension with 1440 members. I don't need benchmarks to tell me the separate dimension approach will best the consolidated dimension approach in every aspect; dimension processing, aggregation processing, ease of maintenance. Everything.

Thirdly, say you have a referenced date in your fact table but no foreign key to your date and time dimensions. You could use Steve's approach to reference a virtual TIME (and DATE) dimension using recursive CTEs to force into SSAS. But wait. If I convert a datetime type to an int [CAST(FactDate AS int)], excluding the time portion (assumes its AM), gives me the number of days elapsed since 1/1/1900. Hey, why not use that as the surrogate key of your date dimension! (Which is exactly what I and others do) I don't even have to do a look up for a date key at all in my ETL and if I haven't already, I could create a calculated column in my DSV. Similarly, I could use the number of minutes (or seconds) that have elapsed since midnight as the surrogate key of my time dimension so that I don't have to do a lookup for the Time Key either. (And could still create a calculated column for that key in my DSV) And because of star joins and the small size of the date and time dimensions, analysis of events between arbitrary date/time periods is lightning fast. (And not nearly as "evil" as the CTE Steve purposes as the replacement of the time dimension)

So to summarize, I still would give this article zero stars. I think it subtracts, not adds, to the body of knowledge.
Post #932527
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse