Script for Creating and Generating members for Time Dimension

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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. 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *