Conformed dimensions, the bus matrix, role-playing dimensions and reference data

How do many teams make their numbers comparable without N² private translations?

Why conformance is a coordination problem

This is where coordination cost arrives as a first-class quantity.

Two fact tables can be compared along a dimension only if that dimension means the same thing in both: the same key domain (the same set of identifiers), the same attribute definitions, and the same member population (the same list of customers, the same list of products). That agreement is called conformance, and a conformed dimension is its physical result: one shared dim_customer that every fact table points to.

The economics

The economics are the strongest argument in this mechanism, so we write them out.

Suppose Fernwood has N fact tables (one per business process: orders, shipments, returns, support tickets, web sessions...). Suppose no dimensions are shared.

To answer any question that crosses two processes, someone must build a mapping between the two processes' versions of an entity (for example, "customer" in orders and "customer" in tickets).
Over all pairs of tables that is N(N−1)/2 reconciliations, which is O(N²). Each is built by hand, each is re-derived by whoever asks.

With conformance, each fact table agrees once with the shared dimension. That is N agreements, which is O(N).

N (fact tables)	Pairwise reconciliations N(N−1)/2	Agreements with a shared dimension (N)
5	10	5
8	28	8
20	190	20
50	1,225	50

RelationshipPairwise coordination against a shared hub →

N (N - 1) /2 vs N

$N, M$: teams, systems or record sources that must agree

pairwiseshared vocabulary

Data table

Agreements to maintain, by parties
Parties N	series	Agreements
2	pairwise	1
2.1	pairwise	1.16
2.21	pairwise	1.33
2.32	pairwise	1.52
2.43	pairwise	1.74
2.55	pairwise	1.98
2.68	pairwise	2.26
2.82	pairwise	2.56
2.96	pairwise	2.89
3.11	pairwise	3.27
3.26	pairwise	3.69
3.42	pairwise	4.15
3.6	pairwise	4.67
3.78	pairwise	5.24
3.97	pairwise	5.88
4.16	pairwise	6.59
4.37	pairwise	7.38
4.59	pairwise	8.25
4.82	pairwise	9.22
5.06	pairwise	10.3
5.32	pairwise	11.5
5.58	pairwise	12.8
5.86	pairwise	14.3
6.16	pairwise	15.9
6.47	pairwise	17.7
6.79	pairwise	19.7
7.13	pairwise	21.9
7.49	pairwise	24.3
7.86	pairwise	27
8.26	pairwise	30
8.67	pairwise	33.3
9.11	pairwise	36.9
9.56	pairwise	40.9
10	pairwise	45.4
10.5	pairwise	50.3
11.1	pairwise	55.8
11.6	pairwise	61.8
12.2	pairwise	68.5
12.8	pairwise	75.8
13.5	pairwise	83.9
14.1	pairwise	92.9
14.9	pairwise	103
15.6	pairwise	114
16.4	pairwise	126
17.2	pairwise	139
18.1	pairwise	154
19	pairwise	170
19.9	pairwise	188
20.9	pairwise	208
22	pairwise	230
23.1	pairwise	254
24.2	pairwise	281
25.4	pairwise	311
26.7	pairwise	343
28	pairwise	379
29.4	pairwise	419
30.9	pairwise	463
32.5	pairwise	511
34.1	pairwise	564
35.8	pairwise	623
37.6	pairwise	688
39.5	pairwise	760
41.5	pairwise	839
43.5	pairwise	926
45.7	pairwise	1.02K
48	pairwise	1.13K
50.4	pairwise	1.25K
53	pairwise	1.38K
55.6	pairwise	1.52K
58.4	pairwise	1.68K
61.3	pairwise	1.85K
64.4	pairwise	2.04K
67.6	pairwise	2.25K
71	pairwise	2.49K
74.6	pairwise	2.74K
78.3	pairwise	3.03K
82.2	pairwise	3.34K
86.4	pairwise	3.69K
90.7	pairwise	4.07K
95.2	pairwise	4.49K
100	pairwise	4.95K
2	shared vocabulary	2
2.1	shared vocabulary	2.1
2.21	shared vocabulary	2.21
2.32	shared vocabulary	2.32
2.43	shared vocabulary	2.43
2.55	shared vocabulary	2.55
2.68	shared vocabulary	2.68
2.82	shared vocabulary	2.82
2.96	shared vocabulary	2.96
3.11	shared vocabulary	3.11
3.26	shared vocabulary	3.26
3.42	shared vocabulary	3.42
3.6	shared vocabulary	3.6
3.78	shared vocabulary	3.78
3.97	shared vocabulary	3.97
4.16	shared vocabulary	4.16
4.37	shared vocabulary	4.37
4.59	shared vocabulary	4.59
4.82	shared vocabulary	4.82
5.06	shared vocabulary	5.06
5.32	shared vocabulary	5.32
5.58	shared vocabulary	5.58
5.86	shared vocabulary	5.86
6.16	shared vocabulary	6.16
6.47	shared vocabulary	6.47
6.79	shared vocabulary	6.79
7.13	shared vocabulary	7.13
7.49	shared vocabulary	7.49
7.86	shared vocabulary	7.86
8.26	shared vocabulary	8.26
8.67	shared vocabulary	8.67
9.11	shared vocabulary	9.11
9.56	shared vocabulary	9.56
10	shared vocabulary	10
10.5	shared vocabulary	10.5
11.1	shared vocabulary	11.1
11.6	shared vocabulary	11.6
12.2	shared vocabulary	12.2
12.8	shared vocabulary	12.8
13.5	shared vocabulary	13.5
14.1	shared vocabulary	14.1
14.9	shared vocabulary	14.9
15.6	shared vocabulary	15.6
16.4	shared vocabulary	16.4
17.2	shared vocabulary	17.2
18.1	shared vocabulary	18.1
19	shared vocabulary	19
19.9	shared vocabulary	19.9
20.9	shared vocabulary	20.9
22	shared vocabulary	22
23.1	shared vocabulary	23.1
24.2	shared vocabulary	24.2
25.4	shared vocabulary	25.4
26.7	shared vocabulary	26.7
28	shared vocabulary	28
29.4	shared vocabulary	29.4
30.9	shared vocabulary	30.9
32.5	shared vocabulary	32.5
34.1	shared vocabulary	34.1
35.8	shared vocabulary	35.8
37.6	shared vocabulary	37.6
39.5	shared vocabulary	39.5
41.5	shared vocabulary	41.5
43.5	shared vocabulary	43.5
45.7	shared vocabulary	45.7
48	shared vocabulary	48
50.4	shared vocabulary	50.4
53	shared vocabulary	53
55.6	shared vocabulary	55.6
58.4	shared vocabulary	58.4
61.3	shared vocabulary	61.3
64.4	shared vocabulary	64.4
67.6	shared vocabulary	67.6
71	shared vocabulary	71
74.6	shared vocabulary	74.6
78.3	shared vocabulary	78.3
82.2	shared vocabulary	82.2
86.4	shared vocabulary	86.4
90.7	shared vocabulary	90.7
95.2	shared vocabulary	95.2
100	shared vocabulary	100

Stated in 8 places →Principle: Coordination scales with parties →

This has the same structure as the argument for a shared interchange data format (such as Apache Arrow, a common in-memory column format) versus writing a converter for every pair of systems. It is the same law: a shared vocabulary turns O(N²) pairwise work into O(N) agreements.

The bus matrix

A bus matrix lists business processes down the side and dimensions across the top. A mark means "this process's fact table uses this dimension".

Process (fact table)	Date	Product	Customer	Warehouse	Region (via customer)
Orders	X	X	X	X	X
Shipments	X	X	X	X	X
Returns	X	X	X	X	X
Support tickets	X		X		X
Web sessions	X	X	X

The bus matrix is not documentation. It is a map of which questions are answerable at all. Each filled cell is a negotiated agreement that someone paid for. An empty cell is a question that will cost a project. In the table above, "support tickets per product" is impossible today, because the tickets table has no product dimension.

Role-playing dimensions

A role-playing dimension is one physical table used in several roles. The calendar is the classic case: an order has an order date, a ship date and an invoice date. There is one dim_date table, but the fact has three foreign keys to it.

The safe way to expose it is three named views, so the column names carry the role:

CREATE VIEW dim_order_date   AS SELECT date_key AS order_date_key,   date AS order_date,   month AS order_month   FROM dim_date;
CREATE VIEW dim_ship_date    AS SELECT date_key AS ship_date_key,    date AS ship_date,    month AS ship_month    FROM dim_date;
CREATE VIEW dim_invoice_date AS SELECT date_key AS invoice_date_key, date AS invoice_date, month AS invoice_month FROM dim_date;

It is cheap to build. The failure mode is a consumer joining on the wrong role and getting a number that is plausible and wrong ("revenue in March" by ship date is not "revenue in March" by invoice date). Naming is the only defence.

Reference data

Reference data means currency codes, country lists, product taxonomies, status codes (also called controlled vocabularies). These are the smallest tables in the platform and have the highest fork rate, exactly because they are small enough to copy instead of asking the owner. Every fork is a silent divergence. Months later it appears as two reports disagreeing by what everybody calls a rounding error, which is not a rounding error. (For example: two currency tables, one with the rate "1.0845" and one with "1.08450001", produce two revenue totals that differ by a few dollars. Everyone blames rounding. The real cause is two sources for one fact.)

A note on decentralization

A federated or mesh arrangement (each domain team owns and publishes its own data, with no central warehouse team) does not remove the O(N²) term. It relocates it into cross-domain contracts and a shared identifier scheme. That is the same agreement with a different owner. This is the cost-conservation law again: the coordination cost is conserved, and any architecture that claims to eliminate it has hidden it.