Conformed Dimensions and the Kimball Bus Matrix, With a Worked Example

A single star schema answers questions about one business process. Real questions cross processes: "What share of last month's revenue was returned?", "Which marketing campaigns brought in customers who opened support tickets?", "How does loan origination volume by branch compare with deposit growth?" To answer them, separate fact tables must share the same dimensions, with the same keys, the same attribute names and the same values. Those shared dimensions are conformed dimensions, and the planning tool that organises them is the enterprise bus matrix.

Together they are the core of the Kimball approach to building an enterprise data warehouse incrementally, one business process at a time, without ending up with a set of disconnected data marts.

What makes a dimension conformed

Two fact tables use a conformed dimension when the dimension means the same thing to both. In practice that means:

• Same keys. fct_order_line.customer_key = 2217 and fct_support_ticket.customer_key = 2217 refer to the same customer version.
• Same attribute names and definitions. segment means the same thing in both, with the same allowed values.
• Same content. The set of customers and their attributes come from one build of dim_customer, not two pipelines that happen to look similar.

The simplest form is literally one physical table referenced by both facts. Conformance also allows two other forms.

Shrunken (rolled-up) dimensions

A fact at a higher grain cannot use the detailed dimension. A monthly sales target is set per category per month, not per product per day. It needs dim_category and a month-level calendar. These are shrunken dimensions: subsets of the columns of the base dimension, with identical values.

create view dim_category asselect distinct    category_key,        -- conformed key, carried on dim_product and generated once    category_name,    department_namefrom dim_product;​create view dim_month asselect distinct    year_month,    month_number,    month_name,    quarter_number,    year_numberfrom dim_date;

Because category_name comes from the same source as on dim_product, a report can put actual sales (from fct_sales, rolled up) next to targets (from fct_category_target) and the categories line up.

Row subsets

A dimension can also be conformed when one fact uses only some of its rows. A fct_loan_payment might only ever reference loan accounts in dim_account, while fct_deposit_balance uses only deposit accounts. Both still reference the same dim_account.

Drilling across

The payoff of conformance is drilling across: querying two facts separately at a common grain and joining the results on conformed attributes.

with sales as (    select d.year_month, p.category_name, sum(f.net_amount) as revenue    from fct_order_line f    join dim_date d    on d.date_key = f.order_date_key    join dim_product p on p.product_key = f.product_key    group by 1, 2),returns as (    select d.year_month, p.category_name, sum(r.refund_amount) as refunds    from fct_return_line r    join dim_date d    on d.date_key = r.return_date_key    join dim_product p on p.product_key = r.product_key    group by 1, 2)select    s.year_month,    s.category_name,    s.revenue,    coalesce(r.refunds, 0)                    as refunds,    coalesce(r.refunds, 0) / nullif(s.revenue, 0) as refund_ratefrom sales sleft join returns r  on r.year_month = s.year_month and r.category_name = s.category_name;

Notice the facts are never joined row to row. Joining fct_order_line directly to fct_return_line on product_key would fan out and multiply amounts. Aggregate each fact to the shared grain first, then join. Semantic layers and BI tools implement exactly this pattern when a query spans two facts, and it only works when the dimensions are conformed.

The enterprise bus matrix

The bus matrix is a grid. Rows are business processes, each of which becomes one or more fact tables. Columns are dimensions. A mark in a cell means that process uses that dimension.

It sounds simple, and it is, but it does three important jobs:

1. It shows which dimensions are shared and therefore must be conformed before the second process is built.
2. It gives a roadmap: build processes one at a time, reusing conformed dimensions as you go.
3. It is a communication tool. Business stakeholders can read it and correct it.

Step 1: List business processes

Work with the business to list operational processes that generate measurable events. Use verbs, not departments. "Marketing" is not a process; "sending campaign emails" and "recording ad spend" are.

For a mid-size e-commerce business:

• Ad spend
• Website sessions
• Orders
• Payments
• Shipments
• Returns
• Inventory (daily stock levels)
• Support tickets
• Subscriptions (a membership programme)

Step 2: List candidate dimensions

Collect the nouns that give these processes context: date, customer, product, warehouse, carrier, campaign, channel, payment method, promotion, support agent, return reason.

Step 3: Fill in the grid

Reading the columns tells you which dimensions matter most. Date and customer are used by almost everything, so they must be conformed early and owned centrally. Product and channel follow close behind. Support agent is used by one process, so it can be designed with that process.

Step 4: Add grain and priority

Expand the matrix with the declared grain of each fact and a priority based on business value and feasibility.

Notice that one process can produce more than one fact table. Orders yields both a transaction fact and an accumulating snapshot for fulfilment.

Step 5: Build incrementally

Build the highest-priority process first, along with the dimensions it needs, designed as enterprise dimensions from day one. When you add the second process, it reuses those dimensions and adds only what is new. The matrix tells you upfront that dim_customer must serve eight processes, so you design it for that rather than for orders alone.

Ownership and governance

Conformed dimensions are shared infrastructure, and shared infrastructure needs an owner.

• One build per dimension. There is one pipeline that produces dim_customer. Other teams consume it; they do not fork it.
• Agreed definitions. If marketing and finance define "active customer" differently, settle it once and publish it, or create two clearly named attributes. Do not let the conflict live in two copies of the dimension.
• Stable keys. Surrogate keys in a conformed dimension must not be regenerated on rebuild, or every fact referencing them breaks. See surrogate keys vs natural keys.
• Change control. Adding an attribute is safe. Renaming or redefining one is a breaking change across every process in its column of the matrix.

A banking example

A retail bank's bus matrix might include these processes: account openings, deposits and withdrawals, daily balances, card transactions, loan applications, loan payments, and complaints. Conformed dimensions include date, customer, account, branch, product and channel.

The cross-process questions this enables are the valuable ones: customers who opened a mortgage after a branch visit, complaint rates per thousand accounts by product, deposit balance growth by branch alongside new loan volume. Each requires two or more facts sharing dim_customer, dim_branch or dim_product.

The usual failure in banking is that each product line (cards, mortgages, deposits) has its own customer table with its own identifiers. The bus matrix makes that visible early: one column, many rows, and a conformance problem to solve with customer matching before anything else.

Common mistakes

Department-shaped marts. Building a "marketing mart" and a "finance mart", each with its own customer dimension, produces two answers to every cross-functional question. Organise by process and conform dimensions.

Conforming in name only. Two tables both called dim_product with different keys or category values are not conformed. Conformance is about content, not names.

Joining facts directly. Fact-to-fact joins on a shared dimension key fan out. Drill across by aggregating first.

Overloading the matrix. The matrix is a planning tool, not a full data dictionary. Keep cells as marks; document attributes elsewhere.

Skipping it because the first project is small. The matrix is cheapest to draw before the first fact is built. Retro-fitting conformance onto three marts is expensive.

If you are deciding whether Kimball-style conformed marts or a Data Vault integration layer suits your organisation, see Data Vault vs Kimball.

Practise in offdataai Learn

The Dimensional Modeling (Kimball) track in offdataai Learn includes a bus matrix exercise: you are given a set of business processes, you build the facts and conformed dimensions in the Model Builder, and an instant check confirms that the dimensions are shared correctly and each fact's grain holds. It is hands-on and included in the Explorer plan at $1/month.

The industry templates are also useful references: each one is a multi-process model built on conformed dimensions. The dimensional modeling page has an overview.