2.2 Data Relationships, Joins, Unions & Data Interpreter

2.2 Data Relationships, Joins, Unions & Data Interpreter

Combining data from multiple tables or files is a core step in data preparation. Tableau provides a multi-layered data modeling architecture: the Logical Layer (using Relationships) and the Physical Layer (using Joins and Unions). Additionally, Tableau’s built-in Data Interpreter tool automates the cleanup of messy spreadsheets before modeling begins.

---

The Tableau Data Model: Logical vs. Physical Layer

The Tableau Data Source canvas is divided into two distinct layers that govern how data is structured and queried:

1. The Logical Layer (Top Layer)

When you drag tables onto the default Data Source canvas, you are working in the Logical Layer. Tables in this layer are connected by flexible lines commonly called "Noodles", representing Relationships.

• Tables remain separate and distinct independent entities.
• Raw data rows are not merged at the data source level.
• Tableau constructs dynamic queries at viz time, generating the exact type of SQL join required based specifically on which dimensions and measures are placed in the view.

2. The Physical Layer (Lower Layer)

Double-clicking any logical table opens its underlying Physical Layer. Here, you perform traditional relational data integration:

• Joins (Inner, Left, Right, Full Outer) combine tables horizontally across shared key columns.
• Unions combine tables vertically by appending rows.
• Physical layer operations produce a single, flattened physical table before aggregations occur in worksheets.

---

Relationships (Logical Layer)

Relationships are the recommended, default method for combining data across multiple tables in Tableau.

How Relationships Work

Unlike physical joins, relationships do not require you to select a join type (such as Inner or Left). Instead, you define matching field pairs between logical tables (e.g., Orders.Customer_ID = Customers.Customer_ID). Tableau defers join execution until you build a visualization.

Primary Advantages of Relationships

1. Preserves Native Grain (No Data Duplication): Suppose you have an Orders table (line-item detail level) and a Sales_Target table (monthly level). A physical join would duplicate the monthly target across every order line item, leading to inflated sums. Relationships maintain each table at its independent level of detail, aggregating Sales_Target correctly at the month level while calculating Orders at the line-item level.

2. Context-Aware Smart Queries: If a worksheet only uses fields from the Orders table, Tableau queries only the Orders table. The Returns table is completely ignored, dramatically improving workbook execution speed.

3. Handles Nulls and Unmatched Records Gracefully: In relationships, unmatched rows are not discarded (unlike physical Inner Joins). Tableau retains all records across logical tables and displays measure values accurately without requiring explicit outer joins.

Performance Options

Analysts can fine-tune relationship query generation in the Performance Options pane by setting:

• Cardinality: Many-to-Many, One-to-One, or One-to-Many. (Default is Many-to-Many).
• Referential Integrity: Some Records Match or All Records Match. Setting this to All Records Match informs Tableau that every record in the source table has a matching key in the target table, allowing Tableau to optimize generated SQL queries by eliminating unnecessary join checks.

---

Joins (Physical Layer)

When you need to force a fixed, row-level merge across tables regardless of viz context, use Physical Joins.

Join Types Breakdown

• Inner Join: Returns only the rows where the join condition matches in both left and right tables. Non-matching records from both sides are dropped entirely.
• Left Join: Returns all rows from the left table, along with matching rows from the right table. Where no match exists in the right table, Null values are populated.
• Right Join: Returns all rows from the right table, plus matching rows from the left table. Unmatched left values are populated with Null.
• Full Outer Join: Returns all records from both tables. Matches are combined on a single row; unmatched records from either side are retained with Null fields.

WARNING: Physical joins can lead to row duplication when joining tables of differing grain sizes (e.g., a one-to-many relationship). Always inspect mark counts when using physical joins.

---

Unions (Physical Layer)

While joins combine tables horizontally (adding columns), Unions combine data vertically (appending rows).

Requirements for Unions

• Data sources must be from the same connection (e.g., multiple tabs in an Excel workbook or multiple CSV files in a single folder).
• Tables should ideally have matching column names and data types.

Manual vs. Wildcard Unions

• Manual Union: Drag individual tables directly under an existing table in the Physical Layer canvas.
• Wildcard Union: Define a search pattern (e.g., Sales_202*.csv or sheet names matching Region_*) to automatically search directories and append matching files or tabs into a single unified table.

AUTOMATIC METADATA FIELDS: When a Union is performed, Tableau automatically creates two system metadata columns: Table Name and Sheet Name. These fields allow analysts to identify the exact source file or tab for every appended row.

---

Tableau Data Interpreter

Spreadsheet files created in Excel often contain human-friendly formatting that confuses database parsers—such as multi-line title headers, merged header cells, blank rows, and notes footers.

How Data Interpreter Works

1. Connect to an Excel or CSV file in Tableau Desktop.
2. In the left pane of the Data Source tab, check the box labeled Use Data Interpreter.
3. Data Interpreter scans the file, detects header rows, unmerges merged cells, strips out top title banners, and ignores footer calculations.
4. Click Review the results to open an annotated Excel file highlighting exactly which cells were parsed as data tables (green), header labels (yellow), or excluded formatting (grey).