Picture an online home-goods store in Pune. A customer orders a lamp for ₹499, a cushion cover for ₹799 and a rug for ₹1,299, and pays a flat ₹60 delivery charge. That single checkout can be stored in a warehouse in several ways: one row for the whole order, one row per item, or one row per item per shipment if the rug ships separately. None of these is wrong. But each one answers a different set of questions, and mixing them up quietly breaks reports. The choice is called the grain, and it is the first design decision for any fact table.
Facts and dimensions, briefly
Dimensional modelling, the approach popularised by Ralph Kimball, splits data into two kinds of tables. A fact table records business events or measurements: sales, payments, clicks, deliveries, account balances. Its columns are mostly numbers you add up or average (quantity, amount, discount) plus foreign keys pointing at dimensions. A dimension table describes the who, what, where and when around those events: customer, product, store, date, sales channel. Dimension columns are mostly text and categories you filter and group by, such as product category, customer city or month name.
A simple test: if you would put it in a GROUP BY or a slicer, it probably belongs in a dimension. If you would put it inside a SUM, it probably belongs in a fact table. Fact tables tend to be long and narrow (millions or billions of rows), while dimensions tend to be short and wide (thousands of rows, dozens of descriptive columns).
What grain actually means
The grain is a plain-language statement of what one row in the fact table represents. “One row per order line” is a grain. “One row per customer per day” is a grain. “One row per account per month-end” is a grain. Kimball’s advice is to declare it before choosing any columns, because every other decision follows from it: which dimensions can attach to the row, which measures make sense on it, and how many rows you will store.
Back to the Pune order. At order grain, you get one row with an order total of ₹2,597 plus ₹60 delivery. You cannot answer “how many rugs did we sell?” because the product is not on the row. At order-line grain, you get three rows, each with a product key, quantity and line amount. Now product questions are easy. But where does the ₹60 delivery charge go?
The classic mistake: measures at the wrong grain
The tempting shortcut is to copy the order-level delivery charge onto every line. Each of the three rows now carries ₹60, and the first time someone sums delivery revenue for the day, the order contributes ₹180 instead of ₹60. Across a few lakh orders the overstatement is large, and nobody notices until finance compares the dashboard with the payment gateway settlement.
There are three honest ways out:
- Allocate the charge down to lines using a documented rule, for example in proportion to line value or weight. The lines then sum back exactly to ₹60. This keeps one fact table but bakes in a business rule that finance has to agree with.
- Keep a separate order-grain fact table for order-level measures (delivery charge, order-level coupon, payment fees). Line-level facts stay in their own table.
- Store it on one line only, such as the first line. This sums correctly but makes per-product analysis of delivery charges meaningless, so it is usually the weakest option.
The general rule: every measure in a fact table must be true at that table’s grain. If a number belongs to a coarser level, either allocate it properly or give it its own table.
Atomic grain versus summary grain
The finest level your source system captures is the atomic grain. For a retailer that is usually the POS line item or the e-commerce order line. Kimball’s long-standing recommendation is to build the base fact table at atomic grain, because you can always roll detail up into summaries but you can never break a summary back down. If you store only “sales per store per day”, the day a category manager asks for sales by product, or by hour, the warehouse has nothing to give.
Summary tables still have a place. A dashboard that only ever shows monthly revenue by region does not need to scan two billion line items each time it loads. The sensible pattern is atomic facts as the foundation, with aggregate tables built from them for speed. The aggregates are derived, so they can be rebuilt when definitions change.
How grain controls which dimensions can attach
A dimension can join to a fact row only if it has a single value for that row. At order-line grain, product is fine because each line has exactly one product. At order grain, product is not, because an order may contain several. If you try to attach a product key at order grain, you are forced to pick one product arbitrarily or duplicate the order row, and either choice corrupts the numbers.
This is also why stating the grain helps reviews. When someone proposes adding “salesperson” to a fact table, the first question is whether each row has exactly one salesperson. If a deal can be split between two people, you have a many-to-many situation that needs a different structure (a bridge table or an allocation), not just a new column.
Grain in everyday analysis
Grain is not only a warehouse-design concern. Most “the numbers don’t match” problems an analyst meets come from joining tables at different grains. Join a customer table (one row per customer) to an orders table (one row per order), then sum a customer-level field such as credit limit, and every customer’s limit is counted once per order they placed. A customer with 12 orders contributes 12 times their limit. The join did not fail; it did exactly what it was told at the new, finer grain.
A useful habit before any join is to ask: what does one row mean in each table, and what will one row mean in the result? If the answer to the last question is not what you expected, aggregate one side to the right grain before joining, not after.
Writing the grain down
Good teams record the grain in the table’s documentation and test it. If the grain is “one row per order line”, then the combination of order ID and line number should be unique, and a uniqueness test on that pair will catch duplicate loads early. If the grain is “one row per account per month-end”, then account key plus month key should be unique. A grain you cannot express as a unique key is a sign the table’s purpose is muddled.
The pay-off is predictability. When everyone knows exactly what a row is, measures add up the way people expect, new dimensions attach cleanly or are rejected for a clear reason, and the inevitable question “why is this number different from that one?” usually has a quick answer.
Written by the RP Strativa Team. Figures and the store in the examples are illustrative.
Leave a Reply