A fact table is the central, high-volume table in a dimensional data model that stores the measurable outcomes of a business process — financial amounts, quantities, counts, durations — at the level of detail defined by the model’s grain. Each row represents one occurrence of the business event at that grain: one GL transaction line, one budget record by account and period, one invoice line. Foreign key columns in the fact table link each row to the relevant dimension tables — which entity, which account, which time period, which cost centre, which scenario. The fact table answers the question “how much?” The dimension tables answer “of what, where, when, and for whom?”
How It Works
Fact tables are wide by row count and narrow by column count — many rows, few columns. A GL actuals fact table may contain hundreds of millions of rows if it stores every journal line across multiple years, but each row has relatively few columns: the foreign keys to each dimension, four or five measure columns, and perhaps an audit timestamp. This structure is optimal for columnar storage: the query engine reads only the columns referenced in the query, compressed column-by-column, scanning very efficiently even at very large row counts.
Fact tables fall into three categories by the nature of the event they represent: transaction fact tables (one row per transaction event — an invoice, a payment, a journal line), periodic snapshot fact tables (one row per entity per period — a balance at month-end, an inventory count at week-end), and accumulating snapshot fact tables (one row per business process instance, updated as milestones are reached — an order from placement through delivery and payment). Finance BI models typically use transaction fact tables for income statement data and periodic snapshot tables for balance sheet data.
Design Decisions and Trade-offs
The grain decision is irreversible at the physical level: once a fact table is loaded at a specific grain, the data cannot be disaggregated below that grain without rebuilding from the source. A fact table loaded at the monthly account-entity total level cannot later support drill-down to individual journal lines unless the table is rebuilt at journal-line grain. Grain must be set at the finest level of detail any BI consumer will ever need — erring on the side of finer grain and relying on aggregation at query time or through aggregate fact tables is safer than discovering a grain limitation after the model is in production.
Common Implementation Errors
The most consequential fact table implementation error in finance BI is loading fact tables with null foreign keys — dimension foreign keys that are null because the source data did not have a matching dimension member for a specific transaction. When null foreign keys exist in the fact table, queries that filter or group on that dimension silently exclude the transactions with null keys from the result. A revenue report that excludes transactions where the cost centre dimension key is null understates revenue without any error message — the query executed correctly against a model that was incorrectly loaded. Unknown or unclassified dimension members should be represented by an explicit “Unknown” or “Unclassified” dimension record (surrogate key -1 by convention), never by null, so that unclassified transactions appear in reports rather than disappearing silently.
How Loop Wise Solutions Designs for This
We enforce a no-null foreign key constraint in every fact table we design — configuring the ETL to route records with unresolvable dimension keys to an error table for investigation rather than loading them with null keys, and creating explicit “Unknown” dimension members for each dimension so that any records that pass the null check are still associated with a visible, investigable dimension value in BI reports.
