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

If you are new To Data Analytics world and wondering why you should learn Data Visualization or Tableau visit my link that explains the importance of learning Tableau for you Data Analytics Journey - Start Your Analytics Journey - Learn Tableau .

During my Corporate Tableau Training in Gurgaon , i get questions many time regarding Cross Database Joins in Tableau .  You can also view this content at my Tableau Community Profile and is one of the largest visited blog - Cross Database Join On Tableau Community Link

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.

For Corporate training and Online Training contact at info@instrovate.com

 

 

 

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

                                                   [ akriti.lal@TechnicalJockey.com ]

 

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.

  [ akriti.lal@TechnicalJockey.com ]

 

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.

[ akriti.lal@TechnicalJockey.com ]

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 .

For Corporate training and Online Training contact at TJT@TECHNICALJOCKEY.COM

 

Write a comment

Comments

  • Arpit Ankan (Tuesday, August 23 16 01:10 pm EDT)

    This is Really Helpful. Thanks Akriti for explaining with such ease . Expecting more articles explaining other new concepts.

  • piyush (Friday, August 26 16 04:42 am EDT)

    SOunds Really Helpful.
    Cross Database join always required.

  • Justine Dela Torre (Friday, September 09 16 02:07 am EDT)

    Great article and examples! It was easy to understand and follow.

  • Jayesh Medhe (Monday, April 24 17 01:36 pm EDT)

    great article Akriti

  • Sneha (Friday, February 23 18 03:14 am EST)

    Great Article. Can you please help me in understanding the concept of Context Filters with example?

Please enter the code
* Required fields

Subscribe to our mailing list

* indicates required

Looking for Corporate Training ? Reach out to us at Akriti.Lal@instrovate.com

Reach out to us if you are looking for Corporate Training to Build The Next Generation Analytical Workforce with an in-depth understanding of  Exploratory Data Analysis , Data Visualisation, Data Analytics , AI First , Machine Learning & Deep Learning Training & Consulting helping them to take Data Informed Decision at each stage of the business. 

We understand that At the present times , the Entire Industry is in a Tranformation stage with the Softwares  being rebuilt with Artificial Intelligence Capabilities .

We need SMART WORKFORCE for the SMART SOFTWARES to reap the maximum return . 

 

Whatsapp at +91-9953805788 or email at - akriti.lal@instrovate.com if you would like to know more . 

 

  • Corporate Tableau Training in Gurgaon
  • Corporate Data Analytics Training in Gurgaon
  • Corporate Microsoft Power BI Training in Gurgaon
  • Corporate Microstrategy Training in Gurgaon
  • Corporate Google Data Studio Training in Gurgaon
  • Corporate Python Training in Gurgaon
  • Corporate Advance Analytics in R Programming Training in Gurgaon
  • Corporate Machine Learning Training in Gurgaon
  • Corporate Deep Learning Training in Gurgaon
  • Corporate Data Visualization Training in Gurgaon

 

Address

Instrovate Technologies

Noida, Gurgaon

 

akriti.lal@instrovate.com 

Hit Your Refresh Button To Rise Higher
Print Print | Sitemap
All rights reserved @ Instrovate Technologies
GSTIN : 09AAECI6862K1Z1

Terms Of Services    Privacy Policy    Disclaimer     Refund Policy    


Call

E-mail