Business rules, data assumptions & personas

US-market default. This data covers the Americas region (US, Canada, Latin America), but a question that doesn't name a market is scoped to the US by default, and the answer states that the default was applied.

WHERE PLANT_CODE LIKE '10US%' (or the US-plant flag where exposed)

Default reporting window. A question that states no time period defaults to Q1 2026 (Jan–Mar), the standard reporting window for this data — stated explicitly whenever applied. This does not extend to other unstated parameters (minimum line counts, thresholds), which get an explicit, reasonable assumption instead of a standard default.

WHERE <period column> >= '2026-01-01' AND <period column> < '2026-04-01'

"Consistently" / "every month" / "throughout" requires the condition to hold in *every* period of the stated window, not merely in the periods where the entity happens to have data. An entity present in only 2 of 3 months of a quarter does not qualify as "consistent" for that quarter unless the missing month is explicitly excluded and that's stated.

Not expressible as a single filter — requires per-period evaluation (e.g.,COUNT(DISTINCT period WHERE condition holds) = COUNT(DISTINCT all periods in window))

"Where" means a physical location. A "where is X concentrated" question is answered by plant/distribution centre (code + site name); business unit, brand, and product family are categories, not places, and do not answer a "where" question.

Not expressible as a filter — aGROUP BY PLANT_CODE requirement, not a WHERE clause

"Which products / materials / SKUs / items" is always resolved at individual material grain (12-digit material code); a brand/BU/category rollup may support the answer but never substitutes for the material-level list.

Not expressible as a filter — aGROUP BY MATERIAL_12NC requirement

Exclude no-activity entities from window-based counts or rankings. A material/vendor/customer/plant with zero relevant activity in the window is out of scope, not a "flat" or "on-target" zero; the population actually used is stated alongside the count.

Not expressible as a single filter — requires excluding rows with no qualifying activity (e.g.,HAVING SUM(activity_qty) <> 0 or an equivalent existence check per entity)

Business-unit grouping/filtering/joining uses the numeric business-unit code, never the descriptive business-unit label, which can repeat or be entered inconsistently. The label is display-only.

GROUP BY BU_CODE (joined to resolve the display name)

Stock/snapshot measures (on-hand, slow-moving, dead-stock) anchor to the latest available period, independent of any demand/sales window named elsewhere in the same question — pulling the stock leg back to an earlier month answers a different, historical question. A "held zero stock in at least one month of Q1" question is checked month-by-month, not collapsed to a single aggregate or latest-period check.

WHERE CALENDAR_MONTH_KEY = (SELECT MAX(CALENDAR_MONTH_KEY) FROM <fact>)

All "open / overdue / at risk" logic is anchored to a fixed snapshot date (the latest actual delivery date in this data extract) rather than the current calendar date. Because the underlying data is a static extract, using the live calendar date would produce a different, wrong answer every day; the fixed anchor is updated by hand whenever the extract itself is refreshed.

WHERE open_qty > 0 AND <requested/confirmed/scheduled date> < DATE '2026-07-07'

Never mix a quantity measure and a value/currency measure in the same aggregation. Wherever a fact table carries both, the measure type is always filtered explicitly before summing.

WHERE MEASURE_TYPE = 'QUANTITY' *or* WHERE MEASURE_TYPE = 'VALUE_EUR' (never omitted)

DRM reliability % (delivery on-time rate) is on-time scored lines ÷ eligible lines, computed only over lines with no populated rejection-reason code — cancelled sales-order lines are treated as not being delivery failures, and are removed from both sides of the ratio, not just the numerator. Including them understates every service metric by roughly 3.6 percentage points on this data. The rejection-reason code itself remains available for explaining *why* a line was rejected; it does not decide whether a rejected line counts toward the miss rate.

SUM(IFF(REJECTION_REASON_CODE IS NULL, DRM_SCORED_LINES, 0)) * 100.0 / NULLIF(SUM(IFF(REJECTION_REASON_CODE IS NULL, DRM_TOTAL_LINES, 0)), 0) on FACT_DELIVERY

OTIF (on-time and in-full) is DRM-reliable AND delivered quantity ≥ agreed quantity, over lines with no populated rejection-reason code. This is always read from the pre-built delivery-grain metric — never hand-reconstructed from the reliability flag plus a quantity comparison (a materially stricter, wrong reading on this data), and never read from the sales-order-line table, whose on-time/OTIF columns are a different, unsanctioned measure that disagrees with the correct figure by roughly 20 percentage points.

100.0 * SUM(IFF(REJECTION_REASON_CODE IS NULL AND DRM_FLAG = 1 AND DELIVERY_ACTUAL_QTY >= DELIVERY_AGREED_QTY, 1, 0)) / NULLIF(SUM(IFF(REJECTION_REASON_CODE IS NULL, 1, 0)), 0) on FACT_DELIVERY

Delivery/order periods are scoped by the customer's requested delivery date, never the actual delivery date. The actual-delivery-date column is populated only on fully delivered lines, so filtering a period by it structurally removes every short, missed, and open line and makes shortfalls invisible.

WHERE CUSTOMER_REQUESTED_DATE BETWEEN <period start> AND <period end> (not ACTUAL_DELIVERY_DATE)

Miss-reason percentages are computed as a share of missed lines (denominator = missed lines) by default; a second, per-opportunity variant (denominator = all scored/eligible lines) is used only when a question is about loss-per-opportunity rather than composition of misses.

Share of misses:100.0 * SUM(missed_reason_flag) / NULLIF(SUM(IS_DRM_MISS), 0). Per opportunity: 100.0 * SUM(missed_reason_flag) / NULLIF(SUM(DRM_TOTAL_LINES), 0)

A DRM miss is always derived from the reliability flag being 0, never from a delay-unmeasurable flag or rejection status. An unmeasurable delay means the delay itself cannot be measured, not that the delivery failed; excluding these rows understates the delivery-lateness population. A related pre-built "is a miss" column is not used as a substitute — it still wrongly drops unmeasurable and rejected lines that should count as misses.

Miss rate:SUM(IFF(DRM_FLAG = 0, 1, 0)) / COUNT(DRM_FLAG). Average lateness excludes unmeasurable rows: AVG(IFF(IS_DRM_MISS = 1 AND REJECTION_REASON_CODE IS NULL AND IS_ADP_UNMEASURABLE = FALSE, DELAY_DAYS_ADP, NULL))

"Late" (positive delay days) and "DRM miss" (reliability flag = 0) are different, non-interchangeable populations — a delivery can be a few days late without being a scored DRM miss, and vice versa. "How late were deliveries" questions use the lateness population; DRM/OTIF questions use the miss population.

Lateness:WHERE DELAY_DAYS_ADP > 0. Miss: WHERE DRM_FLAG = 0

A blank/NULL miss-reason bucket means no miss occurred, not "Uncategorized" — labelling it that way manufactures a data-quality problem that isn't there. Genuine, unexplained misses (a scored miss where none of the 17 coded reason flags fired) do roll into the "Uncategorized" display bucket, which is a different, real state from "no miss."

Miss-reason label derived asCOALESCE(NULLIF(TRIM(PRIMARY_DELAY_REASON), ''), 'Uncategorized'), sorted with Uncategorized last

A delivery/fill-rate gap's root cause is checked against the actual miss-reason breakdown, never inferred from open-PO backlog or low on-hand stock alone — a plant can carry a large PO backlog or thin stock and still have a delivery gap driven by outbound/administrative issues; "No stock" is consistently under 5% of miss lines even at plants with large backlogs.

Not expressible as a single filter — requires aGROUP BY MISS_REASON check before attributing a cause

Open-and-overdue order lines use a pre-built flag (open quantity plus a requested date before the snapshot anchor, see R9), never a hand-built date comparison.

WHERE OPEN_QTY > 0 AND CUSTOMER_REQUESTED_DATE < DATE '2026-07-07' on FACT_SALES_ORDER_LINE

Vendor on-time delivery rate is measured against the PO line's own committed delivery date — goods receipt on or before the planned delivery date — not against the planned-lead-time benchmark in the material-plant master, which is a separate, non-interchangeable concept (the two disagree by 34 percentage points on this data: 37.6% vs 71.7%). Reserve the lead-time-benchmark measures for questions specifically about lead-time variance against plan.

WHERE ACTUAL_GR_DATE <= PLANNED_DELIVERY_DATE on FACT_PURCHASE_ORDER_LINE

Lead-time "variance" has two distinct, non-interchangeable meanings, both exposed separately: requested-vs-actual delay, and planned-vs-actual gap against the master-data lead time. Use the latter for any "gap between actual and planned lead time" question.

Requested-vs-actual:AVG(SUPPLY_DELAY_DAYS). Planned-vs-actual: AVG(COALESCE(ACTUAL_LT_DAYS, 0) - COALESCE(PLANNED_LT_DAYS, 0))

First-goods-receipt timing is computed as the earliest posting date from the goods-movement records, joined by PO number and item — the goods-receipt date on the PO line itself is the *latest* receipt for a line received in multiple partial shipments, not the first, and using it directly for "time to first receipt" reads materially wrong (and even sign-flipped) results for some vendors.

MIN(POSTING_DATE) from FACT_GOODS_MOVEMENT grouped by PO_NUMBER, PO_ITEM, joined to the schedule date on FACT_PO_SCHEDULE (not FACT_PURCHASE_ORDER_LINE.ACTUAL_GR_DATE)

Valid goods receipt on the goods-movement records prefers movement types 101 and 102.

WHERE MOVEMENT_TYPE IN ('101','102') on FACT_GOODS_MOVEMENT

Vendor confirmation type mapping: AB = order acknowledgement, LA = shipping notification. Unless a question asks about a specific type, any row in the confirmation records counts as a valid confirmation.

Acknowledgement rows only:WHERE CONFIRMATION_TYPE = 'AB'

Vendor-confirmation-row questions must not be pre-aggregated (e.g. collapsed to one row per PO line) before comparing confirmation dates — a PO line can carry several confirmation rows (re-confirmations, partial confirmations), and collapsing to one row per line before the date comparison has been found to understate a "how many lines/vendors slipped" count by roughly half.

CompareCONFIRMED_DELIVERY_DATE at raw confirmation-row grain — no GROUP BY PO_NUMBER, PO_ITEM with MAX()/MIN() before the comparison

"How many PO lines..." confirmation-date questions use the PO line's own resolved confirmation date, not a join out to the raw confirmation records (a different, finer grain that answers a different question).

SELECT ... FROM FACT_PURCHASE_ORDER_LINE using CONFIRMED_DELIVERY_DATE directly (no join to FACT_PO_CONFIRMATION)

PO and slow-moving quantities are never summed across units of measure without segmenting by the unit-of-measure column — close to half of PO lines are recorded as sets rather than single pieces, and no single-unit-equivalent conversion is available.

GROUP BY ..., DCPO_BUOM (purchasing) or GROUP BY ..., BASE_UOM (inventory/slow-moving)

Vendor rankings require a non-null supplier name. A NULL/blank supplier name is an unresolved supplier key, not a real anonymous vendor, and can carry an extreme average that displaces a real vendor from a ranking.

WHERE DC_SUPPLIER_NAME IS NOT NULL

Open-and-overdue PO quantity uses a pre-built flag (open quantity plus a confirmed date before the snapshot anchor, see R9), never a hand-built literal-date comparison, which drifts wrong on every reload.

WHERE OPEN_QTY > 0 AND CONFIRMED_DELIVERY_DATE < DATE '2026-07-07' on FACT_PURCHASE_ORDER_LINE

Any safety-stock or reorder-point figure must state its coverage. These fields are populated on only ~1.1% of material-plant rows; a total or average built from them covers a sliver of the portfolio unless that is disclosed.

SUM(IFF(COALESCE(SAFETY_STOCK,0) > 0, 1, 0)) reported alongside COUNT(*) as the coverage denominator

Forecast error (WMAPE/MAPE) is always computed at the aggregate level over the full population, at the one-month-ahead lag by default, never as a per-row average and never excluding zero-actual rows. A per-row, actual-filtered calculation is dominated by tiny-actual outliers and reads roughly three times too high (215% vs. the correct 73%) on this data, and zero-actual rows are legitimate forecast errors, not exclusions. A separate three-month-lag figure is used specifically for the named 3-month-horizon plan-vs-actual gap metric, but is not the default for an unqualified "forecast error" question.

SUM(ABS_DEV_N1) / NULLIF(SUM(ACTUAL_DELIVERED_QTY), 0) on FACT_FORECAST_PERFORMANCE (no actual > 0 filter)

Forecast bias is volume-weighted at the aggregate level, not an unweighted average of per-row bias ratios (which is dominated by tiny-actual outliers). Positive = over-forecast, negative = under-forecast.

100.0 * (SUM(PLANNED_QTY_N) - SUM(ACTUAL_DELIVERED_QTY)) / NULLIF(SUM(ACTUAL_DELIVERED_QTY), 0)

Forecast (plan) and actuals must never be summed together — the demand-forecast records hold both in the same column, distinguished only by a source flag. Summing across both roughly triples the reported quantity on this data.

Plan:SUM(IFF(SOURCE_FILE = 'DEMAND_QXP', FORECAST_QUANTITY, 0)). Actuals: SUM(IFF(SOURCE_FILE = 'SALES_VIPP', FORECAST_QUANTITY, 0))

"Actual sales/demand" and "forecast bias by material" are answered from the demand-forecast records (source-guarded, as in R33), not from the forecast-performance records, which are a different accuracy/lag measure with a different population and quantity definition. The forecast-performance records are reserved for accuracy/lag questions (WMAPE, "how accurate was the forecast N months out").

WHERE SOURCE_FILE IN ('DEMAND_QXP','SALES_VIPP') on FACT_DEMAND_FORECAST, not FACT_FORECAST_PERFORMANCE

Demand-plan lifecycle phase and supply class are read from the demand-forecast records' own contextual columns, not the material master's global values, which disagree materially with the plan-row values (e.g. the dominant supply class on the US plan differs depending on which column is used).

SELECT PLAN_LIFECYCLE_PHASE, PLAN_SUPPLY_CLASS FROM FACT_DEMAND_FORECAST (not the equivalent columns on the material dimension)

Plant-level forecast accuracy cannot be produced. The forecast-performance records carry no plant column (grain is material × planning account × month); a cross-domain question must aggregate the other side (e.g. inventory) up to material level instead.

Not expressible — no plant column exists onFACT_FORECAST_PERFORMANCE

The forecast-performance records are already 100% US-scoped — no additional US filter is needed or possible, unlike every other fact used in this scope.

No filter required (population is already US-only)

Days of Supply is answered by a published, verified query, not a hand-derived on-hand ÷ average-daily-demand calculation from the inventory and demand records separately, which risks mismatching grain or period between the two.

Full query in Appendix A.1

Dead stock = on-hand stock held against a phased-out product, not simply stock that hasn't moved. This is the confirmed definition, and it takes precedence over two other measures that are more readily available but answer different questions: a same-named ageing-based measure that is actually built on a >12-month no-movement bucket, and a "no demand this period" cut that reflects only the current month's activity, not lifecycle phase.

WHERE ON_HAND_QTY > 0 AND LIFECYCLE_PHASE IN ('Not-active','Phase out','Phase-out Initiated') on FACT_SLOW_MOVING_INVENTORY, latest period only (R8)

Available finished-goods inventory requires manufactured-finished-good material type, unrestricted stock status, and a positive on-hand quantity.

WHERE MATERIAL_TYPE = '10MANE' AND IS_UNRESTRICTED AND ON_HAND_QTY > 0 (join FACT_INVENTORY to the material dimension)

Any days-of-supply or stock-rate KPI filters to a positive stock quantity in its denominator population — zero-stock rows produce an infinite or zero cover ratio otherwise.

WHERE ON_HAND_QTY > 0 (or GIT_QTY > 0 where relevant)

On-hand inventory is a repeating monthly snapshot (17 periods live at once) — always filtered to the latest period, or a query overstates stock roughly 17-fold by summing every snapshot together.

WHERE CALENDAR_MONTH_KEY = (SELECT MAX(CALENDAR_MONTH_KEY) FROM FACT_INVENTORY)

On-hand inventory carries multiple rows per material-plant-period (storage location, stock type, batch) — always summed, never a single row or maximum value; stock type is included in the grouping whenever availability matters, or available stock silently blends with blocked/quality-inspection stock.

SUM(ON_HAND_QTY) ... GROUP BY MATERIAL_12NC, PLANT_CODE, [STOCK_TYPE]

The pre-computed ageing buckets (0–6, 7–12, and 12+ months) already partition on-hand stock by age and are used as-is — never re-derived, and never added on top of total on-hand, which would double-count.

QTY_0_6M + QTY_7_12M + QTY_GT_12M = ON_HAND_QTY (identity, not a filter)

Healthy stock (0–6 months) and slow-moving stock (6+ months) are answered in units, from the ageing-bucket quantities. This is not the same measure as, and must not be derived as, on-hand value minus slow-moving value in currency terms.

Healthy:SUM(QTY_0_6M). Slow-moving: SUM(QTY_7_12M + QTY_GT_12M), on FACT_SLOW_MOVING_INVENTORY, latest period

SLOB% (the >12-month no-movement ratio) is a distinct value-based ratio, not the dead-stock definition (R39), and is used only when a question specifically and literally asks for the ">12-month ageing" or "SLOB" measure by that name.

SUM(VALUE_GT_12M) / NULLIF(SUM(ON_HAND_VALUE), 0) — reserved for literal SLOB/ageing questions only

"How much slow-moving stock" uses the dedicated slow-moving quantity/value columns directly, not the ageing-bucket split — the two are not interchangeable and do not sum to the same total. The ageing-bucket split is used only when a question specifically asks for the healthy-vs-aged breakdown (R45).

SUM(SLOW_MO_QTY) / SUM(SLOW_MO_VALUE) on FACT_SLOW_MOVING_INVENTORY, latest period

A negative on-hand quantity on the slow-moving records is a posting-timing exception, not physical negative stock, and is reported as its own line item — never clamped to zero and never folded into a positive total.

WHERE ON_HAND_QTY < 0 reported separately, never MAX(ON_HAND_QTY, 0)

Slow-moving stock quantity does not reconcile to the on-hand inventory quantity for the same period (runs at ~155% of it); only the value reconciles. The two are never stated as tying out to each other.

Not a filter — a reporting caveat: slow-movingON_HAND_QTY ≠ inventory ON_HAND_QTY for the same material-plant-period

Material status is always shown decoded, never as the raw system code, which means nothing to a planner.

CASE MATERIAL_STATUS WHEN 'ZF' THEN 'Stop Supply' WHEN 'ZG' THEN 'EOL, Blocked, Scrap Stock' WHEN 'ZO' THEN 'To Be Phased Out' WHEN 'ZH' THEN 'Fully deleted for plant' WHEN 'ZI' THEN 'Phase In' ELSE MATERIAL_STATUS END

Lead-time and planning-parameter averages are segmented by MRP type before blending — half the material base carries no MRP planning at all, so an unsegmented average describes no real population. The fully-planned group is the only one suited to a lead-time average; a group with no procurement type set is excluded from lead-time measures entirely.

WHERE MRP_TYPE = 'X3' for a fully-planned-only average; exclude WHERE MRP_TYPE = 'X0' AND PROCUREMENT_TYPE IS NULL from any lead-time measure

Brand/business-unit questions (Hue, WiZ, LED Lamps & Luminaires) resolve through the business-unit dimension, never through the product-family field on the material master, which holds unrelated internal family codes and is frequently blank.

JOIN DIM_BUSINESS_UNIT_BYCODE ON BU_CODE ... SELECT BU_NAME (not the material dimension's product-family field)

Different source extracts represent the same material with different code widths and padding. Every table is normalized to Signify's canonical 14-digit 12NC by prefixing 10 and left-trimming leading zeros from the source material code. Some sources already carry a canonical 12NC and need no transform.

Why: without a single normalized code, the same physical material shows up as multiple distinct "materials" depending on which source table you're looking at, breaking every downstream join and rollup.

Some source extracts carry a 4-character SAP plant code; these are prefixed with 10 to reach the canonical 6-character form used everywhere from STAGING onward.

Why: same rationale as the material code — one canonical form per physical plant, regardless of which source extract it came from.

Rows whose material code is not purely numeric are excluded before standardization. Non-numeric codes are placeholder/dummy entries (statistical postings, rebates, and similar non-physical-material rows) rather than real materials — and different source extracts use different placeholder-code conventions, so a numeric-only test is used instead of trying to enumerate every placeholder pattern by name.

Why: a real 12NC is always fully numeric; testing for that directly is robust to any placeholder naming convention a given extract happens to use, rather than chasing pattern variants one at a time.

The source key-figure column mixes physical units and monetary values in the same field. Each row's key figure is classified — by name pattern (units vs. QxP/NNS/GAS monetary indicators) and by whether a currency is present — into exactly one of a quantity column or a EUR-value column. The two are never populated on the same row and never summed together.

Why: summing a units figure together with a EUR figure produces a number with no real meaning. Routing every value through one of the two typed columns makes it structurally impossible to accidentally mix them downstream.

Demand and sales rows are filtered to the blank/default finance-posting indicator (represented in source as a literal placeholder character, not a NULL). Other codes represent rebates, credits, reclassifications, and other non-sales postings that carry a revenue value with no associated physical quantity.

Why: including those codes would inflate reported revenue with postings that were never actual sales, and — since they have no quantity — would silently break any quantity-based metric that assumes revenue and quantity move together.

Only the primary consumer distribution channel is kept for demand and sales. The DRM (delivery reliability) table is deliberately not filtered the same way — a channel used for internal sales is kept there because it is relevant to the delivery-reliability metric specifically.

Why: this is a scope decision for the pilot, not a universal rule — applying it uniformly across every table would have silently dropped data DRM needs to score correctly.

Source demand and sales extracts can carry multiple revisions of the same business record (same material, plant, period, key figure, category, and a handful of other genuinely-distinguishing attributes). Only the most recently updated revision survives; older revisions are dropped rather than summed.

Why: summing every revision as if each were an additive, independent row overstates volume — superseded versions of the same record are not new data, they're corrections to existing data.

Only the standard-delivery item category is scored for DRM/OTIF; returns, free-of-charge, and other non-standard categories are excluded from the eligible population entirely (not counted as either a hit or a miss).

Why: returns and free-of-charge lines don't represent a delivery-promise commitment in the same sense as a standard sales delivery, so scoring them would distort the reliability percentage.

A specific placeholder value in the delay-days field marks a line where the delay genuinely cannot be measured. These lines are excluded from the DRM miss/hit calculation (given NULL, not scored as either) rather than being counted as a failure.

Why: the source system flags these lines internally in a way that, left unhandled, causes every one of them to score as a miss by default — materially overstating the miss rate for a population where the outcome is actually unknown, not bad.

Lines carrying a rejection reason are excluded from DRM scoring entirely rather than counted as delivered or missed.

Why: a rejected line was never going to be delivered by design — including it in either the numerator or denominator misrepresents what DRM is meant to measure (reliability of lines that were actually supposed to ship).

A line that has not yet been delivered is only excluded from scoring while its agreed delivery date is still in the future (genuinely unknown outcome). Once the agreed date has passed with no delivery date recorded, the line is scored as a miss rather than continuing to be excluded.

Why: treating every undelivered line as "unknown, so exclude it" was silently hiding the worst cases — lines that are late and still haven't shipped — from the reliability metric, which overstated performance.

A missed delivery line can trip several of the source system's miss-reason flags simultaneously. A single primary_delay_reason and broader delay_reason_group are derived by testing the specific flags in each domain (Supply, Sales, Outbound) before that domain's catch-all/"other" flag, and testing domains in a fixed order: Supply first, then Sales, then Outbound, then Other.

Why: testing a domain's catch-all flag before its specific flags let "other" win even when a real, specific cause was also flagged on the same line — the fixed, specific-before-catch-all order ensures the most informative reason is always chosen.

The source carries a delivery-completeness indicator, but it reflects a configuration/requirement setting, not proof that a line actually shipped complete — it disagrees with actual outcomes on a meaningful share of rows. A separate, genuinely-derived completeness flag is computed instead, comparing actual delivered quantity against agreed quantity directly.

Why: using a configuration flag as if it were an outcome measurement misrepresents what "perfect order" (on-time + complete) actually means for a given line.

At any rollup grain above the individual line (material, BU, plant, period), the correct DRM percentage is SUM(drm_scored_lines) / SUM(drm_total_lines) × 100 — never a plain AVG(drm_pct) across lines.

Why: DRM percentage is a ratio, and averaging ratios weights every line equally regardless of how much volume it represents — a rollup built on AVG(drm_pct) will diverge from the true additive rate, especially where volume is unevenly distributed across lines.

A purchase order can carry two distinct supplier identities: the DC (distribution-centre) vendor, who is the actual PO counterparty, and the EP (enterprise-planning) vendor, an upstream reference. Both are exposed as separate columns rather than collapsed into one.

Why: silently substituting one for the other on a meaningful share of lines means the wrong counterparty gets attributed to a purchase order — keeping both explicit lets consumers choose the correct one for their use case.

A single DC purchase order line can legitimately split across more than one EP-PO allocation. The main PO fact is deduplicated to one row per DC-PO line (needed for correct received/open/schedule-quantity math), so the full allocation detail — which EP-POs a DC-PO line actually maps to — is preserved separately rather than being lost to the dedup.

Why: collapsing to one row per DC-PO line at the main fact's grain would arbitrarily keep only one of several real allocations, silently misrepresenting the rest as if they didn't exist.

Received quantity nets goods receipts and returns using the source system's debit/credit indicator (receipt = positive, return = negative) rather than a fixed allow-list of movement-type codes.

Why: a hardcoded movement-type list is fragile — it silently excludes valid movement types (such as purchase returns) that weren't anticipated, understating the true received quantity.

The earliest scheduled/confirmed delivery date for a PO line is computed only over schedule/confirmation records that still have quantity remaining to be delivered. Fully-consumed (already-fulfilled) schedule lines are excluded from that calculation, even if they are chronologically earlier.

Why: without this filter, an old, already-fulfilled schedule line can win the "earliest date" calculation over the actual current, active delivery commitment — producing a nearest-delivery date that is stale by months or years.

Lateness (supply_delay_days) is the actual end-to-end lead time minus the requested end-to-end lead time — release/creation of the PO through goods-receipt posting. A narrower sub-leg measurement is retained separately as a diagnostic only, and must not be used to classify a line as on-time or late.

Why: every vendor on-time/OTIF/lead-time-variability metric in the pipeline depends on this figure — measuring only one leg of the journey answers "did this sub-step land on time", not the business question "did the goods arrive when promised".

The source's own data-quality/consistency flag (which identifies purchase orders with potential master-data or process issues that could distort lead-time analysis) is carried through as a column so lead-time figures can optionally be split clean vs. suspect — but it must never be used to silently exclude rows from a query or metric.

Why: excluding flagged rows outright would silently drop real purchase orders from every downstream metric without anyone asking for that; exposing it as an attribute keeps the choice explicit and in the consumer's hands.

A material/plant/period can show a negative value on an inventory quantity or value column. This is not a data-quality defect in the general case — it results from paired reclassification postings between stock types (e.g. moving quantity from unrestricted to quality-hold status), where one stock-type bucket is decremented and another incremented for the same underlying physical inventory.

Why: treating every negative inventory figure as an error and filtering it out would silently drop legitimate reclassification activity and distort net position calculations.

On-hand inventory is split into unrestricted (freely usable) and restricted (e.g. quality-hold) quantities as distinct figures, with a restricted-ratio derived from the two, rather than exposing a single blended on-hand number.

Why: restricted stock is not available to cover demand even though it is physically on-hand — blending the two would overstate usable supply for stockout-risk and days-of-supply calculations.

Average daily demand (from recent known fiscal months) multiplied by total inbound lead-time days produces a "should-be" reorder point. A material/plant is flagged as calibrated only when its configured reorder point meets or exceeds this recalculated figure.

Why: a configured reorder point that hasn't kept pace with actual demand and lead-time patterns will trigger replenishment too late — this comparison surfaces that gap directly instead of leaving it implicit in the configuration.

No real SAP MRP exception code exists in the source extracts. A proxy flag is derived instead: true when current unrestricted stock is below the configured safety stock, or above the configured maximum order quantity.

Why: labeling this as a real SAP exception code would overstate its precision — it is a reasonable stand-in built from the parameters actually available, not a system-generated exception.

Every fact table's fiscal period code is a 7-character YYYYPPP string, where PPP is a 3-digit period number aligned 1:1 to calendar months (001 = January). DIM_FISCAL_PERIOD is the single source of truth for resolving a period code to its calendar-month and fiscal-year attributes — facts join to it rather than parsing the period code themselves.

Why: a single shared dimension for period resolution means every fact table's date logic (fiscal year boundaries, month-over-month rollups) stays consistent, instead of each table implementing its own parsing.

Business-unit-level forecast error is Mean Absolute Percentage Error — the average, over lines with positive actuals, of each line's own |deviation|/actual — computed independently at the BU grain rather than derived from summed material-level totals.

Why: dividing summed planned by summed actual across a BU blends large- and small-volume materials in a way that understates true error; averaging each line's own ratio avoids that distortion.

A material/period is flagged IS_HIGH_BIAS when the absolute forecast bias ratio at the N-3 (three-period-ahead) horizon exceeds 25% — a business-agreed threshold used to link forecast quality to downstream delivery/stockout impact.

Why: N-3 is the horizon planners act on furthest in advance, so bias there has the most opportunity to be corrected before it causes a supply problem — making it the horizon most worth flagging.

The composite supply-chain risk score sums four binary domain flags (stockout risk, no-stock delivery miss, late purchase order, high forecast bias) at 25 points each, for a 0–100 scale. Multi-domain risk is flagged when two or more domains trigger simultaneously.

Why: equal weighting is a reasonable, transparent starting point for surfacing multi-domain risk, but it has not been validated or tuned against real business priorities — treat the score as a triage signal, not a precisely calibrated ranking.