loading . . . Power BI TMDL View: Tasks You Can Finally Do Without External Tools Before the introduction of TMDL (Tabular Model Definition Language), modeling tasks in Power BI Desktop was almost entirely GUI-driven. Any advanced tabular modeling tasks required external tools like Tabular Editor, ALM Toolkit, or SQL Server Management Studio.
These tools are not created by Microsoft, many organizations do not allow them, and some require additional licensing. In this blog, letās talk about a few tasks that the new TMDL view enables directly inside Power BI Desktop tasks that were not possible using the GUI at the time of writing.
Changing the Storage Mode
Before TMDL, switching a table from Import to DirectQuery was not possible in the GUI. TMDL makes it simple.
Ā
* Open your semantic model.
* Switch to the TMDL view.
* Select the table in the Model Explorer on the right.
4. Click Script TMDL to ā Script tab.
Ā
5. A new tab will open with the TMDL script for creating or replacing the table.
6. Scroll down to the partition definition and find the mode attribute
7. Change the value to the desired storage mode and click Apply.
That's it, the storage mode is now updated.
Ā
Ā
Ā
Note:
If you switch from DirectQuery to Import, remember to refresh the table so that data loads into the model.
You can use the same approach to switch to other modes like Dual.
Object-Level Security Implementation
Before TMDL, implementing OLS required Tabular Editor or SSMS. Now it can be done directly in Power BI Desktop.
Below is an example that creates a role called RestrictEmployeeId and hides the employeeId column in the Employee table:
Another example that hides the entire Employee table:
Ā
Write your own script based on your model and run it in the TMDL view.
Ā
You can see the created roles in Model Explorer.
Ā
Creation of Perspectives
If you have used the Personalized Visuals feature, you may already know what Perspectives are. They are used to provide a simplified, focused view of a model.
Earlier, creating perspectives required Tabular Editor. TMDL now supports it directly.
The example below creates a perspective called EmployeePerspective that includes selected columns from two tables:
Run a similar script based on your model in the TMDL view.
Ā
Your perspective will appear in Model Explorer.
Ā
Addition of Cultures and Translations
If you have created multi-language Power BI reports, you may already know about Cultures.
Previously, adding a new language or translation required Translation Builder or Tabular Editor. With TMDL, you can define cultures directly.
Example: Adding Spanish (es-ES) language and translating several objects:
Ā
Run the script in the TMDL view.
Ā
The culture will appear in Model Explorer.
Ā
Ā
Configuration of Detail Rows Expressions
If you build self-service datasets for Excel users, you may already know about Detail Rows. This property controls exactly which columns are returned when users drill through in Excel PivotTables connected to your Power BI semantic model.
Before TMDL, setting this property required Tabular Editor. Now it can be done in Desktop.
The example below creates a measure EmployeeCount and defines a Detail Rows expression for it:
Ā
Run this script in the TMDL view.
Ā
To validate the property, you can run a DAX query:
EVALUATE DETAILROWS([EmployeeCount])
Ā
Creating Custom Table Partitions
Before TMDL, splitting a table into custom partitions required Tabular Editor or other external tools. Now this can be done directly in Desktop.
Script your table into TMDL view, locate the partition section, and modify it as needed.
Example: Splitting the Employee table into two partitions:
Ā
Run the script in the TMDL view.
Ā
You will see the new partitions under the table in Model Explorer.
Note: After updating the partitions, refresh the data in the table.
I hope you find this information useful, If I missed any other TMDL capabilities that were not possible through the Power BI Desktop GUI, please share them with me.
Happy Learning! http://dlvr.it/TQ7910
