Aggregation using CDS view for any given slice and dice in odata -- Part 1

[AbhijeetK]

Introduction

There are many blogs on aggregation using CDS view or without CDS view, even there is topic called embedded analytics where in all KPI, Charts and aggregation is achieved and using KPI modeler one can consume all these functionality.

All you need is the understanding of the annotations and the CDS views (most of the part) and yes if you haven’t started digging on that, start now, don’t be left behind.

https://blogs.sap.com/2019/02/12/dont-be-that-team-why-cds-views-are-the-new-abap-objects/.

There are already many blogs available for embedded analytics, cds views with analytical capabilities and stuff, which has in-depth information. I will be mentioning those in the references.

In this blog I will try to cover dynamic aggregation for any given dimensions and measures, similarly pivot table in ms excel sheet.

Prerequisites

Basic knowledge of ABAP CDS view, SAP Odata service, Cration of odata service using CDS view with reference data source.

Dynamic aggregation(for any given slice and dice of dimensions and measures) can be achieve either we can go with abap(group by) or CDS view.

And in CDS view also there are two ways either using SADL Layer in Odata or Analytical engine framework  which can generate the aggregations for you automatically.

This blog will basically give information about analytical application wherein for given LOB how odata service for charts and KPI single service can be use for aggregation any slice and dice of dimensions and measures.

Aggregations in CDS Views

So basically we can add analytical capabilities to a CDS view using two approaches --

1. Basic Analytical CDS View -> For simple analytical Fiori apps, which uses SAP SADL layer queries


2. Advance Analytical CDS view(Handled in Analytical engine ) -> For Complex analytical Fiori apps with annotation @analytics.query: true  that uses Analytical Engine (Cubes and stuff)

What is it about Basic and Advanced stuff..??

Basic means, you don’t need to have a view which has the annotation  @analytics.query: true and only basic calculations will work. The Query will be handled by the SADL layer (Dynamic ABAP Select query)

For Advance Analytical capabilities like exception aggregations, complex formulas, query browser app etc.., which the analytical engine (BW) provides, we need to go with the Analytical Query CDS view.

 

Advance Analytical CDS view

So Using Advance Analytical CDS view(Embedded analytics) in CDS view we can use annotation Analytics.datacategory and Analytics.query. And we need to put aggregation.default or default.aggregation annotation on measures so that in metadata of odata will clearly get to know which fields are dimension and which one are measures. For BW Analytical engine, what are advantage and how to use maheshkumar.palavalli will publish in next blog.

 

Aggregation using SADL layer

When we want to use single service for read(including analytical quires, KPI, Charts, Stories and aggregation) and the same service need to be use for CUD operations then it approach can be use.

So lets get started  --

first create CDS view according to business requirement using VDM concepts as below --



 

add annotation on measures as below --

 



 

So what have we got here is the annotation @DefaultAggregation : #SUM, this annotation makes that particular column as a measure and does the sum of that column data and #AVG will get the average value.

now in next step  create odata service using RDS(Refrence data source) which is best approach for generating odata service

once we introduce default.aggregation on measures, In metadata of service(which is created by RDS) all other fields become dimension as below --



 

Rest all properties become dimension in metadata as below--

 



now the given service will work as it is, except there will be one 'Analytical ID'  key get generated suppressing all the other semantics key, how to resolve this technical key into semantics key I will cover on later part, which is used for fetching single record, navigation from one entity to other, or updating and deleting  for given semantics key.

In Below Image .1 is aggregation Id followed by sales order(semantic key) then subsequent key 000001 is sales order item and at last PUSD is Parameter where in value is usd(currency conversion).

 



 

If we are  executing service then it wont give aggregation value but it will run as normal service

with all filters, count, inline count, pagination($top,$skip) depending on URL call

If we want aggregation(groupby) on given dimension then we need to call URL as below and it will give aggregation based on given dimensions for the measures.

 

sap/opu/odata/SAP/Service_Name/Entity?$select=Dimension1,dim2,dm3,measure1,measure2

In Below use case we are passing netwr and vkorg, so based on organisation dynamic groupby is result set is coming .



 

by using above url we can see for any given dimension and measures results will come in pivot(like in excel pivot is there, which can be used in different charts and  executive dashboard.

below is the results set --

Based on Sales Orgnization and division(spart) below is aggregation



Based on Sales org, division, and sales area--

 



 

Note:

•         If you pass all non measure fields, again the aggregation will not be applied, which is obvious.

 

• Always pass the aggregation annotations at the consumption view level as these annotations won’t be propagated from child view(interface,basic) to the parent view(consumption).

 

• Don’t create the CDS view with aggregations manually like SUM or AVG or COUNT and expose them as the OData service for the Fiori App. It will loose it’s charm, try to design it in a generic way using defualtaggregation annotations, so that it can be reusable in different smart controls.

Now again there are different cases

1.) Simple CDS view as above

In case of simple CDS view for navigation,update, delete or for single record fetch(using key) we need to use key, to resolve Analytical ID into semantics key there is already interface method we can directly used as below --

if_sadl_gw_dpc_util~get_dpc( )->get_keys_from_analytical_id(
EXPORTING io_tech_request_context = io_tech_request_context
IMPORTING et_keys = DATA(lt_keys) ).

this method is already implemented in DPC class.

2.)CDS view with parameter -- If  we are not using annotation default.aggregation and using CDS view with parameter then there will be 2 entities get generated, and in URL can be use as below --

URL : /sap/opu/odata/SAP/Service_Name/Entity(p_parameter='Value')/Set

But as we are using annotation default.aggregation so instead of Set there will be Results in URL as below --

URL : /sap/opu/odata/SAP/Service_Name/Entity(p_parameter='Value')/Results

In Next Blog maheshkumar.palavalli will cover Advance Analytics, after that if time permits I will try to cover CUD(Create, Update and Delete) operation using same service, with and without input parameters in CDS view, using currency and Unit conversion, Yeartodate, Monthtodate and quatertodate calculation using CDS view.

Feel free to comment and let me know if I miss something here or you would like to add something to this as I am also in the learning stage at this point.

Thanks a lot to maheshkumar.palavalli for making me understand difference between both approach Sadl layer and analitical layer

 

Regards,

Abhijeet Kankani