cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Using relationships vs merge join difference

hi, 

i have a question. When to use the relationships ( one to many, one to one , many t omany etccc) and when to use merge join(inner, outer) ?  whn we can join two tables using merge join without any relationships between tables, what is the use of relationships?

and sometimes there are lot of issues creating relationship between tables like null values, duplicate values,.etc.. what happanes if dont build relationships and just use joins? and vice versa? can somebody throw some light plz?  

6 REPLIES 6
Somasekhar_IND
New Member

When you load the data to Power BI, only a single table (MergeQueries) that combines the two tables is loaded to the data model. The original tables won’t be loaded into the data model as you will be disabling the load, hence less memory consumption and better performance.

When you compare the two PBIX files with the same imported data, you will see that the Relationship file is larger in size than the Merge file.

However, its depends on the scenario why you want to use Relationship or Merge in Power BI, like using some DAX functions for analysis that may not work when using Merge. Also, if you have a complex model, it is recommended to do as many modelling at the Datasource level. The less complex the model is, the better the performance in Power BI.

 

Refer the below link for deatailed explanation 

MERGE VS RELATIONSHIP IN POWER BI - Ruki's Blog (rukiatu.com)

 

Jesusmari
Frequent Visitor

Buenas,

 

 ¿hay mucha diferencia de rendimiento entre tener dos tablas unidas medienta un merge o un join? la percepción que yo tengo es que el merge es mas costoso, no obstante me gustaría saber vuestra opinión.

 

Saludos

 

Saludos

No1
Frequent Visitor

Hi, 

 

When working with a couple of huge files (SQL-DirectQuery) I got the error that there was too little memory to show the matrix when choosing 'Show items with no data' for the linked tables. 
When I didn't choose 'show items with no data' the relationship only showed values from the first table with related values in the second and third tables (practically making it an inner join relationship).
Using a left outer join when merging queries solved it.

Don't know if there is a fundamental advantage in performance when merging, compared to managing your relationships.
I guess there is more choice when you merge, because you can choose:
Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti 
and when you manage relationships, you can only choose
many to one, many to many, one to one and one to many.

Grtz

jvaught
Advocate I
Advocate I

I have the same question and maybe some additional clarity.

 

One reason I have found for using a relationship rather than a join is when the upstream table has information you need in more than one downstream table. If you use a merge, you have to do the merge multiple times, which also duplicates the data in the report. It also creates confusion when you have the same field showing up in both downstream merged tables in your report. You will get errors saying Power BI can't determine the relationship between two fields (quite frustrating when you know they came form the same source - the common upstream table that was merged into two downstream tables). In this case it's better to bring the common upstream table into the report and relate it to both downstream tables via relationships. The data is not duplicated and the relationship is clear.

But what about when you have separate dimension and fact table which only relate to each other and no other tables? Separating dimension and fact tables is good database design, but in my report it means I have to constantly select fields from both tables. Merging would make this way easier, but am I breaking some fundamental rule of good design when I do that?

Also, what are the performance considerations? Again, these questions only really apply to a situation where you have only one pair of tables in mind. As I stated above, when you have a table paired (whether by merge or relationship) to another table, you are better off with the relationship, as far as I know.

danavannz
New Member

hi reddy_powerbi, i have the same question!  Hopefully someone can answer it here, if I found out i will let you know too 🙂

v-shex-msft
Community Support
Community Support

HI @Anonymous,

 

When tables contains available relationships, you can direct table fields to build visuals and graphs, records which used in visuals will auto analysis and summarize on current category through existed relationship.

 

If not exists availed relationship, you can't direct use amount field from different tables, they not summarize so you need to write measure with particular filter conditions manually to display correspond summary result.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!