Joins in SQL vs using relationships performance

[Anonymous]

Hello everyone,

I haven't found the exact answer that I'm looking for, so I hope my question will be clear to understand.

I have two tables: X and Y. Let's say that both tables contain 4 columns that I need in 99% of my reports. Since I use custom queries all the time, I usually select the 4 columns from X and use a left join for the 4 columns from Y. Let's say that I have a report with 5 different queries, all of them use that left join, so 5 left joins in total.

Would it be better to just import table Y and use the built-in relationships instead of left joining in my SQL queries?

Would powerbi performance be better?

Thanks in advance. If my explanation is unclear, please let me know!

[nhoward · Accepted solution]

@Anonymous ,

The answer depends on how you want to shap your data model.  Best Practice is a Fact Table (contains the items that you want to summate, count, etc), and a few Dimension tables (ones that will help you filter the fact table).

If your PBI data model requires the two SQL tables to be in one table, then join on loading via the SQL script.  Or if you access to the SQL DB, create a View that joins them, then you can just load the view into PBI. 

Also, as @v-diye-msft  said, the Relationships in PBI act in certain ways.  This may not be in the way you need for your model, as the relationships are filter relationships not joins. 

hope that helps. 

[Anonymous]

Thank you both so much for your answers. I thought I understood what relationships are for - linking tables. What I completely missed was that relationships indeed filter results, not join them.

So the two methods are somewhat the same, yet the results can be very different.

[v-diye-msft]

Hi @Anonymous 

When we load two or more tables at the same time, Power BI desktop will look at column names in the tables we are querying to determine if there are any potential relationships. If there are, those relationships are created automatically.

From Cardinality and Result we can get:

many : many -> Inner 
one : one -> Full Outer 
one : many -> RightOuter 
many : one -> LeftOuter 

if the relationships set by SQL DB is the same as Power BI detect, then you needn't modify them. While the logic is different, you can manage relationships follow this article based on your needs. See: Create and manage relationships in Power BI Desktop.

Here's similar thread could also be your reference:

https://community.powerbi.com/t5/Desktop/Relationships/m-p/233398