SAP BW Data Load Performance and Limitation Issues

[Anonymous]

We are trying to use Power BI to pull data from SAP BW leveraging both BEx queries and cube connections. With each scenario we are running into different issues and were trying to figure out if this is a Power BI Limitation/Issue with 1. Connecting to a Warehouse 2. They way that Power BI is communicating with SAP BW to pull its data or is this a SAP BW Limitation/Issue based on how SAP BW servers up data.

Tested With Cube:

0FIGL_C10 -General Ledger (New) Transaction Figures

Data Load is very slow

Adding new columns and refreshing data seems to take a very long time. I don't know if this is a sap warehouse issue, an OLAP issue, or a power bi Issue. I had our basis team monitor our BW environment when connecting to a cube where I selected all the columns. The cube contains 349,628 records which is based on generating the SQL Statement from the cube (In SAP BW RSA1) and executing that in SQL Server Management Studio. This call eventually failed with the following ‘RFC_ABAP_RUNTIME_FAILURE: TSV_TNEW_PAGE_ALLOC_FAILED which I was told was due to running out of memory. Prior to testing this I had Basis increase our memory footprint on the server in an attempt to rule out memory issues.

Column limitation (selecting to many columns) or possibly and combination of Columns + Rows

When I connect to a cube and select 1-4 fields everything is pretty responsive. But as you start adding more and more fields the performance starts to suffer. In testing it looks like we can add 12 fields without any issue (Super Slow Refresh Times) but once we hit the 13th field we get an RFC_ABAP_RUNTIME_FAILURE – TIME_OUT.

MDX

I don’t write a lot of MDX statements so my statement could be the cause of the issue but I run into the same issues remain. Which makes sense because I believe Power BI is connecting to SAP BW through the BW Open Analysis Interface OLBAP BAPIs regarless of if you going againt a Cube, BEx Query, or MDX.

This works:

Select   NON EMPTY [Measures].MEMBERS ON COLUMNS,   NON EMPTY   [0FISCYEAR].Members *   [0CO_AREA].Members *   [0COMP_CODE].[LEVEL01].Members   PROPERTIES     [0COMP_CODE].[20COMP_CODE]   on rows   from [$0FIGL_C10]   WHERE ([0COMP_CODE].[1100], [0FISCYEAR].[K12016])

Doesn’t Work

Select   NON EMPTY [Measures].MEMBERS ON COLUMNS,   NON EMPTY   [0FISCYEAR].Members *   [0CO_AREA].Members *   [0COSTCENTER].Members *   [0CHRT_ACCTS].Members *   [0CO_AREA].Members *   [0CURTYPE].Members *   [0FUNC_AREA].Members *   [0GL_ACCOUNT].Members *   [0PROFIT_CTR].Members *   [0SEGMENT].Members *   [0VALUATION].Members *   [0VERSION].Members *   [0FISCPER].Members *   [0FISCPER3].Members *   [0FISCVARNT].Members *   [0FISCYEAR].Members *   [0COMP_CODE].[LEVEL01].Members   PROPERTIES     [0COMP_CODE].[20COMP_CODE]   on rows   from [$0FIGL_C10]   WHERE ([0COMP_CODE].[1100], [0FISCYEAR].[K12016])

Key and Text Values

The last think I want to touch on is the key values. Power BI does not appear to give you the ability to select or toggle between the text and key values. Though MDX I was able to include the properties section and include the key value (Which was nice) but this should be standard functionality.

Tableau

I wanted to try and cover all my bases and rule out as much as I could to try and pin point the issue. As such I used Tableau to connect to the same structure as it’s my understanding that it also utilizing the BW Open Analysis Interface OLBAP BAPIs to query SAP BW. I’ll point out that I don’t really use Tableau but it was a tool that was available to use a test. Once again I had Basis monitor the SAP Environment to help track down any issues. Within seconds tableau had pulled in the Metadata and I was able to create a sheet. Tableau appears to provide a live query connection to the cube whereas Power BI appears to try and load the data first which may also be contributing to the performance issues. Again I just wanted to test with another tool to try and rule out the SAP environment.

With SAP being our ERP system we need the ability to effectively be able to query our data warehouse and provide the user community with self-service reporting/analytics. With the issues listed above its making this a very difficult process to move forward with. Any ideas or suggestions would be greatly appreciated. We love Power BI and the users who have seen our demos love what it can do and I feel like we are always installing this on someone’s machine.

Thoughts?

[sfog]

Hi

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"

I have also raised a support ticket with MS.

regards

[AUDE]

Hi sfog,

in my point of view, the backend technologie between BICS and MDX is not really differently. Only the face and the handling with BICS was improved.
The goal is the same, to bring data from cubes outside of BW. The same is it in between BW and Power BI / RFC SDK.

But the interaction between BOBJ and BW is better as the one between Power BI and BW, so hat has to be improved. 

Let me point to the RSRT and performance recommendations of SAP regarding query properties usinf BOBJ..

I tried it regarding Power BI, so the inactivation of chaches is counterproductive in comparision to BW-BOBJ.
Regards

[HrabinaJan]

@AUDE I definitely agree, I lost three months, worked with certified BI Microsoft consultants with no success. There is community request: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20157385-sap-bw-work-around-for-e...

[sfog]

@AUDE, @HrabinaJan - hang on, there will be new BW connector options coming out soon. I reviewed it last week and there was a huge improvement in both performance and memory consumption.

Look out for the next Power BI release(s).

Regards

Stephen

[HrabinaJan]

@sfog Thanks a lot! When can I expect this to be released exactly, please? My company needs to make a decision very soon. Thank you!

[AUDE]

@sfog

I installed now
Version: 2.58.5103.501 64-bit (Mai 2018)
It looks only a bit better now.
Looking forward to the June Version...
Thanks

[mvpeterman]

I've been working around this issue by creating MultiProviders, then querying off that so that I can do a direct query instead of importing the data into Power BI and it seems to be working a lot faster.

Thanks

[sfog]

@AUDE @HrabinaJan @mvpeterman, The June release is available for download. It provides a new driver for the SAP BW connector. This is a really good outcome for connecting to SAP BW - albeit it is in Beta and refer to my note on the gateway below.

I tested the following and received a 300-400% improvement in performance and more importantly - memory consumption.

Mode: Import:

Implementation: 2.0 (beta)

Excecution mode: BasXmlGzip with both 5,000 and 50,000 batch size. **Note: In my test cases, using the 5,000 batch size was marginally slower during the actual importing of data - as expected - but it used less SAP memory.

Important: I am still experiencing issues with this new SAP BW connector/driver with the Enterprise gatway. I updated the gateway software but I am still unable to refresh datasets that use the SAP BW driver. I am raising this with Microsoft.

regards

[lizaramlee]

try get the basis team to check the memory setting in sap BW. get them to check the RSMEMORY program.

i've been thru this issue but managed to overcome the data load issue. and of course, server memory n data size can also factor to it.

[AUDE]

@mvpeterman

Isn' t it the case, that a direct query in comparison with an importing doesn't allow the usage of the dimension (infoobject) properties (attributes)?

Thanks!