Connection to SAP S4HANA Analytics CDS views

[EduOtero]

Hi all. I´m trying to connect Power BI desktop with our SAP S4HANA server, using CDS views (Core Data Services). This is the new embedded BW, with realtime views of the database.

CDS views are working very well using SAP Analysis for Office, and in Excel or PowerPoint I can connect to the server, open the views and work with them.

In Power BI Desktop, I connect to the server (Get Data, SAP BW Application Server connector), and get the list of CDS views. But trying to select any of them, I get always the same message: "The key matched more than one row in the table" (see picture below).

Anybody has seen this scenario working? Any idea?

Thanks in advance!

 

[hamsala]

As Lydia mentioned, ODATA service and the Power BI connector for Odata can be used.

Other option is generate a Calculation view for the CDS view and use the Calc view through the POwer BI SAP HANA connector. 🙂

[tomaskutac]

@hamsala

Please could you give me some more information or link to article about how to generate calculation view from CDS view? I am trying to find something but not really successful. Thank you very much.

[Anonymous]

It is possible but you have a few items to consider.

1.) Most CDS views exists as table valued functions in the SAP ABAP schema. They are not standard Tables or Views to that regard. A calcualtion view supports this as a data source but you have to map the Function's input parameters (if they exist) to a calculation view's input parameters.

2.) Some of the table valued functions use a the session variable SESSION_CONTEXT( 'CDS_CLIENT') which is dificult for users to set under an ODBC session using PowerBI. Therefore, you likely need to make a custom copy of each table valued function and replace that with the SESSION_CONTEXT( 'CLIENT') which is a standard varable that can be assigned to each HANA database user.

3.) You have to create and maintain the metadata (Column Lables) in the Caclcualtion View. This can take some time to setup.

4.) You have to manage the security on the HANA database but there are numerious ways to automate that.

--- Frankly I think it would be all around better to build a real-time data mart in a sidecar instances of HANA then to try and maintain raw CDS code within the database, wrapped with calcualtion views.

[hamsala]

Another option is generate a Calculation view for the CDS view and consume it through POwer BI connector to HANA 🙂

[v-yuezhe-msft]

@EduOtero,

Based on your description, you consume the CDS view data via the  SAP Analysis for Office  add-in in Excel.

In Power BI Desktop, I am afraid that it doesn't support to directly import data from CDS view using SAP BW APPlication Server connector. However, as per my reserach, CDS Views can be easily exposed as oData Service(API) which can then be utilized to fetch data out from SAP S/4HANA Cloud.

You can use OData feed connector in Power BI Desktop to connect to import data from CDS view.

Reference:
https://blogs.sap.com/2018/01/23/create-cds-views-in-sap-s4hana-cloud-and-consume-them-as-apis/
http://tiven.wang/articles/cds-to-odata-for-analytics/

Regards,
Lydia