Populating a Kimball Date Dimension

I’m a big fan of the Kimball method of Data Warehousing. A common task most of us setting up a new Data Warehouse face is creating a Date Dimension. In their book, “The Microsoft Data Warehouse Toolkit With SQL Server 2005 and the Microsoft Business Intelligence Toolset”, they have an example of a good date dimension table in their books sample code. My complaint though was not so much with the layout itself, I liked it and found it fairly complete. Instead it was the method they chose to load it. They used an Excel spreadsheet, then a SQL Server Integration Services package to read the Excel file and load the date dimension table.

To me this approach has a couple of drawbacks. First, if you are doing all the loading on the server itself, you may not have Excel loaded. Thus you may be faced with the headache of creating the sheet then figuring out how to get it to a location the server can read. Second, when you go to add more dates in the future, you have to go into the spreadsheet and reset everything, removing what was there before. It can also be quite a headache to go back several years from know and find both SSIS packages and that Excel spreadsheet. Plus after that time changes may be made to both Excel and SSIS that make that solution no longer workable. Finally quite often it’s a DBA setting up the warehouse, and I’ve found there are still a few DBAs who are uncomfortable relying on SSIS, although I’m happy to say that number continues to shrink.

A T-SQL solution was clearly, to me anyway, the superior answer for both ease of use and long term stability. I assumed that as popular as the Kimball method is, someone would have already created a routine to load their style of date dimension, but some Binging and Googling around proved fruitless. I did find some code for loading some very simple date dimensions, but nothing as complete as the Kimball design. So, relishing a good coding challenge, I rolled up my sleeves and went to work. Below is the fruit of my labor, a script for loading a Kimball like date dimension. All you have to do is set the begin and end dates, indicate the offset for your fiscal year, and let ‘er rip. You can easily go back and add more dates by just adjusting the begin and end times.

A few things you should note. First, I did make a few slight modifications to the standard Kimball date dimension table as found in the previously mentioned book. They have a column titled “DateName” which holds the date as a string in YYYY/MM/DD format. As long as I was putting the date in, I decided to add string versions of the date for the US and Europe. These are in MM/DD/YYYY and DD/MM/YYYY formats and the columns are named “DateNameUS” and “DateNameEU” (for European Union) respectively.

Their table also had an audit key, used presumably by the SSIS package. I didn’t really see the need for an audit key for a date table, so I changed it to an identity column so I could have a secondary surrogate key if I needed it, just something to count the number of date rows easily and track the order they were inserted in.

One final, but very important distinction. I was in a post conference session taught by Erik Veerman at SQL PASS 2009. In it he mentioned using Dim and Fact schemas, thus you’d have [Dim].[Date] instead of [dbo].[DimDate]. I liked the idea as it was something I’d been considering myself, so in this version that is what I did. If you use the more traditional naming format of dbo.DimDate you’ll need to tweak the code.

Below is the code to load the Date Dimension table, which is my creation. Under it I placed my modified version of the Kimball Date Dimension table. It’s core code came from the sample code mentioned in the first paragraph then was modified by me. I include it for completeness.

Update: A few readers aptly pointed out I’d missed replacing a static date field when I worked the final version of the code. Made the change to replace the static date with @DateCounter.

Code Sample 1 – Script to load a date dimension.

/*---------------------------------------------------------------------------*/
/* Loads a Date Dimension                                                    */
/*---------------------------------------------------------------------------*/

-- A few notes, this code does nothing to the existing table, no deletes
-- are triggered before hand. Because the DateKey is uniquely indexed,
-- it will simply produce errors if you attempt to insert duplicates.
-- You can however adjust the Begin/End dates and rerun to safely add
-- new dates to the table every year.
--
-- If the begin date is after the end date, no errors occur but nothing
-- happens as the while loop never executes.

SET NOCOUNT ON -- turn off all the 1 row inserted messages

-- Hold our dates
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

-- Holds a flag so we can determine if the date is the last day of month
DECLARE @LastDayOfMon CHAR(1)

-- Number of months to add to the date to get the current Fiscal date
DECLARE @FiscalYearMonthsOffset INT   

-- These two counters are used in our loop.
DECLARE @DateCounter DATETIME    --Current date in loop
DECLARE @FiscalCounter DATETIME  --Fiscal Year Date in loop

-- Set the date to start populating and end populating
SET @BeginDate = '01/01/2008'
SET @EndDate = '12/31/2010' 

-- Set this to the number of months to add to the current date to get
-- the beginning of the Fiscal year. For example, if the Fiscal year
-- begins July 1, put a 6 there.
-- Negative values are also allowed, thus if your 2010 Fiscal year
-- begins in July of 2009, put a -6.
SET @FiscalYearMonthsOffset = 6

-- Start the counter at the begin date
SET @DateCounter = @BeginDate

WHILE @DateCounter <= @EndDate
      BEGIN
            -- Calculate the current Fiscal date as an offset of
            -- the current date in the loop
            SET @FiscalCounter = DATEADD(m, @FiscalYearMonthsOffset, @DateCounter)

            -- Set value for IsLastDayOfMonth
            IF MONTH(@DateCounter) = MONTH(DATEADD(d, 1, @DateCounter))
               SET @LastDayOfMon = 'N'
            ELSE
               SET @LastDayOfMon = 'Y'  

            -- add a record into the date dimension table for this date
            INSERT  INTO [Dim].[Date]
                    (
                      [DateKey]
                    , [FullDate]
                    , [DateName]
                    , [DateNameUS]
                    , [DateNameEU]
                    , [DayOfWeek]
                    , [DayNameOfWeek]
                    , [DayOfMonth]
                    , [DayOfYear]
                    , [WeekdayWeekend]
                    , [WeekOfYear]
                    , [MonthName]
                    , [MonthOfYear]
                    , [IsLastDayOfMonth]
                    , [CalendarQuarter]
                    , [CalendarYear]
                    , [CalendarYearMonth]
                    , [CalendarYearQtr]
                    , [FiscalMonthOfYear]
                    , [FiscalQuarter]
                    , [FiscalYear]
                    , [FiscalYearMonth]
                    , [FiscalYearQtr]
                    )
            VALUES  (
                      ( YEAR(@DateCounter) * 10000 ) + ( MONTH(@DateCounter)
                                                         * 100 )
                      + DAY(@DateCounter)  --DateKey
                    , @DateCounter -- FullDate
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) --DateName
                    , RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2)  + '/'
                      + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName
                    , RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2)  + '/'
                      + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName
                    , DATEPART(dw, @DateCounter) --DayOfWeek
                    , DATENAME(dw, @DateCounter) --DayNameOfWeek
                    , DATENAME(dd, @DateCounter) --DayOfMonth
                    , DATENAME(dy, @DateCounter) --DayOfYear
                    , CASE DATENAME(dw, @DateCounter)
                        WHEN 'Saturday' THEN 'Weekend'
                        WHEN 'Sunday' THEN 'Weekend'
                        ELSE 'Weekday'
                      END --WeekdayWeekend
                    , DATENAME(ww, @DateCounter) --WeekOfYear
                    , DATENAME(mm, @DateCounter) --MonthName
                    , MONTH(@DateCounter) --MonthOfYear
                    , @LastDayOfMon --IsLastDayOfMonth
                    , DATENAME(qq, @DateCounter) --CalendarQuarter
                    , YEAR(@DateCounter) --CalendarYear
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + '-'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) --CalendarYearMonth
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @DateCounter) --CalendarYearQtr
                    , MONTH(@FiscalCounter) --[FiscalMonthOfYear]
                    , DATENAME(qq, @FiscalCounter) --[FiscalQuarter]
                    , YEAR(@FiscalCounter) --[FiscalYear]
                    , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + '-'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @FiscalCounter) AS CHAR(2))), 2) --[FiscalYearMonth]
                    , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @FiscalCounter) --[FiscalYearQtr]
                    )

            -- Increment the date counter for next pass thru the loop
            SET @DateCounter = DATEADD(d, 1, @DateCounter)
      END

SET NOCOUNT ON -- turn the annoying messages back on

-- Select all rows inserted for the final year as a sanity check
SELECT  *
FROM    [Dim].[Date]
WHERE DateKey > (YEAR(@EndDate) * 10000)

Code Sample 2 – Modified Kimball code to create a Date dimension.

/* Make sure the Dim schema exists */
IF SCHEMA_ID('Dim') IS NULL
   EXECUTE('CREATE SCHEMA [Dim] AUTHORIZATION [dbo]')
GO

/* Drop table DimDate */
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[Dim].[Date]')
                    AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
   DROP TABLE [Dim].[Date]
GO

/* Create table DimDate */
CREATE TABLE [Dim].[Date]
       ( [DateKey] BIGINT NOT NULL
       , [FullDate] DATETIME NULL
       , [DateName] CHAR(11) NULL
       , [DateNameUS] CHAR(11) NULL   --US Date FORMAT, MM/DD/YYYY
       , [DateNameEU] CHAR(11) NULL   --European Union Date Format DD/MM/YYYY
       , [DayOfWeek] TINYINT NULL
       , [DayNameOfWeek] CHAR(10) NULL
       , [DayOfMonth] TINYINT NULL
       , [DayOfYear] SMALLINT NULL
       , [WeekdayWeekend] CHAR(7) NULL
       , [WeekOfYear] TINYINT NULL
       , [MonthName] CHAR(10) NULL
       , [MonthOfYear] TINYINT NULL
       , [IsLastDayOfMonth] CHAR(1) NULL
       , [CalendarQuarter] TINYINT NULL
       , [CalendarYear] SMALLINT NULL
       , [CalendarYearMonth] CHAR(7) NULL
       , [CalendarYearQtr] CHAR(7) NULL
       , [FiscalMonthOfYear] TINYINT NULL
       , [FiscalQuarter] TINYINT NULL
       , [FiscalYear] INT NULL
       , [FiscalYearMonth] CHAR(9) NULL
       , [FiscalYearQtr] CHAR(8) NULL
       , [AuditKey] BIGINT IDENTITY NOT NULL
       , CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateKey] )
       )
ON     [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name = N'Table Type', @value = N'Dimension',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'View Name', @value = N'Date',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Date dimension contains one row for every day, beginning at 1/1/2000. There may also be rows for "hasn''t happened yet."',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'Used in schemas',
  @value = N'Sales (3 roles); Finance; Currency Rates; Sales Quota (2 roles; one at Cal Qtr level)',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'

GO

INSERT  INTO [Dim].[Date]
        ( DateKey
        , FullDate
        , [DateName]
        , [DateNameUS]
        , [DateNameEU]
        , [DayOfWeek]
        , DayNameOfWeek
        , [DayOfMonth]
        , [DayOfYear]
        , WeekdayWeekend
        , WeekOfYear
        , [MonthName]
        , MonthOfYear
        , IsLastDayOfMonth
        , CalendarQuarter
        , CalendarYear
        , CalendarYearMonth
        , CalendarYearQtr
        , FiscalMonthOfYear
        , FiscalQuarter
        , FiscalYear
        , FiscalYearMonth
        , FiscalYearQtr
        )
VALUES  ( -1
        , NULL
        , 'Unknown'
        , 'Unknown'
        , 'Unknown'
        , NULL
        , 'Unknown'
        , NULL
        , NULL
        , 'Unknown'
        , NULL
        , 'Unknown'
        , NULL
        , 'N'
        , NULL
        , NULL
        , 'Unknown'
        , 'Unknown'
        , NULL
        , NULL
        , NULL
        , 'Unknown'
        , 'Unknown'
        )
GO

EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Surrogate primary key', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Full date as a SQL date (time=00:00:00)', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard Date Format of YYYY/MM/DD', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard US Date Format of MM/DD/YYYY', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateNameUS' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard European Union Date Format of DD/MM/YYYY', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateNameEU' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day of week; Sunday = 1', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Day name of week', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day in the month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day in the year', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Is today a weekday or a weekend', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Week of year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Month name',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Month of year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Is this the last day of the calendar month?',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar quarter', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year and month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year and quarter', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal month of year (1..12). FY starts in July',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal quarter', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year. Fiscal year begins in July.',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year and month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year and quarter', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'What process loaded this row?', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'FK To',
  @value = N'DimAudit.AuditKey', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'20041123', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'11/23/2004', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'23-Nov-2004', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..7',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Sunday',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..31',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..365',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'Weekday, Weekend', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1..52 or 53', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'November', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Y, N',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004-01',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004Q1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'FY2004-01', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'FY2004Q1', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System',
  @value = N'Derived in ETL', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'Comments',
  @value = N'In the form: yyyymmdd', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateKey' ;
GO