Chapter 7. The Right MIX – Blending Multiple Data Sources

In this chapter, we will focus on various aspects of connecting to data and we will cover the following recipes:

• Understanding Multiple Table Join within a single database

• Understanding Multiple Table Join across databases

• Understanding Data Blending

• Understanding and using Unions

• Using Custom SQL Query to fetch data

• Working with Tableau Extracts

Introduction

So far, in all the previous chapters, we've created various visualizations. For each of these visualizations or sheets, we connected to a single data source at a time using the Live connect option in Tableau. However, there are going to be circumstances when data from a single database table or even for that matter a single database may not be sufficient from the point of view of our analysis. There may also be circumstances, where we may have to extract our data into the Tableau data engine and work offline to build our views in Tableau. In this chapter, we will focus on the various ways in which we can connect to data in Tableau.

Understanding Multiple Table Join within a single database

Often there will be instances where the data is stored in multiples tables of a database and for the sake of analysis, we are required to fetch the data from these tables.

For example, there may be a table called Employee Master which contains the employee details such as employee ID, their first name, last name, date of birth and so on. The salary/compensation details could be stored in another table and the employment history could be stored in a separate table as well.

When conducting an analysis, we would want to consider all the information related to an employee and then proceed with our analysis. Thus, in this case, we are required to fetch the data from all the three tables, and while doing so we will also have to specify the Joins by defining a primary key. The primary key in this case could be Employee ID which is common across all the tables.

The functionality is called Multiple Table Joins and it is used when we want to...

Understanding Multiple Table Join across databases

In the previous recipe, we saw how to connect to a single database which was an Access file named Sample - Coffee Chain.mdb or Sample - CoffeeChain (Use instead of MS Access).xlsx and join multiple tables within it. There could also be instances where the data resides in multiple data sources. For example, the transactional sales data could be getting captured in, let's say, a SQL database and the yearly/monthly budgets are defined in Excel. In this situation, Excel is one data source and SQL is another data source. In order to see whether the targets were met or not, we would be required to get data from both Excel as well as SQL.

Cross-database Joins help us make Joins across multiple databases across a single data source, or multiple databases across multiple data sources.

Let us take a look at how we can do a cross-database join in the following recipe.

Getting ready

For this recipe, we use two of the six datasets which have been uploaded...

Understanding Data Blending

In the earlier recipes, we saw how to join data from multiple data tables that are either within a single database or across multiple databases. The cross-database join functionality was introduced by Tableau in version 10.0 and, in versions prior to this, one could rely on the Data Blending functionality to get data from multiple data sources.

Even though the cross-database Join has made joining the data from disparate data sources fairly easy, the Data Blending functionality still exists and can be used where cross-database Joins won't work. For example, cross-database functionality can't be used with Salesforce or Google Analytics or even multi-dimensional cubes. One can also use Data Blending over cross-database Joins when the data is at a different granularity.

Let us explore this functionality in more details in the following recipe.

Getting ready

For this recipe, we will download the sample data that has been uploaded on the following link:

https://1drv.ms...

Understanding and using Unions

So far we have understood how Joins and Data Blending work. However, when we use Joins or blending, we end up appending columns from one table to another. Now imagine a situation where we need to append rows from one table to another. So, for example, imagine having separate tables for each quarter; each table contains the same information but only for the relevant quarter. Now if we wish to look at the performance of the entire year, then we will need the data from all these separate quarter tables. In this case, we will use the Union functionality. Let's see how we can do Unions in Tableau in the following recipe.

Getting ready

For this recipe, we will download the Excel file named Union data.xlsx that has been uploaded on the following link:

https://1drv.ms/f/s!Av5QCoyLTBpnhkx2T7tGFlMQ32MR

We will download this file and save it to the Documents\My Tableau Repository\Datasources\Tableau Cookbook data folder. We will continue using the same workbook, My first...

Using Custom SQL Query to fetch data

When connecting to the data, there may be instances where we would want to connect only to a specific query rather than the entire data source, or use some specific filters before getting the data into Tableau. There could also be instances where we may want some calculation to be pre-computed before fetching it into Tableau. In all these situations, we can write a Custom SQL to fetch the data in Tableau. For using Custom SQL Query option in Tableau, we need to be familiar with writing SQL queries. Let's see how to use the Custom SQL option in Tableau.

Getting ready

We will use the Sample - Coffee Chain.mdb data or Sample - CoffeeChain (Use instead of MS Access).xlsx and we will continue working in the same workbook. We have already established a connection to the Sample - Coffee Chain.mdb Access file, or Sample - CoffeeChain (Use instead of MS Access).xlsx Excel file for our Mac users, for some of our previous recipes. However, for this recipe, we will...

Working with Tableau Extracts

As discussed in our first chapter, there are two options of connecting to our data, namely, Live and Extract. With the Live option, Tableau connects directly to our data and maintains a Live' connection with the data source. In Live connection, Tableau leverages the capabilities of our data source and hence the speed of our data source will determine the performance of our analysis. Whereas, the Extract option helps us import the entire data or subset of our data into Tableau's fast data engine as an Extract. This basically creates a .tde file which stands for Tableau Data Extract.

So far we have used the Live connect option for connecting to our data sources. However, in this section, we will focus on the Extract option.

Typically, Extracts are used for the following reasons:

• Improving performance: If the data source speed is hampering the performance of our analysis, then we can use an Extract. This could be the case when connecting to flat files such as Excel...