I had a recent moment of despair when our Date Dimension table driving one of our key monthly reports ended in 2013. We had zero documentation about the table and zero references to scripts on how to rebuild. I am not even sure where the table came from, it just showed up one day.
By the miracle of a Google search I was able to find an example of a Date Dim table that almost completely matched our table. You’ll find several versions out there, I just didn’t want to reverse engineer all the missing columns. Here is a link to the example: http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/creating-a-time-dimension-2326178
I took the article and made some tweaks. I am parking the scripts here mainly so I can find it again if I am in a pinch. I like this table because it has a column for special days where you can make up your own business rules, etc. Some of the columns I need to explore more.
The last 5 columns are set to NULL. The original table I had would tell you what Monday of the month it is, etc. Need to update that. I also included an UPDATE script for 2014 holidays. This could be scripted as part of the procedure, just haven’t got that far yet. Same call looks like this.
--parm 1 = year to start --parm 2 = number of years to generate EXEC dbo.datedim_insert 2013,7
Table build script
CREATE TABLE [dbo].[DateDim]( [Date_ID] [int] NOT NULL, [Date] [datetime] NOT NULL, [Julian_Date] [int] NOT NULL, [Calendar_Year] [int] NOT NULL, [Financial_Year] [int] NOT NULL, [Calendar_Quarter] [tinyint] NOT NULL, [Calendar_Quarter_ID] [smallint] NOT NULL, [Calendar_Quarter_Name] [char](8) NOT NULL, [Financial_Quarter] [tinyint] NOT NULL, [Financial_Quarter_ID] [int] NOT NULL, [Financial_Quarter_Name] [char](13) NOT NULL, [Season] [char](6) NOT NULL, [Calendar_Month_ID] [int] NOT NULL, [Calendar_Month_Number] [tinyint] NOT NULL, [Month_Name] [nchar](12) NOT NULL, [Month_Abbreviation] [nchar](3) NOT NULL, [Financial_Month_ID] [int] NOT NULL, [Financial_Month_Number] [tinyint] NOT NULL, [Calendar_Week_ID] [int] NOT NULL, [Calendar_Week] [tinyint] NOT NULL, [Financial_Week_ID] [int] NOT NULL, [Financial_Week] [tinyint] NOT NULL, [Day_of_Calendar_Year] [smallint] NOT NULL, [Day_of_Financial_Year] [smallint] NOT NULL, [Day_of_Month] [tinyint] NOT NULL, [Week_Day_Number] [tinyint] NOT NULL, [Week_Day_Name] [nchar](10) NOT NULL, [Week_Day_Abbreviation] [nchar](3) NOT NULL, [Is_a_Weekend_day] [bit] NOT NULL, [Is_a_Public_Holiday] [bit] NOT NULL DEFAULT ((0)), [Is_a_Leap_Year] [bit] NOT NULL DEFAULT ((0)), [Is_a_Special_day] [bit] NOT NULL DEFAULT ((0)), [Days_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((365)), [Days_In_Financial_Year] [smallint] NOT NULL DEFAULT ((365)), [Weekdays_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((260)), [Weekdays_In_Financial_Year] [smallint] NOT NULL DEFAULT ((260)), [Workdays_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((247)), [Workdays_In_Financial_Year] [smallint] NOT NULL DEFAULT ((247)), [Days_In_Calendar_Year_Sofar] [smallint] NOT NULL, [Days_In_Financial_Year_Sofar] [smallint] NOT NULL, [Weekdays_In_Calendar_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), [Weekdays_In_Financial_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), [Workdays_In_Calendar_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), [Workdays_In_Financial_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), [Days_In_Month] [smallint] NOT NULL, [Weekdays_In_Month] [smallint] NOT NULL DEFAULT ((21)), [Workdays_In_Month] [smallint] NOT NULL DEFAULT ((20)), [Days_In_Month_Sofar] [smallint] NOT NULL, [Weekdays_In_Month_Sofar] [smallint] NOT NULL DEFAULT ((0)), [Workdays_In_Month_Sofar] [smallint] NOT NULL DEFAULT ((0)), [wMondayofMonth] smallint NULL, [wTuesdayofMonth] smallint NULL, [wWednesdayofMonth] smallint NULL, [wThursdayofMonth] smallint NULL, [wFridayofMonth] smallint NULL) ALTER TABLE dbo.DateDim ADD CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (Date_ID);
Population Procedure
IF OBJECT_ID('dbo.datedim_insert') IS NULL
EXEC ('CREATE PROCEDURE dbo.datedim_insert AS SELECT 0')
GO
/*********************************************************************************
Author: Some Guy on the Internet
Modified by: thejoestory
Date: 02/06/2014
Purpose: Populate the Dates Dimension table with values.
Parameters: @StartYear - Year you want to start. The start year is actually one
year before the year you pass to ensure the year has the correct week
days in financial year set.
Public and Special Days must be done manually. Special days allow you
to
@NumYears - Years you want to generate
*********************************************************************************/
ALTER PROCEDURE dbo.datedim_insert
@StartYear INT,
@NumYears INT
AS
SET NOCOUNT ON
DECLARE @DT DATETIME
SELECT @DT = DATEADD(YYYY,-1,CAST('1/Jan/'+cast(@StartYear AS CHAR(4)) AS DATETIME))
DECLARE @DATEID INT
DECLARE @JDATE INT
DECLARE @CALYEAR INT
DECLARE @FINYEAR INT
DECLARE @CALQUARTER INT
DECLARE @CALQTRID INT
DECLARE @CALQTRNAME CHAR(8)
DECLARE @FINQUARTER INT
DECLARE @FINQTRID INT
DECLARE @FINQTRNAME CHAR(13)
DECLARE @SEASON CHAR(6)
DECLARE @CALMONTHID INT
DECLARE @FINMONTHID INT
DECLARE @CALMONTH INT
DECLARE @MONTHNAME NCHAR(12)
DECLARE @MONTHABBR NCHAR(3)
DECLARE @FINMONTH INT
DECLARE @CALWEEKID INT
DECLARE @CALWEEK INT
DECLARE @FINWEEKID INT
DECLARE @FINWEEK INT
DECLARE @DAYOFCALYEAR INT
DECLARE @DAYOFFINYEAR INT
DECLARE @DAYOFMONTH INT
DECLARE @DAYOFWEEK INT
DECLARE @DAYOFWEEKNAME NCHAR(10)
DECLARE @DAYOFWEEKABBR NCHAR(3)
DECLARE @ISWEEKENDDAY BIT
DECLARE @ISLEAPYEAR BIT
-- skip public and special day flags, need to be done manually
DECLARE @DAYSINCALYEAR INT
DECLARE @DAYSINFINYEAR INT
-- weekdays in cal year set in update
-- weekdays in fin year set in update
-- workdays in cal year set in update
-- workdays in fin year set in update
-- 'day in year cal sofar' col = dayofCALyear col
-- 'day in year fin sofar' col = dayofFINyear col
DECLARE @WEEKDAYSCALYEARSOFAR INT
DECLARE @WEEKDAYSFINYEARSOFAR INT
DECLARE @WORKDAYSCALYEARSOFAR INT
DECLARE @WORKDAYSFINYEARSOFAR INT
DECLARE @DAYSINMONTH INT
-- weekdays in month set in update
-- workdays in month set in update
-- days in month sofar = dayofmonth
DECLARE @WEEKDAYSMONTHSOFAR INT
-- work days in month sofar = week days in month sofar
-- **************************** --
-- *** initialize variables *** --
-- week days cal year sofar (CHECK)
SELECT @WEEKDAYSFINYEARSOFAR=0
IF (DATEPART(DY,@DT) = 1)
BEGIN
SELECT @WEEKDAYSCALYEARSOFAR=0
END
-- week days fin year sofar (CHECK)
IF (DATEPART(MM,@DT) = 7 AND DATEPART(DD,@DT) = 1)
BEGIN
SELECT @WEEKDAYSFINYEARSOFAR=0
END
-- ************************* --
-- *** process each date *** --
WHILE (DATEPART(YYYY,@DT) <= @StartYear + @NumYears)
BEGIN
--get basic date info
SELECT @ISWEEKENDDAY =0
SELECT @CALYEAR = DATEPART (YEAR, @DT)
SELECT @CALQUARTER = DATEPART (QUARTER, @DT)
SELECT @CALMONTH = DATEPART (MONTH , @DT)
SELECT @MONTHNAME = DATENAME(MM, @DT)
SELECT @CALWEEK = DATEPART (WEEK , @DT)
SELECT @DAYOFCALYEAR = DATEPART (DY , @DT)
SELECT @DAYOFMONTH = DATEPART (DAY , @DT)
SELECT @DAYOFWEEK = DATEPART (DW , @DT)
SELECT @DAYOFWEEKNAME = DATENAME(DW, @DT)
-- date id
SELECT @DATEID = (@CALYEAR * 10000) + (@CALMONTH * 100) + @DAYOFMONTH
-- julian date
SELECT @JDATE = (@CALYEAR * 1000) + @DAYOFCALYEAR
-- calendar quarter id
SELECT @CALQTRID = (@CALYEAR * 10) + @CALQUARTER
-- calendar quarter name
SELECT @CALQTRNAME = CAST(@CALYEAR AS CHAR(4)) + ' Q ' +
CAST(@CALQUARTER AS CHAR (1))
-- calendar week id
SELECT @CALWEEKID = (@CALYEAR * 100) + @CALWEEK
-- set weekend BIT
IF ( @DAYOFWEEK = 1 OR @DAYOFWEEK = 7 )
BEGIN
SELECT @ISWEEKENDDAY = 1
END
-- set leap year BIT
IF ((@CALYEAR % 4 = 0) AND (@CALYEAR % 100 != 0 OR @CALYEAR % 400 = 0))
BEGIN
SELECT @ISLEAPYEAR =1
END
ELSE
BEGIN
SELECT @ISLEAPYEAR =0
END
-- financial year
IF (@CALMONTH < 7)
BEGIN
SELECT @FINYEAR = ((@CALYEAR - 1) * 10000) + (@CALYEAR)
END
ELSE
BEGIN
SELECT @FINYEAR = ((@CALYEAR) * 10000) + (@CALYEAR + 1)
END
-- financial qtr
IF (@CALQUARTER > 2)
BEGIN
SELECT @FINQUARTER = @CALQUARTER - 2
END
ELSE
BEGIN
SELECT @FINQUARTER = @CALQUARTER + 2
END
-- financial quarter id
SELECT @FINQTRID = (@FINYEAR * 10) + @FINQUARTER
-- financial quarter name
SELECT @FINQTRNAME = LEFT(@FINYEAR, 4) + '-' + RIGHT(@FINYEAR, 4) + ' Q
' + CAST(@FINQUARTER AS CHAR (1))
-- season
IF (@CALMONTH IN(9,10,11))
BEGIN
SELECT @SEASON = 'Spring'
END
IF (@CALMONTH IN(12,1,2))
BEGIN
SELECT @SEASON = 'Summer'
END
IF (@CALMONTH IN(3,4,5))
BEGIN
SELECT @SEASON = 'Autumn'
END
IF (@CALMONTH IN(6,7,8))
BEGIN
SELECT @SEASON = 'Winter'
END
-- calendar month id
SELECT @CALMONTHID = (@CALYEAR * 100) + @CALMONTH
-- month abbrev
SELECT @MONTHABBR = LEFT(@MONTHNAME, 3)
-- financial month number
IF (@CALMONTH > 6)
BEGIN
SELECT @FINMONTH = @CALMONTH - 6
END
ELSE
BEGIN
SELECT @FINMONTH = @CALMONTH + 6
END
-- financial month id
SELECT @FINMONTHID = (@FINYEAR * 100) + @FINMONTH
-- financial week
IF (@CALWEEK > 26)
BEGIN
SELECT @FINWEEK = @CALWEEK - 26
END
ELSE
BEGIN
SELECT @FINWEEK = @CALWEEK + 26
END
-- calendar week id
SELECT @FINWEEKID = (@FINYEAR * 100) + @FINWEEK
-- day of fin year
IF (@CALMONTH > 6)
BEGIN
SELECT @DAYOFFINYEAR = DATEPART(DY, @DT) - DATEDIFF(DD,
('1/JAN/'+LEFT(@FINYEAR,4)), ('1/JUL/'+LEFT(@FINYEAR,4)))
END
ELSE
BEGIN
SELECT @DAYOFFINYEAR = DATEPART(DY, @DT) + DATEDIFF(DD,
('1/JUL/'+LEFT(@FINYEAR,4)), ('1/JAN/'+RIGHT(@FINYEAR,4)))
END
-- day abbrev
SELECT @DAYOFWEEKABBR = LEFT(@DAYOFWEEKNAME, 3)
-- days in cal year
SELECT @DAYSINCALYEAR = DATEPART(DY, CAST(('31' + '/DEC/' +
CAST(@CALYEAR AS NCHAR(4))) AS datetime))
-- days in fin year
SELECT @DAYSINFINYEAR = DATEDIFF(DD, CAST(('1' + '/JUL/' +
LEFT(@FINYEAR, 4)) AS datetime), CAST(('1' + '/JUL/' + RIGHT(@FINYEAR,
4)) AS datetime))
-- last day of the month
SELECT @DAYSINMONTH = DATEPART(DD, DATEADD(DD, -1,
CAST(('1/'+LEFT(DATENAME(MM, DATEADD(MM, 1, @DT)),3)+'/'+CAST(@CALYEAR
AS NCHAR(4))) AS DATETIME)))
-- week days cal year sofar (CHECK)
IF (@DAYOFCALYEAR = 1)
BEGIN
SELECT @WEEKDAYSCALYEARSOFAR=0
END
-- week days fin year sofar (CHECK)
IF (@CALMONTH = 7 AND @DAYOFMONTH = 1)
BEGIN
SELECT @WEEKDAYSFINYEARSOFAR=0
END
-- week days month sofar (CHECK)
IF (@DAYOFMONTH = 1)
BEGIN
SELECT @WEEKDAYSMONTHSOFAR = 0
END
-- week days cal year sofar / fin year sofar / month sofar(INCREMENTAL)
IF (@ISWEEKENDDAY = 0)
BEGIN
SELECT @WEEKDAYSCALYEARSOFAR= @WEEKDAYSCALYEARSOFAR + 1
SELECT @WEEKDAYSFINYEARSOFAR= @WEEKDAYSFINYEARSOFAR + 1
SELECT @WEEKDAYSMONTHSOFAR = @WEEKDAYSMONTHSOFAR + 1
END
-- Print the DateID to the screen (for error checking)
PRINT '@DATEID = ' + CAST( @DATEID AS NVARCHAR(30));
PRINT '@DT = ' + CAST( @DT AS NVARCHAR(30));
PRINT '@JDATE = ' + CAST(@JDATE AS NVARCHAR(30));
PRINT '@CALYEAR = ' + CAST(@CALYEAR AS NVARCHAR(30));
PRINT '@FINYEAR = ' + CAST(@FINYEAR AS NVARCHAR(30));
PRINT '@CALQUARTER = ' + CAST(@CALQUARTER AS NVARCHAR(30));
PRINT '@FINQUARTER = ' + CAST(@FINQUARTER AS NVARCHAR(30));
PRINT '@SEASON = ' + @SEASON;
PRINT '@CALMONTHID = ' + CAST(@CALMONTHID AS NVARCHAR(30));
PRINT '@CALMONTH = ' + CAST(@CALMONTH AS NVARCHAR(30));
PRINT '@MONTHNAME = ' + @MONTHNAME;
PRINT '@MONTHABBR = ' + @MONTHABBR;
PRINT '@FINMONTH = ' + CAST(@FINMONTH AS NVARCHAR(30));
PRINT '@CALWEEK = ' + CAST(@CALWEEK AS NVARCHAR(30));
PRINT '@FINWEEK = ' + CAST(@FINWEEK AS NVARCHAR(30));
PRINT '@DAYOFCALYEAR = '+ CAST(@DAYOFCALYEAR AS NVARCHAR(30));
PRINT '@DAYOFFINYEAR = ' + CAST(@DAYOFFINYEAR AS NVARCHAR(30));
PRINT '@DAYOFMONTH = ' + CAST(@DAYOFMONTH AS NVARCHAR(30));
PRINT '@DAYOFWEEK = ' + CAST(@DAYOFWEEK AS NVARCHAR(30));
PRINT '@DAYOFWEEKNAME = '+ @DAYOFWEEKNAME;
PRINT '@DAYOFWEEKABBR = ' + @DAYOFWEEKABBR;
PRINT '@ISWEEKENDDAY = ' + CAST(@ISWEEKENDDAY AS NVARCHAR(30));
PRINT '@ISLEAPYEAR = ' + CAST(@ISLEAPYEAR AS NVARCHAR(30));
-- ***********************************************
-- *** insert values into Date Dimension table *** --
INSERT DateDim(
[Date_ID],
[Date],
[Julian_Date],
[Calendar_Year],
[Financial_Year],
[Calendar_Quarter],
[Calendar_Quarter_ID],
[Calendar_Quarter_Name],
[Financial_Quarter],
[Financial_Quarter_ID],
[Financial_Quarter_Name],
[Season],
[Calendar_Month_ID],
[Calendar_Month_Number],
[Month_Name],
[Month_Abbreviation],
[Financial_Month_ID],
[Financial_Month_Number],
[Calendar_Week_ID],
[Calendar_Week],
[Financial_Week_ID],
[Financial_Week],
[Day_of_Calendar_Year],
[Day_of_Financial_Year],
[Day_of_Month],
[Week_Day_Number],
[Week_Day_Name],
[Week_Day_Abbreviation],
[Is_a_Weekend_day],
[Is_a_Leap_Year],
[Days_In_Calendar_Year],
[Days_In_Financial_Year],
[Days_In_Calendar_Year_Sofar],
[Days_In_Financial_Year_Sofar],
[Weekdays_In_Calendar_Year_Sofar],
[Weekdays_In_Financial_Year_Sofar],
[Workdays_In_Calendar_Year_Sofar],
[Workdays_In_Financial_Year_Sofar],
[Days_In_Month],
[Days_In_Month_Sofar],
[Weekdays_In_Month_Sofar],
[Workdays_In_Month_Sofar])
VALUES (
@DATEID,
@DT,
@JDATE,
@CALYEAR,
@FINYEAR,
@CALQUARTER,
@CALQTRID,
@CALQTRNAME,
@FINQUARTER,
@FINQTRID,
@FINQTRNAME,
@SEASON,
@CALMONTHID,
@CALMONTH,
@MONTHNAME,
@MONTHABBR,
@FINMONTHID,
@FINMONTH,
@CALWEEKID,
@CALWEEK,
@FINWEEKID,
@FINWEEK,
@DAYOFCALYEAR,
@DAYOFFINYEAR,
@DAYOFMONTH,
@DAYOFWEEK,
@DAYOFWEEKNAME,
@DAYOFWEEKABBR,
@ISWEEKENDDAY,
@ISLEAPYEAR,
@DAYSINCALYEAR,
@DAYSINFINYEAR,
@DAYOFCALYEAR -1,
@DAYOFFINYEAR -1,
@WEEKDAYSCALYEARSOFAR,
@WEEKDAYSFINYEARSOFAR,
@WEEKDAYSCALYEARSOFAR,
@WEEKDAYSFINYEARSOFAR,
@DAYSINMONTH,
@DAYOFMONTH,
@WEEKDAYSMONTHSOFAR,
@WEEKDAYSMONTHSOFAR)
--increment the date one day
SELECT @DT = DATEADD(DD, 1, @DT)
-- end while
END;
-- Calculate the actual weekdays in each calendar year
WITH CWeekends (CYear, Num_Weekend_Days, Days_In_Calendar_Year, Weekdays_In_Calendar_Year) AS
(
SELECT
Calendar_Year AS CYear,
COUNT(Is_a_Weekend_day) AS Num_Weekend_Days,
MAX(Days_In_Calendar_Year) AS Days_In_Calendar_Year,
MAX(Weekdays_In_Calendar_Year) AS Weekdays_In_Calendar_Year
FROM dbo.DateDim
WHERE Is_a_Weekend_day = 1
GROUP BY Calendar_Year
)
UPDATE dbo.DateDim
SET Weekdays_In_Calendar_Year = CWeekends.Days_In_Calendar_Year - CWeekends.Num_Weekend_Days
FROM CWeekends
WHERE DateDim.Calendar_Year = CWeekends.CYear;
-- Calculate the actual weekdays in each calendar year
WITH FWeekends (FYear, Num_Weekend_Days, Days_In_Financial_Year, Weekdays_In_Financial_Year) AS
(
SELECT
Financial_Year AS FYear,
COUNT(Is_a_Weekend_day) AS Num_Weekend_Days,
MAX(Days_In_Financial_Year) AS Days_In_Financial_Year,
MAX(Weekdays_In_Financial_Year) AS Weekdays_In_Financial_Year
FROM dbo.DateDim
WHERE Is_a_Weekend_day = 1
GROUP BY Financial_Year
)
UPDATE dbo.DateDim
SET Weekdays_In_Financial_Year = FWeekends.Days_In_Financial_Year -
FWeekends.Num_Weekend_Days
FROM FWeekends
WHERE DateDim.Financial_Year = FWeekends.FYear;
-- Set workdays to week days
UPDATE dbo.DateDim
SET
Workdays_In_Calendar_Year = Weekdays_In_Calendar_Year,
Workdays_In_Financial_Year = Weekdays_In_Financial_Year,
Workdays_In_Month = Weekdays_In_Month
Updates Script
--Public Holiday Updates UPDATE dbo.DateDim SET is_a_public_holiday = 1 WHERE Date_ID IN (20140101,20140120,20140217,20140526,20140704,20140901,20141013,20141111,20141127,20141225) --Special Days --Non Public Holidays, day after thanksgiving, day before Christmas UPDATE dbo.DateDim SET is_a_special_day = 1 WHERE DATE_ID IN (20141128,20141224)
