Date Dimension Table Script

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)
Advertisements

SSRS Show Dates on Chart with Zero Counts

The users wanted a chart that had a count by day of a set of events. Some days did not have events and they wanted to see these days on the chart even though the days had a zero count. I tried to accomplish this with a CTE but I couldn’t get the CTE to return the data I wanted. So instead I looked for an SSRS solution.

My output looked like so:
2014-01-30_10-27-47

So then in SSRS I created a stacked bar chart, and did the following.

  1. Right Click on the Horizonal Axis and click Horizontal Axis Properties
  2. Change the Axis Type to Scalar (Number/Dates)
  3. Change the Interval to 1
  4. Change the Intervalt Type to Number
  5. Click OK

Executed the report and it is working as planned.

2014-01-30_10-31-17