Data Modelling in Tableau: Relationships, Joins, Unions, Blending, Extracts and Tableau Prep

The two layers of a Tableau data source

When you drag a second table onto the canvas, Tableau draws a relationship (a flexible "noodle") and asks for the matching fields. Tableau then generates the right join for each sheet, using only the tables that sheet needs and aggregating each table at its own level.

The Adventure Cycles star schema

The same star schema used in the Power BI lessons: one fact table of 9,000 sales lines and four dimension tables.

import pandas as pd
base = "adventure_cycles/"
fact = pd.read_csv(base + "fact_sales.csv")
dims = {n: pd.read_csv(base + f"dim_{n}.csv") for n in ["product", "territory", "customer", "date"]}
print("fact_sales:", fact.shape, list(fact.columns))
for n, d in dims.items(): print(f"dim_{n}:", d.shape, list(d.columns)[:6])

fact_sales: (9000, 9) ['order_number', 'order_date', 'product_key', 'customer_key', 'territory_key', 'quantity', 'unit_price', 'sales_amount', 'total_cost']
dim_product: (45, 6) ['product_key', 'product', 'subcategory', 'category', 'list_price', 'standard_cost']
dim_territory: (6, 3) ['territory_key', 'territory', 'country']
dim_customer: (1200, 5) ['customer_key', 'customer_name', 'gender', 'age_band', 'territory_key']
dim_date: (1096, 6) ['date', 'year', 'quarter', 'month_no', 'month', 'weekday']

Why joins can inflate totals

A join merges tables before aggregation. If a table on one side has several matching rows, the other side's rows are repeated, and SUM counts them more than once. Relationships avoid this because each table is aggregated at its own level before combining.

# A targets table at territory + year level (one row per territory per year), related to line-level sales
fact["year"] = pd.to_datetime(fact.order_date).dt.year
targets = fact[["territory_key", "year"]].drop_duplicates().assign(target=10_00_00_000)   # ₹10 crore each
joined = fact.merge(targets, on=["territory_key", "year"])                                 # physical join at line level
print("territories x years:", len(targets), "| sales lines:", len(fact))
print("true total target: ₹", f"{targets.target.sum():,}")
print("SUM(target) after a row-level join: ₹", f"{joined.target.sum():,}")
print("inflation factor:", round(joined.target.sum() / targets.target.sum()), "x")

territories x years: 18 | sales lines: 9000
true total target: ₹ 1,800,000,000
SUM(target) after a row-level join: ₹ 900,000,000,000
inflation factor: 500 x

The join repeats each territory's target once per sales line, so SUM(target) is wildly overstated. With a relationship between the sales table and a targets table on territory and year, Tableau aggregates targets at their own level and the total is correct.

Relationships, joins, unions and blends

Cleaning on the Data Source page

wide = pd.DataFrame({"region": ["East", "North"], "Jan": [120, 140], "Feb": [130, 150], "Mar": [125, 160]})
long = wide.melt(id_vars="region", var_name="month", value_name="sales")   # Tableau Pivot
print(wide, "\n"); print(long)

  region  Jan  Feb  Mar
0   East  120  130  125
1  North  140  150  160 

  region month  sales
0   East   Jan    120
1  North   Jan    140
2   East   Feb    130
3  North   Feb    150
4   East   Mar    125
5  North   Mar    160

Live connection vs extract

Extract options: keep only needed fields (Hide all unused fields), add extract filters, aggregate to visible dimensions, and use incremental refresh on a date or ID column for large tables.

Tableau Prep Builder

Prep is a separate app for repeatable cleaning flows. A flow is a left-to-right chain of steps:

Useful steps: Clean (profile pane shows each field's distribution; fix types, trim spaces, Group and Replace to merge spelling variants such as "Bengaluru"/"Bangalore"), Aggregate, Pivot (columns to rows and back), Join (with a join-clause summary that shows unmatched rows), Union, New Rows (fill missing dates) and Script (Python/R). Outputs can be scheduled with Tableau Prep Conductor on Cloud/Server.

Summary