Once you have several tables, how you arrange them decides whether the model is a pleasure or a minefield. Professionals reach for the star schema, an arrangement proven over decades of data warehousing. It has one central fact table surrounded by several dimension tables, joined like the points of a star.
- A fact table records events or measurements, one row per thing that happened. A sales fact has one row per line item: which product, which region, which date, how much (
Amount), how many (Quantity). Fact tables are long and narrow-ish. They run to millions of rows, and they're mostly foreign keys plus a few numeric measures-to-be, the columns you'll sum. The fact table is where the numbers you care about live. - A dimension table records the things you slice by, one row per distinct entity, with descriptive attributes. In a Product dimension there's one row per product, carrying its name, category, brand. Region gets one row per region, with its name, zone, manager. Date gets one row per calendar day, with its month, quarter, year, weekday. Dimensions are short and wide-ish. Expect few rows and many descriptive columns. They're the labels you drag onto rows, columns, and slicers.
Advertisement
The grain of a fact table is the precise meaning of one row, such as "one product sold, in one region, on one date." Fix the grain before anything else. When the grain is muddled, with some rows per order and others per line, the fact table produces numbers no measure can rescue.
The relationship that binds a dimension to a fact is one-to-many. One row in the dimension, say one product, relates to many rows in the fact, the many sales of that product. Mind that direction, because the rest of the model depends on it. On the "one" side, keys must be unique, so each ProductID appears just once in the Product table. On the "many" side is the fact, where a ProductID recurs across thousands of sales. If the "one" side has duplicates, Excel refuses to create the relationship, because it wouldn't know which matching row to use.
Why use a star schema, and how do you build one?
Why a star, though? Because filters flow cleanly from dimensions into the fact. Put Region[Name] on the rows of a PivotTable and Product[Category] on a slicer, and each one filters the single fact table. [Total Sales] then recomputes for the intersection. There's no doubt about the path a filter takes, because every dimension connects directly to the fact and to nothing else. Other arrangements bring in longer, ambiguous filter paths that make measures hard to reason about. One is the snowflake, where dimensions hang off other dimensions. Worse still is joining tables fact-to-fact. Keep it a star and the model stays legible. You'll meet controlled exceptions later, but as a brown-belt default, think one fact, direct dimensions, one-to-many.
To build a relationship, open the Power Pivot window's Diagram View and drag the foreign-key field on the fact, say Sales[RegionID], onto the matching key on the dimension, Region[RegionID]. A line appears with a 1 at the dimension end and a ∗ (many) at the fact end. If you'd rather not open Power Pivot, use Data ▸ Relationships ▸ New and pick the two tables and key columns from the dropdowns. Either way, check that the 1 is on the dimension end.