Power BI – Check a model’s memory usage with VertiPaq Analyzer

If you’ve attended any of our courses, you’ve probably noticed that we often mention performance and efficient data models. One of the things you could do to refresh your data quicker and speed up calculations is resizing the data model.

But how do you even figure out the current size of your data model? Is it the same as its file size? In this blog post, we’ll briefly explain how you can analyse this with VertiPaq Analyzer.

VertiPaq Analyzer

VertiPaq Analyzer in an Excel tool by SQLBI. You can download it for free via the following link: https://www.sqlbi.com/tools/vertipaq-analyzer.
It’s updated regularly, so make sure to download the most recent version from time to time.
The zip file contains three files; we use the file VertiPaq Analyzer 1.92 – 1200.xlsm.

Where to find your model’s port number

To connect the Excel file to your model, you’ll need to find the model’s port number first. T The Excel file will use this port to connect to your Power BI model.

1. Open your Power BI file. (In this example, I’ll open a file that’s 7,321 KB.)
   
2. Open Windows Explorer and go to:
   C:\Users\<user>\ AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
3. You’ll see one or several subfolders.
   
   Based on the folder names alone, it’s impossible to see which folder you need. The only way to check this is by looking at the date in the Date Modified column. Since you’ve just opened your Power BI file, you’ll need the folder with the most recent date and time.
4. Open the correct folder. It’ll contain a Datasubfolder, which you need to open. In this folder, you’ll find a file called msmdsrv.port.txt. Open this file.
5. The file contains a number. Write this number down.
   

Linking Excel to the model

Next, open the Excel file. Ignore the data in the document – they don’t have anything to do with your model. Excel uses Power Pivot to connect with your Power BI model.

If you can’t see the Power Pivot tab, you can activate it as follows: File –
Options – Add-ins. At the bottom of the list, go to Manage, then to COM Add-ins and click Go…
Tick the box next to Microsoft Power Pivot for Excel.
Now, follow these steps:

1. Click Power Pivot – Manage (to go to the data model).
2. Click Home – Existing Connections.
3. Click Editin the pop-up window.
4. In the next pop-up window, click Build…
5. Another pop-up window will appear: Data Link Properties.
   Next to Data Source, you’ll see localhost: – enter the number you’ve written down here.
   UnderEnter the initial catalog to use, choose the only available option in the list.
   
6. Close all pop-up windows by clicking OK, Save and Close.
7. Close the Power Pivot window to go back to the Excel sheets.
8. Click Data – Refresh All.

Understanding the data

Initially, you’ll mainly use the Tablesworksheet Column E of this sheet contains the size of the table and that of the table columns (Columns Total Size). In Column D, you’ll see the size of each table (Table Size).

In my example, the file size is about 7.3 MB, but the memory size of my model is almost 20 MB.

The Columnsworksheet contains a clearer overview that shows you which columns take up the largest amount of memory.