Calendar Tables – Why You Need One.
<credit: This section was copied from Made2Mentor>
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.
- 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.
A script to create a calendar table
I was recently faced with the challenge of creating a calendar dimension with hourly grain for reporting on a Microsoft SQL Server data warehouse application
There are two parts to the solution. A script to create and populate a table and a function used to generate integer keys from dates.
/* Title: Script to create a dateTime dimension with hourly granularity. Author: James Bayley firstname.lastname@example.org http://www.elephantpm.com published at http://blog.jamesbayley.com based on a script published by Joost van Rossum http://microsoft-ssis.blogspot.co.uk/2011/01/create-and-fill-time-dimension.html Purpose: This script creates and populates a dateTime dimension (calendar dimension) with hourly grain. The key is an integer of the form YYYYMMDDHH For each dateTime in your fact table you must introduce a similar integer key. For example if you have a "SaleDateTime" you must create "iSaleDateTime" which represents this dateTime as an integer of the form YYYYMMDDHH. You use this table in a left out join with your fact tables to ensure that you get a record for each hour you want to report. You can of course aggregate at higher levels. Comments: This script is easily extended to include other columns such as financial year and local times or you may increase the granularity to minutes or seconds by adding additional loops Copyright: (C) Please include the author block above. Licence Creative Common Attribution 2.0 http://creativecommons.org/licenses/by/2.0/uk/ */ drop table dbo.ElephantPM_Dim_DateTime go -- Create your dimension table -- Adjust to your own needs Create Table dbo.ElephantPM_Dim_DateTime ( iDateTime int PRIMARY KEY CLUSTERED, DateTime dateTime, DateString varchar(10), HourOfDay int, Date date, Day int, DayofYear int, DayofWeek int, DayofWeekName varchar(10), Week int, Month int, MonthName varchar(10), Quarter int, Year int, IsWeekend bit, IsLeapYear bit ) -- Declare and set variables for loop Declare @StartDate datetime, @EndDate datetime, @DateTime datetime, @iHour int -- Set the dates to match your data Set @StartDate = '2004/01/01' Set @EndDate = '2018/12/31' Set @DateTime = @StartDate -- Loop through dates WHILE @DateTime <=@EndDate BEGIN -- Check for leap year DECLARE @IsLeapYear BIT IF ((Year(@DateTime) % 4 = 0) AND (Year(@DateTime) % 100 != 0 OR Year(@DateTime) % 400 = 0)) BEGIN SELECT @IsLeapYear = 1 END ELSE BEGIN SELECT @IsLeapYear = 0 END -- Check for weekend DECLARE @IsWeekend BIT IF (DATEPART(dw, @DateTime) = 1 OR DATEPART(dw, @DateTime) = 7) BEGIN SELECT @IsWeekend = 1 END ELSE BEGIN SELECT @IsWeekend = 0 END set @iHour = 0 While @iHour <=23 begin -- Insert record in dimension table INSERT Into ElephantPM_Dim_DateTime ( [iDateTime], [DateTime], [Date], [DateString], [HourOfDay], [Day], [DayofYear], [DayofWeek], [Dayofweekname], [Week], [Month], [MonthName], [Quarter], [Year], [IsWeekend], [IsLeapYear] ) Values ( [dbo].[IntKeyForYYMMDDHH](@DateTime), @DateTime, convert(date,@DateTime), CONVERT(varchar(10), @DateTime, 6), -- See help for 6 explanation @iHour, Day(@DateTime), DATEPART(dy, @DateTime), DATEPART(dw, @DateTime), DATENAME(dw, @DateTime), DATEPART(wk, @DateTime), DATEPART(mm, @DateTime), DATENAME(mm, @DateTime), DATENAME(qq, @DateTime), Year(@DateTime), @IsWeekend, @IsLeapYear ) select @DateTime = dateadd(hour,1,@DateTime) set @iHour = @iHour +1 end END GO
/****** Object: UserDefinedFunction [dbo].[IntKeyForYYMMDDHH] Script Date: 01/04/2013 14:37:18 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IntKeyForYYMMDDHH]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[IntKeyForYYMMDDHH] GO USE [fogbugz-development-copy] GO ET QUOTED_IDENTIFIER ON GO -- ============================================= -- --Author: --James Bayley email@example.com -- http://www.elephantpm.com --published at http://blog.jamesbayley.com --Copyright: (C) --Please include the author block above. --Licence Creative Common Attribution 2.0 --http://creativecommons.org/licenses/by/2.0/uk/ -- ============================================= CREATE FUNCTION [dbo].[IntKeyForYYMMDDHH] ( -- Add the parameters for the function here @dateTime dateTime ) RETURNS int AS BEGIN -- Declare the return variable here DECLARE @sYYYYMMDD nchar(8) declare @iHour int declare @sHour nchar(2) declare @sYYYYMMDDHH nchar(10) declare @iReturn int -- Add the T-SQL statements to compute the return value here select @sYYYYMMDD = convert(nchar,@dateTime,112) select @iHour = datepart(hour, @dateTime) if (@iHour < 10) Begin select @sHour = '0' + convert(nchar,@iHour) End else Begin select @sHour = convert(nchar,@iHour) End select @sYYYYMMDDHH = @sYYYYMMDD + @sHour select @iReturn = convert(int,@sYYYYMMDDHH) -- Return the result of the function RETURN @iReturn END GO EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'This function is used to generate the integer key used to join fact tables to the dateTime dimension with hourly granularity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'IntKeyForYYMMDDHH' GO
(also known as date and time dimensions, date dimensions, dateTime dimension)
updated: 2017-01-03, added introductory section