Data Blending in Tableau and Cross-Database Connectivity
Data Blending in Tableau and Cross-Database Connectivity. When to use what?
Dear Reader, if you are looking for information on data blending you are in the right place. Here we will discuss about data blending and other relevant information.
Businesses generate huge volumes of data during their regular activities, be it machine-generated data, customer data, metadata, audit trail, transactional data, or anything else. There is also data for reference such as master data, industry metrics, etc. All the data so created or generated is stored in relational databases in machine-readable formats.
However, simply storing data is of no use unless you can elicit actionable points from them. In order to harness the power of data you need to visualize different genres of data in a single pane. This can help businesses come up with actionable points in the procurement of materials, sales, product design, supply chain management, and more.
To accomplish this objective one needs to have the wherewithal to visualize data in a meaningful way. This can be done with a data analytic platform with a powerful data blending feature such as in Tableau.
What is Tableau?
This is a data visualization tool with a powerful blending feature. With the help of this feature, you can query each source of data independently and visualize the aggregated result for a comprehensive idea about the operational aspects of a business.
Introduction To Tableau
Tableau helps to simplify raw data by converting it into an easily understandable format. You can visualize combinations of data in the form of worksheets and dashboards. Professionals of any level in a business organization can understand the data so visualized. Data Blending in Tableau is so seamless that even a non-technical user can create a customized dashboard. You just need to do some simple drag and drop operations. You do not need any database or programming experience to use the blending feature of Tableau.
What Is Data Blending In Tableau?
Data Blending in Tableau can help businesses harness the power of data effectively. You can import additional data from a secondary source and display it along with data from the primary source. The data blending feature of Tableau helps to display the combined information while keeping the sources separate. Since data from different sources often come with different levels of granularity, it helps to blend this data rather than join them. Joining may result in duplication of the secondary data since joining is done at row-level.
Introduction To Data Blending In Tableau
You can do data blending through Tableau when you have related data from multiple sources and need to analyze them together. You can blend the two in an Excel spreadsheet and get a comprehensive view. To compare the primary data such as actual sales to the secondary data such as target sales, you need to blend the data about actual sales with that about target sales to get your sales target measure. To blend data you need to create a left join between the sources of primary and secondary data so that all the rows in the primary source match the data rows from the secondary source. Let us understand Data Blending in Tableau step by step.
Preparing Data for Blending
As the first step to Data Blending in Tableau, you need to load the primary data to Tableau and look at the metadata. There are different tables and joins available there. In this example we will use “Sample Superstore” Microsoft Excel data source.
-> Select “Microsoft Excel” from the Connect Pane.
-> Browse to the Path where the file is present, and select it.
-> Use the ‘Orders’ table from the Superstore connection. You can see the preview of the data on the Data Grid.
Adding Secondary Data Source
Now, add the secondary data source by tracing the path - Data → New Data Source and then choosing the Data Source. In this example we will use “Coffee Chain” Microsoft Access data Source. You can see both the data sources on the Data window.
-> Go to the Data option on the Menu Bar, and choose “New Data Source” option.
-> Select “Microsoft Access” to connect to the “Coffee Chain” data source. Navigate to the location where the data source is present and select it.
-> Join the Data from - Fact Table, Location Table and Product Table.
-> You can see both the data sources on the Data Pane.
Now you can blend the data from the above sources based on shared field. A small chain image would appear next to the dimension called State. This is the common field that Tableau identifies based on the “Names” of the dimensions in both the data sources. Now, drag the State field from primary data source to the rows and the field of the determinant criteria such as Profit to Columns. From the secondary data source bring Profit to the Columns. The chart will show how the determinant criterion such as Profit varies for each State in both the data sources.
-> Below chart shows the Profit across different States in Superstore.
-> Notice the Orange Link on the “State” dimension in Coffee Chain data source.
-> Get “Profit” from Coffee Chain data source and place it on the Rows shelf. We can see the chart updated with the profits from Coffee Chain as well.
Additional secondary data source
You can also bring in additional relevant secondary data source and blend it to the primary data and get a combined view for inference.
Advanced Data Blending In Tableau
Tableau comes with advanced data blending capabilities such as dynamic workload driven data blending.
Using the data blending feature of Tableau you can automatically integrate data from different and heterogeneous sources such as data marts, data warehouses, spreadsheets, text files, data cubes, etc. The dynamic workload driven data blending capability of Tableau makes sure that you don’t need to put the secondary data in any particular format such as that of the primary data set. This is one of the most pronounced advantages of data blending in Tableau.
You can also build data sources and dashboards and create different views as per your requirement. Moreover, you can select data from any data source such as desktop, mobile, browser, or embedded.
In a modern organization, everybody needs access to data to enhance their productivity. However, their relationship to data may be different. Data may enable different people in your organization differently. While some may need to analyze data to accomplish team goals and drive the business forward, others may need it to prepare reports and spreadsheets and come up with quick snapshots about important KPIs of the business to be used during team meetings or shareholder meetings. The data blending concept in Tableau can help all the stakeholders in their own way and let them achieve their objectives.
The advanced data blending feature of Tableau is designed in such a way that it meets the needs of different users in an organization. This is why there are different roles in Tableau which can match different data needs. These roles allow users to use data differently and create customized views keeping in view individual needs.
Data Blending And Cross Join In Tableau
Tableau is flexible in so far as performing various operations on data is concerned. Data blending lets you see two or more sets of data side by side without clubbing them together. However, that may not be what you need when you desire to join two data sources to come up with a single data source. As a data blending in Tableau example there may be a Sales table showing individual transactions in individual rows and each of them having columns like OrderID, Date, ProductID, etc. There may also be a Products table which shows all the products that a company can sell. Again it contains columns like ProductID and ProductName.
If you join the two tables on ProductID, you can view ProductName and Date together and also create customized views like how many products have been sold on which date. Such views can help the sales managers or relevant personnel come up with actionable points about product design, sales, or others. Yet, you cannot publish the source or generate extracts or even use data aggregation to get a peep into the affairs of your business. This is when you need to go for a cross-database join in tableau. This can add the data source in SQL server to the data source in MySQL and make the job easy.
You need to click on MySQL and enter the common connection information to perform cross-database join in Tableau. This operation establishes a connection between the two databases. Now you just need to drag the specific table from MySQL into the canvas to join the SQL database. This becomes a multi-connection source of data where you can color-code each data connection to distinguish the tables in the cross-database join that you just established.
The benefit of such a join is that you can do row-level calculations using data fields from both SQL and MySQL databases. You can even create an extract of this data source and make a report. However, such cross-database joining is not possible for data fields not supported for cross-database joins.
Cross-Database Join Versus Data Blending
To perform data blending you need multiple and distinct sources of data listed independently unlike a join. The primary data source and the secondary data source should be distinct and should come from different databases. You need to add the secondary data source to the primary data source.
If instead of adding the secondary data source you establish another connection to the first database, it becomes a cross-database join.
Data Blending In Tableau Asterisk
However, while blending data from two different sources, in some cases, the value of data from the secondary source may show up as an asterisk and not as the actual data. Such a behaviour is expected when there is a one-to-many mapping of data bits, wherein the many are in the secondary data source. This is one of the few data blending disadvantages in Tableau. In data blending you have to work with aggregates from the secondary source of data.
Therefore, if there are multiple dimensions in the secondary data source, you will get an asterisk in place of the value of secondary data. To avoid this problem you need to filter the secondary data source by the dimension of the primary source. You can also avoid the problem by bringing a field into the primary data source.
As an example of data blending in Tableau with multiple dimensions in secondary source resulting in asterisk, you can consider the blending of Superstore data with Population data. Here the Population database is the primary data source and the Superstore database is the secondary data source. The secondary data source, i.e the Superstore database contains a lot of data including the data about states and regions in each state associated with the Superstore database.
You can override the asterisk problem by blending the two data sources to create a primary group. Now, pull in the Region field from the Superstore database into the Population database. So, you can get a combined database without using the Superstore database at all.
Steps For Data Blending In Tableau
Data blending is performed sheet-by-sheet by establishing a field from the second data source in the view. You need to have at least two data sources to create a data blend. To create a blend you need to import a field from the target primary database into the worksheet of Tableau. This becomes the primary data source. Now, switch to the secondary database and import a field from this database into the same worksheet. This becomes the secondary data source. As soon as this is done, a linking icon in the shape of a small orange chain will appear in the data pane. This indicates the fields that are being used to blend the two data sources.
How To Use Data Blending In Tableau?
This brings us to the section on how to perform data blending in Tableau. First of all you need to ensure that the workbook has multiple data sources. You need to add the secondary data source to the primary data source to get a blend.
- Drag the relevant field from the primary data source to the visualization pane. The database which contributes to this field of data is the primary data source.
- To add the secondary data source go to Data > New data source. Make sure that there is a relationship between the two data sources.
- If you can see an orange field linking icon ( ), you need to understand that the data sources are inherently related.
- You can perform the blend even if there is only one active link.
- If there are no active links you cannot perform the blend.
- If you see icons suggesting a broken link next to the data field that should supposedly link the two databases, click on the icon. It should turn orange and establish a connection.
- Now, drag a field from the secondary data source into the visualization pane of tableau.
- As soon as you do this, a blend between the primary data source and the secondary data sourced is established.
Limitations Of Data Blending In Tableau
Data blending can be performed between the fields of a single primary data source and those of multiple data sources. The first database used is the primary data source and this defines the view in Tableau. Owing to this, only those fields in the secondary data source which bear corresponding matches with the primary data source appear in visualization pane.
This can restrict many values from the secondary data source. This is one of the disadvantages of data blending in Tableau. Moreover, blending is a kind of left join, not exactly a join. You cannot do data source blending in tableau. If you are looking for types of data blending in tableau you should know that there is only one type. You can join the two data sourced based only on the common dimension/dimensions, whatever those might be.
Popularity Of Data Blending With Tableau
Despite these limitations there is a widespread use of data blending in Tableau. The reasons are not far-fetched. Analysts face an uphill task in so far as joining tables from different databases is concerned. Most database vendors don’t offer easy ways to combine data from other databases. Tableau has solved this problem with the concept of data blending. This is why do we use data blending in tableau. If you are not comfortable with the blending concept yet, you can go through a data blending Tableau video to sharpen your concept.
You may also like...
As per the most recent report by KellyOCG India, it is expected that there would be a 60% Increased demand for Artificial Intelligence and Machine Learning.
The 21st century is all about extreme market competition in every industry and sector that we were to name. To stay in the game it has become important to outperform one’s rivals.