Connecting to a SAP Cube via Dataflow (Power Query)

[zenisekd]

Hi, 

I am looking for some guidance. 

Our customer has SAP and  SAP BW. Now he wants to do some reporting. But he doesn't want me in, so I have to use a gateway to pull data to Power Bi service via dataflow. I managed to turn on the ODBC connector and SAP BW Application Server connector. 

I was asked to do some reporting over few cubes, but I cannot make them run. When I connect to ODBC and look up the table (Z_ERT_M01) I get the following error.

I guess because it is cube that somehow materializes after some parameters are filled, it won't display this way. 

Then I tried to connect with the SAP BW Application Server connector, but when I open infocube, where it is supposed to be located, I cannot see it. 

However, if I go via power query steps manually, I can see it there

However, when I open it, it says the cube is empty. 

And now here lies my big problem. I have no idea, how to materialize the cube, if I am in the online power query steps. If I load some cube attributes with data, natively, it generates these steps in advanced editor "cube.transform". However, I don't know what measures and dimensions are in the cube and I don't know how to use the command. In the Power BI desktop I know about the Cube Tools, which let me add measures and attributes, but I don't see that in the online Power Query. 

I just wish I could load the raw data used for the cube in one large flat table and then play with what I need. But I am not sure if that is possible.

Any suggestions on how to approach this? Cubes are new to me and so far I avoided them even in desktop... 

[Anonymous]

Hi @zenisekd ,

First Power BI uses DirectQuery to connect to SAP BW multidimensional datasets, which means it queries the data directly from the source without importing it. You can check out this documentation: DirectQuery and SAP Business Warehouse (BW) in Power BI - Power BI | Microsoft Learn

Since you have already tried to use the SAP BW Application Server connector without success, make sure that you have followed all the necessary steps and prerequisites. This includes installing SAP .NET Connector 3.0 and ensuring that the Power BI Desktop installation version matches the .NET Framework Connector version (32-bit or 64-bit).

Power BI interacts differently with multidimensional datasets, especially in Power Query Online, than with traditional relational data sources.Power BI handles multidimensional dataset dimensions and metrics differently, and these dimensions and metrics are not directly editable in Power Query Online because they are not directly editable in relational databases or in Power BI Desktop.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zenisekd]

Hi @Anonymous , 

 

I am not sure your first statement is true. I am able to use online power query in data flow to connect to SAP BW application server, load some attributes and measures and then do further adjustments to it. Direct Query behaves differently. 

I did connect to SAP BW application server with partial success, but I see some cubes but not all at first sight. Why?