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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Joins in SQL vs using relationships performance

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!

 

 

1 ACCEPTED SOLUTION
nhoward
Resolver I
Resolver I

@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. 

View solution in original post

3 REPLIES 3
nhoward
Resolver I
Resolver I

@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
Not applicable

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
Community Support
Community Support

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 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.