Deciding Between Joining Tables and Blending Data
Version(s): 8.0, 7.0, 6.1
Last Modified Date: 22 Nov 2013
When you want to determine whether joining data tables or blending them is the right approach, consider where the data is coming from, the number of data connections, and the number of records you have in the data. This article provides guidelines and examples for when to join tables and when to use data blending.
If a workbook uses data from more than one data source, you can either blend data or invest in a federated database system. For more information, refer to the Wikipedia page about Federated Database Systems.
If a workbook uses two data connections from the same data source, joining the data tables can improve performance and filtering control. If you are adding data to an existing data source or a table in the same data connection, using a join is better.
However, within these guidelines, sometimes joining data tables may not work as well as blending data. Common situations that may perform better with data blending include the following:
- The data source contains too many records for a join to be practical.
- You want to display a summary and details at the same time.
Note: If one of the data sources you want to use for data blending is a cube, it must be the primary data source to be valid.
The remaining sections of this article expand on these general guidelines, providing links to examples and additional information.
Adding data to an existing data source
If you want to add details such as returns of ordered merchandise, you can include this information without modifying the original data. For example, you can join to the original data source a table that identifies the orders that were returned.
- For more information, see Joining Tables in the Tableau Desktop Help.
- For an example, see the article Joining Excel Worksheets.
Two data connections to the same data source
If you have more than one table from the same data source, you may find it faster to create the join in Tableau than in the source. If the join does not provide the expected results, see the other options described in this article.
Using a data source with too many records for a query
If you have multiple data connections that are large and take a long time to query, using a join can increase query time dramatically. A better approach is to aggregate the tables, and then blend the data on the aggregate.
- For example, you can aggregate data on the year rather than the date, or on the product type instead of the product name.
- For an example, see the article Data Blending with Summarized Data.
Multiple kinds of data types for one worksheet
Tableau assumes that fields with same name and data type match, and it automatically creates a data relationship between them. Use the Relationships dialog box to connect the two data sources.
Note: You can manually create custom relationships between fields that have different names. For more information, see Defining Relationships in the Tableau Desktop Help.
- For an example, see the article Combining Multiple Data Sources into One View.
Showing summary and details together
If you want to see both the summary of a calculation and the breakdown on same view, select Data > Data Connection > Duplicate to blend the data to communicate between two data connections.
- For more information, see the article Showing Summary and Detail Together.