Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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.
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
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 :
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.