Data Modeling in Power BI: Star Schema Explained

Most Power BI problems that look like DAX problems are really modelling problems: totals that double, slicers that do nothing, measures that need increasingly strange formulas. The fix is usually the same design that relational data warehouses have used for decades, the star schema. Microsoft's own guidance describes it as highly relevant to building Power BI models optimised for performance and usability (Microsoft Learn: star schema). This article explains the idea and then builds one from a real flat export, because the problems only become clear on real data.

Facts and dimensions

A star schema classifies every table as one of two types:

• Dimension tables describe business entities: products, customers, places, and time itself. Each has a unique key column plus descriptive columns used for filtering and grouping.
• Fact tables store observations or events, such as sales orders, stock balances or exchange rates. They hold keys that point to dimensions, plus numeric columns to summarise.

Dimension tables are usually small; fact tables are large and keep growing. Every report visual sends a query that filters, groups and summarises, and a star schema gives the model a table shape for each of those jobs: dimensions filter and group, facts are summarised. In Power BI there is no setting that declares a table a fact or a dimension; the relationships decide it. In a one-to-many relationship, the "one" side is the dimension and the "many" side is the fact.

A real flat export

Most data arrives as one wide table. The online retail export from the UCI repository is typical (UCI: Online Retail): every sale line repeats the product description and the customer's country.

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
print(f"{len(raw)} rows in one flat table:", ", ".join(raw.columns))

541909 rows in one flat table: InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country

Problem 1: the dimension key is not unique

The obvious product dimension is the distinct pairs of stock code and description. It fails:

pairs = raw[["StockCode", "Description"]].dropna().drop_duplicates()
print(f"product codes: {raw['StockCode'].nunique()} | distinct code-description pairs: {len(pairs)} | key unique: {pairs['StockCode'].is_unique}")
print(raw.loc[raw["StockCode"] == "15058A", "Description"].value_counts().to_string())

product codes: 4070 | distinct code-description pairs: 4792 | key unique: False
Description
BLUE POLKADOT GARDEN PARASOL    93
wet/rusty                        1

Warehouse staff sometimes typed stock notes such as "wet/rusty" into the description field, so one code has several descriptions. In Power BI, the "one" side of a relationship must contain unique values; a refresh that loads duplicates into that column fails entirely (Microsoft Learn: model relationships). The fix is a rule: one row per code, with its most common description.

Problem 2: facts with no matching dimension row

most_common = raw.dropna(subset=["Description"]).groupby("StockCode")["Description"].agg(lambda s: s.value_counts().index[0])
orphans = ~raw["StockCode"].isin(most_common.index)
print(f"codes with no description at all: {raw.loc[orphans, 'StockCode'].nunique()}, on {orphans.sum()} rows")

codes with no description at all: 112, on 112 rows

A dimension built only from rows with descriptions would miss 112 codes. Power BI does not reject such rows: it adds a blank virtual row to the dimension, an "unknown member", and the affected sales appear under "(Blank)" in every visual (Microsoft Learn: model relationships). Better to include every code and label the gap explicitly. Customers need the same treatment: many sales have no customer ID, so they get an explicit "Unknown" customer.

Building the star

dim_product = (pd.DataFrame({"StockCode": raw["StockCode"].unique()})
               .assign(Description=lambda d: d["StockCode"].map(most_common).fillna("(no description)")))
dim_customer = (raw.dropna(subset=["CustomerID"]).groupby("CustomerID")["Country"]
                .agg(lambda s: s.value_counts().index[0]).reset_index().astype({"CustomerID": "int64"}))
dim_customer = pd.concat([dim_customer, pd.DataFrame({"CustomerID": [-1], "Country": ["Unknown"]})], ignore_index=True)
first, last = raw["InvoiceDate"].min(), raw["InvoiceDate"].max()
dim_date = pd.DataFrame({"Date": pd.date_range(f"{first.year}-01-01", f"{last.year}-12-31")})
dim_date = dim_date.assign(Year=dim_date["Date"].dt.year, MonthNumber=dim_date["Date"].dt.month,
                           Month=dim_date["Date"].dt.strftime("%b"))
fact_sales = pd.DataFrame({"InvoiceNo": raw["InvoiceNo"], "Date": raw["InvoiceDate"].dt.normalize(),
                           "StockCode": raw["StockCode"], "CustomerID": raw["CustomerID"].fillna(-1).astype("int64"),
                           "Quantity": raw["Quantity"], "UnitPrice": raw["UnitPrice"]})
for name, table, key in [("dim_product", dim_product, "StockCode"), ("dim_customer", dim_customer, "CustomerID"),
                         ("dim_date", dim_date, "Date")]:
    print(f"{name:<13}{len(table):>7} rows | key unique: {table[key].is_unique} | all fact keys match: {fact_sales[key].isin(table[key]).all()}")
print(f"{'fact_sales':<13}{len(fact_sales):>7} rows")

dim_product     4070 rows | key unique: True | all fact keys match: True
dim_customer    4373 rows | key unique: True | all fact keys match: True
dim_date         730 rows | key unique: True | all fact keys match: True
fact_sales    541909 rows

Three small dimensions and one large fact table, with every key unique and every fact matched. A few choices are worth noting:

• Country moved to the customer dimension. Eight customers appear with more than one country; the rule keeps the most common one, and the rule should be documented.
• The date table covers full years with no gaps, because Microsoft's date table guidance requires unique dates, no blanks, no missing dates and full years (Microsoft Learn: date tables).
• The fact date has no time part. A relationship between a date table and a date-time column with times would match almost nothing.
• The invoice number stays in the fact table as a degenerate dimension; a separate table for one column would add clutter without benefit.

The same steps in Power BI

In Power BI, these steps belong in Power Query. The product dimension, for example, is a reference to the sales query, grouped by stock code. List.Mode raises an error on an empty list, so codes without any description are labelled first:

let
    Source = Sales,
    Grouped = Table.Group(Source, {"StockCode"},
        {{"Description", each
            let known = List.RemoveNulls([Description])
            in if List.IsEmpty(known) then "(no description)" else List.Mode(known),
          type text}})
in
    Grouped

Relationships then run one-to-many from each dimension to the fact table, with single-direction filtering; Microsoft recommends bi-directional filtering only where needed. Finally, create explicit measures instead of relying on automatic summarisation. Unit price is a good example: summing it is meaningless, so hide the column and expose measures such as:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Average Unit Price = AVERAGE ( Sales[UnitPrice] )

Related design patterns

• Snowflake dimensions: normalised chains such as product, subcategory and category. Power BI generally works better with them flattened into one table; star schema vs snowflake schema compares the two.
• Role-playing dimensions: one date table used for order date and ship date, through inactive relationships and USERELATIONSHIP, or a separate date table per role.
• Slowly changing dimensions: Type 1 overwrites old values; Type 2 keeps versions with start and end dates, which needs a data warehouse to manage.
• Bridge tables: factless tables that relate two dimensions many-to-many; many-to-many relationships covers them, and relationships and cardinality covers the settings.

For very large facts, composite models and DirectQuery and aggregations build on the same star.

A modelling checklist

1. Every table is either a fact or a dimension, and each fact table has one consistent grain.
2. Every dimension key is unique, and every fact key finds a dimension row, including explicit unknown members.
3. One date table, with full years and no gaps, filters every fact table.
4. Relationships run one-to-many from dimensions to facts, in a single direction unless there is a clear reason.
5. Numeric columns that should not be summed are hidden behind explicit measures.

Relationships, star and snowflake schemas, and calculated columns and measures are part of the data modelling module of the Power BI course.

Sources

• Understand star schema and the importance for Power BI (Microsoft Learn) — Microsoft Learn
• Model relationships in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Design guidance for date tables in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository