SAP ECC to PDM Source-to-Target Mapping | ShaDey Inc. Data Documentation

dim_date Reference

Calendar dimension generated from a date spine script. No direct SAP source extraction. Fiscal calendar attributes aligned to ShaDey Inc.'s fiscal year (February start). Holiday calendar from reference data.

PDM Attribute	Type	SAP ECC Source	Transformation	Confidence
`PK date_sk`	INT	--	Generated YYYYMMDD integer key from calendar script	Explicit
`full_date`	DATE	--	Calendar generation script (date spine from start to end)	Explicit
`day_of_week`	VARCHAR(10)	--	DAYNAME(full_date)	Explicit
`month_name`	VARCHAR(10)	--	MONTHNAME(full_date)	Explicit
`quarter`	CHAR(2)	--	Q + QUARTER(full_date)	Explicit
`year`	INT	--	YEAR(full_date)	Explicit
`fiscal_year`	INT	--	ABC fiscal calendar (February start). Custom logic.	Explicit
`fiscal_period`	VARCHAR(10)	--	ABC fiscal P01-P13 mapping	Explicit
`is_holiday`	BOOLEAN	DWH ref_holiday_calendar	Lookup against reference holiday calendar table	Explicit
`is_weekend`	BOOLEAN	--	DAYOFWEEK(full_date) IN (1,7)	Explicit

Grain & Cardinality

One row per calendar date. No SAP extraction required. Fiscal periods should align with SAP fiscal year variant (e.g., V3 for Feb-start) configured in the ECC system.

dim_customer Core Dimension

Customer dimension with SCD Type 2 history tracking. Sources from SAP ECC customer master (KNA1), CRM contacts, and e-commerce user records. Supports multi-source customer matching.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK customer_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`customer_id`	UUID	`KNA1.KUNNR`	Cast KUNNR to UUID. Secondary: ECOM users.user_id. Cross-reference via customer matching.	Explicit
`full_name`	VARCHAR(200)	`KNA1.NAME1` + `NAME2`	CONCAT(NAME1, ' ', NAME2). Secondary: CRM contacts.full_name	Explicit
`email`	VARCHAR(255)	CRM / ECOM	CRM: contacts.email_primary; ECOM: users.email. Not natively in KNA1.	Explicit
`customer_type`	VARCHAR(20)	`KNA1.KTOKD`	Map account group via ref_account_group lookup (e.g., 0001=B2C, 0002=B2B)	Explicit
`loyalty_tier`	VARCHAR(20)	CRM	CRM: loyalty_program.current_tier. Not in ECC core.	Explicit
`is_active_flag`	BOOLEAN	--	CALC: EXISTS fact_sales WHERE date > DATEADD(month, -12, GETDATE())	Explicit
`registration_date`	DATE	`KNA1.ERDAT`	Customer creation date. Secondary: ECOM users.created_at	Explicit
`effective_from`	TIMESTAMP	--	SCD Type 2 row start timestamp (ETL-managed)	Explicit
`effective_to`	TIMESTAMP	--	SCD Type 2 row end (NULL = current row)	Explicit
`is_current`	BOOLEAN	--	SCD Type 2 current row flag	Explicit

Grain & Business Rules

One row per customer per SCD Type 2 version. Active customers determined by sales activity within 12 months. Customer matching across SAP, CRM, and e-commerce requires a golden record strategy (MDM or probabilistic matching). KNA1.KUNNR is the primary business key from SAP.

dim_product Core Dimension

Product/article dimension with SCD Type 2 history. Sources primarily from SAP material master (MARA, MAKT, MARC, MBEW) with enrichment from pricing conditions (KONP) and source lists (EORD).

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK product_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`product_id`	UUID	`MARA.MATNR`	Cast MATNR to UUID	Explicit
`sku`	VARCHAR(30)	`MARA.MATNR`	Formatted as [DEPT]-[CAT]-[SEQ] pattern	Explicit
`product_name`	VARCHAR(200)	`MAKT.MAKTX`	WHERE SPRAS = 'EN' (English language key)	Explicit
`category`	VARCHAR(100)	`T023.WGBEZ`	JOIN MARA.MATKL = T023.MATKL for material group text	Explicit
`department`	VARCHAR(100)	`T023.WGBEZ`	Parent group lookup from material group hierarchy	Explicit
`brand`	VARCHAR(100)	`MARA.BRAND_ID`	JOIN to brand_master reference table	Explicit
`FK supplier_sk`	BIGINT	`EORD`	Lookup dim_supplier.supplier_sk via EORD source list (primary vendor)	Explicit
`unit_cost`	DECIMAL(10,2)	`MBEW.STPRS`	Standard price from material valuation	Explicit
`unit_price`	DECIMAL(10,2)	`KONP.KBETR`	WHERE KSCHL = 'PR00' (list price condition type)	Explicit
`is_current`	BOOLEAN	--	SCD Type 2 current flag	Explicit
`effective_from`	TIMESTAMP	--	SCD Type 2 row start	Explicit
`effective_to`	TIMESTAMP	--	SCD Type 2 row end	Explicit

Key Joins

MARA.MATNR → MAKT.MATNR (WHERE SPRAS='EN') for name. MARA.MATKL → T023.MATKL for category. MARA.MATNR → EORD.MATNR (WHERE fixed vendor) for supplier. MARA.MATNR → MBEW.MATNR for cost. KONP joined via KONH access sequence for list price.

dim_supplier Core Dimension

Supplier/vendor dimension with SCD Type 2 history. Sources from SAP vendor master (LFA1) with enrichment from purchasing info records (EINA) and quality inspections.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK supplier_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`supplier_id`	UUID	`LFA1.LIFNR`	Cast LIFNR to UUID	Explicit
`supplier_name`	VARCHAR(200)	`LFA1.NAME1` + `NAME2`	CONCAT(NAME1, ' ', NAME2)	Explicit
`supplier_type`	VARCHAR(20)	`LFA1.KTOKK`	Map vendor account group via ref_vendor_group lookup	Explicit
`country_code`	CHAR(3)	`LFA1.LAND1`	Direct mapping	Explicit
`lead_time_days`	INT	`EINA.APLFZ`	Planned delivery time from purchasing info record	Explicit
`quality_rating`	DECIMAL(3,2)	DWH aggregation	AVG(fact_quality_inspection.score) over last 4 quarters	Explicit
`is_certified`	BOOLEAN	`QMEL`	Quality notification status check (no open critical notifications)	Explicit
`is_current`	BOOLEAN	--	SCD Type 2 current flag	Explicit

dim_store Core Dimension

Store/channel dimension sourced from SAP plant master (T001W). Each retail store and distribution centre is modelled as an SAP plant.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK store_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`store_id`	UUID	`T001W.WERKS`	Cast WERKS to UUID	Explicit
`store_name`	VARCHAR(200)	`T001W.NAME1`	Direct mapping	Explicit
`store_type`	VARCHAR(20)	`T001W.FABKL`	Map factory calendar via ref_plant_type lookup	Explicit
`region`	VARCHAR(100)	`T001W.REGIO`	JOIN T005S for region text: T001W.LAND1 + T001W.REGIO = T005S key	Explicit
`city`	VARCHAR(100)	`T001W.ORT01`	Direct mapping	Explicit
`country_code`	CHAR(3)	`T001W.LAND1`	Direct mapping	Explicit
`open_date`	DATE	`T001W.ZOPNDT`	Custom field (Z-field) for store opening date	Explicit
`is_current`	BOOLEAN	--	SCD Type 2 current flag	Explicit

Assumption

T001W is inferred from the PDM's source mapping references to "SAP:T001W.WERKS". The open_date field references a custom Z-field (ZOPNDT) which is ShaDey Inc.-specific and would need to be confirmed in the SAP data dictionary.

dim_promotion Core Dimension

Promotion dimension sourced from SAP pricing condition records (KONH/KONP). Maps promotional pricing conditions to business-friendly promotion attributes.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK promotion_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`promotion_id`	UUID	`KONP.KNUMH` + `KOPOS`	Composite key from condition record number + position. Secondary: promo_mgmt.campaign_id	Explicit
`promotion_name`	VARCHAR(200)	`KONH.VTEXT`	Condition record description text. Secondary: promo_mgmt.campaign_name	Explicit
`type`	VARCHAR(30)	`KONP.KSCHL`	Map condition type: ZP01=Discount, ZP02=BOGO, etc. (ABC-specific Z-conditions)	Explicit
`start_date`	DATE	`KONP.DATAB`	Condition validity start date	Explicit
`end_date`	DATE	`KONP.DATBI`	Condition validity end date	Explicit
`discount_pct`	DECIMAL(5,2)	`KONP.KBETR`	KBETR / 10 (SAP stores percentage conditions scaled by 10)	Explicit
`is_current`	BOOLEAN	--	SCD Type 2 current flag	Explicit

Data Quality Note

SAP stores percentage condition values scaled by 10 (e.g., 15% is stored as 150 in KBETR). The transformation must divide by 10. Condition type codes (ZP01, ZP02) are ShaDey Inc.-specific and may differ from SAP standard condition types.

fact_order Fact Table

Sales order fact sourced from SAP sales document header (VBAK) and status (VBUK). One row per sales order. Grain: order level.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK order_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`FK customer_sk`	BIGINT	`VBAK.KUNNR`	Lookup dim_customer.customer_sk via KUNNR	Explicit
`FK order_date_sk`	INT	`VBAK.AUDAT`	Lookup dim_date.date_sk from AUDAT (document date)	Explicit
`FK store_sk`	BIGINT	`VBAK.WERKS`	Lookup dim_store.store_sk from WERKS (delivering plant)	Explicit
`status`	VARCHAR(20)	`VBUK.GBSTK`	Map: A=Pending, B=Partial, C=Complete	Explicit
`channel`	VARCHAR(20)	`VBAK.AUGRU`	Map order reason to channel. Secondary: ECOM orders.channel	Explicit
`total_amount`	DECIMAL(12,2)	`VBAK.NETWR`	Net value of the order. Secondary: ECOM orders.total	Explicit

fact_inventory_snapshot Fact Table

Periodic inventory snapshot sourced from SAP storage location stock (MARD). Grain: product + store + snapshot date.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK inventory_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`FK product_sk`	BIGINT	`MARD.MATNR`	Lookup dim_product.product_sk via MATNR	Explicit
`FK store_sk`	BIGINT	`MARD.WERKS`	Lookup dim_store.store_sk via WERKS	Explicit
`FK date_sk`	INT	--	dim_date.date_sk from snapshot run date (ETL schedule)	Explicit
`quantity_on_hand`	INT	`MARD.LABST`	Unrestricted stock quantity	Explicit
`quantity_reserved`	INT	`MARD.RESBE`	Reserved stock quantity	Explicit
`quantity_in_transit`	INT	`MARD.UMLME`	Stock in transit quantity	Explicit

Snapshot Strategy

MARD provides current-state stock. The DWH ETL captures daily snapshots at a fixed time (e.g., midnight) to build historical inventory positions. MARD aggregates across storage locations per plant; for storage-location-level detail, join MARD on MATNR+WERKS+LGORT.

fact_sales Fact Table

Sales transaction fact sourced from SAP billing documents (VBRK/VBRP) and POS transactions. Grain: billing document item level (one row per product per billing document).

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK sales_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`FK customer_sk`	BIGINT	`VBRK.KUNAG`	Lookup dim_customer.customer_sk via sold-to party	Explicit
`FK product_sk`	BIGINT	`VBRP.MATNR`	Lookup dim_product.product_sk via material number	Explicit
`FK store_sk`	BIGINT	`VBRP.WERKS`	Lookup dim_store.store_sk via plant	Explicit
`FK promotion_sk`	BIGINT	`KONV`	Lookup dim_promotion.promotion_sk via KONV condition records on billing doc	Explicit
`FK order_date_sk`	INT	`VBRK.FKDAT`	Lookup dim_date.date_sk from billing date	Explicit
`quantity`	DECIMAL(10,3)	`VBRP.FKIMG`	Billed quantity. Secondary: POS txn_line.qty	Explicit
`unit_price`	DECIMAL(10,2)	`VBRP.NETPR`	Net price per unit. Secondary: POS txn_line.unit_price	Explicit
`line_total`	DECIMAL(12,2)	`VBRP.NETWR`	Net value of line item. Secondary: POS txn_line.ext_price	Explicit
`currency_code`	CHAR(3)	`VBRK.WAERK`	Document currency. Secondary: POS txn_header.currency	Explicit

fact_shipment Fact Table

Shipment/delivery fact sourced from SAP delivery documents (LIKP) and external TMS. Grain: delivery document level.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK shipment_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`FK order_sk`	BIGINT	`LIKP.VBELN`	Lookup fact_order.order_sk via delivery-to-order document flow	Explicit
`FK store_sk`	BIGINT	`LIKP.WERKS`	Lookup dim_store.store_sk via shipping plant	Explicit
`FK ship_date_sk`	INT	`LIKP.WADAT_IST`	Lookup dim_date.date_sk from actual goods issue date	Explicit
`carrier_code`	VARCHAR(20)	`LIKP.ROUTE`	Route code. Secondary: TMS shipment.carrier_scac	Explicit
`tracking_number`	VARCHAR(50)	TMS / 3PL	External tracking from TMS shipment.tracking_id or 3PL API	Explicit
`delivery_date`	DATE	`LIKP.LFDAT`	Planned delivery date. Secondary: TMS shipment.pod_date	Explicit

fact_return Fact Table

Returns fact sourced from SAP return orders (VBAK doc type RE) and credit memos (VBRK billing type G2). Grain: return order level.

PDM Attribute	Type	SAP ECC Source	Transformation / Join Logic	Confidence
`PK return_sk`	BIGINT	--	DWH surrogate key sequence	Explicit
`FK original_order_sk`	BIGINT	`VBAK.AUGRU`	Lookup fact_order.order_sk via reference document (return-to-original link)	Explicit
`FK product_sk`	BIGINT	`VBAP.MATNR`	Lookup dim_product.product_sk via return order item material	Explicit
`FK customer_sk`	BIGINT	`VBAK.KUNNR`	Lookup dim_customer.customer_sk via return order sold-to	Explicit
`FK return_date_sk`	INT	`VBAK.AUDAT`	dim_date.date_sk from return order document date (RE type)	Explicit
`reason_code`	VARCHAR(20)	`VBAK.AUGRU`	Return reason code. Secondary: ECOM returns.reason	Explicit
`refund_amount`	DECIMAL(12,2)	`VBRK.NETWR`	Credit memo net value (billing type G2). Secondary: ECOM refunds.amount	Explicit

Data Products

Data products are derived from core DWH entities (dimensions and facts) rather than sourced directly from SAP ECC. Their SAP lineage flows through the core entities above. The mappings below show the DWH-level derivation; for the ultimate SAP source, trace through the parent entity mappings.

dp_customer_360 Data Product

Unified customer profile combining transactional metrics, behavioural signals, and ML-derived scores. Daily refresh. Ultimate SAP lineage: KNA1 → dim_customer → fact_sales → dp_customer_360.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK customer_sk`	BIGINT	dim_customer.customer_sk	WHERE is_current = TRUE	KNA1.KUNNR
`total_orders`	INT	fact_order	COUNT(DISTINCT order_sk) WHERE status <> 'Cancelled'	VBAK
`total_spend`	DECIMAL(14,2)	fact_sales	SUM(line_total) all-time	VBRP.NETWR
`avg_order_value`	DECIMAL(10,2)	Calculated	total_spend / NULLIF(total_orders, 0)	--
`first_purchase_date`	DATE	fact_sales + dim_date	MIN(dim_date.full_date) via order_date_sk	VBRK.FKDAT
`last_purchase_date`	DATE	fact_sales + dim_date	MAX(dim_date.full_date) via order_date_sk	VBRK.FKDAT
`preferred_category`	VARCHAR(100)	fact_sales + dim_product	MODE(dim_product.category) by frequency	T023.WGBEZ via MARA.MATKL
`churn_risk_score`	DECIMAL(5,4)	ml_feature_store	WHERE feature_name = 'churn_risk_90d'	ML pipeline
`ltv_predicted_12m`	DECIMAL(12,2)	ml_feature_store	WHERE feature_name = 'ltv_next_12m'	ML pipeline
`customer_segment`	VARCHAR(50)	dp_customer_segmentation	segment_name	ML pipeline
`snapshot_date`	DATE	Calculated	ETL run date (daily refresh)	--

dp_product_catalog Data Product

Enriched product catalog combining master data with sales velocity, pricing, and customer ratings. Ultimate SAP lineage: MARA/MAKT/MBEW → dim_product → dp_product_catalog.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK product_sk`	BIGINT	dim_product.product_sk	WHERE is_current = TRUE	MARA.MATNR
`sku`	VARCHAR(30)	dim_product.sku	Direct	MARA.MATNR
`product_name`	VARCHAR(200)	dim_product.product_name	Direct	MAKT.MAKTX
`category_l1`	VARCHAR(100)	dim_product.department	Top level (department)	T023 hierarchy
`category_l2`	VARCHAR(100)	dim_product.category	Mid level (category)	T023.WGBEZ
`brand`	VARCHAR(100)	dim_product.brand	Direct	MARA.BRAND_ID
`supplier_name`	VARCHAR(200)	dim_supplier.supplier_name	JOIN via dim_product.supplier_sk	LFA1.NAME1
`current_price`	DECIMAL(10,2)	dim_product.unit_price	WHERE is_current = TRUE	KONP.KBETR (PR00)
`cost`	DECIMAL(10,2)	dim_product.unit_cost	WHERE is_current = TRUE	MBEW.STPRS
`margin_pct`	DECIMAL(5,2)	Calculated	(current_price - cost) / NULLIF(current_price, 0) * 100	--
`velocity_rank`	INT	fact_sales	RANK() OVER (ORDER BY SUM(quantity) DESC) last 30d	VBRP.FKIMG
`avg_rating`	DECIMAL(3,2)	ECOM product_reviews	AVG(rating) by product_id	External
`review_count`	INT	ECOM product_reviews	COUNT(*) by product_id	External

dp_net_sales_daily Data Product

Daily net sales metric aggregated by store. Grain: date + store. Ultimate SAP lineage: VBRK/VBRP → fact_sales/fact_return → dp_net_sales_daily.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK date_sk`	INT	fact_sales.order_date_sk	Group by date	VBRK.FKDAT
`PK store_sk`	BIGINT	fact_sales.store_sk	Group by store	VBRP.WERKS
`gross_sales`	DECIMAL(14,2)	fact_sales	SUM(line_total) GROUP BY date_sk, store_sk	VBRP.NETWR
`return_amount`	DECIMAL(14,2)	fact_return	SUM(refund_amount) GROUP BY date_sk, store_sk	VBRK.NETWR (G2)
`discount_amount`	DECIMAL(14,2)	fact_sales + dim_promotion	SUM(line_total * discount_pct / 100)	KONP.KBETR
`tax_amount`	DECIMAL(14,2)	Calculated	gross_sales * ref_tax_rate.rate by store jurisdiction	MLAN tax class
`net_sales`	DECIMAL(14,2)	Calculated	gross_sales - return_amount - discount_amount - tax_amount	--
`transaction_count`	INT	fact_sales	COUNT(DISTINCT sales_sk)	VBRK count

dp_sales_dailyData Product

Daily sales performance by date + store + product. Grain: date + store + product. Aggregated from fact_sales.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK date_sk`	INT	fact_sales.order_date_sk	Group by date	VBRK.FKDAT
`PK store_sk`	BIGINT	fact_sales.store_sk	Group by store	VBRP.WERKS
`PK product_sk`	BIGINT	fact_sales.product_sk	Group by product	VBRP.MATNR
`total_qty`	DECIMAL(12,3)	fact_sales	SUM(quantity)	VBRP.FKIMG
`gross_amount`	DECIMAL(14,2)	fact_sales	SUM(line_total)	VBRP.NETWR
`transaction_count`	INT	fact_sales	COUNT(sales_sk)	VBRK count
`avg_unit_price`	DECIMAL(10,2)	Calculated	gross_amount / NULLIF(total_qty, 0)	--
`avg_basket_size`	DECIMAL(10,2)	fact_order	AVG(total_amount) by store, date	VBAK.NETWR

dp_inventory_positionData Product

Current inventory position with days-of-supply and reorder signals. Grain: product + store (latest snapshot). Ultimate SAP lineage: MARD → fact_inventory_snapshot → dp_inventory_position.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK product_sk`	BIGINT	fact_inventory_snapshot	Latest date_sk	MARD.MATNR
`PK store_sk`	BIGINT	fact_inventory_snapshot	Latest date_sk	MARD.WERKS
`on_hand`	INT	fact_inventory_snapshot.quantity_on_hand	Latest snapshot	MARD.LABST
`reserved`	INT	fact_inventory_snapshot.quantity_reserved	Latest snapshot	MARD.RESBE
`in_transit`	INT	fact_inventory_snapshot.quantity_in_transit	Latest snapshot	MARD.UMLME
`available`	INT	Calculated	on_hand - reserved	--
`avg_daily_sales_30d`	DECIMAL(8,2)	dp_sales_daily	SUM(total_qty) / 30 over last 30 days	VBRP.FKIMG
`days_of_supply`	DECIMAL(6,1)	Calculated	available / NULLIF(avg_daily_sales_30d, 0)	--
`reorder_flag`	BOOLEAN	Calculated	days_of_supply < safety_stock_days_threshold	MARC.MINBE (reorder point)

dp_supplier_scorecardData Product

Supplier performance scorecard by fiscal period. Grain: supplier + period. Aggregated from shipments, returns, and purchase orders.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK supplier_sk`	BIGINT	dim_supplier	WHERE is_current = TRUE	LFA1.LIFNR
`PK period`	VARCHAR(10)	dim_date	fiscal_year + fiscal_period	--
`on_time_delivery_pct`	DECIMAL(5,2)	fact_shipment	COUNT(WHERE delivery ≤ promise) / COUNT() 100	LIKP.LFDAT vs VBEP.EDATU
`quality_defect_rate`	DECIMAL(5,4)	fact_return + fact_sales	COUNT(returns WHERE reason = quality) / COUNT(sales) * 100	VBAK.AUGRU + VBRP
`lead_time_avg_days`	DECIMAL(5,1)	fact_shipment + fact_order	AVG(DATEDIFF(delivery - order_date))	LIKP + VBAK dates
`cost_variance_pct`	DECIMAL(5,2)	Calculated	(actual_cost - contracted_cost) / contracted_cost * 100	EKPO prices
`fill_rate_pct`	DECIMAL(5,2)	fact_order + fact_shipment	SUM(delivered_qty) / SUM(ordered_qty) * 100	LIPS + VBAP qtys
`overall_score`	DECIMAL(3,2)	Calculated	Weighted avg (OTD0.3 + Quality0.25 + Lead0.2 + Cost0.15 + Fill*0.1)	--

dp_store_analyticsData Product

Daily store performance analytics. Grain: store + date. Combines sales data with footfall from POS and shrinkage from WMS.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK store_sk`	BIGINT	dim_store	Direct	T001W.WERKS
`PK date_sk`	INT	dim_date	Direct	--
`revenue`	DECIMAL(14,2)	dp_net_sales_daily	SUM(net_sales)	VBRP.NETWR
`footfall`	INT	POS store_traffic	visitor_count daily total	External (POS)
`transactions`	INT	dp_net_sales_daily	SUM(transaction_count)	VBRK count
`conversion_rate`	DECIMAL(5,2)	Calculated	transactions / NULLIF(footfall, 0) * 100	--
`avg_basket`	DECIMAL(10,2)	Calculated	revenue / NULLIF(transactions, 0)	--
`sales_per_sqft`	DECIMAL(10,2)	Calculated	revenue / dim_store.square_footage	T001W custom field
`shrinkage_pct`	DECIMAL(5,2)	WMS shrinkage_report	loss_value / revenue * 100	External (WMS)

dp_promotion_effectivenessData Product

Promotion lift analysis comparing pre-promotion baseline with promotional period sales. Grain: promotion + product. Computed post-promotion.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK promotion_sk`	BIGINT	dim_promotion	Direct	KONP.KNUMH
`PK product_sk`	BIGINT	dim_product	Direct	MARA.MATNR
`baseline_qty`	DECIMAL(10,2)	dp_sales_daily	AVG(total_qty) 4 weeks pre-promo	VBRP.FKIMG
`promo_qty`	DECIMAL(10,2)	dp_sales_daily	AVG(total_qty) during promo period	VBRP.FKIMG
`lift_pct`	DECIMAL(5,2)	Calculated	(promo_qty - baseline_qty) / NULLIF(baseline_qty, 0) * 100	--
`discount_cost`	DECIMAL(12,2)	fact_sales + dim_promotion	SUM(line_total * discount_pct / 100) during promo	KONP.KBETR
`incremental_revenue`	DECIMAL(12,2)	Calculated	(promo_qty - baseline_qty) * avg_unit_price * promo_days	--
`roi`	DECIMAL(8,2)	Calculated	(incremental_revenue - discount_cost) / NULLIF(discount_cost, 0) * 100	--

dp_order_fulfillmentData Product

Order fulfillment tracking: order-to-ship and order-to-deliver metrics with on-time flags. Grain: one row per order.

PDM Attribute	Type	DWH Source	Transformation	Ultimate SAP Source
`PK order_sk`	BIGINT	fact_order	Direct	VBAK.VBELN
`promise_date`	DATE	SAP direct	`VBEP.EDATU` (schedule line confirmed date)	VBEP.EDATU
`days_to_ship`	INT	Calculated	DATEDIFF(day, order_date, ship_date)	--
`days_to_deliver`	INT	Calculated	DATEDIFF(day, order_date, delivery_date)	--
`on_time_flag`	BOOLEAN	Calculated	delivery_date ≤ promise_date	--
`complete_flag`	BOOLEAN	Calculated	ALL line items shipped	--
`return_flag`	BOOLEAN	fact_return	EXISTS WHERE original_order_sk = order_sk	VBAK (RE type)

ML / AI Data Products

Machine learning data products are derived from DWH entities and ML pipeline outputs. They have no direct SAP ECC source tables; their SAP lineage is indirect through the core dimensions and facts.

dp_customer_segmentationML/AI

RFM-based customer segmentation with ML-derived cluster assignments and propensity scores. Trained quarterly via k-means clustering.

PDM Attribute	Type	Source	Notes
`PK customer_sk`	BIGINT	dp_customer_360.customer_sk	Current customers only
`segment_id`	INT	ML: k-means cluster_id	k=6, trained quarterly
`segment_name`	VARCHAR(50)	ML: cluster label mapping	Champions, Loyal, At Risk, Lost, etc.
`rfm_recency`	INT	dp_customer_360.days_since_last_purchase	Recency score
`rfm_frequency`	INT	dp_customer_360.total_orders	Frequency score
`rfm_monetary`	DECIMAL(14,2)	dp_customer_360.total_spend	Monetary value
`propensity_score`	DECIMAL(5,4)	ml_feature_store	WHERE feature_name = 'purchase_propensity'
`model_version`	VARCHAR(20)	ML: model registry	Version tag (e.g., v2.3.1)

dp_demand_forecastML/AI

ML-driven demand forecast at product + store + day grain with 90-day horizon. Prophet/LightGBM ensemble model.

PDM Attribute	Type	Source	Notes
`PK product_sk`	BIGINT	dim_product	All active products
`PK store_sk`	BIGINT	dim_store	All active stores
`PK forecast_date_sk`	INT	dim_date (future)	Future dates, 90-day horizon
`forecast_qty`	DECIMAL(10,2)	ML pipeline output	Prophet/LightGBM ensemble, daily grain
`confidence_low`	DECIMAL(10,2)	ML pipeline output	80% prediction interval lower bound
`confidence_high`	DECIMAL(10,2)	ML pipeline output	80% prediction interval upper bound
`model_version`	VARCHAR(20)	ML: model registry	Version tag for reproducibility

ml_feature_storeML/AI

Centralised feature store for all ML features. Entity-key-value structure supporting point-in-time feature retrieval. No direct SAP source.

PDM Attribute	Type	Source	Notes
`PK feature_sk`	BIGINT	SEQ	DWH surrogate key
`feature_name`	VARCHAR(100)	ML: Feature registry	e.g., churn_risk_90d, ltv_next_12m
`feature_value`	DECIMAL(18,6)	ML pipeline output	Computed feature value
`entity_key`	VARCHAR(100)	DWH entity PK	customer_id, product_id, etc.
`entity_type`	VARCHAR(50)	ML: entity enum	customer, product, store, order
`event_timestamp`	TIMESTAMP	Calculated	Point-in-time for feature correctness
`version`	INT	ML: Feature registry	Auto-incremented feature version

ml_training_snapshotML/AI

Immutable snapshots of ML training datasets for reproducibility and audit. No direct SAP source.

PDM Attribute	Type	Source	Notes
`PK dataset_sk`	BIGINT	SEQ	DWH surrogate key
`model_name`	VARCHAR(100)	ML: model registry	Model identifier
`created_at`	TIMESTAMP	Calculated	Snapshot creation timestamp (immutable)
`feature_version`	INT	ml_feature_store.version	Feature version at time of snapshot
`row_count`	INT	Calculated	COUNT(*) of snapshotted rows
`label_column`	VARCHAR(50)	ML: model config	Target variable definition
`train_split_pct`	DECIMAL(3,2)	ML: model config	Train/test split ratio
`source_query_hash`	VARCHAR(64)	Calculated	SHA256 of the SQL that generated the dataset
`is_pii_free`	BOOLEAN	Calculated	Automated PII scan result (must be TRUE)