Data relationships model in Tableau - Step-by-Step Guide

Introduction

Data relationships model in Tableau helps you connect different tables without merging them. It lets you analyze data from multiple sources easily by defining how tables relate to each other.

When you have sales data in one table and customer info in another and want to analyze them together.

When your data comes from different sources but shares common fields like product ID or date.

When you want to avoid duplicating data by joining tables physically.

When you want Tableau to automatically choose the best way to combine data for your analysis.

When you want to keep your data model flexible and easy to update.

Steps

Step 1: Open

- Tableau Desktop and connect to your data sources

Data sources appear in the Data pane on the left side

Step 2: Drag the first table

- Data pane to the canvas area in the Data Source tab

The table appears as a box on the canvas

Step 3: Drag the second table

- Data pane to the canvas near the first table

A link icon appears between the two tables indicating a relationship

Step 4: Click the link icon

- Between the two tables on the canvas

The Edit Relationship dialog opens showing fields to define the relationship

💡 Use common fields like Customer ID or Date to define the relationship

Step 5: Select matching fields

- Edit Relationship dialog for each table

Fields are linked showing how tables relate

💡 You can add multiple field pairs if needed

Step 6: Click OK

- Edit Relationship dialog

The relationship is saved and shown on the canvas

Step 7: Use the related tables in your worksheets

- Data pane and worksheet

You can drag fields from both tables and Tableau combines data automatically

Before vs After

Before

Two separate tables with no connection; dragging fields from both causes errors or duplicates

After

Tables linked by relationships; dragging fields from both shows combined data correctly without duplication

Settings Reference

Relationship fields

📍 Edit Relationship dialog on the Data Source tab

Defines how tables relate to each other

Default: No fields selected

Cardinality

📍 Edit Relationship dialog

Specifies how many matching rows exist between tables

Default: Automatically detected

Referential Integrity

📍 Edit Relationship dialog

Helps Tableau optimize queries if you know all related rows exist

Default: Not enforced

Common Mistakes

Using joins instead of relationships for all cases

Joins combine tables physically and can cause data duplication or loss

Use relationships to keep tables separate and let Tableau combine data as needed

Not selecting correct matching fields in the relationship

Incorrect fields cause wrong data combinations or empty results

Choose fields that uniquely identify related records in both tables

Ignoring cardinality settings

Wrong cardinality can lead to incorrect aggregations or performance issues

Set cardinality to match your data’s real relationships

Summary

Data relationships model connects tables logically without merging them physically.

It helps analyze data from multiple tables easily and avoids duplication.

Remember to select correct matching fields and set cardinality properly.

Practice

1. What is the main purpose of using data relationships in Tableau?

easy

A. To delete unrelated tables automatically

B. To permanently combine tables into one

C. To create duplicate copies of data

D. To connect tables without merging them immediately

Solution

1. Step 1: Understand what data relationships do

   Data relationships link tables by matching key fields but keep tables separate until analysis.

2. Step 2: Compare with other options

   Options A, B, and C describe deleting, permanently combining, or duplicating, which are not the purpose of relationships.

3. Final Answer:

   To connect tables without merging them immediately -> Option D

4. Quick Check:

   Relationships connect tables without merging [OK]

Hint: Relationships link tables without merging data [OK]

Common Mistakes:

• Confusing relationships with joins
• Thinking relationships merge tables immediately
• Assuming relationships duplicate data

2. Which of the following is the correct way to create a relationship between two tables in Tableau?

easy

A. Write a SQL JOIN statement manually

B. Drag a field from one table to the matching field in another table

C. Copy data from one table and paste into another

D. Use the Data Interpreter to merge tables

Solution

1. Step 1: Identify how Tableau creates relationships

   Tableau allows creating relationships by dragging a key field from one table to the matching field in another.

2. Step 2: Eliminate incorrect methods

   Options A, C, and D describe manual SQL, copying data, or using Data Interpreter, which are not how relationships are created.

3. Final Answer:

   Drag a field from one table to the matching field in another table -> Option B

4. Quick Check:

   Drag matching fields to create relationships [OK]

Hint: Drag matching keys between tables to create relationships [OK]

Common Mistakes:

• Trying to write SQL JOINs instead of using drag-and-drop
• Copy-pasting data instead of linking tables
• Confusing Data Interpreter with relationships

3. Given two tables: Orders with fields OrderID, CustomerID and Customers with fields CustomerID, CustomerName, what will happen if you create a relationship on CustomerID and then create a view showing CustomerName and count of OrderID?

medium

A. The view shows each customer with the number of their orders

B. The view shows all orders without customer names

C. The view shows customer names but no order counts

D. The view causes an error due to missing join

Solution

1. Step 1: Understand relationship on CustomerID

   Relationship links Orders and Customers on CustomerID, allowing data from both tables to combine logically.

2. Step 2: Analyze the view with CustomerName and count(OrderID)

   Tableau aggregates orders per customer, showing customer names with their order counts.

3. Final Answer:

   The view shows each customer with the number of their orders -> Option A

4. Quick Check:

   Relationship on CustomerID aggregates orders by customer [OK]

Hint: Relationship on key fields enables combined aggregation [OK]

Common Mistakes:

• Expecting no aggregation without explicit join
• Thinking relationship causes errors
• Assuming customer names won't appear without join

4. You created a relationship between two tables on ProductID, but your view shows incorrect totals. What is the most likely cause?

medium

A. The relationship was created on non-matching fields

B. The tables are physically merged instead of related

C. You forgot to refresh the data source

D. The relationship uses fields with different data types

Solution

1. Step 1: Check the fields used in the relationship

   If the relationship is created on fields that do not match correctly, the data will not combine as expected, causing wrong totals.

2. Step 2: Evaluate other options

   Data type mismatch usually prevents relationship creation; physical merge creates a single table instead of relating; refreshing rarely fixes relationship logic errors.

3. Final Answer:

   The relationship was created on non-matching fields -> Option A

4. Quick Check:

   Incorrect totals often mean wrong relationship fields [OK]

Hint: Verify relationship fields match exactly to fix totals [OK]

Common Mistakes:

• Ignoring field mismatches in relationships
• Assuming refresh fixes relationship logic
• Confusing relationships with joins or merges

5. You have three tables: Sales (with SaleID, ProductID, DateID), Products (with ProductID, ProductName), and Dates (with DateID, Date). How should you set up relationships to analyze total sales by product name and date?

hard

A. Create a relationship from Products to Dates directly

B. Join all three tables into one large table before analysis

C. Create relationships from Sales to Products on ProductID and from Sales to Dates on DateID

D. Create relationships from Products to Sales on ProductName and from Dates to Sales on Date

Solution

1. Step 1: Identify keys for relationships

   Sales table contains foreign keys ProductID and DateID linking to Products and Dates tables respectively.

2. Step 2: Set relationships on matching keys

   Create relationships from Sales to Products on ProductID and from Sales to Dates on DateID to enable combined analysis.

3. Step 3: Eliminate incorrect options

   Joining all tables into one is not necessary; relating Products to Dates directly lacks a direct key; using ProductName and Date for relationships are incorrect keys.

4. Final Answer:

   Create relationships from Sales to Products on ProductID and from Sales to Dates on DateID -> Option C

5. Quick Check:

   Relationships use foreign keys from fact to dimension tables [OK]

Hint: Link fact table keys to dimension tables for analysis [OK]

Common Mistakes:

• Trying to relate dimension tables directly without fact table
• Using descriptive fields instead of keys for relationships
• Joining tables unnecessarily instead of relating