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 <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-more="more" data-wp-more-text="" class="wp-more-tag mce-wp-more" alt="" title="Read more..." data-mce-resize="false" data-mce-placeholder="1" /><10
begin
set @secTemp = '0' + cast ( @second as varchar(10) )
end
else
begin
set @secTemp = @second
end
--Concatenate values for Time30
set @Time = @hourTemp +':'+@minTemp +':'+@secTemp

INSERT into DimTime (TimeKey,TimeAltKey,[Time] ,[HourNumber] ,[MinuteNumber],[SecondNumber],[TimeInSecond])
VALUES (@TimeKey,@TimeAltKey ,@Time ,@hour ,@minute,@Second , @TimeInSeconds)
SET @second = @second + 1
SET @TimeKey = @TimeKey + 1
END
SET @minute = @minute + 1
SET @second = 0
END
SET @hour = @hour + 1
SET @minute =0
END

UPDATE DimTime SET [TimeMinutes] = LEFT([Time],5)
UPDATE DimTime SET [TimeHours] = LEFT([Time],2)

END

GO

Share this:
Twitter
Facebook
LinkedIn
WhatsApp
Email


	
Related