Vertipaq Analyzer in Tabular Editor
See Vertipaq Analyzer stats in Tabular Editor and create Best Practice rules around them.
If you are serious about optimizing your tabular model (be it in Power BI, Azure Analysis Services, or SSAS), then you are likely familiar with Vertipaq Analyzer. It is a great tool which allows you to investigate detailed information as to the structure and compression of objects in your model. Earlier this year, Vertipaq Analyzer was integrated into DAX Studio which makes this information even more accessible. In this post I’ll show you how we can take things a step further.
Best Practice Analyzer
If you are interested in ensuring your tabular models follow best practices for naming, formatting, and performance, Tabular Editor has a great feature called Best Practice Analyzer. If you are not using it yet, I highly recommend that you try it out. It is an efficient way to automatically scan your entire model and ensure that your team’s best practices are kept.
Combining Technologies
Now, when it comes to optimizing performance we often use Vertipaq Analyzer. And I just mentioned that we can codify best practice rules in Tabular Editor’s Best Practice Analyzer. The natural next thought is - “what if we could write Best Practice Analyzer rules taking the Vertipaq Analyzer statistics into account?”. One may think this is not possible as Tabular Editor only contains the metadata for the model (based on the .bim file) so Vertipaq stats are not available. However, this is no longer the case!
As shown in my previous post, Tabular Editor is now able (when connected to a server or a Power BI Desktop model) to run live DAX queries. It turns out that you can also run Dynamic Management Views (DMVs). Since all the Vertipaq Analyzer data comes from DMVs, we can extract this information and make it available in Tabular Editor. In fact, here is how to run a DMV query in Tabular Editor.
var DMV_Tables = ExecuteDax("SELECT * FROM $SYSTEM.TMSCHEMA_TABLES").Tables[0];
DMV_Tables.Output();
The Script & What It Does
Here is a link to the script. In short, this script extracts a model’s Vertipaq Analyzer statistics and saves them as annotations to the model’s file. All values are shown in bytes (unless they are indicated as a percentage). These annotations can easily be browsed and the best part is that they can easily be referenced using Advanced Scripting or the Best Practice Analyzer.
Running the Script
Here are the steps:
- Download the script.
- Open your model in Tabular Editor*
- Copy the script and paste it into the Advanced Scripting window.
- Click the Play button (or press F5).
After completing these steps, the Vertipaq Analyzer statistics have been saved as annotations in your model.
* The model must be connected to a server or via Power BI Desktop.
Note: If running this script against a Power BI Desktop model (using Tabular Editor as an External Tool), you must select the following setting within Tabular Editor:
File -> Preferences -> Features -> Allow Unsupported Power BI features (experimental)
Show Me the Annotations
Annotations are simply notes that can be made on any object in the model. They can easily be viewed in Tabular Editor by selecting an object and expanding the Annotations dropdown in the Properties window.

Which Vertipaq Statistics are saved?
Here is the full list of Vertipaq stats that are saved as annotations on the model. Each stat is saved within the respective object. For example, Relationship Size is stored as an annotation on each relationship in the model. Navigate to a relationship to see this statistic.
- Model: Model Size
- Tables: Row Count; Table Size; Table Size as a Percentage of the Model Size
- Partitions: Record Count; Segment Count; Records Per Segment
- Columns: Cardinality; Column Hierarchy Size; Column Size; Data Size; Dictionary Size; Column Size as a Percentage of the Table Size; Column Size as a Percentage of the Model Size
- Hierarchies: User Hierarchy Size
- Relationships: Relationship Size; Max From Cardinality; Max To Cardinality; Referential Integrity Violation Invalid Rows
This is also documented on the README section on my GitHub page.
Setting up a Rule based on Vertipaq
Now that we have the annotations stored, let’s see how easy it is to create a Best Practice Analyzer rule. We know that bi-directional relationships are costly - but they are especially costly if they cross between large tables. Let’s create a rule that detects this.
Here are the steps:
- Within Tabular Editor, go to
Tools->Manage BPA Rules - Click
Addif you have not created any rules yet. - Click
New Rule Namethe rule (i.e. Bi-Directional High Cardinality)- In the
Applies tosection, select the following: Calculated Columns, Calculated Table Columns, Data Columns - Enter the following in the
Rule Expression Editor
UsedInRelationships.Any(CrossFilteringBehavior == CrossFilteringBehavior.BothDirections)
and
Convert.ToInt32(GetAnnotation("Vertipaq_Cardinality")) > 1000000

Entire rule shown in Best Practice Analyzer’s editor
This rule scans all the columns (of any type) in your model, checks whether they are used in a bi-directional relationship and if the cardinality of the column is more than 1 million unique records. Checking this manually would be quite a bit of work. Now, it’s checked automatically and can be easily done for all your models.
Conclusion
The BPA rule shown above is just an example to show a glimpse of what is now possible when combining Vertipaq Analyzer and Tabular Editor’s Best Practice Analyzer. Ensuring your model follows an optimized structure and compression has never been easier. For more information on analyzing Vertipaq Analyzer statistics, see this video.
Comments 12
No comments yet. Be the first to share your thoughts.
Mike , I ran the script and model is attached with PBI desktop but I cannot able to see Annotation
Did you go to File. Preferences and Allow Unsupported Power BI Features?
Yes, exactly.
Hi,
Great article 😁
Do you have anymore best practice rules based on de vertipaq results?
Kind regards,
Ben
Hi Ben, See all the rules published in the link below. There are more which scan the vertipaq data (they have an asterisk). If you have other rules in mind feel free to share. https://powerbi.microsoft.com/blog/best-practice-rules-to-improve-your-models-performance/
I have this question to and reading the information you linked there is the following: * 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.
So there are 3 Rules you can set up but you only mention setting up the first one * It would be good to know how to set up the other rules in this doc by creating new rule.
This info is on the GitHub page for the Best Practice Rules. https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules
Its proving to be really difficult getting the information I need.
*These rules use Vertipaq Analyzer data. There are 2 methods to load this data into Tabular Editor:
^Run this script while live-connected to the model. ***Run this script while live-connected to the model."
I think that for everything else the rule is created automatically so the only one you need to create is the Bi directional rule which is why you put up the screen shot, But for everything else. You just run the script and they automatically appear in the Best Practice Analyser?
the only other issue is that Split Date and Time is in the Best Practice Analyser with an error. Error while Evaluating expression on data columns. Input String was not in a correct format
I tried creating the first rule. But it comes back with an error. No Property of field 'UsedInRelationships' exists in type Model. I can definitely see that Annotations have been created but I'm not sure what has happened here?
You must uncheck ‘Model’ in the scope portion of the rule. See how the article mentions only specific objects (various column types) for which this rule applies.
This is a great blog and thanks for posting this. Have you tried automating it so that everytime a dataset refreshes in production this script is run automatically ?
Haven’t done that but it would be easy enough to do. These scripts can be run against a dataset using Tabular Editor’s command line options.