This article is contributed. See the original author and article here.

What’s wrong with 1:M relationships between ADX tables?

 

In this article I want to talk about the behavior of 1:M relationships and what kinds of joins are created to support 1: M.

 

Aren’t 1:M just the normal relationships in PBI?

 

Yes, they are but not when both sides are ADX queries in Direct Query mode.

In most cases Power BI “thinks” that the relationship is M:M because of the way distinct count works in ADX.

To get 1:M, you have to change the relationship’s properties using tabular editor or another method.

Also, if the dimension table is small, the distinct count of the key will return the exact value and the relationship will be defined as 1:M.

 

So, if they are the default, what’s wrong with 1:M?

 

The problem is with the KQL joins which are generated based on 1:M relationships.

Let’s assume that we have a Product Category dimension, and you filter by one category.

the relationship is 1:M between Product Category and FactSales.

Assuming you used IsDimension=true on the dimension, The KQL statement generated will be something like:

[“Product_Category”]

| join kind=rightouter hint.strategy =broadcast SalesFact

| summarize A0=sum(Sales) by …

| where Category==”Cat1”

 

 

Because of the right outer join, the filter on Category is applied after the join is performed on the entire fact table.

The query results will be correct, but the performance will be bad.

What can be done to make the query perform better?

 

We need to convince PBI to create an inner join instead of the rightouter join.

There are two way to force an inner join:

• Define the relationship as M:M


• Define the relationships as 1:M and checking the option Assume Referential integrity.


• 
  

   

  
  

 

In the case of inner join , the filter(s) on the dimension that appear at the end of the query, will be pushed by the ADX engine to the early stages of execution and so the join will be only on the products that belong to Cat1 in this example.

The result will be a much faster query.

 

Summary 

If you see in the queries generated by PBI any other join except inner, you have to change your PBI model so that the joins will be inner.

 

 

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.