Mastering DP-500/DP-600 Exam: Optimize Semantic Model with Tabular Editor

Table of contents

In the previous article, we’ve explained how to leverage DAX Studio free external tool to troubleshoot poorly performing DAX queries. Now it’s time to switch our focus to another extremely popular external tool – Tabular Editor! I’ve already written about Tabular Editor and some of its features that can significantly boost your Power BI development.

Tabular Editor is a comprehensive tool, that may be helpful in various development scenarios. However, as the DP-600 exam focuses on optimizing the semantic model with the Tabular Editor topic, let’s explain how the tool may help you in achieving this specific goal.

DISCLAIMER: Since the DP-600 exam explicitly specifies Tabular Editor 2 (free version) for this topic, we’ll use this version in the remaining part of the article. Of course, you can apply all the same steps in Tabular Editor 3, which is a commercial version of the product

But, now you’re probably asking yourselves: optimizing the data model is such a broad topic, how can one single tool help me to apply all those recommendations that spread across multiple books, videos, whitepapers, and blog posts? Is Tabular Editor a kind of magic wand that can help you transform data model “monsters” into “beauties” with a few clicks?!

Of course not! Knowing general best practices for the data model optimization, such as avoiding bi-directional relationships, reducing the column cardinality, avoiding DirectQuery whenever possible, or removing Auto Date/Time hidden tables, still remains the key requirement! But, Tabular Editor may help you quickly and easily identify potential violations of these practices – based on the insight gained, you can then decide if you want to apply the recommended practice(s) or keep your original data modeling logic in place.

Before I show you how to use Tabular Editor for this specific goal, I must admit that I wasn’t completely honest with you😉… Tabular Editor alone WILL NOT save your data model from disaster. You have to use a Tabular Editor integrated feature, called Best Practice Analyzer.

Introducing Best Practice Analyzer

When dealing with problematic data models, Best Practice Analyzer will quickly become your best friend! This tool was developed by Michael Kovalsky from Microsoft. You can find more details about the tool itself in this article.

In a nutshell, the Best Practice Analyzer (BPA) scans your tabular data model and warns you about all the “issues” within the model. Some “issues” are just that – “issues” with double quotes. This means you can live with these “issues” without sacrificing the model’s performance.

BPA relies on rules – each rule belongs to a certain category, such as DAX expressions, Performance, Formatting, and so on. Obviously, not all rules have the same importance for the model performance. That being said, from the model performance perspective, it doesn’t matter if you capitalize the first letter of semantic model objects, or hide foreign keys. But, the implications may be significant if you haven’t partitioned large tables, if your model contains many bi-directional or M:M relationships, or if you have many calculated columns and calculated tables in the model.

Using BPA in Tabular Editor

Best Practice Analyzer is something you get out-of-the-box when you install Tabular Editor (Thanks to Morten Lønskov from the Tabular Editor team for clarifying this😉). You can add your own rules manually, or you can import an already existing set of rules, by running the following script in the Advanced Editor window of the Tabular Editor:

System.Net.WebClient w = new System.Net.WebClient(); 

string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData);
string url = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json";
string downloadLoc = path+@"\TabularEditor\BPARules.json";
w.DownloadFile(url, downloadLoc);

You can also download the BPARules.json file from GitHub, as described here.

Here is my data model, and as you may notice, I’ve intentionally enabled bi-directional filtering on the relationship between the Online Sales fact table and the Customer dimension table. I’ll now switch to Tabular Editor and launch the Best Practice Analyzer tool from the Tabular Editor Tools tab:

Once I run Best Practice Analyzer to scan my data model, a new window pops up with all the findings and warnings:

I can see that 222 objects are in violation of 19 BPA rules! Looks like someone didn’t do the homework😉. However, this model was intentionally built this way, without any optimization in advance. In addition, you may notice that 125 objects violate the rule of not having a description, which is completely irrelevant from the performance optimization perspective.

However, here is the one that can be very relevant for the data model performance, and that’s the one that warns about the bi-directional relationship I’ve previously created in Power BI Desktop:

If I right-click on the specific object, there is a whole range of possible options to choose from:

One of the options is to apply the immediate fix to this object, and as soon as you do this, the warning will disappear. You can also decide to ignore the complete rule, as I will do for those “descriptionless” objects:

But, there is more to it! In my opinion, the most powerful feature of the Best Practice Analyzer is the Manage BPA Rules option under the Tools tab in Tabular Editor. Once you open it, you will be able not just to change the rule logic and definition (assuming that you’re familiar with C# scripting), but in the Description field, you’ll also find the links to an article that explains why a certain rule is – RULE!

This is an amazing feature because it provides you with the possibility to learn and understand the recommended practices behind tabular data model optimization, instead of blindly applying all suggested modifications.

Tabular Editor also enables you to expand on the set of prebuilt rules and define your own rules! More information about how to do this can be found here.

Conclusion

I guess you already know how amazing Tabular Editor is! Even though its power is not exclusively limited to optimizing the tabular data model, in this article we explained how Tabular Editor, in synergy with the Best Practice Analyzer integrated feature, may help you perform a quick health check of your data model. Not just that, based on the results of this check, you can quickly apply fixes and recommended practices to increase the overall model efficiency.

Thanks for reading!

Last Updated on January 26, 2024 by Nikola

Spread the music: