Setup Date Dimension

You cannot think of business intelligence without date dimension and it is so much so that all our measures mostly spins around date dimension for date/time slicing, YTD, MTD, Month to Month comparison and goes on. In my current assignment I had to build date dimension and that’s when I thought of checking with the BI core group in my company. I got plenty of replies from the group. However I was looking for something which is very easy to understand and implement and thinking so I built one date dimension. Note that in my post I have not kept attributes for fiscal year but if you need to, you would merely have to add bunch of attributes to store fiscal month, quarter etc.

I am straight away jumping into the script. We need 4 scripts as explained below -

1. Date dimension table. Name of the table is [OS_Date_Dimension].
2. Stored procedure to populate date dimension table.
3. Script to populate your fact with UNKNOWN time member if lookup does not find one key in the date dimension.
4. Call stored procedure to fill date table.

Date dimension table. Name of the table is [OS_Date_Dimension]

CREATE TABLE [dbo].[Os_Dim_Date](
    --Keys
    [DateKey] [int] PRIMARY KEY CLUSTERED NOT NULL,    --20110101
    [AlternateDateKey] [nvarchar](10) NOT NULL,        --2001-07-01
    [CalenderDate] [datetime] NOT NULL,                --2000-01-01 00:00:00.000
    
    --Day
    [DayOfWeek] [nvarchar](9) NOT NULL,                --Sunday
    [ShortDayOfWeek] [nvarchar](3) NOT NULL,        --Sun
    [DayNumOfYear] [int] NOT NULL,                    --1, 2, 3..365
    [DayNumOfQuarter] [int] NOT NULL,                --1, 2, 3..90
    [DayNumOfMonth] [int] NOT NULL,                    --1, 2, 3..31
    [DayNumOfWeek] [int] NOT NULL,                    --1, 2, 3..7
    
    --Week
    [WeekName] [nvarchar](20) NOT NULL,                --2000-Week-1
    [WeekNumOfYear] [int] NOT NULL,                    --1, 2, 3..49
    [WeekNumOfQuarter] [int] NOT NULL,                --1, 2, 3..14
    [WeekNumOfMonth] [int] NOT NULL,                --1, 2, 3..6
    
    --Month
    [MonthName] [nvarchar](20) NOT NULL,            --January 2001
    [MonthShortName] [nvarchar](6) NOT NULL,        --Jan
    [MonthNumOfYear] [int] NOT NULL,                --1, 2..12
    [MonthNumOfQuarter] [int] NOT NULL,                --1, 2 and 3
    
    --Quarter
    [QuarterName] [nvarchar](10) NOT NULL,            --2000 Q1
    [QuarterShortName] [nvarchar](5) NOT NULL,        --Q1
    [Quarter] [int] NOT NULL,                        --1,2,3 and 4
        
    --Year
    [YearName] [nvarchar](7) NOT NULL,                --2011
    [Year] [int] NOT NULL,                            --2011
        
    --Additional
    [SemiMonth] [nvarchar](50) NULL,                --SemiMonthP1 January 2011
    [Semester] [int] NOT NULL,                        --1 and 2
    [SemesterName] [nvarchar](11) NOT NULL            --H1 CY 2001
)
GO

Stored procedure to populate date dimension table

--Stored procedure to create date dimension table
CREATE PROCEDURE [dbo].[Os_Populate_Date_Dim]
(
  @FIRST_DATE       DATETIME,
  @LAST_DATE        DATETIME
)
AS
BEGIN
    WITH TE_Os_Dim_Date as 
    ( 
    SELECT Cast (@FIRST_DATE as DateTime) Date        --Start Date 
    UNION ALL 
    SELECT Date + 1 FROM TE_Os_Dim_Date 
        WHERE Date + 1 < = @LAST_DATE                --End date 
    ) 
    INSERT INTO [dbo].[Os_Dim_Date] (
         [DateKey]
        ,[AlternateDateKey]
        ,[CalenderDate]
        ,[DayOfWeek]
        ,[ShortDayOfWeek]
        ,[DayNumOfYear]
        ,[DayNumOfQuarter]
        ,[DayNumOfMonth]
        ,[DayNumOfWeek]
        ,[WeekName]
        ,[WeekNumOfYear]
        ,[WeekNumOfQuarter]
        ,[WeekNumOfMonth]
        ,[MonthName]
        ,[MonthShortName]
        ,[MonthNumOfYear]
        ,[MonthNumOfQuarter]
        ,[QuarterName]
        ,[QuarterShortName]
        ,[Quarter]
        ,[YearName]
        ,[Year]
        ,[SemiMonth]
        ,[Semester]
        ,[SemesterName]
    )
    SELECT 
        CAST(CONVERT(NVARCHAR(30),DATE, 112) AS INT) AS DateKey
        ,LEFT(CAST(CONVERT(NVARCHAR(30),DATE, 120) AS VARCHAR), 10) AS AlternateDateKey
        ,DATE AS CalenderDate
        
        ,DateName (dw, date) as DayOfWeek
        ,LEFT (DateName (dw, date), 3) as ShortDayOfWeek
        ,DATEPART (DY, DATE) AS DayNumOfYear 
        ,DATEDIFF(DD,DATEADD(QQ,DATEDIFF(QQ,0,DATE),0),DATE)+1 AS DayNumOfQuarter 
        ,DAY (DATE) AS DayNumOfMonth 
        ,DATEPART (DW, DATE) AS DayNumOfWeek 
        
        ,'Week ' + CAST(DatePart (wk, Date) AS VARCHAR) + ' CY ' + convert(varchar(4),YEAR(Date)) AS WeekName
        ,DatePart (wk, Date) as WeekNumOfYear 
        ,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as WeekNumOfQuarter 
        ,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as WeekNumOfMonth 

        ,DateName (mm, date) + ' ' + datename(year,Date) as MonthName 
        ,LEFT ( DateName (mm, date), 3) MonthShortName 
        ,MONTH (date) as MonthNumOfYear 
        ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as MonthNumOfQuarter 
        
        ,'Q' + datename(quarter,Date) + ' CY ' + datename(year,Date) AS QuarterName
        ,'Q'+ datename(quarter,Date) AS QuarterShortName
        ,DatePart ( qq, date) as Quarter 
        
        ,datename(year,Date) AS YearName
        ,YEAR (date) as Year 
        
        ,'SemiMonthP' +  CASE WHEN Day(Date) <= 15 then '1' ELSE '2' END + ' ' +  DateName (mm, date) + ' ' +  datename(year,Date) AS SemiMonth
        ,CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS Semester
        ,'H' + CAST(CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS VARCHAR) + ' CY ' +  datename(year,Date) AS [SemesterName]
    FROM TE_Os_Dim_Date 
    
    OPTION (MAXRECURSION 0)
END
GO

Script to populate your fact with UNKNOWN time member if lookup does not find one key in the date dimension

--Insert a record for unknown lookup
INSERT INTO [dbo].[Os_Dim_Date] (
         [DateKey]
        ,[AlternateDateKey]
        ,[CalenderDate]
        ,[DayOfWeek]
        ,[ShortDayOfWeek]
        ,[DayNumOfYear]
        ,[DayNumOfQuarter]
        ,[DayNumOfMonth]
        ,[DayNumOfWeek]
        ,[WeekName]
        ,[WeekNumOfYear]
        ,[WeekNumOfQuarter]
        ,[WeekNumOfMonth]
        ,[MonthName]
        ,[MonthShortName]
        ,[MonthNumOfYear]
        ,[MonthNumOfQuarter]
        ,[QuarterName]
        ,[QuarterShortName]
        ,[Quarter]
        ,[YearName]
        ,[Year]
        ,[SemiMonth]
        ,[Semester]
        ,[SemesterName]
    )
VALUES
    (
         19000101
        ,'1900-01-01' 
        ,'1900-01-01 00:00:00'
        ,'Unknown' ,'Unk', 1, 1 ,1 ,1
        ,'Unknown', 1 ,1 ,1
        ,'Unknown', 'Unk' ,1 ,1
        ,'Unkown', 'Q1', 1
        ,'1900', 1900
        ,'Unknown', 1
        ,'Unknown'
    )
GO

Call stored procedure to fill date table

--Populate Date from 2001 to 2020
EXEC [dbo].[Os_Populate_Date_Dim] '2001-01-01', '2020-12-31'
GO

Now let’s setup dimension attribute key, name and hierarchies in the cube. Your Dimension structure (Hierarchies) and attributes relationship should look like this at the end of the exercise.

Set attribute key and names as illustrated below -

Attribute Name	KeyColumns Property	NameColumn Property
DateKey	Os_Dim_Date.DateKey (Integer)	Os_Dim_Date.CalenderDate (WChar) ValueColumn: Os_Dim_Date.CalenderDate (WChar)
Month Name	Os_Dim_Date.Year (Integer) Os_Dim_Date.MonthNumOfYear (Integer)	Os_Dim_Date.MonthName (WChar)
Quarter	Os_Dim_Date.Year (Integer) Os_Dim_Date.Quarter (Integer)	Os_Dim_Date.QuarterName (WChar)
Year	Os_Dim_Date.Year (Integer)	Os_Dim_Date.YearName (WChar)
Week Name	Os_Dim_Date.MonthName (WChar) Os_Dim_Date.WeekNumOfMonth (Integer)	Os_Dim_Date.WeekName (WChar)

Next process dimension and browse Date and Week Hier. Find below the illustrations.

You may need more attributes related to fiscal year, holidays etc. which you should be able to add easily by extending table and stored procedure. Hope this helps.