Tableau Data Blending
Learn how Tableau combines data from multiple data sources using data blending, primary and secondary sources, linking fields, and practical examples.
1. What is Data Blending in Tableau?
Data Blending is a Tableau feature that allows you to combine information from multiple data sources in a single worksheet. It is useful when data is stored in separate databases, files, or systems and cannot conveniently be combined using a single physical join or relationship.
In data blending, one data source acts as the primary source and another acts as the secondary source. Tableau aggregates the secondary data to the level of detail required by the linking fields and combines the results in the visualization.
Key Characteristics
Data blending works with separate data sources. It uses linking fields to connect related information, identifies primary and secondary data sources, and combines aggregated results in the worksheet. It is particularly useful when the sources have different structures or cannot be joined directly.
2. Understanding Primary and Secondary Data Sources
Tableau uses two roles when blending data. The primary data source provides the main structure of the worksheet, while the secondary data source provides additional information through matching linking fields.
Tableau combines aggregated information from primary and secondary sources.
Primary Data Source
The primary data source is the source from which fields are first used to build the worksheet. It determines the main level of detail displayed in the view.
Secondary Data Source
The secondary data source provides additional fields that are related to the primary source through linking fields. Tableau aggregates the secondary data before displaying it alongside the primary data.
3. Example: Sales and Target Data
Imagine a company stores its sales information in an Excel file and its sales targets in a separate database. You want to compare actual sales with targets in one Tableau worksheet.
Sales Data Source
| Region | Sales |
|---|---|
| East | 50,000 |
| West | 70,000 |
| North | 40,000 |
| South | 60,000 |
Target Data Source
| Region | Target |
|---|---|
| East | 45,000 |
| West | 65,000 |
| North | 45,000 |
| South | 55,000 |
Blending Configuration
Primary Source: Sales Data
Secondary Source: Target Data
Linking Field: Region
Tableau uses Region to relate the sales and target information. You can then compare actual sales and targets in the same view.
Sales and target information are blended using the Region field.
4. Linking Fields in Data Blending
Linking fields are the common fields Tableau uses to connect the primary and secondary data sources. These fields help Tableau identify which records from the secondary source correspond to the primary source.
For example, if both data sources contain a Region field, Tableau can use Region as the linking field to combine regional sales and target information.
Example of Linking Fields
Primary Data Source:
Region | Sales
Secondary Data Source:
Region | Target
Linking Field:
Region
5. How Tableau Data Blending Works
When you use fields from multiple data sources in a worksheet, Tableau uses the primary source to define the view. It then uses the linking fields to retrieve related aggregated values from the secondary source.
Data blending uses linking fields to combine aggregated results in a worksheet.
The secondary source is aggregated to the level of detail established by the linking fields. Tableau then uses the results in the visualization. Therefore, the displayed results depend on the dimensions and measures included in the worksheet.
6. How to Perform Data Blending in Tableau
Follow these steps to blend data from two different sources in Tableau Desktop.
- Open Tableau Desktop.
- Connect to the first data source, such as an Excel file.
- Connect to the second data source, such as a database.
- Open a new worksheet.
- Select the first data source and drag its dimension into the view.
- Use a measure from the primary data source.
- Switch to the secondary data source.
- Drag a related dimension or measure into the worksheet.
- Check whether Tableau identifies a matching linking field.
- If required, configure the relationship using the Data menu.
- Review the blended values in the worksheet.
Practical Example
Select Region from the Sales data source and place it on Rows. Add Sales to the view. Then select the Target data source and add Target to the worksheet. If Region is correctly linked, Tableau can display the sales and target measures together for comparison.
7. Data Blending vs Relationships vs Joins
Tableau provides different ways to combine information. Relationships, physical joins, and data blending have different behaviors and are suitable for different scenarios.
| Feature | Data Blending | Relationships | Physical Joins |
|---|---|---|---|
| Data sources | Multiple data sources | Tables in a logical data model | Tables in a physical data model |
| Data model behavior | Combines aggregated results in a worksheet | Tables remain separate logical tables | Rows are combined into one logical table |
| Connection method | Linking fields | Relationship fields | Join conditions |
| Level of detail | Secondary source is aggregated to linking-field detail | Tableau determines query behavior based on the view | Determined by the resulting joined rows |
| Common use | Combining separately maintained sources | Flexible analysis across related tables | Creating a combined physical table |
8. Common Issues and Best Practices
Common Issues
Data blending can produce unexpected results when linking fields contain different values, incompatible data types, null values, or inconsistent formats. A missing linking field can also prevent Tableau from matching the expected records. Aggregation differences between sources may lead to results that require additional validation.
Best Practices
Choose the correct primary data source based on the worksheet requirements. Verify linking fields in both sources and ensure that their values and data types are compatible. Check null values and inconsistent field formats. Understand how the secondary source is aggregated and validate important totals before publishing dashboards.
Summary
Tableau Data Blending allows you to combine information from multiple data sources in a single worksheet. One source acts as the primary source, while another acts as the secondary source. Tableau uses linking fields to match related information and aggregates secondary data according to the level of detail required by the worksheet. Data blending is useful when data is maintained separately or cannot conveniently be combined using physical joins or relationships. Correct linking fields, aggregation, and validation are important for accurate results.