What Is a Fact Table?
A fact table is the central table in a dimensional data model that stores the measurable events of a business: the sales, invoices, payments, shipments, and transactions that happen over time. Each row in a fact table records one event, with the numeric measures of that event (the amount, the quantity, the cost) and keys that connect it to the dimensions describing it. Fact tables are where the numbers live in an analytics model.
Fact tables work together with dimension tables, which hold the descriptive context: who the customer was, which product was sold, what date it happened. A fact table at the center, surrounded by its dimensions, forms the star schema that most analytics models are built on. The fact table answers how much and how many; the dimensions answer who, what, when, and where.
Why Fact Tables Matter
The fact table is the foundation of almost every report and dashboard. When a user slices revenue by region, filters sales by product, or trends payments over time, they are aggregating measures from a fact table along its dimensions. Getting the fact table right, at the correct level of detail and with the correct measures, determines what questions the model can answer and how fast.
A well-designed fact table also keeps a model performant. Because fact tables hold the bulk of the rows in an analytics model, often millions of them, their structure has an outsized effect on query speed and storage. Modeling them well is one of the highest-leverage decisions in building an analytics foundation.
How Fact Tables Work
Measures. The numeric values being analyzed: sales amount, quantity, cost, margin. These are the columns that get summed, averaged, and aggregated in reports.
Foreign keys. Keys that link each fact row to its dimensions. A sales fact row carries keys to the customer, product, and date dimensions, so the sale can be sliced by any of them.
Grain. The level of detail one row represents, known as the grain of the fact table. A fact table might record one row per order line, per invoice, or per daily summary. Defining the grain clearly is the first and most important step in designing a fact table, because everything else depends on it.
Types of Fact Tables
Transaction fact tables record one row per event, such as a single sale or payment. They are the most common and most detailed.
Periodic snapshot fact tables record the state of something at regular intervals, such as an account balance at the end of each month. They suit balance-style metrics that accumulate over time.
Accumulating snapshot fact tables track a process with a defined start and end, such as an order moving through fulfillment, updating the same row as the process progresses.
Fact Tables in ERP Environments
Building fact tables from ERP data is where much of the modeling work happens. The transaction tables in JD Edwards, NetSuite, and Vista hold the raw events, but turning them into clean fact tables means choosing the right grain, deriving the correct measures, and connecting them to conformed dimensions like customer, product, and date.
A general ledger fact table, for example, might be built from the ERP’s account ledger at the grain of one row per journal line, with the amount as the measure and keys to the account, business unit, and date dimensions. Designing these fact tables correctly is a substantial part of what pre-built ERP models provide, because the grain and measure decisions for each ERP are already made.
Common Challenges and Best Practices
- Define the grain first. Decide exactly what one row represents before anything else. An unclear grain produces wrong aggregations that are hard to diagnose.
- Keep dimensions out of the fact table. Store descriptive attributes in dimension tables and reference them by key. A fact table bloated with descriptive columns performs poorly.
- Use conformed dimensions. Shared dimensions across fact tables let reports combine different facts, such as sales and returns, along the same customer or date.
- Store additive measures where possible. Measures that can be summed across all dimensions are the easiest to work with. Be careful with ratios and balances that do not sum.
- Mind the volume. Fact tables hold the most rows. Model them efficiently and use incremental loads to keep them current without full reloads.
Frequently Asked Questions
What is the difference between a fact table and a dimension table?
A fact table stores the measurable events of a business, the numbers being analyzed, along with keys to its dimensions. A dimension table stores the descriptive context, such as customer or product details. The fact table holds the measures; dimensions hold the attributes that give them meaning.
What is the grain of a fact table?
The grain is the level of detail that one row in the fact table represents, such as one row per order line or one row per daily balance. Defining the grain clearly is the most important step in designing a fact table, because every measure and dimension depends on it.
How do fact tables relate to a star schema?
A star schema places a fact table at the center, surrounded by the dimension tables that describe its events. The fact table holds the measures and foreign keys; the dimensions provide context. This structure is the foundation of most analytics models.
Fact Tables and QuickLaunch’s Approach
QuickLaunch Analytics ships pre-built semantic models with fact tables already designed for each source ERP, at the right grain, with the correct measures and conformed dimensions. Instead of making the grain and measure decisions for JD Edwards, NetSuite, Vista, and OneStream from scratch, teams start from fact tables that are modeled and proven across 250+ enterprise implementations.
