One of the biggest benefits of Power BI is in its ability to access millions of rows of data and perform complex measures, build in-memory tables, and determine relationships with the accessible DAX language. But with this great power comes great responsibility, so Power BI developers must balance this capability with efficient resource management. This is especially true when publishing reports to the Power BI service where small savings in memory consumption can scale across thousands of users and can make the difference in getting your organization in a less expensive Premium pricing tier.
Measuring Power BI Document Memory Consumption
In our earlier blog post, Are Your Power BI Performance Issues Due To High Memory Consumption?, we see how Windows Task Manager can monitor memory usage and identify Power BI related processes. As with all technology, Power BI and Windows are ever evolving and in this post we'll revisit this exercise with updated examples.
While using Power BI desktop to create a Power BI report, you can use the Windows Task Manager to review the current memory footprint of the report. To do this:
- Open the report in Power BI Desktop.
- Insert a blank page.
- While in the blank page, save and close the report.
- If possible, close all open applications.
- Launch Power BI Desktop again and open the previously saved report.
- Once the report is open, you should be in the blank page that you had added (the report should open to the blank page added in Step B, above).
- Open Windows Task Manager by pressing Ctrl-Alt-Del and selecting Task Manager from the list provided. Expand the Power BI Desktop Group under the process tab (Detailed information about these processes can be found at the end of this post.)
- Wait for the report CPU % to approach zero, which means that it has completed loading it into memory. See this sample report on the blank page.
- Next, navigate to one of the pages where memory consumption is very high due to an underperforming DAX formula.
- Once you’ve optimized DAX formulas that were causing the memory spike, you can do a side-by-side memory comparison between the initial version and the optimized version.
Repeat the process as you build new Power BI reports or rework slow reports will allow you to minimize memory usage and get the most out of your premium capacity.
The associated processes are defined below:
- This process allows Power BI Desktop to use the Microsoft Edge rendering engine to display web contents in the native app.
Console Windows Host
- Microsoft internal process
Microsoft Mashup Evaluation Container
- The Power Query Engine
- It is responsible for processing all the steps in the Power Query Editor, which gets data from my sources, transforms it and then loads it into my data model.
Microsoft Power BI Desktop
- This is the starting point executable and container for all the processes run within Power BI Desktop
Microsoft SQL Server Analysis Services
- The analytical data engine
- This is where all the data gets loaded from Power Query into the data model.
- This process can have the highest memory usage.
- An expensive DAX measure that must get most of its data from the storage engine will see an increase in memory utilization and CPU during the evaluation and running of the DAX measure.
Power BI Performance Savings
Now that we know how to identify the amount of memory used when carrying out Power BI reports, we can optimize and measure with confidence and prevent increased costs to our organization by staying within a Power BI Service tier.
DesignMind has a skillful team with years of expertise in designing, creating, and building Power BI solutions for companies of all sizes. You can find more tips from our Power BI professionals here. Enhance your Power BI reports and read more about our collection of Power BI services or reach out to us to set up a free consultation.
Raul Amez is a Senior Business Intelligence Consultant at DesignMind.