4.1 Star Schema Design

Quick Answer: Star schema is the gold standard for Power BI models. A central fact table holds numeric measurements (revenue, quantity, cost) and connects to surrounding dimension tables that supply descriptive context (who, what, when, where). This maximizes DAX performance and report simplicity, and Microsoft explicitly recommends it in the Star schema and the importance for Power BI guidance.

What is a Star Schema?

A star schema is a modeling pattern where a central fact table stores quantitative data (measures), multiple dimension tables surround the fact and provide descriptive context, each dimension connects to the fact through a one-to-many relationship, and the layout resembles a star. The pattern comes from data-warehouse pioneer Ralph Kimball, and Power BI's analytics engine is built to consume it.

The benefit is mechanical, not stylistic. In Power BI, filters flow from the one side (dimension) to the many side (fact). When every dimension sits exactly one hop from the fact, every slicer and axis has a single unambiguous path to the data it filters. Long relationship chains and bridge-heavy designs create multiple paths, forcing bi-directional filtering or USERELATIONSHIP workarounds.

Defining the Grain

Before building, define the grain of the fact table: the precise business event one row represents. "One row per order line" differs from "one row per order" or "one daily product summary." The grain dictates which dimensions attach, which measures are valid, and how detailed reports can be. A frequent mistake is mixing grains in one fact table (order-header values stored on the same row as order-line values), which double-counts measures on aggregation.

Fact Tables

Fact tables contain the numerical measurements your reports analyze.

Example: Sales Fact Table

Key columns (DateKey, ProductKey) are foreign keys; measure columns (Quantity, UnitPrice, TotalAmount) are values you analyze.

Types of Fact Tables

Dimension Tables

Dimension tables supply the descriptive context that gives numbers meaning. Each has a primary key (unique per row), descriptive attributes (name, category, address), natural hierarchies (Year > Quarter > Month > Day), and far fewer rows than the fact. Dimensions are wide (many columns) and shallow (few rows).

Building a Star Schema in Power BI

1. Identify the grain of the fact table.
2. Identify dimensions by asking who/what/when/where/how: who bought it (Customer), what (Product), when (Date), where (Store), how paid (Payment Method).
3. Create relationships in Model view by dragging a dimension key onto the matching fact key; set cardinality to One-to-Many and cross-filter to Single.
4. Hide technical columns so report builders filter on friendly dimension attributes, not cryptic keys.

Snowflake vs. Star Schema

Best Practice: Flatten snowflake dimensions into a single table in Power Query. VertiPaq's dictionary and run-length encoding compress repeated dimension values efficiently, so normalization's storage "savings" are largely illusory while the join cost is real.

Model Organization Best Practices

1. Arrange tables so the fact sits center and dimensions surround it.
2. Hide every foreign key and technical/internal column from report view.
3. Create display folders to group measures by business area.
4. Mark your date table (Table Tools > Mark as Date Table) so time intelligence works.
5. Disable Auto date/time (File > Options > Data Load) to stop hidden per-column date tables from bloating the model.

On the Exam

The PL-300 tests identifying fact vs. dimension tables from a scenario, one-to-many direction (dimension > fact), when to denormalize a snowflake, the role of the date dimension in time intelligence, and hiding technical columns. A common trap: choosing the dimension as the "largest" table; the fact is always largest.