A Dependency Map Between Dimensions and Measures

V8's whole thesis — recalculate only what a change affects — depends on one piece of knowledge: which measures does each dimension actually drive? That knowledge has to live somewhere explicit, because the database cannot infer it. In V8 it lives in a single table, MeasureDimensionDependency, and this post is that table: how it is shaped, how it is populated (partly by hand, partly generatively), and the priority formula that reads it to decide what to recompute first.

The map

The table is a many-to-many edge list between measures and dimensions, annotated:

CREATE TABLE MeasureDimensionDependency (
    DependencyId SERIAL PRIMARY KEY,
    MeasureTypeId SMALLINT REFERENCES MeasureTypeLookup(MeasureTypeId),
    DimTypeId SMALLINT REFERENCES DimTypeLookup(DimTypeId),
    DependencyType VARCHAR(20),      -- DIRECT / INDIRECT / AGGREGATION
    IsRequired BOOLEAN,
    ImpactWeight DECIMAL(3,2) DEFAULT 1.0
);

Each row is an edge: this measure depends on this dimension, in this way, with this weight. The DependencyType distinguishes how a measure depends on a dimension:

• DIRECT — the dimension is a direct input to the measure's formula. TotalSale depends DIRECTly on ProductCount, ProductPrice and Discount.
• INDIRECT — the dimension feeds the measure through an intermediate. A measure derived from TotalSale depends INDIRECTly on whatever TotalSale depends on.
• AGGREGATION — the dependency is through a rollup rather than a per-row computation.

And ImpactWeight quantifies how much the dimension matters to the measure — a full 1.0 for a direct driver, less for a diluted indirect one. Those two annotations, type and weight, are what let V8 reason about not just whether a measure is affected but how strongly, which turns out to matter for prioritisation.

Seeded by hand where it matters

The dependencies that carry real semantics are declared explicitly. The seed data spells out the important edges:

-- TotalSale (1) is driven directly by count, price, discount
INSERT INTO MeasureDimensionDependency (MeasureTypeId, DimTypeId, DependencyType, ImpactWeight) VALUES
(1, 1, 'DIRECT', 1.0), (1, 2, 'DIRECT', 1.0), (1, 3, 'DIRECT', 1.0),
-- NetRevenue (2) on count and price
(2, 1, 'DIRECT', 1.0), (2, 2, 'DIRECT', 1.0),
-- TotalCost (7): indirect, diluted, on count/price/discount plus a direct on ShippingCost
(7, 1, 'INDIRECT', 0.5), (7, 2, 'INDIRECT', 0.5), (7, 3, 'INDIRECT', 0.5), (7, 8, 'DIRECT', 1.0);

Look at TotalCost (measure 7). It depends indirectly on the base sale dimensions with a diluted weight of 0.5 — they influence it, but through cost calculations rather than directly — and directly on ShippingCost with full weight. That is a genuinely nuanced statement about how a measure relates to its inputs, hand-authored because only I know that TotalCost's relationship to ProductCount is real but weaker than its relationship to ShippingCost. The important edges deserve human judgement, and they get it.

Generated where it doesn't

Hand-authoring every measure-dimension pair would be tedious and error-prone — with eighteen measures and twelve dimensions, that is up to two hundred edges. So after the explicit seeds, a second pass generates the remaining combinations programmatically, assigning a DependencyType and weight by rule: DIRECT/INDIRECT/AGGREGATION with weights of 1.0 / 0.8 / 0.5 depending on the relationship. The map is therefore a hybrid — carefully authored where the semantics matter, mechanically filled where a reasonable default suffices.

I like this division of labour, and it generalises. A dependency graph you author entirely by hand is accurate and unmaintainable; one you generate entirely by rule is maintainable and often wrong on the edges that matter. Seeding the load-bearing edges by hand and generating the rest gives you correctness where correctness counts and coverage everywhere else. The risk, which I will own, is that the generated defaults can be subtly wrong — a generated edge might claim a dependency that does not really exist, causing an unnecessary recalculation. That errs toward over-recomputation, which is safe (you never skip a measure you should have recomputed), just not maximally efficient.

Reading the map

Three functions query the graph. GetAffectedMeasures(p_DimTypeId) is the workhorse — given a dimension, it returns the measures that depend on it, ordered by impact weight and dependency level:

SELECT * FROM GetAffectedMeasures(2) ORDER BY ImpactWeight DESC;  -- what ProductPrice drives

GetRequiredDimensions(p_MeasureTypeId) walks the other way — given a measure, which dimensions does it need. And AnalyzeMeasureDependencies traverses the DependsOnMeasures chains for measures that depend on other measures. Between them you can ask the graph any question the selective recalculation needs.

The priority formula

Knowing which measures are affected is half the job; knowing which order to recompute them in is the other half. DetermineRecalculationNeeds returns the affected measures each with a computed priority:

Priority := (5 - DependencyLevel) * 10 + (ImpactWeight * 10)

Unpack it. (5 - DependencyLevel) inverts the level so that lower dependency levels — base measures, closer to raw inputs — score higher. That is deliberate: base measures should recompute first, because derived and aggregated measures depend on them, and recomputing a derived measure before its base is settled would just mean doing it twice. The ImpactWeight * 10 term breaks ties by how strongly the dimension drives the measure. The formula encodes a real scheduling insight — recompute foundations before the things built on them, and prioritise strong dependencies over weak ones — in one line of arithmetic.

Why the map is the whole version

Everything else in V8 is mechanism; MeasureDimensionDependency is the knowledge. Without it, “recalculate only what changed” is an aspiration with no way to know what changed affects. With it, the selective trigger becomes a lookup: a dimension moved, ask the map what depends on it, recompute exactly that set in priority order. The map is the difference between the blunt rebuild-everything of V3 and the surgical recalculation V8 promises.

It is also the version's main fragility, and I said as much in part 1: a dependency map is state that can be wrong. The blunt approach kept nothing and so could not drift; V8 keeps a model of the schema's own structure, and that model has to be maintained as measures and dimensions change. Get an edge wrong and you either over-recompute (safe, slow) or under-recompute (fast, incorrect). The map buys efficiency and takes on the maintenance burden that efficiency always costs. The most satisfying demonstration that it works is a dimension with no outgoing edges — change it, and the map correctly says: recalculate nothing. That is next.