Not all relationships should be nurtured. Some of them should be killed. You know which one.

In my previous blog I explained about Data Blending. This blog is for Join & Relationship. Blending, Join and Relationship are 3 ways that Tableau allow us to combine different tables and DataSources together. Lets see the DataSources we will be working with

Lets call this Caste /Character Table.

 

Lets call this is the Profit Sales Table

 

& lets call this the Dialogue Table.

Note- The last 3 rows of the Dialogue Table are missing in the Profit Sales Table. Also, the Profit Sales Table have, Product Id and Customer Id fields.

Data Modelling In Tableau  

 

 

What are Joins ?

A join is a way for us to combine data horizontally. Tableau represent this as a Ven-Diagram. We can do Inner Join, Left Join, Right Join, and a Full Outer join. We can also join based on calculated fields.

Lets now see what happens when we use join in Tableau, and while demonstrating these concepts, I explain viewers why relationship were introduced.

Bring all 3 DataSource in Tableau and join Caste table with Profit sales table. I joined on customer id

 

Before proceeding, notice how the RECORDS DUPLICATED, lets see that in Sheet

 

 

We see that the salary of our characters is not right, and also one of our character, Monica, is missing as we didn’t put her dialogue on the Profit Sales table.

We can go back to DataSource and can change inner join to Left Outer join, to get all the characters. We can also create a Level Of Detail Expression to properly display the salary.

 

 

So I make it left outer join, instead of Inner join, and get all our characters, and make a  Fixed Level of Detail Expression, to get the salary. Since salary is Duplicated, it doesnt matter if we use Minimum, Maximum or Average, it will give the same number.

 

Now what if we want to see all the Dialogues. Lets see how Tableau behave in this case.

 

 

 

Notice how we loose our character again, Monica is missing again. In our first join, which was Left Join, we were preserving the value of Monica, but in the second join, which is inner join, we loose that value, because inner join is a destructive join, meaning if Tableau don’t find matching values, it will drop that value. Since Monica Dialogue was missing, she was dropped. See the images above, to get better understanding. Now if we want to preserve that, we need to make sure that all joins are Left joins, all the way through. Or some other kind of combination that allow us to preserve all characters. Lets make all joins Left Join for this exercise.

 

But now, another issue can be, if I want to see all the dialogues, weather or not they are being used.

 

 

Notice that in the table above, when we click on View data, we see all the records of dialogues. But when we go on a new sheet, and bring the Dialogues(Product Name) on the canvas, we see that Values are Missing. This is another problem /limitationwhen we use join. We are getting Null because not of our products have been matched. See Image below.

If we create right join, then we will miss our Character again. So the solution to this problem is using multiple DataSource, and this is why Relationships are being introduced

 

Lets now understand Relationship.

In a nutshell, the power of relationship is Smart Aggregation and Contextual Joins and what this means is Tableau watches for context, it takes a look on how we are using the data and it may alter the type of joins that is needed or the type of aggregation which is needed based on the fields which we are dragging to our canvas. 

A relationship is a type of contract that tells Tableau that these two tables are related by this field, in this case we are not telling that its a type of join, like inner, outer, left or right join.

Lets see that in action-

 

 

Notice that there are no Venn diagrams, just a Noodle, meaning relationship. The Performance options shows Cardinality, and Integrity. I will explain this in detail in some other blog. For now, focus that the only table appearing is the one we click. If we click on a different table, that table record will show up.

 

Now lets check the issues we have, our first issue was Duplication of salary, and second issue was Missing values of Dialogues.

 

 

Notice that the Salary is right, without using the Fixed Level of Detail.
The sum of salary, is only applied to the character table, which have only one record per character. Also notice that by default it retained Monica, this is because, Tableau recognized that since I dragged character first, I want to retain all characters.

 

Now what if we want to see all characters who have a dialogue. There are couple of ways, the first way is to add a filter, and remove all character who don’t have sales. Second method is shown in the image below

 

 

if we now drag salary, we again have to apply filter to remove Character with no sale.

For our second problem, missing values, we can simply drag all Dialogues and see what happens

 

without making any adjustment to join, or bringing new data sources we get all the records. This is how powerful relationship are, they are way more flexible and are context specific as I mentioned above.

Limitation of Relationship

  •  Cannot join on geographic fields.
  • Cannot define/edit relationships on published data sources.
  • Does not support circular relationships.

 

Checkout online documentation to see all the limitation of Data blending, Joins and Relationships. Hope you find this helpful, if you are a Data Schooler, feel free to reach me out in person to understand these concepts in more depth.

 

 

 

 

 

 

The Data School
Author: The Data School