Inventory Health

Four questions, asked in every inventory review:

• Where is inventory concentrated? Value by site, by ABC class, by planner — the cuts that turn one big number into someone's number.
• Is the position improving? The trend across snapshots, not this month in isolation.
• What's excess, slow-moving, or obsolete — and who owns it? Health bands with an accountable planner attached to every dollar.
• Which items sit below safety stock? The risk list the planners work item by item.

The audience sets the cadence: directors read this monthly, planners weekly. What this pattern is not for is real-time stockout firefighting — that is a monitoring problem, and it belongs on a KPI wall fed by current-state data, not on a review instrument fed by snapshots.

This is the cascade layout — pattern 02 in the layout reference, the one it recommends for inventory views: every level of granularity visible at once, aggregate trend on top, parallel dimension cuts in the middle, item-level detail at the bottom. No clicks required to see the whole cascade.

What each region reads from the schema below — the pairing is the point:

1. 1Filters — every filter is a dimension attribute — dim_date snapshot month, dim_location site, dim_item planner and ABC class. Nothing filters on a fact column.
2. 2KPI row — reads the latest snapshot only: filter to one snapshot_date, then aggregate. On-hand value's semi-additivity is why this tile and the trend beside it are different queries over the same fact.
3. 3Trend — reads all snapshots — one point per snapshot_date, never a sum across them — stacked by a health band derived at query time from the stored components.
4. 4Cuts — the latest snapshot grouped by dim_location site, dim_item ABC class, and dim_item planner — three GROUP BYs over the same filtered set the KPI row uses.
5. 5Detail table — the fact's own grain — item × stocking location for the selected snapshot, with weeks of supply recomputed per row. The drill destination every conversation ends at.

The fact is a periodic snapshot: the same population of item × location rows photographed on a schedule. It exists because ERPs don't keep this history — their stock tables are current-state, overwritten with every movement. The lakehouse builds the history the source system never had, either by capturing the stock tables on the agreed cadence or by reconstructing positions from the movement ledger.

Two rules carry the whole design. Store components, never ratios: the fact carries quantities, values, and the trailing demand component — weeks of supply, turns, and health bands are computed from them at query time, so every grain gets a correct ratio instead of an averaged one. Respect semi-additivity: snapshot balances sum across items and locations but never across time — thirteen weekly snapshots summed is not a position, it's nonsense. Across time you take the period-end value or the average, and the choice belongs on the dashboard label.

Every tile on the wireframe is one of these — each a projection of the stored components, none of them stored themselves:

The one that bites hardest: never average weeks of supply across items. An item with 200 weeks of cover and an item with zero do not average to 100 weeks of anything. At every rollup grain, sum the quantity and the demand components first, then divide once. The same discipline the KPI Dictionary applies to service metrics applies to every ratio here.

Table roles. Quantity buckets by storage location in MARD — LABST is the valuated unrestricted-use stock, with quality-inspection (INSME) and blocked (SPEME) stock beside it. Batch-level stock in MCHB, sales-order stock in MSKA. The value side lives in MBEW: SALK3 (value of total valuated stock), LBKUM (total valuated stock), and the prices VERPR / STPRS — always divided by the price unit PEINH. Planning attributes for dim_item come from MARC: DISPO (MRP controller), MAABC (ABC indicator), PLIFZ (planned delivery time), DISMM (MRP type) — and safety stock lives on the plant-level material master alongside them. Identity from MARA, plants for dim_location from T001W.

The structural insight. All of these are current-state tables — SAP overwrites them in place, so the snapshot history must be built downstream: capture the stock tables into the lakehouse on the agreed cadence, or reconstruct positions from the movement ledger — MSEG line items in ECC, the single MATDOC document table in S/4HANA, which replaces both the document tables and the aggregate buckets.

Table roles. The balance grain is F41021 (item location): LIPQOH on hand in primary UOM, with the commitment buckets LIHCOM (hard committed) and LIPCOM (soft committed) feeding allocated_qty. Branch-level planning attributes in F4102: IBSAFE safety stock, IBABCS ABC ranking, IBBUYR buyer, IBLTLV level lead time. Item identity in F4101, branch plants for dim_location in F0006. The cardex F4111 is the movement ledger — the source for reconstructing history and for the trailing demand component.

The structural insight. Same current-state story: F41021holds today's balances only, so the snapshot is a scheduled capture in the lakehouse. And everything numeric is encoded on the way in: quantities carry implied decimals, the branch plant is space-padded (TRIM before joining), and cardex dates are Julian CYYDDD integers.

Table roles. Balances in InventSum: PhysicalInvent physically on hand, AvailPhysical available (on hand less physical reservations), ReservPhysical reserved, PhysicalValuefor the value side — plus the expectation buckets, where the names invert what you'd guess: Ordered is inbound supply on purchase orders, while OnOrder is open issue demand from sales orders. Every balance row hangs off InventDim — the dimension decode that turns InventDimId into site, warehouse, and batch, joined out to InventSite, InventLocation, and InventBatch for dim_location. Item attributes from InventTable, cost from InventItemPrice (activation-dated, with a price-unit divisor), and InventTrans as the movement ledger for history.

The traps. InventSum keeps settled zero-balance rows flagged Closed— filter them out or the "items stocked" counts are inflated by everything the site ever carried. Every table is company-partitioned (the DataAreaId quirk), and the dimension join has its own quirk entry worth reading before the first query: InventDim. The snapshot itself is built in the lakehouse — same current-state story as the other three systems.

Table roles. The warehouse balance is MITBAL: MBSTQT on hand, MBALQT allocated — and, unusually, the planning parameters sit right beside the balances: MBSSQT safety stock, MBREOP reorder point, MBLEAT lead time, which makes the below-safety-stock list a single-table query. MITLOC carries the finer location/lot/receipt grain (MLSTQT), item identity is MITMAS, facility-level cost is MITFAC (M9APPR approved cost) for the value side, movements are MITTRA, and facilities for dim_location come from CFACIL.

The traps. Every query pins and joins on CONO or it fans out across companies; every column name is a two-char prefix plus a four-char alias (the prefixes quirk — MBITNO and MLITNO are both ITNO); and balances are stored in the item's basic unit of measure, so normalize before valuing or comparing. History is the same lakehouse-built snapshot as everywhere else, with MITTRA as the reconstruction ledger.

How to use this page in a stakeholder session:

• Show two layouts, not one. Put this cascade next to the master-detail alternative from the layout reference and ask which matches how the review actually runs — everyone scanning the whole cascade together, or a leader drilling from a signal to a SKU.
• Agree the snapshot cadence before building anything. Monthly or weekly is not a refresh setting — it is the grain of the fact. A monthly snapshot can never answer a weekly question, and rebuilding the fact later costs more than the meeting where the cadence gets decided.
• Name the anti-patterns out loud: averaging weeks of supply across items, summing on-hand value across months, and storing the ratio instead of the components. Each one produces a confident number that is simply wrong, and each is invisible on a finished dashboard — which is why they get named in the workshop, not discovered in the review.