Script for Creating and Generating members for Date Dimensions: General Purpose

Posted by on Jan 9, 2013 in Business Intelligence, ETL, SQL Server, T-SQL | No Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

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.

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="">