Chapter 7 · Engineering

The Model as Code

A .pbix file is a zip of binary parts: you can't diff it, review it or merge it. A Power BI Project (.pbip) saves the same model and report as text files. Once they are text, they can live in git, every change can be reviewed in a pull request, and scripts can read and even write them. AdventureWorksPL was written as text from the start and opened in Desktop only to check it.

From PBIX to PBIP

In Power BI Desktop, choose File › Save as and pick Power BI project (*.pbip). Desktop writes a folder instead of one file. This is the AdventureWorksPL project in the site's repository:

AdventureWorksPL/
├── AdventureWorksPL.pbip                  pointer file: open this in Desktop
├── .gitignore                             excludes the data cache and local settings
├── AdventureWorksPL.SemanticModel/
│   ├── definition.pbism                   model settings (version 4.2 = TMDL folder)
│   └── definition/
│       ├── database.tmdl                  compatibility level
│       ├── model.tmdl                     model options, table order
│       ├── expressions.tmdl               parameters + shared queries (SalesSource, FiscalCalendarSql)
│       ├── relationships.tmdl             14 relationships
│       ├── roles/Territory Access.tmdl
│       └── tables/                        one file per table: Sales.tmdl, Date.tmdl, _Measures.tmdl, …
└── AdventureWorksPL.Report/
    ├── definition.pbir                    which model the report uses (byPath)
    ├── StaticResources/…                  the base theme
    └── definition/                        PBIR: one JSON file per page and per visual
        ├── report.json
        └── pages/plOverview/
            ├── page.json
            └── visuals/plMatrix/visual.json, trendChart/visual.json, …

The data cache is never committed: the repository holds the definition, and data comes from refreshing it. When Desktop saves a project it creates .pbi/cache.abf and .pbi/localSettings.json next to the definition, and the .gitignore excludes both.

What is inside the definition

The model is stored in TMDL (Tabular Model Definition Language): indented text, one file per table. Everything from Chapters 2–6 is in it. A measure, with its description, format and folder:

table _Measures

	/// Gross sales less discounts, in USD unless one other currency is selected.
	measure '03 - Net Sales' = [01 - Gross Sales] - [02 - Discounts]
		formatString: #,0
		displayFolder: P&L Actual

	measure 'Gross Margin %' = DIVIDE ( [05 - Gross Profit], [03 - Net Sales] )
		formatString: 0.0%
		displayFolder: Ratios

A table, with hidden fact columns and one of its partitions:

/// Grain: one order line, internet or reseller. Amounts are in USD; LocalCurrencyRate converts them to the order's currency.
table Sales

	column GrossSales
		dataType: decimal
		isHidden
		formatString: #,0.00
		summarizeBy: sum
		sourceColumn: GrossSales

	partition 'Sales FY2013' = m
		mode: import
		source =
				let
				    Source = SalesSource,
				    FY2013 = Table.SelectRows(Source, each [DateKey] >= 20120701 and [DateKey] < 20130630)
				in
				    FY2013

Notice what is not there. A relationship with no crossFilteringBehavior or cardinality properties is many-to-one and single-direction, because those are the defaults. A bi-directional relationship shows up as a line crossFilteringBehavior: bothDirections, which is easy to catch in a review.

The report is stored in PBIR: one JSON file per page and per visual, each with a published schema. A card is a few lines:

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/2.13.0/schema.json",
  "name": "cardNetSales",
  "position": { "x": 20, "y": 86, "width": 296, "height": 96 },
  "visual": {
    "visualType": "card",
    "query": { "queryState": { "Values": { "projections": [ {
      "field": { "Measure": { "Expression": { "SourceRef": { "Entity": "_Measures" } }, "Property": "03 - Net Sales" } }
    } ] } } }
  }
}

The workflow

1. Branch. One branch per change: feature/freight-income-line.
2. Edit. In Power BI Desktop for visuals and simple measures; in Tabular Editor or a text editor for bulk changes, calculation groups, partitions and roles. They all edit the same files. If the files change while Desktop has the project open, Desktop shows "This project's files were changed externally" with an Apply external changes button: apply them, and don't save Desktop's copy over them.
3. Validate. Before anything else, load the definition with the Tabular Object Model. A syntax error in a TMDL file fails here, with the file and line, instead of in someone's Desktop.
4. Commit and open a pull request. The diff shows exactly which measures, columns, relationships, queries and visuals changed.
5. Automated checks. The pipeline runs Best Practice Analyzer rules and regenerates the measure documentation (Chapter 8), so the reviewer sees the new lineage too.
6. Review and merge. A second person approves.
7. Deploy from main. A deployment pipeline (dev → test → prod workspaces, with parameter rules for ServerName) or a script against the XMLA endpoint. Then refresh only the partitions that changed.

Add-Type -Path 'C:\Program Files\Microsoft Power BI Desktop\bin\Microsoft.PowerBI.Tabular.dll'
$db = [Microsoft.AnalysisServices.Tabular.TmdlSerializer]::DeserializeDatabaseFromFolder(
          '.\AdventureWorksPL\AdventureWorksPL.SemanticModel\definition')
"{0} tables, {1} relationships, {2} roles" -f $db.Model.Tables.Count, $db.Model.Relationships.Count, $db.Model.Roles.Count
# 15 tables, 14 relationships, 1 roles

That check catches TMDL syntax, not DAX mistakes. To check DAX, open the project in Desktop (or deploy it to a workspace) and read each measure's state from the engine; Chapter 9 shows the query.

What to look for in a review

Best Practice Analyzer

Tabular Editor's Best Practice Analyzer (BPA) checks a model against a list of rules, and its command-line version can fail a build. Start with Microsoft's published rule set, then add your own. Rules worth having for a P&L model: