Facts, Dimensions and Grain
Arjun's cleaned export is one wide table. Every row repeats the branch's city and manager and the product's name and category. It works for six rows, but when the Pune manager changes, thousands of rows need editing, and questions such as "revenue per manager" get clumsy. Modelling starts by splitting that wide table into two kinds of table: facts and dimensions.
The rule
Facts record what happened; dimensions describe who, what, where and when.
- A fact table has one row per event — here, one line of an order. It holds the numbers you add up (Qty, Price, Line Revenue) and keys that point to dimensions (StoreID, ProductID, Date). It is long and keeps growing.
- A dimension table has one row per thing — a branch, a product, a day. It holds the descriptive columns you filter and group by (City, Manager, Category), and each row has a unique key. It is short and changes slowly.
- The grain of a fact table is what exactly one row represents. Say it as a sentence before you build: "one row per product in each order." Every row in a fact table must share the same grain.
Leaf & Cup's model:
| Table | Kind | Key | Other columns |
|---|---|---|---|
| Sales | Fact | — | OrderID, Date, StoreID, ProductID, Qty, Price, Line Revenue |
| Stores | Dimension | StoreID | City, Manager |
| Products | Dimension | ProductID | Product, Category |
| Date | Dimension | Date | Year, Month, Day Name |
With keys, the six January orders become: 1001 (5 Jan, S1, P1), 1002 (5 Jan, S2, P3), 1003 (6 Jan, S1, P2), 1004 (6 Jan, S2, P1), 1005 (7 Jan, S1, P3), 1006 (7 Jan, S2, P2). Stores holds S1 Pune and S2 Mumbai. Products holds P1 Masala Chai and P2 Green Tea, both in the Tea category, and P3 Samosa, in Snack.
The grain also limits what you can ask. A fact stored at one row per branch per day cannot answer "revenue by product", because the products were added together before the data arrived. Keep the finest grain you can afford.
The shortcut
Sort every column with one question: "Would I add it up, or filter by it?" Add it up → it stays in the fact. Filter or group by it → it moves to a dimension, and the fact keeps only the key.
List the columns
OrderID, Date, StoreID, City, Manager, ProductID, Product, Category, Qty, Price, Line Revenue.
The trap is mixing grains. Meera's monthly targets are one number per branch per month. Pasted into Sales, the target would repeat on every order line and add up to nonsense. Targets belong in their own fact table at branch-month grain, sharing the Stores dimension.
Drills
Answer fast — trust the rule.
Which column belongs in a fact table as a value you add up?
Which of these is a dimension table?
What is the grain of Leaf & Cup's Sales table?
Where should Meera's monthly branch targets go?
A fact table stores one row per branch per day. Which question can it NOT answer?
Remember
- Facts record what happened; dimensions describe who, what, where and when.
- Ask of each column: would I add it up, or filter by it?
- Trap: mixing grains in one fact table; give each grain its own fact.