How many to many relationships affect the number of queries required to refresh matrices and tables
Published Nov 16 2023 12:29 PM 2,787 Views
Microsoft

 

Apology:

I must admit that in the last two years I’ve told many Power BI/Kusto customers not to worry about relationships that are created as M:M.

I was pretty sure that with Direct Query, such relationships are fine,

Indeed, the generated queries looked fine and performed as expected.

I recently became aware that the number of queries generated for some visuals e.g. Matrix and tables can be affected by the type of relationships between the participating tables.

 

 

 

Creating a relationship between two queries from Kusto

Usually, the dialog of creating a relationship will look like this:

 

DanyHoter_0-1700166144426.png

 

 

Even thou the ProductKey in the products table is unique, PBI “thinks” that it isn’t because the result of counting the unique number of keys is not accurate (see dcount() (aggregation function) - Azure Data Explorer & Real-Time Analytics | Microsoft Learn)

At this point, you should change the Cross filter direction setting, and the final dialog will look like this

DanyHoter_1-1700166144430.png

 

 

 

 

Why this can be a problem?

For some visuals, PBI needs to issue multiple direct queries to calculate all the values.

In a Matrix with multiple fields on rows, each level in the nesting will need to be calculated in a different query.

In the examples I included you can see 6 queries sent for each refresh of a single visual.

Unless

PBI can sometimes calculate the totals by adding up the values from the more detailed level.

This is true for measures that only include aggregations like sum or count and calculations between such aggregations.

In the attached examples the measures Sales,Cost and Profit% are such measures and so the number of queries can be much lower.

The measure Products# uses DISTINCTCOUNT and so totals cannot be calculated without running a separate query against the source for each level.

Unless

When a M:M relationship exists between the fact and the tables used on rows, the PBI engine can’t be sure that even a simple(additive) measure can be calculated without a separate query for each level.

In these cases, the number of queries is high regardless.

 

How to force the relationship to become 1:M

There are multiple ways to change the relationship but the easiest one is from the properties pane:

DanyHoter_2-1700166144431.png

 

Don’t forget to set the Assume referential integrity to yes and to click Apply changes.

The referential integrity setting will ensure that the generated joins will be of the kind – inner.

 

 

Attached examples

There are two examples using the same data and the same measures.

The only differences are the relationships.

Using the performance analyzer, you can refresh the visuals and see the difference.

With M:M the two matrices require the same number of queries.

With 1:M the matrix that does not include the distinctcount measure refresh much faster and sends less queries.

 

 

 

 

Co-Authors
Version history
Last update:
‎Nov 16 2023 12:29 PM
Updated by: