In this blog, Scott Hietpas and Cory Cundy – two principal consultants on Core BTS’ Data team – explore the advantages of using Tabular Editor for tabular model development.
The Best Practice Analyzer available with Tabular Editor can be useful from a development standpoint, from a consistency standpoint, and when working with (or training) team members without much exposure to tabular models. We touched on this feature briefly on our overview of Tabular Editor advantages but wanted to break the topic out in more detail.
How Does Best Practice Analyzer Work?
When working with Tabular editor, you can import rule files for Best Practice Analyzer to run. These rules look across your model and identify all the places that aren’t consistent. This is a nice way to find any mistakes or areas where those best practices might not have been followed.
Common Tabular Model Best Practice Rules
Standard example rule files are available on GitHub, and you can customize them for the best practices of your team and organization. We share a few powerful best practices below.
Qualified and Unqualified Measures in DAX
One well-known best practice in developing tabular models when writing DAX is to always fully qualify your column names and to not put your table names in front of your measures. Best Practice Analyzer can look for column references that should be fully qualified and measure references that should be unqualified in your DAX expressions.
Not Summarizing Numeric Columns
“Don’t summarize numeric columns” is another good rule for Best Practice Analyzer to help you enforce. For example, in a date table, if you have a month number attribute that has values of 1 through 12 for your month number, you generally don’t want to be aggregating those numbers because it doesn’t make sense. They’re more useful as an attribute or a slicer. The Analyzer will look for numeric columns where the summarization is not set to none.
Limiting Measures in Your Fact Table
Some rules I run with the Best Practice Analyzer may be more about user preference than hard and fast industry best-practices. For example, if I have more than 10 measures in my fact table, the tool can suggest that I start using display folders or other display options. If 10 is not your number and 12 is, then you can change the JSON file that the rule is built off of and the Analyzer will look according to that rule.
Using Best Practice Analyzer
When Best Practice Analyzer runs against your model, it will let you know how many rule violations you have and give you some additional functionality. In one example, I had an AdventureWorks model where Best Practice Analyzer found 122 objects in violation across 10 different practices of 10 different rules. When you get your results, you can right click on them.
If you want to modify the issue yourself, you can “Go to Object” easily. You can also ignore violations. Sometimes there is a violation instance that you know is against the rule, but you have a reason in that instance to ignore the rule. It will stay in your results marked as “Ignored”, but we can always get back to it with the, “Show Ignored.”
With other rules, we can generate and fix them on the fly. An example of this might be, “Hide Foreign Key Columns.” Generally, you don’t want to expose your foreign key columns. If I want to fix that issue, I can apply effects directly in Tabular Editor that will go through and change that property or column to be hidden.
You can do that in bulk or – if you want to go one step further – you can “Generate fix script” to generate more of this advanced scripting-type logic. This essentially does the same thing as the “Apply fix” option, but it lets you see the code that would be used to apply the fix. With that you can sometimes see what it’s doing and how to use it. You can also learn things about the object model.
Finding Rule Scripts for Best Practice Analyzer
One question we get about this Analyzer is what standard best practices are available and where to find the rules that can be fed into it. We’ve covered some rules above, but many more are available on the GitHub site where you can find the source code for Tabular Editor. Remember, all these best practices are customizable for the needs of your team and your organization.
To wrap up, Best Practice Analyzer is a powerful feature that lets you drive consistency across models. Even with manual code reviews, you’re not likely to catch every one of these small issues. Plus, it lets you learn something about the code behind the scenes.
