Jan 4

Jan 4 Tabular Editor – Supercharge your Power BI Development

Tabular Editor is a powerful tool to improve & speed up data model development

MAKE POWER BI DATASETS BETTER & FASTER

…by learning & using Tabular Editor as an External tool

This post is part 1 of a weekly series I’m writing about Tabular Editor.
In this part, we look at the what it is and how it helps us design better models, more effectively.

Next, in part 2, we look at the different licenses of Tabular Editor to pick one right for us. Part 2 is here.

Part 1 – What is Tabular Editor and why do you need to use it?

Tabular Editor (TE) is a powerful, high-quality, external tool for Power BI & AS Tabular Models that helps you make better models more effectively. With TE you can easily connect to and manipulate data models either locally on your desktop or published to the cloud (i.e. Power BI workspace if you have Premium / Premium-Per User & XMLA Read/Write enabled). The tool is unique in that you can both read model objects & properties, but also manipulate them in an elegant user-interface, writing changes back to the model with a push of a button. The suite of features in TE help developers make higher quality models and work more efficiently, reducing development time compared to using first-party tools, alone.

While still very valuable for Power BI Desktop development, it is worth noting, however, that Tabular Editor has some limitations when connecting to models open with Power BI Desktop. Unlike when connected to datasets in the workspace via XMLA endpoint, one cannot write operations for tables & columns, or rename them, for example. It’s unclear if future changes in Power BI Desktop might support these operations.

This is true for both versions of Tabular Editor:

TE2: Free, open-source version available since September 2016
TE3: Commercially licensed (3 tiers) with an expanded feature list, available since April 2021

This is also something that I can also personally attest to. Since adopting Tabular Editor in my development workflow, I estimate that the time I spend on Power BI model development tasks is reduced by on average of approximately 60% compared to using Power BI alone. Three small, simple examples of how I do better work with TE are listed below:

(1) The TE UI is lighter & faster than working only in Power BI.
It sounds simple and silly, but it’s unbelievable how much time is wasted seeing the “Working on it…” pop-up as Power BI processes model changes / additions. In TE, I can batch, debug & document many changes at once. This is true not only for authoring DAX code but also changing model properties like relationships, security, etc.

Dev tasks only to author 25 sales report measures

In a quick trial, I tested to see how long it takes me to write DAX in Power BI alone vs. when using TE3. A big time-saver is batching format tasks, like re-naming or formatting. But even the improved performance & responsiveness of the UI is a big time-saver vs. the Power BI DAX authoring experience.

(2) Right now, one of the only ways to create calculation groups in Power BI is using TE. Calculation groups are calculated objects which can apply additional DAX calculations to existing measures. They are an extremely powerful feature. Calculation groups not only reduce dataset complexity & measure count, but can also result in tremendous performance increases in certain use cases - for example, measure switching. Calculation groups are a standard facet of model development, and in the last year, most models I see at clients have calculation groups. I thus need TE to easily create & manage these objects as a part of the model.

These are performance testing results from a large model that was using dynamic measures selection (results of 20 cold trials, benchmarked with DAX Studio).
V1: The DAX measure in question is a common approach to dynamically select measures in a report. The original measure (V1) took > 1 sec. to evaluate.
V2: This could be optimized by 40% refactoring the code with variables.
V3: Using TE to refactor the approach to use Calculation Groups reduced evaluation times by >90%.

(3) Scripts & Macros can simplify or automate many development tasks.
Another extremely powerful feature of Tabular Editor is the ability to create C# scripts to interact with the tabular object model (TOM). These scripts in TE3 can also be turned to Macros which are simple to use repeatedly across different datasets. This is a huge productivity boost; if you invest some time in making a few scripts for your own tasks, the return is significant.

Later in this blog series, I will share various scripts & macros I use in TE to automate development tasks that in some cases previously took days. A simple example is a script I use to format DAX objects in the entire model while connected via XMLA endpoint.

Format all DAX at once - not just measures (click here)

The DAX Formatter tool by sqlbi is integrated in TE to easily format DAX code with a button push for a single object.
However, you can use scripts to extend this over the model, not only formatting all measures but also calculation group items, calculated columns & calculated tables.
This is a must-have whenever you inherit a model, because as the DAX legends say:
“If it’s not formatted, it’s not DAX!”

(NEW in Tabular Editor 3.2.0) - Author better DAX using a debugger to understand evaluation context, intermediate results & call trees.
A new feature released in January 2022 is the ability to use Tabular Editor to Debug DAX code. This is a very valuable tool for a developer to write complex DAX code more effectively, and understand better what is happening in each step of the process. Using a DAX Query or Pivot Grid, you can right-click a cell and ‘Debug’ the explicit measure result. This will give you information about:

The Call Tree of the measure
The Evaluation Context of that cell
Local variables active in part of the code

On top of this, you can also experiment in the evaluation context to understand how other expressions would be evaluated. Using the watch window, you can enter other DAX expressions ad hoc, then evaluate them to see what the result would be. This helps you better understand the evaluation context and what is happening in DAX.

Debug a DAX measure contained in a Pivot Grid - a way to explore your data in TE3 with drag & drop

These are just a few simple examples of the many reasons why Tabular Editor is a powerful tool. If you develop Power BI models, you owe it to yourself to download Tabular Editor 2 for free to see how TE can help you be more productive.

While a third-party external tool, Tabular Editor is featured by Microsoft and commonly considered by many in the Power BI community to be a must-have tool in every Power BI developer’s belt. This is why I’m writing this blog series – I’ve been so blown away by the impact this tool has had on my effectiveness that in 2022, I would like to try and write a series focused on effectively using the TE tool. The purpose of this series is to help people learn TE for the first time, or for existing users discover & take value from other features they didn’t previously know about or use.

Next up: I’ve written about the differences between the versions (TE2 & TE3) and licenses (TE3), and how you can choose one right for you.