Tags

, , , , ,

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.

The script.

/* Title: Script to create a dateTime dimension with 
hourly granularity.

 Author: 
 James Bayley james@elephantpm.com 
 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

The function.

/****** 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 james@elephantpm.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