Tableau Relationships
Learn how Tableau connects multiple tables using logical relationships while preserving each table's level of detail.
1. What are Tableau Relationships?
Tableau Relationships are a way to connect tables from different data sources or from the same data source without physically merging them into a single table.
Relationships are created in Tableau's logical layer. Each table remains an independent logical table. Tableau determines how to query the related tables based on the fields used in a visualization.
Key Characteristics
- Tables remain separate in the logical data model.
- Relationships are based on matching fields.
- Tableau generates context-aware queries during analysis.
- Different levels of detail can be preserved.
- Relationship behavior depends on the fields used in the view.
2. Understanding the Logical Layer
The logical layer is the top-level data model in Tableau. When you create a relationship, you connect logical tables without combining their physical rows into one table.
Logical tables stay separate and are related through a common field.
3. Example: Customers and Orders
Consider a business database containing two tables: Customers and Orders. Both tables contain the Customer ID field.
Customers Table
| Customer ID | Customer Name | City |
|---|---|---|
| 101 | Rahul | Pune |
| 102 | Priya | Mumbai |
| 103 | Amit | Nashik |
Orders Table
| Order ID | Customer ID | Sales |
|---|---|---|
| 5001 | 101 | ₹5,000 |
| 5002 | 101 | ₹3,000 |
| 5003 | 102 | ₹7,000 |
| 5004 | 103 | ₹2,000 |
Relationship Configuration
First table: Customers
Second table: Orders
Matching field: Customer ID
Typical cardinality: One-to-Many
One customer can have multiple orders, while each order normally belongs to one customer.
One customer can be associated with multiple order records.
4. Cardinality in Relationships
Cardinality describes how records in one table relate to records in another table. Tableau provides One and Many settings for each side of a relationship.
| Cardinality | Meaning | Example |
|---|---|---|
| One-to-One | One record matches one record. | Employee and Employee Profile |
| One-to-Many | One record can match multiple records. | Customer and Orders |
| Many-to-Many | Multiple records can match multiple records. | Students and Courses |
5. Referential Integrity
Referential integrity describes whether records in one table are expected to have matching records in the related table.
All Records Match
Use this setting when every record in one table is expected to have a corresponding record in the related table.
Example: Every order is expected to reference an existing customer.
Some Records Match
Use this setting when some records may not have a matching record in the related table.
Example: A customer table may contain customers who have not placed any orders.
Some customers may have no matching order records.
The correct setting depends on the data and the assumptions you can safely make. Available relationship options can also depend on the connector and data source.
6. Relationships vs Physical Joins
Relationships and joins both connect tables, but they work at different levels of Tableau's data model.
| Feature | Relationships | Physical Joins |
|---|---|---|
| Data model layer | Logical layer | Physical layer |
| Table behavior | Tables remain separate logical tables. | Rows are combined into one logical table. |
| Query behavior | Tableau generates context-aware queries. | Join is defined in the physical data model. |
| Level of detail | Can preserve different levels of detail. | Can change row-level detail through the join. |
| Common use | Connecting tables for flexible analysis. | When a single combined table is required. |
Relationships connect logical tables; physical joins combine rows.
7. How to Create a Relationship in Tableau
- Open Tableau Desktop.
- Connect to your database or data source.
- Open the Data Source page.
- Drag the Customers table onto the logical canvas.
- Drag the Orders table next to the Customers table.
- Tableau creates a relationship between the tables.
- Click the relationship line to configure matching fields.
- Select Customer ID as the matching field in both tables.
- Review cardinality and referential integrity settings.
- Open a worksheet and use fields from both tables.
Configuration Example
Table 1: Customers
Matching Field: Customer ID
Table 2: Orders
Matching Field: Customer ID
Cardinality:
Customers = One
Orders = Many
Relationship:
Customers.Customer ID = Orders.Customer ID
8. Common Issues and Best Practices
Common Issues
- Incorrect matching fields: Selecting the wrong fields can produce unexpected analysis results.
- Different data types: Matching fields should have compatible data types.
- Duplicate identifiers: Duplicate values can affect the expected cardinality.
- Unmatched records: Missing matching records can affect which values appear in a view.
- Incorrect relationship settings: Wrong cardinality or referential integrity assumptions can affect query optimization and aggregation behavior.
Best Practices
- Understand the grain of each table before creating relationships.
- Use the correct matching fields.
- Check for null and duplicate key values.
- Set cardinality based on actual data characteristics.
- Choose referential integrity settings carefully.
- Validate totals and aggregations in worksheets.
- Use relationships for flexible analysis across different levels of detail.
Summary
Tableau Relationships connect tables in the logical layer while keeping them as separate logical tables. Tableau generates context-aware queries during analysis based on the fields used in a visualization. Cardinality describes how records relate between tables, while referential integrity describes whether matching records are expected. Unlike physical joins, relationships do not directly combine table rows into one logical table. Choosing the correct relationship settings and validating your data helps maintain accurate analysis and aggregations.