Tableau Desktop and Web Authoring Help

If you open a workbook created before relationships were available (before version 2020.2), your data source may look different. If there were joined or unioned tables, they appear as a single logical table named Migrated Data. Double-click to see the underlying tables – they’re still there, and your analysis will still behave as expected.

Note that when you’re only using the Migrated Data table, the Data pane behaves more like previous versions of Tableau Desktop.

You can even relate new tables to the directly to the Migrated Data table to extend the data model.

Once you’ve added relationships to the data model, the Data pane updates to the new layout, with dimensions and measures maintained within each table, and the Migrated Data table is displayed as a single, merged table.

To build a data source of related tables, connect to your data and drag a table to the canvas. Drag out a second table. A relationship will automatically form if it can. If Tableau cannot detect the related fields, you will be prompted to select them yourself.

The canvas you’re seeing is a new layer of the data model where you can relate tables together. Dragging out additional tables adds them to the data model. The noodle shows which tables are related.

Relationships can be based on calculated fields. You can also specify how fields should be compared by using operators when you define the relationship.

Add joins

You can create a join by double-clicking on a table to open the join canvas (also referred to as the physical layer), and then dragging out a table to create a join. Creating joins is the same experience as previous versions of Tableau – you can set a join clause, including a calculation if necessary and configure the join type.

Once you close the join canvas, the table on the relationship canvas (also referred to as the logical layer) has a join icon to indicate the underlying join. This is because a join merges its tables together to form a single, new table. Related tables remain distinct.

Add unions

Add unions by dragging out New Union. Unions, like joins, actually merge the data and show an icon on the logical layer. We recommend always renaming unions – or any tables with unclear names – to something more useful.

For any relationship, there are performance options as well as related fields. Tableau automatically chooses safe defaults for these options. However, if you are sure of the characteristics of your data, changing these options can give Tableau more information about how to best configure automatic joins during analysis. Setting the cardinality and referential integrity options gives Tableau more information about how to optimise queries.

Cardinality

Cardinality indicates whether or not the linking field records are unique. If multiple books can have the same author, but each author is listed only once in the author table, the cardinality for AuthID would be Many for Books and One for Author between those two tables. This setting impacts how Tableau handles aggregation before or after the join.

Referential integrity

Referential integrity indicates whether or not a record in one table is guaranteed to have a relationship in another table. If every book has an author, Book has referential integrity to Author. If not every author has a book, Author does not have referential integrity to Book. This setting impacts how Tableau chooses a join type and handles unmatched records.

The Data Source page

Before we go to a sheet tab, notice that the data grid view, below, shows only the fields from one table at a time. If we click to another table, we’ll see the data it contains. There is no overall view because related data is not brought together until it’s used in an analysis. For now, the data remains in each table and is therefore displayed by table.

If we click on a table with an underlying join or union, we do see the merged data, with any nulls that may involve.

The Data pane

When we click onto a sheet, you may notice that the Data pane looks different than in previous versions of Tableau. Instead of the primary organisation of dimensions and measures, it’s now focused on tables. Each table has its own breakdown for dimensions and measures, indicated by this line. It’s possible to still group by folders instead, if you choose.

Number of Records and Count

There is also no Number of Records field. This is because the concept of Number of Records has changed. There isn’t a single Number of Records for the data source. Each table has a Count field, which can be thought of as a local Number of Records for that table.

To get a sense of the number of records for multiple tables at the same time, you can use Measure Names and Measure Values or multi-select the count fields and use Show Me to create a viz.

There are a few calculations we can do to make this data set a bit friendlier. First, we can create a calculation for the full author name that brings together first name and last name. We’ll create a calculation called Author Name that is simply first name, a space and last name. Note that this calculation stays in the Author table because it uses fields only from that table.

[First Name] + " " + [Last Name]

When we make a sale, the sales amount is a combination of the price – fixed by the publisher and specific to the edition or the ISBN – and any discount at the time of the sale. So the calculation Sales Amount will be price times one, minus the discount. Note that we’re using the ZN – or Zero Null – function to handle sales without discounts, we don’t get nulls. This calculation goes to the shared area at the bottom of the Data pane as it uses fields from multiple tables. Aggregated calculations will also go to the bottom of the Data pane.

[Price] * (1-ZN([Discount]))

We’ll create a set for books to identify if they’re in a series. In the Book table, right-click on Title and select Create > Set. We’ll name it In a series. On the Condition tab, choose By field with Series Name, Count, as >= 1.

In the Edition table, the Format field is quite detailed, including specifying mass market and trade paperback. Sometimes that detail is useful, but sometimes we want to generalise between hardcover and paperback. Right-click on Format field in the Data pane and select Create > Group. We’ll name it Cover. Control-click to select everything except Hardcover and then click Group. The name of the group is automatically highlighted, and we can call it Paperback.

Like with the Author Name calculation, these items we just created are relevant within a single table and therefore stay within those tables in the Data pane.

Joined

Here we’re looking at our joined data. On the joined viz, we’ll have a look at a mark’s underlying data.

This view data dialog has two tabs, Summary and Full Data.

• There are loads of rows for the same book – this is likely duplicated because of all the joins.

To handle the duplication, we’ll change the aggregation on the ISBN field in the view to Count Distinct. Remember that we’ll need to think about this duplication in almost every analytic scenario and address it with the correct aggregation, filtering or how we structure the viz.

Related

Let’s have a look at the structure of our related data source, and we’ll have a peek at what’s going on with our join as well. On the related viz, we’ll have a look at a mark’s underlying data.

And we see that the view data dialog again has two tabs, but this time it’s the Edition tab instead of Full Data. Every table used in the viz will have its own tab.

• There’s a doubling of rows for this book – and we know that joins can cause duplication, and the Book table in this data source is really the Book table joined with Info and Award.

We can create a viz with Title and Award Name and scroll down to investigate. And sure enough, The Mallemaroking has won two awards. It would be better to relate these tables than to join them.

We’ll go back to the Data Source tab and remove the Awards table from the join and relate it instead. And now we don’t have to worry about the duplication issue any more for this data source.

Let’s look at which authors we will be working with. Using the related data source, we’ll bring Author Name and Series Name to Rows.

Because related data sources pare down unmatched data in tables that only contain dimensions, we’re focusing on the data that is relevant to us. We can see that there are seven authors, two of whom have written for two series. If you don’t want to see only matched values, you can restore those unmatched values (which is more like the legacy behaviour) by going to the Analysis menu > Table Layout and checking Show Empty Rows.

For the joined data, we know there’s duplication because of the join, and we also know some authors span series. Because of this, we can’t just change the aggregation to, say, MIN or MAX, because we lose information for authors with works in multiple series.

What we really want is the number of events per series, visualised by author. This is a classic case for Level of Detail (LOD) expressions. We’ll create a calculation Series Events:

{FIXED [Series Name] : MIN ([Book Tour Events])}

Note that the MIN is to handle duplication of events for a single series.

Now if we bring this new field to Columns instead of the original events field, we’ll get the correct values.

For the related data, we didn’t need to do any of that. Relationships are smart enough to understand the native level of detail and the way the Author table relates through the Book and Info tables through to the Series table, and to correctly join and aggregate the Events measure back to Author Name – all without having to write LOD calculations.