Data Blending in Tableau is very useful to join two data sources. For example, If you want to generate a tableau report using multiple data sources (or different data sources), then we can use this Tableau Data Blending technique to join those data sources. For this Tableau data blending demo, we use the data present in the Excel Worksheet and SQL Server.
Tableau Data Blending Approach 1
To perform Data Blending in Tableau, we need at least one common field in both data sources. It doesn’t matter whether it is an integer or string. To demonstrate the Tableau data blending, First, we connected to Employees.xlsx workbook and added the Employee List sheet to the data region as shown below
In this Tableau Data Blending example, We require 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, First name on Rows and Sales Amount, and Yearly income on columns. Let us see the report preview.
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, department and Salary Hike percentage data are present in the Master SQL Server database. It means we have to combine a few columns from the Local Excel Sheet and a few others from SQL Database. It is what we call as Tableau Data Blending
To do this data blending in tableau, Please navigate to Data menu and select New Data Source option from Menu or click on the Cylinder symbol
After you choose the New Data Source option, the below-shown window will appear to Connect. Here, we have to connect with SQL so select Microsoft SQL Server
We picked the Department table from Tableau Database
The newly added data source (SQL) Department table
Let us add the Department name to the Rows Card. As you see that Tableau is displaying a little orange tick mark at the department name and also displaying the warning message.
If you read the warning message, it is a pretty straightforward message saying that there is no relation between Employees and departments. A relationship is significant in Tableau data blending. Please add the connection.
To add a relationship, Go to data menu and select Edit Relationships.. option.
Once you choose the Edit Relationships.. option, a new Relationships window will be opened. The following are the available options for Tableau Data Blending.
- Primary Data Source: Data Source from which we first dragged the fields (Dimension or Measure) into the report region called as a Primary data source. Tableau will display little Blue tick mark beside this data source
- Secondary Data Source: Data Source, which is not primary. Tableau shows a small orange tick mark beside secondary data source
- Automatic: If there are any common Filed names in both the data sources, Tableau automatically detects the possible relationship between those two fields.
- Custom: If there are no common Filed names in the data sources, We have to select the Custom option and add the relationship between them. For now, we are choosing this option
Dept ID in Employee table and Id in Department table are the key columns we are going to use for this Tableau Data Blending on Employee and Department table.
Once you specified 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 linking filed tooltip is displaying. Click the link button to enable Data blending in tableau
When you click the link button, the link button color will change to orange color, and our Department column is displaying data. Remember, If there are no matching records in the Department, then Null values will be displayed.
Let us add a Hike Rate measure to complete our client requirements.
Tableau Data Blending Approach 2
Tableau Data Blending second approach is simple and straightforward. Instead of creating custom relationships between Id and Dept ID columns, let’s give a chance to the tableau intelligence
To do this, Please select and right-click on the Id column and choose the Aliases.. option from it.
Once you decided the Aliases.. option, a new window will be popped up to rename the filed. Let us change the Id Column to Dept ID
Once you click the OK button, Tableau will automatically detect the relationship between Dept ID in Employee table 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
Once you click on the link button, the link button color will be changed to Orange color.
Let us add Department Name and Hike Rate to finish the business requirement.
Remove or Edit Relationships in Tableau
We show you how to remove relationships that we created in Tableau data blending process. First, please navigate to Data menu and select Edit Relationships.. option.
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. In this Tableau Data Blending example, we have only one relationship between Dept ID and Id so, we selected it and removed it.
The following warning message is displayed because we are using Department Name and Hike Rate in Tableau report.
Next, One more warning message which we have seen before. Once you click OK button, Tiny URL link will be removed besides the Id column.
That’s it. We successfully removed the relationship required for Tableau Data Blending.