Optimise Relationship Queries Using Performance Options
Performance Options are optional settings that define the cardinality (uniqueness) and referential integrity (matching records) between the two tables in a relationship. These settings help Tableau optimise queries during analysis.
- If you aren't sure what to choose, use the recommended default settings indicated by Tableau. Using the defaults is safe and will automatically generate correct aggregations and joins during analysis. If you don't know the cardinality or referential integrity, you don't need to change these settings.
- If you know the shape of your data, you can optionally change these settings to represent uniqueness and matching between the records in the two tables.
In many analytical scenarios, using the default settings for a relationship will give you all of the data you need for analysis. In some scenarios, you might want to adjust the Performance Options settings to describe your data more accurately. For more details about using relationships to combine and analyse data, see Relate Your Data(Link opens in a new window) and this Tableau blog post: Relationships, part 1: Introducing new data modelling in Tableau(Link opens in a new window).
What the Cardinality and Referential Integrity settings mean
Cardinality options
Cardinality settings determine if Tableau aggregates table data before or after automatically joining the data during analysis.
- Select Many if the field values aren't unique, or you don't know. Tableau will aggregate the relevant data before forming joins during analysis.
- Select One if field values are unique. During analysis, the relevant data will be joined before aggregation. Setting this option correctly optimises queries in the workbook when the field values in the relationship are unique. However, selecting One when field values aren’t unique can result in duplicate aggregate values being shown in the view.
Note: Selecting One treats records as if each key value is unique and there is at most only one row with a null value.
Referential Integrity options
Referential Integrity settings determine the type of join used to get the dimension values for a measure during analysis.
- Select Some Records Match if some values in the field don't have a match in the other table, or you don't know. During analysis, Tableau uses outer joins to get dimensions values for a measure. All measure values will be shown in the view, even unmatched measures.
- Select All Records Match if values in the field are guaranteed to have a match in the other table. This setting generates fewer and simpler joins during analysis, and optimises queries. You might see inconsistent results during analysis (unmatched values removed or missing in view) if there are unmatched values in this table.
Notes: Selecting All Records Match treats records as if no null values exist in the fields used for the relationship. During analysis, Tableau will use inner joins to get dimension values for a measure. By default, Tableau will never join null keys.
For more information about Cardinality and Referential integrity as concepts, see Cardinality and Referential Integrity.
Where did joins go?
You can still specify joins between tables in the physical layer of a data source. Double-click a logical table to go to the join canvas.
Every top-level, logical table contains at least one physical table. Open a logical table to view, edit or create joins between its physical tables. Right-click a logical table, and then click Open. Or, just double-click the table to open it.
When you create a data source, it has two layers. The top-level layer is the logical layer of the data source. You combine data between tables in the logical layer using relationships.
The next layer is the physical layer of the data source. You combine data between tables at the physical layer using joins. For more information, see Logical and physical tables in the data model(Link opens in a new window).
Tips on using Performance Options
If you know the shape of your data, you can use the optional settings in Performance Options to establish the cardinality of the tables to each other (one-to-one, one-to-many, many-to-many) and indicate referential integrity (values from one table will always have match in the other table).
Instead of thinking of the settings in Performance Options as “yes” and “no”, think of them as “yes” and “I don’t know”. If you are sure that a table's values are unique, select One. If you are sure that each record in one table matches one or more records in the other table, select All Records Match. Otherwise, leave the default settings as they are.
If you aren't sure about the shape of your data, use the default settings. When Tableau can't detect these settings in your data, the default settings are:
- Cardinality: Many-to-Many
- Referential integrity: Some Records Match
If Tableau detects key relationships or referential integrity in your data, those settings will be used and indicated as "detected".
To reapply the default settings, click Revert to Default.
Terms defined
Cardinality refers to the uniqueness of data contained in a field (column) or combination of fields. When the tables you want to analyse contain many rows of data, queries can be slow (and performance of the overall data source is affected) so we recommend choosing a method for combining data based on the cardinality of the related columns between tables.
- Low cardinality: When related columns have a lot of repeated data. For example, a table called Products might contain a Category column that contains three values: Furniture, Office Supplies and Technology.
- High cardinality: When related columns have highly unique data. For example, a table called Orders might contain an Order ID column that contains a unique value for every order of product.
Referential integrity means that one table will always have a matching row in the other table. For example, a Sales table will always have a matching row in the Product Catalogue table.