A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, October 22, 2014

A quick and dirty date dimension for PowerPivot

I've built out this sort of thing a few times but in fine fashion, I've never saved my script. In a proper data warehouse, there would be a date dimension built out and I would just reference it. Whenever I get skunkworks projects for things like PowerPivot demos, since that data's not been cared for, I need something handy.

This script generates approximately 20 years of data. It uses an intelligent surrogate key and begins counting at 2014-01-01. It generates date part names and their numeric values for sorting purposes.

    -- 20 years, approximate
    TOP (20 * 365)
    CAST(CONVERT(char(8), D.FullDate, 112) AS int) AS DateKey
,   D.FullDate
,   YEAR(D.FullDate) AS YearValue

,   'Q' + DATENAME(QUARTER, D.FullDate) AS QuarterName
,   DATEPART(QUARTER, D.FullDate) AS QuarterValue

,   DATENAME(mm, D.FullDate) AS MonthName
,   MONTH(D.FullDate) AS MonthValue

,   DAY(D.FullDate) AS DayValue

,   DATENAME(dw, D.FullDate) AS DayOfWeekName
,   DATEPART(dw, D.FullDate) AS DayOfWeekValue

        DATEADD(d, D.number, BOT.StartDate) AS FullDate
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS number
                sys.all_columns AS AC
        ) D
            -- Start date
            SELECT CAST('2014-01-01' AS date) AS StartDate
        ) BOT
) D;

No comments: