Tableau Desktop and Web Authoring Help

Multi-fact relationships let you build data sources with more than one base table. Using multiple base tables in your data model allows you to perform multi-fact analysis in Tableau.

By establishing trees of tables, rooted in a base table, you can model data structures with different conceptual domains and use their shared characteristics to connect them. This type of analysis is often referred to as multi-fact analysis, conformed dimensions or shared dimensions. In Tableau, we call this a multi-fact relationship data model because you use relationships to build it. A multi-fact relationship data model always contains multiple base tables. Base tables are the left-most tables in the data model. For guidance on how to determine which tables to use as base tables, see When to Use a Multi-fact Relationship Model.

A multiple base table data model with one base table's tree highlighted.

Levels of relatedness

Data models with multiple base tables have a lot of flexibility regarding how pieces of data can relate – or not relate – to each other.

Note: Relatedness at any level is only relevant in data models with multiple base tables. Prior to multi-fact relationship data models, either everything was related (within a single data source) or nothing was (blending across multiple data sources).

Relatedness in the data model

Tables are related, unrelated or shared based on the structure of the data model. In a data source, the relatedness of tables is a constant. As a brief overview: 

• Related tables are in the same tree.
  • Prior to 2024.2, all data sources were single base table data sources consisting of a single tree, and in a single base table data source all tables are related.
• Unrelated tables are in different trees. Base tables are always unrelated to each other. Tables that are downstream of exactly one base table are also unrelated to tables in other trees.
• Shared tables have multiple incoming relationships and belong to more than one tree.
  • Tables downstream from a table with multiple incoming relationships are also considered shared.

Relatedness during analysis

Fields can be related, unrelated, not yet related or ambiguously related, or they can act as stitching fields. The relatedness among a group of fields is determined on a sheet-by-sheet basis based on the structure of the data model, what fields are actively in use (that is, on the shelves as pills) and if those fields are dimensions or measures.

To make a visualisation with fields from multiple tables, Tableau has to perform joins behind the scenes to compute the values. The type of join used depends on the relatedness of the fields. As a brief overview: 

• When related fields are used in a viz, dimensions are inner joined and measure values are broken down by the dimensions.
  • It's a little more complicated than that – additional joins might be needed behind the scenes to ensure that no measure values are dropped. But in a dimension-only viz, related dimensions are inner joined and that's the main concept here.
  • This is the same behaviour as single-base table models.
• When unrelated fields are used in a viz, dimensions are cross joined. Measure values are table scoped (that is, aggregated locally to a single value for their entire table) and repeated.
  • It's also possible for fields to be not yet related or ambiguously related, which means that for the combination of active fields, there is more than one way for the relationships between their tables to be resolved. If Tableau encounters uncertainty, it treats the fields as unrelated.
• When fields are stitched based on a shared field, dimensions are outer joined. Measure values are aggregated at the level of whatever dimension they can be broken down by and might be repeated.
  • Stitching dimensions are similar to linking fields in data blending. Results are calculated for each pair of related fields. Then the unrelated values are stitched together across the shared values of the dimension shared between them.

All of these concepts and definitions are discussed in more detail later in this topic.

Field-level relatedness indicators

There are several visual clues that can help you understand the degree of relatedness for fields you're using in an analysis.

Relatedness indicators on a worksheet

• Unrelated icon: Tableau uses an unrelated icon to indicate not everything in the view is related. If you see an unrelated icon on a pill in the view or in the Data pane, you can hover over the icon to get more information.
  • The related icon indicates that field is stitching together unrelated fields.
• Light-grey field names: Field names are displayed in light grey text in the Data pane when they're not related to any fields in use on shelves. You can still use these fields for analysis in that viz, but unrelated fields are evaluated differently in analysis from fields that are related. On hover, these fields also display an unrelated icon.

Note: In previous versions of Tableau, light-grey field names indicated that the fields were hidden and Show hidden fields was selected. Hidden fields, when shown, are now indicated with a clickable eye icon .

Relatedness warning dialog

When unrelated fields are used together in a viz, Tableau shows a warning dialog to let you know that the fields aren't related. This warning appears each time you add an unrelated field to prevent accidental cross joins that might impact performance.

• If you want to use unrelated fields without stitching, click Add to continue adding the field to the viz.
• If you want to stitch unrelated fields, a best practice is to bring out the stitching field before an otherwise unrelated field. The dialog won't appear if the stitching field is already in use. See How joins are used for each level of relatedness for more information about how stitching prevents cross joins.

When multiple fields are being added or are already present in the view, the Details area appears in the dialog. Expand it to see more information about the relatedness of all the fields in use and identify where the unrelatedness issue is coming from.

To stop the warning message from appearing at all, select the option Don't show this again. You can always re-enable these warning messages again by turning them back on:

• In Tableau Desktop, open the Help menu > Settings and Performance > Reset Ignored Messages.
• In a browser, clear your cached data. For example in Chrome, open the three dots menu > Delete Browsing Data... > Choose "Cached images and files" > Delete data.

Table-level relatedness in the data model

In a data model with multiple base tables, each base table defines a set of tables that are related and form a conceptual tree. These trees must be connected by at least one shared table to ensure the overall data source is a single entity.

What might previously have been two data sources that could be blended using linking fields can now be a single data source with two trees, connected by the shared tables that contain those common fields.

Tip: How tables are related in the data model impacts how their fields can be related in the analysis. It can be useful to refer back to the Data Source tab during analysis to see how a table fits into the overall data model.

Let's walk through which tables are related, unrelated or shared using this example data source. There are two trees, one established by base Table A and one by base Table B.

Unrelated tables

Base tables are fundamentally unrelated. Similarly, any tables that exist solely in a single tree are unrelated to tables in other trees.

Table A and Table X are unrelated	Table B and Table X are unrelated

Related tables

Tables in the same tree are considered related.

Table A and Table S are related	Table B and Table S are related (through Table A)

Shared tables

Shared tables have multiple incoming relationships. These tables belong to multiple trees and are shared across them.

Table S and Table T are shared.

Field-level relatedness in the analysis

Relatedness between fields is determined on a sheet-by-sheet basis based on the structure of the data model, what fields are actively in use (that is, what fields are in the viz as pills on shelves) and if those fields are dimensions or measures. How field relatedness impact the results of a viz is covered in the next section.

Let's walk through some scenarios using the same example data source. Each field's name indicates which table it is from, such as FieldB from Table B. Fields can be dimensions or measures unless otherwise noted.

Related fields

At a high level, fields are related when Tableau can clearly determine how to evaluate them together based on a relationship path within a single tree.

For example, FieldB (from Table B) and FieldS (from Table S) are related.

Unrelated fields

At a high level, fields are unrelated in any case when they're not related. This could be because the fields are from unrelated tables, such as using fields from two base tables. In this case, fields from different base tables are fundamentally unrelated.

For example, FieldA and FieldX are unrelated.

Or fields can be treated as unrelated for a point in time – such as in ambiguous or not-yet-related cases. For the most part, you can rely on relatedness indicators to alert you when fields are unrelated in the context of a viz.

Stitching dimension

Stitching is how Tableau evaluates fields from unrelated tables in a multi-fact data model during analysis. In a viz, using a dimension from a shared table stitches together otherwise unrelated fields and allows them to be evaluated simultaneously in the same viz. Think of this as juxtaposing results from two trees together based on a dimension they share.

For example, if a viz is built with FieldA and FieldX, these two fields are unrelated. Adding DimensionS introduces a stitching field.

• FieldA and DimensionS are evaluated together.
• FieldX and DimensionS are evaluated together.
• Those intermediate results are brought together based on the values of DimensionS.
• FieldA and FieldX are now stitched.

Tip: A best practice is to use a stitching field in the viz before bringing out an unrelated field. For example, drag out DimensionS first, or FieldA then DimensionS then FieldX, instead of FieldA then FieldX then DimensionS. Adding the stitching field first ensures that Tableau is always aware of how to evaluate the relationships and avoids potential performance issues from evaluating unrelated dimensions together with cross joins.

Stitching requires a dimension from a shared table to be active in the viz. Fields placed on the Filters shelf or on the Tooltip property of the Marks card aren't considered active for the purposes of stitching.

Not-yet-related fields

Fields can also have multiple ways they could be related but aren't yet. This happens when there's more than one possible relationship between two shared tables (or downstream shared tables).

Consider FieldS and FieldT. Their tables are related to each other both through the tree defined by base Table A and through the tree defined by base Table X.

In a viz with just FieldS and FieldT, there's no information about which tree should be used to relate them. Without additional information, Tableau can't evaluate whether to relate these fields through Base Table A's tree or Base Table B's tree.

FieldS and FieldT are treated as unrelated although there are multiple potential relationships.

These could-be-but-aren't-yet-related fields are evaluated as unrelated because Tableau can't clearly determine their relationship path. Unlike truly unrelated fields, which can only be stitched, not-yet-related fields can be resolved and the fields can be directly related.

Ambiguously related fields

Fields can also be ambiguously related. This happens when there is more than one active possible relationship between shared tables (or downstream shared tables). Unlike not-yet-related fields, which can be thought of as hypo-related or under related, ambiguously related fields are hyper-related or over related.

Consider FieldS and FieldT. Their tables are related to each other both through the tree defined by base Table A and through the tree defined by base Table X.

In a viz with FieldA, Field X, FieldS and FieldT, there's too much information to decide which tree should be used to relate them. Without trimming the information, Tableau can't evaluate whether to relate these fields through Base Table A's tree or Base Table B's tree.

FieldS and FieldT are treated as unrelated, although there are multiple active relationships.

These ambiguously related fields are evaluated as unrelated because Tableau can't clearly determine their relationship path. Unlike truly unrelated fields which can only be stitched, ambiguously related fields can be resolved and the fields can be directly related.

Measure from a shared table

When a dimension is used from a shared table, it stitches together fields from its unrelated upstream tables. Measures can't stitch, however, and the value of a measure depends on its related dimensions.

In a viz with DimensionA and DimensionX, these two dimensions are unrelated. If MeasureS is brought out from Table S, it is unrelated to the combination of DimensionA and DimensionX together. Although it could be related to either one independently, it can't be simultaneously related to both of them in the same viz.

A shared measure can be considered a type of ambiguity or over relatedness and is resolved the same way.

Resolve unclear relationships between fields

Whenever there is uncertainty about how to relate fields, Tableau won't make an arbitrary decision and instead treats them as unrelated. It's often better to relate these fields by clarifying the uncertainty around which tree to use.

Resolving not-yet-related fields is done by adding a field to establish which tree to use. Resolving ambiguously related fields is done by removing fields to establish which tree to use.

Example:

Resolving not yet related: add a field

• In a viz of FieldS and FieldT, adding a field from Table A, B or C to the viz makes Base Table A's tree active and resolves the desired path between FieldS and FieldT.
• Alternatively, using a field from Table X resolves the desired path between FieldS and FieldT to Base Table X's tree.

Resolving ambiguously related: remove a field or fields

• In a viz of FieldA, FieldX, FieldS and FieldT, removing FieldX makes only Base Table A's tree active and resolves the desired path between FieldS and FieldT.
• Alternatively, removing FieldA resolves the desired path between FieldS and FieldT through Base Table X's tree.

Resolving a shared measure: remove a field or fields

• In a viz of DimensionA, DimensionX and MeasureS, removing DimensionX makes only Base Table A's tree active and resolves the desired path between DimensionA and MeasureS.
• Alternatively, removing DimensionA resolves the desired path between DimensionX and MeasureS through Base Table X's tree.

Not yet related	Ambiguously related	Relatedness resolved to a single tree
		Related through base Table A	Related through base Table X

Resolving uncertainty is similar to using a FIXED Level of Detail (LOD) expression. In a FIXED LOD expression, you tell Tableau what level of detail to aggregate to by defining the dimension declaration. Uncertainty is resolved by changing the structure of the viz to make only one tree active, thus telling Tableau what relationship paths it can consider to perform the analysis.

Stitching vs resolving uncertainty

Both stitching and resolving uncertainty are ways of dealing with unrelatedness, but they have different outcomes:

Stitching	Resolving uncertainty
Unrelated FieldA and FieldX stitched by DimensionS	FieldS and FieldT evaluated through the tree defined by base Table A
Juxtaposes unrelated fields based on shared attributes	Narrows down which relationship path to use when there are multiple options (ambiguity or a shared measure), or establishes a relationship path when there wasn't one (not yet related).
Uses multiple base table logic to calculate results	Uses single base table logic to calculate results
Analysis involves unrelated tables	Analysis involves shared tables

How joins are used for each level of relatedness

After the field-level relatedness has been determined, Tableau has to evaluate the results to create the actual visualisation. The queries used to calculate the values shown in a viz rely on joins. Whether fields are related, unrelated or stitched has a different impact on what joins are performed. Remember, ambiguously related and not-yet-related fields are treated as unrelated in this context.

To explain relatedness and joins, this section covers tables and their fields, plus the values in those fields. Consider the following data model with two base tables, Classes and Clubs, and a shared table, Students.

Classes	Clubs	Students
Fields: Class, a dimension with values of Nesting Basics, Advanced Songs, Flying for Fledglings and Alarm Calls 101 Length, a measure Student, a dimension used to relate to the Student table	Fields: Club, a dimension with values of Photography, Travel, Juggling, Art and First Aid Dues, a measure Student, a dimension used to relate to the Student table	Fields: Bus Rider, a dimension with values of yes or no Student, a dimension with values of Finch, Cardinal, Sparrow, Robin and Jay. Used to relate to the other two tables Age, a measure

This very simple model illustrates how the high-level join logic is calculated for multi-fact relationship data models. For more information about the basics of joins used in single base table data models built on relationships, see How Analysis Works for Multi-table Data Sources that Use Relationships.

Related dimensions use inner joins

Related dimensions are inner joined. Inner joins drop any dimension values that aren't shared across both tables.

• Tableau uses additional logic to ensure measure values aren't lost. This section uses only dimensions to demonstrate the basics of how Tableau applies inner joins to related dimensions.

The following example shows how related dimensions only return rows that are present in the data. No students are in the Alarm Calls 101 class, so it's not present in the results. Cardinal and Jay aren't in any classes, so they're not present in the results.

Unrelated dimensions use cross joins

Unrelated dimensions – on their own, without a stitching dimension – are cross joined.

In a cross join, every value from one dimension is combined with every value from the other dimension, even if a resulting combination doesn't actually exist in the data. In this example, the cross join adds a row for each possible combination of Class and Club.

It's important to recognise when a cross join is occurring in your analysis. Although there’s a row for Advanced Songs + First Aid in the results table for the cross join, no students are actually in this combination of activities (we'll see proof of this in the stitching example in the next section).

Why is it important to recognise that not all cross join results are based on the data? Imagine you were trying to build a schedule for classes and clubs so there were no conflicts for any students. There aren't any students in Advanced Songs and First Aid, so you could ignore this result and schedule that class and club simultaneously. The cross join doesn't represent combinations of values that actually exist in the data.

Additionally, cross joins when there is high cardinality (a large number of unique values) can impact performance. Imagine cross joining every phone number with every email address in your contacts. That would be a huge explosion of combinations and a potentially costly operation.

Stitched dimensions use outer joins

Unrelated dimensions – in the presence of a stitching dimension – are outer joined.

In this example, both the Classes table and Clubs table are related to the shared Students table but not to each other, so the fields Class and Club are unrelated. Adding the Student dimension lets Tableau know which values from Class and which values from Club should be juxtaposed in the analysis. We call this outer join behaviour stitching.

Stitching is similar to data blending in that there are intermediate results that are brought back together for the overall results. Unlike blending, however, stitching is an outer join, not a left join, and doesn't drop values from either side. There's no concept of primary or secondary data sources when it's all one data source, so both of the unrelated fields are given equal precedence.

Intermediate results are outer joined

What goes into the outer join for stitched fields? An immediate inner join is computed for each of the unrelated fields and the stitching field in turn. Then those intermediate results are outer joined based on the values of the stitching dimension.

Example

An inner join for Student and Class...

...and an inner join for Student and Club...

...are then outer joined on Student.

Additional joins to retain measures

In addition to the join logic for dimensions, measures can introduce additional joins. When relationships were first introduced in Tableau, one of the core principles was that measure values aren't lost. This is also maintained in multi-fact relationship data models.

The essential details are:

• Measure values are broken down only by related dimensions.
• Measure values repeat for unrelated dimensions.
• Dimension values that would be dropped in dimension-only vizzes may be returned if there are relevant measure values associated with them.

Note: Remember that measures are aggregations – they’re computed at the level of detail (the granularity) set by the combination of dimensions in the viz. This is referred to as a measure being broken down by a dimension. When a measure is used without any dimensions, it is said to be table scoped. This means the measure's value is the fully aggregated value. As soon as we use a dimension in the viz, the measure is broken down more granularly based on the dimension values. The value of a measure in an analysis therefore depends on the context of the dimensions.

Related measures

Consider the subset of dimension values that are returned for an inner join on the related dimensions Student and Class. There are three student values, Finch, Robin and Sparrow; and three class values, Advanced Songs, Nesting Basics and Flying for Fledglings.

If we add the Length measure from the Class table, we see that all four classes are shown and there's a null for Student. Every class Length is displayed, at the level of Class.

If we instead add the Age measure from the Student table, we see that all five students are shown and there are two nulls for Class. The results preserve every student, even if they're not in a class. Every student Age is displayed, at the level of Student.

Unrelated measures

Measure values are repeated for unrelated dimension values.

If we look at the Length measure from the Classes table and the unrelated Club dimension, the measure is table scoped and repeated across all the dimension values of Club.

In the presence of a stitching dimension, measures can be both broken down and repeated.

Here, the measure Age is from the Students table and is broken down to the level of student. Each time a student is repeated based on the dimensions for Class and Club, the Age value is repeated.

Troubleshooting

Considerations when working with multi-fact relationship data models

Per-table extract filters

All extract filters for a multi-fact relationship data model extract are per table (not pervasive). Because of this, filtering results may be different between live and extract connections.

Row-level calculations

Row-level calculations can only refer to fields that share the same upstream base table. That is, row-level calculations can't be performed across trees.

Combined Fields

All fields in a combined field must share an upstream table. That is, you can't create a combined field using fields that are in different trees.

Sets

Sets can only be created with a definition that involves fields that share the same upstream base table. However, in a viz, the option to Add to Set may be available from a mark when that mark is defined by fields unrelated to the fields used to define the set. If you choose Add to Set, Tableau will add only the related fields to the set definition. This is different from the behaviour for Add to Set in single base table data sources, when Add to Set adds everything that defines the mark.

Validate INCLUDE level of detail expressions

INCLUDE LOD expressions can't be evaluated across unrelated fields. Because relatedness between fields is evaluated on a sheet-by-sheet basis, it's possible to have a valid LOD expression in the Data pane or calculation editor that becomes invalid in the context of a specific viz (in the presence of an unrelated dimension). When this happens, the LOD pill will turn red. You can update the LOD expression to remove unrelated field conflicts, change the structure of the viz or remove the LOD expression from the viz.

Updating Published Data Sources

As a best practice, create a copy of an existing published data source if you plan to modify it to become a multi-fact relationship data model when not all of its connected workbooks need the new data model. Don't update the existing version of the data source unless all its workbooks need the new tables. Publish the modified data source as a new data source and create new workbooks from it. This will prevent the existing workbooks from being converted to use VDS instead of data server when they don't need the functionality, preventing the potential for a performance hit.

Resolved issues

Known issues in 2024.2

Relatedness indicators with multiple Marks cards

When a viz is built with multiple measures on the Rows shelf or on the Columns shelf, each measure gets its own Marks card. The logic used to determine relatedness indicators (the unrelated icon, the text in tooltips and the relatedness warning dialog) may not give expected results depending on which Marks card is open. The viz itself, however, is correctly computed based on the relatedness of each pair of fields. There is a planned fix for this behaviour.

BatchQueryProcessor

BatchQueryProcessor must be enabled to support multi-fact relationship data models. This is expected behaviour with no currently planned fix.

Tableau Pulse

Pulse may not work with multi-fact relationship data models. You may be prevented from creating a metric definition, or any metrics that are created may be blank. This is not expected behaviour but there is currently no planned fix yet.

Other articles in this section