SSAS - Creating and filling the DateTime Dimension

 The following describe how to create a DateTime Dimension Data Warehouse table, and prefill it with values in a very fast way.

The DateTime Table is created in Schema named [dim]. With schema naming like this you can easily distinguish between fact, dim and other tables without the need to prefix the object names.

The table has a unique DateTimeID column which is BigInt and will hold the date and time in the format YYYYMMDDhhmm. e.g. 201105111223 for 2011, May 5th 12:23pm. This column will be joined with the fact table. The other columns can be used for hierarchies.

??CREATE TABLE [dim].[DateTime](

      [DateTimeID] [bigint] NOT NULL,

      [DateID] [bigint] NULL,

      [YearID] [bigint] NULL,

      [MonthYearID] [bigint] NULL,

      [MonthID] [bigint] NULL,

      [WeekYearID] [bigint] NULL,

      [WeekID] [bigint] NULL,

      [DayMonthID] [bigint] NULL,

      [DayID] [bigint] NULL,

      [DayOfWeekID] [bigint] NULL,

      [HourDateID] [bigint] NULL,

      [HourID] [bigint] NULL,

      [MinuteHourDateID] [bigint] NULL,

      [MinuteID] [bigint] NULL,

      [MonthYearAsText] [varchar](15) NULL,

      [MonthAsText] [varchar](10) NULL,

      [WeekYearAsText] [varchar](10) NULL,

      [WeekAsText] [varchar](10) NULL,

      [DayMonthYearAsText] [varchar](20) NULL,

      [DayMonthAsText] [varchar](15) NULL,

      [DayAsText] [varchar](3) NULL,

      [DayOfWeekAsText] [varchar](10) NULL,

      [HourDateAsText] [varchar](20) NULL,

      [HourAsText] [varchar](6) NULL,

      [MinuteHourDateAsText] [varchar](25) NULL,

      [MinuteHourAsText] [varchar](10) NULL,

      [StartOfDayAsDate] [datetime] NULL,

      [DateAndTimeAsDate] [datetime] NULL,

CONSTRAINT [PK_DateTime] PRIMARY KEY CLUSTERED

(

      [DateTimeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

This table will hold data like this:

 Now we prefill this table with data for one year using the following SQL Script, this should be done in less than 2 mninutes:

declare @StartDate datetime

SET @StartDate = '20090101 00:00'

Set nocount on;

-- Build 525600 ids (1440 minutes per one years days) to cross join   

SELECT TOP 525600

IDENTITY(INT,1,1) as Id

Into

      dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

;

ALTER TABLE dbo.Tally

      ADD CONSTRAINT PK_Tally_id

            PRIMARY KEY CLUSTERED (id) WITH FILLFACTOR = 100

            ;

-- Build a Month names Table to join

IF OBJECT_ID('dbo.MonthNames') IS NOT NULL

      DROP TABLE dbo.MonthNames;

      Create Table dbo.MonthNames

            ( id int

            , [MonthName] varchar(255)

            );

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (1 , 'Januar') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (2 , 'Februar') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (3 , 'März') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (4 , 'April') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (5 , 'Mai') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (6 , 'Juni') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (7 , 'Juli') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (8 , 'August');

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (9 , 'September');

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (10 , 'Oktober') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (11 , 'November') ;

      Insert dbo.MonthNames   ( id  , [MonthName]     ) Values    (12 , 'Dezember') ;

-- Build a Day names Table to join

IF OBJECT_ID('dbo.DayNames') IS NOT NULL

      DROP TABLE dbo.DayNames;

      Create Table dbo.DayNames

            ( id int

            , [DayName] varchar(255)

            );

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (1 , 'Montag') ;

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (2 , 'Dienstag');

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (3 , 'Mittwoch') ;

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (4 , 'Donnerstag');

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (5 , 'Freitag') ;

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (6 , 'Samstag') ;

      Insert dbo.DayNames     ( id  , [DayName] ) Values    (7 , 'Sonntag') ;

Set nocount off

-- Join and fill the Datetime Dimension

Insert dim.datetime

Select

       cast(convert(varchar, dateadd(n, t.id , @StartDate ) ,112)+ substring(replace(convert(varchar, dateadd(n, t.id , @StartDate ) ,108),':','') , 1, 4) as Bigint)

       as Datetimetid

      ,cast(convert(varchar, dateadd(n, t.id , @StartDate ) ,112) as bigint)

       as DateId

,Cast(year(dateadd(n, t.id , @StartDate ) ) as Bigint)

as YearId

      ,cast( Substring(convert(varchar, dateadd(n,t.id , @StartDate ) ,112), 1, 6) as Bigint)

       as MonthYearId

,Cast(month(dateadd(n,t.id , @StartDate ) ) as Bigint)

as MonthId

,Cast(cast(year(dateadd(n, t.id , @StartDate ))as varchar(4))    +      cast(datepart(wk,(dateadd(n,t.id , @StartDate )))as varchar(2)) as Bigint)

as WeekYearId

,Cast(datepart(wk,(dateadd(n,t.id , @StartDate ))) as Bigint)

as WeekId

,cast( Substring(convert(varchar, dateadd(n,t.id , @StartDate ) ,112), 5, 4) as Bigint)

as DayMonthId

,cast( datepart(d, dateadd(n,t.id , @StartDate ) ) as bigint )

as DayId

,cast( datepart(dw, dateadd(n,t.id , @StartDate ) ) as bigint)

as DayOfWeekId

      ,cast(convert(varchar, dateadd(n,t.id , @StartDate ) ,112)+ substring(replace(convert(varchar, dateadd(n,t.id , @StartDate ) ,108),':','') , 1, 2) as Bigint)

       as HourDateId

,cast(datepart(hh, dateadd(n,t.id , @StartDate ) ) as bigint)

as HourId

      ,cast(convert(varchar, dateadd(n,t.id , @StartDate ) ,112)+ substring(replace(convert(varchar, dateadd(n,t.id , @StartDate ) ,108),':','') , 1, 4) as bigint)

       as MinuteHourDateId

,cast( datepart(n, dateadd(n,t.id , @StartDate)) as bigint)

as MinuteId

,cast( m.[MonthName] + ' ' + Cast(year(dateadd(n, t.id , @StartDate ) ) as varchar) as varchar)

as MonthYearAsText

,cast( m.[MonthName] as varchar)

as MonthAsText

      ,cast('KW' + right('0' + Convert(varchar(2),datepart(wk,dateadd(n, t.id , @StartDate ))),2) + ' ' + Convert(varchar(4),year(dateadd(n, t.id , @StartDate ))) as varchar)

       as WeekYearAsText

      ,cast('KW' + right('0' + Convert(varchar(2),datepart(wk,dateadd(n, t.id , @StartDate ))),2) as varchar)

       as WeekAsText

      ,cast(Convert(varchar(2),day(dateadd(n, t.id , @StartDate ))) + '. ' + m.[MonthName] + ' ' + Convert(varchar(4),year(dateadd(n, t.id , @StartDate ))) as varchar)

       as DayMonthYearAsText

      ,cast(Convert(varchar(2),day(dateadd(n, t.id , @StartDate ))) + '. ' + m.[MonthName] as varchar)

       as DayMonthAsText

      ,cast(Convert(varchar(2),day(dateadd(n, t.id , @StartDate ))) + '. ' as varchar)

       as DayAsText

      ,cast(d.[DayName] as varchar)

       as DayOfWeekAsText

,cast(Convert(varchar(2),day(dateadd(n, t.id , @StartDate ))) + '.' + Convert(varchar(2),month(dateadd(n, t.id , @StartDate ))) + '.' + Convert(varchar(4),year(dateadd(n, t.id , @StartDate ))) + ' ' + convert(varchar(2), datepart(hh, dateadd(n, t.id , @StartDate ))) + ' Uhr' as varchar)

as HourDateAsText

,cast(Convert(varchar(2), datepart(hh, dateadd(n, t.id , @StartDate ))) + 'Uhr' as varchar)

as HourAsText

,cast(Convert(varchar(2),day(dateadd(n, t.id , @StartDate ))) + '.' + Convert(varchar(2),month(dateadd(n, t.id , @StartDate ))) + '.' + Convert(varchar(4),year(dateadd(n, t.id , @StartDate ))) + ' ' + substring(convert(varchar, dateadd(n, t.id , @StartDate ) ,108) , 1, 5) + ' Uhr' as varchar)

as MinuteHourDateAsText

      ,cast(substring(convert(varchar, dateadd(n, t.id , @StartDate ) ,108) , 1, 5) as varchar)

       as MinuteHourAsText

      ,convert(datetime, cast(convert(varchar, dateadd(n, t.id , @StartDate ) ,112) as varchar) )

       as StartOfDayASDate

      ,dateadd(n, t.id , @StartDate )

       as DateAndTimeAsDate

from

      dbo.Tally t

       Inner Join dbo.MonthNames m

       on m.id = month(dateadd(n, t.id , @StartDate ))

       Inner Join dbo.DayNames d

       on d.id = datepart(dw, dateadd(n, t.id , @StartDate ))

            ;

IF OBJECT_ID('dbo.MonthNames') IS NOT NULL

      DROP TABLE dbo.MonthNames;

IF OBJECT_ID('dbo.Tally') IS NOT NULL

      DROP TABLE dbo.Tally;

IF OBJECT_ID('dbo.DayNames') IS NOT NULL

      DROP TABLE dbo.DayNames;

GO