Grain is the precise specification of what one row in a fact table represents — the fundamental unit of measurement captured in the data warehouse for a given business process. In a GL journal fact table, the grain might be “one row per GL journal line item” (the most atomic level — each debit and credit posting from Oracle EBS appears as a separate row), or “one row per account-entity-period combination” (a summarised grain — monthly balance for each account in each entity, with all journal line details aggregated). In a finance BI context, the grain determines what questions the fact table can answer: a journal-line-level grain can answer “show me all manual journal entries for this account this period” and “what is the total balance?” — a period-summary grain can only answer the second question. The grain must be defined and agreed upon before any dimension or measure decisions are made — all other aspects of the fact table design derive from it.
Grain Levels for Finance Fact Tables
| Grain Level | Row Represents | Analytical Capability | Storage Implication |
|---|---|---|---|
| Journal line (atomic) | One GL debit or credit posting | Full drill-through; individual transaction audit; posting date analysis | Largest — millions of rows per year in active ERP |
| Document (mid-level) | One invoice, one journal entry (header-level) | Invoice-level analysis; journal approval tracking; document-level aging | Medium — hundreds of thousands per year |
| Account-period summary | Monthly balance for one account-entity combination | Trend analysis; budget vs actual; no transaction drill-through | Small — depends only on account × entity × period count |
| Mixed grain (multiple grains in one table) | Rows represent different things in the same table | Inconsistent — analytical errors result | N/A — should never be designed this way |
Grain and the Finance Reporting Use Case
The choice of grain for a finance fact table must be driven by the lowest level of detail any report consumer will need — because a fine grain supports both summary reporting (through aggregation) and detail reporting (through no aggregation or filtering), while a coarse grain can only support summary reporting. A management dashboard that shows monthly P&L by entity needs only account-period summary grain. An audit drill-through capability that lets an internal auditor see every journal entry that makes up a specific account balance needs journal-line-level grain. An accounts payable aging analysis that shows individual invoice due dates needs document-level grain. When the reporting use case requires multiple grain levels simultaneously, the standard design is multiple fact tables — one at each required grain — rather than a single fact table that tries to accommodate all grains in one table (which is always incorrect).
GCC Finance Grain Decisions
In GCC enterprise finance data warehouses, two grain decisions require specific regional consideration. First, ZATCA compliance reporting requires invoice-line-level grain in the AR fact table — because the VAT return requires breakdown of revenue by VAT treatment category at the individual invoice line level, which is only available if the grain captures individual invoice lines rather than invoice totals or period summaries. A GCC AR fact table designed at invoice-header grain cannot produce ZATCA-compliant VAT analysis without reconstructing line-level detail from the source ERP. Second, IFRS 16 lease liability calculations require periodic amortisation schedule data at the lease contract level — one row per lease per period — which is a specific grain requirement for a lease management fact table that must be identified at design time.
What Goes Wrong in Practice
The most common grain error in finance data warehouse implementations is a “mixed grain” fact table — where most rows represent account-period summaries but some rows represent individual adjusting journal entries, because the ETL developer loaded two different source datasets into the same fact table. A SUM of the amount column in a mixed-grain fact table double-counts: the period summary already includes the adjusting entries, which are then added again as separate rows. Grain must be consistent across every row in a fact table; different grain data always goes in different fact tables.
How Loop Wise Solutions Defines Grain
We define grain as the first line of every fact table specification document, before any dimension or measure decisions are made. The grain definition is reviewed and signed off by both the technical architect and the finance business owner before ETL development begins — because the grain determines the scope and cost of the entire fact table implementation.
