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.
For this recipe, we use two of the six datasets which have been uploaded on the following link:
https://1drv.ms/f/s!Av5QCoyLTBpngh8QBp6ih44zjjbj.
There are six files and we will download the Access database named Modified CoffeeChain.accdb
and the Excel file named Budget Sales for CoffeeChain.xlsx
.
Mac users can download the Excel file named Modified CoffeeChain (Use instead of MS Access).xlsx
instead of the Access database.
Download and save these files in the DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
folder.
The Access database contains measures such as Sales, Profit, and Margin for Products across various States. However, it doesn't have any information about Budget Sales. The Excel file which is the Budget Sales for CoffeeChain.xlsx
file contains only the budget sales information which is missing in the Access database. We want to create one single view which pulls sales data from the Access
file and the budget sales data from the Excel file. Let us see how this can be done.
Let us continue using the same workbook My first Tableau Workbook
.
Multiple Table Join-cross database
.Modified CoffeeChain
file from DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
. Our Mac user should select the Excel option and then select the Modified CoffeeChain (Use instead of MS Access).xlsx
file from DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
. Once we have opened the Modified CoffeeChain
file, we will get a view as shown in the following screenshot:Budget Sales for CoffeeChain
file by clicking on the Add option in Connections. Refer to the following screenshot:Budget Sales for CoffeeChain
file from DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
. Once we do that, we will see that the CoffeeChain Modified has a blue bar whereas Budget Sales for CoffeeChain has an orange bar, which indicates that CoffeeChain Modified is the primary data source and the Budget Sales for CoffeeChain is the secondary data source. Refer to the following screenshot:SUM([Sales])<SUM([Budget Sales])
In the earlier part of the recipe, we saw that for the link or the join condition to work, we need to make sure that the data types of the fields are the same. Once, we sort that issue, then the next steps are similar to what we saw in Understanding Multiple Table Join within a single database recipe.
Currently, the cross-database Joins are not supported for some sources. These include connections to cube data such as Microsoft Analysis Services or extract-only data sources such as Salesforce, Google Analytics, OData, and so on. To know more about Joins, refer to the following link:
http://onlinehelp.tableau.com/current/pro/desktop/en-us/joining_tables.html#integration
3.140.197.10