CDS - Aggregation

Table of contents

• Sum
• Calculated fields
• Functions
• Performance
• Conclusion

Article update: Since release 7.57 (S/4 HANA 2022), DEFINE VIEW is marked as obsolete, you should use DEFINE VIEW ENTITY instead. These may differ from the examples in some places. You can find more information about the new views in this article.

---

In this article, we take a closer look at aggregation, the true superpower of Core Data Services. Furthermore, we go into the linking of such views with other data and what effects it can have on performance.

 

Sum

Read the sum per partner and material from the database on-demand? What used to require a lot of performance or auxiliary tables can be done in milliseconds with a HANA database. Here is an example view that calculates a total for us and uses the association directly:

@AbapCatalog.sqlViewName: 'ZBSCDMOPAMATSUM'
@EndUserText.label: 'Sum for Partner and Material'
define view ZBS_C_DmoPartnerMaterialSum
  as select from ZBS_I_DmoPosition
{
  key _Invoice.PartnerNumber,
  key MaterialNumber,
      @Semantics.currencyCode: true
      PositionCurrency,
      sum ( PositionPrice )  as PriceForPartnerMaterial

}
group by
  _Invoice.PartnerNumber,
  MaterialNumber,
  PositionCurrency

 

We have redefined the key fields in the view and form our new key. We also use an association to get the partner number in the header of the document. Since we are forming the sum, we have to define a grouping afterwards. In the next step we also define a view to determine the sum per partner:

@AbapCatalog.sqlViewName: 'ZBSCDMOPARSUM'
@EndUserText.label: 'Sum for Partner'
define view ZBS_C_DmoPartnerSum
  as select from ZBS_I_DmoPosition
{
  key _Invoice.PartnerNumber,
      @Semantics.currencyCode: true
      PositionCurrency,
      sum ( PositionPrice ) as PriceForPartnerMaterial

}
group by
  _Invoice.PartnerNumber,
  PositionCurrency

 

Basically, only the material field is missing and the grouping has been adjusted. You can then look at the result in the data preview and only get one line for each existing partner:

 

Calculated fields

Filtering via a calculated field is not possible in the view in which the field is created/calculated. To do this, a further view must be implemented, where the restriction can then be carried out. For the example from the last section, we have mapped another view for a count:

@AbapCatalog.sqlViewName: 'ZBSCDMOPAMATCNT'
@EndUserText.label: 'Count for Partner and Material'
define view ZBS_C_DmoPartnerMaterialCount
  as select from ZBS_I_DmoPosition
{
  key _Invoice.PartnerNumber,
  key MaterialNumber,
      count( * ) as NumberOfDocuments
}
group by
  _Invoice.PartnerNumber,
  MaterialNumber

 

We are now merging these two views into a new statistical CDS, in which we receive information per partner and material. In this case, however, we are only interested in data records with more than 10 documents and less than 100000 total.

@AbapCatalog.sqlViewName: 'ZBSCDMOSTATPARMA'
@EndUserText.label: 'Statistic for high performer'
define view ZBS_C_DmoStatisticParMat
  as select from ZBS_C_DmoPartnerMaterialSum   as Combine
    inner join   ZBS_C_DmoPartnerMaterialCount as Numbers on  Combine.PartnerNumber  = Numbers.PartnerNumber
                                                          and Combine.MaterialNumber = Numbers.MaterialNumber
{
  key Combine.PartnerNumber,
  key Combine.MaterialNumber,
      Combine.PositionCurrency,
      Combine.PriceForPartnerMaterial,
      Numbers.NumberOfDocuments
}
where
      Numbers.NumberOfDocuments       >= 10
  and Combine.PriceForPartnerMaterial <= 100000

 

The complexity of the CDS View increases with each additional layer, but you should also keep an eye on the performance, especially when it gets even more complex.

 

Functions

You can use the following aggregation functions in Core Data Services, with the first two being the most common:

• SUM - Determination of the sum after grouping
• COUNT - Determination of the number of records
• AVG - Average of values after combination
• MIN/MAX - Smallest and largest value in the column

 

Performance

Compound fields or calculated fields have a problem, filtering on this field does not have the best performance and can make access quite slow if it is the only filter criterion. The background is that in the first step the database must perform the operation on all data records in the database before the data volume can be restricted. With a standard field there is no problem, the amount of data can be read appropriately from the database.

A good example is first and last name merged into one field. The selection by search field using the full name is very poor in terms of performance in contrast to filtering using the individual fields.

 

Conclusion

Determine the total per document or simply read the number of documents per partner? No problem with the aggregation functions for Core Data Services. In old reports this was done in a loop, on HANA you can let the database do it.

 

Source:
SAP Documentation - Aggregation