1.2.1. Facts, Dimensions, and Grain
💡 First Principle: Before you can model anything, you must decide what one row represents. That decision, called the grain, determines which questions the table can answer and which dimensions it can relate to. A table at daily grain can never answer an hourly question, no matter how clever the DAX.
Fact tables record measurable business events or snapshots: sales lines, website visits, inventory counts, budget amounts. They are typically long (many rows), narrow (few columns), and contain two kinds of columns: keys that point to dimensions, and numeric measure columns (quantity, amount, cost) that you aggregate.
Dimension tables describe the "who, what, where, when" of facts: Customer, Product, Store, Date, Employee. They are typically short (relatively few rows), wide (many descriptive columns), and contain a unique key plus descriptive attributes used to filter, group, and label (category, color, region, fiscal quarter).
| Characteristic | Fact table | Dimension table |
|---|---|---|
| Represents | Events or measurements | Business entities and their attributes |
| Row count | Large (thousands to billions) | Smaller (tens to millions) |
| Columns | Foreign keys + numeric measures | Unique key + descriptive text and categories |
| Used for | Aggregation (sum, count, average) | Filtering, grouping, labeling |
| Relationship side | Many side | One side |
| Examples | Sales, Orders, Inventory Snapshot | Product, Customer, Date, Geography |
Grain must be consistent within a fact table and stated explicitly: "one row per order line," "one row per store per day." Mixed grain, such as daily actuals and monthly targets in one table, breaks aggregation. When two facts naturally have different grains, keep them as separate fact tables and relate both to shared dimensions. A monthly Budget fact can relate to the Date dimension at month level while the Sales fact relates at day level; you'll see how to handle this in Phase 3.
Surrogate keys (simple integer IDs created for the model) are preferred when source keys are long text, composite (spread across several columns), or inconsistent across systems. Integer keys compress well and make relationships fast and reliable.
⚠️ Exam Trap: Putting monthly budget values into a daily sales table "so everything's in one place" duplicates the monthly amount on every day row, and summing it inflates the budget roughly thirty-fold. When grains differ, the answer is two fact tables sharing dimensions, not one merged table.
Reflection Question: A table has one row per product per store per week, with a column for units on hand. What is its grain, which dimensions can it relate to, and why can't it answer "units on hand on Tuesday"?
