Tableau Desktop and Web Authoring Help

Analysis often involves bringing together tables of data that don't have a direct relationship to each other yet both relate to the same, common information such as date or location. This type of analysis is sometimes referred to as multi-fact analysis with shared dimensions.

To perform this kind of analysis in Tableau, you need to create a data source that uses multiple base tables connected by shared tables.

• Base tables are the left-most tables in the data model on the Data Source tab. For guidance on how to determine which tables to use as base tables, see When to Use a Multi-fact Relationship Model.
• Shared tables are downstream tables with multiple incoming relationships. These tables contain fields that can be used to stitch together unrelated fields during analysis in a viz. Date and Location are examples of commonly shared tables.

Build the model

Building a multi-fact relationship data model is essentially the same as creating any other data source that uses relationships, but with two additional pieces: additional base tables and multiple incoming relationships to shared tables.

1. Connect to your data. You can use multiple data connections if your tables aren't all in the same database.
2. Drag a table onto the canvas to create the first base table.
3. Drag another table from the left pane to the New Base Table drop area.

   

   Tableau shows a warning that you're building a data model with multiple base tables. We recommend that you only set up a multiple base table model if your data needs it. Otherwise, use a single base table model to avoid the complexities that come with a multiple base table model.

   

4. Drag another field to the canvas and relate it to one of the base tables. Configure each relationship if necessary.

   

5. Hover over the not-yet-related base table to display a plus sign (affectionately known as a "meatball").

   

6. Drag the plus sign icon to the shared table to create a new incoming relationship (also known as a "noodle").

   

7. Optional: Continue adding downstream tables and base tables. Click and drag the plus sign icon to build shared tables as needed.

Note: Rather than starting with the base tables, you can also build a single base table model first and add additional base tables later.

Explore the model

A data model with multiple base tables is built with relationships, but not all tables are related to each other to the same degree. Each base table defines a tree, which contains every table related to that base table, either directly related or downstream from a related table. Shared tables exist in multiple trees. For more information about degrees of relatedness, see About Multi-fact Relationship Data Models.

When you view a model with multiple base tables, there are various options for exploring and managing the data model. These options are especially useful when a data model is complex.

An example of a complex data model with four base tables, multiple shared tables and downstream tables that are both shared and unshared between the base tables.

2024.2 introduced some new layout details for the data model. In a multi-fact relationship data model, relationships bundle together to help track how many incoming relationships a table has, and shared tables (and downstream shared tables) have a bolder outline than tables that aren't shared.

Terminology

Tables in a multiple base table data model have specific roles. Base tables and shared tables are the only tables that must exist in a multi-fact relationship data model. (Without more than one base table, it's not a multi-fact relationship data model. Without a shared table connecting the base tables, it's not a valid data model.)

Because of the often complex nature of these models, it's useful to have a shared terminology for discussing other types of tables by how they fit into the data model.

• Base tables are on the far left and have no incoming relationships.
  •  In the example, Inventory, Marketing, Sales and Support are base tables.
• Sub-base tables are between a base table and a shared table.
  • In the example, Parts is a sub-base table.
• Shared tables have more than one incoming relationship.
  • In the example, Products, Dates, Customer and Supplier are shared tables.
• Downstream shared tables have exactly one incoming relationship and have a shared table somewhere upstream of them.
  • In the example, Subcategory and Category are downstream shared tables.
• Downstream unshared tables have exactly one incoming relationship and have no shared tables upstream of them.
  • In the example, there are no downstream unshared tables.

Identify a relationship tree

Hover over a table to highlight the tables it's related to. Tableau emphasises the tree for that table and de-emphasises unrelated tables.

The Marketing base table tree consists of two shared tables, Dates and Products, and downstream shared tables Subcategory and Category.

View relationship details

Hover over a relationship or bundle of relationships to see the details in a tooltip.

The incoming relationships to the Products table include three individual relationships to the Inventory, Marketing and Sales base tables.

Select a relationship

When a relationship is selected, it is highlighted in blue in the data model canvas and its details appear in the Table Details pane below the data model canvas. The Table Details pane is where you can inspect or modify the relationship clause.

There are multiple ways to select a relationship:

• Click a relationship line (noodle) in the canvas. Every relationship has a clickable zone that selects just that noodle.
• Right-click or control-click a table in the canvas to open its menu. Select the Select Relationship option and choose which table's relationship you want.
• Click a bundle of relationships in the canvas to bring up a persistent tooltip (hovering over a bundle brings up the tooltip; you have to click to make it persist). Then select a row in the tooltip details to highlight that relationship in the model.
• Open the menu in the toolbar of the Table Details pane and select the desired relationship. You can also use this menu to select a specific table to see its preview in the pane.

  

Swap with base table

Intermediate tables in a relationship between a base table and shared tables give you the option to swap the downstream table with the base table. This is purely a visual change to aid with conceptual understanding and doesn't change the structure of the data model.

Right-click or control-click a downstream table and select Swap with base table (table name). The swap option is also only present on the downstream table and not the base table.

The swap option is not available for tables that would alter the data model if swapped, such as shared tables or downstream shared tables. Only downstream unshared tables or sub-base tables can be swapped with base tables.

Example

In this example, the Parlour Info and the Ice Cream Sales tables can be swapped without changing the data model's fundamental structure. No other tables can be swapped.

• Ice Cream Sales is related to both Parlour Info and the shared Date table.
• Parlour Info is related to both Ice Cream Sales and the shared Location table.
• Weather is related to both the shared tables of Date and Location.

These two models are conceptually equivalent:

Collapse a base table

You can also collapse a relationship path, or tree, to just its base table to temporarily simplify the view of the data model.

Click the Collapse or Expand buttons on a base table to collapse or expand its entire tree. Alternatively, right-click or ctrl-click on a downstream table and select Collapse this path or Collapse other paths. This option is not available on shared tables or tables downstream of shared tables.

Collapsing a tree to its base table is purely visual and won't trigger the Unrelated Tables alert. A collapsed path is indicated by a base table with a stacked table and an Expand button. Collapsing affects all tables and relationships that are relevant only to that tree, so tables that are shared with an uncollapsed path are not hidden.

Use the Expand button to re-open the base table and everything in the tree.

Troubleshooting

Create a single data source

All tables must be related to the entire data source in some way. If there are any tables that aren't related to the overall data model, an alert appears. The alert remains until no tables or trees are fully separate from the rest of the data model. When the alert is active, the data source can't be published and you can't use the data source in an analysis.

You can open the alert and set Visually Separate Unrelated Tables to identify which tables triggered the alert. This option is useful when you have a complex model and need to identify which table or tables aren't yet related to the rest of the data model.

Example

In the steps under Build a model, an alert displays in Step 4 before a relationship is added to connect the second base table.

Setting Visually Separate Unrelated Tables to On moves the table Clubs underneath the Unrelated Tables line. Relating Clubs to Students resolves the alert.

Resolve a cycle

Even if some tables aren't directly related to each other, the entire data model must be a cohesive whole. In this example, each base table defines a tree but there is no shared table connecting them. This isn't a valid model for analysis.

The two groups of related tables need to be combined via a shared table...

...or the data model needs to be created as two separate data sources.

Data model restrictions

Some relationship paths between tables are not supported in a multi-base table model. If you’re unable to drop the meatball when you attempt to create a second incoming relationship on a table, make sure the structure you're trying to create is supported in Tableau. Examples of unsupported models include:

Cycles

Cycles – where there’s more than one relationship path from an upstream table to the same downstream table – are not supported. This unsupported structure is sometimes called a bowtie. To model this kind of relationship between tables in Tableau, use multiple base tables instead of a bowtie by converting the downstream table to another base table.

The data model must be a directed acyclical graph. This means every incoming relationship to a table must be traceable upstream to a different base table.

Not supported. Table X has two incoming relationships that are both from tables downstream from Base Table A	Supported. Tables S and T both have multiple incoming relationships, but each one is from a different base table.	Supported. Although it may visually look like there's a cycle, the incoming relationships for both Table S and Table T can be traced back to different base tables.

Nested shared tables

Nested shared tables are not supported. Any tables downstream from a shared table (a table with multiple incoming relationships) can only have one incoming relationship.

Not supported. Table T is downstream from a shared table and can't receive an additional incoming relationship.