Fact table

What is a fact table?

A fact table holds the numbers a business wants to measure: orders placed, invoice lines, payments, stock counts, support tickets, website visits. Each row records one measurement event, and each row carries the numeric values you later sum, count, or average. It sits at the centre of a star schema, the layout that dimensional modelling uses to keep reporting fast and readable.

Every fact table holds two things: the measures, and a set of foreign keys that point out to the surrounding dimension tables. The measures are the quantities; the foreign keys are how you slice those quantities by customer, product, date, region, or channel. A fact table is narrow but very tall, growing to billions of rows while a dimension next to it holds a few thousand.

Think of a till receipt. Each line, three bags of coffee sold on 17 April, is a fact row: a quantity and an amount tied to a product and a date. The product catalogue and the store details around it are the dimensions that give those numbers meaning.

The grain: what one row represents

Before you pick a single measure or dimension, you declare the grain: the exact business meaning of one fact row. Kimball calls this a binding contract on the design. One row per invoice is a different table from one row per invoice line, and one row per stock movement is different again from one row per product per day.

The rule that follows is strict. Every row in a fact table must sit at the same grain. Mixing detail rows and summary rows in one table is how double counting creeps in. Suppose an order has three lines and you copy the order total onto each line. Group by month, sum that column, and you report three times the real figure, because the amount belongs to the order and not to the line.

This is why granularity is the design decision everything else depends on. Declare it at the lowest useful level, the atomic grain, and you keep every question open. A sales table at one row per line still answers monthly revenue, while a table pre-aggregated to one row per store per month can never tell you which products sold together. You can always roll detail up, but you cannot split a total back down.

Foreign keys to the dimensions

A fact table does not store descriptions. It carries a foreign key for each dimension it relates to, and those keys point at the surrogate keys held in the dimension table rows. Names, categories, and addresses stay in the dimensions, which keeps the fact table narrow and fast to scan. Set each key as not-null and send missing or unknown values to a dedicated dimension member instead of leaving a blank. Keeping those dimensions flat rather than splitting them into a snowflake schema also keeps the joins around the fact table short.

There is one exception. A recognisable identifier such as an order number or invoice number often sits directly in the fact table with no dimension of its own, which is a degenerate dimension and a normal part of the design.

Additive, semi-additive and non-additive measures

Not every number adds up the same way, and this is where fact table design most often goes wrong. Measures fall into three classes by how they behave when you sum them.

Additive measures sum across every dimension, time included. Quantity sold and net revenue are the clean case: total them by day, by product, by store, or by any combination, and the answer stays correct.

Semi-additive measures sum across every dimension except time. Balances and levels behave this way: an account balance, units of stock on hand, a headcount. You can add today's stock across all products and stores to get the total held at that instant, but adding Monday's closing stock to Tuesday's and Wednesday's is meaningless. To aggregate a balance over time you average it or take the period-end value. The difference between an additive and a semi-additive measure is exactly the time dimension: revenue sums over time, a balance does not.

Non-additive measures cannot be summed across any dimension at all. Ratios, percentages, unit prices, and temperatures are non-additive: adding two unit prices or two discount percentages produces a number that means nothing. The fix is to store the additive parts instead: keep a discount amount and a revenue amount as separate measures and work out the percentage after aggregation, not before. A rate often turns additive once it is resolved, since unit price multiplied by quantity is revenue, which sums cleanly. Even a measure you cannot sum can still be counted, averaged, or reduced to a minimum or maximum.

Three kinds of fact table

Fact tables come in three shapes, split by what a row captures over time.

• Transaction tables record one row per event, fully known the moment it happens and never changed afterwards. A sales table at one row per order line is the classic case, and its measures are usually fully additive.

• Periodic snapshot tables record a measurement at fixed intervals, such as end-of-day stock for every product. Their measures are semi-additive by nature. This family has its own entry as the snapshot fact table.

• Accumulating snapshot tables follow a process through its milestones, with several date keys and a single row that is updated as the work moves from placed to shipped to invoiced. The measures are usually the durations between those steps.

The same fact table design carries from a hand-built data warehouse through to a Power BI semantic model, where the fact sits in the middle and the dimensions filter and group around it.