Power BI star schema and your first report

#Why it matters

A Power BI star schema is the difference between a report that is right, fast and easy to extend and one that breaks the first time someone asks a new question. Power BI was named in 80% of the Data Analyst job descriptions we analysed; data modelling was named in 26% of them in its own right, and Microsoft's PL-300 exam gives "Model the data" 25 to 30% of the marks. "Why a star schema?" and "calculated column or measure?" are the two Power BI interview questions you are most likely to hear.

#The concept

The theory of dimensional modelling (grain, conformed dimensions, slowly changing dimensions) lives in Data modelling. Here you apply it in Power BI.

#Facts and dimensions

• A fact table records events at a fixed grain (one row per invoice line). It holds keys and numbers you add up: quantity, price, revenue. It is long and narrow.

• A dimension table describes the things involved in the events: who (Customer), what (Product), when (Date). One row per key, many descriptive columns. It is short and wide.

• One-to-many relationships run from each dimension's key (the one side, values unique) to the fact table's matching column (the many side).

Draw it and you get a star: the fact in the middle, dimensions around it.

#Why Power BI wants a star

The engine inside Power BI (VertiPaq) and the DAX language were designed around this shape. Most "DAX is hard" problems in real projects turn out to be model problems.

#Build dimensions by referencing one cleaned query

In Power Query, Reference creates a new query that starts from the output of another, so cleaning written once feeds every table. Duplicate copies all the steps instead, so a fix in one copy does not reach the others. For a fact and its dimensions, clean once in a staging query and reference it.

#Dimension keys must be unique, case-insensitively

A one-to-many relationship needs unique values on the one side. Two traps make "Remove duplicates" in Power Query not enough:

1. The attribute differs between rows. In this dataset 8 customers appear under two countries and 213 stock codes have more than one description. Remove duplicates on the key keeps whichever row comes first, which is not guaranteed to be the same on every refresh. Pick a rule (the latest value is a common one) and implement it with Group By.

2. Power Query is case-sensitive, the model is not. The raw file contains both 85123A and 85123a. Power Query sees two keys; VertiPaq compares text without case and sees a duplicate, and Power BI refuses to create the one-to-many relationship. Upper-case text keys before grouping.

#A date table, marked as a date table

Time-based analysis needs a table with one row per day, no gaps, covering whole years. You create it in DAX with Modeling, New table, then Table tools, Mark as date table, choosing its date column. Marking tells Power BI that this is the calendar, which time intelligence relies on and which disables the hidden auto date/time tables for that column. The next lesson but one goes deeper (fiscal years, ISO weeks, holidays).

#The relationship key must be a date, not a datetime

InvoiceDate holds a time: 2010-12-01 08:26. The Date table holds midnight dates: 2010-12-01 00:00. A relationship between them matches almost nothing, and every visual by month shows blank. Create a date-only column in the fact table, ideally in Power Query (DateTime.Date), and relate that.

#Sort by column

A text column such as Month ("Jan 2011") sorts alphabetically: Apr, Aug, Dec. Select the column in Table view and use Column tools, Sort by column with a numeric column such as Year Month (201101). Do the same for weekday names.

#Calculated column or measure

Rule of thumb: if it goes on an axis or a slicer, it is a column (and if possible, build it in Power Query instead of DAX). If it is a number that must respond to slicers, it is a measure.

#Worked example: Online Retail as a star

This uses the same Online Retail CSV as Power BI Desktop tutorial for beginners (UCI, CC BY 4.0), loaded from its GitHub URL. Start a new file.

#Step 1: one staging query

Get data, Web, paste the URL, Transform data, name the query Retail Lines and replace its code with:

let
    Source = Csv.Document(
        Web.Contents("https://raw.githubusercontent.com/databricks/Spark-The-Definitive-Guide/master/data/retail-data/all/online-retail-dataset.csv"),
        [Delimiter = ",", Columns = 8, Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(
        Promoted,
        {
            {"InvoiceNo", type text}, {"StockCode", type text}, {"Description", type text},
            {"Quantity", Int64.Type}, {"InvoiceDate", type datetime}, {"UnitPrice", type number},
            {"CustomerID", type text}, {"Country", type text}
        },
        "en-US"
    ),
    WithCustomer = Table.SelectRows(Typed, each [CustomerID] <> null and [CustomerID] <> ""),
    UpperCodes = Table.TransformColumns(WithCustomer, {{"StockCode", Text.Upper, type text}})
in
    UpperCodes

"en-US" makes 12/1/2010 8:26 mean 1 December whatever your laptop's locale. Lines without a customer are dropped here because every table in this model is about customers; the tutorial showed there are 135,080 of them. Untick Enable load on this query.

#Step 2: the fact table

Right-click Retail Lines, Reference, name the new query Sales:

let
    Source = #"Retail Lines",
    NotCancelled = Table.SelectRows(Source, each not Text.StartsWith([InvoiceNo], "C")),
    WithDay = Table.AddColumn(NotCancelled, "InvoiceDay", each DateTime.Date([InvoiceDate]), type date),
    WithRevenue = Table.AddColumn(WithDay, "Revenue", each [Quantity] * [UnitPrice], type number),
    Kept = Table.SelectColumns(
        WithRevenue,
        {"InvoiceNo", "StockCode", "CustomerID", "InvoiceDay", "Quantity", "UnitPrice", "Revenue"}
    )
in
    Kept

Description and Country are gone from the fact: they belong to the dimensions. The time of day is gone too; nobody in this business analyses by minute, and dropping it shrinks the model.

#Step 3: Customer and Product dimensions

Reference Retail Lines twice more:

let
    Source = #"Retail Lines",
    // One row per customer; the country on their most recent line wins
    Grouped = Table.Group(
        Source,
        {"CustomerID"},
        {{"Country", each Table.Max(_, "InvoiceDate")[Country], type text}}
    )
in
    Grouped

let
    Source = #"Retail Lines",
    // One row per stock code; the most recent description wins
    Grouped = Table.Group(
        Source,
        {"StockCode"},
        {{"Description", each Table.Max(_, "InvoiceDate")[Description], type text}}
    )
in
    Grouped

Table.Max(_, "InvoiceDate") returns the latest row of each group, so the rule is the same on every refresh. Both dimensions come from all lines with a customer, including cancellations, so a customer who only ever returned goods still exists in Customer. Close & Apply.

#Step 4: the Date table

Modeling, New table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2010, 1, 1 ), DATE ( 2011, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm yyyy" ),
    "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "ddd" ),
    "Weekday Number", WEEKDAY ( [Date], 2 )
)

Then:

1. Table tools, Mark as date table, date column Date.

2. Select Month, Column tools, Sort by column, Year Month. Select Weekday, sort by Weekday Number (WEEKDAY(..., 2) numbers Monday as 1).

FORMAT returns month and day names in the model's language, so a model created on a Hindi-locale machine shows Hindi month names. That is usually what you want, but check before you build a slicer on text values.

#Step 5: relationships

In Model view drag:

• Customer[CustomerID] to Sales[CustomerID]

• Product[StockCode] to Sales[StockCode]

• Date[Date] to Sales[InvoiceDay]

Each should show cardinality one to many (1 on the dimension, * on Sales) and cross filter direction Single, with the arrow pointing at Sales. Then hide the key columns on the fact table (right-click, Hide in report view) so report builders use the dimension columns instead.

#Step 6: measures and a calculated column

Home, New measure, one at a time:

Revenue = SUM ( Sales[Revenue] )

Orders = DISTINCTCOUNT ( Sales[InvoiceNo] )

Customers = DISTINCTCOUNT ( Sales[CustomerID] )

Revenue per Customer = DIVIDE ( [Revenue], [Customers] )

And one calculated column on Product (Table tools, New column), because it is meant for a slicer:

Is Non-Product =
Product[StockCode] IN { "POST", "DOT", "M", "C2", "BANK CHARGES", "PADS", "CRUK", "D" }

These codes are postage, carriage, manual adjustments, bank charges and similar lines that are not goods. A slicer on this column lets a buyer look at merchandise only.

#Step 7: one page

A card each for Revenue, Orders, Customers and Revenue per Customer; a column chart of Revenue by Date[Month]; a bar chart of Revenue by Customer[Country] with a Top N filter of 10 on the visual; and a slicer on Customer[Country].

#Step 8: prove the numbers

Before anyone sees the page, reconcile it. This script builds the same four tables with the same rules in DuckDB and computes every number on the page:

import duckdb

con = duckdb.connect()
con.sql("""
    create table retail_lines as
    select InvoiceNo, upper(StockCode) as StockCode, Description, Quantity,
           InvoiceDate, UnitPrice, CustomerID, Country
    from read_csv('online-retail.csv',
                  types = {'InvoiceNo': 'VARCHAR', 'StockCode': 'VARCHAR', 'CustomerID': 'VARCHAR'},
                  timestampformat = '%m/%d/%Y %H:%M')
    where CustomerID is not null
""")
# Fact: invoice lines that are not cancellations
con.sql("""
    create table Sales as
    select InvoiceNo, StockCode, CustomerID, cast(InvoiceDate as date) as InvoiceDay,
           Quantity, UnitPrice, Quantity * UnitPrice as Revenue
    from retail_lines where not starts_with(InvoiceNo, 'C')
""")
# Dimensions: one row per key, attribute taken from the latest line
con.sql("create table Customer as select CustomerID, arg_max(Country, InvoiceDate) as Country from retail_lines group by CustomerID")
con.sql("create table Product as select StockCode, arg_max(Description, InvoiceDate) as Description from retail_lines group by StockCode")

for t in ["Sales", "Customer", "Product"]:
    print(t, "rows:", con.sql(f"select count(*) from {t}").fetchone()[0])
print("duplicate keys:",
      con.sql("select count(*) - count(distinct CustomerID) from Customer").fetchone()[0],
      con.sql("select count(*) - count(distinct StockCode) from Product").fetchone()[0])

print(con.sql("""
    select round(sum(Revenue), 2) as Revenue,
           count(distinct InvoiceNo) as Orders,
           count(distinct CustomerID) as Customers,
           round(sum(Revenue) / count(distinct CustomerID), 2) as "Revenue per Customer"
    from Sales
"""))
print(con.sql("""
    select c.Country, round(sum(s.Revenue), 2) as Revenue
    from Sales s join Customer c using (CustomerID)
    group by c.Country order by Revenue desc limit 5
"""))
print(con.sql("""
    select strftime(InvoiceDay, '%b %Y') as Month, round(sum(Revenue), 2) as Revenue
    from Sales group by Month, date_trunc('month', InvoiceDay)
    order by date_trunc('month', InvoiceDay)
"""))
print(con.sql("""
    select p.Description, round(sum(s.Revenue), 2) as Revenue
    from Sales s join Product p using (StockCode)
    group by p.Description order by Revenue desc limit 5
"""))

Sales rows: 397924
Customer rows: 4372
Product rows: 3684
duplicate keys: 0 0
┌───────────┬────────┬───────────┬──────────────────────┐
│  Revenue  │ Orders │ Customers │ Revenue per Customer │
│  double   │ int64  │   int64   │        double        │
├───────────┼────────┼───────────┼──────────────────────┤
│ 8911407.9 │  18536 │      4339 │              2053.79 │
└───────────┴────────┴───────────┴──────────────────────┘
 
┌────────────────┬────────────┐
│    Country     │  Revenue   │
│    varchar     │   double   │
├────────────────┼────────────┤
│ United Kingdom │ 7308391.55 │
│ Netherlands    │  285446.34 │
│ EIRE           │   265545.9 │
│ Germany        │  228867.14 │
│ France         │  209024.05 │
└────────────────┴────────────┘
 
┌──────────┬────────────┐
│  Month   │  Revenue   │
│ varchar  │   double   │
├──────────┼────────────┤
│ Dec 2010 │  572713.89 │
│ Jan 2011 │  569445.04 │
│ Feb 2011 │  447137.35 │
│ Mar 2011 │  595500.76 │
│ Apr 2011 │  469200.36 │
│ May 2011 │  678594.56 │
│ Jun 2011 │  661213.69 │
│ Jul 2011 │  600091.01 │
│ Aug 2011 │   645343.9 │
│ Sep 2011 │  952838.38 │
│ Oct 2011 │ 1039318.79 │
│ Nov 2011 │ 1161817.38 │
│ Dec 2011 │  518192.79 │
└──────────┴────────────┘
 
┌────────────────────────────────────┬───────────┐
│            Description             │  Revenue  │
│              varchar               │  double   │
├────────────────────────────────────┼───────────┤
│ PAPER CRAFT , LITTLE BIRDIE        │  168469.6 │
│ REGENCY CAKESTAND 3 TIER           │ 142592.95 │
│ CREAM HANGING HEART T-LIGHT HOLDER │  100603.5 │
│ JUMBO BAG RED RETROSPOT            │  85220.78 │
│ MEDIUM CERAMIC TOP STORAGE JAR     │  81416.73 │
└────────────────────────────────────┴───────────┘

Your cards should read 8.91M revenue, 18,536 orders, 4,339 customers and 2,053.79 revenue per customer. Notice:

• Customer has 4,372 rows but Sales has 4,339 customers. The other 33 only appear on cancelled invoices. That is the star schema working: the dimension knows customers the fact table does not.

• Revenue moved from 9.75M to 8.91M. Cancellations and lines without a customer are out.

• The top product is suspicious. PAPER CRAFT, LITTLE BIRDIE earns 168,469.60 from a single line of 80,995 units, which the same customer cancelled minutes later (the cancellation sits in the lines you filtered out). The fifth row has the same story. This is why a later lesson brings returns in as a second fact table.

#Common mistakes

• Relating on a datetime column. Months show blank or only midnight transactions match. Relate on a date-only column.

• Remove duplicates on a dimension with conflicting attributes. The kept row can change between refreshes, so a customer's country flips. Group by the key with an explicit rule.

• Forgetting case. 85123A and 85123a are two keys to Power Query and one to the model. Upper-case keys in the staging query.

• Duplicate instead of Reference. Five copies of the cleaning steps drift apart within a month. Reference one staging query.

• Keeping descriptive columns on the fact. Country on 397,924 rows wastes memory and tempts people to filter the fact directly. Keep descriptions in dimensions and hide fact keys.

• A Date table that does not cover whole years. Time intelligence functions expect full years. Start on 1 January (or the first day of your fiscal year) and end on 31 December.

• Shipping without reconciling. One total, one breakdown and one top-N list checked against an independent query catch most model errors.

#Interview angle

#Practice task

1. Build Retail Lines, Sales, Customer, Product and Date exactly as above, with load disabled on Retail Lines.

2. Create the three relationships, mark the date table and set both sort-by columns.

3. Add the four measures and the Is Non-Product column, and build the page.

4. Run check_star.py (or your own SQL) and compare every card and chart.

5. Add a table of the top 10 products by Revenue, then add the Is Non-Product slicer and record how revenue changes when you exclude non-product lines.

Done when: all cards and the monthly chart match the script output, Model view shows three one-to-many single-direction relationships into Sales, and you can explain the PAPER CRAFT row.

#Further reading

• Microsoft Learn: understand star schema and the importance for Power BI: Microsoft's own guidance, including role-playing and junk dimensions.

• Microsoft Learn: create date tables in Power BI Desktop: the options and their trade-offs.

• Microsoft Learn: model relationships in Power BI Desktop: cardinality, direction and what the engine does with them.

• UCI Online Retail dataset: column definitions and licence.