23 Aug 2016 Cross-Database Join functionality with Tableau 10

Tableau 10 comes with a great new feature called Cross-Database Join, which allows us to cross data between different sources easily and intuitively.
 
In previous Tableau versions, you needed the Data-Blending solution to join data from different databases. This feature works well enough in one-to-one relationships, but unwanted asterisks pop up when we want to perform a join in one-to-many relationships. JOIN Data from Different Sources is one of the most voted for ideas in the Tableau community for avoiding this scenario and at last we got this great feature in August with Tableau 10.
 
In this article we are going to reproduce these painful asterisks by applying Data-Blending and then explain how to use Cross-Database Join functionality to escape such limitations.

1. Data-Blending

 
Imagine that we want to analyse sales by employee and region and that the data comes from different sources:
 

➜ Oracle table: contains region details

➜ Excel file: contains region details and sales by employee

 
Notice that in the second data source, there are multiple employees by region, so in order to cross data between both data sources we use the Region Name field whose relationship is one-to-many.

Figure 1: Primary Data source

Figure 2: Secondary Data source

 
As we mentioned before, when we apply Data-Blending in one-to-many relationships we get asterisks in those cases where a specific region contains more than one employee.
 

Figure 3: Data-Blending

 
Until now, there was no quick way to avoid these asterisks, and technical IT knowledge was needed to apply database federation between connections.

2. Cross-Database Join

 
Cross-Database Join is the new feature that Tableau 10 provides to cross data between different sources much faster and without any additional technical knowledge. Let’s explain how to perform a Cross-Database Join, step by step, using the same example and data sources as before.
 
First, we need to include the Oracle DB table as a primary source and the Excel file as a secondary source. Once both data sources are available, we need to carry out the following steps to apply Cross-Database Join:
 

1. Place on localhost connection (HR Oracle Schema data source).

2. In table area, double click on REGIONS to use this entity.

3. Perform the same steps to include Employee Sales entity from Employee connection (Excel file). Now you can see that Tableau tries to join both entities automatically.

4. Click on the circles icon; a Join set-up window will appear.

5. Select which kind of join you want to perform.

6. Select the specific fields of each entity that you are going to use to apply the join. In our example, they are “Region Name” field for REGIONS entity and “region Name1” field for Employees Sales.

7. After following the above steps, just click on “Update Now” to display the join results.

Figure 4: Cross-Database Join, step by step with Tableau 10

 
If we reproduce the same analysis as before (sales by employee and by region), Tableau 10 aggregates fields from the second data sources without any issues and asterisks do not appear in the analysis.
 

Figure 5: Data-Blending Tableau 9.3

Figure 6: Cross-Database Join Tableau 10

 
It’s easy to see the benefits of this new feature. Cross-Database Join functionality will allow us to cross data between different data sources and types in an easier and more intuitive way (avoiding those painful asterisks when using Data-Blending). It is a very interesting improvement that many Tableau users will welcome to create their daily scorecards.
 
If you want to know the latest news about Tableau 10 check our previous post and keep updated!
 
Click here if you would like to receive more information about the topic!