Power BI consumes too much memory (or all the memory) when refreshing SAP or SAP BW Data

[sfog]

This issue is about Power BI‘s memory consumption when getting data from SAP or SAP BW. Basically there are 2 issues but they are closely related, so I mention both in this post.

1. Power BI uses too much memory. Solution: The amount of memory used, should be reduced
2. When accessing large SAP or SAP BW datasets, Power BI continues to use memory , until it consumes all available extended memory, and so impacts other users. Solution: The memory consumption should be controllable (we need a way of limiting the amount of memory that can be consumed, to avoid impacts to the live/production systems).

Here is an example of the Power BI memory consumption issue, compared to another tool

My test case is:

• I compare Power BI with SAP Business Objects Analysis for Office (the Excel Add-in) - both from my desktop.
• I run the same SAP BW/BEx query from PBI and AFO and get the exact same amount of rows and columns.
• The Power BI query has no additional steps (all it contains is Source, Navigation and Added items)
• I deleted the cache from Power BI. I also deleted the database cache in SAP for the BEx query I am using (t/c RSRCACHE).

This is the result (Power BI uses too much memory or simply consumes all available extended memory):

• I monitor memory consumption in SAP using t/c SM04 and ST02 for both tools, running the same BW/BEx query.
• Power BI, when selecting Edit query and ‘Refresh preview’, takes circa 10 minutes to present the data and consumes more than 500MB of extended memory
• Analysis for office (AFO) takes about 20 seconds to present the data uses 36MB of extended memory – which is less than 10% of what PBI uses)
• Power BI, when you close the query editor and select ‘Apply data’, takes another circa 10 minutes and again consumes 500MB of extended memory.

This is a rather small query, in relation to some of the queries we’d need to consume and report on when analysing data from SAP or SAP BW.

When Power BI is used on the larger, sales datasets, it consumes up to 10GB of memory and impacts other users in our production system as it continues to consume all the available extended memory.  Note: AFO consumed 400MB of memory for the same large query and for an even larger query, AFO will simply return a message “Result set exceeded” – which is how we protect the system from over-utilisation.

Notes on Import and Direct mode, related to this issue

The memory issue can be resolved in Direct mode but this presents the following issues:

• Performance is quite poor – as a result of the underlying BW data structures (this is not a Power BI issue). I therefore want to continue using the Import query approach as performance is great once the query is published in the PBI Service.
• There are known issues with Direct query and it is still in a beta release.

[v-jiascu-msft]

Hi @sfog,

I would suggest you file a support ticket here to investigate more.

Best Regards,

Dale

[sfog]

For those reading this and having the same performance/memnory issues with SAP BW - vote for this idea to enhance the SAP BW connector. In PBI ideas, search for "Improve SAP BW connector performance and BW memory comsumption"

regards