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?

[AUDE]

@sfog

May I sak You, where the parameter BasXmlGzip has to be adusted?
Thanks!

[sfog]

@AUDE, you will find the settings in 'Advanced options' when you get data from SAP BW.

Regards

[sfog]

Hi - just letting you know that Microsoft released the June gateway update. With this upate I was able to refresh a data source that uses the new SAP BW connector/driver.

Regards

Stephen

[AUDE]

Hi
I was testing the new Version 2.60.5169.3201 64-bit (Juli 2018) with new connecting features.
Unfortunately the situation hasn't changed:
- TSV_TNEW_PAGE_ALLOC_FAILED
- other overflow messages
Could somebody tell me something like a best practice?
Thanks!
Regards, Dietmar

[HrabinaJan]

Hi AUDE, the performance really did improve, but only simple visuals with high aggregation work for us. If we try to make a real report, we got 30 sec response times at best. As a result, we are actually considering switching to SAP Analytics Cloud. We tried to export CSVs with ABAP, but it really does not work well and is no good solution anyway (not to mention it is a license violation for indirect use).

[AUDE]

Hi HrabinaJan,

Thanks for Your helpful experiences!

Another issue:
The "DirectQuery" compared with "Importing" works better, but doesn't allow using infoobject attributes.

May I ask You, witch paramters You are setting?

Power BI:

- Importing

- DirectQuery

- !92AIY!1.0 (NetWeaver RFC)
- 2.0 (.Net)

- BasXml

- BasXmlGzip
- DataStream

- Batchsize

Special SAP BW / RSRT Query adjustments?

I tried a lot of combinations but really can't find a different behaviour. 

Beside this, I guess infoobjects with a huge amount of attributes are definitely impossible to work with while working with "Importing" mode.
So if You really want wo work with Power BI on SAP BW, it is to build s special data model?

Thanks and regards!

[HrabinaJan]

Hello AUDE, you are correct, import does seem to have much worse performance. 

We use DirectQuery,

Without any BW / RSRT adjustments.

I do not know how to find out the parameters of SapBusinessWarehouseExecutionMode (I is not displayed in standard M code in the advanced editor for Import) or Batchsize, could you explain, please?

Environment specification:

Net Weaver version 7.40 (SAP GUI) - we do not like the new look of 7.5 icons, lots of menus got rearranged etc. so we stick with 7.40.

However, RFC SDK library (prerequisite for BW API or HANA DB connector) is version 7.50.

On local clients, we use BW API connector because I do not model HANA VIEWS yet, just using existing BEx queries. On the server, the Net Weaver version is 7.5 / SPS06; HANA DB version is 1.00.122.17. We run DB server on RedHat 6.9, kernel version 7.53.

Hope that could help.

[Anonymous]

Guys I'm back with a new issue that were struggling to identify the issue. Over the weekend we upgraded to SAP BW 7.5 on Hana. Everything seems to be working fine with the exception that some of our Power BI Reports can't refresh the data. 

I'll start by saying that I'm still using the version 1 connector, however, we have tested the version 2 connector and it is working. I assume this is due to the gzip mdx interface and the batch size paramter. That said i'd still like to figure out what version 1 is no longer working. Hoping that someone has some ideas. 

Keep in mind this is not every query, only certain ones. Also note that were not talking about a lot of data. Some of these structures only have a couple 100K records in them. When running these reports in ABAP web they run within seconds. In BEx Analyzer they typically take a bit longer (1-2 min). And when viewing the technical propertes of the queries in RSRT the numbers look great. 

But power BI Keeps throwing the following errors. Keep this in mind, when we kick off the query we are doing a trace on the query. From the BW side all indicates say that the process has completed, we can see the number of recordes that have been pulled and it's attempting to send the data. In the Power BI loading window my status is 'Loading Data To Model' (Or what ever it says) and then we get the following. 

So were trying to figure out if this is an SAP Issue, a Power BI issue, or a potential network issue. 

Thoughts? 

[sfog]

Hi @Anonymous

Could you create a new post for your upgrade/refresh issue. As you mentioned - this is a new issue and this post relates to BW/PBI performance.

Regards  

[Anonymous]

Just an update to the previous issue I posted we resolved this by moving to the new .Net Connector. After using the .Net connector for a couple of weeks now I wonder why we didn't upgrade as soon as it came out. With the .Net connector our reports are refreshing 5-10X faster than the old method. One particular report updates in 2 min now which was originaly taking around15 min to refresh. 

As such, if you're still having performance issues connecting to BW I would recommend connecting utilizing the .Net Connector. 

AUDE

Regarding your issue with 'TSV_TNEW_PAGE_ALLOC_FAILED', we also had this issue in the very begining. I don't recall what our Basis did but they did make a change that made this issue go away. If I can recall correctly there was a T-Code that Basis when to and updated the following 2 parameters:

abap/heap_area_nondia

ztta/roll_extension.

I believe we had these as the default values and once updated this issue went away. Please check out this post but I would say that this is a Basis issue

https://wiki.scn.sap.com/wiki/display/SI/TSV_TNEW_PAGE_ALLOC_FAILED