physical-model-tmdl

name	physical-model-tmdl
description	Use when the task involves generating or editing TMDL files for a Power BI semantic model. Triggers: "create TMDL model", "add a column", "define table in TMDL", "generate semantic model", "write TMDL", "add partition", "fix sourceColumn", "TMDL indentation", "TMDL syntax error", "create relationships.tmdl", "define expressions.tmdl", "field parameter table", "generate physical model from ER diagram", "TMDL file structure".
user-invocable	true

Skill: Physical Model & TMDL Development

Prerequisites — MANDATORY

Before writing ANY TMDL code:

1. Read .github/skills/physical-model-tmdl/references/tmdl-syntax-reference.md — validated syntax templates and indentation rules.
2. Read .github/references/naming-conventions.md — naming rules for all objects.
3. Read .github/references/pbip-folder-structure.md — defines output folder structure.
4. Consult .github/skills/physical-model-tmdl/references/column-properties.md — column property values, summarizeBy rules, formatString patterns.
5. Consult .github/skills/physical-model-tmdl/references/object-properties.md — full property reference for all TMDL object types.
6. Consult .github/skills/physical-model-tmdl/references/tmdl-examples.md — curated real-world TMDL patterns.
7. If (and ONLY if) the specification includes Row-Level Security requirements, read .github/references/security-rls-best-practices.md.
8. Verify any uncertain syntax using microsoft_docs_search MCP tool with query: "TMDL <object_type> definition syntax".

Input / Output

Input	<ProjectName>/spec/requirements_summary.md, <ProjectName>/spec/er_diagram.md
Output	TMDL files in <ProjectName>/PBIP/<ProjectName>.SemanticModel/definition/

Pre-DAX Critical Clarification Hard Stop (MANDATORY)

Before closing this skill (and before any transition to DAX development), verify that all critical clarifications are resolved:

1. Time/period semantics that affect cumulative or comparative calculations.
2. Numeric threshold/classification semantics used by status-style KPIs.
3. Grain reconciliation semantics when combining measures at different detail levels.

Blocking policy:

• If any item is unresolved, DO NOT proceed to DAX development.
• Ask targeted clarification questions and keep the task pending.
• If the user accepts temporary assumptions, persist explicit assumption approvals in decisionLedger and annotate impacted TMDL artifacts with the assumption note in step-level metadata/reporting.

TMDL Syntax Critical Rules

TMDL is whitespace-sensitive. Violations cause Power BI Desktop parsing failures.

Comments and Descriptions

• /// (triple-slash): Sets the Description property on the next declaration (measure, column, table, hierarchy). Must be immediately followed by the declaration — no blank lines between.
• // (double-slash): Regular comment with no semantic effect. Supported in TMDL structure and inside DAX expressions.
• DAX expressions: // single-line and /* */ block comments fully supported.
• Block comments (/* */): NOT supported outside DAX expressions.

Use /// to add descriptions to tables, columns, and measures:

/// Total sales amount in local currency, fiscal year-to-date.
measure 'Sales Amount FYTD' =
		TOTALYTD ( [Sales Amount], Dim_Date[Date], "6/30" )
	formatString: #,##0.00
	lineageTag: abc-123

Indentation

• Use single TAB for each indentation level. Do NOT use spaces.
• Level 1: Object declaration (table, relationship) — NO indentation (root level).
• Level 2: Object properties — ONE tab indent.
• Level 3: Multi-line expressions — TWO tabs indent.

Object Hierarchy (no indentation required at root)

These objects are root-level (no indentation):

• model, table, relationship, expression, role, culture, perspective, database

Property Delimiters

• Colon (:) for non-expression properties: dataType: int64
• Equals (=) for expressions and default properties: measure Sales = SUM(...)

Naming

• Object names with spaces, dots, equals, or colons MUST be enclosed in single quotes: 'Sales Amount'
• Single quotes in names are escaped by doubling: 'Customer''s Name'

⚠️ _Measures Table Partition — Use M Empty Table, NOT calculated

The _Measures disconnected table must use an M partition pointing to an empty table. Using partition _Measures = calculated with source = "" is invalid and will cause a model load error in Power BI Desktop.

✅ CORRECT:

	partition _Measures = m
		mode: import
		source =
			```
			let
			    Source = #table(type table [_dummy = type text], {})
			in
			    Source
			```

❌ WRONG (causes error):

	partition _Measures = calculated
		mode: import
		source = ""

⚠️ TMDL File Encoding — NO BOM (UTF-8 without BOM)

TMDL files must be UTF-8 without BOM. Power BI Desktop will fail to parse files that start with the UTF-8 BOM sequence (0xEF 0xBB 0xBF).

PowerShell — write without BOM:

# CORRECT — explicit no-BOM UTF-8 encoder
$enc = New-Object System.Text.UTF8Encoding($false)
[System.IO.File]::WriteAllText($path, $content, $enc)

# WRONG — [System.Text.Encoding]::UTF8 includes BOM
[System.IO.File]::WriteAllText($path, $content, [System.Text.Encoding]::UTF8)

Strip BOM from existing files:

$files = Get-ChildItem $tmdlDir -Recurse -Filter "*.tmdl"
foreach ($f in $files) {
    $bytes = [System.IO.File]::ReadAllBytes($f.FullName)
    if ($bytes[0] -eq 0xEF -and $bytes[1] -eq 0xBB -and $bytes[2] -eq 0xBF) {
        [System.IO.File]::WriteAllBytes($f.FullName, $bytes[3..($bytes.Length-1)])
    }
}

Python — always safe:

with open(path, 'w', encoding='utf-8') as f:  # no BOM by default
    f.write(content)

⛔ CRITICAL: Ambiguous Path Prevention

BEFORE generating relationships.tmdl, verify that your logical model does NOT have redundant Foreign Keys that would create ambiguous paths.

Check for ambiguity:

1. List ALL Foreign Keys in each Fact table
2. For each FK, trace the relationship chain to see which dimensions it connects to
3. If TWO different FKs lead to the SAME dimension through different paths, you have an ambiguity

Example: If Fact_Sales has:

• CustomerKey → Dim_Customer.CustomerKey
• CountryKey → Dim_Country.CountryKey

AND Dim_Customer has:

• CountryKey → Dim_Country.CountryKey

Then you have TWO paths from Fact_Sales to Dim_Country:

• Path A: Fact_Sales.CountryKey → Dim_Country (direct)
• Path B: Fact_Sales.CustomerKey → Dim_Customer → Dim_Country (indirect)

Power BI will REJECT this model with error:

There are ambiguous paths between 'Fact_Sales' and 'Dim_Country'

Solution: Remove the direct relationship Fact_Sales.CountryKey → Dim_Country. Keep only the path through Dim_Customer.

Rule: A fact table should connect to the most granular dimension in a hierarchy, NOT to every level of the hierarchy.

⚠️ MANDATORY CHECKPOINT: Refresh Strategy Confirmation

BEFORE generating ANY TMDL partition definitions, the agent MUST verify that the functional specification contains the following information:

1. Data Refresh Frequency (e.g., Real-time, Hourly, Daily, Weekly)
2. Storage Mode Preference (Import, DirectQuery, Composite)
3. Expected Data Volumes (current and projected row counts for fact tables)
4. Incremental Refresh Requirements (yes/no + audit field name if applicable)
5. Source System Update Pattern (Append-only, Updates in place, Soft deletes, Hard deletes)

If ANY of these are missing or unclear:

• 🛑 STOP execution
• 📋 Ask the user to provide the missing information
• 💡 Provide guidance based on typical patterns:
  • Small datasets (< 1M rows): Import mode with full refresh
  • Medium datasets (1M-10M rows): Import mode with incremental refresh
  • Large datasets (> 10M rows): Consider DirectQuery or Composite mode
  • Real-time requirements: DirectQuery or Composite mode
  • Hourly/Daily refresh: Import mode typically sufficient

Example Questions to Ask User:

Missing Refresh Strategy Information:

I need the following details to configure the physical model correctly:

1. **Data Refresh Frequency**: How often should the report data be updated?
   - Real-time (< 1 minute latency)
   - Near real-time (5-15 minutes)
   - Hourly
   - Multiple times per day
   - Daily
   - Weekly

2. **Expected Data Volumes**: What are the current and projected row counts for fact tables?
   - Fact_Sales: ___ rows (current), ___ rows (12 months projection)

3. **Incremental Refresh**: Do any fact tables support incremental refresh?
   - If yes, what field tracks last modification? (e.g., LastModifiedDate, TransactionDate)

4. **Storage Mode**: What is your preference?
   - Import (best performance, scheduled refresh)
   - DirectQuery (real-time, slower queries)
   - Composite (hybrid approach)
   - Undecided (let me recommend based on requirements)

Please update your specification file (Section 9.1 - Data Refresh Strategy) with these details.

Data Refresh Strategy & Storage Mode Selection

Decision Tree: Import vs DirectQuery vs Composite

Use Import Mode when:

• ✅ Data refresh frequency is hourly or slower
• ✅ Data volumes are manageable (< 10GB compressed)
• ✅ Query performance is critical
• ✅ Source system cannot handle concurrent query load
• ✅ Complex DAX calculations required

Use DirectQuery when:

• ✅ Real-time data required (< 5 minute latency)
• ✅ Data volumes exceed Power BI Import limits (> 10GB)
• ✅ Source system is optimized for analytical queries (e.g., Azure Synapse, SQL Server columnstore)
• ✅ Single Source of Truth enforcement required
• ⚠️ Accept slower query performance
• ⚠️ Limited DAX function support

Use Composite/Hybrid when:

• ✅ Dimensions are small (Import) but facts are large (DirectQuery)
• ✅ Recent data needs real-time refresh, historical data is static
• ✅ Balancing performance and freshness
• ⚠️ More complex to configure and troubleshoot

Incremental Refresh Configuration

When to use Incremental Refresh:

• Fact tables with > 1M rows
• Import mode with daily/hourly refresh
• Source system supports Last Modified Date tracking
• Historical data rarely changes (append-only or updates within recent window)

Requirements:

1. Audit Field: A DateTime or Date column that tracks when each row was last modified
   • Common names: LastModifiedDate, TransactionDate, CreatedDate, UpdatedTimestamp
2. Power Query Parameters: RangeStart and RangeEnd (generated automatically)
3. Partition Strategy: Define refresh window (e.g., "Last 7 days") and archive window (e.g., "Keep last 3 years")

Benefits:

• Only recent data is refreshed (faster refresh times)
• Historical data compressed and cached (better performance)
• Reduces source system load

File Generation Rules

Generate the following TMDL files inside <ProjectName>/PBIP/<ProjectName>.SemanticModel/definition/:

1. database.tmdl

CRITICAL: The compatibilityLevel MUST match the Power BI Desktop version being used. Using an incorrect compatibility level will cause one of two errors:

• Downgrade error: If the TMDL file specifies a lower level than what Power BI Desktop already created
• Upgrade error: If the TMDL file requires features not supported by the installed Power BI Desktop version

How to determine the correct compatibilityLevel:

Power BI Desktop Version	Release Date	CompatibilityLevel	Notes
December 2025 (2.150.x)	Dec 2025	1600	Current version
September 2024 (2.133.x)	Sep 2024	1567	Legacy version
June 2024 (2.130.x)	Jun 2024	1550	Older version

Rule: When generating a new model, ALWAYS use the compatibility level matching the installed Power BI Desktop version. If uncertain, use 1600 for December 2025 and later.

database <ProjectName>
	compatibilityLevel: 1600

2. model.tmdl

CRITICAL: The defaultPowerBIDataSourceVersion: powerBI_V3 property is MANDATORY. Without it, Power BI Desktop (December 2025+) throws "A data model with version 3 of metadata is required" and cascading null-query errors on every refresh attempt.

CRITICAL: Auto date/time MUST remain disabled. The agent MUST NOT enable or preserve annotation __PBI_TimeIntelligenceEnabled = 1, MUST NOT add ref table LocalDateTable_* entries in model.tmdl, and MUST NOT manually author Desktop-managed metadata artifacts with autogenerated names. The semantic model MUST use only the explicit project date dimension for time intelligence.

CRITICAL: If the model uses a dedicated date dimension, mark it as the official Date Table only after the table has been populated and validated in Power BI Desktop. Do NOT attempt to persist the Date Table marking on an empty or not-yet-loaded date table.

CRITICAL: Power BI Desktop can create additional system-managed metadata during save when Date Table settings are persisted. These files may use autogenerated names and are not guaranteed to remain visible as stable project artifacts after save. Leave enough path budget for such Desktop-generated files.

model Model
	culture: en-US
	defaultPowerBIDataSourceVersion: powerBI_V3

ref table Dim_Date
ref table Dim_Customer
ref table Fact_Sales
ref table _Measures

3. tables/<TableName>.tmdl (one file per table)

Dimension Table Template:

Note: This example shows a Date dimension table.

table Dim_Date
	lineageTag: <generate-guid>

	column DateKey
		dataType: int64
		isKey
		isHidden
		sourceColumn: DateKey
		summarizeBy: none
		lineageTag: <generate-guid>

	column Date
		dataType: dateTime
		formatString: yyyy-MM-dd
		sourceColumn: Date
		summarizeBy: none
		lineageTag: <generate-guid>

	column Year
		dataType: string
		sourceColumn: Year
		summarizeBy: none
		lineageTag: <generate-guid>

	partition Dim_Date = m
		mode: import
		source =
			let
				Source = Csv.Document(File.Contents("<absolute-path-to-data>/dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
				PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
				ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"DateKey", Int64.Type}, {"Date", type datetime}})
			in
				ChangedTypes

Fact Table Template:

Note: This example shows a Sales fact table at daily grain per customer. Do NOT add comments in actual TMDL files.

table Fact_Sales
	lineageTag: <generate-guid>

	column SalesKey
		dataType: int64
		isHidden
		sourceColumn: SalesKey
		summarizeBy: none
		lineageTag: <generate-guid>

	column DateKey
		dataType: int64
		isHidden
		sourceColumn: DateKey
		summarizeBy: none
		lineageTag: <generate-guid>

	column CustomerKey
		dataType: int64
		isHidden
		sourceColumn: CustomerKey
		summarizeBy: none
		lineageTag: <generate-guid>