The Power BI Fellowship

Data warehousing patterns Track

The patterns behind every good star schema, practised on Northwind data with SQL and Power BI. These are the ideas that make the difference between a model that answers today's question and one that still answers next year's: history that doesn't rewrite itself, facts at the right grain, and dates, time zones and currencies handled on purpose.

Before you start Use the company pack plus the files in data/tracks/warehousing (customer_changes, early_orders, order_events, web_sessions_local). Any SQL engine works; the model answers are portable SQL tested in CI.

Keys and slowly changing dimensions

Data: CustomerChanges EarlyOrders NWCustomers
Why it matters
Customers change segment, tier and region. Without history, last year's sales are silently re-attributed to this year's segment, and reports can't be reproduced.
Typical production failure
A customer moves from Bronze to Gold and every historical sale jumps into the Gold row: Gold looks like it grew 40% last year when nothing happened.
When to use it
SCD type 2 (new row per change, valid-from/valid-to) for attributes people analyse over time; type 1 (overwrite) for corrections; surrogate keys so versions can coexist.
When not to
Type 2 on everything. Tracking history on phone numbers or typos bloats the dimension and confuses users.

Assignments

Build an SCD type 2 customer dimension

B · Objective 50 min · uses customer_changes

The CRM sends a change feed: one row per customer per effective change. Build dim_customer with full history so sales can be analysed by the segment and tier the customer had at the time.

Requirements
  • A surrogate key, the natural key (CustomerID), the tracked attributes, ValidFrom, ValidTo and IsCurrent.
  • Remove exact duplicate feed rows.
  • Don't create a new version when the CRM sends a "change" in which nothing tracked actually changed.
  • Report the counts.
Expected result: 200 feed rows → 1 exact duplicate and 4 no-op changes removed → 195 versions for 122 customers (122 current, 73 historical); 53 customers have more than one version.

Which version was true on that day?

B · Objective 30 min · uses customer_changes, orders

Attach each order to the customer version that was valid on the order date, so sales by tier reflect the tier at the time.

Requirements
  • Point-in-time join: OrderDate >= ValidFrom AND OrderDate < ValidTo.
  • Show the versions of customer C10005.
  • Decide, per attribute, whether it should be type 1 or type 2.
Expected result: C10005 has 3 versions: from Bronze to Silver, latest change on 2026-02-24. Orders before that date keep the earlier tier.

Orders for customers who don't exist yet

C · Problem 40 min · uses early_orders, customer_changes

Four orders arrived for customers the CRM hasn't sent yet. Last night's load dropped them because the join to dim_customer failed. Fix the design so no fact row is ever lost, and say what happens when the customer arrives later.

Work out
  • Never drop a fact row because a dimension row is missing.
  • Distinguish "unknown" from "not yet arrived".
  • Show which of the four orders can be resolved once the CRM catches up.
What good looks like: 4 early orders: 3 resolve when their customer arrives (inferred members, updated later); 1 never appears in the feed and stays on the Unknown member until someone investigates.

Interview questions

  • Surrogate key vs natural key?
  • SCD type 1 vs type 2?
  • What is an unknown member and why add one?
  • Early-arriving fact vs late-arriving dimension?

Assessment

A customer changes from Bronze to Gold on 1 March. With SCD type 2, a February order is reported under:

  • Gold
  • Bronze
  • Both
  • Unknown

Why use a surrogate key instead of CustomerID in a type 2 dimension?

  • It's shorter
  • CustomerID isn't unique once a customer has several versions
  • Power BI requires integers
  • It hides personal data

In Power BI, relate FactSales to the type 2 dim_customer and build a matrix of sales by LoyaltyTier for 2025 and 2026. Then build the same with only the current version. Explain the difference to a sales manager.

Join facts on the surrogate key for history; use a separate "current customer" view (or the IsCurrent filter on a type 1 copy) for "as is today" analysis.

Fact table types: transaction, snapshot, accumulating, factless

Data: OrderEvents FactInventory NWOrders
Why it matters
The fact type decides which questions are easy and which measures are additive. Inventory, pipelines and fulfilment can't be modelled like sales.
Typical production failure
Weekly stock snapshots are summed across weeks and the warehouse appears to hold ten times its real stock.
When to use it
Transaction facts for events (sales lines); periodic snapshots for levels measured at intervals (stock, balances); accumulating snapshots for processes with milestones (order to delivery); factless facts for coverage and eligibility.
When not to
Forcing a process into a transaction fact and reconstructing milestones with complex DAX at query time.

Assignments

Build an accumulating snapshot of fulfilment

B · Objective 45 min · uses order_events

Operations want to see where March orders spend their time: placed → picked → shipped → delivered. Turn the event log into one row per order with a column per milestone and the lags between them.

Requirements
  • One row per order with PlacedAt, PickedAt, ShippedAt, DeliveredAt (NULL if not reached yet).
  • Hours from placed to shipped; whether it was delivered within 5 days of being placed.
  • Explain why this table is updated in place, unlike a transaction fact.
Expected result: 227 orders; 227 shipped and 226 delivered by the extract date. Average 65.3 hours from placed to shipped; 51% of delivered orders arrived within 5 days.

Stock is a snapshot, not a sum

B · Objective 30 min · uses FactInventory (course dataset)

FactInventory holds weekly stock snapshots. Design the month-end stock figure the finance team needs, and show what goes wrong if someone sums it.

Requirements
  • Month-end stock = the last snapshot in each month.
  • Show the naive sum for comparison.
  • Name which dimensions stock can and can't be summed over.
Expected result: Month-end stock: January 725, February 516, March 682 units. Summing all 10 snapshots gives 5,791. Stock adds up across products and warehouses but not across time.

Choose the fact type

D · Ambiguous 25 min · uses —

Elena wants designs for four new requests. For each, choose the fact table type and grain, and say which measures are additive.

Work out
  • Customer support tickets from opened to resolved, with SLA breaches.
  • Monthly account balances for wholesale customers.
  • Which products were on promotion in which stores, including those that sold nothing.
  • Every web page view.
Deliver
  • A table: request, fact type, grain, additive measures, semi/non-additive measures.
What good looks like: Tickets: accumulating snapshot (one row per ticket). Balances: periodic snapshot (customer × month; semi-additive). Promotion coverage: factless fact (product × store × promotion × day). Page views: transaction fact (one row per view).

Interview questions

  • Name the main fact table types and an example of each.
  • What is a semi-additive measure?
  • Why is an accumulating snapshot updated rather than appended?
  • What question can a factless fact answer that a sales fact can't?

Assessment

Month-end bank balances should be stored as:

  • A transaction fact
  • A periodic snapshot
  • An accumulating snapshot
  • A factless fact

In an accumulating snapshot of orders, what happens when an order ships?

  • A new row is inserted
  • The existing row is updated with ShippedAt and the lag
  • The row is deleted
  • Nothing until delivery

In Power BI, build the semi-additive measure Stock On Hand with LASTNONBLANK (see Advanced → semi-additive inventory) and check it against your SQL month-end numbers.

January 725, February 516, March 682.

Dimension patterns: conformed, role-playing, junk and bridge

Data: NWOrders NWOrderLines CustomerTargets
Why it matters
Shared, well-designed dimensions are what let Sales, Finance and Operations slice different facts by the same customer, product and date, and get comparable answers.
Typical production failure
Each fact gets its own copy of the date table and "Q1" means three slightly different things on one dashboard.
When to use it
Conformed dimensions shared across facts; role-playing for several dates; junk dimensions for scattered flags; bridges for genuine many-to-many.
When not to
Bridges and bi-directional filters to paper over a grain problem that a better fact design would remove.

Assignments

One date table, three dates

B · Objective 35 min · uses orders, order_lines

Sales wants Q1 2026 by order date, Operations by ship date, Finance by posting date, all from one model and one date table.

Requirements
  • One active relationship (order date) and two inactive ones.
  • Measures that use USERELATIONSHIP for ship and posting dates.
  • Compare Q1 2026 gross sales by order date and by ship date, and explain the difference.
Expected result: By order date $254,751; by ship date $255,185. The difference comes from orders placed in late December that shipped in January and March orders that shipped in April.

A junk dimension for order flags

C · Problem 25 min · uses orders

FactSales carries Channel, Status and Currency as text on every row: wasteful at 30 million rows and messy for the field list. Replace them with one small dimension.

Work out
  • Build the dimension from the combinations that actually occur.
  • Give it a surrogate key and point the fact to it.
  • Say why a cross join of all possible values may or may not be better.
What good looks like: 19 combinations occur out of 20 possible. The fact keeps one small integer instead of three text columns.

Targets that don't fit the customer grain

B · Objective 35 min · uses CustomerTargets, DimCustomer (course datasets)

Targets are set by Segment × LoyaltyTier × Quarter, not by customer (course dataset CustomerTargets). Model them so a matrix by Segment and Tier shows actuals and targets side by side.

Requirements
  • Either a bridge (Segment-Tier dimension) or relationships at the target grain; explain your choice.
  • Show what happens at customer level (targets can't be split).
Expected result: Targets appear at Segment × Tier and above; at customer level they are blank (or repeated, if you chose a *:* relationship, which is why you shouldn't).

Interview questions

  • What is a conformed dimension?
  • What is a role-playing dimension, and how do you model it in Power BI?
  • When is a junk dimension useful?
  • When do you need a bridge table?

Assessment

Users need a matrix by Order Month AND Ship Month at the same time. Best model?

  • One date table with USERELATIONSHIP
  • Two date tables (order date, ship date), each with an active relationship
  • A bi-directional relationship
  • A calculated column

Targets exist by Segment × Tier. A *:* relationship from targets to customers will, at customer level:

  • Split the target evenly
  • Repeat the segment's target for every customer
  • Show blank
  • Error

Draw (or describe) the bus matrix for Northwind: facts as rows (Sales, Returns, Budget, Inventory, Payroll), dimensions as columns (Date, Product, Customer, Region, Employee), with a tick where they relate.

Budget is at Category × Month, so it relates to a Category-level product dimension and to Date at month grain.

Calendars, time zones and currencies

Data: WebSessionsLocal NWOrders NWOrderLines NWFx
Why it matters
"Yesterday", "Q1" and "in dollars" each hide a decision. Get them wrong and numbers don't match Finance, regions disagree on daily totals, and currency movements look like sales growth.
Typical production failure
Europe's sales look 4% lower than the German entity reports, because they were translated at the quarter-end rate instead of each month's average.
When to use it
Store timestamps in UTC with the original offset; build one date dimension with calendar and fiscal (including 4-4-5) columns; keep amounts in transaction currency and translate with an explicit, documented rate rule.
When not to
Converting time zones or currencies in DAX at query time when it can be done once at load.

Assignments

Whose "7 April" is it?

B · Objective 30 min · uses web_sessions_local

Web sessions are logged in the visitor's local time with a UTC offset. Marketing in Berlin and the US teams get different daily totals. Normalise to UTC and show the effect.

Requirements
  • Convert StartLocal to UTC using UtcOffset.
  • Count sessions per day by local date and by UTC date.
  • Recommend which date the daily report should use, and when local time is still the right choice.
Expected result: 34 of 240 sessions fall on a different date in UTC. 7 April 2026 has 60 sessions by local date and 55 by UTC date.

Europe in euros and in dollars

B · Objective 30 min · uses orders, order_lines, fx_rates

Lea (Europe) reports Q1 2026 in euros; the group reports in dollars. Show both, and show what changes if someone translates the quarter at the March rate instead of each month's rate.

Requirements
  • Q1 2026 Europe gross in EUR (transaction currency).
  • The same in USD at each month's average rate (the agreed rule).
  • The same at the March rate, and the difference.
Expected result: €20,325.12 in EUR. At monthly average rates $22,050; at the March rate $22,004. The difference is FX, not sales.

Build the 4-4-5 fiscal calendar

C · Problem 40 min · uses orders, order_lines

From FY2026 Finance reports on a 4-4-5 calendar that starts on Sunday 28 December 2025 (see the change-request drill). Add the fiscal columns to the date dimension and reproduce Finance's period totals.

Work out
  • FiscalYear, FiscalPeriod (1–12) and FiscalQuarter columns for every date of FY2026.
  • Gross sales for fiscal periods 1, 2 and 3.
  • One relationship to the same date table: don't create a second date table.
What good looks like: P1 (28 Dec–24 Jan) $103,337, P2 (25 Jan–21 Feb) $60,004, P3 (22 Feb–28 Mar) $95,677; fiscal Q1 $259,018.

Interview questions

  • Why store timestamps in UTC?
  • What is a 4-4-5 calendar and why do retailers use it?
  • Which exchange rate should a sales report use?
  • Where should time-zone and currency conversion happen?

Assessment

A session starts at 23:30 local time in Los Angeles (UTC−07:00) on 6 April. Its UTC date is:

  • 5 April
  • 6 April
  • 7 April
  • It depends on the server

Q1 sales in EUR were flat but Q1 sales in USD grew 5%. Most likely:

  • Sales grew in Europe
  • The euro strengthened against the dollar
  • A data error
  • Returns fell

In Power BI, add a field parameter or calculation group that switches Gross Sales between transaction currency (EUR for Europe) and USD.

Keep both amounts as columns at load; the calculation group (Advanced → currency conversion) selects which one to sum.