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

Calendar Tables Expand / Collapse
Author
Message
Posted Thursday, July 15, 2010 6:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:20 AM
Points: 1,775, Visits: 362
The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.

Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.

As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.
Post #953044
Posted Thursday, July 15, 2010 7:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:00 AM
Points: 29, Visits: 226
Nice article!
People tend to avoid time tables relying on SQL functions. This is a good approach if you don't need to do those calculations frequently.
I remember a project where people wanted to kill me when I designed a table with time period and correspondent details as worked hours in that period and so on.
When somebody decided to change those periods from calendar months to something different, programmers got crazy and came to me to see what to do....
Just to hear all we had to do was to run a couple of UPDATE statements in the previously infamous time table
Post #953126
Posted Thursday, July 15, 2010 7:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
I was first introduced to calendar and time tables when I started learning about data warehousing. I have always thought they would be useful in the relational world as well. It is nice to see someone using them.
Post #953136
Posted Thursday, July 15, 2010 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 1:42 PM
Points: 19, Visits: 44
Victor Kirkpatrick (7/15/2010)
The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.

Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.

As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.


As a BI developer that is dealing with a Date Dimension that used such a means to create the key, it is a royal pain when you are trying to sift through millions of rows of data and where you are looking for a certain date range. Rather than having to go look up what value 5234 is, it would be nice to have the value be the date. This also helps developers if they want certain data and know the date, but only need to query on the date itself, so no need to join the extra table to do so, only need it if you need more dimension data. So a date table would be like 20100715 for today, something very easy for application developer to format and pass along.

Anyhow, I have been on the dev and DBA side and find that if you can help make your normalized data make a little since (with dates at least) it helps make finding issues in the data easier, because is it the app or the data is always the question.
Post #953155
Posted Thursday, July 15, 2010 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 2, 2013 9:35 AM
Points: 16, Visits: 79
Good article on a key topic for db/warehouse developers like me.

It seems that from a maintenance viewpoint, it would be much easier to define all of the variations on the main date as computed fields. Adding date formats and derived dates is as simple as adding a calculated field to the table.

We have several significant data collection and reporting systems and various data sources which use various date formats (including mainframes where records are defined with YY fields and MM fields - text and int types, combined and split). Any date fields in a data table can be joined to the date table on the appropriate date field(s) and of course we always try to store the resulting records in a Sql native datetime field.

Seems like this provides the performance benefits of joining to a table along with the super-simple maintenance - there is only one step on one field when adding months to the table.

Cheers!
Post #953161
Posted Thursday, July 15, 2010 8:19 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
>>
Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.

I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.
>>

So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?



_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Post #953162
Posted Thursday, July 15, 2010 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:01 AM
Points: 7, Visits: 37
katesl (7/15/2010)
>>

So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?


No, neither are OK, but I believe the former might just be safer than the latter. If the DBA leaves, and doesn't tell his replacement about this magic table of numbers, do we wait for the new DBA to find this table, and work out why it's there?

I think you're marginally *less* likely to have a well tested job screw up, than have a DBA remember to infrequently remember to maintain that table.
Post #953179
Posted Thursday, July 15, 2010 9:39 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: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Nice article. Thanks for the time and effort that you took to write it.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #953250
Posted Thursday, July 15, 2010 10:14 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:35 AM
Points: 121, Visits: 370
We advocate and use the calendar table approach too. We deal with reams (tech term) of daily financial data and this approach makes many difficult things easier and some impossible things possible

If not for the calendar table, we'd be recomputing date ranges, etc. to the nth degree. With the table, they're computed once (and referenced many times). It helps with the load.

Thanks for the article.
Post #953290
Posted Thursday, July 15, 2010 10:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
I don't object to a calendar table per se, and a specific calendar table does have certain advantages over a tally table.

But I'm not sure that big of a performance difference should necessarily be one of them.

When using the tally table, I don't think it's necessary to use functions on the table column in the JOIN clause. That will automatically limit the usefulness of any index on that column.

Couldn't we do something like this instead?:

...
FROM Tally
LEFT JOIN #Stay
ON
N <= 12 AND
ArrivalDate >= @StartYear1 AND
(ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear1) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear1)) OR
ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear2) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear2)))

GROUP BY ...


I.e., put all the JOIN criteria in the JOIN and make sure we don't use functions on the ArrivalDate. I'm not sure the optimizer is sophisticated enough to recognize that it should be able to use the index here, but I think at least it gives it a chance .

I know for a single month I usually an index seek vs. a scan using this style of coding, but for the side-by-side month, I'm not sure.


Scott Pletcher, SQL Server MVP 2008-2010
Post #953315
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse