§ 1.9section 22 of 25·~6 min
Dimensional modeling: facts and dimensions
Ralph Kimball's The Data Warehouse Toolkit (with Margy Ross; 3rd edition, Wiley, 2013) is the standard guide to designing warehouse tables. Its core idea, dimensional modeling, splits data into two kinds of table.
- Fact tables hold measurements of business events: an order line, a payment, a delivery. Mostly numbers you add up (amount, quantity, delivery minutes) plus keys pointing to dimensions. They're long: millions or billions of rows.
- Dimension tables hold the context: who, what, where, when. Customer, product, store, date. Mostly descriptive text you filter and group by. They're short and wide.
The star schema
Put the fact table in the middle and the dimensions around it, and you get a star schema:
dim_date
│
dim_customer ── fct_order_lines ── dim_product
│
dim_store
-- Revenue by product category and month: one join per question you ask
SELECT p.category, d.month, SUM(f.amount_paise) AS revenue_paise
FROM fct_order_lines f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
GROUP BY p.category, d.month;
Grain comes first
Before designing a fact table, declare its grain: what exactly one row means. "One row per product per order" is a grain. "Orders data" is not. Mixing grains in one table is the classic way to double-count revenue.
| Fact table | Dimension table | |
|---|---|---|
| Holds | Measurements of events | Descriptive context |
| Example columns | amount_paise, qty, customer_key | customer_name, city, segment |
| Shape | Very many rows, few columns | Fewer rows, many columns |
| You use it to | Sum, count, average | Filter, group, label |
When a dimension changes over time (a customer moves city), you need slowly changing dimensions to keep history correct. You'll build those in the Data Modeling & Warehousing chapter.