Archives

Calendar Tables - Why You Need One.

*** Note: The code below is for use with SQL 2005 and previous versions. I have written new code to take advantage of the Date Datatype in SQL 2008 and above which can be found here.

What is a Date (Calendar) Table?

For the purposes of this article a Date Table is a dedicated table containing a single record for each day in a defined range. They include fields with descriptive attributes for each day such as Year, Month, Week, and whether a particular day is a work day or not. Date Tables (Dimensions) are an integral part of every Data Warehouse, but they can be used with a regular (OLTP) database as well.

Why do I need a Date Table?

There are several situations in which a date table is extremely helpful. Let’s consider just a few. Suppose you need to….

  • Generate a running sales total per day and include days in which you have no sales such as weekends.
  • Calculate the number of workdays between two dates.
  • Calculate projected ship dates for products based upon lead times.
  • Aggregate data by non-standard Fiscal Years or Periods. Perhaps your company uses the Fiscal Year of the US Government which runs from October 1st until September 30th. My current employer uses the 4-4-5 Calendar methodology, which is what originally led me to create this Date Table script.
  • Need to track data by seasons, whatever that means to your company. For example, if you manufactured bicycles you’d certainly want to compare sales figures in the spring versus fall.
  • Need to compare data using rolling months/periods. Suppose your company wants to know how March sales for this year compared to March for the past 5 years. Or you want to compare sales which occurred on Fridays to each other to look for trends.
  • Need to create Gantt Charts.

While many of these issues can be handled by writing T-SQL code, they’re much easier with a dedicated date table. Also, the Date Table can be customized to track anything about a particular day you want.

What about Space Considerations?

One concern that people have when creating a Date Table is the “wasted” space since each day is its own record. The space used is negligible, especially when compared to the benefits of having a dedicated date table. For example, when I execute my script for the years 2000 through 20035 the space used is less than 250MB.

How do I create a Date Table?

Kimball Date Dimensions
In the The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2and the previous 2005 version, the Kimball Group recommends that you create a Date Dimension using Excel. In the latest book, they provide a Date Dimension Spreadsheet which is available for Download and utilizes VBA to generate the table. They recommend you create a SSIS package to load that data into SQL Server.

While this will work, and some non-technical people might prefer this method, I prefer to use SQL Script, because I may not always have access to SSIS.

Using a SQL Script
When I searched for a Date Table Script a few years ago, I couldn’t find something suitable for my needs. Most of them include only one set of fields (regular Calendar) and I to report via custom fiscal calendars.

I created this script from a function called F_TABLE_DATE by Michael Valentine Jones from SQLTeam and have since gotten his permission to distribute it. I’ve modified the script so that it generates a Kimball style date dimension and it’s very comprehensive, yet simple to use and customize.

  1. Create a database called DateDatabase or whatever you’d like to call it. We’ll install F_TABLE_DATE in it and presumably the Date Table as well.
  2. Open the following script and replace “DateDatabase” (Find and Replace) with whatever database name you chose in step 1.
  3. Run the Script to create the function.

Your database should now contain the function used to create Date Tables. As a test, run the following script:

SELECT * FROM dbo.F_TABLE_DATE ('20000101', '20101231')
ORDER BY 1

You should receive 4019 Rows, 11 years worth of days plus a record for unknown dates. Notice the extensive list of fields built into the function. All of them are documented in the script.

Use a Select Into statement to create the Date Table like so. Replace dbo.DimDate with whatever you’d like to call your Date Table.

IF EXISTS
     (SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DimDate')
  BEGIN
        DROP TABLE dbo.DimDate;
  END
 
Select
  DateId
 ,Date
 ,NextDayDate
 ,CalendarYear
 ,CalendarYearQuarter
 ,CalendarYearMonth
 ,CalendarYearDayOfYear
 ,CalendarQuarter
 ,CalendarMonth
 ,CalendarDayOfYear
 ,CalendarDayOfMonth
 ,CalendarDayOfWeek
 ,CalendarYearName
 ,CalendarYearQuarterName
 ,CalendarYearMonthName
 ,CalendarYearMonthNameLong
 ,CalendarQuarterName
 ,CalendarMonthName
 ,CalendarMonthNameLong
 ,WeekdayName
 ,WeekdayNameLong
 ,CalendarStartOfYearDate
 ,CalendarEndOfYearDate
 ,CalendarStartOfQuarterDate
 ,CalendarEndOfQuarterDate
 ,CalendarStartOfMonthDate
 ,CalendarEndOfMonthDate
 ,QuarterSeqNo
 ,MonthSeqNo
 ,FiscalYearName
 ,FiscalYearPeriod
 ,FiscalYearDayOfYear
 ,FiscalYearWeekName
 ,FiscalSemester
 ,FiscalQuarter
 ,FiscalPeriod
 ,FiscalDayOfYear
 ,FiscalDayOfPeriod
 ,FiscalWeekName
 ,FiscalStartOfYearDate
 ,FiscalEndOfYearDate
 ,FiscalStartOfPeriodDate
 ,FiscalEndOfPeriodDate
 ,ISODate
 ,ISOYearWeekNo
 ,ISOWeekNo
 ,ISODayOfWeek
 ,ISOYearWeekName
 ,ISOYearWeekDayOfWeekName
 ,DateFormatYYYYMMDD
 ,DateFormatYYYYMD
 ,DateFormatMMDDYEAR
 ,DateFormatMDYEAR
 ,DateFormatMMMDYYYY
 ,DateFormatMMMMMMMMMDYYYY
 ,DateFormatMMDDYY
 ,DateFormatMDYY
 ,WorkDay
 ,IsWorkDay
into
  dbo.DimDate -- Choose whatever name you like for your table.
From
  dbo.F_TABLE_DATE('20000101','20251231') -- Edit the parameters for a custom date range.
  Order By 1

Don’t panic, you don’t need to use all of the fields which is why this script is so highly customizable. Any fields you don’t want can be commented out before running the Select Into statement, which allows you to create your own custom Date Table.

The final step is to add a Primary Key to the DateID and an index for the Date and NextDayDate fields. As I can’t predict the usage patterns for your date table, you’ll have to implement further indexing if necessary.

ALTER TABLE [dbo].[DimDate]
ADD  PRIMARY KEY CLUSTERED ([DateId])
WITH FILLFACTOR = 100
 ON [PRIMARY]
GO
 
CREATE INDEX id_Dates
ON DateDatabase.dbo.DimDate 
	(Date, 
	 NextDayDate)

How do I use the Date Table?

Using the date table is simple, you simply link to it from the query from your source system. Use the following template.

SELECT
  MY.date
 ,DTE.CalendarYear
 ,DTE.CalendarMonthName
FROM
    MyTable MY
  INNER JOIN
    DateDatabase.dbo.DimDate DTE
  ON MY.date >= DTE.date AND
     MY.date < DTE.NextDayDate

Both DTE.Date and DTE.NextDayDate represent midnight of the two consecutive days. Therefore the time portion of a Datetime field can be ignored.

What about the fiscal fields?

After running these scripts, and examining the Date Table, you’ll notice that the fiscal fields mostly mirror the calendar fields. This is by design. In the next article I’ll explain how to customize the fiscal fields and a script to extract the fiscal information out of Made2Manage. Even if you don’t use M2M, you can still use the fiscal script as an example of how to do it.

Any questions?

Related posts:

12 comments to Calendar Tables and Why You Need One.

  • Great article! I myself have never heard of or thought of this idea.

  • Just wondering, have you played around with CTP1 (Denali)? Downloading it now for kicks.

  • I have a copy of it but haven’t had time to play with it. I’ve been focusing on presentations and the Data Warehouse project.

  • Hi David,

    I think you should probably update your table for SQL 008 + and use just the DATE datatype thus removing the need for the complex join. Also as a date only takes 3 bits of storage space you will save space in your table.

    If you check out my blog there is the script that I use for data warehouses.

    Barny (aka B4PJS)

  • Barny, thanks for the suggestion. I just Followed you on Twitter and will be reading your blog. Unfortunately, the majority of my mission critical databases are still in SQL 2000 (embarrassing, I know.) Also, a large percentage of my readership is as well.

    However, I am going to add an optional script to add a Date datatype column and will post that as well. In regards to storage space in the date table, I don’t feel it’s an issue. The clustered index in this script is an INT datatype anyway, and the additional space for datetime is negligible because there aren’t many records.

    Perhaps I’ll create a script with strictly Date datatypes for 2008+ and leave this one as is.

    Thanks for your input.

  • Yeah, I understand your predicament. My DW templates all use a date column and a time column in the fact tables as this allows for basic date and time calculations on the raw fact tables without the need for unnecessary joins. Also I have heard told from Chris Webb that powerpivot plays a lot nicer with natural date keys rather than surrogate integers. Plus don’t you just hate seeing time keys as ints!

  • So, you’re using a Date datatype field as your surrogate key in your Date Dimension rather than the integer column recommended by Kimball?

  • Yep, saves on a lot of processor cycles when looking at date/time ranges. Also, I do believe Kimball hasn’t updated to these new fangled datatypes. Plus as I noted before, date only takes three bits vs ints four bits. Those extra bits start to count in billion row DW’s :O)

  • Barny, correct me if I’m wrong, but one of the reasons they use the INT surrogate key in the format of YYYYMMDD was to make partitioning easier. Judging from the spelling on your blog, you’re from the other side of “the pond” and that’s how you express dates anyway. However, we Americans express dates naturally as MMDDYYYY.

    If I used Date as my data type, I’d lose the ability to easily partition by year. No?

  • [...] used my Date Table Script to generate two date tables, one with a smart integer key called DateTableInteger and another [...]

  • [...] a Date Table/Dimension on SQL 2008. In a previous article, I listed the benefits of using a dedicated date table and included a customizable script which [...]

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>