SAP Procure-to-Pay Analytics data model
Kimball warehouse for SAP P2P: transaction-grain PO lines, goods receipts, and invoice receipts with SAP document numbers (ebeln/mblnr/belnr) as degenerate dimensions for three-way match, plus a daily AP open-item periodic snapshot with aging. Conformed MM/FI-AP dimensions; vendor tax IDs are classified restricted and masked; SAP fiscal year/period (gjahr/poper) drives period reporting.
SAP Materials Management (MM) and FI Accounts Payable procure-to-pay analytics migrated from ECC / S/4HANA into Snowflake. Covers the purchase order (EKKO/EKPO), goods receipt (MSEG/MKPF, movement 101), and logistics invoice (RBKP/RSEG) processes supporting three-way match, plus a daily AP open-item snapshot, over conformed vendor (LFA1, SCD2), material, plant (T001W), purchasing-org, and company-code dimensions. 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
- 136
- columns
- 23
- 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 facts supporting the three-way match
SAP P2P reconciles PO (EKKO/EKPO), goods receipt (MSEG/MKPF), and invoice (RBKP/RSEG); modelling each as a conformed transaction-grain fact lets quantity and price variances be computed by drilling across shared vendor/material/plant dimensions.
Goods-receipt fact keyed on movement type 101
MM-IM records receipts as material-document movements; isolating bwart 101 (GR for PO) with its 102 reversal flag gives a clean received-quantity/value fact that ties to the PO via ebeln/ebelp.
Daily AP open-item periodic snapshot with aging
Open payables (BSIK) are semi-additive balances needed daily for cash and DPO reporting; a periodic snapshot with aging buckets supplements the invoice transaction fact independent of posting volume.
SCD2 on the vendor master
LFA1/LFB1 attributes (account group, payment terms, reconciliation account, blocked status) change over time; type-2 history preserves the as-was vendor classification required for SOX-compliant spend analysis.
Tables in the SAP Procure-to-Pay 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_purchase_order_line
Grain: one row per purchase order item (EKKO/EKPO)
Transaction-grain MM fact at PO item level.
| Column | Type | Keys |
|---|---|---|
| purchase_order_line_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| vendor_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| purchasing_org_key | bigint | |
| company_code_key | bigint | |
| purchasing_doc_type_key | bigint | |
| delivery_date_key | bigint | |
| purchasing_document | varchar | |
| purchasing_item | varchar | |
| po_quantity | decimal | |
| order_unit | varchar | |
| net_price | decimal | |
| net_order_value | decimal | |
| document_currency | varchar | |
| account_assignment_category | varchar | |
| item_category | varchar | |
| is_deleted | boolean |
fct_goods_receipt
Grain: one row per goods-receipt material-document line (MSEG/MKPF, bwart 101)
Transaction-grain inventory-receipt leg of P2P.
| Column | Type | Keys |
|---|---|---|
| goods_receipt_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| vendor_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| company_code_key | bigint | |
| material_document | varchar | |
| material_doc_item | varchar | |
| reference_purchasing_document | varchar | |
| reference_purchasing_item | varchar | |
| movement_type | varchar | |
| received_quantity | decimal | |
| entry_unit | varchar | |
| received_value | decimal | |
| document_currency | varchar | |
| is_reversal | boolean |
fct_invoice_receipt
Grain: one row per logistics-invoice item (RBKP/RSEG)
Transaction-grain invoice-verification leg of P2P (three-way match).
| Column | Type | Keys |
|---|---|---|
| invoice_receipt_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| vendor_key | bigint | |
| material_key | bigint | |
| company_code_key | bigint | |
| invoice_document | varchar | |
| invoice_item | varchar | |
| reference_purchasing_document | varchar | |
| reference_purchasing_item | varchar | |
| invoiced_quantity | decimal | |
| invoice_amount | decimal | |
| tax_amount | decimal | |
| document_currency | varchar | |
| price_variance_amount | decimal | |
| quantity_variance | decimal | |
| is_blocked_for_payment | boolean |
fct_ap_open_item_snapshot
Grain: one row per open AP item per day (periodic snapshot)
Daily periodic snapshot of open AP liabilities — semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| fiscal_period_key | bigint | |
| vendor_key | bigint | |
| company_code_key | bigint | |
| net_due_date_key | bigint | |
| accounting_document | varchar | |
| accounting_item | varchar | |
| open_amount | decimal | |
| open_amount_document | decimal | |
| document_currency | varchar | |
| days_outstanding | integer | |
| days_overdue | integer | |
| aging_bucket | varchar | |
| 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_vendor
Grain: one row per vendor per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_number | varchar | BK |
| vendor_name | varchar | PII |
| search_term | varchar | |
| street_address | varchar | PII |
| city | varchar | |
| region | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| tax_number | varchar | PII |
| account_group | varchar | |
| vendor_class | varchar | |
| payment_terms | varchar | |
| reconciliation_account | varchar | |
| is_one_time_vendor | boolean | |
| is_blocked | boolean | |
| is_active | boolean |
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 | |
| purchasing_group | varchar | |
| is_active | boolean |
dim_plant
Grain: one row per plant
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| plant | varchar | BK |
| plant_name | varchar | |
| city | varchar | |
| country | varchar | |
| company_code | varchar | |
| purchasing_org | varchar |
dim_purchasing_org
Grain: one row per purchasing organization / group
| Column | Type | Keys |
|---|---|---|
| purchasing_org_key | bigint | PK |
| purchasing_org | varchar | BK |
| purchasing_org_name | varchar | |
| purchasing_group | varchar | |
| purchasing_group_name | varchar | |
| company_code | 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 |
Lookup tables (1)
dim_purchasing_doc_type
Grain: one row per SAP purchasing document type
| Column | Type | Keys |
|---|---|---|
| purchasing_doc_type_key | bigint | PK |
| doc_type | varchar | BK |
| doc_type_name | varchar | |
| doc_category | varchar | |
| is_framework_order | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_purchase_order_line.date_key → dim_date (many-to-one)
- fct_purchase_order_line.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_purchase_order_line.vendor_key → dim_vendor (many-to-one)
- fct_purchase_order_line.material_key → dim_material (many-to-one)
- fct_purchase_order_line.plant_key → dim_plant (many-to-one)
- fct_purchase_order_line.purchasing_org_key → dim_purchasing_org (many-to-one)
- fct_purchase_order_line.company_code_key → dim_company_code (many-to-one)
- fct_purchase_order_line.purchasing_doc_type_key → dim_purchasing_doc_type (many-to-one)
- fct_goods_receipt.date_key → dim_date (many-to-one)
- fct_goods_receipt.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_goods_receipt.vendor_key → dim_vendor (many-to-one)
- fct_goods_receipt.material_key → dim_material (many-to-one)
- fct_goods_receipt.plant_key → dim_plant (many-to-one)
- fct_goods_receipt.company_code_key → dim_company_code (many-to-one)
- fct_invoice_receipt.date_key → dim_date (many-to-one)
- fct_invoice_receipt.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_invoice_receipt.vendor_key → dim_vendor (many-to-one)
- fct_invoice_receipt.material_key → dim_material (many-to-one)
- fct_invoice_receipt.company_code_key → dim_company_code (many-to-one)
- fct_ap_open_item_snapshot.date_key → dim_date (many-to-one)
- fct_ap_open_item_snapshot.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_ap_open_item_snapshot.vendor_key → dim_vendor (many-to-one)
- fct_ap_open_item_snapshot.company_code_key → dim_company_code (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.