Tmdl Mastery

TMDL (Tabular Model Definition Language) Mastery

Overview

TMDL reference for language syntax, folder structure, object types, expressions, serialization API, CI/CD, and deployment. TMDL is the human-readable, source-control-friendly format for Power BI and Analysis Services semantic models at compatibility level 1200+.

2026 Status Snapshot

Bottom line: Use TMDL for new Power BI / Fabric semantic models under source control. Retain TMSL for XMLA scripting or tools that require model.bim.

TMDL vs TMSL vs BIM

When to use TMDL: Any new source-controlled, CI/CD, or team PBIP project.

When to use TMSL/BIM: Legacy projects, Report Server (no TMDL support), or tools that only accept BIM.

Object Declaration Syntax

Declare objects by specifying the TOM object type followed by its name:

model Model
    culture: en-US

table Sales

    measure 'Sales Amount' = SUM(Sales[Amount])
        formatString: $ #,##0

    column 'Product Key'
        dataType: int64
        sourceColumn: ProductKey
        summarizeBy: none

Key rules:

• Enclose names in single quotes if they contain dot, equals, colon, single quote, or whitespace
• Escape single quotes within names by doubling them: 'My ''Special'' Table'
• Child objects are implicitly nested under their parent via indentation (no explicit collections)
• Child objects need not be contiguous -- columns and measures can interleave freely

Property Syntax

Properties use colon delimiter; expressions use equals delimiter:

column Category
    dataType: string          /// colon for non-expression properties
    sortByColumn: 'Cat Order' /// colon for object references
    isHidden                  /// boolean shortcut (true implied)
    isAvailableInMdx: false   /// explicit boolean

measure Total = SUM(Sales[Amount])   /// equals for default expression
    formatString: $ #,##0            /// colon for properties after expression

Text property values: Leading/trailing double-quotes optional and auto-stripped. Required if value has leading/trailing whitespace. Escape internal double-quotes by doubling them.

Default Properties by Object Type

Default properties use equals (=) on the same line or as multi-line expression on the following lines.

Expressions -- Single-Line and Multi-Line

````tmdl /// Single-line expression measure 'Sales Amount' = SUM(Sales[Amount])

/// Multi-line expression (indented one level deeper than parent properties) measure 'YoY Growth %' = VAR CurrentSales = [Sales Amount] VAR PYSales = CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE(CurrentSales - PYSales, PYSales) formatString: 0.00%

/// Triple-backtick block for verbatim content (preserves whitespace exactly) partition 'Sales-Part' = m mode: import source = ``` let Source = Sql.Database("server", "db"), Sales = Source{[Schema="dbo",Item="Sales"]}[Data] in Sales

Expression rules:

• Multi-line expressions indent one level deeper than parent properties
• Trailing blanks are stripped unless using triple-backtick blocks
• Triple-backtick blocks preserve whitespace; end delimiter sets left boundary

Descriptions (/// Syntax)

/// This table contains all sales transactions
/// Updated daily via incremental refresh
table Sales

    /// Total revenue across all product lines
    measure 'Sales Amount' = SUM(Sales[Amount])

Triple-slash comments above an object become its TOM Description property. No whitespace between description block and object type keyword.

Ref Keyword

Reference another TMDL object or define collection ordering:

/// In model.tmdl -- defines table ordering for deterministic roundtrips
model Model
    culture: en-US

ref table Calendar
ref table Sales
ref table Product
ref table Customer

ref culture en-US
ref culture pt-PT

ref role 'Regional Manager'

Rules: Objects referenced but missing their file are ignored on deserialization. Objects with files but no ref are appended to collection end.

TMDL Folder Structure

definition/
  database.tmdl           # Database properties (compatibilityLevel, etc.)
  model.tmdl              # Model properties, ref declarations
  relationships.tmdl      # All relationships
  expressions.tmdl        # Shared/named expressions (Power Query parameters)
  functions.tmdl          # DAX user-defined functions
  dataSources.tmdl        # Legacy data sources
  tables/
    Sales.tmdl            # Table + all its columns, measures, partitions, hierarchies
    Product.tmdl
    Calendar.tmdl
  roles/
    RegionalManager.tmdl  # Role definition with permissions and members
    Admin.tmdl
  cultures/
    en-US.tmdl            # Translations for all objects in this culture
    pt-PT.tmdl
  perspectives/
    SalesView.tmdl        # Perspective definition

One file per table, role, culture, and perspective. All inner metadata (columns, measures, partitions, hierarchies) lives inside the parent table file.

TMDL Scripts (createOrReplace)

Apply changes to a live semantic model using the TMDL view in Power BI Desktop:

createOrReplace

    table 'Time Intelligence'
        calculationGroup
            precedence: 1

        calculationItem Current = SELECTEDMEASURE()

        calculationItem YTD =
                CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[Date]))

        calculationItem PY =
                CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))

        column 'Time Calc'
            dataType: string
            sourceColumn: Name
            sortByColumn: Ordinal

        column Ordinal
            dataType: int64
            sourceColumn: Ordinal
            summarizeBy: none

Only one command verb per script execution. The createOrReplace command creates or replaces specified objects and all descendants.

Indentation Rules

TMDL uses strict whitespace indentation with a default single tab per level:

• Level 1: Object declaration (table, measure, column)
• Level 2: Object properties (dataType, formatString)
• Level 3: Multi-line expressions (DAX/M code)

Database-level and model-level direct children (table, relationship, role, culture, perspective, expression) do not require indentation since they are implicitly under the root. Incorrect indentation produces a TmdlFormatException.

Casing and Whitespace

• Serialization uses camelCase for object types, keywords, and enum values
• Deserialization is case-insensitive
• Property value leading/trailing whitespace is trimmed
• Expression trailing blank lines are dropped
• Blank whitespace-only lines within expressions are preserved as empty lines

TMDL Serialization API (.NET)

using Microsoft.AnalysisServices.Tabular;

// Serialize model to TMDL folder
TmdlSerializer.SerializeDatabaseToFolder(database, @"C:\output\model-tmdl");

// Deserialize TMDL folder back to TOM
var db = TmdlSerializer.DeserializeDatabaseFromFolder(@"C:\output\model-tmdl");

// Serialize single object to string
string tmdl = TmdlSerializer.SerializeObject(table.Measures["Total Sales"]);

NuGet package: Microsoft.AnalysisServices.NetCore.retail.amd64 (or .NET Framework equivalent)

Error types: TmdlFormatException (invalid syntax) and TmdlSerializationException (valid syntax but invalid TOM metadata). Both include Document, Line, and LineText properties.

Self-Validation

Before deploying any TMDL you've generated, run the four-layer validation pipeline from the `powerbi-master:validation-testing` skill:

1. Syntax -- TmdlSerializer.DeserializeDatabaseFromFolder catches TmdlFormatException (bad indentation, invalid keywords) 2. Metadata -- the same call catches TmdlSerializationException (invalid property combinations) and model.Validate() catches dangling references 3. Best practice -- Tabular Editor 2 CLI -A switch or semantic-link-labs.run_model_bpa runs the standard Microsoft BPA rule set 4. Lineage -- model.Validate().Errors plus custom Tabular Editor C# scripts catch sortByColumn / measure-references-column issues

The validation-testing skill provides ready-to-run recipes (C#, Python, GitHub Actions YAML) for each layer. Always validate before recommending a deploy.

TMDL View in Power BI Desktop (2026)

The TMDL view is an integrated code editor inside Power BI Desktop for scripting semantic-model changes. As of the 2026 release wave its feature set includes:

• Semantic highlighting, autocomplete (Ctrl+Space), tooltips on hover, and code actions (generate lineage tags, correct property misspellings)
• Code formatting via Shift+Alt+F or ribbon Format button (including "Format Selection" from the context menu)
• Error diagnostics with a dedicated Problems pane
• Preview diff -- side-by-side or inline TMDL diff of the model before and after executing the script, navigable via toolbar (previewing runs only valid TMDL)
• Compatibility level upgrade prompt -- if the script uses a property above the current model compatibility level (e.g., 1550 -> 1601 for formatStringDefinition), Desktop prompts to upgrade automatically
• Multi-tab scripts, saved into TMDLScripts/ folder in PBIP projects (one file per tab)
• Drag objects from the Data pane onto the editor to script them as createOrReplace; multi-select with Ctrl before dragging
• Right-click > Script TMDL to a new tab or clipboard
• Apply button applies metadata-only changes (no data refresh); renaming a column via TMDL decouples it from sourceColumn (Power Query editor still shows the source name)
• Bulk rename via regex find-and-replace -- common pattern for prefix removal (fact_, dim_) or case changes

Limitation: Desktop does not hot-reload TMDL files changed externally. After editing .tmdl files in VS Code, restart Power BI Desktop to pick up the changes.

Semantic Link Labs (Python TOM from Fabric Notebooks)

For scripting semantic models from Python without a .NET toolchain, use semantic-link-labs (PyPI: semantic-link-labs) inside a Fabric notebook. It provides a Pythonic wrapper over TOM:

%pip install semantic-link-labs -q

import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev", readonly=False) as tom:
    # Add a measure
    tom.add_measure(
        table_name="Sales",
        measure_name="Sales Amount",
        expression="SUM(Sales[Amount])",
        format_string="$ #,##0.00",
        display_folder="Revenue",
    )

    # Add an incremental refresh policy
    tom.add_incremental_refresh_policy(
        table_name="Sales",
        column_name="OrderDate",
        start_date="2020-01-01T00:00:00",
        end_date="2025-12-31T00:00:00",
        incremental_granularity="Day",
        incremental_periods=30,
        rolling_window_granularity="Month",
        rolling_window_periods=36,
    )

    # tom.save_changes() is called automatically on context exit

See references/tmdl-programmatic-python.md for a full cookbook.

Additional Resources

Reference Files

• `references/tmdl-syntax-reference.md` -- Complete TMDL syntax and grammar reference with all object types, properties, and expression rules
• `references/tmdl-examples-cookbook.md` -- Copy-pasteable TMDL examples for every object type: tables, columns, measures, partitions, relationships, roles, perspectives, cultures, calculation groups, hierarchies, KPIs, annotations, and field parameters
• `references/tmdl-cicd-patterns.md` -- CI/CD pipelines, Git integration, deployment patterns, Tabular Editor CLI, Azure DevOps and GitHub Actions workflows, and merge conflict strategies
• `references/tmdl-programmatic-python.md` -- semantic-link-labs / SemPy TOM scripting from Fabric Python notebooks: measures, calc groups, incremental refresh, RLS, Direct Lake, BPA, and model export patterns

Related Skills

• `powerbi-master:validation-testing` -- Validate TMDL artifacts before deployment: TmdlSerializer parser, TOM Validate, BPA via Tabular Editor CLI / semantic-link-labs, custom C# rule scripts

Official Microsoft Learn References (2026)

• Announcing general availability of TMDL -- TMDL GA announcement
• Use the TMDL view in Power BI Desktop -- Feature walkthrough with scenarios
• Power BI Desktop project semantic model folder -- TMDL inside PBIP
• TMDL scripts reference (Microsoft Learn) -- createOrReplace command documentation

TMDL CI/CD, Git Integration, and Deployment Patterns

TMDL in Power BI Desktop (PBIP)

Enabling TMDL Format

1. File > Options and settings > Options > Preview features 2. Check "Store semantic model using TMDL format" 3. Save As > Power BI Project (.pbip)

The semantic model is saved as a definition/ folder inside the semantic model folder, replacing the monolithic model.bim file.

PBIP Project Structure with TMDL

MyReport.pbip
MyReport.report/
    definition.pbir
    report.json
    pages/
        ...
MyReport.SemanticModel/
    definition.pbism            # Required -- specifies model format
    diagramLayout.json
    .pbi/
        localSettings.json      # Git-ignored (user-specific)
        editorSettings.json     # Shared editor settings
        cache.abf               # Git-ignored (local data cache)
    definition/                 # TMDL folder (replaces model.bim)
        database.tmdl
        model.tmdl
        relationships.tmdl
        expressions.tmdl
        tables/
            Sales.tmdl
            Product.tmdl
            Calendar.tmdl
        roles/
            RegionalManager.tmdl
        cultures/
            en-US.tmdl
        perspectives/
            SalesView.tmdl
    DAXQueries/                 # DAX query view saved tabs
        Analysis.dax
    TMDLScripts/                # TMDL view saved script tabs
        CalcGroup.tmdl

Converting BIM to TMDL

Via Power BI Desktop: 1. Open existing PBIP project 2. Enable TMDL preview feature 3. Save -- Desktop prompts to upgrade 4. Select "Upgrade" (one-way conversion; BIM is deleted)

Via Tabular Editor 2/3: 1. File > Preferences > Serialization > "TMDL" mode 2. Open existing .bim file 3. File > Save to Folder -- outputs TMDL folder

Via C# / TmdlSerializer:

// Load BIM file
var db = JsonSerializer.DeserializeDatabase(File.ReadAllText("model.bim"));

// Export as TMDL folder
TmdlSerializer.SerializeDatabaseToFolder(db, @"C:\output\definition");

External Editing of TMDL Files

Install the VS Code TMDL extension for syntax highlighting, autocomplete, and diagnostics:

• Extension: "TMDL" by Microsoft (marketplace ID: analysis-services.TMDL)
• Newer alternative: "TMDL Language Support" (marketplace ID: CPIM.TMDL-language-support) -- adds DAX/M semantic highlighting, code actions, and formatting

Important: Power BI Desktop does not detect external file changes. Restart Desktop after editing TMDL files externally.

TMDL View in Power BI Desktop

The TMDL view provides an integrated code editor for scripting semantic model changes:

• Drag objects from Data pane onto the TMDL view editor
• Right-click objects and select "Script TMDL"
• Edit with autocomplete, semantic highlighting, error diagnostics
• Preview changes before applying (side-by-side diff view)
• Apply changes via the Apply button (metadata only -- no data refresh)
• Script tabs are saved in TMDLScripts/ folder for PBIP projects

Key capabilities:

• Create calculation groups, perspectives, translations (objects without Desktop UI)
• Bulk rename using find-and-replace with regex
• Switch storage modes by modifying partition definitions
• Back up/restore semantic model metadata via saved scripts

Git Integration Patterns

.gitignore for PBIP with TMDL

# Power BI local cache and settings
**/.pbi/localSettings.json
**/.pbi/cache.abf

# Optional: exclude unapplied Power Query changes
# **/.pbi/unappliedChanges.json

# Build artifacts
**/bin/
**/obj/
*.user

Branch Strategy for Power BI Teams

main (protected)
  |
  +-- dev (integration branch, connected to Dev Fabric workspace)
       |
       +-- feature/add-yoy-measures     (developer 1)
       +-- feature/update-product-table  (developer 2)
       +-- feature/new-rls-role          (developer 3)

Workflow: 1. Developer creates feature branch from dev 2. Makes changes in Desktop (PBIP/TMDL) or directly edits TMDL files 3. Commits and pushes to feature branch 4. Creates Pull Request to dev 5. CI pipeline validates TMDL (syntax, BPA rules) 6. Reviewer inspects TMDL diffs (granular per-file changes) 7. Merge to dev triggers sync to Dev workspace via Fabric Git 8. Promote dev to main via PR for production deployment

Merge Conflict Resolution

TMDL's file-per-object structure minimizes conflicts. When they occur:

Common conflict: model.tmdl ref ordering

<<<<<<< HEAD
ref table Sales
ref table Product
ref table NewTableA
=======
ref table Sales
ref table Product
ref table NewTableB
>>>>>>> feature/branch

Resolution: Include both ref lines. Order matters for deterministic roundtrips but does not affect functionality.

Common conflict: Same measure edited by two developers Since all measures for a table live in one file (tables/Sales.tmdl), conflicts can happen when two developers edit different measures in the same table. Resolution: Use standard three-way merge; each measure is a distinct block separated by blank lines.

Prevention strategy: Assign table ownership per developer when possible. Use partial declarations to split measures into separate files if needed.

CI/CD with Azure DevOps

Pipeline: Validate TMDL with Tabular Editor BPA

# azure-pipelines.yml
trigger:
  branches:
    include:
      - main
      - dev
  paths:
    include:
      - '*.SemanticModel/**'

pr:
  branches:
    include:
      - main
      - dev

pool:
  vmImage: 'windows-latest'

jobs:
- job: Validate_Semantic_Models
  displayName: 'Validate Semantic Models (TMDL)'
  steps:

  - task: PowerShell@2
    displayName: 'Download Tabular Editor CLI'
    inputs:
      targetType: 'inline'
      script: |
        $teUrl = "https://github.com/TabularEditor/TabularEditor/releases/latest/download/TabularEditor.Portable.zip"
        Invoke-WebRequest -Uri $teUrl -OutFile "TabularEditor.zip"
        Expand-Archive -Path "TabularEditor.zip" -DestinationPath "$(Agent.ToolsDirectory)/TabularEditor"

  - task: PowerShell@2
    displayName: 'Download BPA Rules'
    inputs:
      targetType: 'inline'
      script: |
        $rulesUrl = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json"
        Invoke-WebRequest -Uri $rulesUrl -OutFile "$(Agent.ToolsDirectory)/BPARules.json"

  - task: PowerShell@2
    displayName: 'Run BPA on all semantic models'
    inputs:
      targetType: 'inline'
      script: |
        $tePath = "$(Agent.ToolsDirectory)/TabularEditor/TabularEditor.exe"
        $rulesPath = "$(Agent.ToolsDirectory)/BPARules.json"
        $exitCode = 0

        Get-ChildItem -Path "$(Build.SourcesDirectory)" -Filter "definition" -Directory -Recurse | ForEach-Object {
            $modelPath = $_.FullName
            Write-Host "Validating: $modelPath"

            & $tePath $modelPath -A $rulesPath -V
            if ($LASTEXITCODE -ne 0) { $exitCode = 1 }
        }

        exit $exitCode

Pipeline: Deploy TMDL to Power BI via XMLA

# deploy-pipeline.yml
trigger:
  branches:
    include:
      - main
  paths:
    include:
      - '*.SemanticModel/**'

pool:
  vmImage: 'windows-latest'

variables:
  - group: PowerBI-ServicePrincipal  # Contains clientId, clientSecret, tenantId
  - name: workspaceXmla
    value: 'powerbi://api.powerbi.com/v1.0/myorg/Production-Workspace'
  - name: datasetName
    value: 'AdventureWorks'

jobs:
- job: Deploy_Semantic_Model
  displayName: 'Deploy TMDL to Power BI'
  steps:

  - task: PowerShell@2
    displayName: 'Install AMO NuGet Package'
    inputs:
      targetType: 'inline'
      script: |
        Register-PackageSource -Name NuGet -Location https://api.nuget.org/v3/index.json -ProviderName NuGet -Force
        Install-Package Microsoft.AnalysisServices.NetCore.retail.amd64 -Source NuGet -Destination "$(Agent.ToolsDirectory)/nuget" -Force -SkipDependencies

  - task: PowerShell@2
    displayName: 'Deploy TMDL to XMLA endpoint'
    inputs:
      targetType: 'inline'
      script: |
        $nugetPath = "$(Agent.ToolsDirectory)/nuget"
        $amoPath = Get-ChildItem -Path $nugetPath -Filter "Microsoft.AnalysisServices.Core.dll" -Recurse | Select-Object -First 1
        $tabPath = Get-ChildItem -Path $nugetPath -Filter "Microsoft.AnalysisServices.Tabular.dll" -Recurse | Select-Object -First 1

        [System.Reflection.Assembly]::LoadFrom($amoPath.FullName) | Out-Null
        [System.Reflection.Assembly]::LoadFrom($tabPath.FullName) | Out-Null

        $tmdlPath = Get-ChildItem -Path "$(Build.SourcesDirectory)" -Filter "definition" -Directory -Recurse | Select-Object -First 1

        Write-Host "Deserializing TMDL from: $($tmdlPath.FullName)"
        $model = [Microsoft.AnalysisServices.Tabular.TmdlSerializer]::DeserializeModelFromFolder($tmdlPath.FullName)

        $connStr = "DataSource=$(workspaceXmla);User ID=app:$(clientId)@$(tenantId);Password=$(clientSecret)"
        $server = New-Object Microsoft.AnalysisServices.Tabular.Server
        $server.Connect($connStr)

        $db = $server.Databases["$(datasetName)"]
        $model.CopyTo($db.Model)
        $db.Model.SaveChanges()
        $server.Disconnect()

        Write-Host "Deployment complete."
    env:
      clientId: $(clientId)
      clientSecret: $(clientSecret)
      tenantId: $(tenantId)

CI/CD with GitHub Actions

Validate TMDL with Tabular Editor

# .github/workflows/validate-tmdl.yml
name: Validate Semantic Models

on:
  pull_request:
    paths:
      - '**.SemanticModel/**'
  push:
    branches: [main, dev]
    paths:
      - '**.SemanticModel/**'

jobs:
  validate:
    runs-on: windows-latest
    steps:
      - uses: actions/checkout@v4

      - name: Download Tabular Editor
        shell: pwsh
        run: |
          $url = "https://github.com/TabularEditor/TabularEditor/releases/latest/download/TabularEditor.Portable.zip"
          Invoke-WebRequest -Uri $url -OutFile TabularEditor.zip
          Expand-Archive TabularEditor.zip -DestinationPath ./te

      - name: Download BPA Rules
        shell: pwsh
        run: |
          Invoke-WebRequest -Uri "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json" -OutFile BPARules.json

      - name: Run Best Practice Analyzer
        shell: pwsh
        run: |
          $exitCode = 0
          Get-ChildItem -Path . -Filter "definition" -Directory -Recurse | ForEach-Object {
              Write-Host "Checking: $($_.FullName)"
              & ./te/TabularEditor.exe $_.FullName -A BPARules.json -V
              if ($LASTEXITCODE -ne 0) { $exitCode = 1 }
          }
          exit $exitCode

Deploy TMDL via Tabular Editor CLI

# .github/workflows/deploy-tmdl.yml
name: Deploy Semantic Model

on:
  push:
    branches: [main]
    paths:
      - '**.SemanticModel/**'

jobs:
  deploy:
    runs-on: windows-latest
    environment: production
    steps:
      - uses: actions/checkout@v4

      - name: Download Tabular Editor 2
        shell: pwsh
        run: |
          $url = "https://github.com/TabularEditor/TabularEditor/releases/latest/download/TabularEditor.Portable.zip"
          Invoke-WebRequest -Uri $url -OutFile TabularEditor.zip
          Expand-Archive TabularEditor.zip -DestinationPath ./te

      - name: Deploy to Power BI
        shell: pwsh
        env:
          PBI_CLIENT_ID: ${{ secrets.PBI_CLIENT_ID }}
          PBI_CLIENT_SECRET: ${{ secrets.PBI_CLIENT_SECRET }}
          PBI_TENANT_ID: ${{ secrets.PBI_TENANT_ID }}
          PBI_WORKSPACE_XMLA: ${{ vars.PBI_WORKSPACE_XMLA }}
          PBI_DATASET_NAME: ${{ vars.PBI_DATASET_NAME }}
        run: |
          $tmdlPath = Get-ChildItem -Path . -Filter "definition" -Directory -Recurse | Select-Object -First 1
          $connStr = "Provider=MSOLAP;DataSource=$env:PBI_WORKSPACE_XMLA;User ID=app:$env:PBI_CLIENT_ID@$env:PBI_TENANT_ID;Password=$env:PBI_CLIENT_SECRET;Initial Catalog=$env:PBI_DATASET_NAME"
          & ./te/TabularEditor.exe $tmdlPath.FullName -D $connStr "$env:PBI_DATASET_NAME"

Fabric Git Integration with TMDL

Connecting Fabric Workspace to Git

1. Open Fabric workspace > Settings > Git integration 2. Connect to Azure DevOps repo or GitHub repo 3. Select branch and folder 4. Fabric exports all workspace items including semantic models as TMDL

Fabric Git Workflow

Fabric Workspace (Dev)  <--->  Azure DevOps / GitHub (dev branch)
         |
         | (PR + pipeline validation)
         v
Fabric Workspace (Prod) <--->  Azure DevOps / GitHub (main branch)

Developer using Desktop: 1. Clone repo locally 2. Open .pbip in Power BI Desktop 3. Edit semantic model (changes saved as TMDL) 4. Commit and push to feature branch 5. Create PR -- pipeline validates 6. Merge to dev -- Fabric syncs automatically

Developer using Fabric Service: 1. Create branch from workspace 2. Edit semantic model in Service (TMDL view or web editor) 3. Commit from workspace to branch 4. Create PR -- pipeline validates 5. Merge to dev -- workspace updates

Tabular Editor 3 with Fabric Git

Tabular Editor 3 can connect directly to TMDL folders in a cloned repo: 1. File > Open > From Folder > select definition/ folder 2. Edit model (DAX, tables, relationships) 3. Save (writes TMDL files back to disk) 4. Commit and push via Git

Deployment Patterns

Pattern 1: TMDL Source + XMLA Deploy (Recommended)

TMDL files (Git) --> TmdlSerializer.Deserialize --> TOM Model --> XMLA endpoint

Best for teams using Premium/Fabric capacity with XMLA read-write enabled.

Pattern 2: TMDL Source + Tabular Editor CLI Deploy

TMDL files (Git) --> TabularEditor.exe -D <connection> --> XMLA endpoint

Simplest approach using Tabular Editor 2 (free). No custom code needed.

Pattern 3: TMDL Source + Convert to BIM + Deploy

TMDL files --> TmdlSerializer --> JsonSerializer --> model.bim --> Deploy via TMSL/REST

Useful when deployment tooling only accepts BIM format.

PowerShell conversion script:

param(
    [string]$TmdlFolderPath,
    [string]$BimFilePath
)

# Install NuGet package
$pkg = Install-Package Microsoft.AnalysisServices.NetCore.retail.amd64 -Source NuGet -Destination ./nuget -Force -SkipDependencies
$dllPath = Get-ChildItem -Path ./nuget -Filter "Microsoft.AnalysisServices.Tabular.dll" -Recurse | Select-Object -First 1

Add-Type -Path $dllPath.FullName

$db = [Microsoft.AnalysisServices.Tabular.TmdlSerializer]::DeserializeDatabaseFromFolder($TmdlFolderPath)

$options = New-Object Microsoft.AnalysisServices.Tabular.SerializeOptions
$options.SplitMultilineStrings = $true

$bimContent = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::SerializeDatabase($db, $options)

Set-Content -Path $BimFilePath -Value $bimContent -Encoding UTF8

Write-Host "Converted TMDL to BIM: $BimFilePath"

Pattern 4: Fabric Deployment Pipelines

Use Fabric's built-in deployment pipelines for promotion across environments without custom CI/CD:

Dev Workspace --> Test Workspace --> Production Workspace
    (linked to dev branch)  (linked to test branch)  (linked to main branch)

Fabric deployment pipelines handle semantic model deployment natively, including dataset refresh rules and parameter binding.

TmdlSerializer Complete API Reference

Namespace and Package

using Microsoft.AnalysisServices.Tabular;
// NuGet: Microsoft.AnalysisServices.NetCore.retail.amd64
// Minimum version: 19.61+ (TMDL support)

Folder Serialization

// Serialize TOM database to TMDL folder
TmdlSerializer.SerializeDatabaseToFolder(Database database, string path);

// Deserialize TMDL folder to TOM database
Database db = TmdlSerializer.DeserializeDatabaseFromFolder(string path);

// Deserialize TMDL folder to TOM model (without database wrapper)
Model model = TmdlSerializer.DeserializeModelFromFolder(string path);

String Serialization

// Serialize any TOM object to TMDL text
string tmdl = TmdlSerializer.SerializeObject(MetadataObject obj, bool qualifyObject = true);

qualifyObject: true includes parent ref declarations (e.g., ref table Sales before column definition).

Compressed File Serialization

// Serialize to compressed file (.tmdl.zip)
TmdlSerializer.SerializeModelToCompressedFile(Model model, string path);

// Deserialize from compressed file
Model model = TmdlSerializer.DeserializeModelFromCompressedFile(string path);

Stream Serialization

// Serialize model to stream documents
foreach (MetadataDocument doc in model.ToTmdl())
{
    using (TextWriter writer = new StreamWriter($"output/{doc.ObjectType}.tmdl"))
    {
        doc.WriteTo(writer);
    }
}

// Deserialize from streams (selective loading)
var context = MetadataSerializationContext.Create(MetadataSerializationStyle.Tmdl);

foreach (var file in Directory.GetFiles(tmdlPath, "*.tmdl", SearchOption.AllDirectories))
{
    if (file.Contains("/roles/")) continue;  // Skip roles
    using (TextReader reader = File.OpenText(file))
    {
        context.ReadFromDocument(file, reader);
    }
}

Model model = context.ToModel();

Error Handling

try
{
    var db = TmdlSerializer.DeserializeDatabaseFromFolder(tmdlPath);
}
catch (TmdlFormatException ex)
{
    // Invalid TMDL syntax (bad keyword, wrong indentation)
    Console.WriteLine($"Syntax error in '{ex.Document}' at line {ex.Line}: {ex.Message}");
    Console.WriteLine($"Line text: {ex.LineText}");
}
catch (TmdlSerializationException ex)
{
    // Valid syntax but invalid TOM metadata (type mismatch, missing required property)
    Console.WriteLine($"Metadata error in '{ex.Document}' at line {ex.Line}: {ex.Message}");
}

Python Integration

Python can invoke TMDL operations via pythonnet or by calling Tabular Editor CLI:

Using pythonnet (CLR)

import clr
clr.AddReference("Microsoft.AnalysisServices.Tabular")
from Microsoft.AnalysisServices.Tabular import TmdlSerializer, Server

# Deserialize TMDL
model = TmdlSerializer.DeserializeModelFromFolder("./definition")

# Deploy
server = Server()
server.Connect("powerbi://api.powerbi.com/v1.0/myorg/Workspace")
db = server.Databases["MyDataset"]
model.CopyTo(db.Model)
db.Model.SaveChanges()
server.Disconnect()

Using Tabular Editor CLI from Python

import subprocess

result = subprocess.run([
    "./te/TabularEditor.exe",
    "./definition",
    "-D", "Provider=MSOLAP;DataSource=powerbi://...;User ID=app:client@tenant;Password=secret",
    "MyDataset"
], capture_output=True, text=True)

print(result.stdout)
if result.returncode != 0:
    print(f"Error: {result.stderr}")

Common Gotchas

TMDL Examples Cookbook

Complete, copy-pasteable TMDL examples for every object type. All examples follow correct indentation (single tab per level) and TMDL syntax conventions.

Complete Model Definition

database.tmdl

database AdventureWorks
    compatibilityLevel: 1601

model.tmdl

model Model
    culture: en-US
    defaultPowerBIDataSourceVersion: powerBI_V3
    discourageImplicitMeasures
    sourceQueryCulture: en-US

ref table Calendar
ref table Product
ref table Customer
ref table Sales
ref table 'Sales Targets'

ref culture en-US
ref culture pt-PT

ref role 'Regional Manager'
ref role Administrator

ref perspective SalesView

relationships.tmdl

/// Sales to Product (many-to-one)
relationship 550e8400-e29b-41d4-a716-446655440000
    fromColumn: Sales.'Product Key'
    toColumn: Product.'Product Key'

/// Sales to Customer (many-to-one)
relationship 6ba7b810-9dad-11d1-80b4-00c04fd430c8
    fromColumn: Sales.'Customer Key'
    toColumn: Customer.'Customer Key'

/// Sales to Calendar via OrderDate (active)
relationship 6ba7b811-9dad-11d1-80b4-00c04fd430c8
    fromColumn: Sales.'Order Date'
    toColumn: Calendar.Date
    isActive

/// Sales to Calendar via ShipDate (inactive)
relationship 6ba7b812-9dad-11d1-80b4-00c04fd430c8
    fromColumn: Sales.'Ship Date'
    toColumn: Calendar.Date
    isActive: false

/// Bidirectional relationship
relationship 6ba7b813-9dad-11d1-80b4-00c04fd430c8
    fromColumn: Sales.'Store Key'
    toColumn: Store.'Store Key'
    crossFilteringBehavior: bothDirections
    securityFilteringBehavior: bothDirections

/// Many-to-many relationship
relationship 6ba7b814-9dad-11d1-80b4-00c04fd430c8
    fromColumn: 'Bridge Table'.'Tag ID'
    toColumn: Tags.'Tag ID'
    fromCardinality: many
    toCardinality: many
    crossFilteringBehavior: bothDirections

/// DirectQuery with referential integrity
relationship 6ba7b815-9dad-11d1-80b4-00c04fd430c8
    fromColumn: FactSales.ProductKey
    toColumn: DimProduct.ProductKey
    relyOnReferentialIntegrity

expressions.tmdl

expression Server = "sql-server.database.windows.net" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression Database = "AdventureWorksDW" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression 'Shared Date Query' =
        let
            Source = Sql.Database(Server, Database),
            DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
        in
            DimDate
    queryGroup: 'Shared Queries'

functions.tmdl (DAX UDFs)

function AddTax = (amount : NUMERIC) => amount * 1.1

function CalcMargin = (revenue : NUMERIC, cost : NUMERIC) =>
        DIVIDE(revenue - cost, revenue)

Table with Columns, Measures, and Partitions

tables/Sales.tmdl

````tmdl /// Core sales transaction table /// Contains all order-level data with incremental refresh table Sales lineageTag: a1b2c3d4-e5f6-7890-abcd-ef1234567890

/// --- Partitions ---

partition 'Sales-Current' = m mode: import source = let Source = Sql.Database(Server, Database), Sales = Source{[Schema="dbo",Item="FactSales"]}[Data], Filtered = Table.SelectRows(Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd) in Filtered

/// --- Key Columns ---

column 'Sales Key' dataType: int64 isKey isHidden sourceColumn: SalesKey summarizeBy: none lineageTag: 11111111-aaaa-bbbb-cccc-dddddddddddd

column 'Product Key' dataType: int64 isHidden sourceColumn: ProductKey summarizeBy: none

column 'Customer Key' dataType: int64 isHidden sourceColumn: CustomerKey summarizeBy: none

/// --- Date Columns ---

column 'Order Date' dataType: dateTime formatString: yyyy-MM-dd sourceColumn: OrderDate summarizeBy: none

column 'Ship Date' dataType: dateTime formatString: yyyy-MM-dd sourceColumn: ShipDate summarizeBy: none isHidden

/// --- Fact Columns ---

column Quantity dataType: int64 sourceColumn: Quantity summarizeBy: sum

column Amount dataType: decimal formatString: $ #,##0.00 sourceColumn: Amount summarizeBy: sum

column 'Unit Price' dataType: decimal formatString: $ #,##0.00 sourceColumn: UnitPrice summarizeBy: none isHidden

column 'Unit Cost' dataType: decimal formatString: $ #,##0.00 sourceColumn: UnitCost summarizeBy: none isHidden

/// --- Calculated Column ---

column 'Profit Margin %' = DIVIDE(Sales[Amount] - Sales[Quantity] * Sales[Unit Cost], Sales[Amount]) dataType: double formatString: 0.00% summarizeBy: none displayFolder: Calculated isDataTypeInferred

/// --- Measures ---

/// Total revenue across all product lines measure 'Sales Amount' = SUM(Sales[Amount]) formatString: $ #,##0.00 displayFolder: Revenue lineageTag: 22222222-aaaa-bbbb-cccc-dddddddddddd

/// Total units sold measure 'Total Quantity' = SUM(Sales[Quantity]) formatString: #,##0 displayFolder: Volume

/// Year-over-year sales growth measure 'YoY Growth %' = VAR CurrentSales = [Sales Amount] VAR PYSales = CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Calendar'[Date])) RETURN DIVIDE(CurrentSales - PYSales, PYSales) formatString: 0.00% displayFolder: Growth

/// Rolling 12-month total measure 'Rolling 12M Sales' = CALCULATE( [Sales Amount], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH) ) formatString: $ #,##0.00 displayFolder: Revenue

/// Year-to-date sales measure 'Sales YTD' = TOTALYTD([Sales Amount], 'Calendar'[Date]) formatString: $ #,##0.00 displayFolder: Revenue

/// Count of distinct customers measure '# Customers' = DISTINCTCOUNT(Sales[Customer Key]) formatString: #,##0 displayFolder: Counts

/// Average order value measure 'Avg Order Value' = AVERAGEX( VALUES(Sales[Sales Key]), [Sales Amount] ) formatString: $ #,##0.00 displayFolder: Revenue

/// Dynamic format string measure measure 'Sales Formatted' = [Sales Amount]

formatStringDefinition = IF( [Sales Amount] >= 1000000, "$ #,##0,,.0 M", IF([Sales Amount] >= 1000, "$ #,##0,.0 K", "$ #,##0.00") ) displayFolder: Revenue

/// Detail rows definition for drill-through measure 'Sales with Detail' = [Sales Amount]

detailRowsDefinition = SELECTCOLUMNS( Sales, "Order Date", Sales[Order Date], "Product", RELATED(Product[Product Name]), "Amount", Sales[Amount], "Quantity", Sales[Quantity] )

/// --- Annotations ---

annotation PBI_ResultType = Table

annotation PBI_NavigationStepName = Navigation

annotation CustomRefreshInfo = ``` { "refreshType": "incremental", "retentionPeriodYears": 3, "incrementalPeriodMonths": 1 }

Calendar Table (Calculated)

tables/Calendar.tmdl

/// Standard date dimension table
table Calendar
    dataCategory: Time
    lineageTag: 33333333-aaaa-bbbb-cccc-dddddddddddd

    partition 'Calendar-Partition' = calculated
        source =
            CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31))

    column Date
        dataType: dateTime
        isKey
        formatString: yyyy-MM-dd
        summarizeBy: none
        sourceColumn: [Date]

    column Year = YEAR('Calendar'[Date])
        dataType: int64
        formatString: 0
        summarizeBy: none

    column 'Month Number' = MONTH('Calendar'[Date])
        dataType: int64
        formatString: 0
        summarizeBy: none
        isHidden

    column 'Month Name' = FORMAT('Calendar'[Date], "MMMM")
        dataType: string
        sortByColumn: 'Month Number'
        summarizeBy: none

    column Quarter = "Q" & FORMAT('Calendar'[Date], "Q")
        dataType: string
        summarizeBy: none

    column 'Year-Month' = FORMAT('Calendar'[Date], "YYYY-MM")
        dataType: string
        summarizeBy: none
        sortByColumn: Date

    column 'Day of Week' = FORMAT('Calendar'[Date], "dddd")
        dataType: string
        summarizeBy: none

    column 'Is Weekend' = IF(WEEKDAY('Calendar'[Date], 2) > 5, TRUE(), FALSE())
        dataType: boolean
        summarizeBy: none

    hierarchy 'Date Hierarchy'

        level Year
            column: Year

        level Quarter
            column: Quarter

        level 'Month Name'
            column: 'Month Name'

        level Date
            column: Date

Product Table with Display Folders

tables/Product.tmdl

table Product
    lineageTag: 44444444-aaaa-bbbb-cccc-dddddddddddd

    partition 'Product-Partition' = m
        mode: import
        source =
            let
                Source = Sql.Database(Server, Database),
                Product = Source{[Schema="dbo",Item="DimProduct"]}[Data]
            in
                Product

    column 'Product Key'
        dataType: int64
        isKey
        isHidden
        sourceColumn: ProductKey
        summarizeBy: none

    column 'Product Name'
        dataType: string
        isDefaultLabel
        sourceColumn: ProductName
        summarizeBy: none
        displayFolder: Description

    column Category
        dataType: string
        sourceColumn: Category
        summarizeBy: none
        displayFolder: Classification

    column Subcategory
        dataType: string
        sourceColumn: Subcategory
        summarizeBy: none
        displayFolder: Classification

    column Brand
        dataType: string
        sourceColumn: Brand
        summarizeBy: none
        displayFolder: Classification

    column Color
        dataType: string
        sourceColumn: Color
        summarizeBy: none
        displayFolder: Attributes

    column 'Unit Price'
        dataType: decimal
        formatString: $ #,##0.00
        sourceColumn: UnitPrice
        summarizeBy: none
        displayFolder: Pricing

    column 'Unit Cost'
        dataType: decimal
        formatString: $ #,##0.00
        sourceColumn: UnitCost
        summarizeBy: none
        displayFolder: Pricing

    column 'Product Image URL'
        dataType: string
        sourceColumn: ImageURL
        dataCategory: ImageUrl
        isHidden

    measure '# Products' = COUNTROWS(Product)
        formatString: #,##0

    measure 'Avg Unit Price' = AVERAGE(Product[Unit Price])
        formatString: $ #,##0.00

    hierarchy 'Product Hierarchy'
        displayFolder: Hierarchies

        level Category
            column: Category

        level Subcategory
            column: Subcategory

        level 'Product Name'
            column: 'Product Name'

Calculation Groups

Time Intelligence Calculation Group

createOrReplace

table 'Time Intelligence'
    calculationGroup
        precedence: 1

    calculationItem Current = SELECTEDMEASURE()
        ordinal: 0

    calculationItem YTD =
            CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[Date]))
        ordinal: 1

    calculationItem QTD =
            CALCULATE(SELECTEDMEASURE(), DATESQTD('Calendar'[Date]))
        ordinal: 2

    calculationItem MTD =
            CALCULATE(SELECTEDMEASURE(), DATESMTD('Calendar'[Date]))
        ordinal: 3

    calculationItem PY =
            CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
        ordinal: 4

    calculationItem 'YoY %' =
            VAR CurrentValue = SELECTEDMEASURE()
            VAR PriorYear = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
            RETURN DIVIDE(CurrentValue - PriorYear, PriorYear)
        formatStringDefinition = "0.00%"
        ordinal: 5

    calculationItem 'YoY Abs' =
            VAR CurrentValue = SELECTEDMEASURE()
            VAR PriorYear = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
            RETURN CurrentValue - PriorYear
        ordinal: 6

    column 'Time Calculation'
        dataType: string
        sourceColumn: Name
        sortByColumn: Ordinal

    column Ordinal
        dataType: int64
        sourceColumn: Ordinal
        summarizeBy: none
        isHidden

Currency Conversion Calculation Group

createOrReplace

table 'Currency Conversion'
    calculationGroup
        precedence: 2

    calculationItem 'Local Currency' = SELECTEDMEASURE()
        ordinal: 0

    calculationItem USD =
            SELECTEDMEASURE() * SELECTEDVALUE('Exchange Rates'[USD Rate], 1)
        formatStringDefinition = "$ #,##0.00"
        ordinal: 1

    calculationItem EUR =
            SELECTEDMEASURE() * SELECTEDVALUE('Exchange Rates'[EUR Rate], 1)
        formatStringDefinition = "#,##0.00 EUR"
        ordinal: 2

    column 'Currency Display'
        dataType: string
        sourceColumn: Name

    column Ordinal
        dataType: int64
        sourceColumn: Ordinal
        summarizeBy: none
        isHidden

Field Parameters

````tmdl table 'Revenue Metric'

column 'Revenue Metric' dataType: string isHidden sourceColumn: Name sortByColumn: 'Revenue Metric Order'

column 'Revenue Metric Fields' dataType: string isHidden sourceColumn: Name isDataTypeInferred

column 'Revenue Metric Order' dataType: int64 isHidden sourceColumn: Ordinal summarizeBy: none

partition 'Revenue Metric-Partition' = calculated source = ``` { ("Revenue", NAMEOF([Sales Amount]), 0), ("Profit", NAMEOF([Gross Profit]), 1), ("Margin %", NAMEOF([Gross Margin %]), 2), ("Units", NAMEOF([Total Quantity]), 3) }

    annotation ParameterMetadata = ```
        {"version":3,"kind":2}

````

Roles with RLS

roles/RegionalManager.tmdl

/// Regional manager can only see their own region's data
role 'Regional Manager'
    modelPermission: read
    description: Row-level security for regional managers

    tablePermission Sales = Sales[Region] = USERPRINCIPALNAME()

    tablePermission 'Sales Targets' = 'Sales Targets'[Region] = USERPRINCIPALNAME()

    member 'alice@contoso.com'
    member 'bob@contoso.com'
    member 'regional-managers@contoso.com' = group

roles/Administrator.tmdl

role Administrator
    modelPermission: readRefresh
    description: Full read and refresh access

    member 'admin-team@contoso.com' = group
    member 'service-principal-id' = auto

Role with OLS (Object-Level Security)

role 'Limited Access'
    modelPermission: read

    tablePermission Sales = TRUE()

    columnPermission Sales.'Unit Cost' = none
    columnPermission Sales.'Profit Margin %' = none

Perspectives

perspectives/SalesView.tmdl

perspective SalesView

    perspectiveTable Sales
        perspectiveMeasure 'Sales Amount'
        perspectiveMeasure 'Total Quantity'
        perspectiveMeasure 'YoY Growth %'
        perspectiveColumn Amount
        perspectiveColumn Quantity
        perspectiveColumn 'Order Date'

    perspectiveTable Product
        perspectiveMeasure '# Products'
        perspectiveColumn 'Product Name'
        perspectiveColumn Category
        perspectiveColumn Subcategory
        perspectiveHierarchy 'Product Hierarchy'

    perspectiveTable Calendar
        perspectiveColumn Date
        perspectiveColumn Year
        perspectiveColumn 'Month Name'
        perspectiveColumn Quarter
        perspectiveHierarchy 'Date Hierarchy'

    perspectiveTable Customer
        perspectiveColumn 'Customer Name'
        perspectiveColumn Region

Cultures / Translations

cultures/en-US.tmdl

culture en-US

    linguisticMetadata =
        {
            "Version": "1.0.0",
            "Language": "en-US"
        }

cultures/pt-PT.tmdl

culture pt-PT
    translations
        model Model
            caption: Modelo
            description: Modelo de dados de vendas
        table Sales
            caption: Vendas
            measure 'Sales Amount'
                caption: Total de Vendas
                displayFolder: Receita
            measure 'Total Quantity'
                caption: Quantidade Total
                displayFolder: Volume
            column Amount
                caption: Valor
            column 'Order Date'
                caption: Data do Pedido
        table Product
            caption: Produto
            column 'Product Name'
                caption: Nome do Produto
            column Category
                caption: Categoria
            hierarchy 'Product Hierarchy'
                caption: Hierarquia de Produto
        table Calendar
            caption: Calendario
            column Date
                caption: Data
            column Year
                caption: Ano
            column 'Month Name'
                caption: Nome do Mes
        table Customer
            caption: Cliente
            column 'Customer Name'
                caption: Nome do Cliente

    linguisticMetadata =
        {
            "Version": "1.0.0",
            "Language": "pt-PT"
        }

KPI Measure

measure Revenue = SUM(Sales[Amount])
    formatString: $ #,##0.00

    kpi
        targetExpression = 1500000
        statusExpression =
            VAR x = DIVIDE([Revenue], [Revenue KPI Target])
            RETURN
                IF(x < 0.5, -1, IF(x < 0.85, 0, 1))
        trendExpression =
            VAR CurrentVal = [Revenue]
            VAR PriorVal = CALCULATE([Revenue], DATEADD('Calendar'[Date], -1, MONTH))
            RETURN
                IF(CurrentVal < PriorVal, -1, IF(CurrentVal = PriorVal, 0, 1))
        statusGraphic: "Three Symbols UnCircled Colored"
        trendGraphic: "Three Symbols UnCircled Colored"

DirectQuery Table

table 'Live Sales'
    lineageTag: 55555555-aaaa-bbbb-cccc-dddddddddddd

    partition 'Live-Sales-DQ' = m
        mode: directQuery
        source =
            let
                Source = Sql.Database("live-server.database.windows.net", "SalesDB"),
                Sales = Source{[Schema="dbo",Item="vw_LiveSales"]}[Data]
            in
                Sales

    column OrderID
        dataType: int64
        sourceColumn: OrderID
        summarizeBy: none

    column Amount
        dataType: decimal
        formatString: $ #,##0.00
        sourceColumn: Amount
        summarizeBy: sum

Dual Mode Table (Composite Model)

table 'Product Lookup'

    partition 'Product-Dual' = m
        mode: dual
        source =
            let
                Source = Sql.Database(Server, Database),
                Result = Source{[Schema="dbo",Item="DimProduct"]}[Data]
            in
                Result

    column ProductKey
        dataType: int64
        isKey
        sourceColumn: ProductKey
        summarizeBy: none

Incremental Refresh Configuration

table Sales

    partition 'Sales-Incremental' = m
        mode: import
        source =
            let
                Source = Sql.Database(Server, Database),
                Filtered = Table.SelectRows(
                    Source{[Schema="dbo",Item="FactSales"]}[Data],
                    each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
                )
            in
                Filtered

        refreshPolicy
            incrementalGranularity: day
            incrementalPeriods: 30
            rollingWindowGranularity: month
            rollingWindowPeriods: 36
            pollingExpression =
                let
                    Source = Sql.Database(Server, Database),
                    MaxDate = List.Max(Source{[Schema="dbo",Item="FactSales"]}[Data][OrderDate])
                in
                    MaxDate
            sourceExpression =
                let
                    Source = Sql.Database(Server, Database),
                    Filtered = Table.SelectRows(
                        Source{[Schema="dbo",Item="FactSales"]}[Data],
                        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
                    )
                in
                    Filtered

createOrReplace Script Examples

Add Measures to Existing Table

createOrReplace

    ref table Sales
        measure 'New KPI' =
                VAR Target = 1000000
                VAR Actual = [Sales Amount]
                RETURN DIVIDE(Actual, Target)
            formatString: 0.0%
            displayFolder: KPIs

        measure 'Filtered Sales' =
                CALCULATE([Sales Amount], Product[Category] = "Electronics")
            formatString: $ #,##0.00
            displayFolder: Revenue

Replace Full Table Definition

createOrReplace

    table 'Exchange Rates'

        partition 'Exchange-Rates-Part' = m
            mode: import
            source =
                let
                    Source = Web.Contents("https://api.exchangerates.io/latest"),
                    Data = Json.Document(Source)
                in
                    Record.ToTable(Data[rates])

        column Name
            dataType: string
            sourceColumn: Name
            summarizeBy: none

        column Value
            dataType: double
            sourceColumn: Value
            summarizeBy: none

Create Perspective via Script

createOrReplace

    perspective 'Executive Summary'

        perspectiveTable Sales
            perspectiveMeasure 'Sales Amount'
            perspectiveMeasure 'YoY Growth %'
            perspectiveMeasure 'Sales YTD'

        perspectiveTable Calendar
            perspectiveColumn Year
            perspectiveColumn Quarter
            perspectiveHierarchy 'Date Hierarchy'

Switch Storage Mode via Script

createOrReplace

    table Product

        partition 'Product-Partition' = m
            mode: directQuery
            source =
                let
                    Source = Sql.Database(Server, Database),
                    Product = Source{[Schema="dbo",Item="DimProduct"]}[Data]
                in
                    Product

        column 'Product Key'
            dataType: int64
            isKey
            sourceColumn: ProductKey
            summarizeBy: none

        column 'Product Name'
            dataType: string
            sourceColumn: ProductName
            summarizeBy: none

TMDL / TOM Scripting from Python (Fabric Notebooks)

As of 2026, the canonical Python path for scripting semantic models is the semantic-link-labs library (formerly sempy_labs), which runs natively in Microsoft Fabric notebooks and wraps the Tabular Object Model (TOM) in Pythonic context managers. It eliminates the need to manually import .NET assemblies via pythonnet (although that still works) and handles authentication implicitly when executed inside a Fabric workspace.

This reference shows complete, working recipes for every common TMDL/TOM authoring task from a Fabric Python notebook.

Installation and Imports

%pip install semantic-link-labs -q

import sempy.fabric as fabric
import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model

Inside a Fabric notebook, semantic-link is preinstalled (Spark 3.4+). Only semantic-link-labs needs the %pip install. Both packages are published by Microsoft.

Connecting and the TOM Wrapper

connect_semantic_model is a context manager that:

1. Opens a TOM connection to the named semantic model in the workspace 2. Exposes a TOMWrapper instance (referred to below as tom) with shortcut methods 3. Calls SaveChanges() on context exit unless readonly=True

with connect_semantic_model(
    dataset="SalesModel",
    workspace="Sales-Dev",
    readonly=False,
) as tom:
    # scripting goes here
    pass
# Changes are saved automatically on exit

Pass readonly=True when you only want to inspect the model (e.g., list measures, export TMDL) without risking mutation.

Adding and Updating Measures

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    # Simple measure
    tom.add_measure(
        table_name="Sales",
        measure_name="Sales Amount",
        expression="SUM(Sales[Amount])",
        format_string="$ #,##0.00",
        display_folder="Revenue",
        description="Total sales revenue",
    )

    # YoY measure with multi-line DAX
    yoy_dax = """
    VAR CurrentSales = [Sales Amount]
    VAR PYSales = CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Calendar'[Date]))
    RETURN DIVIDE(CurrentSales - PYSales, PYSales)
    """
    tom.add_measure(
        table_name="Sales",
        measure_name="YoY Growth %",
        expression=yoy_dax,
        format_string="0.00%",
        display_folder="Growth",
    )

    # Update an existing measure
    measure = tom.model.Tables["Sales"].Measures["Sales Amount"]
    measure.Expression = "SUMX(Sales, Sales[Quantity] * Sales[Unit Price])"
    measure.Description = "Recalculated from quantity * unit price"

Adding Calculation Groups

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    tom.add_calculation_group(
        name="Time Intelligence",
        precedence=1,
        description="Time intelligence calc group",
    )

    tom.add_calculation_item(
        table_name="Time Intelligence",
        calculation_item_name="Current",
        expression="SELECTEDMEASURE()",
        ordinal=0,
    )

    tom.add_calculation_item(
        table_name="Time Intelligence",
        calculation_item_name="YTD",
        expression="CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[Date]))",
        ordinal=1,
    )

    tom.add_calculation_item(
        table_name="Time Intelligence",
        calculation_item_name="PY",
        expression="CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))",
        ordinal=2,
    )

    tom.add_calculation_item(
        table_name="Time Intelligence",
        calculation_item_name="YoY %",
        expression="""
            VAR C = SELECTEDMEASURE()
            VAR P = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
            RETURN DIVIDE(C - P, P)
        """,
        format_string_expression='"0.00%"',
        ordinal=3,
    )

Incremental Refresh Policy

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Prod") as tom:
    tom.add_incremental_refresh_policy(
        table_name="Sales",
        column_name="OrderDate",
        start_date="2020-01-01T00:00:00",
        end_date="2025-12-31T00:00:00",
        incremental_granularity="Day",
        incremental_periods=30,
        rolling_window_granularity="Month",
        rolling_window_periods=36,
        only_refresh_complete_days=True,
        detect_data_changes_column=None,
    )

    # Inspect the result
    print(tom.model.Tables["Sales"].RefreshPolicy)

Row-Level Security (RLS)

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    tom.add_role(role_name="RegionalManager", description="RLS for regional managers")

    tom.set_rls(
        role_name="RegionalManager",
        table_name="Sales",
        filter_expression="Sales[Region] = USERPRINCIPALNAME()",
    )

    tom.set_rls(
        role_name="RegionalManager",
        table_name="Sales Targets",
        filter_expression="'Sales Targets'[Region] = USERPRINCIPALNAME()",
    )

Role members (users/groups) are managed at the dataset-permission level, not via TOM. Use the fabric REST API to add users to roles:

fabric.add_user_to_role(
    workspace="Sales-Dev",
    dataset="SalesModel",
    role="RegionalManager",
    user_principal_name="alice@contoso.com",
)

Object-Level Security (OLS)

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    tom.set_ols(
        role_name="RegionalManager",
        table_name="Sales",
        column_name="Unit Cost",
        permission="None",  # "None" = hide column from role
    )

Adding Relationships

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    tom.add_relationship(
        from_table="Sales",
        from_column="Product Key",
        to_table="Product",
        to_column="Product Key",
        from_cardinality="Many",
        to_cardinality="One",
        cross_filtering_behavior="OneDirection",
        is_active=True,
    )

    # Bidirectional bridge
    tom.add_relationship(
        from_table="Bridge Table",
        from_column="Tag Id",
        to_table="Tags",
        to_column="Tag Id",
        from_cardinality="Many",
        to_cardinality="Many",
        cross_filtering_behavior="BothDirections",
    )

Direct Lake Connections

with connect_semantic_model(dataset="DirectLakeModel", workspace="Analytics-Prod") as tom:
    # Switch Direct Lake fallback behavior
    tom.set_direct_lake_behavior(direct_lake_behavior="Automatic")
    # Options: "Automatic", "DirectLakeOnly", "DirectQueryOnly"

Or update the Direct Lake source at the model level:

labs.update_direct_lake_model_connection(
    dataset="DirectLakeModel",
    workspace="Analytics-Prod",
    source_type="Lakehouse",  # or "Warehouse"
    source_workspace="Data-Platform",
    source=".Lakehouse",
)

Best Practice Analyzer (BPA)

# Run the Microsoft BPA ruleset against a model
bpa_results = labs.run_model_bpa(
    dataset="SalesModel",
    workspace="Sales-Dev",
    export=False,
    language="en-US",
)
display(bpa_results)

# Report-level BPA
report_bpa = labs.run_report_bpa(
    report="SalesReport",
    workspace="Sales-Dev",
)
display(report_bpa)

run_model_bpa produces a pandas DataFrame listing each BPA rule violation with rule name, severity, object, and description -- ideal for CI gates inside a notebook-based pipeline.

Export / Import Model as TMDL

# Export a live workspace model to a TMDL folder in the Fabric notebook attached Lakehouse
labs.export_model_to_tmdl(
    dataset="SalesModel",
    workspace="Sales-Dev",
    path="/lakehouse/default/Files/tmdl_export/SalesModel",
)

# Deploy a TMDL folder back into a target workspace
labs.deploy_semantic_model(
    source_dataset="SalesModel",
    source_workspace="Sales-Dev",
    target_dataset="SalesModel",
    target_workspace="Sales-Prod",
    refresh_target_dataset=False,
)

Reading Model Metadata with DAX INFO Functions

For read-only model introspection (listing measures, columns, relationships), prefer DAX INFO functions via fabric.evaluate_dax over TOM:

import sempy.fabric as fabric

measures_df = fabric.evaluate_dax(
    dataset="SalesModel",
    workspace="Sales-Dev",
    dax_string="EVALUATE INFO.MEASURES()",
)
display(measures_df)

columns_df = fabric.evaluate_dax(
    dataset="SalesModel",
    workspace="Sales-Dev",
    dax_string="EVALUATE INFO.COLUMNS()",
)
display(columns_df)

INFO functions (INFO.MEASURES, INFO.COLUMNS, INFO.TABLES, INFO.RELATIONSHIPS, INFO.CALCULATIONGROUPS, INFO.CALCULATIONITEMS, INFO.ROLES, etc.) are the 2026-preferred alternative to DMV queries and are fully supported in Power BI, Fabric, AAS, and SQL 2025 AS.

Low-Level: Raw TOM via pythonnet (Fallback)

When semantic-link-labs does not expose a needed TOM property, drop down to raw TOM inside the same context:

with connect_semantic_model(dataset="SalesModel", workspace="Sales-Dev") as tom:
    # tom.model is the raw Microsoft.AnalysisServices.Tabular.Model
    from Microsoft.AnalysisServices.Tabular import Annotation

    annotation = Annotation()
    annotation.Name = "CustomMetadata"
    annotation.Value = '{"owner":"data-team","refreshSchedule":"daily"}'
    tom.model.Tables["Sales"].Annotations.Add(annotation)

All TOM classes under Microsoft.AnalysisServices.Tabular are reachable because semantic-link-labs has already loaded the assemblies.

CI Gate Pattern: BPA Failure Blocks Deployment

import sys

results = labs.run_model_bpa(
    dataset="SalesModel",
    workspace="Sales-Dev",
    language="en-US",
)

critical = results[results["Severity"] == "Error"]
if len(critical) > 0:
    display(critical)
    raise RuntimeError(f"BPA blocked deployment: {len(critical)} Error-severity violations")

# Proceed with deployment
labs.deploy_semantic_model(
    source_dataset="SalesModel",
    source_workspace="Sales-Dev",
    target_dataset="SalesModel",
    target_workspace="Sales-Prod",
)

Run this notebook on a schedule or from a pipeline job (runMultiple / Fabric Data Pipeline -> Notebook activity) to gate deployments on BPA.

Gotchas

References

• semantic-link-labs on GitHub -- source and release notes
• semantic-link-labs on PyPI -- latest version
• sempy.fabric package reference -- lower-level SemPy API
• Semantic link overview

TMDL Complete Syntax and Grammar Reference

Object Type Hierarchy

TMDL exposes the entire TOM Database object tree (except Server). Every TMDL object maps 1:1 to a TOM class in Microsoft.AnalysisServices.Tabular.

Top-Level Objects (No Indentation Required)

Table Child Objects (Indented Under Table)

Role Child Objects

Perspective Child Objects

Culture Child Objects

Complete Property Reference by Object Type

Database Properties

database AdventureWorks
    compatibilityLevel: 1601
    id: AdventureWorks-GUID

Model Properties

model Model
    culture: en-US
    defaultPowerBIDataSourceVersion: powerBI_V3
    discourageImplicitMeasures
    sourceQueryCulture: en-US

Table Properties

table Sales
    lineageTag: a1b2c3d4-...
    isHidden
    isPrivate
    excludeFromModelRefresh
    dataCategory: Time
    description: Sales transactions table

Column Properties (DataColumn)

column Amount
    dataType: decimal
    formatString: $ #,##0.00
    sourceColumn: Amount
    summarizeBy: sum
    isHidden
    isKey
    isNullable
    isDefaultLabel
    isDefaultImage
    isAvailableInMdx: false
    isUnique
    sortByColumn: 'Amount Sort'
    displayFolder: Financial
    lineageTag: e5f6g7h8-...
    dataCategory: Uncategorized

Calculated Column Properties

column 'Full Name' = [FirstName] & " " & [LastName]
    dataType: string
    lineageTag: ...
    summarizeBy: none
    isDataTypeInferred

Inherits all DataColumn properties plus:

Measure Properties

measure 'Total Sales' = SUM(Sales[Amount])
    formatString: $ #,##0.00
    displayFolder: Revenue
    lineageTag: ...
    isHidden
    description: Total sales amount

Partition Properties

/// M (Power Query) partition
partition 'Sales-Partition' = m
    mode: import
    source =
        let
            Source = Sql.Database("server", "db"),
            Sales = Source{[Schema="dbo",Item="Sales"]}[Data]
        in
            Sales

/// Calculated partition
partition 'CalcTable-Partition' = calculated
    source =
        CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31))

/// Direct Query partition
partition 'DQ-Partition' = m
    mode: directQuery
    source =
        let
            Source = Sql.Database("server", "db"),
            Result = Source{[Schema="dbo",Item="Sales"]}[Data]
        in
            Result

Relationship Properties

relationship 550e8400-e29b-41d4-a716-446655440000
    fromColumn: Sales.'Product Key'
    toColumn: Product.'Product Key'
    crossFilteringBehavior: oneDirection
    fromCardinality: many
    toCardinality: one
    isActive
    securityFilteringBehavior: oneDirection
    joinOnDateBehavior: datePartOnly
    relyOnReferentialIntegrity

Role Properties

role 'Regional Manager'
    modelPermission: read
    description: Access to regional sales data only

    tablePermission Sales = Sales[Region] = USERPRINCIPALNAME()
    tablePermission Product = TRUE()

    member 'user@company.com'
    member 'group@company.com' = group
    member 'serviceaccount@company.com' = auto
    member DOMAIN\user1 = activeDirectory

Member type values: user (default, Azure AD user), group (Azure AD group), auto (Azure AD auto-detect), activeDirectory (Windows AD)

Hierarchy Properties

hierarchy 'Product Hierarchy'
    lineageTag: ...
    displayFolder: Hierarchies

    level Category
        column: Category
        lineageTag: ...

    level Subcategory
        column: Subcategory

    level Product
        column: 'Product Name'

Calculation Group Properties

table 'Time Intelligence'
    calculationGroup
        precedence: 1
        multipleOrEmptySelectionExpression =
                SELECTEDMEASURE()
        noSelectionExpression =
                SELECTEDMEASURE()

    calculationItem Current = SELECTEDMEASURE()

    calculationItem YTD =
            CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
        formatStringDefinition = "#,##0.00"
        ordinal: 1

    column 'Time Calc'
        dataType: string
        sourceColumn: Name
        sortByColumn: Ordinal

    column Ordinal
        dataType: int64
        sourceColumn: Ordinal
        summarizeBy: none

Perspective Definition

perspective SalesAnalysis

    perspectiveTable Sales
        perspectiveMeasure 'Sales Amount'
        perspectiveMeasure 'Total Quantity'
        perspectiveColumn Amount
        perspectiveColumn 'Order Date'
        perspectiveHierarchy 'Date Hierarchy'

    perspectiveTable Product
        perspectiveColumn Category
        perspectiveColumn 'Product Name'

Culture / Translation Definition

culture pt-PT
    translations
        model Model
            caption: Modelo
        table Sales
            caption: Vendas
            measure 'Sales Amount'
                caption: Total de Vendas
                displayFolder: Metricas Base
            column Amount
                caption: Valor
        table Product
            caption: Produto
            column Category
                caption: Categoria
            hierarchy 'Product Hierarchy'
                caption: Hierarquia de Produto

    linguisticMetadata =
        {
            "Version": "1.0.0",
            "Language": "pt-PT"
        }

Translation properties per object: caption, description, displayFolder.

Named Expressions (Power Query Parameters)

expression Server = "localhost" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression Database = "AdventureWorks" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression 'Shared Query' =
        let
            Source = Sql.Database(Server, Database),
            Result = Source{[Schema="dbo",Item="DimDate"]}[Data]
        in
            Result
    queryGroup: 'Shared Queries'

Annotations and Extended Properties

````tmdl table Sales

annotation PBI_ResultType = Table annotation PBI_NavigationStepName = Navigation

annotation CustomMetadata = ``` { "owner": "data-team", "refreshSchedule": "daily" }

    extendedProperty DataAccessOptions = ```
        {
            "LegacyRedirects": true
        }

````

Annotations use Value (Text) as default property. JSON extended properties use Value (JSON) as default property. Both support single-line and triple-backtick multi-line values.

KPI Properties on Measures

measure 'Revenue' = SUM(Sales[Amount])
    formatString: $ #,##0

    kpi
        targetExpression = 1000000
        statusExpression =
            var x = [Revenue] / [Revenue Goal]
            return
                if(x < 0.4, -1, if(x < 0.8, 0, 1))
        trendExpression =
            var x = [Revenue] / CALCULATE([Revenue], DATEADD('Date'[Date], -1, YEAR))
            return
                if(x < 1, -1, if(x = 1, 0, 1))
        statusGraphic: "Three Symbols UnCircled Colored"
        trendGraphic: "Three Symbols UnCircled Colored"

Format String Definition (Dynamic Format Strings)

measure 'Sales Amount' = SUM(Sales[Amount])

    formatStringDefinition = IF(SELECTEDVALUE(Currency[Code]) = "EUR", "#,##0.00 EUR", "$ #,##0.00")

This enables context-dependent formatting while preserving the numeric data type.

Expression Language Mapping

Partial Declarations

TMDL supports splitting object definitions across multiple files (similar to C# partial classes). A table can be declared in its own file, and additional measures for that table can be declared in a separate file:

/// In measures.tmdl -- additional measures for existing tables
table Sales

    measure 'Sales Amount' = SUM(Sales[Amount])
        formatString: $ #,##0

table Product

    measure '# Products' = COUNTROWS(Product)
        formatString: #,##0

The same property cannot be declared twice across files -- this produces a deserialization error.

Reserved Keywords

All TOM object type names are reserved: model, database, table, column, measure, partition, relationship, role, perspective, culture, expression, function, hierarchy, level, annotation, extendedProperty, calculationGroup, calculationItem, dataSource, member, tablePermission, columnPermission, perspectiveTable, perspectiveMeasure, perspectiveColumn, perspectiveHierarchy, kpi, ref, createOrReplace.

Object names matching reserved keywords must be enclosed in single quotes.