Kusto query settings in Power BI
Summary
When using ADX/KQL DB data in PBI, there are many settings that can be included in the M query.
Some of the settings are sent as part of the KQL syntax and some are directed at the Kusto connector and affect the way KQL is generated.
This article will summarize the behavior of the different settings, the different locations you can include settings and how to control the settings when multiple M queries are joined and eventually appear as one KQL query.
Creating a new query from ADX/KQL DB
Here is the full dialog used for creating a new query.
Some settings have specific fields in the dialog and others can be mentioned in the last field of additional set statements.
You can also include setting as part of the KQL query you embed in this dialog.
Based on these settings the full script for the query will be
let
Source = AzureDataExplorer.Contents("help", "ContosoSales", "SalesTable
| summarize Sales=sum(SalesAmount) by ColorName,Country",
[MaxRows=null, MaxSize=null, NoTruncate=true,
AdditionalSetStatements="set query_datascope= 'hotcache';
set query_results_cache_max_age =5m"])
in
Source
The different settings will be translated to query properties. In this example the # of returned rows and the data volume is not limited.
Additional settings for the connector
In the 4th argument of the AzureDataExplorer,Contents function you can include other settings that are instructions for the connector and will change the KQL syntax.
The list of these settings can be found here.
One important setting that can be included in this area is “IsDimension=true”.
This setting should be included in queries that will be on the 1 side of 1:m relationships.
The generated join statements will position these tables before the fact table and so will be efficient.
How are settings used when multiple queries are joined?
A KQL query can use data from multiple M queries with relationships between them.
Each one of the M queries can include settings but only the setting from the filtered side of the relationship will be used.
In the attached example you can see a products table and a sales table.
Products filter that fact and so the settings from the fact side will be used in the joined query.
The settings from the 1 side (Dimensions) will be used when only one M query is used like in a slicer.
Including let statements in the embedded KQL syntax
Let statements are very different from set statements and are an integral part of the KQL language.
The only reason I mention let here is because of a special case when an M query with embedded KQL that includes a let statement is used in a join.
Let statements cannot appear in the middle of a query, only in the beginning.
If a query that has let is mentioned in any position that is not first , it will generate a syntax error
The query will include something like:
| join hint.strategy=broadcast kind=inner (let a=1;SalesFact
If you need to use let statements in your KQL query, create a function with the full syntax including the let variables.
You can also use function arguments as an alternative to let statements.