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
 UNION ALL
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2015-12-31'
 )
 SELECT
 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

OPTION (MAXRECURSION 0)

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
 UNION ALL
 SELECT HourOfDay + 1
 FROM TimeLoop
 WHERE HourOfDay + 1 < = 24
 )
 SELECT
 HourOfDay as HourId

INTO DimTime

FROM TimeLoop

OPTION (MAXRECURSION 0)

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
 UNION ALL
 SELECT FullDate + 1
 FROM DateLoop
 WHERE FullDate + 1 < = '2012-06-30'
 )
 SELECT
 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

OPTION (MAXRECURSION 0)

Week Start Date Function …


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

BEGIN
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
END
GO

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s