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?
v-xiaoyan-msft
on:
Report jumps between two profile logins.
on:
Get External Data: Power BI semantic models throws...
Idrissshatila
on:
Timeline slicer is showing 30 & 31 March under 30 ...