I’m putting myself on the spot here, and going off top. I made a twitter comment as a joke, stating “you have my respect if you can describe a tableau relationship.”
So as a Salesforce partnered tableau trainer, I’m going to hold myself accountable and give it a go.
For a summarized list of the most important features of a tableau relationship, please scroll to the bottom of the page.
Alright, let’s dive in. 🐇
A tableau relationship is a tableau patented data model, that allows a user to grab data from multiple tables.
The join type between the tables is decided on the worksheet level when one decides to bring fields from these ‘related’ tables into a worksheet view. Tableau coins this action as a contextual join.
Bonus note: If you’d like to see the underlying SQL behind the table join when bringing fields into a worksheet view, one can simply take a performance recording while building out a visualization using fields from related tables, and select the “executing query” marks in the performance report.
Ok, I digress. The benefits of a relationship are avoiding data duplication, and we also do not have to worry about losing any data in the case that our tables do not have matching value from their key fields. The latter part probably sounds like a full outer join, and from my observation this is typically the behavior we will see on the worksheet view, but on the backend tableau uses nested LEFT joins to accomplish the task (*see performance recording for details).
Data duplication issues can arise in the case that tables may have a many to many cardinality, which would create a potentially unintentional cartesian product (*please message directly if you’d like to see a live sample of this problem).
Measures used from related tables are aggregated at the level of detail of the table that it originates from.
The tableau relationship is tableau’s default way of using multiple tables when connected to a multi-table data source. The tables are related on the logical layer of the canvas in the the data source tab. Tableau will try to find a common field or key field between the tables in order to join records at the worksheet level.
Ok, that’s it. As I write, I realize how non sensical this will all seem to non-tableau users, or tableau users without extensive table relationship experience. I question if I should do technical writing when platforms have extensive documentation online, but I do believe this is the reason platform trainings exist. Having a human teach a class with demos and practices, will trump simply having to read articles like these or sift through loads of documentation.
Anyway this was my best for now, and I’ve synthesized a list of the main points to help guide you as you practice using tableau relationships in Tableau.
List of Important Tableau Relationship Features:
1. Occurs on the logical layer — default action when using multiple tables on our canvas
2. Requires a key field to relate tables on
3. Avoids data duplication and issues that could arise with a cartesian product (i.e. caused by many to many cardinality relationship between tables)
4. No data is lost on the worksheet level
5. Measures used are aggregated at the level of detail of the table that it comes from
6. The join type between tables are decided when we bring fields out into our view — tableau coins as a contextual join
I’m leaving this here for records sake. I believe this structure is quite beautiful and solves the complex issue that can arise when using fields from multiple tables that are often of different levels of detail.
I think it would be ideal if transforming and joining tables are done in a data warehouse tool before ingestion in Tableau, but coming from the Tableau side it’s nice to see the product has built in the flexibility to solve complex issues such as this one and maintain data integrity for reporting users to really get into their analysis.
On that note, see you in the next one and follow the data. 🐰
