TMDL in Fabric: two routes for the same file

For years a semantic model was a binary file: it could not be reviewed by two people, nor could you see what a change had altered, nor merge two pieces of work. Review meant opening the tool and looking. The text format settles that at a stroke: a measure becomes a few lines, a diff becomes legible again, and the deployment question can finally be posed in the same terms as for code.

/// Revenue excluding tax, at invoice date.
measure 'Revenue ex tax' =
    SUMX ( Sales, Sales[quantity] * Sales[unit_price] )
    formatString: #,##0 €
    displayFolder: Measures\Sales

    annotation PBI_FormatHint = {"currencyCulture":"fr-FR"}

Five lines a colleague can argue with in review. That is the whole point, and it is what makes the two routes below comparable: they start from the same file.

The platform offers successive workspaces (development, test, production) and a mechanism that pushes the contents of one into the next. Deployment rules substitute what has to change on the way: the data source, a parameter, a connection. Nothing to write, everything settled in an interface.

It is the right route for a team with no integration chain and no wish for one. It has two limits worth knowing before committing. Deployment rules live in the platform, not in the repository: they are neither versioned nor reviewed, and nobody can say what they were three months ago. And the trigger stays manual or tied to the platform: there is no way to make a deployment conditional on a test suite having passed elsewhere.

The other route goes through the platform’s command line, installed in a runner, and driven by the same pipeline file as the rest of the repository. Deployment becomes one step among others, with what that implies: it runs after the tests, it can be replayed, it leaves a trace.

deploy-semantic-model:
  stage: deploy
  image: python:3.12-slim
  rules:
    - if: $CI_COMMIT_BRANCH == "main"
  before_script:
    - pip install --quiet ms-fabric-cli
    # Service principal, not a named account: a pipeline must not stop working
    # because somebody changed team.
    - fab auth login -u $FAB_CLIENT_ID -p $FAB_CLIENT_SECRET --tenant $FAB_TENANT
  script:
    - fab import /Prod.Workspace/Sales.SemanticModel -i ./models/Sales.SemanticModel -f
    - fab import /Prod.Workspace/Sales.Report -i ./reports/Sales.Report -f
    # The refresh after import: without it the model is deployed but empty, and
    # the report shows blank cells to whoever opens it first.
    - fab job run /Prod.Workspace/Sales.SemanticModel -P refreshType=full

The cost is real and it is double. It needs a service principal, its permissions on the workspaces, and the rotation of its secret: three things belonging to a platform team rather than a BI team. And it means accepting that part of what the interface did for free, such as substituting a source between environments, has to be rewritten as parameters.

I do not believe one is better, and the criterion is not technical. The question to ask is: who answers when a report is wrong on a Monday morning. If it is the BI team itself, the built-in route gives it back its autonomy and spares it depending on a chain it cannot debug. If it is a platform team already answering for everything else, then the semantic model should travel the same road as everything else, failing which it becomes the one artefact whose history nobody knows.

I say nothing about testing a semantic model, which is the real gap in both routes. We know how to deploy; we do not know how to check automatically that a measure returns what it returned before, and that is the only thing that would distinguish this chain from moving files around. Solutions exist, none struck me as simple enough to be held by the team after I left, and I would rather write that down than let it look settled.

• TMDL · format de définition d’un modèle tabulaire
• Fabric CLI