Cross Data Base Joins In Tableau
Suppose The First Business use case that we have is as below .So this can be treated as 3 different Data Sources as 3 Different Excel
- Sales Data in One Excel
- Return Data in Second Excel
- People Data in Third Excel
For the First scenario We want to compare the Excel Sales Data present in the first excel with the Return data received in the Second Excel and finally with the People data in the third Excel .
The Second Business use case that we have is as below . Again here we have 2 Data Sources One as Excel & the other as Microsoft Sql Server Database
- The Field Engineer captures the Data in Excel sheet regarding the Lead
- Customer Data is already stored in Microsoft SQL server Database
For the Second scenario We want to compare the Excel Lead Data generated with the Customer Data already stored in MS Sql server database.
So in these scenarios Tableau comes with a very handy feature of “Cross Database Joins” that allows you to directly create a Join between different Data Sources and do your Analysis Quickly.
Below we visit Step by Step How to Use Cross Database Join in Tableau
Open Your Tableau Desktop and connect to “SuperStore-Orders” ( see screen below )
Thereafter in order to create a new connection click on “Add” ( marked in the screen above ) and connect to the Excel “SuperStore-Returns” . It automatically creates a join as shown below , you can change it to “Left Join” to see all Orders Data
Then you can click on “Add” again to create the new Join with “SuperStore-People” and default Inner Join is created that you can change to Left Join
Now you can go to tableau Worksheet and work on these Different Data source with Join created . The same applies if the data instead of coming from different Excel , would have been 1 from Excel , 2nd from MS Sql Server and third from MS Access. Tableau 10.0 & above allows you to combine data with create cross database joins , the only pre-requisite is that the Data should be related.
In order to have a Quick Demo of how Tableau will treat if we had the “Order” Details loaded in our Microsoft SQL Server and the Field people would have collected regarding the “Return” information in an Excel sheet and we would like Tableau to connect to both “Return.xls” and “Order” Table in Database.
For Tableau Corporate Training reach out to us at info@instrovate.com
So We have Microsoft SQL Server Management Studio below that shows the Order Details in the table Orders$ in our TableauSuperStore database
And we have the Return Details collected by field people in the excel “SuperStore-Returns.xlsx”
We want to use Tableau 10.0 to create a Join between the Return Excel and the Orders$ table in the SQL Server Data base.
So First we connect to the SQL SERVER Express , give the data base name and user credentials and connect to Order table
There after we click on “Add” button and connect to “Return.xlsx” and Tableau directly creates a Cross Database Default Inner Join. we can change it to left Join to see all Orders Data.
There after go to worksheet and Do analytics on Related Data’s from two Different Data sources ( SQL Server and Excel ) and do the analytics very easily as if they are at the same source.
This is the Beauty of Tableau . There are many more cool features in Tableau which we will keep visiting one by one . For that keep watching this space and if there is any topic that you would like to be covered next , feel free to drop a message in the comment below .
For Corporate training and Online Training contact at info@instrovate.com
