Understanding Data Blending in Tableau

One of the most powerful features of Tableau is its ability to blend data. Data blending is commonly utilized for defining a relationship between tables from different data sources, but it has many more capabilities.

What does Data Blending do?

It helps in defining the relationship between tables where joins cannot be implemented. Some of the areas where blending can help are:

When cross database join between two tables is not supported by Tableau data blending can be used to create a relationship between the tables
When two tables have many-to-many relationship blending can be used to establish a relationship between the two tables without duplicating resultant aggregate
When two tables are at different levels of detail are joined duplicate records will be created. This can be avoided using data blending which does not create duplicate records

And many more…

Let’s do some hands-on exercise to understand its significance.

Scenario (Using sample superstore dataset):

We need to compare the sales achieved in each segment and their targeted sales.

Solution:

Create an excel workbook (Segment target sales) as follows. This data source contains the target sales for each segment.

Understanding Data Blending in Tableau — Image 1

Using sample superstore build the following view.

To add the target sales to the view, add the Segment target sales excel file and join on Segment field.

Adding the target sales to the existing view we get the following output:

The target sales value is much higher than the values in target sales excel workbook.

We get this output because the data in two sources are at different level of detail.

Sample superstore contains sales aggregated for every transaction and every product whereas target sales source has sales value at segment level only.

In sample superstore dataset:

Number of records under Consumer segment – 5191
Number of records under Corporate segment – 3020
Number of records under Home Office segment – 1783

Therefore, the target sales for each segment are aggregated in the following way:

Consumer: 5191 * 1000000=5191000000
Corporate: 3020 * 2000000=6040000000
Home Office: 1783*3000000=5349000000

These values are reflected in the view as well as shown in Image 4.

To avoid the duplication of measure values we can utilize data blending.

Remove the target sales table in the data source pane.

Now add the same source through the data menu as shown below:

Now we have two data sources.

Use segment and sales from sample superstore source and targeted sales from Target sales source. You can switch between the sources by clicking on the list of sources as shown in image 6.

When switching data sources, you can notice a chain link on the segment dimension. This indicates that blending is performed between two tables based on the segment field.

From image 7 we can see the target sales as mentioned in target sales excel source.

Blending performs a left join operation between data sources.

It is also important to note that the data source from which a dimension/measure is used first in a sheet becomes the primary data source and other data sources become secondary.

In subsequent blogs, we will cover other features of Tableau.