Tableau is the most powerful, secure, flexible and fastest-growing data visualization tool used in the Business Intelligence Industry. It elevates people with the power of data. It is the best way to change or transform the raw set of data into an easily understandable format with zero technical skills and limited coding knowledge.
What is Data Blending?
Blending means mixing or combining things together. Usually, creating a new dataset requires lots of effort, and it will be time-consuming. You may have sets of related data from divergent datasets and are required to analyze them together using common fields/dimensions, and for this time, it might be required to write code, query or use special applications.
In simple language, we can explain Data Blending a process that helps the business analyst to combine data from multiple datasets into a functional dataset (also consider checking out this perfect parcel of information for a data science degree).
Advantages of Data Blending Over traditional ETL model
Data Blending offers tantamount advantages over the traditional ETL model.
One method is to use joins to set up a new data connection over the traditional ETL data warehouse. First, we need to identify "left" and "right” tables out of two tables and then run a query on the same, which will return the records from the entire left table. Our query will produce a join that displays all data from the left table and additional rows from the right table. This in return query adds duplicate rows each time it finds a matching field in the left table.
This is one of the major disadvantages of joins because a lot of duplications will be there.
Let’s try to understand the same using the below example
Left Join: Left join returns a complete set of records/rows from the left table and includes data from the right table which because each row has corresponding matching rows in the left table.
For example, suppose you have the following tables Table A and Table B. Columns in Table A (DeptId, DepartmentName) and Columns in Table A ( EmpId , EmpName, DeptID).
Blending Data in Tableau
Data blending can be used to blend data from 2 separate data sources that you want to analyze together on a single sheet in Tableau.
To exemplify data blending in Tableau, I will use Salesforce and Excel (2 Different Datasets) with 1 common field among them. I will blend them within the tableau (also consider checking out this career guide for data science jobs).
Step 1: Connect to Data Source both Salesforce and Excel
- First, register on www.salesforce.com to get a trial subscription and navigate to the account object in the Sales application.
- Open Tableau and select connector as Salesforce connector.
- Provide login credentials provide during registration and click on Login
- Once you received verification code in your registered email provide same and click on verify
- In the next step, allow access.
You will be connected to Salesforce, drag and drop “Account” table from Table section.
- Click on Sheet 1 you can preview dimensions and measures on the left side along with current datasets.
- Click on Data 🡪 New Data Source, Select the second data connector and connect to the second set of data. In our case, we will be connecting to an Excel dataset.
- If Tableau finds common fields between both datasets, then it will automatically blend datasets. If not, then you need to edit the connection and configure it manually.
- Click on Data🡪 Edit Relationships 🡪 Choose Custom from the dialog box 🡪 Click “Add” 🡪 Select common fields from Primary Data Source Field and Secondary Data Source Field🡪 Click on 🡪 Click on “ok” again to close the relationship dialog box.
Step 2: Blend Data
- Once the relationship dialog box is closed, you will note that a small link image appears next to the relationship field in dimension. In our case, it will be “Name”. This indicates the relationship field between the two data sources.
Data Blending Limitations
- Data Blending is limited while working with Non-additive aggregates like MEDIAN, COUNTD, and RAWSQLAGG.
- Data blending will affect visualization & data query speed.
- Data Source size will be limited after Data Blending.
- Cube data cannot be used as a Secondary data source for blending data in Tableau.