SAP Group Reporting & Consolidation Analytics data model
Kimball warehouse for SAP S/4HANA Group Reporting: one ACDOCU-grain consolidation-journal fact carrying the three currencies (transaction, local, group) and a quantity, over conformed dimensions. The consolidation-unit dimension is SCD2 and role-played as the partner unit for intercompany elimination; a posting-level dimension separates reported data from standardizing adjustments and eliminations; a subitem / flow dimension drives equity and asset rollforwards; and a ledger dimension carries the accounting principle for multi-GAAP (IFRS / US-GAAP / local-GAAP) consolidated reporting surfaced in SAP Analytics Cloud.
SAP S/4HANA Group Reporting financial-consolidation analytics in Snowflake. Models the Universal Consolidation Journal (ACDOCU) — the single 'one version of truth' that unifies reported actuals (ACDOCA), plan (ACDOCP), standardizing adjustments, and eliminated consolidation data — at journal-line grain, carrying transaction / local / group currency amounts over conformed consolidation dimensions: consolidation unit (SCD2, also role-played as the partner unit for intercompany elimination), consolidation group hierarchy, FS item, subitem (movement / flow), consolidation version, ledger (multi-GAAP), posting level (reported vs adjustment vs elimination), and document type, 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.
- 11
- tables
- 87
- columns
- 11
- relationships
- 0
- 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 ACDOCU-grain fact carrying three currencies
SAP Group Reporting stores reported, standardized, and eliminated consolidation data in one Universal Consolidation Journal (ACDOCU); declaring the fact at the ACDOCU journal-line grain with transaction, local, and group currency amounts side by side preserves the 'one version of truth' and lets every currency view aggregate without re-translation.
Posting-level dimension separates reported vs eliminated data
Consolidation amounts span posting level 00 (reported), 01–09 (standardizing adjustments), 20 (two-sided intercompany elimination), and 30 (consolidation of investments); modelling posting level as a dimension lets reporting filter reported-only vs fully consolidated figures and audit each elimination layer.
Consolidation unit role-played as the partner unit
Intercompany elimination matches a consolidation unit against its partner unit; role-playing the single conformed consolidation-unit dimension through two foreign keys (consolidation_unit_key and partner_unit_key) supports both the reporting-unit and partner-unit perspectives without a duplicate dimension.
Subitem / flow dimension for rollforwards, ledger for multi-GAAP
The subitem (movement/flow) dimension models equity and asset rollforward flows (opening balance → additions/disposals/transfers → closing) required by consolidation disclosures, while the ledger dimension carries the accounting principle so IFRS, US-GAAP, and local-GAAP consolidations coexist on the same conformed model.
SCD2 consolidation unit and posting-level auditability for SOX/IFRS
Consolidation method, ownership, and group assignment of a unit change over time; SCD2 on the consolidation unit preserves the as-was group structure, and the posting-level/document-type dimensions give a full SOX- and IFRS-auditable trail from reported data through every adjustment and elimination.
Tables in the SAP Group Reporting & Consolidation 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_consolidation_entry
Grain: one row per ACDOCU consolidation journal line — version × ledger × period × consolidation unit × FS item × subitem × partner unit × posting level
ACDOCU-grain consolidation journal fact carrying transaction/local/group currency amounts.
| Column | Type | Keys |
|---|---|---|
| consolidation_entry_key | bigint | PK |
| fiscal_period_key | bigint | |
| posting_date_key | bigint | |
| consolidation_unit_key | bigint | |
| partner_unit_key | bigint | |
| consolidation_group_key | bigint | |
| fs_item_key | bigint | |
| subitem_key | bigint | |
| consolidation_version_key | bigint | |
| consolidation_ledger_key | bigint | |
| posting_level_key | bigint | |
| document_type_key | bigint | |
| consolidation_document | varchar | |
| document_line | varchar | |
| amount_transaction_currency | decimal | |
| amount_local_currency | decimal | |
| amount_group_currency | decimal | |
| quantity | decimal | |
| transaction_currency | varchar | |
| local_currency | varchar | |
| group_currency | varchar |
Dimension tables (10)
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_period_end | boolean |
dim_fiscal_period
Grain: one row per SAP fiscal year/period (ryear/poper)
| 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_consolidation_unit
Grain: one row per consolidation unit per SCD2 version (also role-played as partner unit)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| consolidation_unit_key | bigint | PK |
| consolidation_unit | varchar | BK |
| unit_name | varchar | |
| country | varchar | |
| local_currency | varchar | |
| consolidation_method | varchar | |
| parent_group | varchar | |
| is_active | boolean |
dim_consolidation_group
Grain: one row per consolidation group hierarchy node
| Column | Type | Keys |
|---|---|---|
| consolidation_group_key | bigint | PK |
| consolidation_group | varchar | BK |
| group_name | varchar | |
| group_currency | varchar | |
| hierarchy_level | smallint | |
| parent_group_node | varchar | |
| is_top_group | boolean |
dim_fs_item
Grain: one row per financial-statement (FS) item (RITEM, mapped from RACCT)
| Column | Type | Keys |
|---|---|---|
| fs_item_key | bigint | PK |
| fs_item | varchar | BK |
| fs_item_description | varchar | |
| fs_item_type | varchar | |
| statement_section | varchar | |
| hierarchy_node | varchar | |
| breakdown_category | varchar | |
| is_balance_sheet | boolean | |
| is_pnl | boolean |
dim_subitem
Grain: one row per subitem (movement/flow code — RSUBITEM)
| Column | Type | Keys |
|---|---|---|
| subitem_key | bigint | PK |
| subitem | varchar | BK |
| subitem_label | varchar | |
| subitem_category | varchar | |
| sort_order | smallint |
dim_consolidation_version
Grain: one row per consolidation version (actual / plan / forecast / restatement)
| Column | Type | Keys |
|---|---|---|
| consolidation_version_key | bigint | PK |
| version | varchar | BK |
| version_label | varchar | |
| version_category | varchar | |
| special_version | varchar |
dim_consolidation_ledger
Grain: one row per consolidation ledger (RLDNR)
| Column | Type | Keys |
|---|---|---|
| consolidation_ledger_key | bigint | PK |
| ledger | varchar | BK |
| ledger_name | varchar | |
| accounting_principle | varchar | |
| ledger_scenario | varchar |
dim_posting_level
Grain: one row per posting level (reported vs adjustment vs elimination)
| Column | Type | Keys |
|---|---|---|
| posting_level_key | bigint | PK |
| posting_level | varchar | BK |
| posting_level_label | varchar | |
| is_elimination | boolean | |
| is_reported_data | boolean |
dim_document_type
Grain: one row per consolidation document type
| Column | Type | Keys |
|---|---|---|
| document_type_key | bigint | PK |
| document_type | varchar | BK |
| description | varchar | |
| posting_level_default | varchar | |
| is_manual | boolean | |
| is_automatic | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_consolidation_entry.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_consolidation_entry.posting_date_key → dim_date (many-to-one)
- fct_consolidation_entry.consolidation_unit_key → dim_consolidation_unit (many-to-one)
- fct_consolidation_entry.partner_unit_key → dim_consolidation_unit (many-to-one)
- fct_consolidation_entry.consolidation_group_key → dim_consolidation_group (many-to-one)
- fct_consolidation_entry.fs_item_key → dim_fs_item (many-to-one)
- fct_consolidation_entry.subitem_key → dim_subitem (many-to-one)
- fct_consolidation_entry.consolidation_version_key → dim_consolidation_version (many-to-one)
- fct_consolidation_entry.consolidation_ledger_key → dim_consolidation_ledger (many-to-one)
- fct_consolidation_entry.posting_level_key → dim_posting_level (many-to-one)
- fct_consolidation_entry.document_type_key → dim_document_type (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.