S4HANA ABAP CDS view provides a powerful way to model and consume the data residing in HANA database. It allows developers to create semantically rich data models which the application services expose to UI clients and various reporting tools. It is the central pillar of S/4HANA development and is used as the core technology in most of SAP’s programming models.
The focus of the blog is on consuming the ABAP CDS view in Power BI and we will be talking more on that.
For ABAP CDS views you can refer the below blog.
The ABAP CDS views are commonly used as data provider for tiles on the Fiori and thousands of dedicated SAP delivered CDS views are available to be consumed. We all know that CDS views can be used in SAP’s provided BI tools like SAP Analytics cloud, Analysis for office or WEB intelligence. Develop a consumption view with analytic query enabled and you are all set.
Wouldn’t it be great if we could consume the ABAP CDS views in other non-SAP tools like Power BI?
Solution:
As Power BI was the reporting tool used to consume the data from Azure Data Lake (ADL) we had to use Power BI to report the data directly from SAP CFIN. The solution was to build the ABAP CDS view and consume it in Power BI.
The extractor 0FI_ACDOCA_10 is built on table ACDOCA (Universal Journal Entry Line Items) so we had to look for existing CDS views that can be used to consume the data. We got the existing Interface view “I_GLAccountLineItemRawData” consuming data from ACDOCA which satisfied our requirement. The Composite view of type Cube and Consumption view (with composite view as source) was built to be consumed in Power BI.
Below links will be helpful in identifying the SAP Delivered ABAP CDS views:
How to find CDS views by Just knowing the field Name in SAP
How to Search for SAP Standard CDS views
Let’s discuss now on establishing connection between CDS view and Power BI.
Exposing the ABAP CDS view as Query and use the SAP Business Warehouse as source in Power BI.
As you know in S4HANA system we have embedded BW on HANA system available. Check the below link for S4HANA embedded BW:
Demystifying SAP S4HANA Embedded BW
The first step is to add the following annotation to you consumption CDS view:
@Analytics.query: true
As seen above we have defined CDS View “ZCDSV_C_Q_FI_ACDOCA” as Consumption view with annotation “@Analytics.query: True”. The SQL View Name for the CDS view is ZFIVACDOCA.
The CDS view of type Query can be access in TCODE – RSRT (Query Monitor) as seen below:
2CZFIVACDOCAS – The source and
2CZFIVACDOCA – The SQL view name of the CDS view
It comes in the same format as any other SAP BW query when given as input in RSRT.
Once checked we are all set to view the ABAP CDS view in Power BI as well.
Login to Power BI and select Get Data ; Provide SAP in the filter section and you will get 3 options as seen below:
Either select “SAP Business Warehouse Application Server” or “SAP Business Warehouse Message Server”. We have selected the SAP Business Warehouse Application Server and click on connect.
Give the Server, System Number and Client ID; select Direct Query and click ok.
Once the connection is established you will see all the available ABAP CDS consumption views of type Query. You can filter on the Source by giving the source name and select the Query created on the source.
Once the query is selected, you will get the prompts defined in the ABAP CDS view. Provide values in the required prompt and you are all set to view data and carry the analysis in Power BI
Other way to expose the ABAP CDS view to Power BI is by enabling it for ODATA.
Exposing the ABAP CDS view to OData and use OData as source in Power BI.
SAP OData is a standard Web protocol used for querying and updating data present in SAP using ABAP, applying and building on Web technologies such as HTTP to provide access to information from a variety of external applications, platforms and devices.
The first step is to add below annotation to your consumption view:
@OData.publish: true
Once CDS view is saved and activated, the service will automatically be created. They can be used in Power BI for Data Import and not live data. The technical name of this service will be <cds_view_name>_CDS.
The OData service must be configured in SAP.
Go to transaction /n/IWFND/MAINT_SERVICE, and click on add service:
Give the System Alias and Technical Service Name “ZCDSV_C_Q_FI_ACDOCA_PBI_CDS” (CDS view Name followed by _CDS) and then click on Add Selected Service.
The service will be added:
You can click on Call Browser or 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 successful, 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, we can consume the ABAP CDS view in Power BI. Login to Power BI and click on Get Data and select OData from the available options.
Enter the Odata URI and click ok.
Provide the required Authantication for login and save. Select the entity name in the Odata Service.
That’s it, now you can use data provided by Core Data Services right in your Power BI reports.
Summary:
CDS-based SAP S/4HANA extraction keeps evolving and its connection with Non-SAP tools will too. I hope this blog post provides a useful guide on how to consume your ABAP CDS views in Power BI.
