Deploy TMDL semantic models to Power BI

In part 2 of this series I showed you an approach to managing and deploying standalone Power BI datasets¹⁾ semantic models as .bim files. In this post I'll be extending the approach to support semantic models described using the newer Tabular Model Definition Language (TMDL).

The .bim files we examined in part 2 of this series are implemented in Tabular Model Scripting Language (TMSL). There are sevaral advantages to storing a semantic model in a .bim file (TMSL script) instead of a Power BI .pbix file:

• a .bim file can be meaningfully version controlled (because it's text-based)
• the semantic model definition is stored separately from any data it might ever contain – so all data remains outside version control
• a TMSL script can be published to a Power BI workspace's XMLA endpoint, allowing semantic models to be published automatically.

While this is a great improvement, working with .bim files can be difficult. TMSL is JSON-based, so it's great for script automation, but not so easy for humans to read. This is an excerpt from the Model.bim file I introduced in part 2, defining the Sales table's Amount by Due Date measure:

"measures": [
{
"name": "Amount by Due Date",
"description": "Sales amount broken down by payment due date.",
"expression": [
  "CALCULATE(",
  "  SUM(Sales[Amount])",
  ", USERELATIONSHIP(",
  "    Sales[Due Date]",
  "  ,\u0027Date\u0027[Date]",
  "  )",
  ")"
],
"annotations": [
  {
    "name": "PBI_FormatHint",
    "value": "{\"isGeneralNumber\":true}"
  }
]
},

Furthermore, the entire semantic model is defined in a single .bim file, making it vulnerable to merge conflicts from parallel development work in separate feature branches. It's for these reasons that Mathias Thierbach invented TMDL!

TMDL splits model definitions across a number of YAML-like files – here's the same measure definition, this time taken from the Sales.tmd file belonging to the TMDL representation of the same semantic model:

/// Sales amount broken down by payment due date.
measure 'Amount by Due Date' =
        CALCULATE(
          SUM(Sales[Amount])
        , USERELATIONSHIP(
            Sales[Due Date]
          ,'Date'[Date]
          )
        )
 
    annotation PBI_FormatHint = {"isGeneralNumber":true}

Already you can see this is easier-to read – there are fewer quotes, fewer list/object delimiters, and the measure definition can be copy-pasted directly into a DAX editor.

To quote the Microsoft docs, “the easiest way to explore TMDL is to reference the Analysis Services Management Objects (AMO) Nuget package and use the TMDL API methods to serialize and deserialize to and from TMDL”. Er, OK. Happily, Daniel Otykier quickly integrated TMDL support into Tabular Editor, so you can convert an existing .bim file that way instead.

If you're using TE2 (free!), set it to save to TMDL like this:

1. Use the top menu bar to select File → Preferences.
2. Select the Serialization tab.
3. Choose Serialization mode “TMDL (preview)”. As the name of the option suggests, this is a preview feature – click OK to accept the warning about this.
4. Click OK to save your preferences.

Then, to convert an existing .bim file to TMDL:

1. Open the file by selecting File → Open → From File…
2. Browse to the folder containing your .bim file, select it, then click Open.
3. Select File → Save to Folder. Browse to the folder of your choice and click Select Folder…

Browse to your new folder using your preferred file explorer. This screenshot shows the root of my exported model, in which you can see three .tmdl files and a folder named tables. The tables folder contains several files: each file describes a table in the model.

The screenshot below shows the file Sales.tmdl opened in VS Code, using Microsoft's TMDL extension – this tool brings syntax highlighting along with other benefits, and is still evolving.

Although a much friendlier format for development, TMDL is not suitable for direct deployment to Power BI – to deploy to a model's XMLA endpoint, TMSL remains the format of choice. A deployment pipeline capable of deploying a TMDL model must do two things:

1. convert the TMDL model to TMSL
2. deploy the TMSL model, as in part 2.

The deployment pipelines presented in part 2 and part 5 need only one change to achieve this – the addition of a task to convert the TMDL model. The following task uses PowerShell Core to execute a script with two arguments:

• TmdlFolderPath – the folder in which the TMDL is stored
• BimFilePath – the location into which the BIM file should be written

- task: PowerShell@2
  displayName: Generate BIM file
  inputs:      
	targetType: filePath
	filePath: "$(System.DefaultWorkingDirectory)/${{ parameters.datasetFolder }}/../../tools/ConvertTo-Tmsl.ps1"
	arguments: >
	  -TmdlFolderPath "$(tmdlFolderPath)"
	  -BimFilePath "$(bimFilePath)"
	failOnStderr: true
	pwsh: true

Here's the PowerShell script called by the deployment task above:

param(
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $TmdlFolderPath,
 
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $BimFilePath
)
 
$ErrorActionPreference = 'Stop'
 
$scriptFolder = $MyInvocation.MyCommand.Path | Split-Path
Import-Module $scriptFolder\PbiDeployment\PbiDeployment.psm1 -Force
 
Write-Host "Converting TMDL model in $TmdlFolderPath to $BimFilePath"
 
$pkgName = "Microsoft.AnalysisServices"
Write-Host "Installing $pkgName"
Install-Package -Name $pkgName -ProviderName NuGet -Force | Out-Null
 
$pkg = Get-Package $pkgName
$nugetFile = Get-ChildItem $pkg.Source
$dllPath = Join-Path $nugetFile.DirectoryName "lib\net8.0\Microsoft.AnalysisServices.Tabular.dll"
Write-Host "Loading types from $dllPath"
try {
    Add-Type -Path $dllPath
} 
catch [System.Reflection.ReflectionTypeLoadException] {
    Write-Host "Message: $($_.Exception.Message)" -ForegroundColor Green
    Write-Host "StackTrace: $($_.Exception.StackTrace)" -ForegroundColor Yellow
    Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)" -ForegroundColor Cyan
}
 
Write-Host "Reading TMDL"
Get-ChildItem -Path $TmdlFolderPath
$model = [Microsoft.AnalysisServices.Tabular.TmdlSerializer]::DeserializeDatabaseFromFolder($TmdlFolderPath)
 
Write-Host "Writing TMSL"
$options = New-Object Microsoft.AnalysisServices.Tabular.SerializeOptions
$options.SplitMultilineStrings = $true
$tmslText = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::SerializeDatabase($model,$options)
$tmslText | Out-File $BimFilePath
$tmslText

The script installs the Microsoft.AnalysisServices NuGet package (line 20) then loads types from the Microsoft.AnalysisServices.Tabular namespace (lines 22-30).

The loaded types include:

• TmdlSerializer – defines a method for reading a TMDL database folder into an in-memory tabular object model (used on line 37)
• JsonSerializer – defines a method for writing an in-memory tabular object model as a TMSL file (used on line 42)

The combined effect of these two calls is to read the TMDL model into memory, then write it back out as TMSL. At that point the TMSL model file is ready for deployment to an XMLA endpoint, as you've seen in earlier posts.

The JSON-based TMSL format introduced in part 2 is version controllable and automatically deployable, but has disadvantages – it's not very readable, and a single model file is prone to merge conflicts when multiple developers work in parallel. In this post I showed you how to:

• use Tabular Editor to convert a TMSL model to TMDL
• use VS Code to explore and work with TMDL files
• convert TMDL models back to TMSL to enable automated deployment.