Date Dimension - DDL/DML to create and maintain

For any data warehouse or business intelligence work, you are going to need a stock process to create and then periodically maintain a date dimension table. The emphasis here is on maintenance.

Periodically, you'll need to add to the date dimension.  Over the years, I've worked out a fairly simple, but sound date dimension create and update process.   Below are the details:

DateKey

Dates never change, so the datekey can have intelligence. It is one of the few dimensions that can break the rule where a dimension key should never have intelligence. The datakey uses the ISO format:  YYYYMMDD. This allows you to assign a datekey to business data using only the date, without having to reference the date dimension.

The scripts

create schema rds;
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND type in (N'U'))
DROP TABLE [rds].[Date_dim]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [rds].[Date_dim](
 [DateKey] [int] NOT NULL,
 [FullDateAltKey] [date] NULL,
 [DayOfWeekNum] [tinyint] NULL,
 [DayOfWeekName] [nvarchar](10) NULL,
 [DayOfMonthNum] [tinyint] NULL,
 [DayOfYearNum] [smallint] NULL,
 [WeekOfYearNum] [tinyint] NULL,
 [MonthOfYearName] [nvarchar](10) NULL,
 [MonthOfYearNum] [tinyint] NULL,
 [CalendarQuarter] [tinyint] NULL,
 [CalendarYear] [smallint] NULL,
 [CalendarSemester] [tinyint] NULL,
 [FiscalPeriod] [tinyint] NULL,
 [FiscalQuarter] [tinyint] NULL,
 [FiscalYear] [smallint] NULL,
 [FiscalSemester] [tinyint] NULL,
 [FiscalDayOfPeriodNum] [tinyint] NULL,
 [FiscalDayOfYearNum] [smallint] NULL,
 [FiscalWeekOfYearNum] [tinyint] NULL,
 [FiscalPeriodOfYearName] [nvarchar](10) NULL,
 [SimpleCalendarDate] [varchar](20) NULL,
 [SimpleFiscalDate] [varchar](20) NULL,
 [SimpleCalendarMonth] [varchar](20) NULL,
 [SimpleCalendarQtr] [char](5) NULL,
 [SimpleFiscalQtr] [char](5) NULL,
 [SimpleFiscalYear] [char](7) NULL,
 [SimpleCalendarYear] [char](7) NULL,
 [CalendarQuarterDesc] [char](10) NULL,
 [FiscalQuarterDesc] [char](10) NULL,
 [CalendarSemesterDesc] [char](10) NULL,
 [FiscalSemesterDesc] [char](10) NULL,
 [SimpleFiscalPeriod] [varchar](20) NULL,
 [SimpleFiscalPeriodNo] [int] NULL,
 [FiscalPeriodLabel] [nchar](2) NULL,

) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND name = N'PK_rds_Date_dim_DateKey')
DROP INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] 
(
 [DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The following section, adds the core date data, Typically, this is added one year at a time.
But, it can be extended to insert as many years as needed.

/* -----------------------------------------------------
   -----------------------------------------------------
   
 Process to add records to the rds.Date_dim
 In production, dates are added one year at a time. 
    Alternatively, you could front load the table with 
    20-30 years into future.

 @intLOOPend= 370     -- added as simple infinite loop break, 
             
 @dtStartDate = 'jan 1, 2014'  -- calendar year start
 @dtEndDate = 'december 31, 2014'; -- calendar year end

 Fiscal dates vary, so they are not included
   -----------------------------------------------------  */

set nocount on;
SET DATEFIRST 7;  -- 7=default=sunday, monday=1
go


declare @intLOOPcnt int, @intLOOPend int , @dtStartDate datetime, @dtEndDate datetime, @dtProcessDate datetime;

-- KEY SETTINGS / INPUTS
Select @intLOOPcnt = 0,@intLOOPend= 4000
, @dtStartDate = 'Jan 1, 2000', @dtEndDate = 'December 31, 2004';

-- init date to be processed
select @dtProcessDate = @dtStartDate;

while @intLOOPcnt < @intLOOPend AND @dtProcessDate <= @dtEndDate
  begin

   /*debug*/ --print 'test' + cast(@dtProcessDate as varchar)
  BEGIN TRY
    -- insert 
        
  insert into rds.Date_dim
   ( dateKey, fullDateAltKey, dayOfWeekNum, DayOfWeekName, dayOfMonthNum, dayOfYearNum
   , weekOfYearNum
   , MonthOfYearName, monthOfYearNum
   , calendarQuarter, calendarYear, calendarSemester
   --, fiscalYear, FiscalDayOfYearNum,FiscalPeriodLabel
   --, fiscalQuarter, fiscalSemester
   )

  select
    dateKey   = cast(CONVERT( varchar(8),  @dtProcessDate , 112) as int)
   , fullDateAltKey = CONVERT( varchar(8),  @dtProcessDate , 112)
   , dayOfWeekNum  = datepart(weekday,@dtProcessDate) 
   , DayOfWeekName  = datename(weekday,@dtProcessDate)
   , dayOfMonthNum  = datepart(day,@dtProcessDate)
   , dayOfYearNum  = datepart(dayofyear,@dtProcessDate)
   , weekOfYearNum  = datepart(week,@dtProcessDate)
   , MonthOfYearName = datename(month,@dtProcessDate)
   , monthOfYearNum = datepart(month,@dtProcessDate)
   , calendarQuarter = datepart(quarter,@dtProcessDate)
   , calendarYear  = datepart(year,@dtProcessDate)
   , calendarSemester  = case when datepart(quarter,@dtProcessDate) in (1,2) then 1 else 2 end 
   --, fiscalYear        = datepart(year,@dtProcessDate)
   --, FiscalDayOfYearNum = datepart(dayofyear,@dtProcessDate) 
   --, FiscalPeriodLabel  = '  '
   ;

  END TRY
  BEGIN CATCH

   print 'CATCH - error: ' + cast(@@error as varchar) + ',  ProcessDate:  '+ cast(@dtProcessDate as varchar);
  END CATCH
      
  -- next item
  select @intLOOPcnt = @intLOOPcnt + 1;
  select @dtProcessDate = dateadd(day,1,@dtProcessDate );

  end
go

This final section simply updates the few remaining values. To keep the build script simple, I separated this out of the main insert. You could include it, but this is run infrequently, and simplicity is more important than having an impressive, but complex insert statement.

-------------------------------------------------------------------------------------------------------------------------
-- OTHER CALENDAR VALUES FOR UPDATE 
-------------------------------------------------------------------------------------------------------------------------
     
update d set 
    -- select top 50 
  SimpleCalendarDate = MonthOfYearName + ' ' + CAST(DayOfMonthNum as varchar) + ', ' + cast(CalendarYear as varchar)
 ,SimpleCalendarMonth = MonthOfYearName + ' ' + cast(CalendarYear as varchar)
 ,SimpleCalendarQtr = 'CY Q' + CAST(CalendarQuarter as CHAR(1))
 ,SimpleCalendarYear = 'CY ' +  cast(CalendarYear as varchar)
 ,CalendarQuarterDesc = 'Q' + CAST(CalendarQuarter as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)
 ,CalendarSemesterDesc = 'H' + cast(CalendarSemester as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)

    from rds.date_dim as d 
 WHERE d.DateKey between 20050101 and 20051231;