Consuming S/4HANA CDS views in PowerBI
Consuming S/4HANA CDS views in PowerBI
With ABAP Core Data Services, SAP S/4HANA provides us a very powerful way to model and consume the data which resides in our HANA database. Because they are being developed in the ABAP application layer, CDS can offer a rich variety of capabilities like conceptual modeling and relationship definitions. Their execution however, takes place in the HANA database, resulting in great performance as well. For more information on Core Data Services, please refer to this user guide, since they will not be discussed in-depth in this read.
The most common use for these Core Data Services is for them to serve as data providers for tiles on the Fiori Launchpad. Thousands of dedicated CDS views are available to be used like that. It’s obvious that CDS views can easily be used in SAP’s own BI tools like Analysis for Office, Web Intelligence or Analytics Cloud. Just define your consumption view (cfr. the CDS user guide) as an analytical query and you’re ready to go.
But, wouldn’t it be useful if these views you already developed for Fiori, Analysis for Office or WeBI, could be consumed easily within other (non-SAP) front-end tools as well?
Yes, it would. Next to SAP’s own BI tools, third party applications can make use of the powerful CDS views as well.
To enable this, the Core Data Services have to be exposed as an OData provider. In PowerBI, you can only use this type of data source for data import, not for live data. SAP made it fairly easy to do this, but some extra configuration in is required.
In computing, Open Data Protocol (OData) is an open protocol which allows the creation and consumption of queryable and interoperable RESTful APIs in a simple and standard way.
Let’s have a look at how CDS views can be easily consumed as OData services in PowerBI.
The first step is to simply add following annotation to it:
@OData.publish: true
Thanks to this annotation, the service will automatically be created when saving and activating the CDS view.
The technical name of this service will be <cds_view_name>_CDS.
Next, the OData service has to be configured in SAP.
Go to transaction /IWFND/MAINT_SERVICE, and add the service as follows.
Go to ‘Add Service’:
Enter system alias ‘LOCAL’.
Enter the technical service name, which is the name of your CDS view, with suffix ‘_CDS’.
Once the service is added, you can test it in transaction /IWFND/GW_CLIENT, by using URI /sap/opu/odata/IWBEP/<cds_view_name>_CDS/?$format=json&sap-statistics=true.
If the test turns out to be succesful, your Odata service will be available by using following URI:
https://<server>:<ip>/sap/opu/odata/sap/<cds_view_name>_CDS/
Now we have the URI, alle we have to do is to use it as a data source in Power BIand import the data:
Enter the Odata URI and, if necessary, your SAP credentials:
That’s it, now you can use data provided by Core Data Services in ABAP/HANA right in your Power BI reports! Of course, you can now use this OData service in tons of other tools as well.
Blog by Joachim Dewaelheyns
