top of page
  • MK

Vertipaq Analyzer in Tabular Editor

Updated: Aug 18, 2021

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


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:

  1. Download the script.

  2. Open your model in Tabular Editor*

  3. Copy the script and paste it into the Advanced Scripting window.

  4. 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:

  1. Within Tabular Editor, go to Tools -> Manage BPA Rules

  2. Click Add if you have not created any rules yet.

  3. Click New Rule

  4. Name the rule (i.e. Bi-Directional High Cardinality)

  5. In the Applies to section, select the following: Calculated Columns, Calculated Table Columns, Data Columns

  6. Enter the following in the Rule Expression Editor

UsedInRelationships.Any(CrossFilteringBehavior == CrossFilteringBehavior.BothDirections)


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.


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.

12 Kommentare

Abhijith DSouza
Abhijith DSouza
24. Nov. 2022

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 ?

Gefällt mir
Antwort an

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.

Gefällt mir

Debbie Edwards
Debbie Edwards
15. Okt. 2021

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?

Gefällt mir
Antwort an

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.

Gefällt mir

Ben Arendse
Ben Arendse
19. März 2021


Great article 😁

Do you have anymore best practice rules based on de vertipaq results?

Kind regards,


Gefällt mir
Debbie Edwards
Debbie Edwards
19. Okt. 2021
Antwort an

Its proving to be really difficult getting the information I need.

  • "The following rules require running an additional script before running the Best Practice Analyzer

    • Avoid bi-directional relationships against high-cardinality columns * DONE

    • Large tables should be partitioned *

    • Reduce usage of long-length columns with high cardinality *^

    • Split date and time ***

    • Fix referential integrity violations *

*These rules use Vertipaq Analyzer data. There are 2 methods to load this data into Tabular Editor:

  1. Load Vertipaq Analyzer data directly from a server (instructions) (script). I used your instructions here.

  2. Load Vertipaq Analyzer data from .vpax file (instructions) (script).

^Run this script while live-connected to the model. ***Run this script while live-connected to the model."

I think that for everything else…

Gefällt mir

Farhan Soomro
Farhan Soomro
07. Feb. 2021

Mike , I ran the script and model is attached with PBI desktop but I cannot able to see Annotation

Gefällt mir
Antwort an

Yes, exactly.

Gefällt mir
bottom of page