When Selective Isn't — the V8 Batch Path

I have spent four posts praising V8's selectivity, and it deserves the praise — at the row level. The trigger genuinely recomputes only what a change affects, down to doing nothing for PaymentMethod. But V8 has a second path, the batch path, and it does not live up to the name “selective.” This closing V8 post is the honest audit: where the selectivity holds, where it quietly doesn't, and a measure that computes correctly once and zeroes itself out forever after. If the series has earned anything, it is the right to end V8 by marking its own homework.

Two paths, one reputation

V8 recalculates in two places. The row-level trigger TR_IntelligentDimensionChange fires on individual dimension changes and is truly selective — it consults the dependency map and acts only on the affected measures. The batch path, ProcessSalesBatchV8Selective, is meant to bring that same selectivity to bulk processing. The name says selective. The code says otherwise.

-- ProcessSalesBatchV8Selective, in essence:
FOR each current dimension WHERE AffectsCalculations = TRUE LOOP
    recalculate that dimension's MeasureDimensionDependency set
END LOOP;

Read what it actually loops over: every current dimension where AffectsCalculations = TRUE — not the dimensions that changed, but all dimensions that could affect calculations. For a typical sale, that is most of them. So the batch path recomputes the dependency set of nearly every dimension, which collectively covers nearly every measure. It is doing a full-ish recalculation dressed in selective clothing.

The contrast with the row-level trigger is stark. The trigger is change-driven: it knows which dimension moved and recomputes only that dimension's dependents. The batch path is not change-driven at all — it has no notion of what changed, so it processes everything that theoretically could matter. The real selectivity of V8 lives in the trigger, not the batch. The batch path borrows the vocabulary of selectivity without the mechanism.

Why the distinction matters

This is not pedantry, because the two paths run in different situations. The trigger handles interactive, one-at-a-time changes — exactly where its selectivity shines and where the 60–80% claims are most believable. The batch path handles bulk reprocessing — exactly where you would most want selectivity, because that is where the volume is, and it is precisely where V8 does not deliver it.

So the aggregate efficiency of V8 in production depends heavily on which path dominates your workload. A warehouse driven mostly by interactive edits gets the real selective win. A warehouse that leans on bulk batch reprocessing gets something much closer to the old rebuild-everything behaviour, with extra bookkeeping on top. The headline number is real for one path and aspirational for the other, and an honest reading of V8 keeps them separate.

There is an efficiency metric that half-admits this. AnalyzeBatchEfficiencyV8 computes a “Selective Recalculation Rate” — the percentage of batches that touched fewer than all 18 measures. That it needs to measure whether batches were actually selective is a tell that many of them are not.

The measure that zeroes itself

There is a second honesty item, subtler and more instructive. ProfitabilityIndex is one of V8's advanced measures, and it depends on a product's cost — data that lives in the Product table, not in the sale's dimensions. Two different code paths compute it, and they disagree.

PopulateEnhancedFacts, which runs on full initialisation, joins Product and computes it properly:

-- on init: real computation, joins Product for Cost
(ProductPrice - Discount - Cost) / Cost   AS ProfitabilityIndex

But RecalculateSpecificMeasures, the selective incremental path, does not join Product. It cannot compute ProfitabilityIndex because it does not have Cost in scope, so it returns:

0   -- 'Will be calculated with product cost'

Read the consequence carefully. On initialisation, ProfitabilityIndex is correct. The first time any selective recalculation touches that sale, ProfitabilityIndex gets overwritten with zero, because the incremental path cannot compute it and defaults to 0 rather than leaving it alone. So the measure is correct exactly until the first incremental update, and silently wrong (zero) forever after. A measure that degrades on its first recalculation is worse than one that is never computed, because it looks populated — you get a plausible zero, not an obvious NULL.

The root cause is that the dependency map models dependencies between dimensions and measures, but ProfitabilityIndex depends on data outside the dimension rows entirely — a Product attribute the selective path never loads. V8's whole intelligence assumes a measure's inputs are its sale's dimensions; a measure whose input lives in another table falls through the model. It is a real limit of the dependency approach, exposed by one measure, and I would rather point at it than let a stray zero pass as a computed value.

What I would fix

If I were hardening V8, three things, in order. First, make ProcessSalesBatchV8Selective actually change-driven — track which dimensions changed since last processing and loop only those, so the batch path earns its name. Second, fix RecalculateSpecificMeasures to join Product (or to skip rather than zero measures it cannot compute — a preserved old value beats a wrong new one). Third, clean up the tautological log-stamping bug so the efficiency measurements the audit relies on are themselves trustworthy. None of these touches the core idea; all of them are the gap between a good design and a finished one.

The honest verdict on V8

V8's central idea — model the dependency structure, recompute only the blast radius — is the right idea, and the row-level trigger proves it works with the unarguable PaymentMethod zero. The execution is incomplete: the batch path is selective in name only, and at least one measure degrades on incremental recalculation. That is a fair verdict — a genuinely good architecture with an honest gap between what it claims and what every path delivers.

I think that gap is the truest thing about the whole project. KC Star is a real system that reaches for real ideas and does not always fully close the distance, and the versions are legible precisely because I kept the reaching and the falling-short on the record. V8 reached for selective recalculation, got it in the trigger, and missed it in the batch. Naming the miss is the point.

V9 takes V8's intelligence and fuses it with V6's permissions — two engines, one version — and immediately runs into the same tension between a clever design and a maintainable one. Two engines, one version opens the V9 series.