SAP Order-to-Cash Analytics data model
Kimball warehouse for SAP O2C: transaction-grain sales order lines, delivery items, and billing lines with SAP document numbers (vbeln) as degenerate dimensions, plus a daily open-order periodic snapshot, over conformed SD dimensions. Customer tax IDs and PII are classified and masked; SAP fiscal year/period (gjahr/poper) drives period reporting.
SAP Sales & Distribution (SD) order-to-cash analytics migrated from ECC / S/4HANA into Snowflake. Covers the sales order (VBAK/VBAP), outbound delivery (LIKP/LIPS), and billing (VBRK/VBRP) processes plus a daily open-order backlog snapshot, over conformed material (MARA/MAKT), customer (KNA1/KNVV, SCD2), sales organization, company code, and shipping-point 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
- 139
- columns
- 25
- relationships
- 4
- 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
Three transaction-grain facts mirroring the SD document flow
SAP SD's order (VBAK/VBAP) → delivery (LIKP/LIPS) → billing (VBRK/VBRP) document chain is best modelled as three conformed transaction-grain facts so backlog, fulfilment, and revenue can each drill to atomic line detail and drill-across via shared dimensions.
SAP document numbers as degenerate dimensions
vbeln/posnr identify the operational document but carry no attributes of their own, so per Kimball they live as non-null varchar degenerate dimensions on the fact rather than as standalone dimension tables.
Daily open-order periodic snapshot
Open backlog (ordered minus delivered) is semi-additive and needed daily for demand and revenue forecasting independent of order volume, so a periodic snapshot supplements the transaction facts.
SCD2 on material and customer master data
MARA material group / product hierarchy and KNA1/KNVV customer group and sales-area assignments change over time; type-2 history preserves the as-was master attributes required for comparable trend analysis.
Tables in the SAP Order-to-Cash 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 (4)
fct_sales_order_line
Grain: one row per sales order schedule line (VBAK/VBAP)
Transaction-grain SD fact at order schedule-line level.
| Column | Type | Keys |
|---|---|---|
| sales_order_line_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| customer_key | bigint | |
| sales_org_key | bigint | |
| company_code_key | bigint | |
| sales_doc_type_key | bigint | |
| requested_delivery_date_key | bigint | |
| sales_document | varchar | |
| sales_item | varchar | |
| schedule_line | varchar | |
| order_quantity | decimal | |
| sales_unit | varchar | |
| net_value | decimal | |
| net_price | decimal | |
| tax_amount | decimal | |
| document_currency | varchar | |
| confirmed_quantity | decimal | |
| rejection_reason | varchar | |
| is_rejected | boolean |
fct_delivery_item
Grain: one row per outbound delivery item (LIKP/LIPS)
Transaction-grain delivery fact — fulfilment leg of O2C.
| Column | Type | Keys |
|---|---|---|
| delivery_item_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| customer_key | bigint | |
| sales_org_key | bigint | |
| shipping_point_key | bigint | |
| goods_issue_date_key | bigint | |
| delivery_document | varchar | |
| delivery_item | varchar | |
| reference_sales_document | varchar | |
| delivery_quantity | decimal | |
| sales_unit | varchar | |
| net_weight | decimal | |
| weight_unit | varchar | |
| picked_quantity | decimal | |
| is_goods_issued | boolean |
fct_billing_line
Grain: one row per billing document item (VBRK/VBRP)
Transaction-grain billing fact — invoiced revenue leg of O2C.
| Column | Type | Keys |
|---|---|---|
| billing_line_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| customer_key | bigint | |
| sales_org_key | bigint | |
| company_code_key | bigint | |
| sales_doc_type_key | bigint | |
| billing_document | varchar | |
| billing_item | varchar | |
| reference_delivery | varchar | |
| billed_quantity | decimal | |
| sales_unit | varchar | |
| net_value | decimal | |
| cost_value | decimal | |
| tax_amount | decimal | |
| gross_value | decimal | |
| document_currency | varchar | |
| is_cancelled | boolean |
fct_open_order_snapshot
Grain: one row per open sales order line per day (periodic snapshot)
Daily periodic snapshot of open order backlog — semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| customer_key | bigint | |
| sales_org_key | bigint | |
| sales_document | varchar | |
| sales_item | varchar | |
| open_quantity | decimal | |
| open_value | decimal | |
| days_open | integer | |
| is_overdue | boolean |
Dimension tables (7)
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/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 | |
| is_special_period | boolean | |
| period_status | varchar |
dim_material
Grain: one row per material per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| material_number | varchar | BK |
| material_description | varchar | |
| material_type | varchar | |
| material_group | varchar | |
| base_unit_of_measure | varchar | |
| product_hierarchy | varchar | |
| division | varchar | |
| gross_weight | decimal | |
| weight_unit | varchar | |
| is_active | boolean |
dim_customer
Grain: one row per customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| customer_name | varchar | PII |
| search_term | varchar | |
| street_address | varchar | PII |
| city | varchar | |
| region | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| tax_number | varchar | PII |
| customer_group | varchar | |
| account_group | varchar | |
| payment_terms | varchar | |
| credit_control_area | 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 | |
| distribution_channel_name | varchar | |
| division | varchar | |
| division_name | varchar | |
| company_code | 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 | |
| chart_of_accounts | varchar | |
| fiscal_year_variant | varchar |
dim_shipping_point
Grain: one row per shipping point
| Column | Type | Keys |
|---|---|---|
| shipping_point_key | bigint | PK |
| shipping_point | varchar | BK |
| shipping_point_name | varchar | |
| plant | varchar | |
| country | varchar | |
| loading_time_days | smallint |
Lookup tables (1)
dim_sales_doc_type
Grain: one row per SAP sales document type
| Column | Type | Keys |
|---|---|---|
| sales_doc_type_key | bigint | PK |
| sales_doc_type | varchar | BK |
| sales_doc_type_name | varchar | |
| sd_document_category | varchar | |
| is_returns | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_sales_order_line.date_key → dim_date (many-to-one)
- fct_sales_order_line.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_sales_order_line.material_key → dim_material (many-to-one)
- fct_sales_order_line.customer_key → dim_customer (many-to-one)
- fct_sales_order_line.sales_org_key → dim_sales_org (many-to-one)
- fct_sales_order_line.company_code_key → dim_company_code (many-to-one)
- fct_sales_order_line.sales_doc_type_key → dim_sales_doc_type (many-to-one)
- fct_delivery_item.date_key → dim_date (many-to-one)
- fct_delivery_item.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_delivery_item.material_key → dim_material (many-to-one)
- fct_delivery_item.customer_key → dim_customer (many-to-one)
- fct_delivery_item.sales_org_key → dim_sales_org (many-to-one)
- fct_delivery_item.shipping_point_key → dim_shipping_point (many-to-one)
- fct_billing_line.date_key → dim_date (many-to-one)
- fct_billing_line.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_billing_line.material_key → dim_material (many-to-one)
- fct_billing_line.customer_key → dim_customer (many-to-one)
- fct_billing_line.sales_org_key → dim_sales_org (many-to-one)
- fct_billing_line.company_code_key → dim_company_code (many-to-one)
- fct_billing_line.sales_doc_type_key → dim_sales_doc_type (many-to-one)
- fct_open_order_snapshot.date_key → dim_date (many-to-one)
- fct_open_order_snapshot.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_open_order_snapshot.material_key → dim_material (many-to-one)
- fct_open_order_snapshot.customer_key → dim_customer (many-to-one)
- fct_open_order_snapshot.sales_org_key → dim_sales_org (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.