Cross-database joins in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to analyze total sales by product category combining sales data from the online store and inventory data from the warehouse database.

📊 Data: You have two data sources: 1) Online Sales database with columns OrderID, ProductID, Quantity, SalesAmount; 2) Warehouse Inventory database with columns ProductID, Category, StockLevel.

🎯 Deliverable: Create a Tableau dashboard showing total sales amount by product category, combining data from both databases using cross-database joins.

Sample Data

OrderID	ProductID	Quantity	SalesAmount
1001	P001	2	40
1002	P002	1	25
1003	P003	3	75
1004	P001	1	20
1005	P004	5	100

ProductID	Category	StockLevel
P001	Electronics	50
P002	Home Goods	30
P003	Electronics	20
P004	Clothing	40
P005	Clothing	15

1

Step 1: Connect Tableau to the Online Sales database and load the Sales table with columns OrderID, ProductID, Quantity, SalesAmount.

In Tableau, click 'Connect to Data', select the Online Sales database, and import the Sales table.

Expected Result

Sales data with 5 rows loaded into Tableau.

2

Step 2: Connect Tableau to the Warehouse Inventory database and load the Inventory table with columns ProductID, Category, StockLevel.

In Tableau, add a new data source, select the Warehouse Inventory database, and import the Inventory table.

Expected Result

Inventory data with 5 rows loaded into Tableau.

3

Step 3: Create a cross-database join between the Sales and Inventory tables on the ProductID field.

In Tableau's Data Source tab, drag the Inventory table next to the Sales table and join on Sales.ProductID = Inventory.ProductID using an inner join.

Expected Result

A combined data source with sales and category information joined by ProductID.

4

Step 4: Create a calculated field 'Total Sales' summing SalesAmount.

Create calculated field: SUM([SalesAmount])

Expected Result

Total Sales measure available for analysis.

5

Step 5: Build a bar chart visualization with Category on Rows and Total Sales on Columns.

Drag 'Category' to Rows shelf and 'Total Sales' to Columns shelf.

Expected Result

Bar chart showing total sales amount for each product category.

6

Step 6: Format the chart with clear labels and title 'Total Sales by Product Category'.

Add chart title and axis labels in Tableau formatting options.

Expected Result

Clean, readable bar chart ready for presentation.

Final Result

Total Sales by Product Category

Category      | Total Sales
--------------|------------
Electronics   | ########## (135)
Home Goods    | ### (25)
Clothing      | ####### (100)

Bar chart bars represent sales amounts visually.

✓Electronics category has the highest total sales of 135.

✓Clothing category follows with total sales of 100.

✓Home Goods category has the lowest sales at 25.

✓Cross-database join successfully combined sales and inventory data for analysis.

Bonus Challenge

Add a filter to the dashboard to allow users to select sales by specific categories dynamically.

Show Hint

Use Tableau's filter feature on the Category field and show filter control on the dashboard.

Practice

1. What is the main purpose of a cross-database join in Tableau?

easy

A. To export data to an external file

B. To create a copy of data within the same database

C. To combine tables from different data sources into one view

D. To filter data based on user input

Solution

1. Step 1: Understand cross-database join concept

   A cross-database join allows combining tables from different data sources in Tableau without moving data.

2. Step 2: Identify the main purpose

   The main goal is to create a richer view by joining data from multiple sources.

3. Final Answer:

   To combine tables from different data sources into one view -> Option C

4. Quick Check:

   Cross-database join = combine tables from different sources [OK]

Hint: Cross-database joins combine data from different sources [OK]

Common Mistakes:

• Thinking it copies data instead of joining
• Confusing with data filtering
• Assuming it exports data

2. Which of the following is the correct way to create a cross-database join in Tableau?

easy

A. Drag a table from one data source onto a table from another data source in the Data pane

B. Use the Data Extract option to combine tables

C. Create a calculated field to merge data sources

D. Export both tables and merge in Excel

Solution

1. Step 1: Recall Tableau's method for cross-database joins

   In Tableau, you drag a table from one data source onto another in the Data pane to create a cross-database join.

2. Step 2: Eliminate incorrect options

   Options A, B, and D describe other unrelated methods not used for cross-database joins in Tableau.

3. Final Answer:

   Drag a table from one data source onto a table from another data source in the Data pane -> Option A

4. Quick Check:

   Drag tables between sources = cross-database join [OK]

Hint: Drag tables between sources to join cross-database [OK]

Common Mistakes:

• Using data extracts instead of joins
• Trying to merge with calculated fields
• Exporting data outside Tableau

3. Given two tables from different databases joined on CustomerID, what happens if some CustomerID values exist only in one table?

medium

A. Those rows will be excluded from the join result

B. Those rows will appear with NULLs for missing columns

C. Tableau will throw an error and stop the join

D. Those rows will be duplicated in the result

Solution

1. Step 1: Understand join behavior with unmatched keys

   When joining tables, rows with keys only in one table appear with NULLs for columns from the other table if using a left or full outer join.

2. Step 2: Apply to cross-database join context

   In Tableau cross-database joins, unmatched keys show rows with NULLs, not excluded or duplicated.

3. Final Answer:

   Those rows will appear with NULLs for missing columns -> Option B

4. Quick Check:

   Unmatched keys = rows with NULLs [OK]

Hint: Unmatched keys show NULLs, not errors or duplicates [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Expecting errors on unmatched keys
• Thinking unmatched rows duplicate

4. You created a cross-database join but Tableau shows incorrect results. Which of these is the most likely cause?

medium

A. You joined tables from the same database

B. You forgot to refresh the data extract

C. You used a calculated field instead of a join

D. Join keys have mismatched data types between sources

Solution

1. Step 1: Identify common issues in cross-database joins

   Mismatched data types in join keys cause incorrect or missing matches in joins.

2. Step 2: Evaluate other options

   Refreshing extracts or using calculated fields are unrelated to join key mismatches; joining same database tables is valid but not an error cause here.

3. Final Answer:

   Join keys have mismatched data types between sources -> Option D

4. Quick Check:

   Data type mismatch = wrong join results [OK]

Hint: Check join key data types match across sources [OK]

Common Mistakes:

• Ignoring data type differences
• Assuming refresh fixes join logic
• Confusing calculated fields with joins

5. You want to join a large SQL Server sales table with a small Excel customer list using a cross-database join. What is the best practice to optimize performance?

hard

A. Create extracts for both sources before joining

B. Use a left join with the Excel table on the left side

C. Import the Excel data into SQL Server and join there

D. Join directly without extracts to get live data

Solution

1. Step 1: Understand performance impact of cross-database joins

   Joining large and small tables across sources can slow performance; extracts improve speed by caching data.

2. Step 2: Evaluate options for optimization

   Creating extracts for both sources reduces query time and improves join speed compared to live joins.

3. Final Answer:

   Create extracts for both sources before joining -> Option A

4. Quick Check:

   Extracts speed up cross-database joins [OK]

Hint: Use extracts to speed up cross-database joins [OK]

Common Mistakes:

• Joining live without extracts on large data
• Importing Excel into SQL unnecessarily
• Placing smaller table on left without extracts