Introduction
In this blog you will learn about the creation of Query CDS Views for Analytical purpose and their consumption in RSRT tool, along with the required steps that needs to be followed to achieve it. Once after the Analytical query creation, I have tried replicating one of my project scenarios to have better understanding on defining key figures/ measures in CDS views. In the end blog explains about the process needs to be followed to expose the given Query CDS view into OData service links. The Query CDS Views are created specifically for Analytical reporting purposes within SAP systems. They can be optimized for certain reporting scenarios, analytical queries, aggregations and further can be integrated with SAP FIORI tool providing much better visual reporting experience.
Creation of query CDS view:
For the creation of query CDS view we must follow 3 basic steps as mentioned below:
Steps to follow for the creation of query CDS view:
1. CDS Info Provider as source
We cannot use any standard R\3 DB tables as a source for the creation of Query CDS View. We can only use standard/existing CDS Views from the system that needs to be converted into CDS Info Provider. CDS Info Provider is called by the special annotation @Analytics.DataCategory: #CUBE. Here #CUBE means Info Provider. The significance of declaring your CDS View as cube because when we compare it with our BW terms, we will be creating report on top of Composite Provider, ADSO or Info Cube. Similarly, we need to convert our source CDS view as Info Provider to create the analytical report.
2. CDS Query view
Once after creation of CDS Info Provider view, create new CDS view by utilizing the source Info Provider CDS view as a data source which is ZCDS_CUBE from my context. Then start building our Query CDS View report as per the Business requirement and logic. Important annotations required for enabling the CDS as query view is @analytics.query: true and VDM.viewType: #CONSUMPTION. By declaring our CDS view as consumption CDS View, we will be able to derive all the required fields from our source CDS view without facing error.
Few important Annotations and their significance for the creation of the structure of our Query CDS View:
- @AnalyticsDetails.query. axis: #ROWS – Used for defining attributes to row characteristics in Query Browser.
- @AnalyticsDetails.query. axis: #COLUMN- Used for defining key figures to column in Query Browser.
- @AnalyticsDetails.query. axis: #FREE – Used for defining attributes to Free characteristics in Query Browser.
- @AnalyticsDetails.query. sortDirection: #DESC or #ASC – For sorting the values of the defined rows in Ascending or Descending order.
- @AnalyticsDetails.query. display: #KEY_TEXT- Used for displaying the text of the key.
- @AnalyticsDetails.query. totals: #HIDE – used for displaying the totals.
- @AnalyticsDetails.query. formula: – To perform the calculations based on the Business requirements. We cannot see the formula calculations in data preview and can be displayed only when the query is processed in reporting tool.
- @EndUser.text. label: – Can be used for labelling the new field that will be derived from Formula.
- @consumption. filter.mandatory : True :- This annotation will give us prompts same as parameter.
- @Aggregation.default: with this annotation domain, you can declare the required field as a measure.
3. CDS Consumption in RSRT
Two major stages involved in consuming the CDS View as Query in RSRT transaction as explained below:
a. For each Query CDS view created, first COMPU ID will be generated in RSRREPDIR backend table along with the Info Provider CDS View name under Info Cube field and query name in COMPID field as highlighted below. Provide the SQL view name of your CDS view with prefix 2C in COMPID tab and you will end up in the below screen as shown:
b. One basic rule to consume the Query CDS view in RSRT Query monitor transaction. Use the prefix “2C+SQL view name” given in the CDS View.
Now let me try to explain you by replicating one of the issues I was facing in my project and thereby we will try to understand more about the the importance of Annotations related to Analytical report. In my source Info Provider CDS View ZCK_QUERY(structure provided below), I was calculating one field name called “calc” as shown below which populated the correct value as expected in the data preview.
But when I was trying to create report on top of this CDS View, the field “calc” had null values in the report, even after calling the required annotation as shown below.
After going through series of analysis and understanding on the basics of CDS View annotations, came to know that, as my field “calc” is measure/key figure in nature and values which got populated were not aggregated and thus causing error in my Query CDS View. In CDS View when such calculation happens on the key figure, it is necessary to declare the annotation @Aggregation.default: #SUM to derive the values from the source CDS View to Query CDS View.
Exposing CDS Query as OData Service:
One of my project requirements was to expose the query CDS View to OData service through sap gateway, so I got the opportunity to explore more about this OData services usage in CDS View and thought of sharing the required procedures in this platform.
Basically, OData Service is a standard web protocol used for querying and updating data present in SAP using ABAP/CDS. So, it is like a bridge between Backend to Front end systems like UI5/FIORI or any other integration systems. In this section let me try to explain to expose the given CDS View into OData service by activating the service link through Transaction code: /n/iwfnd/maint_service.
Usage of OData Annotation:
Declare the Annotation @OData.Publish: true in your CDS View as shown below and activate the CDS view.
Once after the activation of CDS View, a popup will appear near the OData annotation comment declared, which will show as “Service is not active”.
For the activation of OData service, get into the GUI system and follow the below mentioned steps:
1. Use the transaction code: /n/iwfnd/maint_service.
2. Click on the Add service tab in the Activate and Maintenance page.
3. Fill the System Alias option with “1. LOCAL” and External Service Name option with the “2. CDS Entity name + _CDS” and click on the “3. Get Services”. In my case ZCDS_CONS is my CDS entity name, so my service name will be ZCDS_CONS_CDS which will appear under the tab “4. Technical Service Name”.
4. Click on the service name been generated under the technical service name tab and provide the package assignment name as $TMP to store it locally where this service link will be generated. After providing the package name and then click on ok as highlighted below:
5. Next you will have the popup saying “Service XXX_CDS was created, and its metadata was loaded successfully”.
6. As a next step for the confirmation, come back to your ADT tool where you have called OData Annotation in your CDS View. Now the popup will show the OData service link for your CDS View as shown below:
7. Click on the OData -Service link and your OData server will appear like below:
8. To get OData service in SAP gateway client, use the option SAP gateway client option which is available in the maintenance service page. Here first search your activated OData CDS view with technical name and click on sap gateway client.
9. Once you enter the gateway client, click execute to display the OData service in gateway client.
10. To display the meta data structure of your OData service, use the $metadata in the Request URI.
The scope of these Query CDS views can be extended to display in SAP FIORI as well. For this you need to be aware of creating the UI5 App through UI annotations for Analytical Query CDS view.
