I wrote another posts on this subject up to now, you will discover them right here and right here. Within the first submit I clarify find out how to create “Time” dimension with time bands at minutes granularity. Then one among my clients required the “Time” dimension at seconds granularity which inspired me to write down the second blogpost. Within the second blogpost although I didn’t do time bands, so right here I’m, writing the third submit which is a variation of the second submit supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min whereas the grain of the “Time” dimension is right down to second. on this fast submit I soar on to the purpose and present you find out how to generate the “Time” dimension in three alternative ways, utilizing T-SQL in SQL Server, utilizing Energy Question (M) and DAX. Right here it’s then:

Time Dimension at Second Grain with Energy Question (M) Supporting Time Bands:

Copy/paste the code beneath in Question Editor’s Superior Editor to generate Time dimension in Energy Question:

let
Supply = Desk.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Desk.RenameColumns(Supply,{{"Column1", "ID"}}),
#"Time Column Added" = Desk.AddColumn(#"Renamed Columns", "Time", every Time.From(#datetime(1970,1,1,0,0,0) + #period(0,0,0,[ID])), Time.Kind),
    #"Hour Added" = Desk.AddColumn(#"Time Column Added", "Hour", every Time.Hour([Time]), Int64.Kind),
    #"Minute Added" = Desk.AddColumn(#"Hour Added", "Minute", every Time.Minute([Time]), Int64.Kind),
    #"5 Min Band Added" = Desk.AddColumn(#"Minute Added", "5 Min Band", every Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #period(0, 0, (Quantity.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Kind),
    #"15 Min Band Added" = Desk.AddColumn(#"5 Min Band Added", "15 Min Band", every Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #period(0, 0, (Quantity.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Kind),
#"30 Min Band Added" = Desk.AddColumn(#"15 Min Band Added", "30 Min Band", every Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #period(0, 0, (Quantity.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Kind),
#"45 Min Band Added" = Desk.AddColumn(#"30 Min Band Added", "45 Min Band", every Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #period(0, 0, (Quantity.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Kind),
#"60 Min Band Added" = Desk.AddColumn(#"45 Min Band Added", "60 Min Band", every Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #period(0, 0, (Quantity.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Kind),
    #"Eliminated Different Columns" = Desk.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
    #"Eliminated Different Columns"

Time Dimension at Seconds Grain with DAX Supporting Time Bands:

Create a calculated desk utilizing the DAX expressions beneath:

Time (DAX) = 
SELECTCOLUMNS(
    GENERATESERIES(1/86400, 1, TIME(0, 0, 1))
    , "Time", [Value]
    , "Hour", HOUR ( [Value] )
    , "Minute", MINUTE ( [Value] )
    , "5 Min Band",  TIME(HOUR([Value]), FLOOR(MINUTE([Value])/5, 1) * 5, 0) + TIME(0, 5, 0)
    , "15 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/15, 1) * 15, 0) + TIME(0, 15, 0)
    , "30 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/30, 1) * 30, 0) + TIME(0, 30, 0)
    , "45 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/45, 1) * 45, 0) + TIME(0, 45, 0)
    , "60 Min Band", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/60, 1) * 60, 0) + TIME(0, 60, 0)
 )

Time Dimension at Seconds Grain with T-SQL Supporting Time Bands:

Operating the next T-SQL script in SSMS creates a brand new “Time” desk throughout the present database. The code beneath doesn’t verify for an present “Time” desk although.

;WITH GenerateInt AS 
(SELECT 1 ID
UNION ALL
SELECT id + 1
FROM GenerateInt
WHERE id < 86400)
, GenerateTime AS
(
SELECT Dateadd(second, id, '1900-01-01') [Time]
FROM GenerateInt)
SELECT [Time]
	, DATEPART(HOUR, [Time]) [Hour]
	, DATEPART(MINUTE, [Time]) [Minute]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 5) * 5) + 5), '1900-01-01') [5 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 15) * 15) + 15), '1900-01-01') [15 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 30) * 30) + 30), '1900-01-01') [30 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 45) * 45) + 45), '1900-01-01') [45 Minute Band]
	, DATEADD(MINUTE, (((DATEPART(MINUTE, [Time]) / 60) * 60) + 60), '1900-01-01') [60 Minute Band]
INTO [Time]
FROM GenerateTime
OPTION (MAXRECURSION 0)

Do you discover this submit helpful? Have you ever used any of those strategies earlier than to deal with the “Time” dimension? Do you’ve a greater concept? Please share it with us within the feedback part beneath.