Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Create & Populate Time Dimension with 24 Hour+ Values

Rate me:
Please Sign up or sign in to vote.
5.00/5 (31 votes)
5 Sep 2013CPOL1 min read 116K   3.4K   21   14
Create time dimension with 24 hour plus values and time buckets in your data warehouse

Introduction

A common task most of us face while setting up a new data warehouse is creating a time dimension.

This tip will especially help those people who work in Business Intelligence and whenever as a starting point, they need to set new data warehouse. During this time, they need to create and fill their time dimension with the necessary values.

I have searched the internet to find T-SQL script which can create and fill time dimension with 24 hour plus values. I did not find any readymade script, then I invested my time to create this script and am now sharing with all so that it can help everyone.

The given time dimension script will create table of time dimension and populate it with appropriate values. It also creates time buckets in table and fills it with group values, so that the user can perform aggregation of data using various combinations of hourly time buckets or day time buckets and they can do analysis of data using these time buckets and can do study of trend over the entire day.

Image 1

Using the Code

Follow the given steps to create time dimension:

  1. Open SQL Server Management Studio.
  2. Connect database engine.
  3. Open new query editor.
  4. Copy paste the script given below in new query editor window.
  5. Press F5 to run the given SQL script.
SQL
/*Created by Mubin M. Shaikh */ 
--Create Test Database named as Test_DW 
Create Database [Test_DW] 
GO 
--Choose the database Test_DW 
USE [Test_DW] 
GO 
/****** Create Time Dimension Table In Test_DW ******/ 
/****** Create Table [dbo].[DimTime] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[DimTime]( 
[TimeKey] [int] NOT NULL, 
[TimeAltKey] [int] NOT NULL, 
[Time30] [varchar](8) NOT NULL, 
[Hour30] [tinyint] NOT NULL, 
[MinuteNumber] [tinyint] NOT NULL, 
[SecondNumber] [tinyint] NOT NULL, 
[TimeInSecond] [int] NOT NULL, 
[HourlyBucket] varchar(15)not null, 
[DayTimeBucketGroupKey] int not null, 
[DayTimeBucket] varchar(100) not 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] 
GO 
SET ANSI_PADDING OFF 
GO 
/***** Create Stored procedure In Test_DW and Run SP To Fill Time Dimension with Values****/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE PROCEDURE [dbo].[FillDimTime] 
as 
BEGIN 
--Specify Total Number of Hours You need to fill in Time Dimension 
DECLARE @Size INTEGER 
--iF @Size=32 THEN This will Fill values Upto 32:59 hr in Time Dimension 
Set @Size=23 
DECLARE @hour INTEGER 
DECLARE @minute INTEGER 
DECLARE @second INTEGER 
DECLARE @k INTEGER 
DECLARE @TimeAltKey INTEGER 
DECLARE @TimeInSeconds INTEGER 
DECLARE @Time30 varchar(25) 
DECLARE @Hour30 varchar(4) 
DECLARE @Minute30 varchar(4) 
DECLARE @Second30 varchar(4) 
DECLARE @HourBucket varchar(15) 
DECLARE @HourBucketGroupKey int 
DECLARE @DayTimeBucket varchar(100) 
DECLARE @DayTimeBucketGroupKey int 
SET @hour = 0 
SET @minute = 0 
SET @second = 0 
SET @k = 0 
SET @TimeAltKey = 0 
WHILE(@hour<= @Size ) 
BEGIN 
if (@hour <10 ) 
begin 
set @Hour30 = '0' + cast( @hour as varchar(10)) 
end 
else 
begin 
set @Hour30 = @hour 
end 
--Create Hour Bucket Value 
set @HourBucket= @Hour30+':00' +'-' +@Hour30+':59' 
WHILE(@minute <= 59) 
BEGIN 
WHILE(@second <= 59) 
BEGIN 
set @TimeAltKey = @hour *10000 +@minute*100 +@second 
set @TimeInSeconds =@hour * 3600 + @minute *60 +@second 
If @minute <10 
begin 
set @Minute30 = '0' + cast ( @minute as varchar(10) ) 
end 
else 
begin 
set @Minute30 = @minute 
end 
if @second <10 
begin 
set @Second30 = '0' + cast ( @second as varchar(10) ) 
end 
else 
begin 
set @Second30 = @second 
end 
--Concatenate values for Time30 
set @Time30 = @Hour30 +':'+@Minute30 +':'+@Second30 
--DayTimeBucketGroupKey can be used in Sorting of DayTime Bucket In proper Order 
SELECT @DayTimeBucketGroupKey = 
CASE 
WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) THEN 0 
WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) THEN 1 
WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) THEN 2 
WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) THEN 3 
WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959)THEN 4 
WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)THEN 5 
WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959) THEN 6 
WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)THEN 7 
WHEN (@TimeAltKey >= 240000) THEN 8 
END 
--print @DayTimeBucketGroupKey 
-- DayTimeBucket Time Divided in Specific Time Zone 
-- So Data can Be Grouped as per Bucket for Analyzing as per time of day 
SELECT @DayTimeBucket = 
CASE 
WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) _
THEN 'Late Night (00:00 AM To 02:59 AM)' 
WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) _
THEN 'Early Morning(03:00 AM To 6:59 AM)' 
WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) _
THEN 'AM Peak (7:00 AM To 8:59 AM)' 
WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) _
THEN 'Mid Morning (9:00 AM To 11:59 AM)' 
WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959) _
THEN 'Lunch (12:00 PM To 13:59 PM)' 
WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)_
THEN 'Mid Afternoon (14:00 PM To 15:59 PM)' 
WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959)_
THEN 'PM Peak (16:00 PM To 17:59 PM)' 
WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)_
THEN 'Evening (18:00 PM To 23:59 PM)' 
WHEN (@TimeAltKey >= 240000) THEN 'Previous Day Late Night _
(24:00 PM to '+cast( @Size as varchar(10)) +':00 PM )' 
END 
-- print @DayTimeBucket 
INSERT into DimTime (TimeKey,TimeAltKey,[Time30] ,[Hour30] ,_
[MinuteNumber],[SecondNumber],[TimeInSecond],[HourlyBucket],_
DayTimeBucketGroupKey,DayTimeBucket) 
VALUES (@k,@TimeAltKey ,@Time30 ,@hour ,@minute,@Second , _
@TimeInSeconds,@HourBucket,@DayTimeBucketGroupKey,@DayTimeBucket ) 
SET @second = @second + 1 
SET @k = @k + 1 
END 
SET @minute = @minute + 1 
SET @second = 0 
END 
SET @hour = @hour + 1 
SET @minute =0 
END 
END 
Go 
Exec [FillDimTime] 
go 
select * from DimTime// 

Enjoy T-SQLization.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Cybage Software Pvt. Ltd.
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Microsoft Certified Technology Specialist with more than 16+ years of expertise to architect and implement effective solutions for Data Analytics, Reporting and Data Visualization solutioning need on Azure Cloud or On-Premise

Technology :
Azure (Data Lake, Data Factory, Synapse Analytics, Databricks, SQL),
Microsoft BI (SSIS, SSAS, SSRS, SQL-Server), C#.Net, Pentaho,
Data Warehousing, Dimension modelling, Snowflake DW, SQL DW, MySQL
Data Visualization using (Tableau, Power BI, QlikView, Pentaho),
Domain : Sales, Retail, CRM, Public Transport, Media & Entertainment, Insurance
Data Integration and Analytics Experience with MS. Dynamic CRM, Salesforce CRM, Dataverse, SAP- FI, Dynamics AX etc.

Linked In Profile:
Click Here to View Linked In Profile

Change will not come if we keep waiting for some other person !!, or keep waiting for some other time !!, We are the one we are waiting for, We are the change that we are looking for.

Comments and Discussions

 
QuestionMy vote of 5 Pin
R2B219-Oct-21 11:39
R2B219-Oct-21 11:39 
GeneralMy vote of 5 Pin
Member 1497376424-Oct-20 4:37
Member 1497376424-Oct-20 4:37 
QuestionQuestion: Deviation in the fact table Pin
Member 127761144-Oct-16 8:06
Member 127761144-Oct-16 8:06 
AnswerRe: Question: Deviation in the fact table Pin
Member 127761144-Oct-16 8:13
Member 127761144-Oct-16 8:13 
GeneralMy vote of 5 Pin
Member 1190756013-Aug-15 8:24
Member 1190756013-Aug-15 8:24 
Questiondont understand Pin
Member 1039196930-Mar-15 1:09
Member 1039196930-Mar-15 1:09 
Generalthank you! Pin
PBrent29-Jan-15 3:46
PBrent29-Jan-15 3:46 
QuestionInfo About ETL, DataMapping, Pin
Member 1139123521-Jan-15 0:58
Member 1139123521-Jan-15 0:58 
QuestionThanks heaps Pin
Hendrikkie17-Sep-14 17:49
Hendrikkie17-Sep-14 17:49 
QuestionAwesome! Pin
Patrick.MB15-Aug-14 18:24
Patrick.MB15-Aug-14 18:24 
GeneralMy vote of 5 Pin
John Tolar14-Mar-14 2:51
John Tolar14-Mar-14 2:51 
Very Helpful
GeneralMy vote of 5 Pin
ssnasp19-Oct-13 23:26
ssnasp19-Oct-13 23:26 
GeneralMy vote of 5 Pin
Suket shah18-Sep-13 0:00
professionalSuket shah18-Sep-13 0:00 
GeneralDeeksha Pin
Mubin M. Shaikh26-Aug-13 1:07
professionalMubin M. Shaikh26-Aug-13 1:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.