Best practice rules to improve your model's perfor...

mikova · ‎02-03-2021

There are a plethora of articles, blog posts, and videos which share recommendations for best practices for Power BI and tabular modeling. It is of course essential to read these in order to learn the proper development approach. However, wouldn't it be nice to have these best practices codified in a single place and alert you of a modeling issue as you are developing your model? Think of how 'Spell Checker' works in Microsoft Word. It notifies you of spelling or grammar mistakes while you are typing. This tool would notify you of potential modeling missteps or changes which can be made to improve the model design and performance. This includes recommendations for naming, user experience and common optimizations that can be made to improve performance.

Luckily, the basis for this tool already exists. It is the Best Practice Analyzer within Tabular Editor. Tabular Editor is an open-source community tool developed by Daniel Otykier who is a Power BI MVP and prominent member of the Power BI contributor program.

In this post, we are sharing a set of rules which you can add to your instance of Tabular Editor. Within seconds it scans your entire model against each of the rules and provides a list of all the objects which satisfy the condition in each rule. To do this manually would take ages...especially on a model with many tables and measures. Instead, it's done in a snap!

To clarify, this rule engine may be used against any origin/destination of tabular model - be it a model in Power BI Desktop, Analysis Services, Azure Analysis Services or Power BI Premium.

The Rules

Now, let's see the Best Practice Rules. As you can see below, the rules are segmented into categories. Some rules are more aesthetic-oriented while others are more focused on performance optimization. Note that there are several rules which require running an additional script (each is marked with an asterisk below).

DAX Expressions

Use the DIVIDE function for division
Avoid using the IFERROR function
Column references should be fully qualified
Measure references should be unqualified
Measures should not be direct references of other measures
No two measures should have the same definition
Use the TREATAS function instead of INTERSECT for virtual relationships

Error Prevention

Data columns must have a source column
Calculated columns must have an expression

Formatting

Add data category for columns
Do not summarize numeric columns
First letter of objects must be capitalized
Hide fact table columns
Hide foreign keys
Mark primary keys
Month (as a string) must be sorted
Objects should not start or end with a space
Percentages should be formatted with thousands separators and one decimal
Format flag columns as Yes/No value strings
Provide format string for "Date" columns
Provide format string for "Month" columns
Provide format string for measures
Relationship columns should be of integer data type
Whole numbers should be formatted with thousands separators and no decimals

Maintenance

Ensure tables have relationships
Objects with no description
Remove data sources not referenced by any partitions
Remove roles with no members
Remove unnecessary columns
Remove unnecessary measures

Naming Conventions

Use CamelCase for hidden columns
Partition name should match table name for single partition tables
Object names must not contain special characters

Performance

Avoid bi-directional relationships against high-cardinality columns *
Avoid excessive bi-directional or many-to-many relationships
Avoid snowflake schema architecture
Do not use floating point data types
Large tables should be partitioned *
Limit row level security (RLS) logic
Many-to-many relationships should be single direction
Minimize Power Query transformations
Model should have a date table
Model using Direct Query and no aggregations
Reduce number of calculated columns
Reduce usage of calculated columns that use the RELATED function
Reduce usage of calculated tables
Reduce usage of long-length columns with high cardinality **
Remove auto-date table
Remove redundant columns in related tables
Set IsAvailableInMdx to false on non-attribute columns
Split date and time ***
Date/calendar tables should be marked as a date table
Unpivot pivoted (month) data

* In order to run this rule, you must first run this script as documented here.

** In order to run this rule, you must first run this script in the same fashion as the note above.

*** In order to run this rule, you must first run this script in the same fashion as the note above.

Note: Each of the asterisked (*) rules requires Tabular Editor version 2.12.1 or higher.

Loading the Rules

Here are the steps to load the Best Practice Rules into your local Tabular Editor.

1. Download and install Tabular Editor.

2. Open Tabular Editor and run the following code in the Advanced Scripting window. *

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);

3. Close and reopen Tabular Editor.

Hint: Save the above code as a Custom Action so you can easily import the latest rules from GitHub.

Alternatively, here is the 'less fancy' method for loading the rules into your instance of Tabular Editor (keeping Step 1 from above).

2. Download the Best Practice Rules from GitHub (BPARules.json).

3. Within the Start Menu, type %localappdata% and click Enter.

4. Navigate to the 'TabularEditor' folder.

5. Copy the rules file (.json) and paste it into the TabularEditor folder. *

6. Open Tabular Editor and connect to your model.

7. Select 'Tools' from the File menu and select 'Best Practice Analyzer'.

8. Click the Refresh icon (in blue).

After completing either method, you will now have loaded all the rules to your local Tabular Editor and run these rules against your model. After completing step 8, you will be notified of potential issues or improvements which can be made to your model.

*Note: This will overwrite the existing BPARules.json file (in case you have preexisting rules).

Viewing Rule Details

To view additional details about a particular rule, navigate to 'Tools -> Manage BPA Rules' in Tabular Editor. Click on 'Rules for the local user'.

Best_practice_rules_to_improve_your_model_s_performance

Next, click on any rule within the collection below and click 'Edit Rule'.

Best_practice_rules_to_improve_your_model_s_performance

As shown above, you now see not only the rule name and the code behind it but also a brief description along with a reference document (where applicable). The reference link will be an article/blog/video that delves into more detail about that particular best practice rule.

Fixing Issues

Some of the issues highlighted by the Best Practice Rules are quite simple and can be fixed in seconds. Others are more complicated and may take some time. Some of the simpler rules have a built-in mechanism to fix the issue. All you have to do is right click on an object (or select multiple objects within a rule) within the Best Practice Analyzer and select 'Generate fix script'. This will copy a C# script which can be pasted and executed in the Advanced Scripting window in Tabular Editor. Running that script will modify the specific property of the objects in order to fix the issue. This is shown in the image below.

You may also use the 'Apply fix' option. This will instantaneously run the Generate fix script. I recommend folks start by using the Generate fix script as it allows you to see exactly what is happening. As you get more comfortable with the tool, feel free to try out 'Apply fix'.

As mentioned earlier, only some of the rules have a built-in fix mechanism. Other more complicated rules do not. This is where it is imperative to find the rule in the 'Manage Best Practice Rules' window, click 'Edit Rule', and read the rule description. Also follow the reference link to learn more about the rule and how to fix the particular issue.

Disabling Rules

If a particular rule does not apply, there are several ways to hide rules. First, you can uncheck a rule within the 'Manage Best Practice Rules' window. This will disable the rule as a whole. If you want to disable a rule for a particular object, you can right click on an object within the Best Practice Analyzer and click 'Ignore items' (see earlier screenshot).

Rule Severity

There is an option to set the 'Severity' level for each rule. This has no impact when the Best Practice Analyzer rules are run inside of Tabular Editor. However, if you deploy a model using the Command Line option and specify the Best Practice Analyzer rules to run, then the severity level comes into play. See the following article for more information.

Conclusion

These rules were crafted from the learnings of analyzing dozens of tabular models created by developers from across the world. We hope these rules empower people to more easily produce higher quality models in terms of architecture, design, and performance. Please feel free to write your own rules and make rule suggestions on GitHub. Just mark the issue/comment with the prefix 'BPARules'. As we learn more and technology advances, we will continue to enhance the rules in an effort to ease the process of building high quality models.

A new Data Days event is coming soon!