million-dollar question - Non-Optimal SQL Queries for Many-to-Many Relationships

[hi_world]

Hello, 

Power BI Direct Query on GCP Views Generates Non-Optimal SQL Queries for Many-to-Many Relationships

The problem is that it makes a kind of cross join on NULLs.
The following article describes a similar case in detail : https://cbailiss.wordpress.com/2022/09/03/power-bi-nullable-column-merge-join-problems/
How to hack this problem?

Best regards

[hi_world]

the problem still remains valid. it's strange that we don't have a workaround

[lbendlin]

Have you reduced the cardinality of the join columns?

[hi_world]

This is part of the technical-functional specifications, and I cannot change it. Therefore, I am looking for a solution without reducing the cardinality.

[lbendlin]

You would need to throw a bigger capacity at the problem then, with more memory.

[hi_world]

For me no, the initial problem is that the powerbi engine generates an unoptimized sql query on the views.
if I replace the views with tables and I define not null constraints on my join key columns in the powerbi model, the queries are generated correctly and the performance is good.
I have to keep views and not tables, that's why I'm trying to understand why powerbi behaves in an abnormal way with views and how to get around it?
the article that I shared explains very well this very strange behavior of powerbi en direct query on big data views.

[lbendlin]

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41

[lbendlin]

Don't have nulls in your join columns?

Note that DAX Studio shows you the issue clearly in the RI column.

[hi_world]

Hi 
Thank you for you response @lbendlin 
No, there is no null value in my columns.

There is nothing showing in RI column : 

if you have other ideas I'm interested
Thank you for your help
Best regards

[lbendlin]

What's the cardinality of your join columns?

[hi_world]

table1 one to many table2 

table1 one to many table3 
This happen when i take fields from table2  and table3 (at the end it's a many to many)

[lbendlin]

what is the cardinallity of the join columns in table1? Anything above 50K is asking for trouble.

[hi_world]

>1 million.
 Do you think there is a workaround?

[lbendlin]

You will need to look into further normalization.  

Composite model guidance in Power BI Desktop - Power BI | Microsoft Learn

I recommend you read this article thoroughly. It talks about composite models but the principles apply in-model as well.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn