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:
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:
CREATE TABLE [dbo].[DimDate] (
[Date] [int] NOT NULL
,[FullDateAlternateKey] [date] NOT NULL
,[DateEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[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')
,[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')
,[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')
,[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')
,[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')
,[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')
,[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')
,[Year] [smallint] NOT NULL DEFAULT (-1)
,[YearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[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')
,[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')
,[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')
,[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')
,[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')
,[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')
,[FiscalYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,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:
- By deleting existing DimDate table and rerunning the script, or
- 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:
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:
,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:
,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:
,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:
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:
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:
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:
Finally, complete wizard by giving a name to your dimension:
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:
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:
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:
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:
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.
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
