Using a data source that has multiple, related tables affects how analysis works in Tableau. Because multiple, related tables accept independent domains and retain their native level of detail, when you drag fields into the view:

  • Data is queried at its natural level of detail.
  • But information that is relevant to a viz is queried.
  • The relationships between the tables affect the results of the query. The period of building a viz can vary depending on how tables of fields are related to each other in the data model, or if they aren't related directly.

For an overview of information source enhancements and an introduction to using relationships, meet this 5-minute video.

Notation: The interface for editing relationships shown in this video and in this topic differs slightly from the electric current release but has the aforementioned functionality.

Learn more about how relationships work in Don't Be Scared of Relationships and these Tableau blog posts:

  • Relationships, part 1: Introducing new data modeling in Tableau(Link opens in a new window)
  • Relationships, part 2: Tips and tricks(Link opens in a new window)
  • Relationships, part 3: Request questions beyond multiple related tables(Link opens in a new window)

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 encounter more.

Note: You tin still create single-tabular array information sources in Tableau. Y'all can build a logical table using a combination of joins, unions, custom SQL, and so on. The beliefs of unmarried-table analysis in Tableau has non changed. Assay over a single logical table that contains a mixture of dimensions and measures works just every bit in Tableau pre-2020.2.

Considerations for analysis

Validating relationships

You have several options for validating your information model for analysis. As you create the model for your information source, we recommend going to a sheet, selecting that data source, so edifice a viz to explore tape counts, expected data, unmatched values, nulls, or repeated measure values. Try working with fields across different tables to ensure everything looks how you lot expect it to.

What to look for:

  • Are your relationships in the data model using the correct matching fields for their tables?
  • Would calculation multiple matching field pairs brand the human relationship more authentic?
  • What are the results of dragging different dimensions and measures into the view?
  • Are yous seeing the expected number of rows?
  • If you lot changed any of the Operation Options settings from the default settings, are the values that y'all are seeing in the viz what y'all would expect? If they aren't, you might desire to cheque the settings, or reset to the default.

Options for validating relationships and the data model:

  • Every table includes a count of its records, equally a field named TableName(Count), at the level of detail for that table. To meet the count for a table, drag its Count field into the view. To encounter the count for all tables, select the Count field for each table in the Data pane, then click the Text Tabular array in Show Me.
  • Click View Data in the Data pane to meet the number of rows and data per table. Also, before you start creating relationships, viewing the data from the information source earlier or during analysis can be useful to give you a sense of the scope of each table. For more data, meet View Underlying Data.
  • Drag dimensions onto rows to see the Number of Rows in the status bar. To see unmatched values, click the Assay menu, and then select Table Layout > Show Empty Rows or Show Empty Columns. Yous can also drag different measures to the view, such as <YourTable>(Count) from one of the tables represented in your viz. This ensures that yous volition run across all values of the dimensions from that table.

Tip: If you would like to see the queries that are being generated for relationships, you lot can use the Performance Recorder in Tableau Desktop.

  1. Click the Assistance menu, and and then select Settings and Operation > Start Performance Recording.
  2. Drag fields into the view to build your viz.
  3. Click the Help carte, and then select Settings and Performance > Stop Performance Recording. The Performance Recording workbook will automatically open.
  4. In the Operation Summary dashboard, nether Events Sorted By Time, click an "Executing Query" bar and view the query below.

Another more advanced option is to utilise the Tableau Log Viewer(Link opens in a new window) on GitHub. You can filter on a specific keyword using end-protocol.query. For more than information, showtime with the Tableau Log Viewer wiki page(Link opens in a new window) in GitHub.

Dimension-only visualizations

When using a multi-table data source with related tables: If you build a dimension-simply viz, Tableau uses inner joins and y'all won't encounter the full unmatched domain.

To meet partial combinations of dimension values, you can:

  • Utilise Show Empty Rows/Columns to see all of the possible rows. Click the Analysis carte, and and then select Tabular array Layout > Show Empty Rows or Show Empty Columns. Be aware that this setting will also trigger densification for Date and Numeric Bin fields, which may exist unwanted.
  • Add a measure to the view, such equally <YourTable>(Count) from ane of the tables represented in your viz. This ensures that y'all will see all values of the dimensions from that table.

For more information, also see Unmatched values behavior for dimensions that might surprise you and Troubleshooting multi-table analysis.

When to use LOD calculations and expressions

Because Tableau understands the level of detail (LOD) of your input tables, you shouldn't demand to use LOD calcs to remove unwanted duplication due to joins.

Y'all may however desire to use LOD calcs to:

  • Handle unwanted duplication in your source tables.
  • Compute multi-level aggregations (e.k. an average over a sum)
  • To do cohort analysis (e.g. to compute the first order date for each client)

If the dimensionality of an LOD calc includes fields from a single table, that LOD calculation will appear in its owning table in the Information pane.

Examples of multi-tabular array analysis

The following ready of examples show how data might be queried beyond multiple, related tables. This multi-table data source contains a curt list of actors' appearances in different picture roles.

A row in the Appearances table ways that an Thespian played a detail Grapheme in a specific Movie. In this data set, an Actor can take zero or more than Appearances.

Acquire more virtually how relationships work in these Tableau blog posts:

  • Relationships, part 1: Introducing new data modeling in Tableau(Link opens in a new window)
  • Relationships, part 2: Tips and tricks(Link opens in a new window)

When you connected to data in Tableau before 2020.2, your data source could consist of a single table, or multiple tables that were joined or unioned together into a single, denormalized table. Starting with Tableau 2020.ii, Tableau recognizes and preserves normalized data for multi-table data sources, where the tabular array data remains separate, and each tabular array maintains its native level of particular. The post-obit examples evidence how analysis differs between single table and multi-tabular array information sources.

This example shows three tables of movie information: Appearances, Actors, and Movies.

These tables can be joined together, joining the Appearances tabular array with the Actors tabular array on Player = Actor and the Appearances tabular array with the Movies table on Pic = Moving-picture show. If the joins are full outer joins and then no rows are lost, the final output looks like this. Annotation that fields that are used in the join clauses appear twice.

Information that is brought together into a single table in this mode is chosen denormalized or flattened.

This joined data is flattened data. Each row consists of an actor's appearance as a grapheme in a movie (so John Rhys-Davies has 2 rows for The Return of the King because he played two characters); therefore the granularity of the data is at the level of a grapheme in a movie. Information that is relevant in multiple rows is duplicated. The Return of the Rex'due south premiere engagement is present twice because there are 2 characters from that pic in the data ready. John Rhys-Davies' height is listed 5 times because in that location are 5 unique combinations of graphic symbol/movie for him as an actor.

This flattened data therefore has some characteristics to be aware of. For example, if you wanted to plot actor tiptop by the average gross of their movies, you might assume we could bring out Height to columns and Gross to Rows, then take the boilerplate of the Gross. Simply if you lot do that the default view doesn't seem right. Here John Rhys-Davies is supposed to be 925cm tall, which is over 30 anxiety!

This is because the default aggregation is SUM. There are v rows in the information for him, so nosotros get his truthful top of 185cm 5 times. You could set this by changing the assemblage on Height, say to boilerplate or minimum. This finer returns the value for only i row (since they are all the same).

When you change the aggregation the heights are much more realistic. But at present you lot need to pay attention to average gross. Remember that Tableau considers all five rows for John Rhys-Davies' height. When you lot think about the average gross for the movies he was in, it shouldn't be the average of five rows, simply rather the average of three movies. You don't want to count the gross for Return of the King twice just considering he played two characters in it. Only is that what is happening?

Doing some quick math, the Lord of the Rings movies should have an average of (869 + 923 + 1119)/3, or $970.3. However, the value in the scatterplot is $990.6. The current average is from the 5 rows (869 + 923 + 923 + 1119 + 1119)/5.

This isn't as easy to fix every bit the top effect with changing the assemblage. You need to use a Level of Detail (LOD) expression to alter which level of detail Tableau looks at, going from the default—Appearance—upward to the level of Moving picture. You lot could create a calculation for LOD Gross as {Fixed [Moving-picture show] : MIN([Gross (USD millions)])} and so take the boilerplate of that new LOD Gross field for our view.

The LOD expression can be read equally "for each movie, return its minimum Gross". This removes the duplication problem considering the gross is ever returned per picture show, even though the view is constructed with movie and histrion.

Now the numbers are correct. John Rhys-Davies is 185cm alpine, and the average gross for his movies in this information set is 970.3. You would need to understand how data was being replicated and how Tableau aggregated the data to display it before yous could ensure the correct values were returned.

Normalized data in multiple tables

Creating relationships between logical tables may seem similar to creating joins, but instead of flattening the data into a single table, with all the replications that tin entail, Tableau stays aware of the relationships between the tables. Information is brought out of each table at the appropriate level of item and related to other data.

On the Data Source page, you won't come across the "full" grid view of the flattened tabular array. It doesn't exist. Tableau keeps all 3 tables every bit they are and merely establishes the relationships, pulling together the required data every bit needed by the view.

To create the aforementioned scatterplot, drag Elevation and Gross into the view, and gear up Gross to boilerplate. That's it! Tableau looks at the way each table'due south data is related to data from other related tables and infers how the height should be displayed (by actor) and how the average gross should exist computed (by movie).

Example 2: Dimension from a single table

If the dimensions in your visualization come from a single table, Tableau queries merely the i table and shows results for the entire domain. You can add measures and still encounter the entire domain.

For example, using the Movie Appearances data source introduced higher up, adding the Actor field to a visualization results in the following visualization:

Considering the only dimension in the viz came from the Actors table, Tableau will run a query against just the Actors tabular array. All actors that appear in the Actors table are displayed in the viz, regardless of whether they take whatever Appearances.

Bringing the Appearance Actor field into the view as a measure, and so applying a COUNT assemblage creates a view that shows the number of appearances by actors. Notice that Sigourney Weaver does not have any appearances, but her name is still in the view.

Example iii: Dimensions from multiple tables

If the dimensions in the view are from multiple tables, Tableau finds the table that relates all the dimensions and shows the domain from that table. As a effect, some dimension values that y'all saw in Case 1 modify.

For example, dragging a field from the Movies table into the viz changes the query. Because the Movies and Actors tables are related by the Appearances table, the query returns just the Thespian/Movie pairs that exist in the Appearances table.

Considering Sigourney Weaver does non take whatever Appearances in this data gear up (and, thus, is not related to any of the Movies in the data ready), the viz of Actor/Film pairs does not bear witness her:

Example four: Measures that can't exist split by a dimension

If a measure can't exist split by a dimension, Tableau replicates the measure across that dimension.

The next visualization shows the full amount grossed by Movie. Since both fields come from the Movies table, Tableau will query simply the Movies table.

The Movies tabular array includes the already aggregated Gross receipts for each movie, as shown in the following visualization (the gross for Infinity War was not available at the time this data set was created and is shown equally nil).

If you add together Actor to this visualization, Tableau knows that it cannot break down the Movie Gross receipts by Actor, since this more granular data is non available in the information model. Instead, Tableau shows the Pic Gross receipts for each picture show, replicated across actors.

Removing the Pic dimension from the previous visualization queries the sum of the Movie Gross measure for each Actor. The result is the aggregated Movie Gross for every motion picture the player appeared in.

In this case at that place isn't a hierarchical human relationship between the dimension, Actor, and the mensurate, Pic Gross—multiple actors may have appeared in the same film. For example, Benedict Cumberbatch and Chris Hemsworth both appeared in Dr. Strange. In this case, Tableau includes the Movie Gross for Dr. Foreign in both actors' totals.

Considering the aforementioned Movie Gross value is included in multiple actors' totals, Tableau won't direct sum over these values.

Nevertheless, when totals are shown for this visualization, note that Tableau correctly computes the total Movie Gross without including duplicate movies.

Troubleshooting multi-table analysis

The following situations are possible with multi-tabular array, related tables. This table describes known scenarios and loftier-level descriptions for how to troubleshoot your analysis.

Analysis Situation Description
Inner join for dimension-only vizzes You may non immediately see all of the values you were expecting when you add multiple dimensions to the aforementioned view. Or, you might detect that adding a new dimension to the viz from a different tabular array causes some values to disappear from the viz.

Tableau will use queries that preserve the combination of values that really exist in the data. This means that yous will see rows that would be produced past an inner bring together of the tables providing dimensions to the viz.

If you want to see partial combinations of the dimension values, you can either turn on Show Empty Rows/Columns to see all of the possible rows, or y'all can add a measure, such as <MyTable>(Count), from i of the tables represented in the view to ensure y'all run across all values of the dimensions from that table.
Constants in calculations In a multi-table datasource, constant values behave every bit though they come from their own tabular array with a unmarried row. If yous amass a constant value, it will behave as though the aggregate was on a single row. Sum(10) volition always equal x. Avg(x) will also always equal 10. Count(10) will always equal 1.

To ensure backwards compatibility, abiding values on a single-logical tabular array information source will deport equally though the constant value is replicated for each value in the table.

Constants in row level calculations do not alter the row level of detail of the calculation. The calculation [Sales] + 10 will behave equally though it came from the same table as the [Sales] field.
Forcing outer join Tableau ensures that all measure values are represented in the viz (forth with all combinations of dimension values that really occur in the data) then if you lot desire to ensure you are seeing all of the possible values in your data including "unmatched nulls" yous can do then past bringing measures into the view from each table in the worksheet.
I'thou not seeing the measure values I would expect from a cross-tabular array calculation The domain of a adding is the inner bring together of its inputs. If there are not matching values from all of the inputs of the mensurate adding they will non be included in the mensurate calculation.

Consider using an LOD calculation to move the mensurate values to the same object earlier creating the row-level calculation.
Calculation to switch betwixt fields from different logical tables gives unexpected results If you have a calculation that switches between row level fields, either using a instance statement, if statement, or a office like "IFNULL", you lot may see unexpected results, because this calculation is being computed for every row, where row is the inner join between the inputs of the calculation.

A amend approach is to switch betwixt Aggregated values, rather than trying to switch within the row level adding. This volition also produce better performance in single table scenarios.

Alternately, this is only an result for calculations that span between tables, so using LOD calculations to bring all of the fields to the same table will work equally well.

Don't do this:
SUM( IF [Parameter] == "Foo" And then [Field i] ELSE [Field 2] END )

Practise this:
IF [Parameter] == "Foo" THEN SUM([Field 1]) ELSE SUM([Field two]) Terminate

Unexpected unmatched nulls You may see measure values associated with a null dimension value that you didn't expect. This tin possibly be a sign that the relationships in the information source were incorrectly configured. It may also indicate that in that location are really unmatched values from the table that contains the mensurate, that accept no respective row in the dimension table.

In the by, this information might have been lost if the wrong bring together type was selected. When you utilize relationships, these unmatched values are preserved. If you don't want to see the unmatched values, you tin can exclude them using a filter.
Incorrect aggregate values Are you using relationships or joins? With relationships, aggregates are computed correctly past default. When using joins, you lot may demand to write LOD calcs to deduplicate values.

Take you incorrectly set Functioning Options on relationships? Attempt resetting the Operation Options to the default and see if that produces the correct aggregations.
Dimensions replicate measure values rather than partitioning them.

Dimension filters don't subset measures.

 Check that the fields used to define relationships are correct.
Tableau is generating too many queries or queries with many left joins Check the logs or operation recording to see how many queries are generated and how many left joins are used. With the new data modeling capabilities, Tableau generates queries with left joins and/or additional queries to make sure that unmatched measure values are always included in the visualization. If y'all don't demand to see the unmatched values, use filters to remove the unmatched (NULL) values from your visualization. This should result in fewer queries.

If you know that your data doesn't have whatsoever unmatched values, y'all tin ready the Referential Integrity setting for each relationship to "All Values Lucifer" in Performance Options. This will also event in fewer queries.

You can also reduce the complexity of your visualization to reduce the number of queries generated. Removing measures and hiding Filter controls are key ways to simplify queries multi-table, related data.
Queries have many subqueries Check the logs or functioning recording to see the complication of the queries Tableau generates.

Tableau automatically generates subqueries to deduplicate data, when necessary, to generate correct aggregates. This is similar to queries generated by LOD calculations.

If you know the relationships betwixt the logical tables in your data have many-to-ane or one-to-one cardinality, and so you tin ready this cardinality information in the human relationship Performance Options. This volition allow Tableau to eliminate unnecessary subqueries since information technology volition know that no duplication tin can happen.
I was using joins to filter data In 2020.2, Tableau will work hard to recover unmatched values. Sometimes that means it will use a left join where you might take specified an inner join to intentionally filter out data.

If you filter out the unmatched values that are brought in past this join, Tableau will be able to optimize the query back to an inner join.

Depending on your specific scenario, it might make sense to model this inner join equally a concrete join within a logical table. This is especially powerful if a table containing measures is being used to filter a dimension table, equally it will not introduce additional measure replication.