Business Intelligence track

Power BI Semantic Modelling: From Warehouse Tables to Trusted Measures

Lab:SQL Server 2022 · AdventureWorksDW2022+Power BI Desktop (PBIP · TMDL · PBIR)+DAX · Power Query M+DAX Studio · Tabular Editor+git · PowerShellEvery example comes from one model you can open: the AdventureWorksPL project in bi/code, built on Microsoft's AdventureWorksDW2022 sample warehouse.

This tutorial builds a finance P&L model in Power BI from start to finish and explains every decision on the way: which tables, which grain, where each rule lives, how last year works on a 4-4-5 calendar, how amounts are shown in the order's currency, and how the model is kept in git, documented and tested. All numbers on these pages were produced by that model and reconciled with SQL.

For: BI developers, analytics and data engineers Prerequisite: SQL, basic Power BI and DAX ~3 hours

The approach in one line

Source queries: the contract→ Thin Power Query: names and types→ Star schema: many-to-one, one direction→ Layered measures: base → line → ratio→ Model as code: PBIP in git→ Generated docs: every measure traced

Row-level business rules live in SQL, where they can be read and tested. Power BI adds what only a semantic model can do: relationships, filter context and measures. Because a model with dozens of measures can't be understood by clicking through it, the model is stored as text, reviewed like code and documented by a script.

Get the lab model

  1. Warehouse. Restore AdventureWorksDW2022.bak (from Microsoft's sample-databases release) on any SQL Server 2019 or later. The lab used SQL Server 2022 in a Docker container on Linux, the same server as the Data Warehousing track. Nothing is created in the warehouse: the model only reads.
  2. Project. Copy the bi/code/AdventureWorksPL folder from this site's repository and open AdventureWorksPL.pbip in Power BI Desktop.
  3. Connect. In Transform data › Edit parameters, set ServerName and DatabaseName. Refresh, and sign in with a SQL login that can read the database (choose the Database tab in the credentials dialog).

Chapters

What you will be able to do

  • Design a star schema for Power BI on a warehouse: one fact with a stated grain, conformed dimensions and a few disconnected helper tables.
  • Build last-year and period-to-date logic on a 52/53-week fiscal calendar, without the built-in time-intelligence functions.
  • Organise measures so that a P&L reads the same in the field list, the report and the documentation.
  • Apply currency conversion and row-level security patterns once, and reuse them everywhere.
  • Keep the model in git as a PBIP project in TMDL, and review changes in pull requests.
  • Document any model: every measure, its dependencies and the warehouse columns it reads.
  • Prove the numbers by reconciling them with SQL, and measure performance instead of guessing.

How to use this tutorial

Chapters 1–3 are about the model's shape, 4–6 about measures, and 7–9 about engineering practice. If you have just inherited a model you did not build, read Chapter 8 first: documenting what exists is the fastest way to understand it.