Tableau Data Blending

Tableau Data Blending is very useful for joining or combining records from two data sources. For example, If you want to generate a report using multiple or different data sources, then we can use this Data Blending technique to join them. For this Tableau data blending demo, we use the sheets present in the Excel Worksheet and SQL Server.

Tableau Data Blending Example

To perform Tableau Data Blending or combining, we need at least one common dimension in both sources. It doesn’t matter whether it is an integer or a string. To demonstrate Data Blending, we connected to the primary data source Employees.xlsx workbook, and added the secondary data source called Employee List sheet to the region.

Excel Workbook for Tableau Data Blending

In this Tableau Data Blending example, We are required to create a table report with the employee’s first name, last name, occupation, Sales, and yearly income.

So, we created a simple table report with Occupation, Last name, and First name on the Rows shelf, Sales Amount, and Yearly income on the columns shelf. Let us see the report preview.

Please refer to Connecting to Excel Files and Connecting to Server in Tableau.

Table Report

Scenario: Suddenly, the Client changed the requirements and asked us to add department and Salary Hike percentage details for every Employee. Here the real obstacle is the department, and Salary Hike percentage columns are present in the Master SQL Server database.

It means we have to combine a few columns from the primary data source, the Local Excel Sheet, the records from the secondary data source, and a few others from SQL Database. It is what we call Tableau Data Blending.

To do the Tableau blending of primary and secondary or multiple data sources, Please navigate to the Data menu. Next, select the New Data Source option from the Menu or click on the Cylinder symbol.

Add New Data Source to existing report for Tableau Data Blending

A.fter you choose the option, the below-shown window will appear to Connect. Here, we have to connect with SQL, so select Microsoft SQL Server.

Connect to Microsoft Sql Server

We picked the Department table from the Database for the Tableau Data Blending.

Select Database Tables

The newly added data source (SQL) Department table comes from the secondary data sources.

Data Blending In Tableau 6

Let us add the Department name to the Rows Card. As you see that the desktop is displaying a little orange tick mark at the department name. It is also displaying a warning message.

If you read the warning message, it is a pretty straightforward message, saying there is no relation between Employees and departments. A relationship is significant to performing data blending in Tableau. Please add the connection.

Relationship Warning 7

To add a relationship, Go to the data menu and select the Edit Relationships.. option.

Under data Menu choose Edit Relationship option to solve Tableau Data Blending error 8

Once you choose the Edit Relationships.. option, a new Relationships window will open. The following are the available options for Tableau Data Blending.

  • Primary Data Source: From which we first dragged the fields (Dimension or Measure) into the report region called a Primary data source. The desktop will display a little Blue tick mark beside this one.
  • Secondary Data Source: This is not primary. It shows a small orange tick mark beside the secondary data source.
  • Automatic: If there are any common dimension names in both data sources, the desktop automatically detects the possible relationship between those two fields.
  • Custom: If there are no common Field names in the primary and secondary data sources, We have to select the Custom option. And then add the relationship between them. For now, we are choosing this option
Relationship window will open 9

Dept ID in the Employee table and Id in the Department table are the key columns. We will use them for this Tableau Data Blending on Employee and Department table.

Choose the Common Column from Primary and Secondary Data source fields 10

Once you specify the relationship, a tiny URL link will appear beside the Id column. From the below screenshot, when you hover on that link, Use Id as the linking field tooltip is displaying. Click the link button to enable Data blending in Tableau.

Use ID as Linking Field 11

When you click the link button, the link button color will change to orange color, and our Department column is displaying records. Remember, If there are no matching records in the Department, then Null values will show.

Data Blending In Tableau 12

Let us add a Hike Rate measure to complete our client requirements.

Data Blending In Tableau 13

Tableau Data Blending using Aliases Column

The second approach is simple and straightforward. So instead of creating custom relationships between Id and Dept ID columns, let’s give a chance to this desktop intelligence.

Using Desktop Intelligence 14

To do this, Please select and right-click on the Id column. Next, choose the Aliases.. option from it.

Right-click on Dimension and choose Aliases Name 15

Once you decide on the Aliases.. option, a new window will open to renaming the field. Let us change the Id Column to Dept ID.

Change the Name in Rename Field box 16

Once you click the OK button, it will automatically detect the relationship between Dept ID in Employee and Dept ID in the Department table. It means you don’t have to create a relationship on your own.

You can observe that a tiny URL link will appear beside the Dept ID column. Click the link button to enable Tableau Data Blending.

Data Blending In Tableau 17

Once you click on the link button, the link button color will change to Orange color.

Data Blending In Tableau 18

Let us add Department Name and Hike Rate to finish the business requirement.

Remove or Edit Tableau Relationships

We show you how to remove relationships that we created in the Tableau data blending process. First, please navigate to the Data menu and select the Edit Relationships.. option.

Edit Table Relationships

Once you select Edit Relationships.. option, a new Relationships window will open. Please choose the Relationship you want to remove and click the Remove button. For example, in this Tableau Data Blending example, we have only one relationship between Dept ID and Id, so we selected and removed it.

Remove the Field 20

The following warning message is displayed because we use Department Name and Hike Rate in the report.

no connection error 21

Next, One more warning message that we have seen before. Once you click the OK button, the Tiny URL link will remove beside the Id column.

We successfully removed the relationship required for Tableau Data Blending.

Relationship between two data Sources error

Comments are closed.