SAP Receivables & Payables Aging Analytics data model
Kimball warehouse for SAP AR/AP aging: one periodic-snapshot fact (grain = one sub-ledger line item per as-of snapshot date) that merges the customer (BSID/BSAD) and vendor (BSIK/BSAK) open-item sub-ledgers with an account_type flag so a single mart drives AR/AP aging buckets, DSO/DPO, overdue analysis and dunning. Accounting document/line (belnr/buzei), reference (xblnr) and special-GL indicator (umskz) are degenerate dimensions; five date roles (snapshot, document, posting, net-due, clearing) all role-play a single dim_date with sentinel members so not-yet-cleared and undeterminable due dates never need null date FKs; customer and vendor are SCD2; an aging-bucket dimension bands days overdue for consistent receivables/payables reporting.
SAP FI Accounts-Receivable / Accounts-Payable open-items and aging analytics migrated from ECC / S/4HANA into Snowflake. Models a single periodic-snapshot sub-ledger line-item fact unifying customer open items (BSID open / BSAD cleared, the Debitor sub-ledger) and vendor open items (BSIK open / BSAK cleared, the Kreditor sub-ledger) via an account_type flag, carrying local- and document-currency amounts, open/cleared/overdue measures, dunning level and cash discount over conformed customer (KNA1, SCD2), vendor (LFA1, SCD2), company code (T001), GL reconciliation account (SKAT), payment terms (T052), aging bucket, and role-played date 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.
- 9
- tables
- 95
- columns
- 12
- relationships
- 3
- 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
Single fact unifying AR and AP sub-ledgers via an account_type flag
SAP keeps customer open items in BSID/BSAD and vendor open items in BSIK/BSAK, but both are FI sub-ledger line items with identical aging mechanics; carrying them in one fact with an account_type flag (D Debitor / K Kreditor) lets one mart serve receivables and payables aging, DSO and DPO without two parallel star schemas.
Periodic-snapshot grain for aging rather than transaction grain
Aging answers 'what is open and how overdue as of a date'; declaring the fact at one row per line item per snapshot date materialises the as-of open balance and overdue position directly, so aging buckets, DSO/DPO and trend reports never have to reconstruct point-in-time balances from clearing history.
Aging-bucket dimension instead of hard-coded bucket measures
Banding days overdue into a conformed aging-bucket dimension (current, 1-30, 31-60, 61-90, 91-180, over-180) keeps the bucket scheme in one governed place; reclassifying or re-banding aging is a dimension change, and AR and AP reports stay comparable on the same buckets.
Sentinel date members so not-yet-cleared / undeterminable dates avoid null FKs
AUGDT (clearing date) is populated only once an item clears, and the net due date is occasionally undeterminable; rather than null date foreign keys, the clearing and net-due roles point at dedicated 'not-yet-cleared' / 'unknown' members of dim_date, preserving inner-join behaviour and additive aging math.
SCD2 on customer and vendor master data
Customer (KNA1) and vendor (LFA1) attributes such as credit control area, payment terms and grouping change over time; type-2 history preserves the as-was master attributes behind each open item, which is required for accurate, SOX-auditable receivables/payables aging trends.
Tables in the SAP Receivables & Payables Aging 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_open_item
Grain: one row per FI sub-ledger line item per snapshot date (open or cleared, from BSID/BSAD customer + BSIK/BSAK vendor open items)
Periodic-snapshot AR/AP open-items fact unifying customer (BSID/BSAD) and vendor (BSIK/BSAK) sub-ledger line items via account_type.
| Column | Type | Keys |
|---|---|---|
| open_item_key | bigint | PK |
| snapshot_date_key | bigint | |
| document_date_key | bigint | |
| posting_date_key | bigint | |
| net_due_date_key | bigint | |
| clearing_date_key | bigint | |
| fiscal_period_key | bigint | |
| customer_key | bigint | |
| vendor_key | bigint | |
| company_code_key | bigint | |
| gl_account_key | bigint | |
| payment_terms_key | bigint | |
| aging_bucket_key | bigint | |
| accounting_document | varchar | |
| document_line | varchar | |
| reference | varchar | |
| special_gl_indicator | varchar | |
| account_type | varchar | |
| is_open | boolean | |
| is_overdue | boolean | |
| is_cleared | boolean | |
| dunning_level | smallint | |
| payment_block | varchar | |
| amount_local_currency | decimal | |
| amount_document_currency | decimal | |
| open_amount | decimal | |
| cleared_amount | decimal | |
| cash_discount_amount | decimal | |
| days_overdue | smallint | |
| days_to_due | smallint | |
| document_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/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_customer
Grain: one row per customer per SCD2 version (Debitor / KNA1)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| customer_name | varchar | PII |
| country | varchar | |
| region | varchar | |
| city | varchar | PII |
| credit_control_area | varchar | |
| customer_group | varchar | |
| payment_terms_default | varchar | |
| industry | varchar | |
| is_active | boolean |
dim_vendor
Grain: one row per vendor per SCD2 version (Kreditor / LFA1)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_number | varchar | BK |
| vendor_name | varchar | PII |
| country | varchar | |
| region | varchar | |
| vendor_group | varchar | |
| payment_terms_default | varchar | |
| is_active | boolean |
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 | |
| credit_control_area | varchar |
dim_gl_account
Grain: one row per GL reconciliation account
| Column | Type | Keys |
|---|---|---|
| gl_account_key | bigint | PK |
| gl_account | varchar | BK |
| account_name | varchar | |
| account_group | varchar | |
| is_reconciliation_account | boolean | |
| recon_account_type | varchar |
dim_payment_terms
Grain: one row per SAP payment-terms key (T052)
| Column | Type | Keys |
|---|---|---|
| payment_terms_key | bigint | PK |
| payment_terms | varchar | BK |
| description | varchar | |
| days_net | smallint | |
| cash_discount_1_days | smallint | |
| cash_discount_1_percent | decimal | |
| cash_discount_2_days | smallint | |
| cash_discount_2_percent | decimal | |
| baseline_date_rule | varchar |
dim_aging_bucket
Grain: one row per aging bucket band (current / 1-30 / 31-60 / 61-90 / 91-180 / over-180)
| Column | Type | Keys |
|---|---|---|
| aging_bucket_key | bigint | PK |
| aging_bucket_code | varchar | BK |
| bucket_label | varchar | |
| bucket_lower_days | smallint | |
| bucket_upper_days | smallint | |
| bucket_sort_order | smallint | |
| is_overdue_bucket | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_open_item.snapshot_date_key → dim_date (many-to-one)
- fct_open_item.document_date_key → dim_date (many-to-one)
- fct_open_item.posting_date_key → dim_date (many-to-one)
- fct_open_item.net_due_date_key → dim_date (many-to-one)
- fct_open_item.clearing_date_key → dim_date (many-to-one)
- fct_open_item.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_open_item.customer_key → dim_customer (many-to-one)
- fct_open_item.vendor_key → dim_vendor (many-to-one)
- fct_open_item.company_code_key → dim_company_code (many-to-one)
- fct_open_item.gl_account_key → dim_gl_account (many-to-one)
- fct_open_item.payment_terms_key → dim_payment_terms (many-to-one)
- fct_open_item.aging_bucket_key → dim_aging_bucket (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.