Business Problems & Data Solutions

Conceptual Data Model (CDM)
├─ Customer    ← glossary term #001
├─ Product     ← glossary term #002
├─ Store       ← glossary term #003
├─ Supplier    ← glossary term #004
├─ Promotion   ← glossary term #005
├─ Sales       ← glossary term #006
├─ Inventory   ← glossary term #007
└─ Return      ← glossary term #008

Rule: No entity enters LDM without
a glossary entry signed by the Council.

The CDM is the single source of truth for business semantics. Every entity in the CDM maps 1:1 to a glossary term. Every attribute in the LDM inherits its definition from the glossary. Every column in the PDM carries a glossary_term_id metadata tag. This means:

Naming Convention dim_customer, not tbl_cust or customer_master_v2. The name itself encodes the entity type and business domain.

Certification Gate CDM→LDM transition requires business sign-off. LDM→PDM transition requires steward approval. No physical table is born without a semantic anchor.

Quality by Design Valid values defined in the glossary become CHECK constraints in the PDM. If the glossary says customer_type IN ('B2B','B2C','B2B2C'), the PDM enforces it.

CDM (Business-approved)
  Customer, Product, Sales, Inventory...

LDM (3NF, domain-reviewed)
  Customer { id, name, type, region }
  Product  { id, name, category, uom }
  Sales    { customer, product, date, qty, amt }

PDM (Star Schema, implemented)
  dim_customer  ← SCD Type 2
  dim_product   ← SCD Type 2
  dim_date      ← role-playing (order/ship/bill)
  fact_sales    ← grain: 1 row per line item
    └ FK → dim_customer, dim_product, dim_date,
         dim_store, dim_promotion

Grain Declaration Every fact table documents its grain in the LDM metadata. fact_sales: “One row per billing line item (VBRP) per fiscal period.” Grain changes require a formal model change request; no silent redefinition.

SCD Type 2 Dimensions that need historical tracking carry effective_from_ts, effective_to_ts, is_current_flag. This is declared at CDM stage (“do we need to track changes to customer type?”), not discovered during ETL.

Conformed Dimensions dim_customer is shared across fact_sales, fact_order, fact_return, and dp_customer_360. One definition, one surrogate key, one SCD policy. Eliminates naming collisions by design.

Entity Completeness Before PDM implementation, validate: “Can we answer every business question in the use-case backlog with these entities?” Missing dim_promotion is caught at CDM review, not after 6 months of production.

dim_product (SCD Type 2)
├─ product_sk          INT         [surrogate key]
├─ product_id          VARCHAR(18) [MARA.MATNR]
├─ product_name        VARCHAR     [MAKT.MAKTX]
├─ material_type       VARCHAR     [MARA.MTART]
├─ material_group      VARCHAR     [MARA.MATKL → T023]
├─ category_code       VARCHAR     [KLAH/KSML]
├─ base_uom            VARCHAR     [MARA.MEINS]
├─ sales_uom           VARCHAR     [MVKE.VRKME]
├─ standard_cost       DECIMAL     [MBEW.STPRS]
├─ plant_code          VARCHAR     [MARC.WERKS]
├─ effective_from_ts   TIMESTAMP
├─ effective_to_ts     TIMESTAMP
└─ is_current_flag     BOOLEAN

Sources: 7 SAP tables → 1 dimension

dim_customer (SCD Type 2)
├─ customer_sk         INT         [surrogate key]
├─ customer_id         VARCHAR(10) [KNA1.KUNNR / BUT000.PARTNER]
├─ customer_name       VARCHAR     [KNA1.NAME1 / BUT000.NAME_ORG1]
├─ customer_type       VARCHAR     [B2B / B2C / B2B2C]
├─ city                VARCHAR     [KNA1.ORT01]
├─ region              VARCHAR     [KNA1.REGIO]
├─ sales_org           VARCHAR     [KNVV.VKORG]
├─ credit_limit        DECIMAL     [KNKK.KLIMK]
├─ source_system       VARCHAR     [ECC / S4HANA]
├─ effective_from_ts   TIMESTAMP
├─ effective_to_ts     TIMESTAMP
└─ is_current_flag     BOOLEAN

Sources: KNA1+KNB1+KNVV+KNVP+KNKK (ECC)
         or BUT000+roles (S/4HANA)

Graph Schema
(:SourceTable {name, module, system})
  -[:HAS_COLUMN]→
(:SourceColumn {name, datatype, pii_flag})
  -[:TRANSFORMS_INTO {rule, owner}]→
(:PDMColumn {name, entity, glossary_term_id})
  -[:BELONGS_TO]→
(:PDMEntity {name, grain, scd_type})
  -[:FEEDS]→
(:Report {name, owner, certification})

Cypher: Forward impact
MATCH (s:SourceColumn {name:"MATNR"})
      -[:TRANSFORMS_INTO*]→(r:Report)
RETURN r.name, r.owner

Every PDM column carries:
├─ source_system       [ECC / S4HANA / CRM]
├─ source_table        [e.g., KNA1]
├─ source_column       [e.g., NAME1]
├─ transformation_rule  [e.g., TRIM+UPPER]
├─ pii_classification  [PUBLIC/INTERNAL/
│                      CONFIDENTIAL/RESTRICTED]
├─ glossary_term_id    [links to CDM]
└─ data_contract_id    [links to SLA/schema]

This metadata IS the lineage backbone.
No visualization tool can replace it.

Staging Layer (source-specific)
  stg_customer_ecc     ← KNA1+KNB1+KNVV
  stg_customer_s4      ← BUT000+BP roles
       │                     │
       └────── UNION ──────┐
                  │
PDM Layer (source-agnostic)
  dim_customer (unchanged schema)
    ├─ customer_sk
    ├─ customer_id
    ├─ customer_name
    ├─ source_system  [ECC / S4HANA]
    └─ ...all other attributes

Key insight: PDM schema is STABLE.
Source changes are absorbed by staging.
Star schema consumers see no change.

Adapter Pattern The staging layer absorbs all 9 structural changes. stg_*_ecc and stg_*_s4 tables are the only place where source-specific logic exists. The PDM star schema remains stable across the entire migration.

Schema Stability fact_sales consumers query dim_customer.customer_name regardless of whether it came from KNA1.NAME1 or BUT000.NAME_ORG1. The join key is customer_sk (surrogate), not the source natural key.

Dual-Source Coexistence During migration, both adapters run in parallel. source_system column in every dimension enables filtering and reconciliation. Variance detection built into the pipeline.

This is the primary value of the CDM→LDM→PDM progression: physical tables represent business entities, not source system structures. Source systems can be swapped without reshaping the analytical layer.

Star Schema (Foundation)
  fact_sales × dim_customer × dim_product
  × dim_store × dim_date × dim_promotion

Data Product Layer (Governed Aggregates)
  dp_netsales
    ├─ date_key           [FK → dim_date]
    ├─ product_sk         [FK → dim_product]
    ├─ store_sk           [FK → dim_store]
    ├─ gross_amount       [SUM(fact_sales)]
    ├─ discount_amount    [SUM conditions]
    ├─ return_amount      [SUM(fact_return)]
    └─ net_sales          [calculated]

  Grain: product × store × day
  SLA: refreshed daily by 6:00 AM
  Owner: Finance domain lead

Single Source of Metrics Data products are first-class PDM entities, not ad-hoc views. dp_netsales is modeled with its own grain, attributes, source lineage, and refresh cadence. Every dashboard that shows “Net Sales” queries this entity.

Aggregate Fact Tables Data products are modeled as aggregate facts derived from the star schema. They pre-compute common metrics so dashboards don’t need complex joins. But unlike LIS S-tables, they are governed, versioned, and have published contracts.

ACDOCA Integration The fact_sales entity is redesigned to source from ACDOCA (single table) instead of BSEG + CE1xxxx separately. This eliminates the 3–5% reconciliation gap at the model level: one source table, one extraction, one truth.

fact_demand_signal (transactional fact)
  Grain: product × location × day
  ├─ demand_signal_sk    [surrogate key]
  ├─ product_sk          [FK → dim_product]
  ├─ store_sk            [FK → dim_store]
  ├─ date_key            [FK → dim_date]
  ├─ channel             [POS/ECOM/WHOLESALE]
  ├─ demand_qty          [units demanded]
  └─ demand_amount       [value demanded]

ml_feature_store (derived entity)
  ├─ product_sk
  ├─ store_sk
  ├─ feature_date
  ├─ rolling_7d_velocity
  ├─ rolling_30d_velocity
  ├─ promo_lift_coeff
  ├─ stockout_freq_30d
  └─ feature_version     [v1, v2, ...]

Atomic Grain fact_demand_signal at product-location-day grain captures demand from all channels in one place. This is the single input for both statistical forecasting and ML models; no more fragmented signals.

Conformed Keys Demand signals from POS, e-commerce, and wholesale all join to the same dim_product and dim_store via surrogate keys. Channel-specific attributes are measures in the fact, not separate tables.

Feature Store as PDM Entity ML features are derived attributes modeled in their own entity with versioning. They are computed from the star schema (not raw source data), making them governed, reproducible, and auditable. Feature engineering is a modeling activity, not a notebook hack.

Closed-Loop Design The PDM enables a feedback loop: fact_demand_signal → ml_feature_store → dp_demand_forecast → MARC parameters → better inventory → better demand fulfillment. The model architecture makes this loop possible.

fact_production_order (process fact)
  Grain: production order × operation
  ├─ prod_order_sk       [surrogate key]
  ├─ order_number        [AUFK.AUFNR]
  ├─ product_sk          [FK → dim_product]
  ├─ bom_sk              [FK → dim_bom (versioned)]
  ├─ work_center_sk      [FK → dim_work_center]
  ├─ planned_start_date  [AFKO]
  ├─ actual_start_date   [AFKO]
  ├─ planned_cost        [AFVC planned]
  ├─ actual_cost         [COBK/COEP actual]
  └─ variance_amount     [calculated]

dim_bom (SCD Type 2)
  ├─ bom_sk, bom_id, material_sk
  ├─ component_list (JSON or child table)
  ├─ effective_from_ts, effective_to_ts
  └─ is_current_flag

Operation-Level Grain fact_production_order at operation grain (not order-header grain) enables variance analysis per manufacturing step. “Which operation caused the cost overrun?” becomes a simple WHERE clause.

Versioned BOM dim_bom with SCD Type 2 links each production order to the BOM version active at creation time. When a BOM changes mid-quarter, old orders reference old BOM, new orders reference new BOM. Costing accuracy preserved.

Graph for Multi-Hop Traversal Batch recall is inherently a multi-hop problem: batch → production order → product → delivery → customer. Neo4j traverses this in milliseconds. The star schema handles aggregation (“total defect cost this quarter”); the graph handles traversal (“who received batch X?”).

fact_inventory_snapshot (periodic snapshot)
  Grain: product × location × batch × date
  ├─ snapshot_sk         [surrogate key]
  ├─ product_sk          [FK → dim_product]
  ├─ store_sk            [FK → dim_store]
  ├─ warehouse_sk        [FK → dim_warehouse_location]
  ├─ batch_id            [MCH1 batch number]
  ├─ date_key            [FK → dim_date]
  ├─ snapshot_time       [06:00/12:00/18:00]
  ├─ owned_qty           [MARD unrestricted]
  ├─ consignment_qty     [MKOL]
  ├─ in_transit_qty      [LIPS pending]
  ├─ reserved_qty        [MSKA]
  └─ available_to_promise [calculated]

Periodic Snapshot Kimball’s periodic snapshot pattern captures stock levels at regular intervals. Multiple stock types (owned, consignment, transit, reserved) modeled as measures in one fact, not separate tables. This unifies ShaDey’s fragmented stock views.

Batch-Level Grain Adding batch_id to the grain enables batch-level traceability. “Where is batch X right now?” becomes a fact table query, not a multi-table SAP investigation.

dim_warehouse_location A single warehouse dimension abstracts over both WM (LAGP) and EWM (/SCWM/LAGP). The same star schema works before, during, and after the EWM migration. Bin structure changes are absorbed in the dimension, not in every query.

Near-Real-Time In S/4HANA, MATDOC CDC enables the accumulating snapshot variant: instead of periodic captures, update the snapshot on every goods movement. The model supports both patterns without schema change.

Star Schema (for aggregation)
  fact_shipment
    Grain: delivery item
    ├─ supplier_sk   [FK → dim_supplier]
    ├─ product_sk    [FK → dim_product]
    ├─ store_sk      [FK → dim_store]
    ├─ date_key      [FK → dim_date]
    ├─ planned_date, actual_date
    └─ otif_flag

Graph (for traversal)
  (:Tier2Supplier)-[:SUPPLIES]→
  (:Tier1Supplier)-[:SUPPLIES]→
  (:Product)-[:STOCKED_AT]→
  (:Store)-[:SERVES]→
  (:Customer)

Graph nodes carry PDM surrogate keys
for cross-paradigm joins.

Dual Paradigm Supply chain analytics requires two modeling paradigms: star schema for aggregation (“what is our average OTIF this quarter?”) and graph for traversal (“if supplier X fails, which customers are affected?”). The key is that graph nodes carry PDM surrogate keys (supplier_sk, product_sk), enabling cross-paradigm joins.

Multi-Hop Traversal “Supplier X fails → which products affected → which stores stocked out → which customers impacted?” is a 3-hop traversal in Neo4j. In a star schema, this requires 3 self-joins with exponentially degrading performance. The graph model makes this constant-time.

Risk as a Data Product dp_supplier_risk aggregates signals from multiple PDM entities (quality, delivery, sourcing) into a single risk score. This is a derived data product, a first-class modeled entity, not an ad-hoc calculation.

dim_promotion (conformed dimension)
  ├─ promotion_sk        [surrogate key]
  ├─ promotion_id        [business key]
  ├─ promo_type          [%OFF/BOGO/FIXED/MKDN]
  ├─ mechanic_desc       [human-readable]
  ├─ start_date          [KONH.DATAB]
  ├─ end_date            [KONH.DATBI]
  └─ planned_spend       [budget allocated]

dp_margin_waterfall (derived wide table)
  Grain: product × store × month
  ├─ list_price          [PR00]
  ├─ trade_discount      [K004/K005]
  ├─ volume_rebate       [BO01-BO05]
  ├─ markdown            [final price delta]
  ├─ cogs                [MBEW.STPRS]
  └─ net_margin          [calculated]

Conformed Dimension dim_promotion is shared by fact_sales and dp_promoeff. Every sale that occurred during a promotion period is linked to the promotion via promotion_sk. This enables “was this sale promoted?” at the line-item level.

Margin Waterfall The waterfall is a derived wide table that decomposes price into named components. Each component maps to specific KONV/PRCD_ELEMENTS condition types. This makes margin leakage visible: if trade discounts grew 15% while volume only grew 3%, the model surfaces it.

Promotion Effectiveness Without dim_promotion, there is no analytical anchor for promo ROI. The dimension provides the structural capability to ask: “what was the incremental lift of this specific promotion on this specific product at this specific store?” This question is unanswerable without the modeled entity.

fact_order (transactional fact)
  Grain: order item × schedule line
  ├─ order_sk            [surrogate key]
  ├─ order_number        [VBAK.VBELN]
  ├─ customer_sk         [FK → dim_customer]
  ├─ product_sk          [FK → dim_product]
  ├─ store_sk            [FK → dim_store]
  ├─ order_date_key      [FK → dim_date (role)]
  ├─ delivery_date_key   [FK → dim_date (role)]
  ├─ requested_qty       [VBAP.KWMENG]
  ├─ confirmed_qty       [VBEP.BMENG]
  ├─ net_value           [VBAP.NETWR]
  ├─ lifecycle_state      [CREATED/CONFIRMED/
  │                      DELIVERED/BILLED/COMPLETE]
  └─ perfect_order_flag  [calculated]

Transactional Fact fact_order at order-line grain joins data from 4 SAP document categories (VBAK/VBAP/VBEP/VBKD) into one fact. The lifecycle_state column tracks each order through its states. This replaces the manual VBAK→LIKP→VTTK chain investigation.

Role-Playing Dimensions dim_date is used twice: once as order_date_key and once as delivery_date_key. This Kimball pattern enables “orders placed in January but delivered in March” analysis without self-joins.

Graph for Network Optimization The fulfillment graph overlays inventory data (dp_inventory_position) onto the network topology. This is fundamentally a shortest-path problem; the graph model enables real-time routing that the star schema cannot.

dp_customer_360 (wide aggregate table)
  Grain: one row per customer
  ├─ customer_sk          [FK → dim_customer]
  ├─ total_lifetime_value [SUM(fact_sales)]
  ├─ total_orders         [COUNT(fact_order)]
  ├─ avg_basket_value     [AVG order value]
  ├─ days_since_last_order[recency]
  ├─ top_categories       [JSON array]
  ├─ return_rate          [returns/orders]
  ├─ channel_mix          [POS/ECOM/WHOLESALE %]
  ├─ credit_utilization   [from dim_customer]
  ├─ segment_id           [ML-assigned]
  └─ churn_probability    [ML prediction]

ml_training_snapshot
  ├─ model_id, model_version
  ├─ customer_sk, prediction_date
  ├─ churn_probability, segment_id
  └─ feature_vector_hash

Wide Aggregate Table dp_customer_360 pre-computes all customer metrics into a single row. This is a modeled data product, not an ad-hoc query. It aggregates from fact_sales, fact_order, fact_return, and dim_customer through documented transformations. Every customer question starts here.

ML as a Modeling Layer Segment labels and churn probabilities are derived attributes that flow back into the model. dim_customer.segment_id becomes a filter dimension: “show me sales trends for At-Risk customers.” ML outputs are first-class modeled entities with versioning, not ephemeral notebook outputs.

Graph for Affinity Customer-product affinity is a bipartite graph problem. Neo4j’s GDS library computes Jaccard similarity and community detection natively. The graph complements the star schema: the star schema answers “what did this customer buy?”; the graph answers “which customers are similar?”

fact_return (multi-source transactional fact)
  Grain: one row per returned line item
  ├─ return_sk            [surrogate key]
  ├─ return_order_number  [VBAK.VBELN type RE]
  ├─ customer_sk          [FK → dim_customer]
  ├─ product_sk           [FK → dim_product]
  ├─ store_sk             [FK → dim_store]
  ├─ return_reason_sk     [FK → ref_return_reason]
  ├─ return_date_key      [FK → dim_date]
  ├─ returned_qty         [LIPS.LFIMG]
  ├─ credit_amount        [VBRP.NETWR type G2]
  ├─ batch_id             [MSEG.CHARG]
  ├─ source_doc_type      [RE/LR/G2/651]
  └─ original_order_sk    [FK → fact_order]

ref_return_reason (junk dimension)
  ├─ return_reason_sk
  ├─ reason_level_1  [Defective/Wrong/...]
  ├─ reason_level_2  [subcategory]
  └─ reason_level_3  [specific code]

Multi-Source Fact fact_return unions 4 disparate SAP document types into a single fact with a source_doc_type discriminator. The grain (one row per returned line item) is consistent regardless of source. This is the modeling solution to the “returns are not a first-class entity” problem.

Junk Dimension for Reasons ref_return_reason uses the Kimball junk dimension pattern to standardize free-text and locally-maintained reason codes into a governed hierarchy. Every return in fact_return has a foreign key to this reference dimension, enabling return-reason analytics across all channels and document types.

Cross-Fact Linkage fact_return.original_order_sk links back to fact_order, enabling: return rate = COUNT(fact_return) / COUNT(fact_order) per customer, per product, per period. This cross-fact relationship is only possible because both facts share conformed dimensions.

Graph for Root Cause The Neo4j return-to-root-cause graph traverses: return → product → batch → supplier. Pattern detection: “80% of returns for this SKU come from this supplier’s batches” is a 3-hop query. The star schema quantifies the problem; the graph finds the cause.