Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Creating Date dimension from scratch in Microsoft SSAS

Rate me:
Please Sign up or sign in to vote.
4.56/5 (5 votes)
12 Dec 2015CPOL9 min read 37.1K   949   10   3
Script to generate DimDate for specified date range

Introduction

In this article you will learn how to:

  • Create DimDate table for Calendar and Fiscal dates
  • Populate DimDate table
  • Create and configure Date dimension in SSAS

Time. Google's definition of it is "The indefinite continued progress of existence and events in the past, present, and future regarded as a whole". Time is very important aspect in human life as people usually analyze their success / failure based on time: "How many kilos I lost last month?". "How much money I earned in third quarter?", etc. This is the reason why 99.9% of Data Warehouses have Date (Time) dimension which enables analysis by periods of time. Actually, Date (Time) is so important and so frequent in data warehouses that Microsoft created special set of functions specific for time analysis.

In this article I will explain how to create Date dimension from scratch - from creating DimDate table through populating it and finally creating Date (Time) dimension in Microsoft SQL Server Analysis Services (SSAS). Microsoft SSAS has an in-built Wizard which can create Date (Time) dimension for us. It can either create dimension in SSAS only or it can create underlying table in SQL Server as well, depends on our preference. In this article I won't use this handy Wizard, but instead I will do all manually which is my preferred way because it gives me much more flexibility. 

DimDate Table

There are many ways you could design DimDate table, but in this article I will create table which perfectly fits Microsoft SSAS based on Kimball Methodology. Microsoft SSAS has predefined set of attribute types for several "special" dimensions and one of these special dimensions is Date (Time). In this article I will cover only Calendar (Fiscal in next version) groups of data types:

Image 1      Image 2

Knowing all of this, we can now create and populate DimDate table.

Create DimDate table

For the purpose of creating DimDate table use T-SQL script called 'DimDateCreate.sql' attached to this article. Firstly, script will check if DimDate table already exists and if it does not, new DimDate table will be created:

SQL
CREATE TABLE [dbo].[DimDate] (
    [Date] [int] NOT NULL
    ,[FullDateAlternateKey] [date] NOT NULL
    ,[DateEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- D
    ,[DayOfWeek] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[DayOfMonth] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfMonthEnglishName] [varchar](20) DEFAULT ('Unknown')
    ,[DayOfQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[DayOfTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[DayOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[DayOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[DayOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[Weekday] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- W
    ,[Week] [int] NOT NULL DEFAULT (-1)
    ,[WeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[WeekOfYear] [int] NOT NULL DEFAULT (-1)
    ,[WeekOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- t
    ,[TenDays] [int] NOT NULL DEFAULT (-1)
    ,[TenDaysEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TenDaysOfMonth] [smallint] NOT NULL DEFAULT (-1)
    ,[TenDaysOfMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TenDaysOfQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[TenDaysOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TenDaysOfTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[TenDaysOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TenDaysOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[TenDaysOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TenDaysOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[TenDaysOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    --M
    ,[Month] [int] NOT NULL DEFAULT (-1)
    ,[MonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[MonthOfQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[MonthOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[MonthOfTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[MonthOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[MonthOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[MonthOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[MonthOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[MonthOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- Q
    ,[Quarter] [smallint] NOT NULL DEFAULT (-1)
    ,[QuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[QuarterOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[QuarterOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[QuarterOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[QuarterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- T
    ,[Trimester] [smallint] NOT NULL DEFAULT (-1)
    ,[TrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[TrimesterOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[TrimesterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- H
    ,[HalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[HalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[HalfYearOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[HalfYearOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- Y
    ,[Year] [smallint] NOT NULL DEFAULT (-1)
    ,[YearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FD
    ,[FiscalDay] [int] NOT NULL DEFAULT (-1)
    ,[FiscalDayEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalDayOfWeek] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalDayOfMonth] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfMonthEnglishName] [varchar](20) DEFAULT ('Unknown')
    ,[FiscalDayOfQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalDayOfTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalDayOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalDayOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalDayOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FW
    ,[FiscalWeek] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalWeekOfMonth] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekOfMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalWeekOfQuarter] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalWeekOfTrimester] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalWeekOfHalfYear] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalWeekOfYear] [int] NOT NULL DEFAULT (-1)
    ,[FiscalWeekOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    --FM
    ,[FiscalMonth] [int] NOT NULL DEFAULT (-1)
    ,[FiscalMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalMonthOfQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalMonthOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalMonthOfTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalMonthOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalMonthOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalMonthOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalMonthOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalMonthOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FQ
    ,[FiscalQuarter] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalQuarterOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalQuarterOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalQuarterOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalQuarterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FT
    ,[FiscalTrimester] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalTrimesterOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalTrimesterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FH
    ,[FiscalHalfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    ,[FiscalHalfYearOfYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalHalfYearOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- FY
    ,[FiscalYear] [smallint] NOT NULL DEFAULT (-1)
    ,[FiscalYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
    -- Primary Key
    ,CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ([Date] ASC)
)   

On the other hand, if DimDate already exists script will raise an error and stop execution. Then it is up to you to solve this in two possible ways:

  1. By deleting existing DimDate table and rerunning the script, or
  2. By modifying existing DimDate table so it's structure matches the one in the script

Keep in mind that security (if any) will have to be reapplied to database object if it is dropped and recreated.

Populate DimDate table

After DimDate table has been prepared use second script called 'DimDatePopulate.sql' to populate the table with desired range of data. Before running the script make sure to check parameters at the beginning of the script. These parameters should be adopted as per your business' needs (range of dates to be generated, first day of the week, etc.). Following script represent example of correctly set parameters:

SQL
declare @startDate date = '20100101' 
declare @endDate date = '20191231' 
declare @firstDayOfWeek tinyint = 1 
declare @fiscalDay tinyint = 21 
declare @fiscalMonth tinyint = 5 
declare @fiscalYearPlus1 tinyint = 1

Setting Parameters

@startDate

First date which will appear in Date dimension. In our case first date in dimension will be 1 January 2010.

Possible values: Any valid date smaller than date defined in @endDate.

@endDate

Last date which will appear in Date dimension. In our case last date in dimension will be 31 December 2019.

Possible values: Any valid date larger than date defined in @startDate.

@firstDayOfWeek 

This parameter defines first day of the week. Default SQL Server setting for English (US) settings is 7 (Sunday), but you can adopt it based on your preferences: 1 = Monday, 2 = Tuesday, ... , 7 = Sunday.

Possible values: 1, 2,..., 7.

@fiscalDay

First day of fiscal year. If you fiscal year starts on 1st July, use 1 as a parameter. In our case, fiscal year starts on 21st May so we used 21 as a parameter.

Possible values: 1, 2,..., 28. (You should not use 29, 30 and 31 as you can experience unexpected results).

@fiscalMonth

First month of fiscal year. If you fiscal year starts on 1st July, use 7 as parameter. In our case, fiscal year starts on 21st May so we used 5 as a parameter.

Possible values: 1, 2,..., 12.

@fiscalYearPlus1

Determines if fiscal year will be calendar year + 1 or equal to calendar year. For example, if fiscal year starts on 1st July, calendar year is 2015 and this parameter is 1, then from 1 July 2015 until 30 June 2016 (of calendar date), fiscal year will be 2016:

Calendar Date Fiscal Year when param. is 0 Fiscal Year when param. is 1
... ... ...
29 June 2015 2014 2015
30 June 2015 2014 2015
1 July 2015 2015 2016
2 July 2015 2015 2016
... ... ...

Possible values: 0 and 1.

Running the Script

After parameters are set, you can run the script on the same database where you previously created DimDate table. Firstly, script will collect information about table constraints (foreign keys) for DimDate (if any). Then, it will store this constraints internally and drop them from the database making sure that we can delete all records from the table without "breaking" any referential integrity. Following, script will delete all records from the table and prepare it for new set of data.

After that, script will use Tally Table technique to generate desired range of dates which are used in various transformations necessary to calculate values for each column. Example of few calculations for Day columns are:

SQL
,convert(smallint, datepart(weekday, d)) as [DayOfWeek]
,convert(smallint, datepart(day, d)) as [DayOfMonth]
,convert(smallint, datediff(day, dateadd(quarter, datediff(quarter, 0, d), 0),d) + 1) as [DayOfQuarter]
,convert(smallint, datediff(day, dateadd(month, datediff(month, 0, d) - datediff(month, 0, d) % 4, 0), d) + 1) as [DayOfTrimester]
,convert(smallint, datediff(day, dateadd(month, datediff(month, 0, d) - datediff(month, 0, d) % 6, 0), d) + 1) as [DayOfHalfYear]
,convert(smallint, datepart(dayofyear, d)) as [DayOfYear]
,isnull(convert(varchar(20), case when datename(weekday, d) in ('Saturday', 'Sunday') then 'Weekend' else 'Weekday' end), 0) as [Weekday]

...month:

SQL
,convert(int, datepart(year, d) * 100 + datepart(month, d)) as [Month]
,convert(smallint, (datepart(month, d) - 1) % 3 + 1) as [MonthOfQuarter]
,convert(smallint, (datepart(month, d) - 1) % 4 + 1) as [MonthOfTrimester]
,convert(smallint, (datepart(month, d) - 1) % 6 + 1) as [MonthOfHalfYear]
,convert(smallint, datepart(month, d)) as [MonthOfYear]

... and trimester:

SQL
,convert(smallint, datepart(year, d) * 10 + ((datepart(month, d) - 1) / 4 + 1)) as [Trimester]
,convert(smallint, (datepart(month, d) - 1) / 4 + 1) as [TrimesterOfYear]

Finally, script will recreate all constraints collected at the beginning of the script.

Because script is regular T-SQL you can easily adopt it to suit your and your business' needs. Also, if your Data Warehouse does not require certain levels (Trimester, Half Year,...) feel free to remove those levels by deleting correspondent numeric and textual columns from the DimDate table.

Script will create continuous range of dates (all dates) between input parameters you provided. Do not be tempted to delete rows of data (dates) even if you know you are never going to use it! For example, if you know your source system will never have any data for weekends, you could be tempted to delete Saturdays and Sundays from DimDate table, but that is not good practice as MDX's functions expect complete date dimension to be able to return correct data.

Let's say your BI system does not have any data for month July of 2014 and you decide to delete all dates in DimDate for July 2014. Then, imagine you run the query to see sales for third quarter (July, August, September) of 2015 and you would like to compare it with sales for third quarter of 2014. Usually you would use ParallelPeriod for that purpose. Unfortunately, ParallelPeriod as well as other MDX functions are "calendar unaware", so if you run earlier mentioned ParallelPeriod MDX query expecting to get sum of all sales for July, August and September of 2014 - you would actually get sales for June, August and September!

Point of this story is - do not delete any dates (rows) from DimDate table!!!

Create Date dimension in SSAS

I will assume you are familiar how to create SSAS project in BIDS / SSDT, so I would expect you to know how to create Data Source and Data Source View.

Start New Dimension Wizard and leave default selection 'Use an existing table' on the first screen:

Image 3

In following screen, select your data source view and select DimDate as Main table. Optionally, you can select textual representation (Name Column) of key column at this point or you could do it later. In our case, I already set it to EnglishDateName:

Image 4

In following page of the Wizard you need to select attributes you would like to use in your dimension. Make sure to select only numeric attributes (not attributes that start with 'English') and choose appropriate Attribute Type for each selected column:

Image 5

Column names in DimDate are created in a way that matches defined SSAS attribute types, so it should be really easy for you to locate and assign appropriate attribute type for each selected dimension attribute. In this example, I selected following attributes and attribute types:

Attribute Name Attribute Type
Date Date
Day of Week Regular
Week Week
Ten Days Ten Days
Month Month
Month of Year Regular
Quarter Quarter
Trimester Trimester
Half Year Half Year
Year Year

Once you are done with selecting attributes and assigning attribute types, your Wizard should look like this:

Image 6

Finally, complete wizard by giving a name to your dimension:

Image 7

After dimension is being created, you will be presented with Dimension Designer where in Dimension Structure tab you will be able to see attributes you previously selected in a wizard:

Image 8

If you select dimension (like i did in picture above) and go to object properties, you will notice that Date dimension type is et to Time:

Image 9

This is very important as SSAS engine will be able to apply and use certain optimizations specific for Date (Time) operations.

Furthermore, make sure to assign textual representation (Name Column) for each of the attributes in dimension by clicking at the attribute in Dimension Designer, going to Properties pane and assigning textual column from DimDate table to Name Column property of the attribute. For example, for Year dimension attribute you should assign it's textual representation from DimDate table as shown in picture below:

Image 10

List of all Name Columns assigned to attributes can be found below:

Name Name Column
Date EnglishDateName
Day Of Week EnglishDayOfWeekName
Week EnglishWeekName
Ten Days EnglishTenDaysName
Month EnglishMonthName
Month of Year EnglishMonthOfYearName
Quarter EnglishQuarterName
Trimester EnglishTrimesterName
Half Year EnglishHalfYearName
Year EnglishYearName

It is always good practice to create natural hierarchies in any dimension and Date (Time) dimension is no exception. Initially, you can create all possible calendar hierarchies and let users decide which ones they would like to keep and then delete rest of them:

Image 11

As you can see in picture above, all user hierarchies have "blue wavy line" below it's name. This is because SSAS is warning us that relationships between attributes have not been created. In certain cases, we are not able to create relationships between attributes in hierarchy and those types of hierarchies are known as Un-natural hierarchies. In case of Date (Time) dimension we are currently creating, that is not the case as we know that days "naturally" fit into month, months "naturally" fit into quarter, quarters into half year, half years into year, quarters into year, etc. These hierarchies are known as Natural Hierarchies.

Attribute Relationships are really important aspect in dimensional design and should be defined whenever possible. There are two types of attribute relationships: Flexible and Rigid. Flexible relationship is default setting and essentially it means that "child" attribute can change "parent". In case of Date (Time) dimension that is not the case as January will always belong to 1st quarter, 1st trimester and 1st Half Year. Thus, we can set attribute relationships as Rigid.

Image 12

This is it. Save, deploy and process your Date dimension and you are ready to go!

History

Dec 2015 - Initial version without

Dec 2015 - Added Fiscal Calendar, added "sanity check" control in the script, added test at the end of the populate script

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Australia Australia
Business Intelligence Professional passionate about turning data into meaningful information and helping business grow and make better "data-driven" decisions.

Comments and Discussions

 
QuestionError in [DayOfQuarter] and [DayOfTrimester] Pin
aCaps28-Mar-18 12:40
aCaps28-Mar-18 12:40 
AnswerRe: Error in [DayOfQuarter] and [DayOfTrimester] Pin
Miljan Radovic19-Jun-20 11:26
Miljan Radovic19-Jun-20 11:26 
PraiseThank you this helped a lot. Pin
allanmunroza26-Jul-17 22:19
allanmunroza26-Jul-17 22:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.