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.
- Let us create a new sheet and rename it
Multiple Table Join-cross database. - Let us create Ctrl+D to connect to the data and select the Access option and then select the
Modified CoffeeChainfile fromDocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data. Our Mac user should select the Excel option and then select theModified CoffeeChain (Use instead of MS Access).xlsxfile fromDocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data. Once we have opened theModified CoffeeChainfile, we will get a view as shown in the following screenshot:
- We will then connect to the
Budget Sales for CoffeeChainfile by clicking on the Add option in Connections. Refer to the following screenshot:
- In the popup section, select the Excel option and browse the
Budget Sales for CoffeeChainfile fromDocumentsMy 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:
- Further, we will also see that the joining condition is not working and it has a red exclamation mark. If we hover over the exclamation mark, we will get a message saying that the Date field in the Access database is a Date & Time field, whereas in the Excel file, it is just a Date field. Refer to the following screenshot:
- This is clearly a data type mismatch issue and, in order to rectify this, we will look for the Date field of the Budget Sales for CoffeeChain datasource in the data preview section and change the datatype to the Date & Time field. Refer to the following screenshot:
- Once we change the datatype, our Join will be sorted as shown in the following screenshot:
- Now that the Join is sorted, let's add more linking fields on Product and State as well. Refer to the following screenshot:
- Now that we are sorted on the Joins, let us quickly change the name of the data source from Sheet1+ (Multiple Connections) to Multiple Table Join-cross database as shown in the following screenshot:
- Let us click on the Multiple Table Join-cross database sheet tab in order to start building our visualization.
- Next, we will create a State map by double-clicking on the State field from the Sheet1 table in the Dimensions pane. In this case, the map doesn't show up, so let's make sure we select the country as United States from the Edit Locations…. option in the Map section of the toolbar.
- Let's then fetch Sales from the Measures pane and drop it into the Size shelf. Now we would want to compare the Sales and the Budget Sales and find out which states are below target and which states are above target. In order to do so, we will create a new calculated field and call it Sales vs Budget Sales: The formula could be as follows:
SUM([Sales])<SUM([Budget Sales])
- Refer to the following screenshot:
- When we click OK, we will get a new measure which is of Boolean output. Let us the new field and drop it into the Color shelf as shown in the following screenshot:
- In the preceding screenshot, we can see that there is only one State which is below target. However, these targets are across all the products. If we wish to see the sales performance of all the states versus the budget values for a particular product, then let's make the provision for the user to select the desired product by creating a filter on Product from the Sheet1 table by right-clicking on it and selecting the Show Filter option. Refer to the following screenshot:
- Next, let us make the filter a single select radio button option by clicking on the Filter dropdown and selecting the option of Single Value (list). Refer to the following screenshot:
- If we select Colombian, then our view will update as shown in the following screenshot:
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