Populating a Time Dimension

A ready-made script that I have modified to create and populate a Kimball Time dimension.

This script will create a time dimension and populate it with different levels of granularity: second, minute, hour.


--Create the time dim table

CREATE TABLE [dbo].[DimTime](
[TimeKey] [int] NOT NULL,
[TimeAltKey] [int] NOT NULL,
[Time] [varchar](8) NOT NULL,
[TimeMinutes] [varchar](5) NULL,
[TimeHours] [varchar](2) NULL,
[HourNumber] [tinyint] NOT NULL,
[MinuteNumber] [tinyint] NOT NULL,
[SecondNumber] [tinyint] NOT NULL,
[TimeInSecond] [int] NOT NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TimeKey] ASC
)
)

--Script to populate the time dimension
CREATE PROCEDURE [dbo].[p_InsertDimTime]
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 @TimeKey INTEGER
DECLARE @TimeAltKey INTEGER
DECLARE @TimeInSeconds INTEGER
DECLARE @Time varchar(25)
DECLARE @hourTemp varchar(4)
DECLARE @minTemp varchar(4)
DECLARE @secTemp varchar(4)
SET @hour = 0
SET @minute = 0
SET @second = 0
SET @TimeKey = 0
SET @TimeAltKey = 0
WHILE(@hour<= @Size )
BEGIN
if (@hour <10 )
begin
set @hourTemp = '0' + cast( @hour as varchar(10))
end
else
begin
set @hourTemp = @hour
end
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 @minTemp = '0' + cast ( @minute as varchar(10) )
end
else
begin
set @minTemp = @minute
end
if @second Continue reading "Populating a Time Dimension"