How to Optimize Power BI Models with VertiPaq Analyzer

Power BI Desktop shows you only one number, the size of the .pbix file. That is not enough to make good decisions. VertiPaq Analyzer answers the three questions that every optimization depends on. 

Where is the memory?  
It shows the size of every table and every column, so you know which 5–10 objects are worth your time. In most models, a handful of columns take the majority of the memory. 

What is causing the size? 

For every column, it shows how much memory goes to data, dictionary and hierarchy, together with cardinality and encoding. Instead of just seeing that a column is big, you understand why it is big, and that points you straight to the right fix. 

Did my change work? 
You can save the statistics before and after a change and compare them. Optimization becomes something you can measure and show to your team or client. 

Getting Started in DAX Studio 

Open your report in Power BI Desktop, start DAX Studio and connect to the model (Power BI / SSDT Model in the Connect dialog). For a model published to the Power BI Service, you can connect through the XMLA endpoint if the workspace runs on Fabric, Premium or Premium Per User capacity. Then go to Advanced -> View Metrics. The VertiPaq Analyzer Metrics pane opens at the bottom of the window with the Summary, Tables, Columns, Relationships and Partitions tabs. 

The Numbers That Drive Every Optimization Decision 

You do not need to understand every column in VertiPaq Analyzer. For optimization, these are the ones that matter: 

Total Size and % DB - Where the memory is. Sort by Total Size in the Tables tab to find the heaviest tables, then in the Columns tab to find the heaviest columns. 

Cardinality - The number of distinct values in a column. This is the most important number in the whole tool. Almost every optimization comes down to reducing cardinality or getting rid of a high-cardinality column. 

Dictionary Size - VertiPaq stores each distinct value of a column only once, in a dictionary, and the data itself contains only small integer IDs pointing to it. The more distinct values and the longer they are, the bigger the dictionary. A large Dictionary Size means the column has many unique values. 

Hierarchy Size - For every column, VertiPaq builds an attribute hierarchy by default. It is used for sorting, filtering and by MDX clients such as Excel. Its size also grows with cardinality, and it is created even for columns nobody will ever filter on. 

Data Size - The compressed values themselves. Large Data Size compared to the number of rows means poor compression, often caused by high cardinality or by the order of the rows. 

Encoding - HASH means the column uses a dictionary. VALUE means it does not. The engine stores the numbers directly, which is usually much cheaper. Text columns are always HASH. Numeric columns can be either. 

The trick is to read these numbers together. The split between dictionary, hierarchy and data tells you which of the fixes below to use. 

Finding 1: Hidden Date Tables You Never Created 

When you open the Tables tab, you will often find a group of tables you never created: many tables named LocalDateTable_… and one DateTableTemplate_…. They come from the Auto date/time feature, which quietly creates a hidden date table for every date column in the model, including technical dates that nobody uses in reports. Each of these tables may look small on its own, but together they can take a noticeable part of the model. It gets worse when a date column contains placeholder values like 1900-01-01 or 9999-12-31. Power BI then generates a calendar covering thousands of years, and a single hidden table can suddenly contain millions of rows. 

The fix is simple. Turn off Auto date/time in File -> Options and settings -> Options -> Current File -> Data Load -> Time intelligence and use one proper date dimension instead. All the hidden tables disappear at once, and you get one date table that you fully control. 

In our example model, the automatically created date tables take around 180 MB out of the total 3.45 GB. That is memory you can win back by changing a single setting. 

Finding 2: High-Cardinality Columns Nobody Uses 

The next thing to look for are text columns sitting at the top of the Columns tab with cardinality close to the number of rows in their table. These are usually transaction IDs, GUIDs, document numbers, free text comments or audit columns. Because almost every row has a different value, the dictionary has to store all of them, compression has very little to work with, and the Hierarchy Size is typically large. In many cases these columns end up in the model for one reason, they were part of the source view, so they were loaded together with everything else. 

Before you try to optimize such a column, ask a simpler question. Does anyone actually filter or display it? If not, remove it in Power Query or directly in the source view. In most models, this is the single most effective optimization, because the entire cost of the column disappears at once. Tools like Measure Killer or Tabular Editor's Best Practice Analyzer can help you confirm that the column is not used in any measure, relationship or visual before you delete it. 

In our example model, TransactionGUID alone takes around 500 MB, which is almost 15% of the entire model, for a column that no report ever displays or filters on. 

Finding 3: Datetime Columns with Millions of Values 

Datetime columns are another common source of wasted memory. In VertiPaq Analyzer they usually show up with very high cardinality and a large dictionary, and they are often among the biggest columns in the fact table. The reason is simple, date and time together create an almost unique value for every row. One year of data stored with second precision can easily produce millions of distinct values, and each of them has to be kept in the dictionary and in the hierarchy. 

The solution is to split the column into two, a Date column and a Time column. The date column will contain only a few thousand distinct values, and the time column at most 86,400. If you do not need seconds, round the time to minutes, which reduces it to just 1,440 values. And if nobody analyzes the time of day at all, simply keep the date and remove the time completely. All of this can be done in Power Query with a couple of extra steps, and the date column can then be connected directly to your date dimension. 

Finding 4: Numeric Columns with HASH Encoding 

Numeric columns can also be more expensive than they need to be. Look for amounts, prices or quantities with Encoding set to HASH and a noticeable Dictionary Size. This means VertiPaq could not use VALUE encoding, which would store the numbers directly without any dictionary. The most common reason is that the column is a Decimal Number, which is a floating-point type, and contains many decimal places. It can also happen when the values are spread over a very wide range. 

There are three ways to fix it, and they work best together. Start by reducing precision and rounding amounts to the number of decimals you really need, which is usually two. Then change the data type from Decimal Number to Fixed Decimal. Fixed Decimal is stored internally as an integer with four decimal places, so it is much more likely to get VALUE encoding. Finally, you can set the Encoding Hint property of the column to Value, either in the TMDL view in Power BI Desktop or in Tabular Editor. Keep in mind that it is only a hint, if the values cannot be value-encoded, the engine will still fall back to HASH, so always refresh the model and check the Encoding column in VertiPaq Analyzer again. 

A good example in our model is the CostAmount column. It was stored as a Decimal Number with HASH encoding, and its dictionary alone took 355 MB. After rounding the values and switching the column to Fixed Decimal, the dictionary dropped to just 144 KB. Same column, same business meaning, but a fraction of the memory. 

Finding 5: Large Hierarchies on Columns Nobody Filters 

The next place to look is the Hierarchy Size column. Sort the Columns tab by it, and you will often find numeric fact columns and hidden key columns near the top, even though nobody ever uses them in slicers or on chart axes. This happens because VertiPaq builds an attribute hierarchy for every column by default, whether it is needed or not. The size of that hierarchy grows with cardinality, so for high-cardinality columns it can easily reach tens or even hundreds of megabytes. Power BI reports query the model with DAX and in most cases do not need these structures at all. They exist mainly for MDX clients such as Excel. 

You can switch them off per column with the IsAvailableInMDX property. Set it to False in the TMDL view in Power BI Desktop or in Tabular Editor, and after the next refresh the engine stops building the hierarchy for that column. In VertiPaq Analyzer, its Hierarchy Size drops to zero. 

In our model, turning this setting off for the BatchID column in the Inventory table saved 24 MB. The column, its values and its dictionary stayed exactly the same, only the Hierarchy Size dropped to zero. 

Measuring the Result 

An optimization is finished only when you can prove it worked. After your changes, refresh the model in Power BI Desktop and click View Metrics again in DAX Studio. Export the new statistics as a .vpax file and open the baseline with Import Metrics to compare both versions side by side. 

Look at the total model size, the size of the largest fact table and the top 10 columns. These numbers are also the best way to explain the value of the work to your team or client: "the model went from X to Y, and refresh time dropped by Z" is much more convincing than "I cleaned up the model". 

Before optimization - total model size: 3.45 GB 

After optimization - total model size: 480 MB (about 86% smaller) 

The result speaks for itself. Our example model went from 3.45 GB to just 480 MB, a reduction of about 86%, which makes it roughly seven times smaller. None of the changes removed anything the reports actually use. Every visual, measure and relationship still works exactly as before. The model simply stopped storing data that nobody needed and structures that nobody queried