S&OP / Demand Review

One question, asked once a month: is the plan we agreed last cycle still the plan? Everything else on the board is a way of answering it — how much demand has moved away from the number, whether supply can still meet it, and how much the plan itself has been rewritten since.

That only works if the plan being scored is fixed. Two variants get named and kept apart everywhere on this pattern: the consensus plan — the constrained number the room signed off and locked at the end of the cycle — and the latest estimate — the unconstrained current view, edited continuously between cycles. Every tile says which one it shows. Score the latest estimate and the review becomes self-congratulatory by construction: the plan is always close to actual, because it was moved toward actual all month.

This is the same laundering trap the sell side has with promise dates — the date-basis switch in the OTIF entry — where measuring against the current promise makes every reschedule disappear. Same failure, different column: an unlocked baseline flatters the number that is supposed to hold the room accountable.

One boundary to keep clean: this is the executive cycle review, at family grain, once a month. The planner instrument underneath it — item × location, worked continuously, error ranked onto a named worklist — is the Plan vs Actual pattern. Same discipline about locked baselines, different grain and different audience.

Top-down: executive summary first, granularity below, read in one direction. It suits a review with a fixed agenda and a fixed audience: the headline lands in the first thirty seconds, and the plan grid at the bottom is where the argument actually gets settled.

What each region reads from the schema below:

1. 1Filters — dim_plan_cycle is the first filter and the most consequential one — it selects which locked plan the whole board scores against. Family and site come from dim_product and dim_location.
2. 2KPI row — four tiles over the selected cycle, each labeled with the plan variant it reads: attainment and churn are ratios recomputed from summed components, drift and supply gap are signed quantities. A tile that says only "plan accuracy" is a tile nobody can defend.
3. 3Plan vs LE vs actual — three series over dim_date's plan_month role — the locked consensus plan, the unconstrained latest estimate, and actuals attaching as months close. The gap between the first two is the conversation; drawing only one of them hides it.
4. 4Demand and supply gaps — the same locked plan as the denominator on both sides — dim_product family for demand, dim_location site for supply. Two panels, one baseline, so the demand and supply halves of the meeting cannot quietly be arguing about different numbers.
5. 5Plan grid — the fact's own grain, pivoted family × plan month with locked plan, latest estimate, and actual stacked in each cell — the drill destination, and the artifact the room marks up.

The fact is a periodic snapshot, but the snapshot is of a plan, not a population: at the close of every cycle, the whole forward plan is photographed and stored under that cycle's key. That is what makes the lock enforceable — last cycle's rows are immutable history, and no edit made since can reach back into them.

Two rules carry the design. Store every variant as its own component: consensus, latest estimate, supply plan, prior cycle, and actual all sit side by side on the row, so drift, gap, churn, and bias are subtractions at query time rather than four differently-built tables that will disagree by March. Store quantity and value in parallel: the demand side runs the review in units and finance runs it in value, and both are legitimate. Carry both components and compute each view from its own pair — never convert a ratio computed in units into a value number by multiplying it by a price. Mix effects make that quietly wrong every time — churn included, which is why the prior cycle carries a value component too.

The consequence of photographing the whole forward plan every cycle: the quantities sum within a cycle, never across cycles. Each cycle re-states the same plan months, so a total that spans two cycles counts those months twice. The cycle is the first filter on this board for exactly that reason.

Five measures, each a projection of the stored components, none of them stored:

The one that bites hardest: never average attainment across families. A family at 300% of plan and a family at 20% do not average to anything a business should act on. Sum the actuals and sum the plan first, then divide once — at every grain the board rolls up to. The same discipline the KPI Dictionary applies to service metrics applies to every ratio here.

And the targets: the attainment line drawn on this board is the number the business agreed to in its own planning process, labeled as exactly that. There is no external benchmark for plan attainment worth drawing next to it — a line with no provenance invites the room to argue about the line instead of the plan.

Rendering notes for building this wireframe on Databricks AI/BI dashboards. Where a capability below comes from hands-on checking rather than documentation, it was verified in one workspace on 2026-08-24 — a point-in-time observation, not a vendor commitment; re-check in your own workspace before committing a design to it.

Table roles. The honest starting point: in an SAP landscape the consensus plan usually does not live in ECC or S/4 at all — it lives in APO or, increasingly, in IBP. What the ERP reference gives you is the mapping layer: /SAPAPO/MATKEY is the APO product master and /SAPAPO/MATLOC the location-product record, and together they tie planning output back to the material and plant keys the rest of the model uses — MARC for the plant-level planning attributes. Supply-side response shows up as PLAF planned orders with MDKP as the MRP document header. Actual demand comes from the sales side: VBAK / VBAP on an orders basis, or VBRK / VBRPon an invoiced basis — which of the two is "actual demand" is a decision to make once and label on the axis.

The structural insight. The planning system holds the current plan, not the plan history — it is built to be overwritten by the next run. So the cycle snapshot is a lakehouse capture: at the lock date, extract the plan in full and write it under that cycle's key. Miss a cycle and it is unrecoverable, which is why the lock calendar is a build prerequisite rather than a scheduling detail.

Table roles. JDE is the one system here that keeps the plan in the ERP: F3460 holds forecast quantities keyed by item, branch, and forecast type — and that forecast type is a genuine in-ERP version axis, which is what lets generated and adjusted plans coexist as separate rows rather than one overwriting the other. Planning-run output lands in F3413 (the MRP time series, where the quantity-type dimension gives you demand, supply, and projected on-hand from one narrow table) and F3411 (the action messages planners work after each run); F4801 carries work orders on the make side. Actuals come from F4211 plus F42119 — the union is mandatory, because lines move to history at sales update.

The traps. Forecast-type discipline is a client-by-client question: if the business overwrites the same type each cycle, the version axis is decorative and the snapshot still has to be captured downstream. And everything numeric is encoded on the way in — quantities carry implied decimals, the branch plant is space-padded (TRIM before joining), and dates are Julian CYYDDD integers.

Table roles. ForecastSales holds demand forecast lines per forecast model, item, dimension set, and period — and the forecast model is the version axis, the same role JDE's forecast type plays. The master plan's own output is ReqTrans: every demand and supply the planning run saw per plan version, with the planned orders it generated — the supply-response side of the board. Actuals come from SalesTable / SalesLine on an orders basis, or CustInvoiceJour / CustInvoiceTrans on an invoiced basis.

The traps. Plan versions in D365 are routinely recycled — a static plan copied over a dynamic one keeps the same version name, so the version axis alone will not give you cycle history without a dated capture. Every table is company-partitioned (the DataAreaId quirk), forecast lines hang off a dimension set (InventDim), and open date columns carry sentinel values rather than NULLs (sentinel dates).

Table roles. Said plainly: the M3 reference carries no forecast table, and this pattern does not pretend otherwise. What it has is MITPLO, the material plan — one row per planned demand or supply transaction per item and warehouse. That is a current-statetable, regenerated by the planning run, so it answers "what does the plan say today" and nothing about what it said last month. MITBAL carries the warehouse balances and planning parameters behind the supply response, MPLINE the inbound purchase orders and MWOHED the manufacturing orders. Actual demand is OOLINE on an orders basis or ODLINE on an invoiced basis.

The traps. Because MITPLOis regenerated, the cycle snapshot is entirely a lakehouse capture — the fact's cycle key exists only downstream, and if nobody schedules the extract at the lock date there is no history to build later. Then the usual M3 mechanics: pin CONO in every join, read column names as a two-char prefix plus a four-char alias (the prefixes quirk), and normalize quantities out of the item's basic unit of measure before aggregating a family.

Table roles. The demand plan is two tables: MRP_FORECAST_DESIGNATORS names the forecast and the set it belongs to, and MRP_FORECAST_DATES is the fact under it — a row per item, org, forecast name, and bucket date, except that a row with RATE_END_DATE set spans every bucket through to that date and has to be expanded before it is summed. The supply response comes from ASCP plan output: MSC_PLANS is the plan registry and MSC_SUPPLIES the supply side of the run that plan produced. Actual demand is OE_ORDER_LINES_ALL.

The traps. The designator is the identity of a forecast, so it belongs in every join — and a row with a NULL forecast set is itself a set, which inflates a forecast count if it is not filtered. Forecast quantities are consumed: CURRENT_FORECAST_QUANTITY is post-consumption, so summing the original double-counts demand that sales orders already took. Plan output means nothing until one PLAN_ID is chosen, and the plan tables are overwritten by the next run, so the cycle snapshot is still a lakehouse capture taken at the lock date. Order lines carry operating-unit striping (MOAC in practice).

Table roles. The Fusion reference carries no plan table. Plans live in Supply Planning and reach the lakehouse through BICC extracts of their own, so the cycle snapshot is assembled downstream rather than read from a physical plan table. What this reference does carry is the demand actuals: DOO_FULFILL_LINES_ALL, at the fulfillment-line grain, rolled up to the family and month the review runs on.

The traps. Fusion Cloud has no direct SQL path; the physical names describe the source model and the extract must use the verified BICC/PVO surfaces available for those tables (no SQL path), and extract definitions drift with each release (PVO drift). That means the fact is assembled from two extract paths — the planning extract and the order-management extract — landed on different schedules, so the cycle key has to be stamped in the lakehouse rather than inherited. Keep business-unit scope explicit (_ALL means BU here) before a site cut is trusted.

Three decisions have to be made before anyone builds — the first two are the grain of the fact, and the third decides what the demand number contains:

• Agree the family grain. S&OP is a family-level conversation, but "family" is a business decision, not a master-data attribute you can look up. Get the room to name the level it plans at and commit to it — too fine and the review drowns, too coarse and no one can act. Item-level detail belongs on the planner instrument, not here.
• Agree the lock calendar. Which day of the cycle the consensus plan is frozen, and who signs it. That date is when the snapshot gets captured, and a cycle nobody captured is a cycle nobody can ever score. Write it on a calendar before writing any SQL.
• Decide where the returns forecast enters the cycle. A returns forecast is not a demand forecast run backwards. Nothing returns that was not first shipped, so returns are conditional on prior issues rather than an independent series — and where returned units re-enter sellable supply, replenishment runs on net demand, demand minus returns. Placement is the decision, and there is nothing to inherit: full-text scans of the CSCMP glossary, the industry's main process-reference model and the ASCM Dictionary return nothing that seats a returns forecast in a named step of the monthly cycle. So the room has to author it: name the step returns enter at, and log the choice as an assumption the way the reference build's assumption log does.

And the anti-patterns to name out loud: scoring the latest plan (the plan gets edited toward actual all month, so the review congratulates itself), averaging attainment across families (a ratio of ratios that no grain agrees with), and a one-number review with no supply column — a demand number alone tells the room it missed, not whether the business could have delivered if it hadn't.