Saturday, May 21, 2022
HomeBusiness IntelligenceFast Suggestions: Time Dimension with Granularity of Seconds in Energy BI and...

Fast Suggestions: Time Dimension with Granularity of Seconds in Energy BI and Evaluation Companies Tabular Fashions


Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models
Picture by Markus Spiske

In an article I posted some time again I confirmed completely different strategies of making Time dimension in Energy BI and Tabular fashions. The Time dimension I defined was in Minutes. On this submit I present you easy option to create Time dimension supporting Seconds. As this can be a fast tip, I solely present you the way to get the Time and ID columns within the Time dimension. If you want to add time bands (time buckets) verify this out for extra particulars.

Time Dimension in Seconds Grain with Energy Question (M):

Copy/paste the code under in 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)+#length(0,0,0,[ID]))),
#"Modified Kind" = Desk.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Kind}, {"Time", kind time}})
in
#"Modified Kind" 
Time Dimension in Power Query

Time Dimension in Seconds Grain with DAX:

Run the DAX expression under in a brand new calculated Desk in Energy BI or SSAS Tabular mannequin:

Time in DAX = ADDCOLUMNS(
                       GENERATESERIES(1, 86400, 1)
                        , "Time", TIME(0, 0, 0) + [Value]/86400
                        )
Time Dimension in Seconds Level with DAX

In my earlier submit about Time dimension you see a unique DAX expression to create Time dimension in Minutes granularity. It’s possible you’ll ask why I used a unique construction right here? Effectively, the reason being that TIME perform has limitation on accepting numbers greater than 32,767. Subsequently, if you happen to use the under DAX expression you’ll get the “An argument of perform ‘TIME’ has the mistaken information kind or the result’s too giant or too small.” error message. This limitation in inherited from TIME perform in Excel.

An argument of function 'TIME' has the wrong data type or the result is too large or too small.
Time in DAX with Error = ADDCOLUMNS(
                                    GENERATESERIES(1, 86400, 1)
                                    , "Time", TIME(0, 0, [Value])
                                    )

Right here is one other intelligent approach of producing Time dimension in Seconds Jeffrey Wang posted on LinkedIn:

Time in DAX Jeffrey Model = GENERATESERIES(1/86400, 1, TIME(0, 0, 1))

Time Dimension In Seconds Grain with T-SQL:

Run the next T-SQL script in SSMS:

WITH cte
AS (SELECT 1 ID
UNION ALL
SELECT id + 1
FROM cte
WHERE id < 86400)
SELECT id
, CONVERT(CHAR(8), Dateadd(second, id, ‘1900-01-01’), 108) [Time]
FROM cte
OPTION (maxrecursion 0)

Time Dimension in Seconds Level with T-SQL

All executed!

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments