Chapter 1 · Foundations
The Big Picture
A semantic model is the layer that turns warehouse tables into business answers. It decides which tables relate to which, what "Net Sales" means, and how every visual, Excel pivot and ad-hoc question gets the same number. This chapter shows where that layer sits, the rules we follow when building one, and the P&L that the rest of the tutorial builds.
What a semantic model is for
You could point every report directly at a SQL query. It works for one report. With twenty reports it breaks down: each report re-implements "Net Sales", last year, currency conversion and security, and they drift apart. A semantic model does those things once:
| The model owns | Example in AdventureWorksPL | Why not in each report |
|---|---|---|
| Relationships | Sales → Customer, Product, Date, Sales Territory | Slicing by any dimension just works, in every visual |
| Measures | [03 - Net Sales], [Gross Margin %] | One definition; a ratio is computed correctly at every level, not averaged |
| Time logic | Last year and period to date on a 4-4-5 calendar | Fiscal weeks are hard to get right; do it once |
| Security | A sales rep sees only their territory | Enforced for every report and every Excel user |
| Business names | "Gross Sales", not ExtendedAmount | Self-service users find the right field |
What the model should not own is heavy data preparation. Joining, cleansing and business rules that work row by row belong in SQL, where they can be read, tested and reused by other tools. That split is the heart of the approach.
The approach in seven rules
- The source query is the contract. Each model table reads one SQL query: a reporting view if you own the warehouse, or a query kept in Power Query if you don't (as in this lab). Row-level rules live in that SQL; Power Query only connects, types and splits partitions. Chapter 3
- Star schema, always. Every fact has one stated grain. Dimensions relate many-to-one in a single direction. No calculated columns: if a column is needed, the source query provides it. Chapter 2
- The calendar is fiscal and defined once. Fiscal week and period IDs are columns of the Date table, built by one calendar query, and time intelligence uses those IDs. Chapter 4
- Measures live in one place, in layers. One measure table; hidden base measures; numbered P&L line measures; ratios built only from other measures. Chapter 5
- Special logic is a pattern, written once. Currency conversion and time comparisons sit in the lowest layer so every line inherits them. Chapter 6
- The model is code. Saved as a PBIP project, committed to git, changed through pull requests and deployed from the main branch. Chapter 7
- Every measure is documented and traceable, by a script. Hand-written documentation is out of date within a month; generated documentation is never more than one run behind. Chapters 8–9
Meet Adventure Works
All examples use Adventure Works Cycles, the fictional bicycle company in Microsoft's AdventureWorksDW2022 sample warehouse. It sells bikes, components, clothing and accessories through two channels: Internet orders from individual customers and Reseller orders from bike shops and warehouses, in ten sales territories across North America, Europe and the Pacific. Orders are placed in six currencies, but every amount in the warehouse is stored in US dollars.
The warehouse has what a real P&L model needs, which is why it was chosen:
| Warehouse table | Rows | Used for |
|---|---|---|
FactInternetSales + FactResellerSales | 60,398 + 60,855 | The sales fact: P&L lines 01–05 |
FactCurrencyRate | 14,264 | Daily rates for showing amounts in the order's currency |
DimDate, DimCustomer, DimReseller, DimProduct, DimSalesTerritory, DimEmployee, DimCurrency | Dimensions |
Finance closes on a 4-4-5 calendar: the fiscal year ends on the last Saturday of June, so most years have 52 weeks and FY2012 has 53. AdventureWorksDW2022 doesn't have that calendar; we derive it from DimDate in Chapter 4.
The model answers one question at every level of detail: how much did we sell on this territory, customer or product, and how much profit did it leave after the cost of the products? The answer is a P&L, and the measures are numbered to match it. The FY2013 column is what the model returns:
| Line | Measure | How it is calculated | FY2013 |
|---|---|---|---|
| 01 | Gross Sales | Order value before discounts | 40,960,226 |
| 02 | Discounts | Volume and promotional discounts | 296,483 |
| 03 | Net Sales | 01 − 02 | 40,663,743 |
| 04 | Product Cost | Standard cost of the products sold | 36,814,706 |
| 05 | Gross Profit | 03 − 04 | 3,849,037 |
Every line comes straight from order lines, and the lines can also be shown as last year and period to date, with variances. Ratios such as [Gross Margin %] sit on top of the lines. Showing the same lines in the order's currency, on a fiscal calendar, under row-level security, is what makes a P&L model interesting, and it is why the patterns in Chapter 6 exist.
Sample data has sample economics
In FY2013 Adventure Works' internet sales earn a 41.6% gross margin, while reseller sales lose money (−0.7%). Together that gives a 9.5% gross margin. That is how Microsoft generated the data, not a modelling error, and it makes a useful test: a model that "fixed" it would be wrong.
Two ways into the same model
Building a new model
Read the chapters in order. Agree on the source queries first (Chapter 3), then the grain and dimensions (Chapter 2), then the measures. Put the PBIP project in git on day one, not after the first release.
Taking over an existing model
Start with Chapter 8. Load the model definition, generate a page per measure and read the catalogue. You will quickly see which measures are used, which are duplicates and which read columns nobody can explain. Then use Chapters 2–6 to decide what to fix.
Exercise: where does this logic belong?
For each item, decide whether it belongs in the SQL source query, in Power Query or in a DAX measure: (a) giving internet customers and resellers one customer key; (b) Gross Margin %; (c) renaming ExtendedAmount to GrossSales; (d) last year's Net Sales; (e) giving every order line the fiscal week it falls in.
(a) SQL: it is a row-level rule other tools need too. (b) Measure: a ratio must be computed from totals at the level being viewed, never stored per row. (c) SQL, with an alias; renaming in Power Query works but hides the mapping. (d) Measure: it depends on what the user has selected. (e) SQL: it is a rule about each row, and Chapter 4 builds it in the calendar query.
Key takeaways
- The semantic model owns relationships, measures, time logic, security and business names.
- Row-level business rules belong in SQL, not in Power Query or DAX.
- Number the measures to match the P&L, so the field list reads like the finance report.
- Treat the model as code and generate its documentation.