Tableau Desktop and Web Authoring Help

These are some of the questions we've heard related to data modelling and relationships in Tableau. If you have a question that isn't in this list, please share it with us by clicking the blue Send feedback icon in the bottom right corner of this Help page, (click Yes or No, add your question in the Comment field, and then click Send).

Relationships

Is a relationship just a different name for a join?

Relationships are a dynamic, flexible way to combine data from multiple tables for analysis. A relationship describes how two independent, logical tables relate to each other, but does not merge the tables together (What is a logical table?). When a relationship is created between tables, the tables remain separate (normalised), maintaining their native level of detail and domains. You can use relationships to create multi-fact data models.

You can’t set a join type for relationships. Relationships defer joins to the time and context of analysis. Tableau automatically selects what join types should be used based on the current fields in use in the viz. During analysis, Tableau adjusts join types intelligently and preserves the native level of detail in your data. You can see aggregations at the level of detail of the fields in your viz rather than having to think about the underlying joins.

Relationships can be many-to-many and support full outer joins. You don't need to use LOD expressions such as FIXED to deduplicate data in related tables.

Can I use joins between logical tables?

You must use relationships between logical tables. You can only use joins between physical tables contained in a logical table. Double-click a logical table to open it.

Where did joins go? Can I still combine table data using joins?

Joins are still an option for combining your data. You just need to open a logical table to work with joins (double-click a logical table to open it). Tables that you join get merged into a single logical table.

Where did joins go?

Are relationships like blends? When should I use a blend?

While both relationships and blends support analysis at different levels of detail, they have distinct differences. If you want to combine data from published data sources, blends are currently your only option.

You might also choose to use blends when the fields used to join two tables are dependent on the worksheet.

Blends only support left joins, while relationships support full outer joins. Blends can be customised on a per-sheet basis.

Relationships versus blends

Data sources, data model and connections

How have data sources changed?

In Tableau version 2020.1 and earlier, tables that you combined in the Data Source page using joins and unions made up the data model in a data source. The data was merged into a single, flat table.

Starting in Tableau 2020.2, a new logical layer has been added to Tableau's data model. The logical layer and relationships expand your options for bringing the data you need into Tableau. You can now create normalised data sources with multiple tables at different levels of detail. Related tables aren't merged; they remain distinct. Using relationships makes creating and analysing data across multiple tables more flexible and more intuitive.

You can still create single-table data sources in Tableau. You can build a logical table using a combination of joins, unions, custom SQL and so on. The behaviour of single-table analysis in Tableau has not changed. Analysis over a single logical table that contains a mixture of dimensions and measures works just as in Tableau pre-2020.2. When you upgrade a workbook or data source to Tableau version 2020.2 and later, it will contain a single logical table that represents your pre-2020.2 data source, and it will work the same as it did before.

What are logical tables and physical tables?

Starting in Tableau version 2020.2, data sources use a data model that has two layers: a logical layer where you can relate tables and a physical layer where tables can be joined or unioned. Tables that you drag to the logical layer use relationships and are called logical tables. Every logical table can contain one more physical tables in the physical layer.

For more information, see The Tableau Data Model.

Can relationships exist between tables from different connections?

Yes. You can create a data source with relationships between tables from different connections. For more information, see Combine Data in Plan the Data Source.

Can I use inequality operators or calculated fields to define relationships?

In Tableau 2020.3 and later, you can create relationships based on calculated fields, and compare fields used for relationships using operators in the relationship definition.

Note that the following connectors do not support inequality operators:

• Google BigQuery
• MapR
• Salesforce

Do all connection types support logical tables and relationships?

Most relational connection types are completely supported. Cubes, SAP HANA (with OLAP attribute), JSON and Google Analytics are limited to a single logical table in 20.2. Stored procedures can only be used within a single logical table.

Published data sources can't be related to each other.

Unsupported

• Cube databases do not support the new logical layer. Connecting to a cube offers the same experience as pre-2020.2.
• Stored Procedures: Don't support federation, relationships or joins. They are represented in a single logical table, and don't allow opening the Join/Union canvas (physical layer).
• Splunk: Doesn't support left joins (and therefore relating logical tables).
• JSON: Doesn't support federation, custom SQL, joins or relationships (only unions).
• Datasources that do not support LOD calcs. For more information, see Data Source Constraints for Level of Detail Expressions.

Limited support

• Salesforce and WDC Standard Connections: These are represented as joined tables within a logical table. Adding them is currently only supported for single logical table data sources. Standard connections cannot join to an existing table.
• SAP HANA: Doesn't support relating logical tables when the connection has the OLAP attribute set.

What types of data models are supported?

See The Tableau Data Model and Supported data model schemas

Is there a classic view of the Data Source page that I can use?

The physical layer of the Data Source page canvas is basically the "classic view" of the Data Source page in previous versions of Tableau.

The default view of Data Source canvas is the logical layer in Tableau version 2020.2 and later. You must double-click a logical table in the Data Source page canvas to go to the physical layer of the canvas.

How do I create a single-table data source?

Drag a single table into the canvas (logical layer) of the Data Source page. In the worksheet, use the fields from that table in the Data pane for analysis.

You can add more data inside the single, logical table by double-clicking the table. This opens the physical layer of the Data Source page canvas. If you need to use joins or unions, you can drag the tables you want to join or union into the physical layer canvas. The physical tables are merged into their single, logical table.

This example shows the Book table in the Relationships canvas (logical layer) of the data source. Double-clicking the Book logical table opens the Join/Union canvas (physical layer). The joins merge the Award and Info tables with the Book table.

Has row-level security changed?

Row-level security works the same. For information on row-level security, see Best Practices for Row Level Security(Link opens in a new window) in Tableau Server help.

How do I use custom SQL in the new data model?

Double-click the New Custom SQL option in the left pane of the Data Source page (as before). The custom SQL will be contained by a single logical table.

What happens to my older data sources when I open them in Tableau version 2020.2 and later?

The data is migrated without changes and you can continue to use the workbook as you did before.

When you open a pre-2020.2 workbook or data source in Tableau version 2020.2 and later, your data source will appear as a single logical table in the canvas with the name "Migrated Data". You can rename the Migrated Data table.

Your original, denormalised data may have been originally built from one or more tables using joins and unions. When you open the data source in Tableau version 2020.2 and later, Tableau migrates the denormalised data model to a single logical table in the new data model to ensure that your data and workbooks are migrated without changes.

To see the physical tables that make up the single logical table, double-click that logical table to open it in the physical layer. You will see its underlying physical tables, including joins and unions.

Do I need to change my migrated data source to use relationships instead of joins?

If you have existing multi-table data sources defined using physical layer joins, they will still work as they did before. You don't have to change your data source. You can relate logical tables to your migrated table, but you can't downgrade logical tables.

You might consider changing you data source to use relationships instead of joins if you want to add more tables to your data model. This will require you to remove tables from the physical layer, and then add them to the logical layer.

Can I use a data source on Tableau Server or Tableau Cloud (version 2020.2 and later) in a previous version of Tableau Desktop (version 2020.1 and earlier)?

If you attempt to open a published data source or published workbook from Tableau Server or Tableau Cloud version 2020.2 and later in a previous version of Tableau Desktop (version 2020.1 and earlier), any logical tables that were related to the root table in the data source will be removed. Only the root table (the first table added to that data source model) will remain.

If you attempt to use a local data source in a previous version of Tableau:

• Tableau displays a warning that the data source is from a newer version of Tableau that isn't compatible with the previous version.
• On the Data Source page, only the root table will remain in the data source.
• In the Data pane in a Tableau Desktop worksheet, Tableau displays errors (red exclamation points) next to affected fields that are not part of the root table.

If you attempt to use a published data source in a previous version of Tableau:

• Tableau displays a warning that the data source is from a newer version of Tableau that isn't compatible with the previous version.
• In the Data pane in a Tableau Desktop worksheet, Tableau displays an error (red exclamation point) next to the selected data source and a message that the connection needs to be updated. Click Show Details for more information about the error.

Can published data sources be related to each other?

You can't relate published data sources to each other. But you can edit and view the data model for a published data source.

If you want to combine data from published data sources, blends are currently your only option.

Relationships versus blends

How do extracts work with related logical tables?

Extract data is now stored based on logical tables (replaces the Single Table option) or physical tables (replaces the Multiple Tables option). For more information, see Extract Your Data.

If I need to downgrade to Tableau version 2020.1 and earlier, what happens to relationships between logical tables?

If you downgrade a workbook to Tableau version 2020.1 and earlier, any logical tables that were related to the root table in the model will be removed from the data source. Only the root table (the first table added to that model) will remain.

All sheets in the workbook used fields from logical tables (non-root) become invalid, because their tables and fields have been removed from the data model.

Downgrading works best for workbooks that contain single-table data sources.

How does the new data model work with cubes?

Cubes work the same as in previous versions of Tableau. A cube data source will appear as a single-table data source, just like it is today. You can't create relationships using cube data.

Interaction with other Tableau features and products

Does Explain Data work with multi-table data sources that use relationships?

In Tableau version 2020.3 or later, you can use Explain Data with data sources that contain multiple related tables. Cardinality and Referential Integrity settings for relationships must be set up correctly for Explain Data to analyse multi-table related data.

In Tableau version 2020.2 and earlier, you can use Explain Data with single-table data sources only. Your data source can have a single, logical table that is defined by one or more physical tables.

Does Ask Data work with multi-table data sources?

Ask Data fully supports multi-table, normalised data sources.

How do new data modelling capabilities affect using Tableau Bridge?

Using the latest version of Tableau Bridge should provide full compatibility with Tableau version 2020.2 and later data modelling functionality.

When should I use Tableau Prep vs authoring in Tableau Desktop, Tableau Cloud or Tableau Server to create a data source?

Tableau Prep cleans data, and creates flows, extracts and published data sources that contain physical tables.

In Tableau Desktop, and in Tableau Cloud and Tableau Server web authoring, you can create data sources that use normalised data models. These data models can be made of logical tables and physical tables, and your data sources can be saved as live data sources or as extracts.

Only logical tables can be related. Physical tables can be joined and unioned.

Analysis with multi-table data sources

Does analysis work differently with multi-table data sources that use relationships?

Using a data source that has multiple, related tables affects how analysis works in Tableau. Because multiple, related tables have 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.
• Only data that is relevant to a viz is queried.
• The relationships between the tables affect the results of the query. The flow 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 more information, see Don’t Be Scared of Relationships(Link opens in a new window), How Analysis Works for Multi-table Data Sources that Use Relationships, Unmatched values behaviour for dimensions that might surprise you, and Troubleshooting multi-table analysis.

Do LODs work the same with the new data model? When should I use an LOD?

LOD expressions and calculations work the same. Since Tableau now understands the level of detail (LOD) of your input tables, you shouldn't need to use LOD calculations to remove unwanted duplication due to joins.

You may still want to use LOD calcs to:

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

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

How can I tell if I combined my data correctly with relationships?

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

What to look for:

• Are your relationships in the data model using the correct matching fields for their tables?
• Would adding multiple matching field pairs make the relationship more accurate?
• What are the results of dragging different dimensions and measures into the view?
• Are you seeing the expected number of rows?
• If you changed any of the Performance Options settings from the default settings, are the values that you are seeing in the viz what you would expect? If they aren't, you might want to check the settings, or reset to the default.

Options for validating relationships and the data model:

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

Can I see the queries that Tableau is generating for relationships?

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

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

Another more advanced option is to use 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 information, start with the Tableau Log Viewer wiki page(Link opens in a new window) in GitHub.

Changes to different parts of the interface

How has the Data Source page changed? The data grid? View Data? The Data pane?

The Data Source page (canvas, data grid), View Data and Data pane have been updated to support a multi-table analysis experience.

Your first view of the data source canvas is now the logical layer, which is where you define relationships. The data grid shows row data for each table’s level of detail.

In the Data pane, fields and calculated fields are automatically organised by their native level of detail.

The View Data window displays row level data at the correct level of detail, without replication, to make validation easier. Get row counts for each table in the Data pane using Table(Count) fields.

Data Source page changes

Data Grid

View Data

Data pane in worksheets

View Data column order changes in Tableau 2020.2 and later

When you open a data source from a previous version of Tableau in Tableau 2020.2 and later, the column order may be different. Columns may be displayed differently in the View Data window, and the column order may be different when you export it to CSV format.

This change in how column order is handled in the View Data window is required to support relationships and logical tables.

If you use scripts that depend on a customised column order, we recommend using the Tableau Extensions API for View Data to download underlying data to CSV format.

How does Count of Table work versus Number of Records?

You will no longer see the Number of Records field in data sources that contain logical tables. Every table includes a count of its records, as a field named TableName(Count), at the level of detail for that table. In the previous example you can see Addresses(Count), Customers(Count) and LineItems(Count).

COUNT of table = SUM of Number of Records per table. You can't build calculations on top of a table's Count field. Count is aggregate-only.

Note: You might see the Number of Records field in the Data pane if you open a pre-2020.2 workbook that used Number of Records in a view.

Where are sets, groups and calculated fields shown?

If the field belongs to a table, it is listed under the table. If it doesn’t, it is listed in the general area at the bottom of the Data pane.

Calculated fields are listed with their originating field, if all of their input fields come from the same table.

Sets and groups are displayed under the table with their originating field.

Fields that don't belong to a specific table are displayed in the general area below the tables. These include: aggregated calculations, calculations that use fields from multiple tables, Measure Names and Measure Values.