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

Posted by on Dec 11, 2013 in ETL, T-SQL | One Comment
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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)

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">