For analytics engineers who keep getting handed "why did this move?" and keep finding the slices don't add up.
You know the request. A metric moved, and you're asked to explain it before end of day. So you do the natural thing: slice by region. Then by product. Then by channel. Each cut tells a slightly different story, none of them adds up to the total, and you end up writing a paragraph that starts with "directionally..." This post is about why that happens and what fixes it.
The problem with one-dimension-at-a-time
Slicing a single dimension answers "how did this metric differ across regions?" — a state question. It does not answer "how much of the change did each region drive?" — a decomposition question. Worse, single-dimension slicing systematically loses interaction effects.
Suppose revenue dropped. Maybe EMEA is flat overall, Enterprise is flat overall, and Paid-Search is flat overall — yet the intersection EMEA × Enterprise × Paid-Search fell off a cliff. Slice by region alone and EMEA looks fine; slice by channel alone and Paid-Search looks fine. The real driver lives in the combination, and marginal slices smear it across rows until it disappears. You can stare at three dashboards and never see it.
What "reconciled decomposition" means
A reconciled decomposition assigns a contribution to each factor — and each intersection of factors — such that all contributions sum exactly to the observed change. No residual. No "unexplained" bucket you quietly hope nobody asks about. (When there are many small drivers, the smallest are grouped into a labeled "Other smaller factors" rollup — but that bar is itself a sum of real attributed drivers, not a balancing plug, so the total still ties out exactly.)
That exactness is the whole point. "EMEA × Enterprise × Paid-Search contributed −2.3pts of the −6pt move, and here are the other factors that make up the remaining −3.7" is an answer you can defend in a meeting. "EMEA looks soft and Paid-Search is down a bit" is not — because it doesn't close.
A worked walkthrough
Here's a real decomposition from the FactorPrism engine. A revenue-style metric moved by an observed −15,275.74 over the period, across three dimensions — region, product, channel. The engine attributes the change to individual factors and their intersections, and the waterfall ties out exactly:
| Waterfall step | Contribution |
|---|---|
| Starting level | 1,185,610.53 |
| Channel C0 declined | −89,461.98 |
| Region R2 surged | +68,933.81 |
| R4 × P1 × C2 (localized spike) | +5,309.14 |
| R0 × C0 | −66.58 |
| R2 × P5 × C1 | +50.46 |
| P5 × C0 | +35.75 |
| R2 × P4 | −30.92 |
| R4 × P4 × C1 | +28.22 |
| Other smaller factors (labeled rollup of real drivers) | −73.63 |
| Ending level | 1,170,334.79 |
Sum of all driver bars = −15,275.74. Observed total change = −15,275.74. Residual = 0.00 — the bridge ties out to the unit, no fudge factor. (This is exact by construction: a log-mean / Divisia decomposition, independently confirmed by the engine's own regression suite.)
Notice what single-dimension slicing would have missed: the biggest moves are a channel-wide decline and a regional surge, but the localized R4 × P1 × C2 spike — a three-way intersection — only shows up because the decomposition reaches intersections. Slice any one dimension and it's invisible.
Why this is hard to hand-roll in SQL
You can write this by hand. Teams do — and it tends to eat anywhere from half a day to a week. The interaction terms multiply fast, every dimension you add expands the combinatorics, and the throwaway query almost never reconciles cleanly on the first pass. So it gets cut: "we'll just report the main effects." That's exactly when the real driver slips through.
FactorPrism does this decomposition — main effects, interaction effects, reconciled exactly to the total, down to factor intersections — natively in your warehouse, in seconds (thousands of segment-intersections solve in under ~4 seconds). The math is the product, not an afterthought.
Want to see it on a real moving metric? Watch the 73-second demo →