Facts, dimensions and grain
🎯You'll be able to
Separate a business process into facts (what's measured) and dimensions (how it's described), and declare the grain of a fact table.
🏠The everyday picture
A shop's bill lists what was bought (quantity, price) and is stamped with when, where and for whom. The amounts are facts; the stamp is the description you group by.
📘What it really is
-
A fact table records events of one business process: order lines, payments, deliveries. Each row holds measures (quantity, amount) and keys to dimensions.
-
A dimension table describes the who, what, where and when: customers, products, stores, dates. Its columns are what you filter and group by (category, area, weekday).
-
The grain is the most important design choice: exactly what one fact row stands for. Declare it in words before writing any SQL: "one row per order line".
-
Choose the lowest useful grain. Order lines can be summed into orders, days or stores; a table of daily totals can't be split back into products.
-
SabziSathi's model:
- fact
fct_order_lines: one row per order line, with quantity, amount, net amount and status; - dimensions
dim_products,dim_customers,dim_dateand the store code; - summaries
mart_daily_salesand the area marts, built from the fact.
📝A worked example
Input: the question "net revenue by product category, October 2026", answered from the solution's warehouse:
select p.category, sum(f.net_amount_paise) / 100 as net_rupees
from marts.fct_order_lines f join marts.dim_products p using (product_key)
group by 1 order by 2 desc;
Expected output: five rows, led by staples at ₹10,93,951 of the month's ₹19,65,764. The query has one join and one group by, because the fact holds the measure and the dimension holds the category.
🌍Where it's used
- Retail: sales facts by product, store and day.
- Insurance: claim facts by policy, hospital and diagnosis.
- Telecom: usage facts by customer, tower and hour.
📝Case scenario
A clinic chain built its first warehouse table at "one row per patient per month". Then the board asked which doctors saw the most follow-up visits. The table couldn't answer: visits had been summed away. Rebuilding the fact at "one row per visit" answered that question and every later one.
🖱️Try it
Write the grain, three measures and four dimensions for a fact table of SabziSathi deliveries (one rider delivering one order). Success: your grain is one sentence and every measure is a number.
⚠️Common mistakes
- No declared grain. Sign: some rows are orders and some are lines. Fix: write the grain down and test it (Project 2 test 2).
- Summarising too early. Sign: new questions need a new table. Fix: keep the lowest useful grain in the fact.
- Descriptions in the fact table. Sign: product names repeated in every line and out of date. Fix: keep them in the dimension.
🛠️For your project
Project 2 test 2 checks one row per order line in fct_order_lines, with a uniqueness test on its grain.
✅Quick check
What is a fact table's grain?
Show the answer
What one row stands for, such as one order line.
Why choose the lowest useful grain?
Show the answer
Detail can be summed up later; totals can't be split back down.