Script To Populate AdventureWorksDW DimDate

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!

12 thoughts on “Script To Populate AdventureWorksDW DimDate”

  1. 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!!!

  2. 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!! ๐Ÿ™‚ ๐Ÿ™‚ ๐Ÿ™‚

  3. Hey Ryan , Thank you so much. please what does mean 112 ? convert(varchar,dl.FullDate,112)

Leave a Reply