cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
th77
Microsoft
Microsoft

Create Interactive Dashboard from Kusto Data via Kusto Queries

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?


1 ACCEPTED SOLUTION
Tad17
Solution Sage
Solution Sage

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.

View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

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
Microsoft
Microsoft

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):

   
 ViewsDate
 [
  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.

 

 

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.)

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
Community Support
Community Support

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?

x3.PNG

 

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

Like this?

x4.PNG

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
Solution Sage
Solution Sage

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.

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors