1.2.2. The Star Schema and Why VertiPaq Loves It
💡 First Principle: Power BI's in-memory engine, VertiPaq, stores data column by column and compresses each column by replacing repeated values with small codes. The fewer distinct values a column has, the better it compresses. A star schema naturally keeps fact columns numeric and low-variety and moves repeated descriptive text into small dimension tables, which is exactly the shape VertiPaq compresses best.
A star schema places one fact table at the center with dimension tables around it, each connected by a one-to-many relationship from the dimension (one) to the fact (many):
Three benefits follow from this shape, and each one shows up on the exam. First, compression: the text "Kitchen" appears once in the Product table, not on a million sales rows. Second, predictable filtering: a slicer on Product[Category] filters the Product table, which filters Sales through the relationship, with no ambiguity about direction. Third, simple DAX: a measure such as SUM(Sales[SalesAmount]) automatically responds to any dimension because the relationships do the filtering work.
A snowflake schema normalizes dimensions further (Product → Subcategory → Category as separate tables). It's common in source systems, but in Power BI it adds relationships, slows filter propagation, and clutters the field list. The usual guidance is to flatten snowflaked dimensions into a single dimension table in Power Query, merging Category and Subcategory columns into Product, unless a table is genuinely shared at a different grain.
Column cardinality (the number of distinct values) is the lever behind model size. A transaction timestamp with seconds may have millions of distinct values and compress poorly; splitting it into a Date column and a Time column, or removing the time portion when it isn't needed, can shrink a model dramatically. You'll apply this in 3.3.1.
⚠️ Exam Trap: "Minimize model size" questions rarely want you to remove relationships or measures, which cost almost nothing. They want you to remove unused columns, reduce high-cardinality columns (split or round datetimes, drop unique IDs nobody reports on), and avoid storing descriptive text on fact rows.
Reflection Question: Why does moving a Product Category text column from a 10-million-row Sales table into a 2,000-row Product table shrink the model, even though the same information is still available to every visual?
