HomeBusiness IntelligenceFast Ideas: Time Dimension with Time Bands at Seconds Granularity in Energy...

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


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

I wrote another posts on this matter up to now, you will discover them right here and right here. Within the first publish I clarify create “Time” dimension with time bands at minutes granularity. Then certainly one of my prospects 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 publish which is a variation of the second publish 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 publish I leap on to the purpose and present you 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.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:

Operating the next T-SQL script in SSMS creates a brand new “Time” desk throughout the present database. The code beneath doesn’t examine for an current “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 publish helpful? Have you ever used any of those strategies earlier than to deal with the “Time” dimension? Do you might have a greater concept? Please share it with us within the feedback part beneath.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments