Time Dimension with Time Bands at Seconds Granularity in Energy BI and SSAS Tabular

I wrote another posts on this matter previously, you’ll find 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 in every of my clients required the “Time” dimension at seconds granularity which inspired me to jot 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 all the way down to second. on this fast submit I bounce on to the purpose and present you find out how to generate the “Time” dimension in three other 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.Sort),
    #"Hour Added" = Desk.AddColumn(#"Time Column Added", "Hour", every Time.Hour([Time]), Int64.Sort),
    #"Minute Added" = Desk.AddColumn(#"Hour Added", "Minute", every Time.Minute([Time]), Int64.Sort),
    #"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.Sort),
    #"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.Sort),
#"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.Sort),
#"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.Sort),
#"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.Sort),
    #"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:

Working the next T-SQL script in SSMS creates a brand new “Time” desk inside the present database. The code beneath doesn’t test 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 will have a greater thought? Please share it with us within the feedback part beneath.

Associated