Data Blending in Tableau is very useful to join two data sources. For example, If you want to generate tableau report using multiple data sources (or different data sources) then we can simply use this Tableau Data Blending technique to join those data sources.
NOTE: In order to perform Data Blending in Tableau, We need at least one common field in both data sources. It doesn’t matter, whether it is integer or string.
In this article we will show you, Steps involved for Data Blending in Tableau with example. For this, we are going to use the data present in Excel Work sheet and SQL Server so, Please refer Connecting to Excel Files in Tableau and Connecting Tableau to SQL Server articles to understand the connection settings.
Tableau Data Blending Approach 1
First, we connected to Employees.xlsx workbook and added the Employee List sheet to data region as shown below
In this example, Our requirement is to create table report with employee first name, last name, occupation, Sales and yearly income. So, we created 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, Client changed the requirements and asked us to add department and Salary Hike percentage details for every Employee. Here the real problem is, department and Salary Hike percentage data is present in Master SQL Server database. It means, we have to combine few columns from Local Excel Sheet and few from SQL Database. This is what we call as Tableau Data Blending
To do this, Please navigate to Data menu and select New Data Source option from Menu as shown below or click on the Cylinder symbol
After you select New Data Source option, below shown window will be appeared to Connect. Here, we have to connect with SQL so select Microsoft SQL Server as shown below
From the below screenshot you can observe that, we selected the Department table from Tableau Database
Following screenshot will show the newly added data source (SQL) Department table
Let us add the Department name to the Rows Card. From the below screenshot you can observe that, Tableau is displaying little orange tick mark at department name and also displaying the warning message.
If you read the warning message, it is pretty straight forward message saying that, there is no relation between Employees and depart so, please add the relation.
In order to add relationship, Go to data menu and select Edit Relationships.. option as shown below
Once you select Edit Relationships.. option, a new Relationships window will be opened. Following are the option available in this window.
- Primary Data Source: Data Source from which we first dragged the fields (Dimension or Measure) into report region is called as Primary data source. Tableau will display little Blue tick mark beside this data source
- Secondary Data Source: Data Source which is not primary. Tableau will display little orange tick mark beside secondary data source
- Automatic: If there are any common Filed names in both the data sources then tableau will automatically detect the possible relationship between those two fields.
- Custom: If there is no common Filed names in the data sources, We have to select Custom option and add the relationship between them. For now, we are selecting this option
Dept ID in Employee table and Id in Department table are the key columns we are going to use for Data Blending on Employee and Department table as shown below.
Once you specified the relationship, tiny URL link will be appeared besides 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, link button color will be changed to Orange color and Our Department column is displaying data. Remember, If there is no matching records in Department then Null values will be displayed.
Let us add Hike Rate measure to complete our client requirements.
Tableau Data Blending Approach 2
Tableau Data Blending second approach is simple and straight forward. Instead of creating custom relationships between Id and Dept ID columns, let’s give chance to the tableau intelligence
To do this, Please select and right-click on the Id column will open the context menu. Please select the Aliases.. option from the context menu as shown below
Once you selected 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 OK button, Tableau will automatically detects the relationship between Dept ID in Employee table and Dept ID in Department table. It means, you don’t have to create relationship on your own. From the below screenshot you can observe that, tiny URL link will appear besides the Dept ID column. Click the link button to enable Tableau Data Blending
Once you click on the link button, link button color will be changed to Orange color as shown below.
Let us add Department Name and Hike Rate to finish the business requirement.
Remove or Edit Relationships in Tableau
Please navigate to Data menu and select Edit Relationships.. option from Menu as shown below
Once you select Edit Relationships.. option, a new Relationships window is opened. Please select the Relationship you want to remove and Click Remove button. In this example we have only one relationship between Dept ID and Id so, we selected it and removed.
Following warning message is displayed because, we are using Department Name and Hike Rate in Tableau report.
Next, One more warning message which we seen before. Once you click OK button, Tiny URL link will be remove besides the Id column as shown below
That’s it, We successfully removed the relationship required for Tableau Data Blending