physical-model-tmdl

# 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: ```tmdl /// 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:** ```tmdl partition _Measures = m mode: import source = ``` let Source = #table(type table [_dummy = type text], {}) in Source ``` ``` **❌ WRONG (causes error):** ```tmdl 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:** ```powershell # 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:** ```powershell $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:** ```python 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. ```tmdl 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. ```tmdl 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. ```tmdl 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. ```tmdl 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>