There are times when you need a time dimension. Web and
operational data is a common use. Over the last month, what were the web
statistics by hour of the day. What time of day do we get the most orders?
Again, a time of day question.
So, we need a time of day dimension, along with some logic to
capture the time of day. I've divided the creation and population of the time
of day dimension into two parts. The first part creates and populates a simple
five column time dimension. The later part provides several possible
enhancements. Every data warehouse is different, so it makes it easier to first
build out the core time of day dimension first, and then provides ways to
extended it, so that you can extend it the way you need it.
Surrogate Key
While most dimensions are best served with a surrogate key,
date and time dimensions are best when the key defines the value. Date and time
dimensions never change, with no expectation of change. With absolutely no change,
there is no need to use a surrogate key.
Our surrogate key is effectively an integer based compound key derived
from the hour, the minute and the second. For example:
timeOfDayKey
|
fullTimeAltKey
|
hour
|
minute
|
second
|
0
|
000000
|
0
|
0
|
0
|
24639
|
024639
|
2
|
46
|
39
|
101324
|
101324
|
10
|
13
|
24
|
Data Dictionary
Column
|
Defintion
|
timeOfDayKey
|
Table key, and surrogate key
|
fullTimeAltKey
|
Character based time key, will all leading zeros
|
hour
|
24 hour clock hour in the day
|
minute
|
Minute for the hour
|
second
|
Second for the minute in the hour
|
Table Create
/* -----------------------------------------------------------
create the initial table
----------------------------------------------------------- */
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dimTimeOfDay](
timeOfDayKey int not null
,fullTimeAltKey char(6)
,hour tinyint
,minute tinyint
,second tinyint
);
alter table [dimTimeOfDay]
add constraint PK_dimTimeOfDay PRIMARY KEY ( timeOfDayKey);
create index idx_dimTimeOfDay ON dbo.dimTimeOfDay (fullTimeAltKey)
include ( hour, minute, second);
Initial Data Population
/* -----------------------------------------------------------
Populate the table
# Rows: 86400
----------------------------------------------------------- */
set nocount on
declare @dtProcessDate datetime, @dtEndDate datetime, @intCounter int
,@intprocessDate int , @strFullTimeAltKey char(6),@intHour tinyint
,@intMinute tinyint, @intSecond tinyint
set @dtprocessDate = dateadd(dd, datediff(dd,0,getdate()),0);
set @intCounter = 0
select @dtEndDate = dateadd(dd,1,@dtProcessDate)
select @dtprocessDate as 'process date', @dtEndDate as 'end date'
while @dtProcessDate < @dtEndDate and @intCounter < 100000
begin
--print '------------------------------------------------------------'
-- debug select @intCounter as 'Counter', @dtprocessDate as 'process date', @dtEndDate as 'end date'
-- time parts, w/ explicit conversion from int to tinyint
select @intHour = cast(datepart(hh, @dtProcessDate) as tinyint)
,@intMinute = cast(datepart(mi, @dtProcessDate) as tinyint)
,@intSecond = cast(datepart(ss, @dtProcessDate) as tinyint)
select @strFullTimeAltKey =
right( '00' + cast(@intHour as varchar),2)
+ right( '00' + cast(@intMinute as varchar),2)
+ right( '00' + cast(@intSecond as varchar),2)
select @intprocessDate = cast(@strFullTimeAltKey as int)
-- populate table
insert into dbo.dimTimeOfDay(timeOfDayKey,fullTimeAltKey,hour, minute, second )
values ( @intprocessDate, @strFullTimeAltKey, @intHour, @intMinute, @intSecond )
-- bump date
select @dtProcessDate = dateadd(second,1,@dtProcessDate)
select @intCounter = @intCounter + 1
end
Possible Enhancements
For presentation purposes, we need something better than
these five values. For this post, we have listed out six enhancements. Pick and
choose as needed, or add your own.
Presentation Enhancements to the Data Dictionary
Column
|
Definition
|
TimeOfDayNameFullMil
|
00:00:00; military
format, time presentation, including seconds
|
TimeOfDayNameShtMil
|
00:00; military
format, time presentation, hour and minute only
|
TimeOfDayNameFullStd
|
12:00:00 am/pm; standard
format, time presentation, including seconds
|
TimeOfDayNameShtStd
|
12:00 am/pm; standard
format, time presentation, hour and minute only
|
HourNameStd
|
12 am/pm; hour only name
|
MinuteName
|
00:00 minute with seconds
|
Table Alter to add Enhancements
alter table dimTimeOfDay
add TimeOfDayNameFullMil char(8)
alter table dimTimeOfDay
add TimeOfDayNameShtMil char(5)
alter table dimTimeOfDay
add TimeOfDayNameFullStd char(11)
alter table dimTimeOfDay
add TimeOfDayNameShtStd char(8)
alter table dimTimeOfDay
add HourNameStd char(5)
alter table dimTimeOfDay
add MinuteName char(5)
Populate Enhanced columns
update dbo.dimTimeOfDay
set
TimeOfDayNameFullMil = right('00' + cast(hour as varchar),2) + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)
,TimeOfDayNameShtMil = right('00' + cast(hour as varchar),2) + ':'
+ right('00' + cast(minute as varchar),2)
,TimeOfDayNameFullStd = case
when hour = 0 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2) + ' am'
when hour >= 1 and hour <=11 then cast(hour as varchar) + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2) +' am'
when hour = 12 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2) + ' pm'
when hour >= 13 then cast((hour-12)as varchar) + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2) + ' pm'
else '?' end
,TimeOfDayNameShtStd = case
when hour = 0 then '12' + ':' + right('00' + cast(minute as varchar),2) + ' am'
when hour >= 1 and hour <=11 then cast(hour as varchar) + ':' + right('00' + cast(minute as varchar),2) + ' am'
when hour = 12 then '12' + ':' + right('00' + cast(minute as varchar),2) + ' pm'
when hour >= 13 then cast((hour-12)as varchar) + ':' + right('00' + cast(minute as varchar),2) + ' pm'
else '?' end
,HourNameStd = case
when hour = 0 then '12 am'
when hour >= 1 and hour <=11 then cast(hour as varchar) + ' am'
when hour = 12 then '12 pm'
when hour >= 13 then cast((hour-12)as varchar) + ' pm'
else '?' end
,MinuteName = right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2);
