Validate every model change: deploy it right (part 2)

Key Takeaways

• A clean merge is not necessarily a valid model: Git merges text line by line but doesn't validate semantic models, so two correct changes can combine into a broken model without any conflict.
• Check the model, and the report: The TE CLI's te validate checks if the model is structurally valid, Best Practice Analyzer checks if the design is sound, and pbir validate checks that the downstream Power BI report can still find every field it needs.
• Exit codes connect your checks to a pipeline: Checks return exit code 0 when they pass and a non-zero exit code when they fail. Pipelines stop the run at any non-zero exit code.
• You don't have to remember the checks if a pipeline runs them for you: A pipeline runs it all on every push, on a fresh machine, and marks the commit that broke something, so a broken model is caught within minutes instead of when a user opens a report.

This summary is produced by the author, and not by AI.

Source-controlling and validating models

This is part 2 of our blog series on how to deploy your semantic models right. In part 1 we showed how source control for your models unlocks a paper trail of all changes made to it over time. We ended on a warning: a clean Git merge (where you integrate multiple actors' work on the same file) can still break the model, for example because one commit renames a measure and another commit references that measure by its old name. You need a tool that validates the semantic model to catch problems like these: Tabular Editor 3 or the TE CLI. TE3's validation is always there; squiggly-lined expressions that will (errors) or may (warnings) cause problems, and print them as messages.

For this series, we will focus on the Tabular Editor CLI: Tabular Editor as a command you type in a terminal. The TE CLI reads the model folder directly, so it needs no Power BI Desktop, nor a connection to a workspace. It checks the model and reports each problem as a line of text, plus an exit code that tells a script whether the check passed. You could deploy with TE3 too, but the TE CLI also runs on the machines that your Git provider (such as GitHub or Azure DevOps) starts for you, so every change is checked and deployed in the same way, from a commit, with a log of what ran.

TE3 is where you and your AI make the changes; the TE CLI is what checks and deploys the changes the same way every time, on any machine. This series is designed to let you follow along, as much as we can enable it. You will need these tools:

• Git (as in part 1)
• The TE CLI, installed on your PATH; for the pipeline, also its Linux archive (te-<version>-linux-x64.tar.gz), committed to tools/te/ in your repository
• Python 3.10 or later and pbir-cli: pip install pbir-cli (Windows x64, or a Mac with Apple silicon).
• An Azure DevOps or GitHub repository (as in part 1).
• No Fabric workspace and no credentials: nothing is deployed in this part.

A clean merge can break the model

The SpaceParts company -- Tabular Editor's fictional daughter company, where Eugene and Ruben are employed for this blog series -- updated its naming conventions, and Ruben needs to make semantic model measure names compliant. He renames the measure # Units Sold to Units Sold and pushes this change to the main branch of the repository. A branch is a named series of commits, and for now everyone on the team commits to the same branch, called main. We'll discuss branches in detail in part 4 of this blog series.

Unbeknownst to Ruben, his colleague Eugene has been working on the same model locally, and wants to push a new measure Revenue per Unit = DIVIDE ( [Revenue], [# Units Sold] ) to the main branch. Eugene's push is rejected, because Ruben's push came into the repo after Eugene's latest pull.

To <remote>
 ! [rejected]        main -> main (fetch first)
error: failed to push some refs to '<remote>'
hint: Updates were rejected because the remote contains work that you do not
hint: have locally. This is usually caused by another repository pushing to
hint: the same ref. If you want to integrate the remote changes, use
hint: 'git pull' before pushing again.
hint: See the 'Note about fast-forwards' in 'git push --help' for details.

Before Eugene can push, he must pull down Ruben's changes and merge his. Git merges without a conflict because the two edits changed different lines; it does not read or validate DAX, so it cannot see that a line added by Eugene now refers to a name Ruben removed.

PS C:\git\spaceparts-analytics> git pull
From <remote>
   f082d4c..7cb088c  main       -> origin/main
Auto-merging Invoiced Sales.SemanticModel/definition/tables/Invoiced Sales.tmdl
Merge made by the 'ort' strategy.
 .../definition/tables/Invoiced Sales.tmdl                      | 10 +++++-----
 .../definition/tables/MetricSelector.tmdl                      |  2 +-
 .../definition/tables/ScatterChartXMetricSelector.tmdl         |  2 +-
 .../definition/tables/ScatterChartYMetricSelector.tmdl         |  2 +-
 4 files changed, 8 insertions(+), 8 deletions(-)

Eugene's local merged model now has a measure that cannot be evaluated because it refers to a measure that no longer exists by that name. Nothing tells Eugene he accidentally broke the model until someone opens the model or a visual that uses the measure. He retries the push and this time it's accepted. The main branch of the repository now holds a broken model. If this gets deployed, every query that uses Revenue per Unit fails, and any visual that shows it turns into a grey error box.

Let's rewind to when Eugene pulled Ruben's changes and merged his, locally, before the retried push.

Local validation

Git won't validate a semantic model, but the TE CLI will: a command-line tool that reads the model folder and checks, changes, deploys, and tests the model. We already mentioned te save in part 1 of this blog series. Now it's te validate's turn. Eugene runs the check from the repository folder:

PS C:\git\spaceparts-analytics> te validate --model "Invoiced Sales.SemanticModel" --errors-only
Validating...
Validating: Invoiced Sales

Errors

  Code    │ Message                                  │ Object           │ Line
 ─────────┼──────────────────────────────────────────┼──────────────────┼──────
  DAX0002 │ The value for [# Units Sold] cannot be   │ Revenue per Unit │ 1
          │ determined. Either [# Units Sold]        │                  │
          │ doesn't exist, or there is no current    │                  │
          │ row for a column named [# Units Sold].   │                  │

  Errors:        1
PS C:\git\spaceparts-analytics> $LASTEXITCODE
1

The table output of te validate tells Eugene something is wrong; he reads the text and can start debugging from there; where did that measure go? The last PowerShell command, $LASTEXITCODE, is to illustrate the concept of exit codes: every command-line program returns a number when it finishes, 0 for success and any other number (a non-zero exit code) for failure. Pipelines decide from exit codes whether to carry on or stop.

The --errors-only option of the validate command specifies the validation to only report on errors and ignore things such as DAX style hints. The simplest fix in this case would be to update the reference in the [Revenue per Unit] measure to use [Units Sold]. Running the command again then yields:

PS C:\git\spaceparts-analytics> te validate --model "Invoiced Sales.SemanticModel" --errors-only
Validating...
Validating: Invoiced Sales

No validation errors found.
PS C:\git\spaceparts-analytics> $LASTEXITCODE
0

No errors, and exit code 0. Eugene commits the fix. The model is now functional again.

Model best practices

The validate command checks whether the model is structurally valid; can every DAX expression be evaluated, and would Analysis Services accept the model's structure, for example no two columns or measures with the same name in one table? A model that passes te validate has no broken expressions, but is not necessarily well-designed. To test a model's compliance to good practices in design, you can run Best Practice Analyzer (BPA). The built-in rule set has 32 rules and comes with the TE CLI (and Tabular Editor 3). You can also define custom rules yourself, such as compliance to your organization's naming convention. Custom rules can live in the model itself and be versioned with it, or in a separate rules file you call with te bpa run --rules.

Eugene runs the built-in rules against the model with te bpa run, and it flags some things that flew under the radar until now:

PS C:\git\spaceparts-analytics> te bpa run --model "Invoiced Sales.SemanticModel"
Analyzing: Invoiced Sales
Rules loaded: 32 from built-in defaults + model annotations

Analyzing 32 rule(s)...

  Severity │ Rule ID                  │ Rule                    │ Object                  │ Type
 ──────────┼──────────────────────────┼─────────────────────────┼─────────────────────────┼────────
  Error    │ TE3_BUILT_IN_DO_NOT_SUMM │ Do not summarize        │ 'Invoiced               │ Column
           │ ARIZE_NUMERIC            │ numeric columns         │ Sales'[Product Key]     │
  Warning  │ TE3_BUILT_IN_HIDE_FOREIG │ Hide foreign keys       │ 'Invoiced               │ Column
           │ N_KEYS                   │                         │ Sales'[Product Key]     │
  Warning  │ TE3_BUILT_IN_FORMAT_STRI │ Provide format string   │ 'Invoiced               │ Column
           │ NG_COLUMNS               │ for visible numeric     │ Sales'[Product Key]     │
           │                          │ columns                 │                         │
  Warning  │ TE3_BUILT_IN_VISIBLE_COL │ Visible columns with no │ 'Invoiced               │ Column
           │ UMNS_NO_DESCRIPTION      │ description             │ Sales'[Product Key]     │

  Rules evaluated: 32
  Violations:      4

Failed: 1 violation(s) at error level or above

The Product Key column of the Invoiced Sales table displays anti-patterns: it's a visible and summarizable foreign key. Users are able to use the key in a visual and summarize into meaningless numbers. A semantic model should be intuitive for its users. One that can produce meaningless numbers often isn't; it can lead to confusion and misreadings.

A BPA run will inform you of model design problems such as these; deciding how to tackle design problems like these is up to you. The fix in this case is simple: hide the key and set its SummarizeBy property to None.

PS C:\git\spaceparts-analytics> te set "Invoiced Sales/Product Key" -p IsHidden=true -p SummarizeBy=None --model "Invoiced Sales.SemanticModel" --save
Invoiced Sales/Product Key (Column)
  - IsHidden: False
  + IsHidden: True
  - SummarizeBy: Sum
  + SummarizeBy: None

Saved: Invoiced Sales.SemanticModel

Running BPA again yields a clean bill of health on its built-in rules:

PS C:\git\spaceparts-analytics> te bpa run --model "Invoiced Sales.SemanticModel"
Analyzing: Invoiced Sales
Rules loaded: 32 from built-in defaults + model annotations

Analyzing 32 rule(s)...

  Rules evaluated: 32
  Violations:      0

Report validation

The model is now fixed, but renames in models break everything that comes asking for things by name, like Power BI reports. Ruben's rename changed only the files in the .SemanticModel folder, nothing in the .Report folder, or any other asset built to use this model (apps, queries, etc). To validate Power BI reports, we can use the validate command of pbir-cli, a command-line tool by Kurt Buhler and Maxim Anatsko for reports saved in the PBIR format.

pbir validate --fields reads the model folder the report points to, so it needs no connection nor a sign-in:

PS C:\git\spaceparts-analytics> pbir validate "Invoiced Sales.Report" --fields --strict
Validating Invoiced Sales

✗ Invoiced Sales validation failed
…
                                             Errors (8)
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Location                               ┃ Code            ┃ Message                               ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ Profitability (a0fd188a04e9be4db302) > │ FIELD_NOT_FOUND │ Field '# Units Sold' not found in     │
│ Customer Explorer (scatterChart,       │                 │ table 'Invoiced Sales'                │
│ 037cb689ff7a95fa2301) > visual.json    │                 │                                       │
…
└────────────────────────────────────────┴─────────────────┴───────────────────────────────────────┘

Invalid (8 errors, 0 warnings)

Eight errors across three visuals that still use # Units Sold. Without --strict, pbir-cli reports a missing field only as a warning and still returns exit code 0, so the pipeline would pass; --strict turns those warnings into errors, as in the output above. pbir fields replace swaps the old name for the new name throughout the report:

PS C:\git\spaceparts-analytics> pbir fields replace "Invoiced Sales.Report" --from "Invoiced Sales.# Units Sold" --to "Invoiced Sales.Units Sold"
…
✓ Replaced 3 location(s)

pbir validate passes now (exit code 0). Eugene commits the fix and pushes. Note that pbir says nothing about the correctness of numbers; that will be the role of te test, which we will discuss in part 5 of this blog series.

Pipeline checks

Eugene has now validated that the model is structurally sound, complies to the built-in BPA rules, and that the report can find every field it needs. He did it himself, but we already hinted at the pipelines that could do it for him (and any other developer on the team) automatically, on every push. Pipelines are an investment in quality; you have to set them up and probably iterate a bit to fine-tune them. Once they're set up, pipelines are incredibly valuable to make sure only checked work gets deployed, teaching developers to deliver high-quality work and raising the bar for quality your users can expect.

Here are the essential terms to know when adopting pipelines:

• A pipeline is a file in the repository that lists commands GitHub or Azure DevOps will run for you.
• Every pipeline starts from a trigger, in this case an event; every push to the main branch.
• A runner (GitHub) or agent (Azure DevOps) is a temporary machine that starts empty, runs the steps defined in the pipeline file, and is deleted afterwards. One execution of the whole pipeline is a run.
• A step is one command or a few lines of script. The run stops at the first step that returns a non-zero exit code, and shows as failed.
• A job is a group of steps that runs on one runner or agent; there are two jobs in the pipeline below, and the second job only starts when the first one passed.

To set this up, commit the file below as azure-pipelines.yml and the TE CLI Linux archive to tools/te/, then create the pipeline in Azure DevOps (Pipelines, New pipeline, your repository, Existing Azure Pipelines YAML file). The sample repository's README covers the details, and GitHub.

Azure DevOps (azure-pipelines.yml):

trigger:
  branches:
    include:
      - main

stages:
  - stage: Validate
    jobs:
      - job: Model
        displayName: Validate model
        pool:
          vmImage: ubuntu-latest
        steps:
          - checkout: self

          - script: |
              tar -xzf ./tools/te/te-*-linux-x64.tar.gz -C ./tools/te
              echo "##vso[task.prependpath]$(Build.SourcesDirectory)/tools/te"
            displayName: Set up Tabular Editor CLI

          - script: te validate --model "Invoiced Sales.SemanticModel" --errors-only --ci azure-devops
            displayName: Validate model

          - script: te bpa run --model "Invoiced Sales.SemanticModel" --fail-on error --ci azure-devops
            displayName: Best Practice Analyzer

      - job: Report
        displayName: Validate report
        dependsOn: Model
        pool:
          vmImage: windows-latest
        steps:
          - checkout: self

          - task: UsePythonVersion@0
            inputs:
              versionSpec: "3.12"

          - script: pip install pbir-cli==0.9.32
            displayName: Set up pbir-cli

          - script: pbir validate "Invoiced Sales.Report" --fields --strict
            displayName: Validate report

You can find the GitHub Actions version of this pipeline in the sample repository at .github/workflows/invoiced-sales.yml.

Each job starts in a clean slate; a fresh machine that downloads the repo, installs its tool, and runs the same commands Eugene ran on his own machine. The option --ci azure-devops makes the TE CLI report each error in a form Azure DevOps understands, so the error appears on the run's summary page instead of only in the long log.

The pipeline in action

Suppose Eugene had not run the check and had pushed the merge, as at the start of this article. With the pipeline in place, the push now fails at the Validate model job; the BPA step is skipped and the report job never starts. Nothing was deployed, no report was changed, and the run page shows the commit and the error. The broken model is still in the repository, but the pipeline flags it and keeps failing until Eugene fixes it.

Eugene performs the fixes we described earlier, for the model and the report, and pushes again. This time both jobs pass and the run is green:

The pipeline only checks the change after it has reached the main branch, because in this part everyone pushes straight to main. In part 4, changes go to a branch first, and the same checks must pass before the merge, so a broken change never reaches main.

Further Reading

• CI/CD Integration (Tabular Editor Docs). Complete GitHub Actions and Azure DevOps pipelines for the TE CLI, including the deployment and test steps that the next parts of this series add.
• Command Reference (Tabular Editor Docs). Every option of te bpa run, including where your own rules can come from: the model, a rules file, or the CLI configuration.
• Building CI/CD maturity for report development (Tabular Editor). Our take on CI/CD maturity for report development, which points back to this series for building the pipeline step by step.

Conclusion

Clean Git merges can break semantic models because Git doesn't validate that, and without proper checks you only find out when the problem is reported to you. In part 2 of our five-part "deploy it right" blog series, you learned to check the model with te validate and Best Practice Analyzer, check the report with pbir validate, and put all three checks in a pipeline. Every push to main now runs them, and the run page will tell you which commit failed and why. Had the pipeline been in place, Eugene's broken merge would have been flagged minutes after his push, instead of whenever someone happened to open the model.

In the next parts, we'll deploy the checked model and record which commit reached the workspace (part 3), control what can reach production (part 4), and check that the numbers are still right after a change (part 5).