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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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