During my developments I found that analytical oData services (or maybe to be more precise "analytical entitysets" within oData services) are needed quite often but it's not very well documented what options can be used to create them. In most of the official literature you will find oData generated on top of "analytical query" but this option is not supported in Restful ABAP programming model and makes some stuff (like adding additional entitysets in the same service) difficult.
As a first thing let's explain "analytical oData" on a very simple example and probably most common scenario: you have some kind of document item which has for example Supplier, Company Code, Fiscal Year, Amount with Currency code and Quantity with Unit. And you perhaps need to get the "sums" of amounts and quantities on different levels
- per Suppliers
- per Company Code and Fiscal Year combination
- etc.
So your data is on the most granular level (document item) but you can pull aggregated data through odata service with totals on any level (or in other words for any combination of dimensions).
Typical UI consumers in Fiori / S/4HANA world for this would be:
- List reports with Analytical tables (with total at the bottom and subtotals on groups) - built either with Fiori Elements or smarttable with analytical table or just analytical table itself
- Cards in Overview Pages (perhaps having full total in header of the card and then subtotals in the chart below)
- Analytical List Pages (with charts for visual filters, chart in the middle and analytical table at the bottom)
- KPI tiles (generated through Manage KPIs and Reports apps)
- and more
To enable this behaviour in oData service, the analytical entity must be annotated with sap:semantics="aggregate". Now getting to the actual core of this blog - how to achieve it.
There are different ways:
1) oldest option for SEGW based oData services - adding annotations in *MPC_EXT class, redefining *SET_QUERY_OPTIONS method in *DPC_EXT class etc. This is pre-ABAP programming model for Fiori (not applicable if you use CDS view as referenced data source) so won't go into details but a bit more about this here:
2) stack of CDS views where the main things are:
- cube CDS view with header annotation
@analytics.dataCategory: #CUBE
and measure fields (like Amount or Quantity) annotated with
@Aggregation.default: #SUM
//or obsolete way of the same
@DefaultAggregation: #SUM
and then on top of cube having "query CDS" which is exposed as oData service - achieved by
@analytics.query: true
@OData.publish: true
This is in my point of view most often mentioned way of doing it (e.g. in SAP Press Books for Fiori Elements). However it brings some difficulties - because it's using oData.publish: true it's not easily possible to add additional entitysets into the same service - for stuff like value helps. And in general when using the "analytical query CDS" some of the annotations and approaches you are used to from usual Fiori Elements / smart controls development won't be working as expected (e.g. adding value helps for filterbar, linking text and id fields)
3) is very similar as option 2 just using new CDS view entities. Basically instead of Analytics.query: true annotation your query CDS will be using "provider contract analytical_query" notation
define transient view entity ZC_CDS_QUERY
provider contract analytical_query
as projection on ZC_CDS_CUBE
Interestingly when I tried to add this CDS into Service Definition (expose it as RAP) it gives error that it's not supported (S/4 HANA 2023 FPS1). So when you want to expose it as oData service you end up again with adding annotation oData.publish: true into the query transient CDS view entity.
And you have same problems as with option 2.
4) Not very well documented in my point of view and most flexible and convenient + compatible with Restful ABAP programming model.
Don't create "query CDS", simply put annotation for Measure fields (Amount / Quantity) what we used to call "consumption CDS" - CDS that will be exposed as the entityset.
@Aggregation.default: #SUM
In the same CDS (or the underlying CDS views) you can put any other annotations that you are used to (e.g. ObjectModel.textElement or Consumption.valueHelpDefinition or UI.lineItem)
It doesn't matter if you use older dictionary based CDS views or new CDS view entitities. Works with both.
And then you can either go with ABAP programming model for Fiori and add it in SEGW as "referenced data source" - you will notice that CDS is ticked as "analytical".
Or you can go with Restful ABAP programming model and add this CDS into Service Definition and expose it as RAP based oData service.
In both case you can mix the analytical and non-analytical entitysets (CDS views) in the same oData service and that means it's also possible to add transactional behaviour etc...
Based on my experience I would recommend using option 4 (without having analytical query) for:
◉ Analytical List Pages, Overview Pages and list reports with analytical tables
The actual analytical query (options 2 or 3) is required only for:
◉ KPI tiles done through Manage KPIs and Record - I noticed that oData services done through option 4 are not available to select as data source for the KPI - they must have analytical query in the background
It would be interesting to get some guidance from SAP or hearing about real development experience from the community. For something like ALP (Analytical list page) I saw only option 2 being used in tutorials but in practice option 4 makes development easier (and I actually saw standard ALPs using option 4). Is there any benefit of having analytical query? Is there perhaps difference in performance? I couldn't see any major difference. Interestingly we even noticed some issues in the ALP framework when we used option 3 (subtotals disappearing in the analytical table) and these issues are not there when we used option 4.
No comments:
Post a Comment