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.
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).
- 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
- Data columns must have a source column
- Calculated columns must have an expression
- 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
- 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
- Use CamelCase for hidden columns
- Partition name should match table name for single partition tables
- Object names must not contain special characters
- 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 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’.
Next, click on any rule within the collection below and click ‘Edit Rule’.
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.
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.
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).
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.
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.