Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hi_world
Helper III
Helper III

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

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

13 REPLIES 13
hi_world
Helper III
Helper III

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

Have you reduced the cardinality of the join columns?

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

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

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.

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
Super User
Super User

Don't have nulls in your join columns?

 

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

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

There is nothing showing in RI column : 

hi_world_0-1702291226748.png


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

 

What's the cardinality of your join columns?

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)

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

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors