Friday 5 April 2024

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services

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:

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot1: Pictorial representation of Analytical Query CDS View

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.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot2: Source CDS view

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.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot3: Creation of Query CDS View

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:

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot4: RSREEPDIR Table view

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. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot 5&6: Query monitor and Result 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. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot7: Source CDS view for "calc" field and 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. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot8: Query CDS View structure and output result view for field "calc"

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. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot9: Corrected source CDS view and output result

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.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot10: OData annotation declaration for Query 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”.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot11: OData exposure popup

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.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot12: Add service tab

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”. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot 13: Add selected service view

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: 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot14: Create package for the service

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: 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot15: Activated OData service link reflecting in Query CDS view

7. Click on the OData -Service link and your OData server will appear like below:

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot16: OData server image
 
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.

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot17: SAP Gateway client

9. Once you enter the gateway client, click execute to display the OData service in gateway client. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot18: OData service in Gateway client
 
10. To display the meta data structure of your OData service, use the $metadata in the Request URI. 

Analytical Query CDS View creation and consumption in RSRT along with publishing OData Services
Screenshot19: Metadata structure view

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.

No comments:

Post a Comment