Conformed Dimensions and the Bus Matrix

Two fact tables, one recording sales and one recording inventory, both carry a product key. A report that compares revenue per product to stock per product joins them on that key. If the two keys were assigned by separate loaders from separate source systems, the join returns rows where the key values happen to coincide, which is to say it returns nonsense with a plausible shape. The keys are not the same key even though they have the same name and the same type.

Conforming is the discipline that prevents this, and it is a property of dimensions rather than of facts. Two fact tables are comparable only when their dimensions agree on both the identity of each member and the set of members present. The bus matrix is the artefact that plans those dimensions across the whole warehouse before any of them is built.

What conformity actually requires

Conformity has three levels, and they are often confused.

Identical keys. The same dimension table is referenced by both fact tables, so a key value means the same member in both. This is the strongest form and the one that makes drill-across work: two fact tables can be aggregated separately at a shared dimension’s grain and the results aligned row by row.

Identical members. Both fact tables see the same population. A product dimension that contains only products sold this year, because the sales loader only emits those, is not conformed to an inventory fact table that holds every product in stock. The join produces nulls for the products inventory knows about and the totals stop reconciling. The dimension must contain the union of members any fact table can reference, which usually means loading from a master source rather than from each fact’s own extract.

Identical attribute semantics. The column category must mean the same classification in both. A product dimension where sales assigns categories from a merchandising hierarchy and inventory assigns them from a storage hierarchy shares a column name and nothing else.

Attribute semantics is the failure that survives all structural checks. Keys can match, members can match, and the column can still mean two things. This is why a conformed dimension needs a documented definition per attribute, and why the dimension’s owner is a single team rather than each fact’s pipeline.

The strongest form has an additional consequence that is easy to miss: the conformed dimension is shared, so a change to it changes every fact table’s reports at once. Adding an attribute is additive and safe. Removing or redefining one is a coordinated change across all consumers, and the type 2 versioning rules apply to all of them simultaneously.

Drill-across, and why it needs a shared grain

Drill-across is the operation conformity exists to enable: aggregate two fact tables independently at the same dimension grain, then combine the result sets. It is not a join of facts. The two aggregations produce one row per dimension member, and those rows are combined by key, which is a set operation on small results rather than a join of large tables.

WITH sold AS (
  SELECT product_key, SUM(extended_price) AS revenue
  FROM fact_sales_line GROUP BY product_key
),
stocked AS (
  SELECT product_key, SUM(on_hand_units) AS units_on_hand
  FROM fact_inventory_snapshot GROUP BY product_key
)
SELECT p.product_name, s.revenue, i.units_on_hand
FROM dim_product p
LEFT JOIN sold    s ON s.product_key = p.product_key
LEFT JOIN stocked i ON i.product_key = p.product_key;

Each common table expression aggregates at its own fact table’s grain, and the outer join aligns them at the dimension’s grain. Doing it the other way — joining the two fact tables first and aggregating afterwards — multiplies rows by the number of rows in the other table per product and produces a revenue figure inflated by the count of inventory snapshots. The distinction is the same fan-out problem that a mismatched fact grain creates within a single table.

Drill-across requires the aggregations to be at the same grain. Sales at line grain aggregated by product is one row per product. Inventory at product-per-day grain aggregated by product is also one row per product, provided the inventory measure is a snapshot balance that should not be summed across days. Summing on_hand_units across a month of daily snapshots gives thirty times the stock. The measure has to be reduced with the right function — average, last value, or maximum — before it can be combined, and that choice belongs to the measure’s definition, not to the query.

The bus matrix

The bus matrix is a grid: business processes as rows, conformed dimensions as columns, and a mark wherever a process uses a dimension. Its purpose is to plan the shared dimensions once, across processes, so that the fourth fact table built does not invent its own customer key.

The matrix is read in two directions. Down a column, a dimension’s consumers are visible: a date dimension marked for every process is the most conformed dimension in any warehouse and is almost always built first. Across a row, a process’s dimensionality is visible, which tells the implementer what the fact table’s foreign keys will be before any table exists.

The order of implementation follows the matrix rather than the org chart. The first process is chosen to establish the most shared dimensions — commonly sales, because it touches date, product, customer, store and promotion — so that subsequent processes inherit rather than create. A process that needs only dimensions already built can be added at low cost. A process that introduces a new dimension pays for that dimension’s master data work, which is usually the expensive part.

Two rows that share no dimension cannot be compared at all. That is not a defect; some processes are genuinely unrelated. What is a defect is two rows that should share a dimension and do not, which is the state that arises when each pipeline is built independently and each assigns its own keys.

The matrix also records the level of conformity. A date dimension conformed at day grain serves every process that reports daily. A process that needs shift-level reporting cannot use it without a separate shift dimension, and marking that column as “conformed at day grain only” is more honest than pretending the shared table covers the case.

Conforming dimensions that already diverged

Retrofitting conformity onto two independent dimensions is a migration, and the cheap version does not exist. The work is a member reconciliation: build the union of members from both sources, assign a single surrogate key per member, and rewrite the fact tables’ foreign keys to the new values.

The union step is where the real decisions sit. Two source systems rarely agree on the natural key — one may use an internal identifier, the other a manufacturer part number, and some products have neither in common. Matching then requires either a cross-reference table maintained by the business or a matching rule, and a matching rule that merges two products which are actually distinct is worse than leaving them apart, because the merged history cannot be split again without the original keys.

Rewriting the fact keys is a backfill over every historical row, which for a large fact table is a full rewrite. It is feasible when the fact tables are partitioned by date, since the rewrite can proceed partition by partition; it is painful when they are not. The alternative — leaving history on the old keys and conforming only forward — produces a table where the same product has two keys depending on the row’s date, and every query that groups by product silently splits the member in two.

A cross-reference table is the durable answer either way. It maps each source’s natural key to the conformed surrogate key, and it is the only place that mapping is recorded. Fact loads resolve keys through it, so a new source system joins the warehouse by adding rows to the cross-reference rather than by changing any fact table.

Where the matrix stops helping

The bus matrix plans dimensions; it says nothing about the slowly changing behaviour of each one, which is a per-dimension decision with its own consequences for every consumer. A customer dimension that gains type 2 history changes the meaning of “customer count” for all processes that use it, and the matrix has no column for that.

It also says nothing about which dimensions are conformed by reference versus by value. Two warehouses in the same organisation, or a warehouse and a source mart, can share dimension definitions without sharing tables, which requires a documented contract and a periodic comparison rather than a foreign key. That arrangement is common where a business unit owns its own platform, and it is only as good as the comparison job behind it.