Don’t Be Scared of Relationships

The new data modelling capabilities create flexible data sources built around relationships.

Relationships combine data from different tables by looking at what columns (fields) those tables have in common and using that information to bring information from each table together in the analysis.

Unlike joins or unions, relationships form a data source without flattening multiple tables into a single table. Because of this, related data sources know which table each field is from. That means each field keeps its context or level of detail. Related data sources can therefore handle tables with different granularity without issues of duplication or data loss.

In a related data source, the joins aren’t fixed up front. Instead of merging all the data (and having to work with all the data regardless of what each viz requires), only the relevant data is combined – per sheet and as necessary. As you drag and drop, Tableau evaluates the relationships of the relevant fields and tables. Those relationships are used to write queries with the correct join types, aggregations and null handling.

You can think about how the data fits together and what questions you want to answer, rather than how to combine the data or compensate for artefacts from the data source.

Relationships don’t replace the previous ways of combining data: joins, unions and blends. Rather, relationships are the new, flexible way to bring data together from multiple sources. The existing methods aren’t going away and are still useful in specific scenarios.

Use Relationships

This topic walks through building a related data source and using one for analysis. If you would like to follow along, you can download the Bookshop data set(Link opens in a new window). Choose Bookshop.xlsx for the raw data to start from scratch, or MinimalBookshop.tdsx to start with the related data source basics configured for you.

Note: Relationships are available in Tableau 2020.2 and later.

Video: Migrated Data

If you open an existing workbook from a previous version of Tableau (version 2020.1 or earlier) in Tableau 2020.2 or later, your data source tab might show a Migrated Data table.

There are multiple videos embedded in this topic. For your convenience, a transcript is provided in the expandable section underneath each video.

Migrated Data video transcript

Video: Relate tables

Note: The interface for editing relationships shown in this video differs slightly from the current release but has the same functionality.

Relate tables video transcript

Video: Performance Options

Note: The interface for editing relationships shown in this video differs slightly from the current release but has the same functionality.

Performance Options video transcript

For more information, see Cardinality and Referential Integrity.

Example: Bookshop Data

We’ll do some analysis with this data source in a moment, so let’s talk about the data.

The data set is around (fictitious) books, and it’s important to consider the distinction between a book and an edition. A book is the conceptual work itself, with attributes such as the title, author and genre. There are also editions of a book, with attributes such as a price and format (hardcover or paperback), that are identified with an ISBN. An edition of a book has a publisher and a page count, whereas a book may have won an award or may be in a series.

You can follow along by downloading MinimalBookshop.tdsx, or be adventurous and build the model yourself from Bookshop.xlsx. Note that you can hide many of the ID fields once the tables are combined.

Video: Work with multiple tables

Note: The interface for editing relationships shown in this video differs slightly from the current release but has the same functionality.

Work with multiple tables video transcript

Video: Basic calculations

Basic calculations video transcript

Video: Sets and groups

Sets and groups video transcript

Let’s do some comparisons of how analysis looks between relationships and joins. We recommend that you download the companion workbooks for Question 1 and Question 2 rather than continue with your own data source from above. If you identify issues with the data source configuration or steps for Question 1, note that they are intentional for the purposes of the scenario.

Question 1: How many editions are there for each book? 

Important: The video for Question 1 was recorded on beta software. The released version of 2020.2 correctly handles our attempt to introduce an error in the related data source. If you follow along, your workbook will not match the video – the relationships values will be correct. Please bear with us while we attempt to outsmart our product and force an error for educational purposes.

We’ll set this up by doing some basic analysis with both data sources. We’ll build a bar chart with each one, looking at the number of editions by title.

RelatedJoined

Using the related data source:

  1. Bring Title to Rows
  2. Bring Edition (Count) to Columns
  3. Click the Label icon in the toolbar to turn on labels
  4. Click the Sort Descending icon in the toolbar

 

Using the joined data source:

  1. Bring Title to Rows
  2. Bring ISBN to Columns
  3. Right-Click on the pill and select Measure > Count
  4. Click the Label icon in the toolbar to turn on labels
  5. Click the Sort Descending icon in the toolbar

It’s immediately clear that Count is not the right aggregation for the joined data. However, something isn’t right with the related data, either. Let’s view the data and see what’s going on and how to fix it. Watch the video (or read its transcript) to see how.

Video: Data validation and fixing the data model

Question 1 video transcript

Question 2: For authors with books in a series, who has the most book tour events?

Video: Work with different levels of details

Question 2 video transcript

So don’t be scared of relating your data. See for yourself! You can download the Bookshop data(Link opens in a new window) or use some of your own. Try filtering, using table calculations, building a variety of chart types, configuring performance options and pushing relationships as far as you can.

  • View Underlying Data often to verify what data a mark represents.
  • If you aren’t required to join – and there are reasons you may need to – using a relationship provides more flexibility.
  • If you don’t want to see only matched values, you can restore those unmatched values by going to the Analysis menu > Table Layout > Show Empty Rows.

Ready to tackle calculations with Relationships? Check out Don’t Be Scared of Calculations in Relationships.

Ready to keep exploring how to do complex analysis with Relationships? Check out Don’t Be Scared of Deeper Relationships.

For more information on the technical underpinnings of relationships straight from the Product Management team, check out the series on relationships on the Tableau Blog.

Also see video podcasts on relationships from Action Analytics(Link opens in a new window), such as Why did Tableau Invent Relationships?(Link opens in a new window) Click "Video Podcast" in the Library(Link opens in a new window) to see more.