Tableau Reading Cte in Custom Sql Query

Date dimension has been discussed quite a lot on the Internet and you tin can find lots of valuable manufactures effectually it here and in that location. But what if you need to analyse your data in time level? A client has a requirement to analyse their data in Minutes level. This means that the granularity of the fact table would be at infinitesimal level. And then, if they store the data in their transactional database in seconds level, then nosotros need to aggregate that data to minutes level. I don't want to go there, just bear in mind that the granularity of your fact table is something that yous must think nearly at the very first steps. In most cases, if not all cases, yous'd be better to have a split Time dimension. And so you need to accept a TimeID or Fourth dimension cavalcade in your fact table to be able to create a relationship between the Time dimension and the fact table. In this post I evidence you ii ways to create Time dimension in Ability BI:

• Creating Time dimension with DAX
• Creating Fourth dimension dimension with Power Query (G)

Alternatively, y'all tin can take care of the Time dimension in the source system like SQL Server. Continue reading and y'all'll observe a T-SQL codes as complementary.

The techniques that I explain hither tin can be done in SSAS Tabular model and Azure Analysis Services as well.

Requirements:

To follow the steps of edifice the exam model you demand to have:

• Power BI Desktop: Download the latest version from here
• A sample fact table containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it available for you to download in Excel format (find the download link at the bottom of the post)

How it works

Get-go of all, you need to take a look at the table construction of the "FactInternetSales_withTime.xlsx" file.

As you can see the table contains "OrderDateTime" cavalcade in DateTime format. What nosotros need to practise is to dissever that column to two columns, one holding "OrderDate" data and the other holds "OrderTime" information. And then yous tin can create the "Time" dimension with DAX or Power Query (One thousand), or both if you like ?. Y'all will then create a relationship betwixt the "Fourth dimension" dimension and the fact table.

Let's start.

• Open Ability BI Desktop
• Get information from Excel and load data from "FactInternetSales_WithTime" Excel file
• Click "Edit Queries"
• In the Query Editor page click "FactInternetSales_WithTime"
• Curlicue to very stop of the table and find "OrderDateTime" cavalcade. Equally you see the data blazon is DateTime
• Click "Add Columns" tab then click "Custom Cavalcade" to add a new column. We are going to add "OrderDate" column
• Type "OrderDate" as "New column proper noun"
• Type the post-obit Ability Query function to get the date part of the OrderDateTime then click OK

=Date.From([OrderDateTime])

• Now add another column using the same method and proper noun information technology "OrderTime" with the following Power Query part

=Time.From([OrderDateTime])

• Now we need to convert the data types of the new columns to Date and Time respectively. To exercise then select both columns and click "Detect Data Blazon" from "Transform" tab

• Close & Apply

In the next steps we create a Time dimension using DAX and Power Query (Thousand). And so nosotros create a relationship between the "FactInternetSales_WithTime" and the Time dimension.

Time Dimension with DAX

If you're willing to create the Time dimension with DAX and so:

• In Power BI Desktop click "New Table" from "Modeling" tab from the ribbon
• Copy and paste the below DAX code and then printing Enter

Time in DAX = SELECTCOLUMNS ( ADDCOLUMNS ( GENERATESERIES ( one, 1440, one ) , "TimeValue", TIME ( 0, [Value], 0 ) ) , "ID", [Value] , "Time", [TimeValue] , "Hour", HOUR ( [TimeValue] ) , "Minute", Infinitesimal ( [TimeValue] ) , "v Min Lower Band", FORMAT (TIME ( 0, Floor ( Divide ( [Value], five ), one ) * five, 0 ), "hh:mm:ss") , "xv Min Lower Band", FORMAT (Time ( 0, Flooring ( DIVIDE ( [Value], fifteen ), i ) * fifteen, 0 ), "hh:mm:ss") , "30 Min Lower Band", FORMAT (Fourth dimension ( 0, FLOOR ( Separate ( [Value], thirty ), 1 ) * 30, 0 ), "hh:mm:ss") , "45 Min Lower Band", FORMAT (Time ( 0, FLOOR ( Split up ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss") , "60 Min Lower Band", FORMAT (Time ( 0, Flooring ( Dissever ( [Value], threescore ), 1 ) * threescore, 0 ), "hh:mm:ss") , "v Min Upper Band", FORMAT (Fourth dimension ( 0, CEILING ( DIVIDE ( [Value], 5 ), 1 ) * five, 0 ), "hh:mm:ss") , "15 Min Upper Band", FORMAT (Fourth dimension ( 0, CEILING ( DIVIDE ( [Value], 15 ), 1 ) * xv, 0 ), "hh:mm:ss") , "thirty Min Upper Band", FORMAT (Fourth dimension ( 0, CEILING ( DIVIDE ( [Value], 30 ), i ) * xxx, 0 ), "hh:mm:ss") , "45 Min Upper Band", FORMAT (Time ( 0, CEILING ( DIVIDE ( [Value], 45 ), i ) * 45, 0 ), "hh:mm:ss") , "60 Min Upper Band", FORMAT (Fourth dimension ( 0, CEILING ( DIVIDE ( [Value], threescore ), 1 ) * 60, 0 ), "hh:mm:ss") )

The code above creates a table based on a list of numbers from ane to 1440 with interval of 1. This integer number shows the number of minutes per 24-hour interval. So in case you want a Time dimension in 2d level and so you need to create a list of seconds from 1 to 86,400.

Click on the "Information" tab to see the data. If you lot look at the "Lower Band" and "Upper Band" columns you'll notice that the values of the "Lower Band" columns start from 0 while the values of the "Upper Ring" columns beginning with the band number. I created both columns to cover different scenarios when the client prefers to start from 0 and then you only simply remove the "Upper Ring" columns or the other manner around.

Yous may already noticed that the data type of the "Time" column is DateTime which is not right. To fix this, just click the "Time" column and change the data type to "Time" from "Modeling" tab

To make sure the Fourth dimension shows in the correct order when added to the visuals I change the format to "HH:mm:ss".

Y'all need to do the same for all other time columns. The result should expect like the screenshot below:

Now you are skillful to create the relationship betwixt the "Time" dimension and the "FactInternetSales_WithTime" past connecting "OrderTime" from the fact table to "Time" column form the Time dimension.

Create Fourth dimension Dimension with Power Query (K)

You merely need to create a blank query in "Query Editor" and copy/paste the following Power Query codes.

let Source = Table.FromList({1..1440}, Splitter.SplitByNothing()), #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}), #"Time Column Added" = Tabular array.AddColumn(#"Renamed Columns", "Time", each Fourth dimension.From(#datetime(1970,ane,1,0,0,0)+#elapsing(0,0,[ID],0))), #"5 Min Lower Ring Added" = Table.AddColumn(#"Time Cavalcade Added", "5 Min Lower Ring", each Time.From(#datetime(1970,i,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/five) * 5, 0))), #"15 Min Lower Band Added" = Table.AddColumn(#"5 Min Lower Band Added", "15 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/fifteen) * 15, 0))), #"xxx Min Lower Band Added" = Tabular array.AddColumn(#"xv Min Lower Band Added", "30 Min Lower Band", each Time.From(#datetime(1970,ane,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/30) * 30, 0))), #"45 Min Lower Band Added" = Table.AddColumn(#"30 Min Lower Ring Added", "45 Min Lower Band", each Fourth dimension.From(#datetime(1970,one,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/45) * 45, 0))), #"60 Min Lower Band Added" = Tabular array.AddColumn(#"45 Min Lower Ring Added", "lx Min Lower Ring", each Time.From(#datetime(1970,1,i,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/60) * 60, 0))), #"v Min Upper Band Added" = Table.AddColumn(#"60 Min Lower Ring Added", "five Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/5) * 5, 0))), #"15 Min Upper Ring Added" = Table.AddColumn(#"5 Min Upper Ring Added", "15 Min Upper Band", each Fourth dimension.From(#datetime(1970,1,i,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/15) * 15, 0))), #"30 Min Upper Band Added" = Table.AddColumn(#"xv Min Upper Ring Added", "30 Min Upper Band", each Fourth dimension.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/30) * 30, 0))), #"45 Min Upper Ring Added" = Tabular array.AddColumn(#"30 Min Upper Ring Added", "45 Min Upper Band", each Time.From(#datetime(1970,1,one,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/45) * 45, 0))), #"threescore Min Upper Ring Added" = Table.AddColumn(#"45 Min Upper Band Added", "60 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/60) * 60, 0))), #"Changed Type" = Table.TransformColumnTypes(#"sixty Min Upper Ring Added",{{"Time", type fourth dimension}, {"v Min Lower Band", type time}, {"15 Min Lower Band", type time}, {"30 Min Lower Band", type fourth dimension}, {"45 Min Lower Band", type time}, {"60 Min Lower Band", type fourth dimension}, {"v Min Upper Band", blazon time}, {"xv Min Upper Band", blazon time}, {"30 Min Upper Ring", type time}, {"45 Min Upper Band", blazon fourth dimension}, {"lx Min Upper Band", type time}}) in #"Changed Type"

Create Time Dimension with T-SQL

Copy/paste the below T-SQL in SSMS to go the Time dimension in SQL Server. You can create a DimTime tabular array f you uncomment the commented line and run the code.

WITH cte  As (SELECT 0 ID  Wedlock ALL  SELECT ID + ane  FROM cte  WHERE ID < 1439)  SELECT ID  , CONVERT(CHAR(v), 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(five), Dateadd(minute, ( ID / 15 ) * 15, '1900-01-01'), 108) [15 Minutes Upper Band]  , Convert(CHAR(5), Dateadd(minute, ( ID / xxx ) * 30, '1900-01-01'), 108) [thirty Minutes Upper Band]  , Catechumen(CHAR(5), Dateadd(minute, ( ID / 45 ) * 45, '1900-01-01'), 108) [45 Minutes Upper Band]  , Catechumen(CHAR(5), Dateadd(minute, ( ID / lx ) * 60, '1900-01-01'), 108) [60 Minutes Upper Ring]  , Catechumen(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS Float) / five) * 5, '1900-01-01'), 108) [5 Minutes Lower Band]  , CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 15) * fifteen, '1900-01-01'), 108) [15 Minutes Lower Band]  , CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / xxx) * 30, '1900-01-01'), 108) [30 Minutes Lower Band]  , CONVERT(CHAR(5), Dateadd(infinitesimal, Ceiling (Cast(ID AS FLOAT) / 45) * 45, '1900-01-01'), 108) [45 Minutes Lower Band]  , Catechumen(CHAR(5), Dateadd(infinitesimal, Ceiling (Cast(ID AS FLOAT) / lx) * 60, '1900-01-01'), 108) [60 minutes Lower Band]  --INTO DimTime FROM cte  Pick (maxrecursion 0)

Then y'all tin can load the DimTime to Power BI Desktop and create the necessary relationships.

Visualising Information

Now you can hands analyse and visualise your data in Power BI. Equally you tin can see in all different implementations of the Time dimension regardless of the platform, you always take unlike columns to back up different time bands. If you want to take dynamic timeband, then you have to unpivot the time dimension. I'd like to give credit to "Patrick Leblanc" from "Guy in a Cube" who explains how you can create dynamic centrality in Ability BI here. This is helpful particularly in those scenarios that y'all like to switch between dissimilar timebands and see the results immediately. I would non explain the technique once again as Patric explains it quite clear on a step-by-step basis, then I encourage yous to watch his video if you'd like to learn more. I simply put the DAX lawmaking together for those of you who are wondering how to unpivot the table in DAX. It would become handy if you are working on a SSAS Tabular 2016 (or before) or if you're working on a pure PowerPivot model and yous don't take access to Power Query to leverage the UNPIVOT functionality in M. Here is an instance of visualising data on minute level based on various timebands.

Unpivot in DAX

At the time of writing this postal service, there is no built-in UNPIVOT function in DAX. And then we have to somehow fake it. The below DAX code creates a calculated table based on the Time dimension we created earlier. Again, the whole matter gets more than clear when you download the Ability BI sample and have a look at the model structure.

Time in DAX Unpivot = UNION(           SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID], "Time Band", "Fourth dimension", "Time", 'Time in DAX'[Time])           , SELECTCOLUMNS('Fourth dimension in DAX', "ID", 'Fourth dimension in DAX'[ID],  "Time Band", "Time", "Time", 'Fourth dimension in DAX'[Fourth dimension])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "v Min Lower Band", "v Min Lower Band", 'Time in DAX'[5 Min Lower Band])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Fourth dimension Band", "15 Min Lower Band", "15 Min Lower Band", 'Fourth dimension in DAX'[15 Min Lower Band])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Ring", "xxx Min Lower Band", "thirty Min Lower Band", 'Time in DAX'[30 Min Lower Band])           , SELECTCOLUMNS('Time in DAX', "ID", 'Fourth dimension in DAX'[ID],  "Time Ring", "45 Min Lower Band", "45 Min Lower Band", 'Fourth dimension in DAX'[45 Min Lower Ring])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Ring", "sixty Min Lower Band", "lx Min Lower Band", 'Time in DAX'[60 Min Lower Band])                      , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "5 Min Upper Band", "5 Min Upper Band", 'Fourth dimension in DAX'[5 Min Upper Ring])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Ring", "15 Min Upper Band", "15 Min Upper Band", 'Fourth dimension in DAX'[15 Min Upper Band])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Fourth dimension Ring", "30 Min Upper Ring", "30 Min Upper Band", 'Fourth dimension in DAX'[xxx Min Upper Ring])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "45 Min Upper Ring", "45 Min Upper Band", 'Time in DAX'[45 Min Upper Band])           , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Ring", "threescore Min Upper Band", "60 Min Upper Band", 'Time in DAX'[sixty Min Upper Band])                            )

Click here to download the Excel, PBIX and SQL files.

hopkinsfalk1995.blogspot.com

Source: https://www.biinsight.com/time-dimension-in-power-bi-and-ssas-tabular-model-supporting-minutes-time-bands/

Share this post