Create Interactive Dashboard from Kusto Data via Kusto Queries

[th77]

Hi All,

I'd like to create an interactive dashboard for a dataset from Kusto. The dataset would be queried by a preset query with some parameters. I would need a dashboard with a user-enterable textbox, a dropdown with preset values, and a date-range for narrowing the dataset by time.

These user inputs should form the query which will be used to query data from Kusto and present it to the users. 

I'm new to Power BI so I was wondering if this might be doable, and if so, how?

[v-lionel-msft]

Hi @th77 ,

Was your problem solved? If yes,  please consider Accept it as the solution to help the other members find it more quickly.

Best regards,
Lionel Chen

[Rothbardian]

I am working on a similar use case (and hitting the same blocks), although with slightly narrower requirements. Progress on one may further the more general case.

Scenario: A stakeholder wants to see the page views for a given internal KB article over an arbitrary time with an arbitrary grouping (step in Kusto). Let's say daily views of article 12345 for the past 90 days. 

I have generated the specific query in Kusto for the given parameters, resulting in a beautiful line chart, and replaced the literals with parameters in both Kusto and PowerQuery.

Kusto:

set query_results_cache_max_age = time(5m); // PowerBI query allowing variable view periods, binning, and article selection
declare query_parameters (view_period : timespan = 90d, periods : timespan = 1d, kbid : int = 12345);
let start_time = ago(view_period); // Getting internal content set
let min_t = toscalar(ContentMetricsByCurrentPage | summarize min(Date));
let max_t = toscalar(ContentMetricsByCurrentPage | summarize max(Date));
database('[DB]').ContentMetricsByCurrentPage
| where PageId == kbid
| where Locale contains 'en-'
| make-series Views= sum(PageViews) on Date in range(min_t, max_t, periods)

This results in a list of views and dates (my goal is to have PowerBI handle the visualizations):

	Views	Date
	[   293,   238,   257,   271,   273,   100,   80,   188,   269,   365,   320,   194,   116,   101,   320,   403,   425,   603,   487,   175,   173,   276,   390,   409,   429,   419,   256,   174,   429,   338,   305,   327,   257,   129,   99,   371,   323,   356,   400,   413,   150,   160,   416,   416,   322,   224,   417,   220,   146,   430,   357,   320,   340,   470,   157,   156,   483,   338,   456,   645,   359,   162,   146,   367,   358,   294,   453,   315,   99,   165,   344,   472,   314,   295,   278,   120,   175,   503,   311,   341,   356,   341,   139,   166,   463,   319,   327,   256,   243,   136,   102,   256,   269,   246,   334,   281,   149,   77,   251,   314,   466,   299,   432,   194,   293,   342,   201,   205,   360,   412,   192,   139,   264,   228,   142,   320,   297,   122,   95,   209,   318,   220,   227,   234,   128,   83,   255,   249,   434,   342,   241,   203,   129,   238,   277,   302,   250,   338,   142,   96,   334,   302,   255,   210,   314,   111,   103,   587,   339,   418,   291,   390,   111,   85,   337,   292,   350,   273,   231,   103,   106,   258,   255,   192,   240,   275,   134,   135,   247,   365,   259,   260,   249,   127,   135,   266,   260,   312 ]	[   "2019-09-09T00:00:00Z",   "2019-09-10T00:00:00Z",   "2019-09-11T00:00:00Z",   "2019-09-12T00:00:00Z",   "2019-09-13T00:00:00Z",   "2019-09-14T00:00:00Z",   "2019-09-15T00:00:00Z",   "2019-09-16T00:00:00Z",   "2019-09-17T00:00:00Z",   "2019-09-18T00:00:00Z",   "2019-09-19T00:00:00Z",   "2019-09-20T00:00:00Z",   "2019-09-21T00:00:00Z",   "2019-09-22T00:00:00Z",   "2019-09-23T00:00:00Z",   "2019-09-24T00:00:00Z",   "2019-09-25T00:00:00Z",   "2019-09-26T00:00:00Z",   "2019-09-27T00:00:00Z",   "2019-09-28T00:00:00Z",   "2019-09-29T00:00:00Z",   "2019-09-30T00:00:00Z",   "2019-10-01T00:00:00Z",   "2019-10-02T00:00:00Z",   "2019-10-03T00:00:00Z",   "2019-10-04T00:00:00Z",   "2019-10-05T00:00:00Z",   "2019-10-06T00:00:00Z",   "2019-10-07T00:00:00Z",   "2019-10-08T00:00:00Z",   "2019-10-09T00:00:00Z",   "2019-10-10T00:00:00Z",   "2019-10-11T00:00:00Z",   "2019-10-12T00:00:00Z",   "2019-10-13T00:00:00Z",   "2019-10-14T00:00:00Z",   "2019-10-15T00:00:00Z",   "2019-10-16T00:00:00Z",   "2019-10-17T00:00:00Z",   "2019-10-18T00:00:00Z",   "2019-10-19T00:00:00Z",   "2019-10-20T00:00:00Z",   "2019-10-21T00:00:00Z",   "2019-10-22T00:00:00Z",   "2019-10-23T00:00:00Z",   "2019-10-24T00:00:00Z",   "2019-10-25T00:00:00Z",   "2019-10-26T00:00:00Z",   "2019-10-27T00:00:00Z",   "2019-10-28T00:00:00Z",   "2019-10-29T00:00:00Z",   "2019-10-30T00:00:00Z",   "2019-10-31T00:00:00Z",   "2019-11-01T00:00:00Z",   "2019-11-02T00:00:00Z",   "2019-11-03T00:00:00Z",   "2019-11-04T00:00:00Z",   "2019-11-05T00:00:00Z",   "2019-11-06T00:00:00Z",   "2019-11-07T00:00:00Z",   "2019-11-08T00:00:00Z",   "2019-11-09T00:00:00Z",   "2019-11-10T00:00:00Z",   "2019-11-11T00:00:00Z",   "2019-11-12T00:00:00Z",   "2019-11-13T00:00:00Z",   "2019-11-14T00:00:00Z",   "2019-11-15T00:00:00Z",   "2019-11-16T00:00:00Z",   "2019-11-17T00:00:00Z",   "2019-11-18T00:00:00Z",   "2019-11-19T00:00:00Z",   "2019-11-20T00:00:00Z",   "2019-11-21T00:00:00Z",   "2019-11-22T00:00:00Z",   "2019-11-23T00:00:...

The PowerBI query, generated by Kusto, is:
let KustoQuery = 
let Source = Json.Document(Web.Contents("[DB link}.show version",[Query=[#"csl"="set query_results_cache_max_age = time(5m); declare query_parameters (view_period : timespan = 90d, periods : timespan = 1d, kbid : int = 12345); let start_time = ago(view_period); let min_t = toscalar(ContentMetricsByCurrentPage | summarize min(Date)); let max_t = toscalar(ContentMetricsByCurrentPage | summarize max(Date)); database('mseg').ContentMetricsByCurrentPage | where PageId == kbid | where Locale contains 'en-' | make-series Views= sum(PageViews) on Date in range(min_t, max_t, periods)",#"x-ms-app"="PowerQuery",#"properties"="{""Options"":{""servertimeout"":""00:04:00"",""query_language"":""csl""},""Parameters"":{},""PrincipalIdentity"":null,""ClientRequestId"":null,""SecurityToken"":null,""AuthorizationScheme"":null,""RequestHostName"":null,""LocalClusterName"":null,""Application"":null,""User"":null}"], Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "DataType", "Type" },
{
{ "Double", Double.Type },
{ "Int64", Int64.Type },
{ "Int32", Int32.Type },
{ "Int16", Int16.Type },
{ "UInt64", Number.Type },
{ "UInt32", Number.Type },
{ "UInt16", Number.Type },
{ "Byte", Byte.Type },
{ "Single", Single.Type },
{ "Decimal", Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String", Text.Type },
{ "Boolean", Logical.Type },
{ "SByte", Logical.Type },
{ "Guid", Text.Type }
}),
Exception = Source[Exceptions]?{0}?,
Result = if (Exception <> null) then 
error Exception
else 
let 
DataTable = Source[Tables]{0},
Columns = Table.FromRecords(DataTable[Columns]),
ColumnsWithType = Table.Join(Columns, {"DataType"}, TypeMap , {"DataType"}),
TableRows = Table.FromRows(DataTable[Rows], Columns[ColumnName]),
LastColumn = Table.ColumnCount(ColumnsWithType) - 1,
TypedTable = Table.TransformColumnTypes(TableRows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{LastColumn} }))
in
TypedTable
in
Result
in KustoQuery

 I created aligning parameters in PowerBI for view_period, periods, and kbid. The results in Power Query are concatenated into:

ViewsDate

[List]

[List]

I am stuck extracting the list contents into a usable table form (I suspect this is trivial) and connecting the user action (probably less trivially an on_click for views of the article ID) running the Kusto query using the relevant article ID. 

I believe creating the slicers for view period times and groupings will be relatively simple once the dynamic query is running. 

For reference, the article data is pulled separately on a scheduled refresh using another Kusto query in a related database.

[Rothbardian]

I reworked my query using 'project' and was able to return  clean, date-specific view count table in PowerBI for the article of interest over the given time period. 

set query_results_cache_max_age = time(5m); // PowerBI query allowing variable view periods, binning, and article selection
declare query_parameters (view_period : timespan = 90d, periods : timespan = 1d, kbid : int = 12345);
let start_time = ago(view_period); // Getting internal content set
let min_t = toscalar(ContentMetricsByCurrentPage | summarize min(Date));
let max_t = toscalar(ContentMetricsByCurrentPage | summarize max(Date));
database('[DB]').ContentMetricsByCurrentPage
| where Date >= now(-view_period)
| where PageId == kbid
| where Locale contains 'en-'
| project Date, PageViews

 Which leaves me with creating the 'on_click, run query' behavior and modeling the parameters for non-technical users. (In my day job, I manage curated support content.)

[th77]

Sorry for the late response.

I spoke to someone in my org who is doing PowerBI dashboards for org-level insights.

Seems like dynamic query is not supported by PowerBI out-of-box, so we decided to move forward with building our own custom solution, directly querying Kusto and building our own model and views.

Thanks!

[v-lionel-msft]

Hi @th77 ,

"I would need a dashboard with a user-enterable textbox, a dropdown with preset values, and a date-range for narrowing the dataset by time."

Like this?

"The dataset would be queried by a preset query with some parameters."

Like this?

Your description is too general, please give sample data and specify the effect you want to achieve.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tad17 · Accepted solution]

Hey @th77 

I've seen similar threads to this before and the answer is usually that dynamic querries are not a thing. You can query all of the data and then let users splice and filter to their heart's content using visuals, but there is no such thing as users editing the actually data query from a dashboard or report.

If this helps please kudo.

If this answers your question please accept it as a possible solution.