Sample Time Dimension Table


Time - Time of day in hour and minute

Column  / Alternative

Data Type / Comment

Description  /  Examples  /  Notes

Time_Key

TimeKey

 SmallInt

Surrogate key arbitrary number that uniquely identifies a time of day in hour and minute.

1, 2, 3, ... 1439, 1440, and 0 (for not applicable).

This key is also a column in fact tables where it relates a fact to a time of day.

Hour_12_ID

Hour12ID

VarChar (11)

'lookup'

Text that identifies an hour of a day using a 12-hour clock.

12 Midnight, 1 AM, 2 AM, … 12 Noon, 1 PM, … 11 PM, and NA.

Hour is calculated with minutes truncated so that rounding does not move a time into the next hour.

Hour_24_ID

Hour24ID

Char (2)

'lookup'

Text that identifies an hour of a day using a 24-hour clock.

00, 01, 02, … 12, 13, … 23, and NA.

This ID lists hours in correct order when sorted.

Hour is calculated with minutes truncated so that rounding does not move a time into the next hour.

Quarter_Day_12_ID

QuarterDay12ID

VarChar (23)

'lookup'

Text that identifies a quarter of a day using a word and a 12-hour clock.

Night (12 – 5:59 AM), Morning (6 – 11:59 AM), Afternoon (12 – 5:59 PM), Evening (6 – 11:59 PM), and NA.

If preferred, the text, such as Night, could be moved to a separate column, which could be named Quarter_Day_Text or Quarter_Day_Name. If so, Quarter_Day_12_ID would have values such as 12 – 5:59 AM.

If preferred, :00 could be added to the starting times, making the first example Night (12:00 – 5:59 AM).

Quarter_Day_24_ID

QuarterDay24ID

Char (13)

'lookup'

Text that identifies a quarter of a day using a 24-hour clock.

00:00 – 05:59, 06:00 – 11:59, 12:00 – 17:59, 18:00 - 23:59, and NA.

This ID lists quarter days in correct order when sorted.

Quarter_Hour_12_ID

QuarterHour12ID

Char (11)

'lookup'

Text that identifies a quarter hour of a day using a 12-hour clock.

12:00-14 AM, 12:15-29 AM, …, 1:00-14 AM, …, 12:30-44 PM, 11:45-59 PM, and NA.

If preferred, the hour could be added to the ending times, making the first example 12:00 - 12:14 AM.

Quarter_Hour_24_ID

QuarterHour24ID

Char (8)

'lookup'

Text that identifies a quarter hour of a day using a 24-hour clock.

00:00-14, 00:15-29, … 01:00-14, … 12::30-44, 23:45-59, and NA.

This ID lists quarter hours in correct order when sorted.

If preferred, the hour could be added to the ending times, making the first example 00:00 - 00:14.

Time_12_ID

Time12ID

Char (8)

'lookup'

Text that identifies a time of day in hour and minute using a 12-hour clock.

12:00 AM, 12:01 AM, … 1:00 AM, … 12:00 PM, 12:01 PM, … 11:58 PM, 11:59 PM, and NA.

Time is calculated with seconds and fractions of seconds truncated so that rounding does not move a time into the next minute.

Time_24_ID

Time24ID

Char (5)

'lookup'

Text that identifies a time of day in hour and minute using a 24-hour clock.

00:00, 00:01, … 01:00, … 12:00, 12:01, … 01:00, … 23:58, 23:59, and NA.

This ID lists times in correct order when sorted.

Time is calculated with seconds and fractions of seconds truncated so that rounding does not move a time into the next minute.

Loaded_Date_Time

LoadedDateTime

 TimeStamp

Date and time, in the current time zone, a row was first loaded into the data warehouse staging area.

... 2011-05-20 23:59:59, 2011-05-21 00:00:00, ....

The date and time retrieved from this column are those that were in effect in the time zone of the retrieval when the statement that loaded the row began to execute, even if the row was loaded in a different time zone.

Modified_Date_Time

ModifiedDateTime

 TimeStamp

Date and time, in the current time zone, a row was last modified in the data warehouse staging area.

0000-00-00 00:00:00, ... 2011-05-20 23:59:59, 2011-05-21 00:00:00, ....

The date and time retrieved from this column are those that were in effect in the time zone of the retrieval when the statement that last modified the row began to execute, even if the row was modified in a different time zone.

"We strongly encourage designers to separate time of day from the date dimension to avoid an explosion in the date dimension row count."

"If the enterprise has well defined attributes for time slices within a day, such as shift names, or advertising time slots, an additional time-of-day dimension can be added to the design where this dimension is defined as the number of minutes (or even seconds) past midnight. Thus this time-of-day dimension would either have 1440 records if the grain were minutes or 86,400 records if the grain were seconds."

  • We recommend against duplicating some columns in this table with their names expanded to differentiate between local time and time at a headquarters and/or Universal Time, since the difference between time in two time zones can be different on different days. Instead, we recommend adding additional Date_Key and Time_Key columns to fact tables if times in different time zones are needed for facts, such as Local_Date_Key and Local_Time_Key, and Headquarters_Date_Key and Headquarters_Time_Key.