tmdl-mastery

TMDL (Tabular Model Definition Language) Mastery

Overview

Complete TMDL reference covering language syntax, folder structure, object types, expressions, serialization API, CI/CD integration, and deployment patterns. TMDL is the human-readable, source-control-friendly format for defining Power BI and Analysis Services semantic models at compatibility level 1200+. GA since 2025 and supported in Power BI Desktop, Fabric, Azure Analysis Services, and SQL Server 2025 Analysis Services.

TMDL vs TMSL vs BIM

Aspect	TMDL (.tmdl folder)	TMSL / BIM (model.bim)
Format	YAML-like text, indentation-based	Single JSON file
Files	One file per table, role, culture, perspective	One monolithic file
Git friendliness	Excellent -- granular diffs, minimal merge conflicts	Poor -- entire model in one diff
Human readability	High -- minimal delimiters, DAX/M inline	Low -- escaped JSON strings
Tooling	VS Code extension, TMDL view in Desktop, Tabular Editor 3	Any JSON editor, Tabular Editor 2/3
API	TmdlSerializer (.NET)	JsonSerializer (.NET), TMSL commands
Migration	Can convert from BIM via Tabular Editor or Desktop	Default legacy format

When to use TMDL: Any new project requiring source control, CI/CD, or team collaboration. Prefer TMDL for all PBIP projects.

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

Object Type	Default Property	Language
measure	Expression	DAX
calculatedColumn	Expression	DAX
calculationItem	Expression	DAX
partition (M)	Expression	M
partition (calculated)	Expression	DAX
tablePermission	FilterExpression	DAX
namedExpression	Expression	M
annotation	Value	Text
jsonExtendedProperty	Value	JSON

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

Expressions -- Single-Line and Multi-Line

/// 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 must be indented one level deeper than parent object properties
• Trailing blank lines and whitespace are stripped (unless using triple-backtick blocks)
• Triple-backtick enclosing preserves exact 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 directly above an object become its TOM Description property. No whitespace allowed between the description block and the 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.

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