Power BI basics: How to use DAX Studio to reduce your model footprint
Hugh Johnson explains why you should reduce the memory your Power BI model consumes and how DAX Studio can help.
In very simple terms, the more memory that your Power BI model consumes when it is open (aka your model footprint), the slower it will be. This is not the only thing to consider if you want to improve the performance of your model, but it is very important. Putting it simply, you should be a much happier person if can reduce the footprint of your Power BI model.
This is where DAX Studio from SQLBI is a truly great tool, or more specifically a single feature of it called “View Metrics”, which you can learn to use in just a few minutes. Simply download DAX Studio and then switch on the preview features under File > Options > Advanced.
If you want to analyse the footprint of your Power BI file in DAX Studio, open your Power BI file, and connect to it from DAX Studio.
Once connected, go to the advanced tab and select “View Metrics”
This simple report is gold dust. Here you can very quickly see which tables and columns are consuming the most memory in your model. You can also see if you have any auto-generated local date tables and their impact (more on this in a future article).
Now, browsing the model metrics, you can quickly see problem areas. In the example above, you can see that just one column TransactionsNominal[Amount], out of 323 columns in my model, accounts for 17.25% of the total memory usage, occupying 1.46MB.
On the one hand, I know that getting rid of this column is not an option; I need it. On the other hand, I can see straight away that there is something wrong.
Looking at the data type, I see that it is “Double”. That means a floating-point decimal number. This, I know, is not optimal for accounting transaction values that will always have a finite (two) maximum number of decimals. A Decimal type may end up storing £12.51 as 12.5100000000001 or something like that. It also, crucially, increases the risk of high cardinality (a technical term that means that there is a high number of unique values in the column). High cardinality dataset columns take up more space in Power BI than low cardinality columns.
In Query Editor I can change the data type to “Fixed Decimal Number” (aka “Currency”) by right-clicking on the column header and selecting “Change Type”.
After I do this, and refresh my model and then analyse in DAX Studio, I can see a great improvement. The size of this column has reduced from 1.46MB to 0.25MB. A reduction of 83%!
Summary
In this case, we found a data type problem. In other cases, we might see columns and tables that we simply don’t need, and are occupying a lot of space in the model. We can delete these. We may see local date tables that may be presenting a problem. The point is that in just a few seconds, the DAX Studio “View Metrics” feature shines a spotlight on your top priorities if you want to reduce the footprint of your model. If you are short of time, try not to be short of memory also.
Useful links
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...