I do quite a bit of training for Pragmatic Works so I find myself working a lot with the AdventureWorksDW data warehouse database. AdventureWorksDW is a great test database if you’re wanting to demonstrate a concept or test a theory, but one of the things that has always bugged me is that the date dimension (dbo.DimDate) has holes in the data! Having a complete date dimension is important when working with SSAS. Well yesterday was the final straw, so I put together the following script that will fill in the missing dates in DimDate. You can specify a start date and end date and make the AdventureWorksDW DimDate date dimension as big as you like. Enjoy!
BEGIN TRAN
declare @startdate date = '2005-01-01',
@enddate date = '2014-12-31'
IF @startdate IS NULL
BEGIN
Select Top 1 @startdate = FulldateAlternateKey
From DimDate
Order By DateKey ASC
END
Declare @datelist table (FullDate date)
while @startdate <= @enddate
Begin
Insert into @datelist (FullDate)
Select @startdate
Set @startdate = dateadd(dd,1,@startdate)
end
Insert into dbo.DimDate
(DateKey,
FullDateAlternateKey,
DayNumberOfWeek,
EnglishDayNameOfWeek,
SpanishDayNameOfWeek,
FrenchDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
SpanishMonthName,
FrenchMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear,
CalendarSemester,
FiscalQuarter,
FiscalYear,
FiscalSemester)
select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,
dl.FullDate,
datepart(dw,dl.FullDate) as DayNumberOfWeek,
datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,
(Select top 1 SpanishDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as SpanishDayNameOfWeek,
(Select top 1 FrenchDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as FrenchDayNameOfWeek,
datepart(d,dl.FullDate) as DayNumberOfMonth,
datepart(dy,dl.FullDate) as DayNumberOfYear,
datepart(wk, dl.FUllDate) as WeekNumberOfYear,
datename(MONTH,dl.FullDate) as EnglishMonthName,
(Select top 1 SpanishMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as SpanishMonthName,
(Select top 1 FrenchMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as FrenchMonthName,
Month(dl.FullDate) as MonthNumberOfYear,
datepart(qq, dl.FullDate) as CalendarQuarter,
year(dl.FullDate) as CalendarYear,
case datepart(qq, dl.FullDate)
when 1 then 1
when 2 then 1
when 3 then 2
when 4 then 2
end as CalendarSemester,
case datepart(qq, dl.FullDate)
when 1 then 3
when 2 then 4
when 3 then 1
when 4 then 2
end as FiscalQuarter,
case datepart(qq, dl.FullDate)
when 1 then year(dl.FullDate)
when 2 then year(dl.FullDate)
when 3 then year(dl.FullDate) + 1
when 4 then year(dl.FullDate) + 1
end as FiscalYear,
case datepart(qq, dl.FullDate)
when 1 then 2
when 2 then 2
when 3 then 1
when 4 then 1
end as FiscalSemester
from @datelist dl left join
DimDate dd
on dl.FullDate = dd.FullDateAlternateKey
Where dd.FullDateAlternateKey is null
COMMIT TRAN
I’ve tested the script against AdventureWorksDW2012 and AdventureWorksDW2008R2 and it worked great.
Let me know if you found this useful! Thanks for reading!