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)