Time Dimension is an important dimension in most of the Data Warehouses, especially when requirement for keeping hours, minutes, and seconds is vital for the business.
Some blog and books and articles consider time dimension as a dimension that contains date columns, but in this post I only focus on the Time part of it, which means hours, minutes, and seconds, which makes this dimension as a Time Dimension. But if you want to know more about Date dimension, follow my previous post here.
In this post you will see the script to Create the Time Dimension structure, and also the script to fill the dimension with members.
The Time Dimension that I work on it, has columns as below:
Script for creating the structure of time dimension:
CREATE TABLE [dbo].[DimTime](
[TimeKey] [int] NOT NULL,
[Hour24] [int] NULL,
[Hour24ShortString] [varchar](2) NULL,
[Hour24MinString] [varchar](5) NULL,
[Hour24FullString] [varchar](8) NULL,
[Hour12] [int] NULL,
[Hour12ShortString] [varchar](2) NULL,
[Hour12MinString] [varchar](5) NULL,
[Hour12FullString] [varchar](8) NULL,
[AmPmCode] [int] NULL,
[AmPmString] [varchar](2) NOT NULL,
[Minute] [int] NULL,
[MinuteCode] [int] NULL,
[MinuteShortString] [varchar](2) NULL,
[MinuteFullString24] [varchar](8) NULL,
[MinuteFullString12] [varchar](8) NULL,
[HalfHour] [int] NULL,
[HalfHourCode] [int] NULL,
[HalfHourShortString] [varchar](2) NULL,
[HalfHourFullString24] [varchar](8) NULL,
[HalfHourFullString12] [varchar](8) NULL,
[Second] [int] NULL,
[SecondShortString] [varchar](2) NULL,
[FullTimeString24] [varchar](8) NULL,
[FullTimeString12] [varchar](8) NULL,
[FullTime] [time](7) NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Structure of time dimension above contains few columns for banding, such as HalfHour. but for adding more banding on minutes and seconds you should change the structure and script. I will work on writing a post on hours, minutes, and seconds banding for time dimension in the future to cover that.
as You see in the columns structure, there are codes, short string and full strings for many columns that prevent you to add more string and data conversion t-sql commands on the top of this data. Grain for this dimension is Seconds, so this means that the table will contain 24 hours * 60 Minutes * 60 Seconds = 86400 members.
Script for generating members (records) for Time Dimension:
declare @hour int
declare @minute int
declare @second int
set @hour=0
while @hour<24
begin
set @minute=0
while @minute<60
begin
set @second=0
while @second<60
begin
INSERT INTO [dbo].[DimTime]
([TimeKey]
,[Hour24]
,[Hour24ShortString]
,[Hour24MinString]
,[Hour24FullString]
,[Hour12]
,[Hour12ShortString]
,[Hour12MinString]
,[Hour12FullString]
,[AmPmCode]
,[AmPmString]
,[Minute]
,[MinuteCode]
,[MinuteShortString]
,[MinuteFullString24]
,[MinuteFullString12]
,[HalfHour]
,[HalfHourCode]
,[HalfHourShortString]
,[HalfHourFullString24]
,[HalfHourFullString12]
,[Second]
,[SecondShortString]
,[FullTimeString24]
,[FullTimeString12]
,[FullTime])
select
(@hour*10000) + (@minute*100) + @second as TimeKey,
@hour as [Hour24],
right(‘0’+convert(varchar(2),@hour),2) [Hour24ShortString],
right(‘0’+convert(varchar(2),@hour),2)+‘:00’ [Hour24MinString],
right(‘0’+convert(varchar(2),@hour),2)+‘:00:00’ [Hour24FullString],
@hour%12 as [Hour12],
right(‘0’+convert(varchar(2),@hour%12),2) [Hour12ShortString],
right(‘0’+convert(varchar(2),@hour%12),2)+‘:00’ [Hour12MinString],
right(‘0’+convert(varchar(2),@hour%12),2)+‘:00:00’ [Hour12FullString],
@hour/12 as [AmPmCode],
case when @hour<12 then ‘AM’ else ‘PM’ end as [AmPmString],
@minute as [Minute],
(@hour*100) + (@minute) [MinuteCode],
right(‘0’+convert(varchar(2),@minute),2) [MinuteShortString],
right(‘0’+convert(varchar(2),@hour),2)+‘:’+
right(‘0’+convert(varchar(2),@minute),2)+‘:00’ [MinuteFullString24],
right(‘0’+convert(varchar(2),@hour%12),2)+‘:’+
right(‘0’+convert(varchar(2),@minute),2)+‘:00’ [MinuteFullString12],
@minute/30 as [HalfHour],
(@hour*100) + ((@minute/30)*30) [HalfHourCode],
right(‘0’+convert(varchar(2),((@minute/30)*30)),2) [HalfHourShortString],
right(‘0’+convert(varchar(2),@hour),2)+‘:’+
right(‘0’+convert(varchar(2),((@minute/30)*30)),2)+‘:00’ [HalfHourFullString24],
right(‘0’+convert(varchar(2),@hour%12),2)+‘:’+
right(‘0’+convert(varchar(2),((@minute/30)*30)),2)+‘:00’ [HalfHourFullString12],
@second as [Second],
right(‘0’+convert(varchar(2),@second),2) [SecondShortString],
right(‘0’+convert(varchar(2),@hour),2)+‘:’+
right(‘0’+convert(varchar(2),@minute),2)+‘:’+
right(‘0’+convert(varchar(2),@second),2) [FullTimeString24],
right(‘0’+convert(varchar(2),@hour%12),2)+‘:’+
right(‘0’+convert(varchar(2),@minute),2)+‘:’+
right(‘0’+convert(varchar(2),@second),2) [FullTimeString12],
convert(time,right(‘0’+convert(varchar(2),@hour),2)+‘:’+
right(‘0’+convert(varchar(2),@minute),2)+‘:’+
right(‘0’+convert(varchar(2),@second),2)) as [FullTime]
set @second=@second+1
end
set @minute=@minute+1
end
set @hour=@hour+1
end
All you need to do is to create the Time dimension first (first script), and then fill it with members (second script)
Data Rows in the Time Dimension after loading data will be look like following images:
Image 1:
Image 2:
Image 3:
Hope it helps you all.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.