Data Blending Using Tableau

[laymanlearning]

Data Blending using Tableau

Data Blending and its need

The process of combining fields from multiple tables spanning different data sources is called Data Blending. In Tableau when we need to create a visualization which requires fields from multiple data sources, we need to do Data Blending.

Let's understand different ways Data Blending can be done.

Case 1 – Automatic Data Blending

- For two fields having same name and at least one member/value is commonly present in both fields, tableau automatically blends/creates link from secondary data source field to primary data source field.

E.g. 'State' field from Coffee chain datastore (MS Access) and 'State' field from Superstore datastore (MS Excel) have same name and both have at least one common data value, i.e. 'California'

Case 2 – Manual Data Blending

- For two fields having different names but at least one member/value is commonly present in both fields, then we need to manually establish relationship between these two fields.

E.g. 'Market' field from Coffee chain datastore (MS Access) and 'Region' field from Superstore datastore (MS Excel) have different name but both have at least one common data value, i.e. 'West'

Let us look at both these cases once we cover common steps.

Common Steps

1. Add a new worksheet in your current Tableau Workbook by Worksheet >> New Worksheet.
2. Lets add 'Sample - Coffee Chain (Access)' data source in addition to 'Sample – Superstore Subset (Excel)' data source already in our workbook.

Once done the screen should be as follows:

 

3. Select 'Sample – Coffee Chain (Access)' data connection which gives us the dimensions and measures for this dataset.

Now lets begin with the first Case

Case 1 – Automatic Data Blending

In case two data connections that are currently in use have fields having same name which have at least one common data value among them, Tableau creates a link between them automatically.

In order to view the relationship between connected data sources click Data >> Edit Relationships which opens the following dialog window.

 

As can be seen, 'State' field from Coffee chain datastore(MS Access) and 'State' field from Superstore datastore(MS Excel) have same name and both have at least one common data value, i.e. 'California' .

Let us use this relationship between datasets to create visualization which consumes fields from both of these data connections.

Let us create a visualization for the 'Statewise total sales for Superstore for those states where Coffee chain sells coffee'.

- Making sure 'Sample – Coffee Chain (Access)' is selected in data connections, drag the field 'State' on the Row Shelf of Tableau, yielding to following screen.

 

- Now make sure you select 'Sample – Superstore Subset(Excel)' data connection, which gives you following screen.

 

Make note of the orange strip that is visible on the left of Dimensions, Measures, and Sets which means that these fields are of secondary data store and the resulted visualization is based on the field of the primary data set which is Coffee chain data store. It can also be seen that there is a orange link type icon which represents that the field 'State' is linked to a field in primary data store.

- Now drag the field 'Sales' from the Measures section of the Data window to Column Shelf, which completes the basic visualization we wanted.

 

To summarize, we are viewing 'Statewise total sales for Superstore for those states where Coffee chain sells coffee'. Here as states are selected from Coffee chain data connection, the sales for Superstore is only for these states.

Lets move on to next Case

Case 2 – Manual Data Blending

In case two data connections that are currently in use have fields not having same name, but which have at least one common data value among them, Tableau allows us to create a link between them manually.

- In order to view the relationship between connected data sources click Data >> Edit Relationships which opens the following dialog window.

 

- Click on 'Custom' in order to manually link fields from both of these data sources. Click on 'Add'

- In this case we link the field 'Market' from Primary source (Coffee chain dataset) with 'Region' from Secondary data source (Superstore dataset). Both these fields have values 'Central', 'East', 'West' and 'South'

 

- Click 'OK' and our newly created data relationship is shown as follows:

 

As can be seen 'Market' field from Coffee chain datastore(MS Access) and 'Region' field from Superstore datastore(MS Excel) have different name but both have at least one common data value, say 'West'.

Now lets create a visualization that makes use of the fields which we just related which is 'Marketwise total sales for Superstore for those Regions where Coffee chain sells coffee'

- Create new worksheet Worksheet >> New Worksheet.

- Make sure you have selected the 'Sample – Coffee Chain (Access)' data connection.

- Drag 'Market' field from Dimensions section of Data window to the Row Shelf.

- Select the 'Sample – Superstore Subset(Excel)' data connection.

- Drag 'Sales' field from Measures section of Data window to the Column Shelf.

The isualization is as follows:

 

To summarize, we are viewing 'Marketwise total sales for Superstore for those Regions where Coffee chain sells coffee'. This concludes Case 2.

Data Blending is a great feature in Tableau Desktop which allows to create visualizations that depends on fields from various different data sources.