Concept of Tableau Blend and Tableau Join before Tableau 10.0 and The Easy way that Tableau handles it through cross Data Base join in Tableau 10.0
Cross Data Base Joins In Tableau 10.0
We Explain this concept through the classic Sample Super Store but in order to explain the Blend, join in Tableau 9.0 to 9.3, we have changed the three Sheets of Sample Super Store in Three different Excel. So rather than having 1 Excel Sheet having three sheets, we have 3 different Excel Here.
Now before Tableau 10.0, if you want to do an Analysis on these 3 Excel, you will have to create a Blend (In order to use Join, you will have to bring the data from the three Excel in one Excel sheet, only then you would be able to use join).
Using Blend to Connect to Different Excel ( Before Tableau 10.0 )
Open your Tableau Desktop and connect to SuperStore-Orders.xlsx . This creates the primary connection.
Thereafter click the Database+ button at the top , and add the secondary connection with the “SuperStore-Returns.xlsx”
Again press the Database plus button at top and connect with your “SuperStore-People.xlsx”
There after go to your Tableau “sheet” and you would see all your connection as below
Click on Orders as this is your Primary Connection and there after click on Data – Edit Relationship as below
You will see both the “Relationship” already created between primary and secondary based on the name . Validate it ,if it is correct. If it is not , click on custom and change the relationship.
Now suppose you want to see all the Orders, with the “Returns” and the Region “Person” responsible for each of the Orders.
So Drag Order Id from Order-SuperStore Orders. To get the Returns go on Return Sheet. Check if the Link on OrderId is always created (showing as Orange marked below), if not click to create it. And Drag “Returned” on Colors.
Now to see the People Associated, go to People, click on “Region” to highlight the “Orange” connection and there after drag the People on to the visualization.
Tableau treats 3 different Excel sheet as 3 different Data source so you have to create Blend in order to use the Data from them and we know that Blend is not a Pure Join.
If we wanted to use “Join” in case of “Blend” before Tableau 10.0 , one of the option was to copy the “Return” and “Person” from different excel to Different Sheet of the “Orders” excel . Experienced Excel users can create Macros so that the Data from different Excel shows on the same Excel in different Sheet.
Tableau 10.0 comes with cross Data base join feature, so for different Excel Tableau 10.0 allows you to create Joins. The same example that we dealt above is shown below step by step to be used in Tableau 10.0. This is shown for Different Excel sheet that is being considered as different Datasource by Tableau , however the same approach can be used for analysis if we have one of our Data in Excel and the Other in Microsoft SQL Server or MS Access Or Hadoop . This is essentially the Cross Database Join feature that Tableau 10.0 comes with and is really a great feature .
You can have a use case , say the Field Engineer captures the Data in Excel sheet regarding the Lead , and you want to compare the Excel Lead Data generated with the Customer Data already stored in MS Sql server database. So in all these scenario 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.
Using Cross Database Join in Tableau 10.0
Open Your Tableau 10.0 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 sheet 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 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.
So We have Microsoft SQL Server Management Studio that contains 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 10.0 . There are many more cool features in Tableau 10.0 which we will keep visiting one by one . For that keep watching this space and if there is any topick that you would like to be covered next , feel free to drop a message in the comment below .