Custom OData Service Design for Analytical List Page Application

[Saswata89]

Hi Experts,

I was trying to create a Analytical List Report Application consuming an OData service. From the Analytical List report Fiori element documentation I got to know this type of application only works for analytical(?) and the OData service should have some specific annotations like - "aggregate" and "sap:aggregation-role=dimension/measure".

For experiment, I created a service(I'm on 7.4 SP012 on HDB) with only SPFLI structure and created a mapping from DDIC source SPFLI. I generated the SEGW project.

Then I also annotated my Entity and properties with "aggregate" and dimension/measure through MPC_EXT(redefining DEFINE method). I added "Generated_ID" as a key because I read from a blog that aggregations do not work without a generic key like that.

After all these, my metadata looks like below -

I also added the aggregation logic for the "FlightTimeSum" field on the dpc_ext IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS.

* Set the aggregation fields.
* Provide the measure to be aggregated as ‘element’ and the ABAP field name of the property that contains
* the result of the aggregation as ‘alias’.

    io_query_options->set_aggregation( VALUE #( ( element = 'FLIGHTTIMESUM' alias = 'FLTIME'
                                                  type    = if_sadl_gw_query_options=>co_aggregation_type-sum )
                                              ) ).

Now, when I run the get query I get the following results but no "Generated_ID" populated.

In another discussion segment of a blog from Joseph Berthe, https://blogs.sap.com/2018/01/12/fiori-elements-analytical-table-with-sadl/ I got to know the SAP given sources are okay to use as source mapping.

When I consume this service through FIORI I get lot of duplicated entries. I guess because the entity was having only one key "generated_id" which remained blank, caused the duplicities. No luck when tried to implement charts as well.

Now, I have three questions -

1. How can we populate the "GENERATED_ID"? With a correct design, this should be auto populated I believe. Am I missing any major part here?

2. Even if I somehow populate the generated_id and create a CUBE type of data model(one showed in this blog - https://blogs.sap.com/2018/03/18/create-an-analytical-model-based-on-abap-cds-views/ ) will I be able to do the aggregate function and render the charts like CDS Analytical views? (I'm asking this because when I created a CDS and tried to add "@Analytics" annotation it refused to take that, because of my version. It allows me to add @Analytics in 7.5 system.)

3. Is there any way around to develop a custom gateway service which will allow me to create an Analytical List Report? My actual business scenario demands a custom implementation.

Any leads regarding any of these questions will be appreciated. Am I messing up with SADL and Analytical concepts?

[thalesvb]

Hi, you probably have already found out by now... I've stumbled on the very same case and discovered the issue, so I'm answering for completeness, because my main System is 7.4 based and I don't see it changing anytime soon (SAP stack is based on it), and eventually I'll need this answer again on my future self.

Answer #1:

Indeed GENERATED_ID is auto-populated through CL_SADL_GW_ODATA_RUNTIME->_GENERATE_ANALYTICAL_IDS as a concatenation of dimension fields. You used DDIC source SPFLI but since it does not have on its DDIC structure a GENERATED_ID column, SADL framework silently abort ID generation.

I found out that while mapping a CDS view, I had to declare a dummy GENERATED_ID column on it to have it populated by SADL.

It is also important to declared GENERATED_ID field have enough size to store all dimensions concatenated with dot as separator, otherwise one day will happen a key duplication and you'll se again a "duplicated" entity on ALP app.

My SEGW project was very similar to yours, only difference being source data is mapped to a (plain) CDS view that has GENERATED_ID declared on CDS itself (as "cast('' as abap.char(100) ) as generated_id,", imported to SEGW, but no actual data mapping for it).

Answer #2:

Yes, it is possible, but all annotations that would be on CDS view must be "declared" through MPC class (aka the painful annotation coding). 7.4 doesn't support many CDS annotation and unfortunately there is no complete single list with what works and what does not; you'll need to find out which annotations are necessary and how it converts into metadata annotations. Some of them are way easier to put direct on annotations XML file of Fiori ALP project than coding it on MPC side. I found out this Help Page outlining them but is basically the same ones that that you already mentioned on question.

I think SADL is working OK on 7.4, I'm currently trying to do an ALP app, until now I only used Sum aggregation and it worked fine, displaying aggregated data correctly on Interactive Chart, also working OK selecting multiple values on first level then doing a drilldown about them. I'm still having issues to display a Visual Filter, but that is very likely some missing annotation somewhere else (the cons of having to manually declare them on MPC class)...

Answer #3:

You should be fine as answer #2 explained, but won't be a soothing ride as would be on 7.5, you'll have more code to write, and more dots to connect.

Kind regards