KQL best practices for joins and filters

For Power BI developers but not only

 

Summary

Many users who try  ADX in direct query mode encounter errors right away.

The errors  complain about lack of memory.

 If the tables are small enough, it may work but still performance will not be as advertised on TV.

The reason in most cases is the behavior of joins in ADX as they are created by PBI.

In this article I’ll show different approaches to joining tables as used by PBI for related tables or as can be expressed in KQL in general.

I created a special table in the help cluster with 31 million rows that is big enough to demonstrate the performance differences between the variations.

Worst vanilla case

All the examples use a model with three tables. One is a fact and two are dimensions, for dates and customers.

In PBI the model will look like this:

 

 

If you try to build a report with two slicers and a simple table, you’ll see this error:

 

This is the situation in the attached example worst case.pbix.

I forced the relationships to be 1:M and not M:M because the experienced PBI practitioner will try to avoid M:M and will find away to do that. Actually````````` 1:M relationship is part of the problem.

The KQL statement created by PBI will look like (Simplified for readability)

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

// Lacks memory

DuplicateFact

| join kind=leftouter Dates on $left.DateKey == $right.Datekey

| join kind=leftouter Customers on CustomerKey

| where RegionCountryName == "Canada"

| where FiscalYear == 2008

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

You can run these examples by following the link to web, and see the error.

If we limit the size of the fact table by ~ 50% it will run but with very high memory consumption.

 

 

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

// Very high memry with limited size fact table

DuplicateFact

| where SalesAmount > 100

| join kind=leftouter Dates on $left.DateKey == $right.Datekey

| join kind=leftouter Customers on CustomerKey

| where RegionCountryName == "Canada"

| where FiscalYear == 2008

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

Statistics :

 

 

First improvement – inner join

A simple change will make things better.

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

// High memory but still works with inner join

DuplicateFact

| join kind=inner Dates on $left.DateKey == $right.Datekey

| join kind=inner Customers on CustomerKey

| where RegionCountryName == "Canada"

| where FiscalYear == 2008

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

Stats:

 

Notice the high memory utilization but it works.

In PBI, you can get inner joins in one of two ways:

• M:M relationships with single direction filtering.
• 1:M relationships with assume referential integrity checked.

Both ways are acceptable but you should avoid leftouter or rightouter joins.

See the attached file referential integrity.pbix

The query still consumes 3.46 seconds of CPU which is pretty high.

If you try to move the position of the filters , you’ll see no change because with inner joins, we know to move the filters during optimization

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

// Same memory as before

DuplicateFact

| join kind=inner (Dates | where FiscalYear == 2008) on $left.DateKey == $right.Datekey

| join kind=inner (Customers | where RegionCountryName == "Canada") on CustomerKey

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

 

Using lookup instead of join

 By using lookup instead of join we make the real difference

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

DuplicateFact

| lookup kind=inner Customers on CustomerKey

| lookup kind=inner Dates on $left.DateKey == $right.Datekey

| where RegionCountryName == "Canada"

| where FiscalYear == 2008

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

 

 

Notice the memory utilization going down from 1.15GB to 20.38MB

 

 

 

If you don’t make sure that the joins will be inner joins, CPU cost will more than double.

 

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

Customers

| join kind=rightouter  hint.strategy=broadcast

    (Dates

        | join kind=rightouter  hint.strategy=broadcast  DuplicateFact on $left.Datekey==$right.DateKey)

    on CustomerKey

| where CityName =="Paris"

| where FiscalYear ==2007

| summarize count(),sum(SalesAmount) by Education

 

 

 

 

 

How to achieve the lookup effect from PBI?

We can’t convince PBI to generate lookup instead of joins, but we can get  the same effect.

First, we need to understand what the magic behind lookup is.

LargeTable kind=inner lookup Smalltable on key1

Is executed as:

Smalltable | join kind=inner hint.strategy=broadcast LargetTable on key1

The reverse order of the tables + the hint are making the difference.

A full equivalent example not using lookup is:

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]

Customers

| join hint.strategy=broadcast kind=inner

    (Dates

    | join hint.strategy=broadcast kind=inner DuplicateFact on $left.Datekey == $right.DateKey)

    on CustomerKey

| where RegionCountryName == "Canada"

| where FiscalYear == 2008

| summarize sum(SalesAmount) by StateProvinceName, FiscalHalfYear

 

 

 

Getting this query pattern in PB is achieved by adding a setting to the 4^th parameter in the  AzureDataExplorer.Contents  function in each query for the dimensions.

= AzureDataExplorer.Contents("help", null, null, [IsDimension=true])

See the attached file is dimension true.pbix