Transformations: aggregation

[alugunov]

Hello Community,

I have a question about transformation of one entity to another, how it is better to implement in CAP?

Case: we have entity e.g. "Costs", where we have some attributes like "date", "month_year", "costType", and fields with amounts: "amountN", "amountT", "amountG". We save daily based amounts. This entity is persistent in DB and exposed as projection in Service.

Now, we need new Entity, "aggegatedCosts" which has aggregations grouped by "month_year", "costType" and summed to fields "sumN", "sumT", "sumG". This should not be persistent in DB. Also, we want to use Fiori Elements to build UI.

I see two options: 1) create new aggregation entity and use annotation Aggregation to aggregate fields. 2) Create new entity in service as projection on "Costs" and implement custom handler for action READ to aggregate in runtime.

Option 2 should work, not sure about option 1, will it work for multiple sum fields and groupby fields?

thanks and best regards,

Andrey

[gregorw]

Please check out how I did aggregations here.

db/schema.cds#L86

[former_member6443]

Hi Gregor,

If you are referring to the code below, How will this be deployed in HANA Cloud / HANA Premise as well i.e. via a CDS ?

@Aggregation.ApplySupported.PropertyRestrictions : true
view BooksAnalytics as
  select from Books {
    key ID,
        @Analytics.Dimension : true
        BooksAuthorsAssignment_ASSOC_Books.ASSOC_Author.name,
        @Analytics.Dimension : true
        BooksAuthorsAssignment_ASSOC_Books.ASSOC_Author.country.code,
        @Analytics.Measure   : true
        @Aggregation.default : #SUM
        stock,
        @Analytics.Dimension : true
        currency
  };

Regards

Afshin

[alugunov]

Hi Gregor, thank you, I will look into it.

[gregorw]

Hi Afshin,

the cds compiler translates it to HANA artifacts and it's deployed using a star file.

Best regards
Gregor

[former_member6443]

Hi Gregor,

Thanks for your prompt answer, I am not getting the results I was expecting a group by

Sorry to hijack this thread, perhaps I should open another one

entity EQUI {
    key EQUNR : String(18);
        ERDAT : String(12);
        EQTYP : String(1);
        OBJNR : String(22);
        ANSWT : Decimal(17, 3);
        MATNR : String(18);
        SERNR : String(18);
}

My View Definition is 
@Aggregation.ApplySupported.PropertyRestrictions : true
view vw_Equi as
  select from EQUI {
    key EQTYP,
        @Aggregation.default : #SUM
        ANSWT
  };

I was expecting a group by EQTYP when the view was build in HANA result is this.

VIEW erp_EAM_vw_Equi AS SELECT
  EQUI_0.EQTYP,
  EQUI_0.ANSWT
FROM erp_EAM_EQUI AS EQUI_0

@sap/cds: 4.2.4
@sap/cds-compiler: 1.43.0
@sap/cds-dk: 3.4.0
@sap/cds-foss: 2.1.0
@sap/cds-reflect: 2.13.1
@sap/cds-runtime: 2.5.5
@sap/hana-client: 2.6.58
Node.js: v12.19.0

Am I missing something ?

Thanks

Afshin

[gregorw]

Check out my test:

tests/analytics.http

This is doing the grouping.

[alugunov]

Hi Gregor,

I tried it out - but no effect, no aggregation has happened. I just got single values.

Should it work for v4?

[gregorw]

My Example is for OData V4. Have you tried my sample project?

[alugunov]

Hi Gregor,

ok, I got it. Thanks!

If I do normal GET without parameters to this Aggregated entity - it doesn't work.

If I do with parameter $apply=groupby... --> then it works! Cool! But I have two questions:

- how to add UI to this query?

- can I add multiple aggregate parameters? I tried to add one more -> doesn't work.

However...I would like to build view which I can access just by calling GET /AggregatedEntity

I have fixed groupby attributes as well aggregated fields.

Will it work with sql view or I have to implement custom handler?

best regards,

Andrej