Thousands of GetSchema queries fired for kusto queries

[yahuang]

Hi, PowerBI Team:

We have 3 kusto queries we use "Append" to combine them together. Then we found instread of 3 get schema query and 3 acutal query, there are 5000+ getschema queriese against our kusto cluster, which hurt the health of our cluster. These quereis have format like:

<my query>

| limit 1000

| limit 1000

....

| limit 1000

| getschema

very long limit 1000 inserted, what is the reason so many get schema queries fired?

Thousands of GetSchema queries fired for kusto queries

[camargos88]

Hi @yahuang ,

I think you answer is in this video: https://www.youtube.com/watch?v=3uKNNZqBIkg

You can improve the way PQ queries your source.

[yahuang]

Hi, @camargos88 :

Thanks for sharing the video. I tried to follow the tips in the video to disable the backgroud preview and parrell query. If i refresh from PowerBI desktop, the quer is done to below 18 queries now. But when I publish the report and replace the dataset on powerbi service, refresh data set from powerbi service, still result in 4k+ queries? Is there difference config to be done for data set refresh on powerbi service?

Thanks,

-Yan

[camargos88]

Hi @yahuang ,

Check the first steps of each query, how is Power Query getting the data ?

Maybe it's bringing all the table catalog instead of the table directly.

[yahuang]

Hi, @camargos88 :

The first step for each query is against a function like this:

let
Source = Kusto.Contents("https://<mykusto>.kusto.windows.net", "Reporting", "GetTopCustomers() | where ReportDays == 7 | where CustomerType == 'User'", [MaxRows=null, MaxSize=null, NoTruncate=null]),
#"Appended Query" = Table.Combine({Source, vwTopOrgsWeekly, vwTopTenantsWeekly})
in
#"Appended Query"

And as I said, if i refresh the data in power bi desktop, the queries are around 18, but after i publish to powerbi service, there are 4k+ quereies again when refreshing data.

Thanks,

-Yan

[camargos88]

Hi @yahuang ,

How are you calling the other tables (vwTopOrgsWeekly, vwTopTenantsWeekly) ?

Table.Combine({Source, vwTopOrgsWeekly, vwTopTenantsWeekly})

[yahuang]

Hi, @camargos88 :

Here is the 2nd query:

let
Source = Kusto.Contents("https://<mykusto>.kusto.windows.net", "Reporting", "GetTopCustomers() | where ReportDays == 7 | where CustomerType == 'Organization'", [MaxRows=null, MaxSize=null, NoTruncate=null]),
#"Appended Query" = Table.Combine({Source, vwCAPSLATopTenantsWeekly})
in
#"Appended Query"

The 3rd query:

let
Source = Kusto.Contents("https://<mykusto>.kusto.windows.net", "Reporting", "GetTopCustomers() | where ReportDays == 7 | where CustomerType !in ('Organization', 'User')", [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
in
Source

There used to be one query, but the total rows returned over the kusto client limitation, so we have to divide into 3 queries.

Thanks,

-Yan

[camargos88 · Accepted solution]

Hi @yahuang ,

You have another append function here:

#"Appended Query" = Table.Combine({Source, vwCAPSLATopTenantsWeekly})

Perhaps that's why you are getting so many calls in your source.

Can you bring them together from the source ?

[yahuang]

Hi, @camargos88 :

Thanks for the tip! I removed the append from second query and publish to PowerBI service. This time, there are only 19 queries, 11 of them is to getschema. Though it is still not optimal as 6 queries (3 getschema and 3 get data), it is acceptable now.

Thanks,

-Yan

[camargos88]

Hi @yahuang ,

Nice it worked.

Maybe there is a query not pulling data directly from the source and bringing all the catalog.

Although you got a good reduce of queries running.