Date dimension has been mentioned quite a bit on the Web and yow will discover plenty of beneficial articles round it right here and there. However what if it is advisable to analyse your knowledge in time degree? A buyer has a requirement to analyse their knowledge in Minutes degree. Which means the granularity of the actual fact desk can be at minute degree. So, in the event that they retailer the information of their transactional database in seconds degree, then we have to combination that knowledge to minutes degree. I don’t wish to go there, simply keep in mind that the granularity of your truth desk is one thing that you should take into consideration on the very first steps. Generally, if not all circumstances, you’d be higher to have a separate Time dimension. Then it is advisable to have a TimeID or Time column in your truth desk to have the ability to create a relationship between the Time dimension and the actual fact desk. On this publish I present you two methods to create Time dimension in Energy BI:

• Creating Time dimension with DAX
• Creating Time dimension with Energy Question (M)

Alternatively, you possibly can handle the Time dimension within the supply system like SQL Server. Proceed studying and also you’ll discover a T-SQL codes as complementary.

The strategies that I clarify right here might be completed in SSAS Tabular mannequin and Azure Evaluation Providers as nicely.

To observe the steps of constructing the take a look at mannequin it is advisable to have:

• Energy BI Desktop: Obtain the newest model from right here
• A pattern truth desk containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it out there so that you can obtain in Excel format (discover the obtain hyperlink on the backside of the publish)

Initially, you want to take a look on the desk construction of the “FactInternetSales_withTime.xlsx” file.

As you possibly can see the desk accommodates “OrderDateTime” column in DateTime format. What we have to do is to separate that column to 2 columns, one holding “OrderDate” knowledge and the opposite holds “OrderTime” knowledge. Then you possibly can create the “Time” dimension with DAX or Energy Question (M), or each for those who like ?. You’ll then create a relationship between the “Time” dimension and the actual fact desk.

Let’s begin.

• Open Energy BI Desktop
• Get knowledge from Excel and cargo knowledge from “FactInternetSales_WithTime” Excel file
• Click on “Edit Queries”
• Within the Question Editor web page click on “FactInternetSales_WithTime”
• Scroll to very finish of the desk and discover “OrderDateTime” column. As you see the information kind is DateTime
• Click on “Add Columns” tab then click on “Customized Column” so as to add a brand new column. We’re going to add “OrderDate” column
• Sort “OrderDate” as “New column identify”
• Sort the next Energy Question operate to get the date a part of the OrderDateTime then click on OK

=Date.From([OrderDateTime])

• Now add one other column utilizing the identical methodology and identify it “OrderTime” with the next Energy Question operate

=Time.From([OrderDateTime])

• Now we have to convert the information forms of the brand new columns to Date and Time respectively. To take action choose each columns and click on “Detect Information Sort” from “Rework” tab

Within the subsequent steps we create a Time dimension utilizing DAX and Energy Question (M). Then we create a relationship between the “FactInternetSales_WithTime” and the Time dimension.

When you’re prepared to create the Time dimension with DAX then:

• In Energy BI Desktop click on “New Desk” from “Modeling” tab from the ribbon
• Copy and paste the beneath DAX code then press Enter

Time in DAX =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, 1440, 1 )
, "TimeValue", TIME ( 0, [Value], 0 )
)
, "ID", [Value]
, "Time", [TimeValue]
, "Hour", HOUR ( [TimeValue] )
, "Minute", MINUTE ( [TimeValue] )
, "5 Min Decrease Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Decrease Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Decrease Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Decrease Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Decrease Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
, "5 Min Higher Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Higher Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Higher Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Higher Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Higher Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
)

The code above creates a desk primarily based on a listing of numbers from 1 to 1440 with interval of 1. This integer quantity reveals the variety of minutes per day. So in case you need a Time dimension in Second degree then it is advisable to create a listing of seconds from 1 to 86,400.

Click on on the “Information” tab to see the information. When you have a look at the “Decrease Band” and “Higher Band” columns you’ll discover that the values of the “Decrease Band” columns begin from 0 whereas the values of the “Higher Band” columns begin with the band quantity. I created each columns to cowl totally different situations when the shopper prefers to start out from 0 you then simply merely take away the “Higher Band” columns or the opposite approach round.

You might already observed that the information kind of the “Time” column is DateTime which isn’t proper. To repair this, simply click on the “Time” column and alter the information kind to “Time” from “Modeling” tab

To verify the Time reveals within the right order when added to the visuals I alter the format to “HH:mm:ss”.

You should do the identical for all different time columns. The end result ought to seem like the screenshot beneath:

Now you’re good to create the connection between the “Time” dimension and the “FactInternetSales_WithTime” by connecting “OrderTime” from the actual fact desk to “Time” column type the Time dimension.

You simply must create a clean question in “Question Editor” and replica/paste the next Energy Question codes.

let
Supply = Desk.FromList({1..1440}, 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,[ID],0))),
#"5 Min Decrease Band Added" = Desk.AddColumn(#"Time Column Added", "5 Min Decrease Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundDown([ID]/5) * 5, 0))),
#"15 Min Decrease Band Added" = Desk.AddColumn(#"5 Min Decrease Band Added", "15 Min Decrease Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundDown([ID]/15) * 15, 0))),
#"30 Min Decrease Band Added" = Desk.AddColumn(#"15 Min Decrease Band Added", "30 Min Decrease Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundDown([ID]/30) * 30, 0))),
#"45 Min Decrease Band Added" = Desk.AddColumn(#"30 Min Decrease Band Added", "45 Min Decrease Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundDown([ID]/45) * 45, 0))),
#"60 Min Decrease Band Added" = Desk.AddColumn(#"45 Min Decrease Band Added", "60 Min Decrease Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundDown([ID]/60) * 60, 0))),
#"5 Min Higher Band Added" = Desk.AddColumn(#"60 Min Decrease Band Added", "5 Min Higher Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundUp([ID]/5) * 5, 0))),
#"15 Min Higher Band Added" = Desk.AddColumn(#"5 Min Higher Band Added", "15 Min Higher Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundUp([ID]/15) * 15, 0))),
#"30 Min Higher Band Added" = Desk.AddColumn(#"15 Min Higher Band Added", "30 Min Higher Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundUp([ID]/30) * 30, 0))),
#"45 Min Higher Band Added" = Desk.AddColumn(#"30 Min Higher Band Added", "45 Min Higher Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundUp([ID]/45) * 45, 0))),
#"60 Min Higher Band Added" = Desk.AddColumn(#"45 Min Higher Band Added", "60 Min Higher Band", every Time.From(#datetime(1970,1,1,0,0,0)+#period(0, 0, Quantity.RoundUp([ID]/60) * 60, 0))),
#"Modified Sort" = Desk.TransformColumnTypes(#"60 Min Higher Band Added",{{"Time", kind time}, {"5 Min Decrease Band", kind time}, {"15 Min Decrease Band", kind time}, {"30 Min Decrease Band", kind time}, {"45 Min Decrease Band", kind time}, {"60 Min Decrease Band", kind time}, {"5 Min Higher Band", kind time}, {"15 Min Higher Band", kind time}, {"30 Min Higher Band", kind time}, {"45 Min Higher Band", kind time}, {"60 Min Higher Band", kind time}})
in
#"Modified Sort"

Copy/paste the beneath T-SQL in SSMS to get the Time dimension in SQL Server. You’ll be able to create a DimTime desk f you uncomment the commented line and run the code.

WITH cte 
AS (SELECT 0 ID 
UNION ALL 
SELECT ID + 1 
FROM cte 
WHERE ID < 1439) 
SELECT ID 
, CONVERT(CHAR(5), Dateadd(minute, ID, '1900-01-01'), 108) [Time] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 5 ) * 5, '1900-01-01'), 108) [5 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 15 ) * 15, '1900-01-01'), 108) [15 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 30 ) * 30, '1900-01-01'), 108) [30 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 45 ) * 45, '1900-01-01'), 108) [45 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 60 ) * 60, '1900-01-01'), 108) [60 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Forged(ID AS FLOAT) / 5) * 5, '1900-01-01'), 108) [5 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Forged(ID AS FLOAT) / 15) * 15, '1900-01-01'), 108) [15 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Forged(ID AS FLOAT) / 30) * 30, '1900-01-01'), 108) [30 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Forged(ID AS FLOAT) / 45) * 45, '1900-01-01'), 108) [45 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Forged(ID AS FLOAT) / 60) * 60, '1900-01-01'), 108) [60 Minutes Lower Band] 
--INTO DimTime
FROM cte 
OPTION (maxrecursion 0)

Then you possibly can load the DimTime to Energy BI Desktop and create the required relationships.

Now you possibly can simply analyse and visualise your knowledge in Energy BI. As you possibly can see in all totally different implementations of the Time dimension whatever the platform, you at all times have totally different columns to help totally different time bands. If you wish to have dynamic timeband, then you must unpivot the time dimension. I’d like to offer credit score to “Patrick Leblanc” from “Man in a Dice” who explains how one can create dynamic axis in Energy BI right here. That is useful notably in these situations that you just like to modify between totally different timebands and see the outcomes instantly. I might not clarify the approach once more as Patric explains it fairly clear on a step-by-step foundation, so I encourage you to look at his video for those who’d wish to be taught extra. I simply put the DAX code collectively for these of you who’re questioning the best way to unpivot the desk in DAX. It will turn into useful in case you are engaged on a SSAS Tabular 2016 (or earlier) or for those who’re engaged on a pure PowerPivot mannequin and also you don’t have entry to Energy Question to leverage the UNPIVOT performance in M. Right here is an instance of visualising knowledge on minute degree primarily based on numerous timebands.

Unpivot in DAX

On the time of penning this publish, there isn’t any built-in UNPIVOT operate in DAX. So we have now to by some means faux it. The beneath DAX code creates a calculated desk primarily based on the Time dimension we created earlier. Once more, the entire thing will get extra clear whenever you obtain the Energy BI pattern and take a look on the mannequin construction.

Time in DAX Unpivot = UNION(
          SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID], "Time Band", "Time", "Time", 'Time in DAX'[Time])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "Time", "Time", 'Time in DAX'[Time])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "5 Min Decrease Band", "5 Min Decrease Band", 'Time in DAX'[5 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "15 Min Decrease Band", "15 Min Decrease Band", 'Time in DAX'[15 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "30 Min Decrease Band", "30 Min Decrease Band", 'Time in DAX'[30 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "45 Min Decrease Band", "45 Min Decrease Band", 'Time in DAX'[45 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "60 Min Decrease Band", "60 Min Decrease Band", 'Time in DAX'[60 Min Lower Band])
          
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "5 Min Higher Band", "5 Min Higher Band", 'Time in DAX'[5 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "15 Min Higher Band", "15 Min Higher Band", 'Time in DAX'[15 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "30 Min Higher Band", "30 Min Higher Band", 'Time in DAX'[30 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "45 Min Higher Band", "45 Min Higher Band", 'Time in DAX'[45 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "60 Min Higher Band", "60 Min Higher Band", 'Time in DAX'[60 Min Upper Band])
          
                )

Click on right here to obtain the Excel, PBIX and SQL recordsdata.