SAP Product Cost & Production Variance Analytics data model
Kimball warehouse for SAP CO-PC product costing: a standard cost component split fact (KEKO/KEPH) at material × plant × costing-run × cost-component grain carrying fixed/variable portions and MBEW prices, plus a production-order variance/settlement fact (aggregated from COEP) at production-order × cost-element × variance-category grain carrying target/actual/total variance, scrap, WIP, and settled amounts. The cost component is a conformed dimension so the cost structure is drillable; the variance category dimension classifies each settlement variance; material is SCD2 to preserve as-was costing attributes; costing numbers and order/settlement documents are degenerate dimensions for SOX-auditable, IFRS-compliant inventory valuation and margin analysis.
SAP Product Cost Controlling (CO-PC) analytics migrated from ECC / S/4HANA into Snowflake. Models two facts at different grains: the released standard cost estimate cost component split (KEKO header + KEPH cost component structure elehk, with fixed/variable portions, valued against MBEW standard/moving-average price) at material × plant × costing-run × cost-component grain; and production-order variance / settlement (actuals aggregated from COEP line items — COSP/COSS totals are gone in S/4HANA — with AUFK + AFKO order master) broken out by SAP variance category (input price/quantity, resource-usage, lot-size, scrap, output-price, remaining/mixed) including WIP and amounts settled to FI and CO-PA. Conformed material (MARA, SCD2), plant (T001W), production order, cost component, cost element (kstar), variance category, costing run (klvar), and profit-center dimensions with SAP fiscal-period accounting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 12
- tables
- 115
- columns
- 16
- relationships
- 0
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Cost component split modelled as a conformed cost-component dimension
The SAP cost component structure (KEKO.elehk → KEPH) decomposes a material's standard cost into raw material, labor, machine, overhead, and external-processing components with fixed/variable portions; modelling cost component as a dimension and putting the value on the fact lets analysts drill the cost structure and reconcile it to the same components on production variances.
Two separate facts at different grains: estimate vs variance
The standard cost estimate (KEKO/KEPH) and production-order actuals/variances (COEP) have fundamentally different grains and event semantics — a periodic released estimate versus per-period order settlement; splitting them into two conformed-dimension facts avoids a mixed-grain table while still allowing target-vs-actual analysis across the shared cost-component, material, and plant dimensions.
Variance-category dimension for the SAP variance scheme
SAP calculates production variances in fixed categories (input price, input quantity, resource-usage, lot-size, scrap, output-price, remaining/mixed-price); making variance category a dimension keeps the variance fact at one additive grain and lets the report pivot by category and sign convention rather than spreading category-specific measure columns across the fact.
SCD2 on material
Material master attributes that drive costing (material type, procurement type, product hierarchy, MRP controller) change over time; type-2 history preserves the as-was material configuration so a cost estimate or variance can be analysed against the material setup that was effective when it was costed.
Settlement and WIP measures linked toward FI and CO-PA
Production-order variances are settled to FI (price-difference accounts / inventory) and to CO-PA, and unsettled orders carry WIP at period end; carrying settled_amount, wip_amount, and a profit-center key on the variance fact captures the settlement linkage and keeps the mart reconcilable to the general ledger and profitability analysis for SOX and IFRS inventory valuation.
Tables in the SAP Product Cost & Production Variance Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_product_cost_estimate
Grain: one row per material × plant × costing run × cost component (KEKO/KEPH cost component split)
Cost-component-grain standard cost estimate fact — drillable cost structure split.
| Column | Type | Keys |
|---|---|---|
| product_cost_estimate_key | bigint | PK |
| valid_from_date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| cost_component_key | bigint | |
| costing_run_key | bigint | |
| cost_element_key | bigint | |
| costing_number | varchar | |
| costing_version | varchar | |
| total_cost | decimal | |
| fixed_portion | decimal | |
| variable_portion | decimal | |
| standard_price | decimal | |
| moving_average_price | decimal | |
| costing_lot_size | decimal | |
| price_unit | decimal | |
| currency | varchar |
fct_production_order_variance
Grain: one row per production order × cost element/cost component × variance category (COEP settlement/variance)
Variance-category-grain production order settlement fact — variance + WIP analysis.
| Column | Type | Keys |
|---|---|---|
| production_order_variance_key | bigint | PK |
| fiscal_period_key | bigint | |
| posting_date_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| production_order_key | bigint | |
| cost_component_key | bigint | |
| cost_element_key | bigint | |
| variance_category_key | bigint | |
| profit_center_key | bigint | |
| order_number | varchar | |
| settlement_document | varchar | |
| target_cost | decimal | |
| actual_cost | decimal | |
| planned_cost | decimal | |
| total_variance | decimal | |
| control_cost | decimal | |
| scrap_cost | decimal | |
| wip_amount | decimal | |
| settled_amount | decimal | |
| target_quantity | decimal | |
| actual_quantity | decimal | |
| currency | varchar |
Dimension tables (10)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_period_end | boolean |
dim_fiscal_period
Grain: one row per SAP fiscal year/period (gjahr/monat)
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | PK |
| fiscal_period_code | varchar | BK |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| fiscal_variant | varchar | |
| period_start_date | date | |
| period_end_date | date | |
| period_status | varchar |
dim_material
Grain: one row per material per SCD2 version (matnr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| material_number | varchar | BK |
| description | varchar | |
| material_type | varchar | |
| material_group | varchar | |
| base_unit | varchar | |
| product_hierarchy | varchar | |
| procurement_type | varchar | |
| mrp_controller | varchar | |
| is_active | boolean |
dim_plant
Grain: one row per SAP plant / valuation area
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| plant | varchar | BK |
| plant_name | varchar | |
| company_code | varchar | |
| valuation_area | varchar | |
| country | varchar | |
| costing_sheet | varchar |
dim_production_order
Grain: one row per production / process order (aufnr, AUFK + AFKO)
| Column | Type | Keys |
|---|---|---|
| production_order_key | bigint | PK |
| order_number | varchar | BK |
| order_type | varchar | |
| order_category | varchar | |
| description | varchar | |
| mrp_controller | varchar | |
| production_supervisor | varchar | |
| order_status | varchar | |
| routing | varchar | |
| bom | varchar |
dim_cost_component
Grain: one row per cost component within the cost component structure
| Column | Type | Keys |
|---|---|---|
| cost_component_key | bigint | PK |
| cost_component | varchar | BK |
| cost_component_structure | varchar | |
| description | varchar | |
| cost_component_group | varchar | |
| is_fixed_cost_component | boolean | |
| is_variable | boolean |
dim_cost_element
Grain: one row per cost element / GL account (kstar)
| Column | Type | Keys |
|---|---|---|
| cost_element_key | bigint | PK |
| cost_element | varchar | BK |
| description | varchar | |
| cost_element_category | varchar | |
| cost_element_group | varchar |
dim_variance_category
Grain: one row per SAP production variance category
| Column | Type | Keys |
|---|---|---|
| variance_category_key | bigint | PK |
| variance_category_code | varchar | BK |
| variance_label | varchar | |
| variance_type | varchar | |
| is_favorable_sign_convention | boolean | |
| sort_order | smallint |
dim_costing_run
Grain: one row per costing run (costing variant klvar + run id)
| Column | Type | Keys |
|---|---|---|
| costing_run_key | bigint | PK |
| costing_run | varchar | BK |
| costing_variant | varchar | |
| costing_version | varchar | |
| costing_type | varchar | |
| valuation_variant | varchar | |
| costing_date_from | date | |
| costing_date_to | date |
dim_profit_center
Grain: one row per profit center (prctr, CEPC)
| Column | Type | Keys |
|---|---|---|
| profit_center_key | bigint | PK |
| profit_center | varchar | BK |
| name | varchar | |
| controlling_area | varchar | |
| segment | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_product_cost_estimate.valid_from_date_key → dim_date (many-to-one)
- fct_product_cost_estimate.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_product_cost_estimate.material_key → dim_material (many-to-one)
- fct_product_cost_estimate.plant_key → dim_plant (many-to-one)
- fct_product_cost_estimate.cost_component_key → dim_cost_component (many-to-one)
- fct_product_cost_estimate.costing_run_key → dim_costing_run (many-to-one)
- fct_product_cost_estimate.cost_element_key → dim_cost_element (many-to-one)
- fct_production_order_variance.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_production_order_variance.posting_date_key → dim_date (many-to-one)
- fct_production_order_variance.material_key → dim_material (many-to-one)
- fct_production_order_variance.plant_key → dim_plant (many-to-one)
- fct_production_order_variance.production_order_key → dim_production_order (many-to-one)
- fct_production_order_variance.cost_component_key → dim_cost_component (many-to-one)
- fct_production_order_variance.cost_element_key → dim_cost_element (many-to-one)
- fct_production_order_variance.variance_category_key → dim_variance_category (many-to-one)
- fct_production_order_variance.profit_center_key → dim_profit_center (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.