Date Dimension can be found in every Data Warehouse, because Date dimension is one of the most important factors of each operational system that users like to view reports based on date dimension.
So the importance and requirement to a date dimension persuade me to write a t-sql script to generate records for date dimension. This script will consider fiscal year and weekend and annual public holidays as well.
Date dimension consists of some static members; each member is a date with attributes like year, month, week, fiscal year….
So loading data in Date dimension happens only once you create the data warehouse, and it shouldn’t be scheduled like other ETL jobs from source system.
This script only works with Microsoft SQL Server
Script below is for creating the DimDate:
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[DateFullName] [varchar](50) NULL,
[DateFull] [date] NULL,
[Year] [int] NULL,
[Quarter] [int] NULL,
[QuarterName] [varchar](50) NULL,
[QuarterKey] [int] NULL,
[Month] [int] NULL,
[MonthKey] [int] NULL,
[MonthName] [varchar](50) NULL,
[DayOfMonth] [int] NULL,
[NumberOfDaysInTheMonth] [int] NULL,
[DayOfYear] [int] NULL,
[WeekOfYear] [int] NULL,
[WeekOfYearKey] [int] NULL,
[ISOWeek] [int] NULL,
[ISOWeekKey] [int] NULL,
[WeekDay] [int] NULL,
[WeekDayName] [varchar](50) NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [int] NULL,
[FiscalQuarterKey] [int] NULL,
[FiscalMonth] [int] NULL,
[FiscalMonthKey] [int] NULL,
[IsWorkDayKey] [int] NULL,
[IsWorkDayDescription] [varchar](50) NULL,
[IsPublicHolidayKey] [int] NULL,
[IsPublicHolidayDescription] [varchar](50) NULL,
CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And this is an illustration of our Date dimension columns
and below you will find the script for generating records (members) and inserting them into DimDate.
The script Includes;
Fiscal Attributes
Note that in this date dimension FISCAL attributes like Fiscal Year, Fiscal Month, Fiscal Quarter also exists.
Workday and Weekends
Weekends are different in some countries, so I’ve created a table for weeklyHolidays and you can insert weekend days there before running the script
declare @WeeklyHolidays table ([WeekDay] int) — weekday, sunday is 1 and saturday is 7
— insert weekly holidays
insert into @WeeklyHolidays([WeekDay]) values(1) — sunday
insert into @WeeklyHolidays([WeekDay]) values(7) — saturday
Annual Public Holidays
As annual public holidays are different in each country and also different based on each year, so I’ve created a table variable in script and you can add annual holidays as you want to the script before running it.
declare @AnnulPublicHolidays table([Date] int) — int in YYYYMMDD format
— insert annual public holidays
insert into @AnnulPublicHolidays([Date]) values(20130101) — New Year’s Day
insert into @AnnulPublicHolidays([Date]) values(20130102) — Day after New Year’s Day
Keys and Names for all attributes
This script generated unique key for each attribute (this is useful when you want to use it in SSAS Cube), and also creates Name column for each attribute.
And here you can find the whole script for populating and generating records for date dimension:
declare @CurrentDate date
declare @FiscalYearStartMonth int
declare @WeeklyHolidays table ([WeekDay] int) — weekday, sunday is 1
and saturday is 7
declare @AnnulPublicHolidays table([Date] int) — int in YYYYMMDD format
declare @FirstDate date
declare @NumberOfYearsToGenerate int
declare @LastDate date
set @FirstDate=‘1990-01-01’
set @NumberOfYearsToGenerate=40
— do not change line below
set @LastDate=DATEADD(YEAR,@NumberOfYearsToGenerate,@FirstDate)
set @CurrentDate=@FirstDate
set @FiscalYearStartMonth=7
— insert weekly holidays
insert into @WeeklyHolidays([WeekDay]) values(1) — sunday
insert into @WeeklyHolidays([WeekDay]) values(7) — saturday
— insert annual public holidays
insert into @AnnulPublicHolidays([Date]) values(20130101) — New Year’s Day
insert into @AnnulPublicHolidays([Date]) values(20130102) — Day after New Year’s
Day
insert into @AnnulPublicHolidays([Date]) values(20130206) — Waitangi Day
insert into @AnnulPublicHolidays([Date]) values(20130329) — Good Friday
insert into @AnnulPublicHolidays([Date]) values(20130401) — Easter Monday
insert into @AnnulPublicHolidays([Date]) values(20130425) — ANZAC Day
insert into @AnnulPublicHolidays([Date]) values(20130603) — Queen’s Birthday
insert into @AnnulPublicHolidays([Date]) values(20131028) — Labour Day
insert into @AnnulPublicHolidays([Date]) values(20131225) — Christmas Day
insert into @AnnulPublicHolidays([Date]) values(20131226) — Boxing Day
while(@CurrentDate<@LastDate)
begin
INSERT INTO [dbo].[DimDate]
([DateKey]
,[DateFullName]
,[DateFull]
,[Year]
,[Quarter]
,[QuarterName]
,[QuarterKey]
,[Month]
,[MonthKey]
,[MonthName]
,[DayOfMonth]
,[NumberOfDaysInTheMonth]
,[DayOfYear]
,[WeekOfYear]
,[WeekOfYearKey]
,[ISOWeek]
,[ISOWeekKey]
,[WeekDay]
,[WeekDayName]
,[FiscalYear]
,[FiscalQuarter]
,[FiscalQuarterKey]
,[FiscalMonth]
,[FiscalMonthKey]
,[IsWorkDayKey]
,[IsWorkDayDescription]
,[IsPublicHolidayKey]
,[IsPublicHolidayDescription])
select
convert(int,convert(varchar(8),@CurrentDate,112)) as [DateKey],
convert(varchar(max),@CurrentDate,106) as [DateFullName],
@CurrentDate as [DateFull],
datepart(year,@CurrentDate) as [Year],
datepart(QUARTER,@CurrentDate) as [Quarter],
‘QTR ‘+datename(QUARTER,@CurrentDate) as [QuarterName],
convert(int,datename(year,@CurrentDate)+datename(QUARTER,@CurrentDate)) as [QuarterKey],
datepart(month,@CurrentDate) as [Month],
convert(int,datename(year,@CurrentDate)+right(‘0’+convert(varchar(2),datepart(month,@CurrentDate)),2)) as [MonthKey],
datename(month,@CurrentDate) as [MonthName],
datepart(day,@CurrentDate) as [DayOfMonth],
datepart(day,EOMONTH(@CurrentDate)) as [NumberOfDaysInTheMonth],
datepart(DAYOFYEAR,@CurrentDate) as [DayOfYear],
datepart(WEEK,@CurrentDate) as [WeekOfYear],
datename(year,@CurrentDate)+right(‘0’+datename(week,@CurrentDate),2) as [WeekOfYearKey],
datepart(ISO_WEEK,@CurrentDate) as [ISOWeek],
datename(year,@CurrentDate)+right(‘0’+convert(varchar(2),datepart(ISO_WEEK,@CurrentDate)),2) as [ISOWeekKey],
datepart(WEEKDAY,@CurrentDate) as [WeekDay],
datename(WEEKDAY,@CurrentDate) as [WeekDayName],
case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end as [FiscalYear],
ceiling(convert(float,(case when month(@CurrentDate)=13–@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth–1)+month(@CurrentDate))%12 end))/3) as [FiscalQuarter],
convert(varchar(4),case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end)
+
convert(varchar(1),ceiling(convert(float,(case when month(@CurrentDate)=13–@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth–1)+month(@CurrentDate))%12 end))/3))
as [FiscalQuarterKey],
case when month(@CurrentDate)=13–@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth–1)+month(@CurrentDate))%12 end as [FiscalMonth],
convert(varchar(4),case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end)
+
right(‘0’+convert(varchar(2),case when month(@CurrentDate)=13–@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth–1)+month(@CurrentDate))%12 end),2)
as [FiscalMonthKey],
case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 1 else 0 end as [IsWorkDayKey],
case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then ‘Weekend’ else ‘Workday’ end as [IsWorkDayDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AnnulPublicHolidays) then 1 else 0 end as [IsPublicHolidayKey],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AnnulPublicHolidays) then ‘Holiday’ else ‘Non Holiday’ end as [IsPublicHolidayDescription]
set @CurrentDate=dateadd(day,1,@CurrentDate)
end
You can also download the script from here.
Notes to consider before running the script:
- 1- Create the date dimension
first with the first script in this post - 2- Set first date that you
want to load the date dimension members from that date (@FirstDate variable) - 3- Set number of years that
you want to generate date dimension members (@NumberOfYearsToGenerate variable) - 4- Set fiscal year start month
(@FiscalYearStartMonth variable) - 5- insert weekends (into
@WeeklyHolidays table variable) - 6- insert annual public
holidays (into @AnnualPublicHolidays table variable) - 7- Run the script
Is that too much?! Don’t worry you can run the script as is,
But, if you run the script as is;
It will load date dimension with data from 1st of
January 1990 to 40 years after (means 31st Dec 2029), and it will
consider Sunday and Saturday as weekend, and it will consider New Zealand
annual public holidays for only 2013 (as it set already) as public holidays. And it will consider July
as the first month of Fiscal Calendar.
Here is a sample of data rows generated by query (as number
of attributes doesn’t fit in width of this page it splatted into 3 images)
Image 1:
Image 2:
Image 3:
Hope it helps you all.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.