Why Power BI developers should care about the Tabular Model Definition Language (TMDL)

Microsoft's adoption of Tabular Model Definition Language (TMDL) as the semantic model file format for the Power BI project files, announced in the Power BI February 2024 Feature Summary, marks a significant milestone in improving the Power BI Desktop developer mode experience. Instead of one long JSON file like the legacy Tabular Model Scripting Language (TMSL), TMDL has a folder structure, with separate files for each table, perspective, role and culture.

Note: During the Public Preview of Power BI Desktop developer mode, the TMDL semantic model file format is opt-in only, however, it is due to become the default at General Availability.

This blog post will explore the main benefits of TMDL, specifically in comparison to TMSL, and why it will make a difference to Power BI developers.

1. Source control

Enhanced collaboration with source control

Prior to the release of Power BI projects, there was no native source control support for Power BI. This has impacted productivity, as up until now, only one developer could work on a Power BI report at any one time. However, with Power BI projects, multiple developers can now work on the same Power BI report simultaneously. This is because the semantic model and report definition files are source control friendly, meaning that developers can independently work on the same Power BI semantic model and integrate their changes via traditional branching / merging processes. For example, different developers could work on developing different parts of the semantic model e.g. developing DAX measures for different areas of the report. They can then integrate their changes by checking their semantic model files into source control and following PR processes.

The new TMDL semantic model file format is better suited for source control integration than the TMSL format initially released with Power BI projects. Whilst the TMSL semantic model file format is source control friendly, it is still quite difficult for developers to integrate their changes using this format. This is because with TMSL, the entire semantic model definition is contained within one long JSON file, meaning that when reviewing PRs it's difficult to determine the specific changes that have been made to the semantic model. However, with the new TMDL format, the semantic model definition is broken down into several smaller files within a folder structure. There are individual files for each table, perspective, role, and culture. This means that when a developer issues a PR it is easy for other developers to understand the updates that have been made to the semantic model simply by looking at the specific folder and file that has changed.

Reviewing Git diff's and resolving merge conflicts during PR processes is also made much simpler due to the fact that the TMDL files themselves are easy to read. The new TMDL semantic model file format has been designed specifically for human readability. It has a YAML-like syntax where indentation is used to indicate parent-child relationships between objects. It also has minimal use of delimiters, string quotes and escape characters. All of these factors make TMDL very easy to read. By contrast, reading the TMSL format isn't as straight-forward. The JSON syntax has many structural dependencies which you need to take into consideration when reading the file.

Ultimately, due to TMDL's readability and its suitability for integration with source control systems, it is now much easier for developers to collaborate on the Power BI semantic model. This enhances productivity as semantic model development is not constrained to the speed of one developer.

Tracking semantic model changes with source control

With a Power BI Desktop file, there is no meaningful way to track changes that have been made to our Power BI semantic model. A .pbix file is an opaque binary file format, that cannot be parsed and cannot be used with traditional source control systems. Without source control support like Git, we cannot see the semantic model changes in granular detail, nor can we compare different versions of the semantic model. This means that between versions of Power BI reports, mistakes could easily get introduced to the semantic model, for example, we could accidentally delete or overwrite a DAX measure, or, we could unknowingly overwrite another developer's changes.

However, with the new TMDL semantic model format, we can now easily track changes that have been made to our semantic model. The TMDL files are source control friendly, and so by saving our TMDL files in a Git repository, we can granularly track version history at the code level, compare different semantic model versions and revert back to previous versions of the semantic model. This also means that we can now ensure that only intentional changes are integrated into the final semantic model. Any proposed change to the semantic model must be reviewed and approved before it can be integrated into the main codebase, and any conflicting changes (merge conflicts) must be resolved. This means that developers can now be confident that when they make semantic model updates, they will not be overwriting another developer's changes, or introducing any bugs or errors.

Looking at the Git diffs, we can clearly understand semantic model updates, as the new TMDL report format has a folder structure, with separate files for each table, perspective, role, and culture. Unlike the TMSL format, the TMDL format allows you to edit and version each semantic model component independently, without affecting the entire model definition. This makes tracking semantic model changes much simpler.

2. An alternative way to work on the semantic model

Code editor support

Prior to the release of Power BI projects, there was no natively supported way to edit the semantic model programmatically. However, with Power BI projects, the semantic model definition can now be modified programmatically. Each of the TMDL semantic model files can be opened and edited within a code editor, such as Visual Studio Code (VS Code). This changes the way we might approach Power BI semantic model development. Instead of creating and editing our semantic model in the Power BI Desktop interface, we can choose to do this programmatically in a code editor, which opens up a whole host of benefits.

The TMDL format inherently facilitates ease of editing in a code editor, especially for DAX and M expressions. This is an important upgrade for the semantic model file format. Unlike TMSL, M queries and DAX queries are integrated into TMDL natively, there are no escape characters or delimiters etc. so you can actively copy and paste these queries from a DAX editor such as DAX Studio for example and into the TMDL script and vice versa.

It is also easy to identify the specific semantic model component we would like to make updates to as each table, perspective, role, and culture has their own individual TMDL file organised within an intuitive folder structure. TMDL also supports descriptions, which encourages best practices of describing your semantic model. You can add descriptions to measures, tables, columns, hierarchies, hierarchy levels and names expressions by simply using the /// syntax:

You can use any code editor to make updates to your TMDL files, but Visual Studio Code (VS Code) is recommended. This is because Microsoft have developed a TMDL Language Extension for VS Code which will give you syntax highlighting, auto-complete suggestions and highlight errors that will cause TMDL serialisation to fail.

Code editor support for the TMDL semantic model files marks a significant step towards Power BI being a developer tool. Editing our Power BI semantic models is now a similar experience to working with any other code base.

Note: Power BI Desktop isn't aware of changes to Power BI project files made by other tools. Therefore, if you make any changes to your Power BI semantic model by editing and saving the TMDL semantic model files in a code editor, you need to re-open your report in Power BI Desktop for the changes to be applied here.

3. Improved semantic model development efficiency

Code editor support for TMDL also improves semantic model development efficiency. Instead of using the Power BI Desktop interface to add new tables or to create and edit measures, you can now do this outside of Power BI Desktop using a code editor. You can now easily copy and paste tables from one Power BI semantic model to another, make batch edits to measures, or even programmatically generate tables and measures, all of which reduce the amount of time and effort required to develop the semantic model.

Ease of copying semantic model tables

It is very common to build a Power BI report that has the same tables in the semantic model as a previously built Power BI report. It would therefore be useful to be able to 'copy and paste' tables between reports, however, there is no native way within Power BI Desktop to do this. Instead you would need to add the tables from scratch within the Power BI Desktop interface, and then configure the relationships between tables via the modeling tab.

However, with the new TMDL format, there is now a way to 'copy and paste' tables between different semantic models. This is a huge time saver as it enables you to re-use tables which have already been configured in Power BI reports you have built previously. To copy semantic model tables from one Power BI project to another, you can simply copy the TMDL file for that table over and then configure the relationships for that table in the 'relationships.tmdl' file.

Batch edits

It is also common to want to update multiple measures within a Power BI report. For example, you might have multiple measures which have a 'Date'[Year] = 2023 filter condition, and you want to update the year value in all of these measures to 2024. If you were to do this within the Power BI Desktop interface, it would be extremely time consuming, especially if there are hundreds of measures you need to update. Manually updating these measures is also prone to human error, you could easily miss a few measures. Now, with the new TMDL format, you can make batch edits to DAX measures. This means you could update multiple measures in one go using one simple script. This massively speeds up and simplifies semantic model development. It also improves the quality of your semantic model as programmatically applying batch edits ensures that updates are applied consistently.

Programmatic generation of tables and measures

Finally, with the new TMDL format, you can even programmatically generate tables in your semantic model by leveraging re-usable component libraries and code generation techniques. For example, instead of adding and configuring each table from scratch, you could have a template 'table.tmdl' file, with placeholders for the variable parts of the TMDL file such as the measures contained within that table, the different columns of the table, the properties of those columns such as their data type, and the M code which defines the data source for that table. Then you could use a script to replace these placeholders with actual values. Using this method, you can efficiently add and configure tables in your semantic model, reducing manual effort and saving development time.

4. Opportunity to create/work with community based tools

TMDL goes beyond Microsoft Power BI products. TMDL actually came from the Power BI community. Mathias Thierbach (creator of pbi-tools) added TMDL to the Tabular Object Model (TOM) library as part of the Power BI Contributor Program. Any community tool can utilise TMDL as its semantic model definition format via the Tabular Object Model (TOM) library. Open-source tools such as pbi-tools or Tabular Editor already use the TMDL format which enables them to integrate with Power BI project files.

5. The future of Power BI projects

During the Public Preview of Power BI Desktop developer mode, the TMDL semantic model file format is opt-in only, and TMSL will continue to be the default format. However, TMDL is due to become the default semantic model file format at General Availability. Therefore, it is important to understand and adopt TMDL now, as it will soon be the standard semantic model format for all Power BI project files. By familiarising yourself with TMDL, you can stay ahead of the curve and ensure a smooth transition when the format becomes the default.

It is important to note that TMSL is not going away, it will still be supported and evolved. TMDL is just another tabular model representation language. TMDL is essentially better than TMSL for human readability, whereas TMSL is still the best machine readable tabular model representation language. The Tabular Object Model (TOM) library will allow you to serialise to TMSL or TMDL, but both formats deserialise to TOM.

Conclusion

The new TMDL semantic model format within Power BI projects offers a host of features that can significantly benefit Power BI developers. From enhanced collaboration to improved semantic model development efficiency, TMDL is a welcomed improvement to Power BI projects. By leveraging TMDL, we can now create, edit and collaborate on our semantic model in a more time-efficient way.

If you want to stay on top of all the news and articles from the Power BI ecosystem, then I'd definitely recommend signing up to endjin's free Power BI Weekly newsletter.