Data Blending in Tableau and Cross-Database Connectivity

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

By transforming raw data into an intelligible format, Tableau aids in the simplification of the data. Data combinations can be represented visually in spreadsheets and dashboards. Any level of professionals in a company organisation may comprehend the facts in this visualisation. Even a non-technical person may make a customised dashboard using Tableau's data blending feature because it is so easy to use. You only need to do a few straightforward drag and drop actions. To utilise Tableau's blending function, you don't need any programming or database skills.

What Is Data Blending In Tableau?

When you need to analyse relevant data from many sources together, you may use Tableau to do data blending. In an Excel spreadsheet, you may combine the two to get a complete picture. You must combine the data about actual sales with that about goal sales to obtain your sales target measure before comparing primary data, such as actual sales, to secondary data, such as target sales. To combine main and secondary data sources, you must perform a left join such that all of the rows in the primary source correspond to the rows in the secondary source. Let's take a step-by-step look at Data Blending in Tableau.

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

  Follow the link Data New Data Source, then select the Data Source to add the secondary data source. We'll utilise the Microsoft Access data source "Coffee Chain" in this example. Both data sources are shown in 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.

  

• Blending Data

  Using a common field, you can now combine the data from the aforementioned sources. The State dimension would be followed by a little chain picture. Based on the "Names" of the dimensions in both data sources, Tableau determines this common field. Drag the State field from the primary data source to the rows and the Determiner Criteria field, such as Profit, to the Columns now. Bring the profit to the columns from the secondary data source. The graph will demonstrate how each State's determinant criterion, such as Profit, differs across both 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.

Watch Free Videos on Youtube

Advanced Data Blending In Tableau

Advanced data blending features like dynamic workload driven data mixing are available in Tableau.

You can automatically combine data from various and heterogeneous sources, including data marts, data warehouses, spreadsheets, text files, data cubes, and more, using Tableau's data blending tool. You don't need to place the secondary data in any certain format, such as that of the original data collection, thanks to Tableau's dynamic workload-driven data blending feature. One of Tableau's data blending's most obvious benefits is this.

Additionally, you may construct data sources, dashboards, and other views based on your needs. You may also choose data from any data source, such a desktop, mobile device, browser, or embedded system.

To increase efficiency, everyone in a contemporary organisation has to have access to data. Their connection to the data, though, can be different. Different individuals in your organisation may benefit from data in various ways. While some may require data analysis to achieve team objectives and advance the company, others may require it to compile reports and spreadsheets and provide fast summaries of key KPIs for the company to be utilised during team meetings or shareholder meetings. Each stakeholder may benefit individually from Tableau's data blending idea and work towards their goals.

Tableau's sophisticated data blending capability is created with a variety of users in an organization's needs in mind. Because of this, Tableau offers a variety of roles that may be matched to various data requirements. With the help of these roles, users may utilise data in new ways and design displays that are specific to their requirements.

Are you looking to become a Tableau expert? Go through 360DigiTMG's Tableau classes in Chennai.

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.

You may examine ProductName and Date together and construct custom views, such as how many goods were sold on particular dates, if you combine the two tables on ProductID. Such perspectives can assist the sales managers or other relevant staff in coming up with useful suggestions on product design, sales, or other topics. You cannot, however, publish the code, produce extracts, or even use data aggregation to peek into the workings of your company. This is the time to use tableau's cross-database connect feature. This might simplify the task by adding the SQL Server data source to the MySQL data source.

To do a cross-database join, click MySQL and provide the common connection details. The two databases are connected by this process. To join the SQL database, you only need to drag a specific table from MySQL into the canvas. This turns into a source of data with several connections, allowing you to color-code each connection to separate the tables in the newly created cross-database join.

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.

Earn yourself a promising career in PMP by enrolling in the Tableau Training Institute in Pune offered by 360DigiTMG.

Cross-Database Join Versus Data Blending

In contrast to a join, data blending requires many, unique sources of data that are mentioned separately. The primary and secondary data sources must to be distinct and derive from several databases. The secondary data source must be added to the main data source.

It becomes a cross-database join if, instead of adding the secondary data source, you create another connection to the first database.

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.

As a result, you will see an asterisk in lieu of the secondary data value if the secondary data source has several dimensions. You must filter the secondary data source by the original source's dimension in order to get around this issue. Adding a field to the primary data source is another way to get around the issue.

You may think of the blending of Superstore data with Population data as an example of data blending in Tableau with various dimensions in secondary source resulting in asterisk. Here, the Superstore database serves as a supplemental data source to the Population database, which serves as the main data source. The Superstore database, a secondary data source, provides a large amount of data, including information on the states and areas within each state that are linked to 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

By adding a field from the second data source to the view, data blending is done sheet by sheet. To develop a data mix, you must have at least two data sources. You must import a field into Tableau's worksheet from the target main database in order to construct a mix.

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 takes us to the part of the article that describes how to mix data in Tableau. To begin with, make sure the worksheet contains a variety of data sources. To create a blend, you must combine the primary data source with the secondary data source.

• 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

It is possible to combine data from several primary data sources and fields from one primary data source. The primary data source, which determines the display in Tableau, is the first database utilised. As a result, the visualisation pane only displays fields from the secondary data source that match those from the original data source.

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

In spite of these restrictions, data blending is often used in Tableau. The explanations are plausible. When it comes to combining tables from several databases, analysts have a difficult task ahead of them. Most database providers don't provide simple methods for combining data from different databases. This issue has been resolved by Tableau using the idea of data mixing. This is why data blending is used in tableau. You may watch a data mixing Tableau video to clarify your understanding if you are not yet at ease with the blending notion.