Vertipaq Analyzer – How to Connect to Power BI Desktop

[Nolock]

If you have a dataset in Power BI Desktop and you want to see what happens under the roof of the Vertipaq Engine, there is a great tool known as Vertipaq Analyzer. It can tell you how much space tables and columns consume, what their data types are like, what compress algorithms they use and so on. But before you can start analyzing your dataset, you need to set everything up.

First, you need to download the tool: https://www.sqlbi.com/tools/vertipaq-analyzer/. Then unzip it and open the Excel Workbook “VertiPaq Analyzer 1.92 – 1200.xlsx”. On the last sheet you can find instructions how to start.

In step 6 you are asked for the SSAS connection, as in the following screenshot.

But where to get the port from if you want to connect to an instance of Power BI Desktop running on your machine? The recommended way is to download DAX Studio and get it from there (also a part of the Vertipaq Analyzer instructions). However, in my work environment it was not allowed to download or install any third-party software and I had to find another solution.
Power BI Desktop uses Microsoft SQL Server Analysis Services as a database engine as you can see on the screenshot of the Task Manager below.

Great, now you have the running process! When you click on “Go to details”, you will also see the process called “msmdsrv.exe” and its PID (process ID). Having the PID, you can get a local port that the process is listening on.

I personally use Powershell:

Get-NetTCPConnection -OwningProcess <your PID> | select -Property LocalPort -Unique

If you do it once, it is ok. However, having to do this repeatedly, you will most probably want to automate the process.

The Powershell script which I use is the following one:

Get-NetTCPConnection -OwningProcess (Get-Process -Name msmdsrv).Id | select -Property LocalPort -Unique

The middle part (Get-Process -Name msmdsrv).Id gives me the PIDs of all running msmdsrv processes on my machine which I use as a parameter for Get-NetTCPConnection.

And the result is the port you need to insert in step 6 of the Vertipaq Analyzer instructions. In the end, you should check if everything is set up correctly by clicking on “Test Connection”.

I wish you a happy deep dive into Vertipaq Analyzer and a lot of fun exploring the internals of your dataset.