About Multi-fact Relationship Data Models
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 to 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 (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, 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 visualization 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 behavior 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 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 ambiguity, 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 dimensions 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.
In Tableau, measures are aggregations—they’re aggregated up to the granularity set by the dimensions in the view. The value of a measure therefore depends on the context of the dimensions. For example, "number of cereal boxes" depends on if we mean the total inventory or the number of boxes per brand.
Dimensions are usually categorical fields, such as country or brand. In Tableau, dimensions set the granularity, or the level of detail, of the view. We typically want to group our data into marks by some combination of categories. What dimensions we use to build the view determines how many marks we have.
When a measure is used without dimensions, it's said to be table scoped. This means its value is the fully aggregated value for the entire table. As soon as we use a dimension such as brand in the viz, the measure is broken down more granularly. The total number of cereal boxes is now per brand.
Aggregation refers to how the data is combined. Tableau's default aggregation is SUM. You can change the aggregation to other options, including: average, median, count distinct, minimum, and so on. Granularity refers to how detailed or broken down the measure is—which is controlled by the dimensions. Unless the granularity of the measure is row level (aka disaggregated), its value must be aggregated.
Example
What's the value of "number of boxes of cereal?"
Well, it depends on the aggregation type and the granularity as set by the dimensions.
- Aggregations:
- Sum (or total)
- Average
- Granularity:
- Table scoped / fully aggregated (the blue bars in the example)
- Broken down by the Brand dimension (the colored bars in the example)
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.
- Light gray field names: Field names are displayed in light gray 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 than fields that are related. On hover, these fields also display an unrelated icon.
Note: In previous versions of Tableau, light gray 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 message appears each time you add an unrelated field to remind you to validate using unrelated fields in your analysis. The warning message doesn't prevent you from proceeding but it can help avoid potential performance issues. For example, using unrelated fields with high cardinality can result in cross joins that impact performance in the workbook.
Click Add to continue adding the field to the viz. If you don't want to see the message again, select the option Don't show this again. You can always make these warning messages appear again by turning them back on: open the Help menu > Settings and Performance > Reset Ignored Messages.
If multiple fields are being added or are already present in the view, 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.
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 what 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 ambiguously 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 fields
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.
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.
Ambiguously related fields
Fields can also be ambiguously related. This happens when there's more than one possible relationship between two shared tables (or tables downstream from a shared table) and can be thought of as not-yet-related in the context of the viz.
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 ambiguity 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 ambiguously related: there are multiple potential relationships.
Ambiguously related fields are evaluated as unrelated field because Tableau can't clearly determine their relationship path. Unlike truly unrelated fields, ambiguously related fields can be resolved and the fields can be directly related.
Resolve ambiguity
Resolving ambiguity is done by adding a field to establish which tree to use.
Example:
- 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 ambiguity between FieldS and FieldT.
- Alternatively, using a field from Table X resolves the ambiguity between FieldS and FieldT to base Table X's tree.
| Ambiguously related | Ambiguity resolved to a single tree | |
| Related through base Table A | Related through base Table X | |
|
|
|
|
Resolving ambiguity 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. Ambiguity is reolved 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 ambiguity
Both stitching and resolving ambiguity are ways of dealing with unrelatedness, but they have different outcomes:
|
Stitching |
Resolving ambiguity |
|
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 |
|
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 visualization. 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 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:
|
Fields:
|
Fields:
|
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.
For this three table data model, it might be tempting to set it up as a single base table model, as Classes-Students-Clubs or Clubs-Students-Classes, or with Students as a base table. As a rule, multi-fact relationship data models are intended for specific kinds of data schemas or analysis scenarios. If your data model has characteristics that are best suited to a multi-fact relationship data model, set it up that way to keep your base tables conceptually unrelated. However, if your data doesn't require this type of structure, a single base table model can be simpler to use.
Models that could be built for these three tables: (1) Classes and Clubs as base tables with Students as a shared table, (2) linearly, starting with either Classes or Clubs, and (3) Students as a single base table with Classes and Clubs as downstream tables.
In this particular instance, there's nothing about these tables, the data, or the model that truly requires multiple base tables. We're using this model as an example to keep it simple so the focus can be on the join logic. Or you could imagine that there's another related table, Rooms, that we're simply ignoring to avoid over complicating the discussion.
As a best practice, however, only use a multi-fact relationship model when your data requires it.
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 recognize 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 recognize that not all cross join results are based in 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.
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 behavior 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 Fledgelings.
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
Row level calculations
Row level calculations can only refer to fields which 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 behavior 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.
Known issues in 2024.2
Extracts
Warning: Data sources with multi-fact relationships should be live connections, not extracts.
Local data source (in a workbook): Attempting to extract a multi-fact relationship data source will give a "No such table" error.
Published data source: Extracting a published multi-fact relationship data source appears to succeed, but field values can be swapped.
There is a planned fix for this behavior.
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 behavior.
EXCLUDE Level of Detail expressions
Only INCLUDE LODs should be validated in the presence of unrelated fields. However, EXCLUDE LODs may also be incorrectly marked as not valid in the same conditions. There is a planned fix for this behavior.
Nested user calculations
Nested user calculations are not available in published data sources with a multi-fact relationship data model. There is a planned fix for this behavior.
BatchQueryProcessor
BatchQueryProcessor must be enabled to support multi-fact relationship data models. This is expected behavior 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 behavior but there is no currently planned fix yet.