Improve SAP BW connector performance and BW memory comsumption

This idea is really about solving a performance issue with Power
BI‘s memory consumption when getting data from SAP BW.

Basically there are 2 issues but they are closely related, so I mention both in this idea.


1.When connecting to SAP BW, performance is extremely slow and Power BI uses too much SAP memory.

2.When accessing large SAP BW datasets, Power BI continues to use memory , until it consumes all available extended memory, and so impacts other users.


Here is an example of the Power BI memory consumption issue, compared to another SAP 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 from 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 500GB 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 500GB of extended memory.