SAP Record-to-Report (GL) Analytics data model
Kimball warehouse for SAP R2R: transaction-grain GL line items with document numbers (belnr) as degenerate dimensions and multi-currency amounts (local/document/group), plus a monthly trial-balance periodic snapshot. Conformed FI/CO dimensions with SCD2 chart of accounts, cost center, and profit center; SAP fiscal year/period (gjahr/poper) including special closing periods drives period reporting.
SAP FI/CO record-to-report analytics migrated from ECC / S/4HANA into Snowflake. Covers GL line-item postings at document-line grain (classic BKPF/BSEG or the S/4HANA ACDOCA universal journal) and a monthly trial-balance snapshot over conformed GL account (SKA1/SKB1, SCD2), company code (T001), cost center (CSKS), profit center (CEPC), document type, and currency dimensions, with SAP fiscal-period accounting for statement and segment reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 104
- columns
- 14
- relationships
- 2
- 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
Document-line grain aligned to ACDOCA / BSEG
The atomic event of the SAP ledger is a single document line (one GL account, one debit or credit); the S/4HANA ACDOCA universal journal already stores at this grain, so declaring the fact there supports drill-across to any FI/CO rollup.
Multi-currency amounts on every fact line
SAP carries amounts in document, local (company-code), and group currencies (wsl/hsl/ksl); retaining all three plus the exchange rate supports consolidation and statutory reporting under IFRS/GAAP without re-deriving FX.
Separate monthly trial-balance periodic snapshot
Balances are semi-additive (sum across accounts, not periods); a periodic snapshot at account/company-code/period grain gives fast financial-statement reporting independent of journal volume and ties to SAP period-end close.
SCD2 on chart of accounts, cost center, and profit center
Reorganizations and restatements change FI/CO master attributes (account group, FSV node, profit-center segment) over time; type-2 history preserves the as-was mapping required for comparable, SOX-auditable period reporting.
Tables in the SAP Record-to-Report (GL) 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_gl_line_item
Grain: one row per GL accounting document line (BKPF/BSEG or S/4 ACDOCA universal journal)
Transaction-grain ledger fact — one line of a posted GL document.
| Column | Type | Keys |
|---|---|---|
| gl_line_item_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| gl_account_key | bigint | |
| company_code_key | bigint | |
| cost_center_key | bigint | |
| profit_center_key | bigint | |
| document_type_key | bigint | |
| currency_key | bigint | |
| document_date_key | bigint | |
| entry_date_key | bigint | |
| accounting_document | varchar | |
| document_line | varchar | |
| reference_document | varchar | |
| posting_key | varchar | |
| debit_credit_indicator | varchar | |
| amount_local | decimal | |
| amount_document | decimal | |
| amount_group | decimal | |
| debit_amount | decimal | |
| credit_amount | decimal | |
| exchange_rate | decimal | |
| is_reversal | boolean | |
| is_open_item | boolean |
fct_trial_balance_snapshot
Grain: one row per GL account per company code per fiscal period (monthly periodic snapshot)
Monthly periodic snapshot — balances semi-additive across periods.
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | |
| gl_account_key | bigint | |
| company_code_key | bigint | |
| cost_center_key | bigint | |
| profit_center_key | bigint | |
| currency_key | bigint | |
| opening_balance | decimal | |
| period_debits | decimal | |
| period_credits | decimal | |
| net_movement | decimal | |
| closing_balance | decimal | |
| closing_balance_group | decimal | |
| is_adjusted | boolean |
Dimension tables (6)
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_gl_account
Grain: one row per GL account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| gl_account_key | bigint | PK |
| gl_account_number | varchar | BK |
| gl_account_name | varchar | |
| chart_of_accounts | varchar | |
| account_group | varchar | |
| account_type | varchar | |
| pl_statement_account_type | varchar | |
| balance_sheet_indicator | boolean | |
| normal_balance | varchar | |
| financial_statement_version | varchar | |
| is_reconciliation_account | boolean | |
| is_open_item_managed | boolean | |
| 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 | |
| chart_of_accounts | varchar | |
| fiscal_year_variant | varchar | |
| controlling_area | varchar |
dim_cost_center
Grain: one row per cost center per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| cost_center | varchar | BK |
| cost_center_name | varchar | |
| controlling_area | varchar | |
| cost_center_category | varchar | |
| company_code | varchar | |
| profit_center | varchar | |
| responsible_manager | varchar | PII |
| hierarchy_node | varchar | |
| is_active | boolean |
dim_profit_center
Grain: one row per profit center per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| profit_center_key | bigint | PK |
| profit_center | varchar | BK |
| profit_center_name | varchar | |
| controlling_area | varchar | |
| segment | varchar | |
| responsible_manager | varchar | PII |
| hierarchy_node | varchar | |
| is_active | boolean |
Lookup tables (2)
dim_document_type
Grain: one row per SAP FI document type
| Column | Type | Keys |
|---|---|---|
| document_type_key | bigint | PK |
| document_type | varchar | BK |
| document_type_name | varchar | |
| number_range | varchar | |
| reverse_document_type | varchar | |
| is_net_document | boolean |
dim_currency
Grain: one row per ISO / SAP currency
| Column | Type | Keys |
|---|---|---|
| currency_key | bigint | PK |
| currency_code | varchar | BK |
| currency_name | varchar | |
| decimal_places | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_gl_line_item.date_key → dim_date (many-to-one)
- fct_gl_line_item.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_gl_line_item.gl_account_key → dim_gl_account (many-to-one)
- fct_gl_line_item.company_code_key → dim_company_code (many-to-one)
- fct_gl_line_item.cost_center_key → dim_cost_center (many-to-one)
- fct_gl_line_item.profit_center_key → dim_profit_center (many-to-one)
- fct_gl_line_item.document_type_key → dim_document_type (many-to-one)
- fct_gl_line_item.currency_key → dim_currency (many-to-one)
- fct_trial_balance_snapshot.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_trial_balance_snapshot.gl_account_key → dim_gl_account (many-to-one)
- fct_trial_balance_snapshot.company_code_key → dim_company_code (many-to-one)
- fct_trial_balance_snapshot.cost_center_key → dim_cost_center (many-to-one)
- fct_trial_balance_snapshot.profit_center_key → dim_profit_center (many-to-one)
- fct_trial_balance_snapshot.currency_key → dim_currency (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.