How to use Vertipaq Analyzer with DAX Studio for Power BI Model analysis
If you are looking to better understand your Power BI Model, how big are your tables, which column is taking up the most space then you can use Vertipaq analyzer which is built directly into the amazing DAX studio.
If you are looking for a video on how to do this, there are some awesome videos found here.
The reason for my blog post, is that I find sometimes I want to watch a video to better understand the content, and other times I want to follow a step by step process on how complete the analysis.
Before you start you can download DAX Studio HERE
NOTE: All the values shown in Vertipaq Analyzer are shown in Bytes
How to connect to my Power BI Model with DAX Studio
The first step is to connect my Power BI Model with DAX Studio
- I open my Power BI PBIX file.
I then click on External Tools and then click on DAX Studio.
- This then opens DAX Studio and I now have connected to my Power BI Model
Running Vertipaq Analyzer
The next step is to locate and run Vertipaq Analyzer.
In the ribbon click on Advanced and there I could see View Metrics
- I then clicked on View Metrics to run it.
When it is running I could then see in the Vertipaq Analyzer Metrics Window it is running
- Once it has finished running, I could then see the results.
How much memory is my Power BI Model consuming?
One of the key features is to find out how much memory the Power BI Model is consuming when it up and running.
One key thing to note, is that the PBIX size is not how much memory the Power BI Model is consuming.
I have found that the memory usage is roughly 3 – 4x the PBIX file size.
- In the Vertipaq Analyzer Metrics, I click on the Summary pane.
As shown below my current PBIX is consuming 469.12 MB.
Finding the table taking the most memory
Another great feature is to find out which table is consuming the most amount of memory in my Power BI Model.
- In the Vertipaq Analyzer Metrics, I click on the Tables pane.
- As shown below I only have multiple tables it will default to the largest table at the top.
- Another nice feature is that you can sort on the columns if you want to sort it by name or Cardinality too.
Which column is taking up the most space?
Very often there could be a few columns that are not required in your Power BI model, but they take up a lot of space. This is easy to find with Vertipaq Analyzer.
- I clicked on Columns, this allowed me to see the Col Size (Column Size) and it is sorted by largest to smallest.
- I can now quickly see that the internal date tables and associated columns are taking the most space overall a whopping 76%
Finding missing Keys
Did you know that you can also use Vertipaq Analyzer to find out missing keys?
- I clicked on the Relationships pane.
- I could easily see which relationship and associated table has got the missing keys.
As I have shown below there is a lot of very useful and quick information that can be used when using the Vertipaq Analyzer in DAX Studio.
If there is anything I have missed, or you would like an explanation please leave it in the comments below.
Thanks for reading!