SAP is a transaction-processing system, not an analytical one. The same column can mean different things in different document types, the same business event lives across multiple tables, and the journal-document structure is normalized to a degree that makes ad-hoc analytical querying painful. A dimensional model -- specifically a star schema -- is the layer that makes SAP data useful to a BI tool, a finance team, or an executive dashboard.
This guide is for analytics engineers who already know SAP. We are not going to enumerate every FI/CO table or argue about whether to use BSEG, BKPF, or COEP as the source for journal lines. Instead, we will walk through the dimensional design decisions that consistently produce a usable warehouse on top of SAP, with enough callouts to module-specific quirks to keep you out of the most common traps.
Pick the Right Fact-Table Grain First
The single most damaging mistake in SAP warehouses is conflating grain. Finance has at least four candidate grains that often get mashed into a single "fact_financial_postings" table, and the result is irreconcilable totals at every cut.
The grains worth declaring explicitly:
- Journal line item (one row per posting line, the BSEG/BKPF or ACDOCA-equivalent grain). This is the atomic fact. Debits and credits balance per document. Useful for trial balance, GL reconciliation, and audit traceability.
- Cost element posting (one row per CO posting in COEP / ACDOCA-CO). This is the grain at which CO sees costs -- by cost center, internal order, or WBS element. It is not the same as the FI journal line for several reasons, including statistical postings and CO-internal allocations that never reach FI.
- Document header (one row per BKPF document). Useful for posting counts, document-type analysis, and posting-period aggregates that do not need line detail.
- Period balance (one row per account / cost center / period). Pre-aggregated, typically from GLT0, FAGLFLEXT, or a snapshot of ACDOCA. Useful for fast time-series reporting; lossy on detail.
Declare each grain in a separate fact table. Conform the dimensions across them. Do not attempt a one-table-rules-them-all design -- it always loses to the report writer who wants line detail for one slice and pre-aggregated balances for another.
ACDOCA Changes the Calculus
With S/4HANA, ACDOCA -- the Universal Journal -- merges FI and CO line items into a single source. If you are modeling on top of S/4HANA, your atomic fact table is essentially ACDOCA, and many of the historical join headaches (BSEG to COEP, FAGLFLEXA to BSIS) collapse into a single source.
The dimensional design is largely the same -- declare grain, build conformed dimensions, separate processes by fact table -- but you no longer need parallel CO-side and FI-side fact tables for the same posting. One ACDOCA-derived journal-line fact table covers both perspectives, with cost-center and profit-center dimensions slicing it for the CO views.
On classic ECC, expect to maintain two or three parallel atomic fact tables (FI journal line, CO posting, profitability segment) with conformed dimensions across them. The conformed dimensions are the integration layer; do not try to integrate at the fact-table level.
Conformed Dimensions That Always Show Up
Some dimensions are universal across SAP-sourced star schemas. Build them once, conform them everywhere.
Date. A standard date dimension keyed on the calendar date of the posting (BKPF-BUDAT, the posting date) plus role-playing copies for document date, entry date, and clearing date. SAP's fiscal-year variants need their own attributes on this dimension -- fiscal year, fiscal period, and fiscal-year-variant key -- because many enterprises run non-calendar fiscal years and posting period 13-16 for adjustments.
Company code. One row per legal entity. SAP's company-code dimension (T001) carries currency, country, chart-of-accounts assignment, and fiscal-year variant. These attributes drive currency-conversion logic and consolidation reporting.
GL account. One row per chart-of-accounts entry (SKA1/SKB1 in classic, with chart-of-accounts in SKA1 and company-code-specific extensions in SKB1). Include the account group, P&L vs. balance-sheet flag, and a financial-statement-version hierarchy as snowflaked attributes or a separate hierarchy table.
Cost center. One row per cost center (CSKS), with the cost-center hierarchy (KSH1, KSH2) attached either as parent-level attributes or via a separate hierarchy table. The cost-center hierarchy is almost always needed for management reporting, and SAP's standard hierarchy is rarely the only one in play -- expect to model multiple alternative hierarchies.
Profit center. Similar shape, often dotted-line-organizational rather than legal-entity-aligned.
Vendor / customer. From LFA1 / KNA1 plus their company-code-specific extensions (LFB1, KNB1). Mind the SAP business-partner refactor in S/4HANA -- in newer systems these are unified under BUT000 / BUT020, and you will want a single Business Partner dimension role-played as Vendor and Customer rather than two separate dimensions with overlapping data.
Document type. One row per BKPF document type (T003). Small dimension, but it drives a lot of filtering ("show me only customer invoices, not residual postings") and should not be modeled as a fact-table attribute.
The FI/CO Reconciliation Trap
A perpetual question on SAP warehouses: why do my FI totals and CO totals not match?
The honest answer is that they often shouldn't -- not perfectly. CO posts statistical entries that never touch FI. CO allocations move costs between cost centers without an FI counter-posting. FI postings to balance-sheet accounts have no CO partner. Reconciliation between the two modules has its own ledger (KALC, in ECC; obsoleted by ACDOCA in S/4HANA) and its own analytical questions.
Model this honestly. Have a separate fact table for CO postings (or a CO-perspective view of ACDOCA), and provide a conformed date / company-code / cost-element dimension so that a user can pull "total primary cost element postings in Q3" from either side and get the same number. Document where the numbers legitimately diverge and provide a reconciliation report rather than fighting the source data.
Slowly Changing Dimensions in SAP
SAP master data changes. Cost centers get re-parented. Vendors get merged. Account hierarchies get restated. A Type-2 slowly changing dimension approach is the default for SAP master data, but pick your battles -- a Type-1 (overwrite) approach is often fine for descriptive attributes like address or contact name, while a Type-2 (history) approach is essential for organizational attributes like cost-center-to-profit-center assignment.
A trap specific to SAP: the cost-center hierarchy lives outside the cost-center master data (in tables like SETHEADER / SETNODE). Changes to the hierarchy do not appear as changes to the cost-center record. If you Type-2 the cost-center dimension and ignore hierarchy changes, you will silently lose the ability to report "what did the org chart look like in Q3 last year." Either capture hierarchy changes into the SCD trigger logic, or model the hierarchy as its own period-effective dimension.
SD (Sales) and the Document Flow
For sales analytics, the natural fact-table grain is the billing-document line (VBRP joined to VBRK). But a sales-order line, a delivery line, and a billing line are all linked through SAP's document flow (VBFA), and there is real value in being able to ask "what is the lag between order and billing" or "what percentage of order lines shipped this period."
The cleanest pattern is an accumulating-snapshot fact table at the order-line grain, with date columns for order, delivery, and invoice and measures for the quantities at each step. Keep the billing fact table separate for revenue reporting -- the billing line is the source of truth for booked revenue, and you should not derive it from the order line.
Putting It Together
A working SAP FI/CO warehouse typically lands at:
- 4–6 fact tables (journal line, CO posting, period balance, document header, plus optional SD billing line and accumulating-snapshot order line)
- 10–15 conformed dimensions (date, company code, GL account, cost center, profit center, business partner, document type, currency, material, plant, customer hierarchy, profit-center hierarchy)
- A handful of bridge tables for many-to-many hierarchies
This is more tables than a greenfield Kimball model on a simpler source, but it is far fewer than a one-to-one mirror of the SAP transactional schema. The goal is not to replicate SAP -- it is to make SAP queryable.
Going Deeper
For the underlying Kimball patterns referenced throughout this guide, see What Is a Star Schema? and Kimball Dimensional Modeling. For an alternative architectural approach that some SAP warehouses prefer for the raw integration layer, see Data Vault 2.0 Explained -- a common pattern is Data Vault below, star schema on top.
If you are starting an SAP warehouse from scratch and want to skip the "stare at BSEG for three weeks" phase, paste an extract of your SAP table structure into the demo and let the assistant propose a starting dimensional model. The output will not be production-ready -- it never is for SAP -- but it gives you a structured starting point to iterate from.