When and How to Use Multi-fact Relationships in Tableau

Expand your data modeling and analysis with Multi-fact Relationships, available with Tableau 2024.2. Evolving the capabilities of Relationships, Multi-fact Relationships allows you to combine multiple groups of tables to perform multi-fact analysis. This unlocks new types of analysis using a single data source, simplifies the process to build more robust data models in Tableau, and accelerates time to insights.

What is Multi-fact Relationships in Tableau?

Multi-fact Relationships is a data modeling capability in Tableau that enhances the way you can model and analyze data with multiple fact tables (called base tables in Tableau). With Multi-fact Relationships, you can work with sophisticated data models, like multiple star or snowflake schemas together.

You may have heard of Multi-fact Relationships informally referred to as “shared dimensions.” Sometimes data spans multiple base tables in different, unrelated contexts. For example, you might have one base table with Sales data and another base table with Marketing data. Each of these tables can now be linked to shared dimension tables, like Dates or Product, even though the measures in their respective base tables don’t share a direct row-level relationship with each other.

This data model enables you to explore correlations and answer more sophisticated analytical questions, such as how Marketing spend affects Sales, or how Spend actuals are tracking against Budget forecasts. You may now explore more comprehensive data scenarios in a single data source, making it easier for you to perform multi-fact analysis without the need for extensive data engineering.

In the data model presented here for a hypothetical retail store, it tracks warehouse inventory, marketing campaigns, sales transactions, and support cases. The base tables are not directly related to each other and shown with a thin box outline. Whereas the shared tables, shown in a thicker box outline, relate across base tables and contain shared dimensions and shared measures. In addition, shared tables can relate to downstream shared tables in the case of Products –  Subcategory – Category tables.

When to use Multi-fact Relationships in your data model

Before Multi-fact Relationships, a Tableau data model built with Relationships could support branching out downstream from a base table, but could not relate a table back to more than one upstream table. If you don't need multiple base tables, you can use standard relationships and build a data model with tables that only branch out.

Let’s look at some examples where the power and flexibility of Multi-fact Relationships shine.

Analyzing multiple sets of facts with conformed dimensions

If you have dimensions that share the same meaning across tables, known as conformed dimensions, it's easy to model in Tableau with Multi-fact Relationships.

Shown in the diagram above, notice how all four fact tables become base tables, and the three downstream conformed dimension tables are related back to their relevant base tables.

Unlike in other BI solutions, not all fact tables are forced to conform to all common dimension tables in Tableau—plus, you don’t need to worry about directional filters. This means you can aggregate measures from separate base tables, which do not have direct relationship to each other, to dimensions in shared tables by stitching together the unrelated measures with their shared dimensions.

Returning back to the hypothetical retail store data model, multi-fact analysis aggregates the measures from Inventory, Marketing, and Sales individually, broken down and visually presented by their collective shared dimensions, Dates and Products. 

Note that Tableau is outer joining intermediate results (from the shared dimensions and each base table) on the shared dimension values (Dates and Products). This is complementary to inner-joining dimensions for tables that are directly related. This outer join semantic stitches together correctly aggregated measures from each base table based on their shared dimensions to create the final viz.

While conformed dimensions may enable multi-fact analysis, it lacks the additional flexibility of using measures from shared tables. In Tableau, dimensions from base tables can also be used with measures from shared tables—so you can align a shared measure to a specific data silo to answer questions that conformed dimensions would not.

However, when using measures from tables that are shared between multiple base tables, Tableau will require clarification if there are dimensions stemming from two or more base tables. By default, the measures will be fully aggregated, but you can specify how to break them down by using a FIXED LOD expression, for example:

• {FIXED [Product (Marketing)]: COUNT([Products])} 
• {FIXED [Product (Sales)]: COUNT([Products])}

Relating tables through branching out and branching in

With support for multiple base tables, Tableau data models are more flexible and allow you to create what are effectively circular relationships. Previously, you could only branch out from the base table but not branch back into other tables. Now you can model that circularity by using multiple base tables and multiple shared tables. 

Tableau also preserves the native granularity of each base table. For example, Budget may be recorded monthly, while Spend is captured daily, and the Calendar table contains separate columns for months (to relate to Budget) and days (to relate to Spend). 

Gaining flexibility within a single data model

Before Multi-fact Relationships, using multiple field pairs to relate tables required all all field pairs to be simultaneously true (you can think of this as “AND” logic). Now, it’s recommended to have each shared dimension as its own separate table, which allows you to consider each relationship in turn, and use any combination of shared dimensions that you like (think of this as more contextual “OR” logic). Your data source becomes more powerful across a wider range of scenarios, without constraining your options for analysis.

This enables more flexibility within your data model. The Sales team can look at regional sales related to the products they are selling, while the Product team can look at product data without bringing in geographical customer data that isn’t pertinent to them, and the Finance team can explore the combination of dates and product without customer demographic information.

Applying filters on shared dimensions affects only the relevant base tables that share the dimension. A filter applied to an unshared dimension is restricted to only that base table and its related tables.

Combining distinct data sources that share common dimensions

Rather than building several different data sources and visually combining them in a dashboard, you can create a single data source with multiple base tables. This maintains the unrelated tables as distinct from each other, but without the workarounds sometimes required when blending data.

Using Multi-fact Relationships, you can also analyze data that goes through different stages all in one data source. This also means that you can now create calculations that will span across them. The above diagram shows how you can combine data about students from class registrations, various school activities, and data related to graduation—data that may otherwise be siloed.

As with all relationships, Tableau maintains performance and usability by keeping the logical relationships between tables without physically merging them. 

Looking for correlations between disconnected tables

Until now, we’ve looked at examples that all involve shared dimensions. But what about analyzing data that doesn’t share dimensions at all? Multi-fact Relationships introduce new possibilities to analyze islands of data that don’t need to have direct relationships with each other.

For example, a retailer can explore different customer segments for correlations between advertising mediums (like online and print) and preferred methods of payment, using the hypothetical retail store data model. 

Tableau cross-joins unrelated dimensions together so you can explore potential correlations without direct data relationships or using shared dimensions. The underlying factor for the correlation may not even be captured in the data—it could be some other aspect of the customer demographics—but this opens a whole new world of analysis and the types of questions you can explore!

Learn more about Multi-fact Relationships

Excited for the realm of possibilities? Dive deeper into Multi-fact Relationships with these resources:

• About Multi-fact Relationship Data Models
• When to Use a Multi-fact Relationship Model

Want to try out Multi-fact Relationships? Play around with the hypothetical retail store data model and explore analytics scenarios: Download the sample Tableau workbook.