Tableau 2020.2 - Relationships
This post is the fourth in a series of posts examining a whole host of game-changing Tableau Desktop features that have been released in 2020. In the last post we discussed the nuances of Set Controls. In this post we will dive into Relationships, which were released in Tableau Desktop 2020.2.
We have already written about this topic in a previous blog post, but that was more theoretical. Instead of talking about the concept of relationships, this blog post is going to show you how to implement them in your dashboards and what they mean for your data model.
What are Relationships?
When building a data model you generally have two approaches, flat files or relational models. Flat files are when you have all of your data in one file with every row receiving some value in every column, and every data attribute you need is in that large layout. Relational models use STAR schemas where you define relationships between your fact and dimension tables without joining the values into one file and then using those dimension tables only when needed to describe the fact table.
In the image below, I pulled the Sample – Superstore data set from Tableau 2019.4 and created the joins necessary to bring in all of that data. Circled in red you can see that the data from the different tables are being joined into one flat file, aka all the data from those tables is being combined into one table or view.
See below for an example of the new relationship model. You can see that as you click on each of the different tables that they show up individually in the preview pane. This is because they are not all joined together, they are related. The only time that multiple tables are referenced in the same query is when a query goes across a relationship.
How To Set Up a STAR Schema
Relational data models use STAR schemas to create an efficient and optimized way of storing and retrieving data. STAR schemas are made up of fact and dimension tables.
Identifying your fact and dimension tables is an important first step in creating your relational data model. The next step is to create the proper relationships between your tables. See the example below where we have our Sales_Fact table and two related dimension tables.
You then need to determine the cardinality of your relationships. Cardinality of a relationship is the relationship between the number of rows in one table and the number of rows found in the other table. Let me give you an example.
The Sales_Fact table to the right is going to have many rows with each region present. The Region_Dim table will have one row that describes that reason. The cardinality of this relationship is referred to as one-to-many.
You can also have relationships that are one-to-one or many-to-many. You will not only need to know the cardinality of your relationships for the purpose of building your data model, but you also must understand this relationship in order to correctly use your data to build out your analysis.
The last concept you must understand to complete your data model is to understand your relationship’s referential integrity. This refers to the quality of the relationship between two tables. If every value found in your fact table is found in your dimension table, then the two tables are assumed to have referential integrity. A lack of referential integrity in your relationships will disrupt your data model and can cause inaccurate and/or misleading reporting.
Building a Relationship in Tableau
Now that we’re refreshed on the basics of data modeling and relationships, let’s build out our data model using relationships in Tableau!
I am going to use the Sample-Superstore data set in Tableau 2020.2 to create our data model. First, let’s identify our fact table. Here, the Orders table is our fact table because it describes our business process. We want people to order items from our Superstore, and when they do, they are recorded in the Orders table. Drag on the Orders table.
Next, let’s describe elements within that fact table with one of our dimension tables. Drag the People table onto the pane. The menu below will pop up. This is where we will define the characteristics of our relationship.
When I brought in the People table, Tableau recognized that there was probably a relationship between the Region field from the Orders table and the Region field in the People table. If this had not been the case, then you would have needed to select the fields from the drop-down that you want to relate.
Next you will need to select the cardinality for this relationship. Here, there are many rows with each region in the Orders table but there is only one row per region in the Region table. This means the cardinality is many-to-one. Choose those options from the drop-down accordingly.
Last, you will need to choose where your relationships will hold its referential integrity or not. If you do not know whether every value has a match, then choose Some Records Match. If every value is your field will 100% for sure have a match in the field it’s related to, then you can choose All Records Match.
Now that we’ve set up our initial relationship, let’s bring in the Returns dimension table and set up a relationship to the Orders fact table. In this example there are many rows in your Returns dimension for each order just as there is in the Orders table, so it will be a many-to-many relationship on the Order ID field. Although I assume that because an order got returned, that it was ordered, I am still choosing Some Records Match because I am not entirely familiar with the data.
Once you’ve created your relationships, you are now ready to build your report! When you go to your first sheet you will see each of the tables in your relationship grouped together in the Tables pane on the left-hand side (see below).
Can I Still Join Data?
Although relationships are generally better to use (as we’ll discuss in our next topic), this does not mean that joins are not possible.
To create a join, drag your first table into the empty pane, then double-click on that table. When you double-click, it will look like the selected table popped out into a separate container with a border around it. Once you’re in this view, you can drag in your desired tables and create joins in the same way you would have previously in Tableau. See below for an example.
Why Are Relationships Better?
Although we’ve already covered why relationships are better for your data model in a previous blog post, let’s recap why you should use relationships and STAR schemas instead of joins.
Although there are lots of advantage of Relationships, there are some things you should be aware of when transitioning from a flat file.
Overall, relationships are a HUGE upgrade for Tableau and their users. Although it’s not as sexy as Set Actions or Buffer Calculations, the effect that relationships will have on everyday business users is immense.
Thanks so much for reading! If you have any comments, suggestions, or feedback make sure to email me at firstname.lastname@example.org.