Facts, Dimensions and the Star
Meera's questions always have the same shape: "revenue by category", "on-time rate by warehouse, by week", "units by state, each month". A number, then a list of "by" words. Dimensional modelling builds the warehouse in exactly that shape, so every question like hers becomes a simple query.
The rule
Numbers go in facts; the "by" words become dimensions.
- A fact table records business events. Each row is one event at a declared grain, and holds the measures you add up (qty, revenue, cost) plus keys pointing to dimensions. It is long and keeps growing.
- A dimension table describes the things involved in events: one row per product, customer, date or warehouse. Its columns are what you filter and group by (category, fabric, state, month). It is short, wide and changes slowly.
- The grain is what one fact row means, said as a sentence: "one row per product in each order." Every row in a fact table has the same grain.
Put the fact in the middle and the dimensions around it, each one join away, and you get a star schema:
dim_date
(date, month, weekday, festival)
|
dim_product -------- fct_sales -------- dim_customer
(name, category, (date_key, product_key, (city, state,
fabric, colour) customer_key, first order date)
warehouse_key,
qty, revenue, cost)
|
dim_warehouse
(city, manager)A snowflake schema normalizes the dimensions further, for example moving category into its own dim_category table linked to dim_product. It repeats less, but every question about category needs one more join, so BI tools and analysts usually prefer the plain star.
One grain per fact table. Whether an order arrived on time is a fact about a whole order, not about one product line, so deliveries get their own table, fct_deliveries, with one row per delivered order. Monthly targets, set per category, also get their own table rather than being copied onto every sales row.
The shortcut
Underline the numbers, circle the "by" words, then say the grain out loud. The numbers are measures in a fact table, the "by" words are columns in dimensions, and the grain must be fine enough to answer every question you expect.
Read the question
"Revenue and units by category and customer state, for each month."
Drills
Answer fast — trust the rule.
In "average delivery days by courier, by week", which parts are dimensions?
Which column belongs in dim_product rather than in fct_sales?
Which is a proper grain statement for Weftwise's sales fact table?
What turns a star schema into a snowflake?
Why not copy the monthly revenue target onto every row of fct_sales?
Remember
- Numbers go in facts; the "by" words become dimensions.
- Underline the numbers, circle the "by" words, say the grain out loud.
- Trap: mixing grains in one fact table, such as order-level delivery flags or monthly targets on line-level rows.