OTIF / Customer Service

Two jobs, one instrument. The weekly service review: is the service promise being kept, where is it slipping, and what changed since last week. And the retail-compliance defense: when a scorecard customer disputes a month, the path from their number to your order lines has to be minutes, not a data pull. Both jobs are the same drill — from "OTIF dipped to 91%" to "these 40 order lines, at this DC, for this customer."

One prerequisite, stated bluntly: a dashboard displays a definition — it doesn't create one. Before this gets built, the six switches of the OTIF definition — grain, date basis, ship vs arrival, first attempt vs cumulative, tolerance, exclusions — must already be set. That conversation is the switchboard, and it comes first. Build the dashboard before the definition and every review becomes an argument about the number instead of the operation.

This is the master-detail layout — pattern 03 in the layout reference, the workhorse for going from signal to SKU. The trend is interactive: clicking a month filters the cuts and the detail table below it, so the review walks the same path every week.

What each region reads from the schema below:

1. 1KPI row — four count_ifratios over the same fact — each tile is annotated with its formula, because a tile whose formula can't be stated is a tile nobody should trust. Change a switch setting and the SELECT changes; the dashboard doesn't.
2. 2Trend vs target — the joint pass flag aggregated by promise month from the role-playing dim_date — which date role anchors it is the date-basis switch, stated on the axis label. The target line is the number the business agreed to, drawn on the chart, not implied.
3. 3Cuts — the same joint flag grouped by dim_customer and dim_ship_from — the two questions every review asks next: who is affected, and which site owns it.
4. 4Failing lines — the fact's own grain, filtered to failures — the degenerate order and line numbers carry the drill all the way to the document in the ERP.
5. 5Failure reason — late vs short vs both, derived from the same on_time and in_full flags the tiles use — the panel that turns a score into an action.

This is the same fact the OTIF Dictionary entry lands — one fct_order_line_fulfillment at order-line grain carrying the components: requested / first-confirmed / current promise dates, first-ship and last-ship (and, where captured, arrival) dates, ordered / shipped / canceled quantities, the delivery count, and a first-attempt-complete flag. No OTIF column anywhere. The full bronze → silver → gold walk is in the landing pattern.

Where the snapshot fact on the inventory pattern photographs a population on a schedule, this fact accumulates: one row per order line, its dates and quantities filling in as the line moves through confirmation, shipping, and delivery. Together the two patterns are the two fundamental Kimball fact types every supply chain gold layer is built from.

Every tile is a count_if over the fact — all non-additive, all recomputed at whatever grain the view needs:

Each measure moves with the switches it depends on: on_time with date basis and ship vs arrival and tolerance, in_full with first attempt vs cumulative and exclusions, and the whole denominator with grain. That is the payoff of storing components: the customer's scorecard configuration and your internal view are two SELECTs over one fact, reconciled by construction.

Order lines from VBAK / VBAP carry the requested date, ordered quantity, and rejection status; the first-confirmed promise comes from the VBEP schedule lines; deliveries and actual goods issue from LIKP / LIPS — aggregated to the order line before comparing, because every delivery split is its own row — and KNA1 feeds dim_customer. The switch-by-switch Databricks SQL lives on the OTIF entry →

Sales lines from F4211 plus the F42119 history — lines move at sales update, so the union is mandatory — with headers in F4201. JDE keeps all three promise dates on the line, which is what makes the rebooking rate computable at all; the F42199 change ledger records every date edit, F4209 the held orders, and F4215 / F4941 / F4942 carry the arrival-side dates. The switch-by-switch Databricks SQL lives on the OTIF entry →

Promises on SalesTable / SalesLine (requested vs confirmed as a column swap), posted shipments in the packing-slip journal CustPackingSlipJour and invoices in CustInvoiceJour / CustInvoiceTrans, with warehouse-true ship timestamps in WHSShipmentTable and WHSLoadTable / WHSLoadLine tying loads back to source lines — company pinned and sentinel dates NULL-wrapped throughout. The switch-by-switch Databricks SQL lives on the OTIF entry →

Order lines and promises in OOHEAD / OOLINE, the delivery trail in MHDISH / MHDISL (one row per order line per delivery — the split-shipment record), and the invoiced ledger in ODHEAD / ODLINE — CONO pinned in every join, status ladders deciding what counts as canceled. The switch-by-switch Databricks SQL lives on the OTIF entry →

Two conversations, in a fixed order:

• The switchboard conversation first. Walk the six switches with the business and write the settings down. This is the definition the dashboard will display — and if a scorecard customer exists, their configuration is a second view over the same fact, agreed in the same meeting.
• Then the layout conversation. Agree the target line (drawn on the trend, not implied), and which cut leads — customer for a compliance-driven business, ship-from site for an operations-driven one. The wireframe above is the starting point to react to, not a spec to accept.

And the anti-patterns to name out loud before anyone builds: storing the ratio (an OTIF column in a table is a definition frozen at build time), measuring against the current promise (every reschedule launders lateness — the date-basis switch), and dashboards with no path to the failing line — a service score nobody can drill into is a number, not an instrument.