CAP: Aggregations

[kammaje_cis]

Hi,

I am trying to have an aggregated OData entity using CAP.

Business case: Every time the user accesses an app, it creates an entry in database entity 'demo.Actions'. Entity 'demo.Actions' has application and userId as elements. I am trying to find distinct user counts of an application.

First I tried the Annotations approach as below.

 @Aggregation.ApplySupported.PropertyRestrictions: true
 entity Actions as select from demo.Actions {
   key application,
   userID,
   @Analytics.Measure: true
   @Aggregation.default: #COUNT_DISTINCT
   @Aggregation.referenceElement: ['userID']
   1 as usercount: Integer
 };

The aggregation did not happen. 'usercount' always gave 1 as the value.

Next, I tried the SQL way.

    entity  Actions1 as select from  demo.Actions{
        key application,
        userID,
        count (distinct userID) as usercount
    } group by application

Now I get an error saying DISTINCT in functions is not supported yetCDS(<unspecified>)

CAP documentation is of no help other than a reference here. https://cap.cloud.sap/docs/guides/generic-providers#aggregation

Is there a way to achieve the aggregation I need?

[david_kunz2]

Hi hans-joachim.both ,

Maybe you can help out?

Thanks and best regards,
David

[AB · Accepted solution]

Using @sap/cds: 4.5.3, the following service definition serves a count of distinct UserIDs who interacted with an application grouped by object type and week.

service Statistics @(path: '/statistics') {
@readonly entity Interactions as SELECT 
  from my.Interactions { count( distinct UserID ) as UserCnt:Integer, ObjectType, Week }  group by ObjectType, Week;

Response is

{"@odata.context":"$metadata#Interactions","value":[{"UserCnt":75,"ObjectType":"BUS2007","Week":"2021-05"},{"UserCnt":75,"ObjectType":"BUS2007","Week":"2021-06"},{"UserCnt":75,"ObjectType":"BUS2007","Week":"2021-07"},{"UserCnt":73,"ObjectType":"BUS2007","Week":"2021-08"},{"UserCnt":40,"ObjectType":"BUS2038","Week":"2021-05"},{"UserCnt":44,"ObjectType":"BUS2038","Week":"2021-06"},{"UserCnt":41,"ObjectType":"BUS2038","Week":"2021-07"},{"UserCnt":39,"ObjectType":"BUS2038","Week":"2021-08"}]}

[AB · Accepted solution]

Business context: In this case we have an mobile application that writes a log in SAP ECC containing every user interaction. The mobile applications processes Maintenance Notifications and Maintenance Orders. This query provides a metric of usage - how many distinct users processed orders || notifications in a week. There are other services that provide metrics of usage - how many distinct orders || notifications were processed in a month| (ISO) week | day.

Fun fact: The usage analysis show us that Fridays are (statistically significantly) heavier processing days. Not sure why that is..

[fabian_krger]

countdistinct is no HANA function at all - but it doesn't throw an error (!) and is getting translated 1:1 into .hdbcds. By postprocessing I meant using a commandline tool like sed and replacing the countdistinct( with count(distinct again in the generated .hdbcds to prepare it for deploy.

[kammaje_cis]

wow... countdistinct is strange. I expected CAP to support this feature considering it is more than 18 months since it was declared as GA.

[fabian_krger]

I have the same problem. Not supporting COUNT(distinct ...) is really annoying. I talked to Daniel Hutzel at TechEd 2019 and he was surprised that it is out of scope and basically called it a bug/missing feature.

It also prevents people from migrating .hdbcds into .cds... .hdbcds supports count(distinct ...)!

I already thought about changing the code to countdistinct(...) which will be translated 1:1 into the generated .hdbcds and then postprocessing the .hdbcds by replacing countdistinct( with count(distinct before deploy.

[kammaje_cis]

Thanks for the reply. I did not know about countdistinct. Is it supported in .cds? How is it different than count (DISTINCT)?

[kammaje_cis]

I ended up creating 'custom logic', where I fetch non-aggregated data and aggregate.