Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years

Posted on by Reza Rad

Almost a year ago I published first version of T-SQL script to generate and populate a general purpose date dimension here.

Today I want to publish the second version of that date dimension which has some corrections (fiscal calendar calculation fixed), and additional attributes (such as current year, current month, and current day)

Multiple countries with different Holidays supported, this script only is for New Zealand and Australia, other countries can be add in similar way. 

The most important advantage of this version 2 is that I’ve added multiple financial calendars to the date dimension. This script only generates and populates financial years of New Zealand and Australia, but you will understand how to add extra financial years. for each different financial year a new set of attributes will be added to this date dimension. based on this Wikipedia page (list of financial years) there won’t be that much different financial years, so we are not going to add hundreds of attributes for having multiple financial years. below script generates 8 attribute for each different financial year.

Here is the script to create/generate date dimension structure:

CREATE TABLE [dbo].[DimDate](

[DateKey] [int] NOT NULL,

[DateFullName] [varchar](50) NULL,

[FullDateAlternateKey] [date] NULL,

[YearCode] [int] NULL,

[YearDescription] [varchar](50) NULL,

[QuarterCode] [int] NULL,

[QuarterDescription] [varchar](50) NULL,

[QuarterYearCode] [int] NULL,

[MonthNumberOfYearCode] [int] NULL,

[MonthYearCode] [int] NULL,

[MonthName] [varchar](50) NULL,

[MonthYearDescription] [varchar](50) NULL,

[MonthLocalizedString] [varchar](50) NULL,

[DateLocalizedString] [varchar](50) NULL,

[DayNumberOfMonth] [int] NULL,

[NumberOfDaysInTheMonth] [int] NULL,

[DayNumberOfYear] [int] NULL,

[WeekNumberOfYear] [int] NULL,

[WeekYearCode] [int] NULL,

[ISOWeekNumberOfYear] [int] NULL,

[ISOWeekYearCode] [int] NULL,

[WeekDay] [int] NULL,

[WeekDayName] [varchar](50) NULL,

[NZFiscalYearCode] [int] NULL,

[NZFiscalYearDescription] [varchar](50) NULL,

[NZFiscalQuarterCode] [int] NULL,

[NZFiscalQuarterYearCode] [int] NULL,

[NZFiscalQuarterDescription] [varchar](50) NULL,

[NZFiscalMonthCode] [int] NULL,

[NZFiscalMonthYearCode] [int] NULL,

[NZFiscalMonthDescription] [varchar](50) NULL,

[AUFiscalYearCode] [int] NULL,

[AUFiscalYearDescription] [varchar](50) NULL,

[AUFiscalQuarterCode] [int] NULL,

[AUFiscalQuarterYearCode] [int] NULL,

[AUFiscalQuarterDescription] [varchar](50) NULL,

[AUFiscalMonthCode] [int] NULL,

[AUFiscalMonthYearCode] [int] NULL,

[AUFiscalMonthDescription] [varchar](50) NULL,

[IsWeekDayCode] [int] NULL,

[IsWeekDayDescription] [varchar](50) NULL,

[IsNZPublicHolidayCode] [int] NULL,

[IsNZPublicHolidayDescription] [varchar](50) NULL,

[NZPublicHolidayFullDescription] [varchar](50) NULL,

[IsAUPublicHolidayCode] [int] NULL,

[IsAUPublicHolidayDescription] [varchar](50) NULL,

[AUPublicHolidayFullDescription] [varchar](50) NULL,

[CurrentYear] [int] NULL,

[CurrentMonth] [int] NULL,

[CurrentDay] [int] 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]

GO

This is a screenshot of columns available for the Date dimension:

Populate Dimension 

below is description about populating Date dimension. the basic description about attributes in this dimension can be read in first version of date dimension script here:

http://www.rad.pasfu.com/index.php?/archives/95-Script-for-Creating-and-Generating-members-for-Date-Dimensions-General-Purpose.html

Financial Year Start 

Because we are storing multiple financial years, so we store multiple financial year starts. for each financial year, there is a variable in below script which you can set starting month of the financial year there.

Current Year, Month, Day

These attributes will show difference between the day of record with the current calendar date. (Note that current year, month, and day should be updated each day). if you don’t need these attributes in your date dimension then you won’t require to update date dimension each day.

Multiple countries Holiday

Because we are storing multiple country holidays, we are storing holidays in a table variable with their description. at this script we are storing these values hard coded. However, you can use Master Data Services or a reference table in your database for holding this information.

Here is the script for populating date dimension:

declare @CurrentDate date

declare @NZFiscalYearStartMonth int

declare @AUFiscalYearStartMonth int

declare @WeeklyHolidays table ([WeekDay] int) — weekday, sunday is 1
and saturday is 7

declare @NZAnnulPublicHolidays table([Date] int,[Description] varchar(50)) — int in YYYYMMDD
format

declare @AUAnnulPublicHolidays table([Date] int,[Description] varchar(50)) — int in YYYYMMDD
format

declare @FirstDate date

declare @NumberOfYearsToGenerate int

declare @LastDate date

declare @NZFiscalStartDateForCurrentYear date

declare @AUFiscalStartDateForCurrentYear date

set @FirstDate=‘1990-01-01’

set @NumberOfYearsToGenerate=40

— do not change line
below

set @LastDate=DATEADD(YEAR,@NumberOfYearsToGenerate,@FirstDate)

set @CurrentDate=@FirstDate

set @NZFiscalYearStartMonth=4

set @AUFiscalYearStartMonth=7

— insert weekly
holidays

insert into @WeeklyHolidays([WeekDay]) values(1) — sunday

insert into @WeeklyHolidays([WeekDay]) values(7) — saturday

— insert annual
public holidays

— 2013 NZ holidays

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130101,‘New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130102,‘Day after New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130206,‘Waitangi Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130329,‘Good Friday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130401,‘Easter Monday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130425,‘ANZAC Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130603,‘Queen”s Birthday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131028,‘Labour Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131225,‘Christmas Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131226,‘Boxing Day’)

— 2014 NZ holidays

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140101,‘New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140102,‘Day after New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140206,‘Waitangi Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140418,‘Good Friday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140421,‘Easter Monday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140425,‘ANZAC Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140602,‘Queen”s Birthday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141027,‘Labour Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141225,‘Christmas Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141226,‘Boxing Day’)

— 2015 NZ holidays

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150101,‘New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150102,‘Day after New Year”s Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150206,‘Waitangi Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150403,‘Good Friday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150406,‘Easter Monday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150427,‘ANZAC Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150601,‘Queen”s Birthday’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151026,‘Labour Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151225,‘Christmas Day’)

insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151228,‘Boxing Day’)

— 2013 AU ACT
holidays

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130101,‘New Year”s Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130128,‘Australia Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130311,‘Canberra Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130329,‘Good Friday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130330,‘Easter Saturday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130401,‘Easter Monday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130425,‘ANZAC Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130610,‘Queen”s Birthday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130930,‘Family & Community Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131007,‘Labour Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131225,‘Christmas Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131226,‘Boxing Day’)

— 2014 AU ACT
holidays

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140101,‘New Year”s Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140127,‘Australia Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140310,‘Canberra Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140418,‘Good Friday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140419,‘Easter Saturday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140421,‘Easter Monday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140425,‘ANZAC Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140609,‘Queen”s Birthday’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140929,‘Family & Community Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141006,‘Labour Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141225,‘Christmas Day’)

insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141226,‘Boxing Day’)

while(@CurrentDate<@LastDate)

begin

set @NZFiscalStartDateForCurrentYear=

case when datediff(day,convert(date,datename(year,@CurrentDate)+‘-‘+convert(varchar(2),@NZFiscalYearStartMonth)+‘-01’),@CurrentDate)>=0 then

convert(date,datename(year,@CurrentDate)+‘-‘+convert(varchar(2),@NZFiscalYearStartMonth)+‘-01’)

else

convert(date,datename(year,dateadd(year,-1,@CurrentDate))+‘-‘+convert(varchar(2),@NZFiscalYearStartMonth)+‘-01’)

end

set @AUFiscalStartDateForCurrentYear=

case when datediff(day,convert(date,datename(year,@CurrentDate)+‘-‘+convert(varchar(2),@AUFiscalYearStartMonth)+‘-01’),@CurrentDate)>=0 then

convert(date,datename(year,@CurrentDate)+‘-‘+convert(varchar(2),@AUFiscalYearStartMonth)+‘-01’)

else

convert(date,datename(year,dateadd(year,-1,@CurrentDate))+‘-‘+convert(varchar(2),@AUFiscalYearStartMonth)+‘-01’)

end

INSERT INTO [dbo].[DimDate]

([DateKey]

,[DateFullName]

,[FullDateAlternateKey]

,[YearCode]

,[YearDescription]

,[QuarterCode]

,[QuarterDescription]

,[QuarterYearCode]

,[MonthNumberOfYearCode]

,[MonthYearCode]

,[MonthName]

,[MonthYearDescription]

,[MonthLocalizedString]

,[DateLocalizedString]

,[DayNumberOfMonth]

,[NumberOfDaysInTheMonth]

,[DayNumberOfYear]

,[WeekNumberOfYear]

,[WeekYearCode]

,[ISOWeekNumberOfYear]

,[ISOWeekYearCode]

,[WeekDay]

,[WeekDayName]

,[NZFiscalYearCode]

,[NZFiscalYearDescription]

,[NZFiscalQuarterCode]

,[NZFiscalQuarterYearCode]

,[NZFiscalQuarterDescription]

,[NZFiscalMonthCode]

,[NZFiscalMonthYearCode]

,[NZFiscalMonthDescription]

,[AUFiscalYearCode]

,[AUFiscalYearDescription]

,[AUFiscalQuarterCode]

,[AUFiscalQuarterYearCode]

,[AUFiscalQuarterDescription]

,[AUFiscalMonthCode]

,[AUFiscalMonthYearCode]

,[AUFiscalMonthDescription]

,[IsWeekDayCode]

,[IsWeekDayDescription]

,[IsNZPublicHolidayCode]

,[IsNZPublicHolidayDescription]

,[NZPublicHolidayFullDescription]

,[IsAUPublicHolidayCode]

,[IsAUPublicHolidayDescription]

,[AUPublicHolidayFullDescription]

,[CurrentYear]

,[CurrentMonth]

,[CurrentDay])

select

convert(int,convert(varchar(8),@CurrentDate,112)) as [DateKey],

convert(varchar(max),@CurrentDate,106) as [DateFullName],

@CurrentDate as [FullDateAlternateKey],

datepart(year,@CurrentDate) as [YearCode],

‘CY ‘+datename(year,@CurrentDate) as [YearDescription],

datepart(QUARTER,@CurrentDate) as [QuarterCode],

‘QTR ‘+datename(QUARTER,@CurrentDate) as [QuarterDescription],

convert(int,datename(year,@CurrentDate)+datename(QUARTER,@CurrentDate)) as [QuarterYearCode],

datepart(month,@CurrentDate) as [MonthNumberOfYearCode],

convert(int,datename(year,@CurrentDate)+right(‘0’+convert(varchar(2),datepart(month,@CurrentDate)),2)) as [MonthYearcode],

datename(month,@CurrentDate) as [MonthName],

datename(month,@CurrentDate)+‘ ‘+datename(year,@CurrentDate) as [MonthYearDescription],

substring(

convert(varchar(max),@CurrentDate,103),

charindex(‘/’,convert(varchar(max),@CurrentDate,103),1)+1,

len(convert(varchar(max),@CurrentDate,103))-charindex(‘/’,convert(varchar(max),@CurrentDate,103),1)

) as [MonthLocalizedString],

convert(varchar(max),@CurrentDate,103) as [DateLocalizedString],

datepart(day,@CurrentDate) as [DayNumberOfMonth],

datepart(day,EOMONTH(@CurrentDate)) as [NumberOfDaysInTheMonth],

datepart(DAYOFYEAR,@CurrentDate) as [DayNumberOfYear],

datepart(WEEK,@CurrentDate) as [WeekNumberOfYear],

datename(year,@CurrentDate)+right(‘0’+datename(week,@CurrentDate),2) as [WeekYearCode],

datepart(ISO_WEEK,@CurrentDate) as [ISOWeekNumberOfYear],

datename(year,@CurrentDate)+right(‘0’+convert(varchar(2),datepart(ISO_WEEK,@CurrentDate)),2) as [ISOWeekYearCode],

datepart(WEEKDAY,@CurrentDate) as [WeekDay],

datename(WEEKDAY,@CurrentDate) as [WeekDayName],

year(@NZFiscalStartDateForCurrentYear)+1 as [NZFiscalYearCode],

‘FY ‘+convert(varchar(4),year(@NZFiscalStartDateForCurrentYear)+1) as [NZFiscalYearDescription],

datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1 as [NZFiscalQuarterCode],

((datepart(year,@NZFiscalStartDateForCurrentYear)+1)*10)+(datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalQuarterYearCode],

‘QTR ‘+convert(varchar(1),datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalQuarterDescription],

datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1 as [NZFiscalMonthCode],

((datepart(year,@NZFiscalStartDateForCurrentYear)+1)*100)+(datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalMonthYearCode],

‘Month ‘+convert(varchar(2),datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalMonthDescription],

year(@AUFiscalStartDateForCurrentYear)+1 as [AUFiscalYearCode],

‘FY ‘+convert(varchar(4),year(@AUFiscalStartDateForCurrentYear)+1) as [AUFiscalYearDescription],

datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1 as [AUFiscalQuarterCode],

((datepart(year,@AUFiscalStartDateForCurrentYear)+1)*10)+(datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalQuarterYearCode],

‘QTR ‘+convert(varchar(1),datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalQuarterDescription],

datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1 as [AUFiscalMonthCode],

((datepart(year,@AUFiscalStartDateForCurrentYear)+1)*100)+(datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalMonthYearCode],

‘Month ‘+convert(varchar(2),datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalMonthDescription],

case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 0 else 1 end as [IsWeekDayCode],

case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then ‘Weekend’ else ‘Weekday’ end as [IsWeekDayDescription],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then 1 else 0 end as [NZIsPublicHolidayCode],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then ‘Holiday’ else ‘Non Holiday’ end as [IsNZPublicHolidayDescription],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then (select [Description] from @NZAnnulPublicHolidays where [Date]=convert(int,convert(varchar(8),@CurrentDate,112))) else ‘N/A’ end as [NZPublicHolidayFullDescription],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then 1 else 0 end as [AUIsPublicHolidayCode],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then ‘Holiday’ else ‘Non Holiday’ end as [IsAUPublicHolidayDescription],

case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then (select [Description] from @AUAnnulPublicHolidays where [Date]=convert(int,convert(varchar(8),@CurrentDate,112))) else ‘N/A’ end as [AUPublicHolidayFullDescription],

DATEDIFF(Year,getdate(),@CurrentDate) as [CurrentYear],

DATEDIFF(Month,getdate(),@CurrentDate) as [CurrentMonth],

DATEDIFF(Day,getdate(),@CurrentDate) as [CurrentDay]

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 (@NZFiscalYearStartMonth variable) for each different fiscal calendar you have. (if you are only using single fiscal calendar, remove extra columns)
  • 5- insert weekends (into @WeeklyHolidays table variable)
  • 6- insert annual public holidays (into @AnnualPublicHolidays table variable), remember to do this for multiple countries if you are storing multiple country holidays
  • 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 and Australia annual public holidays for 2013 and 2014 (as it set already) as public holidays. And it will consider July as the first month of Fiscal Calendar for Australia, and April for New Zealand.

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 7 images)

Image 1:

Image 2:

Image 3:

Image 4: (Multiple Financial Calendars)

Image 5:(Multiple Financial Calendars)

Image 6: (Multiple Financial Calendars)

Image 7: (Multiple countries holidays and Current Year, Month, and Day)

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
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.

1 thought on “Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years”

Leave a Reply