Power BI Premium Performance – Part 2 | Optimization of your Power BI Model
In the second part of my blog series on Performance I am going to show how to look to potentially optimize your Power BI Model.
The biggest impact you can have to the performance of your model is if you can reduce the size of the model, it just means that everything will run that much faster, process quicker and potentially fit more Power BI models into your Power BI Premium Capacity.
If this is your first time here, you can read about just first blog post in the series
- Part 1 | How to use the new XMLA End Point to see how much memory your datasets are using
- Part 3 | How does a dataset refresh affect performance?
- Part 4 | How does Row Level Security affect performance?
- Part 5 | How much memory will the PBIX consume?
Quick overview of my Power BI Model
I am using the sample dataset called World Wide Importers (WWI), and below is what it looks like. It contains sales from a retail store.
As shown above it is a Star Schema, the only thing that is different is that I have used a period table which I used in this blog post
Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M which allowed me to create the filters I needed in order to make the report dynamic.
By leveraging the XMLA end points, I can very quicky see how much memory my Power BI model is taking. As shown below it is currently using 166.62MB of memory.
Not only that but I can also see how many rows the data is taking. As shown below it is currently using 51.56 million rows. That is a lot of rows!
What is consuming all the memory?
Now that I know how much memory my Power BI model is consuming, I am going to see if there is anything that I can do to optimize it.
Below are a few things that I will be looking for:
- Removal of any columns not required
- Removal of any tables not required
- Identifying columns which consume a large amount of memory to see what the reason is
Because I have already got my XMLA report, it allows me to very quickly and easily see what is consuming all the memory at a high level by to the page called “Tables and Partitions”
As explained previously this page shows in the chart how much memory each table is consuming.
Now when I look above, I can see that currently there are 3 tables that are contributing to almost ALL of the memory consumption.
What is interesting is that there are two tables called “LocalDateTable_6b2a749c-799d-4031-ae27-e5778f93a3a4” & “LocalDateTable_34271e62-21ff-4618-99af-d9eec5bf420a”
But when I look at my Power BI model, I do not see any tables with that name.
Fortunately, I do know what is causing those tables to appear.
I go into the Options of my Power BI PBIX file, then under CURRENT FILE I click on Data Load
As you can see below there is an option selected for “Auto Date/Time”
What “Auto Date/Time” does is it will automatically create Date tables in my Power BI Model where ever it finds a datetime column.
What it also does is far as I can recall is it looks for the first date and last date and creates a table based on those values.
When I run a DAX query via SQL Server Management Studio on these large tables I can then see that it has got dates until 9999 which is causing the table to be so big.
Removing unwanted columns or tables
I can now see that the tables that start with LocalDateTable_GUID are the ones consuming a lot of memory in my Power BI Model.
For me to fix this what I can do is to simply remove the tick from the option “Auto Date/Time”
Then save and refresh my PBIX
What effect did my changes have on my Power BI Model?
After making the simple change above, I uploaded to my Power BI Premium Workspace.
I then refreshed my XMLA for PBIX – Template.pbit and what a massive difference it made.
It now is using 4.55MB of Memory, the row count is down to 4.85 Million rows.
That is an incredible difference and I was fortunate in this example it was something that was easy to change and see an immediate difference.
A lot of what I do cover in this blog post is detailed in the following whitepaper from Microsoft
I hope that you are enjoying my blog post series and that you are getting some value.
If there are any questions or comments, please leave them in the section below.
Thanks, and in the next blog post in this series I will be covering How a dataset refresh affects the memory in Power BI Premium.
If you would like to have a look at my sample PBIX you can download it from here: WWI – Power BI – Blog.pbix
[…] Power BI Premium Performance – Part 2 | Optimization of your Power BI Model (@GilbertQue) […]
Thanks for this.
i get this error during the refresh process (Databasesize table) while i tried to use the above steps –
OLE DB or ODBC error: Query ‘DatabaseSize’ (step ‘AutoRemovedColumns1’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Can you let me know how to fix this
Hi there
Can you confirm that you are connecting to the Power BI Premium dataset via XMLA?
[…] Part 2 | Optimization of your Power BI Model […]
[…] Part 2 | Optimization of your Power BI Model […]
[…] Part 2 | Optimization of your Power BI Model […]