Module 2: The Star Schema And Conformed Dimensions

The bus matrix: mapping Kiosko's processes

Next →

Description

The previous lesson compared two processes at once — orders and sessions — and found that dim_store and dim_date are conformed between them. This lesson generalizes that comparison into a single tool: the bus matrix, a table with business processes in the rows and dimensions in the columns, marking with an X every combination that exists. It's, literally, the complete map of a dimensional warehouse, and — this is the important part — it gets built before writing the SQL for each new table, not after.

Connection to the module. This lesson runs no JOIN against real data — the bus matrix is a planning artifact, not a query. What it produces is the map lesson 7 is going to confirm with code: which dimensions fact_orders needs for its final assembly.

An analogy: the bus route map

The name "bus matrix" isn't a vague metaphor — Kimball took it, quite deliberately, from a building's or a computer's electrical system: a bus is a shared line that information or power travels along, accessible from several points without needing a dedicated connection for each one. The everyday analogy is even simpler: think of a city's bus route map. That map doesn't tell you "how to get from A to B" for a specific trip — it tells you, at a glance, which route passes through which station. Someone planning any future trip, without yet knowing which one they'll take, can look at the map and see that Route 5 passes through the Centro, Norte, and Terminal stations, while Route 12 only passes through Centro and Sur.

A warehouse's bus matrix is exactly that map, with business processes instead of routes and dimensions instead of stations: it tells you, at a glance and before building anything, which dimension (station) serves which process (route). A data team that draws this map before writing a new process's first line of SQL systematically avoids the mistake the previous lesson named: creating a duplicate dimension because nobody stopped to ask "does this already exist somewhere else on the map?"

Worked example: Kiosko's bus matrix, today and what's coming

The bus matrix is represented here as a fixed Python data structure — not as a SQL query, because it doesn't describe data, it describes design decisions — and printed as a text table.

# bus_matrix.py
# Kiosko's bus matrix: rows = business processes, columns = dimensions.
# It's not a JOIN, it's not SQL run against data -- it's a planning table,
# the same kind of artifact Kimball describes for mapping an ENTIRE warehouse
# before building each piece. It's represented here as a fixed, deterministic
# Python data structure, and printed as a table.

BUS_MATRIX = [
    # (business_process, fact_table, status, {dim_store, dim_product, dim_date})
    ("orders",         "fact_orders",         "built (M1-M2)", {"dim_store", "dim_product", "dim_date"}),
    ("sessions",       "fact_sessions",       "planned (M6)",      {"dim_store", "dim_date"}),
    ("store_activity", "fact_store_activity", "planned (M6)",      {"dim_store", "dim_date"}),
]

DIMENSIONS = ["dim_store", "dim_product", "dim_date"]

header = f"{'business_process':16} | {'fact_table':20} | " + " | ".join(f"{d:11}" for d in DIMENSIONS) + " | status"
print(header)
print("-" * len(header))
for process, fact_table, status, dims in BUS_MATRIX:
    marks = " | ".join(f"{'X':11}" if d in dims else f"{'.':11}" for d in DIMENSIONS)
    print(f"{process:16} | {fact_table:20} | {marks} | {status}")

print()
for dimension in DIMENSIONS:
    processes_using_it = [process for process, _, _, dims in BUS_MATRIX if dimension in dims]
    conformed = len(processes_using_it) > 1
    label = "CONFORMED" if conformed else "specific to one process"
    print(f"{dimension:12} -> used by {processes_using_it} [{label}]")

What to expect. Running python3 bus_matrix.py, the output is exactly this:

business_process | fact_table           | dim_store   | dim_product | dim_date    | status
------------------------------------------------------------------------------------------
orders           | fact_orders          | X           | X           | X           | built (M1-M2)
sessions         | fact_sessions        | X           | .           | X           | planned (M6)
store_activity   | fact_store_activity  | X           | .           | X           | planned (M6)

dim_store    -> used by ['orders', 'sessions', 'store_activity'] [CONFORMED]
dim_product  -> used by ['orders'] [specific to one process]
dim_date     -> used by ['orders', 'sessions', 'store_activity'] [CONFORMED]

This map says, at a glance, something that would be far harder to see reading three separate fact tables' code: dim_store and dim_date are the two "bus routes that pass through every station" — every Kiosko business process, present and planned, needs them — while dim_product is still a "special route," serving only the sales process. Notice this map includes two processes that don't exist yet (sessions and store_activity, marked "planned (M6)") — and that is, precisely, the bus matrix's real usefulness: it gets built with the whole warehouse in mind, not just what's already built.

Diagram: the same map, as a bipartite graph

flowchart LR
    subgraph Procesos["Business processes (rows)"]
        P1["orders\nbuilt"]
        P2["sessions\nplanned M6"]
        P3["store_activity\nplanned M6"]
    end

    subgraph Dims["Dimensions (columns)"]
        D1["dim_store"]
        D2["dim_product"]
        D3["dim_date"]
    end

    P1 --> D1
    P1 --> D2
    P1 --> D3
    P2 --> D1
    P2 --> D3
    P3 --> D1
    P3 --> D3

Going deeper: the bus matrix is a team planning tool, not just a code artifact

It's worth being explicit about something: on a real data team, the bus matrix is almost never built by one person looking at code — it gets drawn in a joint working session, with business analysts, before a single table exists, precisely to decide what to build first and in what order. Kimball recommends building the warehouse's complete bus matrix — with all known business processes, even ones implemented much later — as one of the first steps of any serious dimensional modeling project, not as a late afterthought.

This explains a decision in the worked example that might seem odd at first glance: why include sessions and store_activity, if they don't exist yet? Because the map's value lies precisely in anticipating reuse before building. If Kiosko's team had built fact_orders without ever thinking about sessions, it's entirely possible that, upon reaching module 6, someone would have created a brand-new dim_store from scratch for that process — the exact mistake the previous lesson warned about — simply from not having consulted a map that already existed. The bus matrix, built in advance, prevents that mistake structurally, not through the individual discipline of whoever builds each new table.

A note about this guide's scope: a real production warehouse's complete bus matrix can have dozens of processes and dozens of dimensions — the examples in Kimball's The Data Warehouse Toolkit, for a real retail chain, easily exceed fifteen processes. Kiosko's, with three processes and three dimensions, is intentionally small: this guide's goal isn't to simulate a production warehouse's scale, but for you to understand the tool's shape with a case you can verify from memory, line by line.

Common mistakes

Building the bus matrix only with processes that already exist. What happens: someone builds the map with only orders, because it's the only process that currently has a real fact table, and leaves out sessions and store_activity because "they don't exist yet." Why it happens: it feels more "honest" to document only what's already built, instead of something that's still a plan. How to spot it: if your bus matrix changes every time a new process gets built, instead of simply updating a row that was already anticipated, the map failed at its job of anticipation. How to fix it: the bus matrix gets built with the warehouse's complete vision, explicitly marking what's built and what's planned (as this lesson's status column does) — the goal is to anticipate dimension reuse, not to retroactively document what already exists.

Confusing "the bus matrix is complete" with "every dimension must serve every process." What happens: someone, seeing that dim_product only has one X on the map, feels the design is "incomplete" or "unbalanced," and looks for a way to force dim_product to also serve sessions or store_activity. Why it happens: a map with more Xs feels, visually, more "used." How to spot it: if you're inventing an artificial reason for a process to use a dimension its real grain doesn't need, the problem isn't the bus matrix — it's that you're optimizing the map's appearance instead of the real business model. How to fix it: an "unbalanced" bus matrix — with some dimensions heavily conformed and others specific to a single process — is completely normal and expected. dim_date almost always ends up being the most conformed dimension in any warehouse, precisely because almost every business process happens over time; that doesn't mean the other dimensions are poorly designed.

Treating the bus matrix as a document written once and never updated. What happens: a team draws the bus matrix at the start of a project, saves it, and never consults or updates it again as the warehouse grows with new processes nobody originally anticipated. Why it happens: once the map served its initial purpose, it feels "finished," like any design document. How to spot it: if a new business process got built without anyone first checking whether some existing dimension already covered it, the bus matrix stopped being used as an active tool. How to fix it: the bus matrix is a living document — every time a new process enters the warehouse (as sessions and store_activity are going to enter in this guide's module 6), the first question before writing any CREATE TABLE should be "what does the bus matrix say about the dimensions this process needs?"

Exercises

Exercise 1 — Add a hypothetical fourth process to the bus matrix. Imagine Kiosko decides, in the future, to measure "product returns" as a new business process (fact_returns), which would need dim_store, dim_product, and dim_date — all three dimensions. Add it to BUS_MATRIX and print the complete map again.

See solution 
BUS_MATRIX.append(
    ("returns", "fact_returns", "hypothetical (outside this guide)", {"dim_store", "dim_product", "dim_date"})
)

header = f"{'business_process':16} | {'fact_table':20} | " + " | ".join(f"{d:11}" for d in DIMENSIONS) + " | status"
print(header)
print("-" * len(header))
for process, fact_table, status, dims in BUS_MATRIX:
    marks = " | ".join(f"{'X':11}" if d in dims else f"{'.':11}" for d in DIMENSIONS)
    print(f"{process:16} | {fact_table:20} | {marks} | {status}")

Expected output:

business_process | fact_table           | dim_store   | dim_product | dim_date    | status
------------------------------------------------------------------------------------------
orders           | fact_orders          | X           | X           | X           | built (M1-M2)
sessions         | fact_sessions        | X           | .           | X           | planned (M6)
store_activity   | fact_store_activity  | X           | .           | X           | planned (M6)
returns          | fact_returns         | X           | X           | X           | hypothetical (outside this guide)

With returns added, dim_product stops being exclusive to orders — now two processes share it, and it would technically already qualify as conformed. This exercise is purely hypothetical — this guide doesn't build fact_returns in any module — but it shows how the map naturally updates as a real warehouse grows with more business processes.

Exercise 2 — Calculate which process uses the MOST dimensions. Using the worked example's BUS_MATRIX (without exercise 1's hypothetical process), write Python code that identifies which of the three current processes uses the largest number of dimensions.

See solution 
most_dimensions = max(BUS_MATRIX, key=lambda row: len(row[3]))
print(f"Process with the most dimensions: {most_dimensions[0]} ({len(most_dimensions[3])} dimensions)")

Expected output:

Process with the most dimensions: orders (3 dimensions)

orders uses all three dimensions (dim_store, dim_product, dim_date), while sessions and store_activity each use only two. This lines up with what you already know about each process's grain: fact_orders describes a specific sale of a specific product — it needs dim_product — while fact_sessions and fact_store_activity describe aggregated behavior by store and by date, without descending to the individual product level.

Exercise 3 — Explain, without code, why dim_date almost always ends up being any warehouse's most conformed dimension. In 2-3 sentences, using what you know about Kiosko's three processes (existing and planned), explain why the calendar tends to show up in almost every business process a company has, far more than any other dimension.

See solution

Almost any business event happens at a specific moment in time — a sale, a browsing session, a daily activity close-out — so almost any process needs, one way or another, to anchor itself to a date to be analyzed by day, week, or month. On the other hand, not every process needs to describe a specific product (as you saw with fact_sessions and fact_store_activity, which don't need it) or even a specific store (in a business different from Kiosko, there could be fully digital processes with no physical location at all). Time is, in that sense, the most universal dimension there is — the reason Kimball treats it as the first conformed dimension almost any warehouse builds, well before any other.

Summary and next step

In this lesson you built Kiosko's bus matrix: a planning table — business processes in rows, dimensions in columns — that confirms, at a glance, what the previous lesson already found comparing a pair of processes: dim_store and dim_date are conformed across Kiosko's three processes (one built, two planned for module 6), while dim_product remains specific to the sales process. You learned that this tool gets built before writing code, with the warehouse's complete vision in mind, precisely to avoid the mistake of duplicating a dimension that already exists in another process.

Before moving on you should be able to: draw Kiosko's bus matrix from memory with its three processes and three dimensions; explain why the name "bus matrix" comes from a shared electrical system, not a vehicle; and name the dimension that, in almost any warehouse, ends up being the most conformed of all.

With all four tables built — fact_orders, dim_store, dim_product, dim_date — and the complete map of how they relate, lesson 7 does what this entire module has been building toward: assembling Kiosko's first real star, with all three JOINs actually executed, and verifying that none of them loses or duplicates a single row.

Resources

← PreviousConformed dimensions across processesNext →Assembling Kiosko's first real star