Blog: William McKnight

« United States I have NOT had a client in during career | Main | UK business intelligence projects hitting target »

Code to populate a date dimension

Here is some code I’ve been dragging around in my toolbox for many years. Some data warehouses populated their date dimension through only 2007 or 2008 because that seemed to be a long time from when the data warehouse project began. Well, now it’s time to add on a few more years of data to the date dimension. However, with the auto-population capabilities with current releases of database management systems for date dimensions, manually populating the date dimension has become a lost art. So, whether it’s useful or a stroll into the past, this code populates Year, Quarter, Month, Week, Day and Date dimensions.

The table names are as follows:

Year – Year_D
Year_Key - SK
The_Year - Int

Quarter – Quarter_D
Quarter_Key - SK
The_Quarter - Int

Month – Month_D
Month_Key - SK
Quarter_Key - Int [Ref: Quarter_D.Quarter_key]
The_Month - Int

Week – Week_D
Week_Key - SK
The_Week - Int

Day – Day_D
Day_Key - SK
The_Day - Int

Date – Date_D
Date_Key - SK
Year_Key - Int [Ref: Year_D.Year_Key]
Month_Key - Int [Ref: Month_D.Month_Key]
Week_Key - Int [Ref: Week_D.Week_Key]
Day_Key - Int [Ref: Day_D.Day_Key]
The_Date - Date

Code:

create procedure Load_Date_Dimension_Sp @year_start int, @year_end int
as
begin
declare @yr int
set @yr = year_start
while (@yr <= year_end)
begin
insert into year_d (the_year) values (@yr)
set @yr = @yr + 1
end

declare @qt int
set @qt = 1
while (@qt <= 4)
begin
insert into quarter_d (the_quarter) values (@qt)
set @qt = @qt + 1
end

declare @mo int
set @mo = 1
while (@mo <= 12)
begin
insert into month_d (the_month) values (@mo)
set @mo = @mo + 1
end

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=1)
where the_month in (1,2,3)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=2)
where the_month in (4,5,6)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=3)
where the_month in (7,8,9)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=4)
where the_month in (10,11,12)

declare @wk int
set @wk = 1
while (@wk <= 52)
begin
insert into week_d (the_week) values (@wk)
set @wk = @wk + 1
end

declare @dt1 int
set @dt1 = 1
while (@dt1 <= 31)
begin
insert into day_d (the_day) values (@dt1)
set @dt1 = @dt1 + 1
end

declare @dt datetime
declare @dt_end = ‘12/31/’ + @year_end
declare @day_k int
declare @week_k int
declare @month_k int
declare @year_k int

set @dt = '01/01/’ + @year_start
while (@dt <= @dt_end)
begin
select @year_k=year_key from year_d where the_year = year(@dt)
select @month_k=month_key from month_d where the_month = month(@dt)
select @day_k=day_key from day_d where the_day = day(@dt)
select @week_k=week_key from week_d where the_week = datepart(wk,@dt)
insert into date_d (the_date, day_key, week_key, month_key, year_key) values (@dt, @day_k, @week_k, @month_k, @year_k)
set @dt = @dt + 1
end

end

The Procedure can be executed as:

Exec Load_Date_Dimension_Sp 1985, 2030

…which will populate the date dimension for the years 1985 through 2030.

All the syntaxes are based on SQL Server TSQL.

Technorati tags: Data warehouse, Dimensional Model, Date Dimension

Share: