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 put up I present you easy technique 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 have to add time bands (time buckets) examine this out for extra particulars.

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

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

Time Dimension in Seconds Grain with DAX:

Run the DAX expression beneath 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
                        )

In my earlier put up about Time dimension you see a special DAX expression to create Time dimension in Minutes granularity. You could ask why I used a special construction right here? Properly, the reason being that TIME operate has limitation on accepting numbers greater than 32,767. Due to this fact, when you use the beneath DAX expression you’ll get the “An argument of operate ‘TIME’ has the improper knowledge kind or the result’s too giant or too small.” error message. This limitation in inherited from TIME operate in Excel.

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)

All achieved!