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:
DateTimeID |
DateID |
YearID |
MonthYearID |
MonthID |
WeekYearID |
WeekID |
DayMonthID |
DayID |
DayOfWeekID |
HourDateID |
HourID |
MinuteHourDateID |
MinuteID |
MonthYearAsText |
MonthAsText |
WeekYearAsText |
WeekAsText |
DayMonthYearAsText |
DayMonthAsText |
DayAsText |
DayOfWeekAsText |
HourDateAsText |
HourAsText |
MinuteHourDateAsText |
MinuteHourAsText |
StartOfDayAsDate |
DateAndTimeAsDate |
201001010000 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010000 |
0 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:00 Uhr |
00:00 |
2010-01-01 00:00:00.000 |
01.01.2010 00:00 |
201001010001 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010001 |
1 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:01 Uhr |
00:01 |
2010-01-01 00:00:00.000 |
01.01.2010 00:01 |
201001010002 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010002 |
2 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:02 Uhr |
00:02 |
2010-01-01 00:00:00.000 |
01.01.2010 00:02 |
201001010003 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010003 |
3 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:03 Uhr |
00:03 |
2010-01-01 00:00:00.000 |
01.01.2010 00:03 |
201001010004 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010004 |
4 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:04 Uhr |
00:04 |
2010-01-01 00:00:00.000 |
01.01.2010 00:04 |
201001010005 |
20100101 |
2010 |
201001 |
1 |
20101 |
1 |
101 |
1 |
6 |
2010010100 |
0 |
201001010005 |
5 |
Jan 10 |
Januar |
KW01 2010 |
KW01 |
01. Jan 10 |
01. Jan |
1. |
Samstag |
1.1.2010 0 Uhr |
0Uhr |
1.1.2010 00:05 Uhr |
00:05 |
2010-01-01 00:00:00.000 |
01.01.2010 00:05 |
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