Quick Hacks (Creating a Date Dimension)

If you require a Date Dimension and you are unhappy with the automatically generated sample provided for in the Analysis Services Business Intelligence Wizard, try your own custom table.

The following code will create a list of dates between Jan 1, 1970 and Dec 32, 2015 then insert the content into a table aptly named DimDate.   To use this code you will need to edit as follows :

  • Change the Begin Date from 1970-01-01 to your choice
  • Change the End Date from 2015-12-31 to your choice
  • Add as many additional date attribute fields you require in the Date Dimension.  The provided attributes allow only for a minimalist set of columns to create a sample date hierarchy
  • Change the name of the table INTO DimDate to your own choice
WITH DateLoop as
 SELECT Cast ('1970-01-01' as DateTime) FullDate
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2015-12-31'
 CAST(CONVERT(varchar(8),FullDate,112) AS int) as DateId
 , FullDate as FullDate
 , CAST(CONVERT(varchar(6),FullDate,112) AS int) as MonthId
 , CAST(CONVERT(varchar(4),FullDate,112) AS int) as YearId

INTO DimDate

FROM DateLoop


Once you get this working, try creating your own time (hour of day) dimension by adapting the logic above and customising the code sample provided below.

WITH TimeLoop as
 SELECT Cast (1 as int) HourOfDay
 SELECT HourOfDay + 1
 FROM TimeLoop
 WHERE HourOfDay + 1 < = 24
 HourOfDay as HourId

INTO DimTime

FROM TimeLoop


A more detailed working example is provided below.  The WeekStartDate function is provided at the end of this article to demonstrate how a function can be used for some of these dynamic date calculations.

WITH DateLoop as
 SELECT Cast ('2012-01-01' as DateTime) FullDate
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2012-06-30'
 FullDate AS PK_Date
, CAST(CONVERT(varchar(8),FullDate,112) AS int) as Date_Id
, CONVERT(varchar(50),FullDate,106) as Date_Name
, DATEADD(yy, DATEDIFF(yy,0,FullDate), 0) AS [YearStartDate]
, DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,FullDate)+1,0))) AS [YearEndDate]
, CAST(LEFT(CONVERT(varchar(8),FullDate,112),4) AS int) AS Year_Id
, 'Year ' + LEFT(CONVERT(varchar(8),FullDate,112),4) AS Year_Name
, CONVERT(datetime,DATEADD(dd,-(DAY(FullDate)-1),FullDate),103) AS [MonthStartDate]
, CONVERT(datetime,CONVERT(varchar(12),DATEADD(dd,-(DAY(DATEADD(mm,1,FullDate))),DATEADD(mm,1,FullDate)),112),103) AS [MonthEndDate]
, CAST(CONVERT(varchar(6),FullDate,112) AS int) as Month_Id
, CAST(DATENAME(MM,FullDate) as varchar(30)) as Month_Name
, CAST(LEFT(DATENAME(MM,FullDate),3) AS CHAR(3)) AS Month_ShortName
, CAST(DATEPART(dy,FullDate) as int) AS Day_Of_Year
, CAST(DATEPART(dd,FullDate) as int) AS Day_Of_Month
, CAST(DATEPART(mm,FullDate) as int) AS Month_Of_Year
, CAST(DATENAME(dw , FullDate) AS varchar(30)) AS WeekDay_Name
, CAST(LEFT(DATENAME(dw , FullDate),3) as CHAR(3)) AS WeekDay_ShortName
, DATEPART(WEEKDAY, FullDate) AS Day_Of_Week
, dbo.WeekStartDate(FullDate) AS WeekStartDate
, DATEADD(day,6, dbo.WeekStartDate(FullDate)) AS WeekEndDate
, DATEADD(day,-1,dbo.WeekStartDate(FullDate)) AS PreviousWeekEndDate

INTO DimDate

FROM DateLoop


Week Start Date Function …

CREATE FUNCTION [dbo].[WeekStartDate] (@MidWeekDate DateTime)

DECLARE @WeekCommence DateTime
 SET @MidWeekDate = DATEADD(dd, DATEDIFF(d, 0, @MidWeekDate),0)
 SET @WeekCommence = DateAdd(d, -((@@DATEFIRST + DatePart(dw, @MidWeekDate) -1) % 7), @MidWeekDate)
 RETURN @WeekCommence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s