SAP CO-PA Profitability Analytics data model
Kimball warehouse for SAP CO-PA profitability: a transaction-grain operating-concern line-item fact (CE1xxx) with CO-PA value fields and a characteristic junk dimension for low-cardinality flags (record type, plan/actual, billing type). Billing documents (rbeln) are degenerate dimensions; conformed customer/product/profit-center dimensions are SCD2; SAP fiscal year/period drives period reporting for contribution-margin and segment profitability analysis.
SAP Controlling Profitability Analysis (CO-PA) analytics migrated from ECC / S/4HANA into Snowflake. Models the costing-based CO-PA operating-concern line item (tables CE1xxx through CE4xxx) at transaction grain, carrying characteristics and value fields (revenue, sales deductions, COGS, variable/fixed cost, contribution margin) over conformed customer (KNA1, SCD2), product (MARA, SCD2), sales organization, company code, and profit-center dimensions plus a CO-PA characteristic junk dimension, 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.
- 9
- tables
- 88
- columns
- 8
- relationships
- 1
- 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
Transaction-grain operating-concern line-item fact
Costing-based CO-PA stores actuals as individual operating-concern line items in CE1xxx; declaring the fact at that grain preserves the full characteristic combination and lets value fields aggregate to any profitability segment (CE4xxx).
Value fields modelled as additive measures with a margin ladder
CO-PA's revenue, sales-deduction, COGS, and cost value fields are fully additive; carrying them plus the derived contribution-margin and operating-profit ladder on the fact supports drill-down profitability without re-deriving the margin scheme.
Characteristic junk dimension for low-cardinality flags
CO-PA carries many low-cardinality characteristics (record type, plan/actual indicator, billing/sales-document type, derived WWxxx groups); collapsing them into a single junk dimension keeps the fact narrow per Kimball rather than adding many tiny foreign keys.
SCD2 on customer, product, and profit center
CO-PA characteristic derivations (customer/product hierarchy, profit-center segment) change over time; type-2 history preserves the as-was segment assignment required for comparable, SOX-auditable profitability trend reporting.
Tables in the SAP CO-PA Profitability 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 (1)
fct_copa_line_item
Grain: one row per CO-PA operating-concern line item (CE1xxx, transaction grain)
Transaction-grain CO-PA fact — value fields by characteristic combination.
| Column | Type | Keys |
|---|---|---|
| copa_line_item_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| sales_org_key | bigint | |
| company_code_key | bigint | |
| profit_center_key | bigint | |
| characteristic_key | bigint | |
| billing_document | varchar | |
| billing_item | varchar | |
| reference_document_number | varchar | |
| sales_quantity | decimal | |
| sales_unit | varchar | |
| revenue | decimal | |
| sales_deductions | decimal | |
| net_revenue | decimal | |
| cost_of_goods_sold | decimal | |
| variable_cost | decimal | |
| fixed_cost | decimal | |
| contribution_margin | decimal | |
| operating_profit | decimal | |
| currency | varchar |
Dimension tables (8)
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_business_day | boolean | |
| is_period_end | boolean |
dim_fiscal_period
Grain: one row per SAP fiscal year/period (gjahr/perio)
| 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 | |
| is_special_period | boolean | |
| period_status | varchar |
dim_customer
Grain: one row per customer per SCD2 version (CO-PA characteristic kndnr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| customer_name | varchar | PII |
| city | varchar | |
| region | varchar | |
| country | varchar | |
| customer_group | varchar | |
| customer_hierarchy_node | varchar | |
| industry | varchar | |
| is_active | boolean |
dim_product
Grain: one row per product per SCD2 version (CO-PA characteristic artnr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_number | varchar | BK |
| product_description | varchar | |
| material_group | varchar | |
| product_hierarchy | varchar | |
| division | varchar | |
| brand | varchar | |
| is_active | boolean |
dim_sales_org
Grain: one row per sales organization / distribution channel / division
| Column | Type | Keys |
|---|---|---|
| sales_org_key | bigint | PK |
| sales_org | varchar | BK |
| sales_org_name | varchar | |
| distribution_channel | varchar | |
| division | varchar | |
| sales_district | varchar | |
| currency | varchar |
dim_company_code
Grain: one row per SAP company code
| Column | Type | Keys |
|---|---|---|
| company_code_key | bigint | PK |
| company_code | varchar | BK |
| company_name | varchar | |
| country | varchar | |
| local_currency | varchar | |
| controlling_area | varchar | |
| operating_concern | varchar |
dim_profit_center
Grain: one row per profit center per SCD2 version (CO-PA characteristic prctr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| profit_center_key | bigint | PK |
| profit_center | varchar | BK |
| profit_center_name | varchar | |
| controlling_area | varchar | |
| segment | varchar | |
| hierarchy_node | varchar | |
| is_active | boolean |
dim_characteristic
Grain: one row per CO-PA characteristic combination (junk dimension of low-cardinality flags)
| Column | Type | Keys |
|---|---|---|
| characteristic_key | bigint | PK |
| characteristic_combo_code | varchar | BK |
| record_type | varchar | |
| plan_actual_indicator | varchar | |
| sales_document_type | varchar | |
| billing_type | varchar | |
| customer_group_1 | varchar | |
| product_group_1 | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_copa_line_item.date_key → dim_date (many-to-one)
- fct_copa_line_item.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_copa_line_item.customer_key → dim_customer (many-to-one)
- fct_copa_line_item.product_key → dim_product (many-to-one)
- fct_copa_line_item.sales_org_key → dim_sales_org (many-to-one)
- fct_copa_line_item.company_code_key → dim_company_code (many-to-one)
- fct_copa_line_item.profit_center_key → dim_profit_center (many-to-one)
- fct_copa_line_item.characteristic_key → dim_characteristic (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.