What is Data Blending in Tableau?
Data blending is joining related data from different sources in a single view.
Data blending in Tableau is a powerful feature available in Tableau which brings the additional information available in the secondary data source and displays it with the primary data source. Options available in Tableau to combine data, like Relationships, Joints and Blends.
Blend: Unlike joins or relationships, the blend does not combine the data. Instead, it aggregates the values and displays together in the same view. So, the data blending in Tableau can aggregate the data from multiple sources, of all levels and display them in one view.
The blend is highly recommended for operations done on published data or on sheet by sheet linking that is varying on every sheet. It correlates the different data sources in a shorter period, unlike the traditional data processing that involves more time.
Simply put, a left join is created between the primary and secondary data sources. This matches with all the rows from the primary data source that matches with the secondary data source. This is how a blend in Tableau is created. An * (asterisk) will appear indicating the multiple dimensions in the single level. A secondary data source is re-aliased in the primary data source.
Data Blending v/s available other data combine concepts:
| Data Blending in Tableau | Joins |
|---|---|
| Data Blending Aggregates the data and then combines it. | Joins combine the data and then aggregates it. |
| Data Blending can combine data from different sources. | Joins can combine data from the same sources only. |
| Data Blending can execute Left-Join only. | Joins can execute all four varieties of Joins. |
| Data Blending offers data availability at different levels of granularity. | Data has to be maintained at one single level granularity throughout the process while using Joins. |
| Data Blending in the tableau can execute queries to the separate datasets, aggregate data, and then perform data blending. | Joins can only perform the join operations at row level. |
How to Data Blend in Tableau?
- Tableau workbook has to connect to at least two data sources to create data blend.
- To set up the relationships, on the menu bar, Data – Edit Blend Relationships.
- Edit Blend Relationships from the Data option on the menu bar.
- Tableau will guess if there are related fields, this will be those in the Automatic relationship setting.
- Custom option users can define a connection between two tables.
- When data blending with dates, the relationship should be set up at the correct “level” in the date hierarchy.
- Just defining the relationships does not activate the relationship. The “joins” are activated within the worksheet by clicking the chain link symbols in the secondary source.
- Click the chain link to activate/deactivate the relationship in the worksheet.
- Within the same workbook it is possible to have the same data sources connected by different join fields in different worksheets. An orange chain link means that specific join is active within that worksheet. The broken chain shows that join is not active within that worksheet.
- To switch the connection on or off, click the chain symbol.
- It is not possible to blend data on a measure, but it is possible to convert a measure to a dimension.
- To convert to a dimension, drag the field from the Measure section to the Dimension section, or right click on the field and ‘Convert to Dimension’.
Key points about data blending:
An important point about data blending, the relationships should always blend at the least-granular level.
In join we join data at the most granular level. With a blend it’s the opposite, blend at the least granular level. If possible, avoid blending a large data set with a large data set. Data blend is always at the worksheet level. The blend occurs only within that specific worksheet, it is not shared across data sources and dashboards. The blend applies to that worksheet only.
Primary and Secondary sources in data blending:
When blending in Tableau, there is always a primary source and a secondary data source. It is important to understand which is the primary source, it can impact your view.
The primary source has a blue check, secondary sources have an orange check mark. To reiterate, which data source is primary and which is secondary does matter. As it is a type of left join, all fields will be included from the primary and related from the secondary.
Fields from a secondary data source are indicated by an orange check mark. In a worksheet, the primary source is defined by the field that is first placed on to a worksheet. The data source of the first field becomes the primary data source, and other data sources will be secondary. Even if the worksheet is cleared, if a field was on that worksheet, the primary data source remains set. To change the primary data source on a worksheet, create a new worksheet and start again.
