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?

[jithinjoseph]

We were also facing the exact same issue. Connecting to SAP BW Infocubes and Bex Queries were taking for ever. Load window is showing DataSource.Erro: SAP Business Warehouse: Error occured when starting the parser: timeout during allocate/CPIC-CALL:'ThSAPCMRV',

SAP BW Release 740 SP Level 0008

Power BI Version: 2.42.4611.701 64-bit (May 2017) which is the latest version of Power BI.

[adeel23]

We are also facing the same issue. However when we connect via Hana DB, we see significant improvements. So I am wondering if there are any tweaks we can do on the SAP BW system in terms of the GW Connections or CPIC parameter changes.

[krishnateja]

I’m also trying to find out if I can build PowerBI queries on SAP-BW master data objects (i.e. standard SAP-BW master data object 0MATERIAL). It seems the only way to do this is to first create an SAP-BI query that references this master data object, add all the fields needed, save and then it can be visible in PowerBI info provider listing. I was hoping to avoid the entire step of creating a query and just do a direct connection to the object itself. Is there a way to accomplish this approach?

[jeevanthi]

SAP Business Warehouse: Error occured when starting the parser: timeout during allocate/CPIC-CALL:'ThSAPCMRV',

http://eonlinetraining.co/course/sap-basis-online-training/

[mvpeterman]

I'm also having the same issues. I'm receiving the following error on a query that shouldn't take a lot of memory and I'm not even trying to bring it all into Power BI, like the original post describes.

DataSource.Error: SAP Business Warehouse: RfcInvoke failed (RFC_ABAP_RUNTIME_FAILURE): TSV_TNEW_PAGE_ALLOC_FAILED

Do we know why this is happening or when it will be fixed?

Thanks

[Anonymous]

Jithinjoseph and Jeevanthi and mvpeterman, I had basis watch the system while power bi was trying to pull in data and basis ended up increasing the memory. I don't believe they increased the memory on the actual server but there was a config setting (I believe) that addressed the issue. Again, I worked with basis on this. I know this dosen't give you a specific answer but basis was huge in this regards.  

Krishnateja, unfortuntially you'll have to create the query to pull the data directly from SAP BW. If you were on BW on Hana then you could expose the data as a view. In my opinion SAP BW sucks and is not a true datawarehouse and this makes working with SAP BW painful. However, Power BI over the last couple of releases has addressed some issues. Being able to pull Key/Text and attritbutes is huge and I'd like to think that the performance has also improved. But back to your master data question, create the query, create it once and then reuse that query everywhere material is needed as a dimension. One thing I'll point out is that you have to add a measaure to the BEx query. In our case the only measure was 'Number of Records' but if you don't add at least 1 measure Power BI will error out trying to pull in the data. I looked at the MDX it was creating and it seems like there is a lot going on. I'll point out that you can run the query in BEx Analyzer without the measure but Power BI will fail. Once added everything works. 

[sfog]

Hi

I just logged a similar issue (only saw this issue after I logged mine).

Refer to the issue "Power BI consumes too much memory (or all the memory) when refreshing SAP or SAP BW Data".

After doing a detail analysis in the SAP Backend, while refreshing a SAP BW/BEx query from Power BI and SAP BOBJ Analysis for office (Excel Add-in) I saw that Power BI (a) consumes a lot more memory and (b) continues to consume all the available SAP's extended memory. The latter impacts on our SAP BI production system.

[Anonymous]

I saw that someone posted so I thought I'd post an update. 

We're actually experiencing better performance now. I believe that since the original post the SAP BW connector has had an update (or 2) and Basis changed some settings (I don't know exactly what they did). 

To comment on the sections above

Data Load is Very Slow

While we aren't loading data at a very quick rate the volumn of data does impact our load time but we're no longer timing out unless we try to consume way to much data (3+Millions of rows). At that point my question to the users is why are we trying to consume that much data. Usually the don't need that much and we are able to filter on the data to reduce the data volumn. In other cases we've created a new strucuture that aggregates our data to redcue the volumn of data.

We've also seen a huge improvement with the ability to select the text/key values for each field. But I have noticed that you have to be selective in what you select when selecting your fields. In my case I will only select the text and the key value for the dimensions. Ocasionaly I'll select some of the attributes. But you have to expand the Properties and select only the ones you need. If the case below if I were to select Plant at the top level it would select every field and this does lead to performance issues and timeouts. In the case below I exapanded Plant and selected Plant Level 01 and the Key (I know key is out of order in this pick, i merged to images to show this). 

Column limitation (selecting to many columns) 

Now that I can select attricture and have been doing the way described above I've not ran into any issues with selecting to many columns. The key is I only select what I need. I have had queries where I've 10-20 columns without issue. The other thing that I have been doing is creating queries on master data and bring in those tables seperatly and then not brining in the key/text in the transaction data. In the example above I would have loaded Plant as a seperate query and then joined Key to Key from the master data and the transaction table. 

Key and Text Values

With one of the updates a while back this funcationality is now avaiable and I beleive is one of the reasons why the performance has improved. This has been a huge help. 

All in all the issues I originally posted about aren't causing us issues anymore. We do create a BEx query for everything we import and we don't connect directly to cubes (Performance sucks). None the less I believe the SAP BW connector has had several updates to it that has allowed us to leverage this connector more so now than we were in the past. 

In regards to the errors above, I beleive this is where basis made some changes. Basis was able to run a trace and then we would try to import data into Power BI. From this basis was able to identify a point of failure and made changes to address those. I don't recall what they did off the top of my head but they may of increaseted teh amount of memory that BW was allowed to allocated (Not memory on the physical server but a config setting in BW GUI). If memory servers me correctly (No pun intended) we had the default setting which was not alot. Actually after updating this config setting our BEx Queries started to perform better as well. I'm neither SAP or Basis but I did work directly with them to correct the issue. 

Hope this helps someone. 

[AUDE]

Hi

I'm also having the same issues.

With small data volums, there is now problem.
As far I am adding a charcteristic with many records, for example 0MATERIAL to the PowerBI query, the issues starts.

In my oppinion the connection is not sophisticated.

The question is, what is the answer of Microsoft / SAP in improving the RFC SDK connectivity?

Is it politically wanted, to work thith SAP BW / Power BI?

I would like to point out, that Business Objects also works within MDX and it works properly.

Regards

[sfog]

Hi

I have just posted an idea and hope that we can get some traction in solving this issue.

I agree, the conenctor/conenction is very poor in terms of performance and memory consumption.

When you say BOBJ works with MDX, which BOBJ tool are you referring to - universes? BOBJ tools, from BI 4.0 onwards connect via the BICS connector. Prior to using BICS (XI 3.x) performance with BOBJ over SAP BW was very poor.

Lastly, if you are using Power BI over BW and you are only interested in master data dimensions, without key figures - you may also experience extremely poor performance. To solve this, make sure you include at least one key figure.

Regards