Script for Creating and Generating members for Time Dimension

Posted by on Jul 14, 2013 in Business Intelligence, SQL Server, T-SQL | No Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">