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)