{"id":1654,"date":"2026-08-14T13:20:32","date_gmt":"2026-08-14T11:20:32","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1654"},"modified":"2026-08-14T13:20:32","modified_gmt":"2026-08-14T11:20:32","slug":"partitioning-power-bi-data-models-with-tmdl","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1654","title":{"rendered":"Partitioning Power BI data models with TMDL<\/strong>"},"content":{"rendered":"\n

In one of my previous articles on partitioning, I described how Power BI data models can be partitioned using the XMLA endpoint. One limitation of that approach was that tables could only be partitioned using SQL Server Management Studio. In addition, the partitions had to be recreated completely after redeploying the model, which meant that the model always had to be downloaded before changes could be made. This still applies if you are working with an outdated version. However, changes can now also be made online. Therefore, when using the online portal, you should always download the latest version before making changes to it. With this new approach, partitions can be created using TMDL both in the desktop application and in the online portal, without requiring additional software. Nevertheless, I recommend using SQL Server Management Studio.<\/p>\n\n\n\n

In this article, I will explain how this can be implemented from a technical perspective. You can then decide which approach you want to use and which tools you need.<\/p>\n\n\n\n

First, I created a table that initially contains random values for two years, 2006 and 2007:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

I initially imported this table into the Power BI data model without applying a filter. I then switched to the TMDL view in Power BI Desktop, where the partitions can be created. Until now, I was completely unaware that indentation also matters when developing with TMDL. This is also the case with YAML. I am not particularly enthusiastic about it, but if it is required, that is fine.<\/p>\n\n\n\n

I then used this code to create the first partition in the TMDL view. Because a CI\/CD pipeline is required in most scenarios, it makes sense to create parameters for the server and database names.<\/p>\n\n\n\n

This screenshot initially shows only the import, with the data source configured using parameters:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

I then created the first partition. The newly created partitions are also displayed in the navigation pane on the right:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

These parameters can also be referenced in the script. This means that they only need to be populated once, which I consider a major advantage over the traditional approach. With the traditional approach, the source must be entered manually in every partition; with this approach, a central parameter is used:<\/p>\n\n\n\n

createOrReplace\r\n\r\n    ref table RandomValues\r\n\r\n        partition RandomValues_2006 = m\r\n            mode: import\r\n            source =\r\n                    let\r\n                        Source = Sql.Database(Servername, Database),\r\n                        RandomValues = Source{[\r\n                            Schema = \"dbo\",\r\n                            Item = \"RandomValues\"\r\n                        ]}[Data],\r\n                        filtered = Table.SelectRows(\r\n                            RandomValues,\r\n                            each [ImportDate] >= #date(2006, 1, 1)\r\n                                and [ImportDate] < #date(2007, 1, 1)\r\n                        )\r\n                    in\r\n                        filtered\r\n<\/code><\/pre>\n\n\n\n
The code is then executed in the TMDL editor:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The following query can be used to check the records in the partition. A DAX query should always be used to validate the corresponding partitioning rules:<\/p>\n\n\n\n
<\/p>\n\n\n\n
EVALUATE\r\nGROUPBY(\r\n    SELECTCOLUMNS(\r\n        'RandomValues',\r\n        \"Year\", YEAR('RandomValues'[ImportDate])\r\n    ),\r\n    [Year],\r\n    \"No.\",\r\n        COUNTX(CURRENTGROUP(), 1)\r\n)\r\nORDER BY [Year]\r\n<\/code><\/pre>\n\n\n\n
Please note that this query does not take the physically existing partitions into account. It only determines the data available after filtering. Ideally, the partitions should be verified in SQL Server Management Studio by connecting to the model through the XMLA endpoint. In practice, this requires a Premium or Fabric capacity.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The result also shows data for a partition that has not yet been created. This is because the query groups the records by year and does not assign them to physical partitions. The table still contains the original, unfiltered default partition, so all records are loaded regardless of whether a corresponding yearly partition exists. This is generally not useful. You can therefore open the default partition in the TMDL view and remove it. As part of quality assurance, you should verify that no records remain that are not covered by a partition. However, this must be assessed for each individual scenario: from a business perspective, all records should be available for analysis, while the technical focus is on correct partitioning. If the model is not validated after every change, this design should be reconsidered. To remove the default partition, load the entire table as a script in the TMDL view, delete the default partition from the script, and execute it. The default partition is then removed.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
After refreshing the semantic model, the result can be verified using the DAX query:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The most reliable way to check how many records a partition contains is to use SQL Server Management Studio. To do this, publish the model to a Premium workspace and then connect to it through the XMLA endpoint:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Here you can see that a new partition does not yet contain any records. After the partition has been fully processed, the records are available:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The semantic model can now also be refreshed through the Power BI service without any issues. However, this processes all partitions because no information has been provided about which specific partition should be refreshed:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
To specify which partition should be refreshed, you can either select and refresh it manually in SQL Server Management Studio or automate the process. For automation, you need to know how to define the partition in the request. In my example, this is relatively straightforward because only the data for the relevant year is refreshed, and this will usually be the current year. I therefore created the following PowerShell script to refresh a specific partition. The script should, of course, be parameterized as required. One possible option would be to run it daily through Azure DevOps, or whenever new data for a year becomes available. However, if data from previous years changes continuously, you should seriously reconsider whether partitioning is appropriate for this use case.<\/p>\n\n\n\n
Import-Module SqlServer\r\n\r\n$Server = \"powerbi:\/\/api.powerbi.com\/v1.0\/myorg\/tmdl_partition\"\r\n$Database = \"tmdl_part\"\r\n\r\n$Tmsl = @'\r\n{\r\n  \"refresh\": {\r\n    \"type\": \"full\",\r\n    \"objects\": [\r\n      {\r\n        \"database\": \"tmdl_part\",\r\n        \"table\": \"RandomValues\",\r\n        \"partition\": \"RandomValues_2008\"\r\n      }\r\n    ]\r\n  }\r\n}\r\n'@\r\n\r\nInvoke-ASCmd `\r\n    -Server $Server `\r\n    -Database $Database `\r\n    -Query $Tmsl\r\n<\/code><\/pre>\n\n\n\n
After running the script for a new partition, the data was loaded into that partition.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
As mentioned previously, Power BI Desktop does not always have to be used. Using Desktop would also require the latest file to be downloaded first. TMDL editing is now also available online as a preview feature:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
In the example above, I created a new partition, which then became available:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
It is also advisable to validate the filter logic of each partition. When SQL Server is used as the source, the easiest way to do this is with SQL Server Profiler, which shows the SQL statement sent to the source:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
In one of my previous articles on partitioning, I described how Power BI data models can be partitioned using the XMLA endpoint. One limitation of that approach was that tables could only be partitioned using SQL Server Management Studio. In … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1654"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1654"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1654\/revisions"}],"predecessor-version":[{"id":1669,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1654\/revisions\/1669"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1654"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1654"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1654"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}