Using ADX Datetime columns in Power BI – part2
Using a time table to filter multiple Kusto tables with a single slicer
In the first part of the series I described some of the available features to support DateTime columns.
In this part I want to describe a method to filter multiple tables based on one DateTime slicer/filter.
This is something that comes very naturally when you use date columns.
You create a calendar table and you create relationships between the calendar table and some other tables
We create a time calendar in the same way.
Creating a time calendar
The equivalent to the calendar table is a table with all the time elements in the period you want to analyze.
The problem with such a table is that there are theoretically an infinite number of such time elements.
The question is at what granularity do you want to slice time. The granularity can vary between hours and ticks.
If you go too granular, the table will be huge and if you use hours, you will not be able to slice by minutes.
I’ll use minutes in my example, but you can apply at any granularity, just remember that there are 2.6 million seconds in a month :smiling_face_with_smiling_eyes:
Like a date calendar, you can create a time table in M or in DAX. I prefer the M option.
First add two new M parameters
- LookBack is a number that will specify the number of days you want your table to cover going backward from today.
- GranularityMinutes is the number of minutes in a time bucket. If you specify 1, you’ll get a row for every minute and if you specify 10, a row for every 10 minutes
Create a blank query and paste this script in the advanced editor
let
Today=Date.From(DateTime.LocalNow()),
StartDate=Date.AddDays(Today,-LookBack), TimeSeries=List.DateTimes(DateTime.From(StartDate),(LookBack+7)*24*60/GranularityMinutes,#duration(0,0,GranularityMinutes,0)),
#"Converted to Table" = Table.FromList(TimeSeries, Splitter.SplitByNothing(), {"DateTime"})
in
#"Converted to Table"
The main part is the List.DateTimes function that returns a list of DateTimes starting from a point in time.
I set the second argument to the number of time periods in the number of days + 7 so I don’t need to refresh the table every day.
The third argument specifies the granularity in minutes as a duration value.
Creating matching columns in the fact tables
Our goal is to create relationships between the time table and the fact tables that contain a DateTime column. In order to create the relationships, we need to add a column in the fact table that will match the values in the time table.
In the attached example I only have one fact table but you can repeat the same steps for every fact table.
We need a value that is the original DateTime column rounded to a minute or 5 minutes or 10 minutes according to the granularity of the time table.
We can do that using M in the or in the original table/function in Kusto.
In the M script we can add a new step:
= Value.NativeQuery( RawSysLogs1 ,"| extend TimeValue=floor(timestamp," &Text.From(GranularityMinutes) & "m),App=tostring(tags.appname)")
This step uses the Value.NativeQuery function to add a snippet of KQL that creates a column with the right granularity based on the M parameter GranularityMinutes.
At the same time I add another column extracted from the dynamic column tags.
I created a relationship between the new column TimeValue column in the fact table and the time table
Filtering using the time table
We can use all the types of filters possible for DateTime columns using the column DateTime in the time table.
As the table in the example is constantly streaming new values, we can use the relative time slicer.
We can look for rows ingested in the last minute or in the last 96 hours and see the numbers of rows for each application
Posted at https://sl.advdat.com/3Iur2tlhttps://sl.advdat.com/3Iur2tl
