Star schema design

Before the fifth model session, on Tuesday, November 3, Marisol asked Keiko to bring the spreadsheet that fed her old sales report. It was one enormous table: every sales line, with the store name, region, product description, category, supplier, date, month name, fiscal quarter and customer tier repeated on every row. It worked, after a fashion, but every new question meant another column, and a change to a category name meant rewriting years of rows. Marisol drew a box in the middle of the whiteboard and six boxes around it. "This," she said, "is what the warehouse should look like."

Facts and dimensions

A star schema separates measurements from descriptions. A fact table holds the events the business measures, such as sales lines, with numeric columns that can be added up, such as quantity and amount, and keys that point to the dimensions. Dimension tables hold the descriptions used to filter and group those events: stores, products, dates, customers, promotions. Drawn as a diagram, the fact sits in the middle and the dimensions around it, which is where the name comes from.

The design is not just tidy; it is what Power BI's engine is built for. Filters on a dimension, such as Region = Prairie, flow along a relationship to the fact table, and the engine compresses dimension columns well because each value is stored once. A star schema also makes measures simpler to write and easier for report authors to understand, because there is one obvious place for each kind of field.

Grain first

The most important decision in a fact table is its grain: what one row represents. Marisol made Keiko write the grain as a sentence before naming any columns: "one row per sales line on a till receipt". Every other choice follows from it. The dimensions are the things that have one value per sales line: one date, one store, one product, one customer (or the non-member customer), one promotion, one channel. The facts are the numbers measured at that grain: quantity, sales amount, discount amount and cost amount.

Mixing grains in one table is the classic error. Keiko's old spreadsheet had a column for the store's monthly target on every sales line, which made summing targets meaningless: a store with 40,000 lines in a month showed a target 40,000 times too big. Targets have a different grain, one row per store per month, so they belong in their own fact table, fact_sales_target, that shares the store and date dimensions with fact_sales.

Keys

Dimensions use surrogate keys, the whole-number keys from lesson 3, as their primary keys, and fact tables store those keys. There are three reasons. Surrogate keys make Type 2 history possible, because the same store can have several rows. They insulate the model from source changes, such as StockLine renumbering SKUs. And whole numbers compress and join faster than long text codes. The business key, such as the SKU or store number, stays in the dimension as an ordinary column so people can still search for it.

The date dimension is the exception that people argue about. Many designs use a smart key such as 20261103 for November 3, 2026, because it is readable and sorts correctly, and dates do not change. Keiko used that, and built dim_date with one row per day from January 1, 2019 to December 31, 2030, with columns for the day, month, quarter, the retail fiscal year, which at Harrow & Finch starts on February 1, a flag for statutory holidays and a flag for whether the day is before today.

Dimensions that do more than one job

Some dimensions appear more than once. A sales line has a sale date; a special order also has a promised delivery date. Both point to the same dim_date, which then plays two roles. In the warehouse that is simply two key columns; in the semantic model it means two relationships to one table, only one of which can be active, as lesson 8 explains. A degenerate dimension is a key that has no other attributes, such as the receipt number: it lives in the fact table itself, because a dimension table with only one column would add nothing. A junk dimension gathers small flags, such as "paid by card" and "price overridden", into one small table of their combinations rather than cluttering the fact.

Star or snowflake?

A snowflake schema normalizes dimensions into chains of tables: product to subcategory to category to department. It saves a little space and mirrors the source system, but it adds relationships for the model to follow and for report authors to understand. For Power BI semantic models, Microsoft's guidance favours a star, with each dimension denormalized into one table. Keiko flattened StockLine's four product tables into one dim_product with category and department columns, which is exactly the denormalization step in the DP-600 outline.

# Why a star is smaller than one wide table (rough, illustrative sizes)
fact_rows = 41_200_000
# bytes per row of descriptive text in the old wide table (store, product, category, region, month names...)
wide_text_bytes = 180
key_bytes = 6 * 4                 # six whole-number keys in the fact table
measure_bytes = 4 * 8             # four numeric measures

dims = {"dim_product": (48_000, 160), "dim_store": (27, 120), "dim_date": (4_383, 60),
        "dim_customer": (310_000, 90), "dim_promotion": (900, 70), "dim_channel": (3, 20)}

wide = fact_rows * (wide_text_bytes + measure_bytes)
star = fact_rows * (key_bytes + measure_bytes) + sum(rows * size for rows, size in dims.values())

print(f"Wide table: {wide / 1e9:.1f} GB before compression")
print(f"Star:       {star / 1e9:.1f} GB before compression")
print(f"Star is about {wide / star:.1f} times smaller")

Wide table: 8.7 GB before compression
Star:       2.3 GB before compression
Star is about 3.7 times smaller

The numbers are illustrative, and columnar compression changes them a great deal in practice, because a store name repeated 41 million times compresses very well. The deeper benefit is not size but change: when a category is renamed, the star changes one value in dim_product, and the wide table rewrites every row that mentions it.

Bridge tables

Most relationships in a star are one-to-many: one store has many sales lines. Sometimes the business has a genuine many-to-many relationship. In Finch Rewards, a household can have several members and, since 2025, a member can belong to more than one household, for example a parent who shares points with two adult children. Reporting points earned by household needs a bridge table, bridge_household_member, with one row per member and household pair. Facts link to members, members link to the bridge, and the bridge links to households, so a filter on a household reaches its members' facts. Lesson 8 shows how the semantic model handles the double counting that this can cause when a member is in two households.

Factless facts and snapshots

Two more patterns came up as Keiko listed Grace's other questions. A factless fact table records that something happened with no numbers attached, such as a promotion being active for a product in a store on a day; counting its rows answers "which promotions ran where" and, compared with sales, "which promoted products did not sell". A periodic snapshot records a state at regular intervals, such as stock on hand per product per store each night; snapshots are not additive over time, because adding Monday's stock to Tuesday's means nothing, so their measures must be averaged or taken at the last date of a period.

Conformed dimensions

Because the hub has several fact tables, the same dimensions have to mean the same thing in each of them. A conformed dimension is one that is shared, with the same keys and attributes, by every fact table that uses it: dim_store and dim_date serve fact_sales, fact_sales_target and fact_inventory_adjustment alike. Conformed dimensions are what make it possible to put sales, targets and shrink side by side in one report and have the store and month filters apply to all three. If the inventory team had built its own store table with different keys, every comparison would need a mapping, and sooner or later two reports would disagree about which stores are in the Prairie region.

Keiko found one place where conformance needed work. StockLine's adjustment records used the old four-digit store codes, while sales used the newer two-digit store numbers. Rather than adding a second store dimension, she added the old code as an attribute of dim_store and looked it up during the adjustment load, so every fact table ended up with the same StoreKey. Marisol called this the quiet work of modelling: nobody sees it in a report, but without it the numbers do not line up.

The date dimension in practice

A date dimension earns its place in almost every model. It turns a plain date into everything people filter by: day of week, week number, month name, the retail fiscal year and quarter, holiday flags and relative flags such as "in the last 13 weeks". Building it once, in the warehouse, means every model and every report uses the same fiscal calendar, which at Harrow & Finch had previously been worked out by hand in three different spreadsheets that did not agree about which week belonged to which quarter. The table is tiny, about 4,400 rows for twelve years, so there is no reason to skimp on columns. In the semantic model it is marked as a date table, which tells Power BI to use it for time intelligence calculations, as lesson 9 shows.

Designing in the warehouse

Keiko created the star in wh_sales with T-SQL: dimensions first, each with its surrogate key, then the fact tables. Fabric warehouses support primary key, foreign key and unique constraints only as NOT ENFORCED declarations; they document the design and help some tools, but the engine does not check them, so the load procedures and the data quality checks from lesson 3 must guarantee that every fact key exists in its dimension. She also wrote the grain sentence for each fact table into the table's description, so that the next person to add a column would see it first.