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!
Works a treat, has saved me a bucket of time. Much appreciated, wish you good karma.
Thank you. I’m glad you found this post helpful. Take care. ๐
It works like a charm!, thanks for share it,
You’re welcome, sir!
I’m currently doing an edX course “Microsoft: DAT206x Analyzing and Visualizing Data with Excel” that is based on Excel 2016. I’ve got Excel 2013 and needed a date dimension so thought I’d use DimDate in AdventureWorksDW2012 but there wasn’t any dates to suit the Fact table in the tutorial. My google search found your very helpful script. Thank you!!!
Awesome! Glad it was helpful! You’re welcome!
Thanks Dustin this saved me from having to write my own!
You’re welcome! ๐
Thanks Dustin! MS couldn’t be bothered to provide the correct MDF file, but thanks to you my ssis tutorial is now working. Thanks for doing the work and sharing it!! ๐ ๐ ๐
Hey Ryan , Thank you so much. please what does mean 112 ? convert(varchar,dl.FullDate,112)
i see now is a format but what does mean dlFulldate?
Fulldate is the name of the column.