This is a test message to test the length of the message box.
Login
ABAP CDS Aggregation
Created by Software-Heroes

CDS - Aggregation

4007

How can Core Data Services deliver the most value? The views shine especially with aggregations and code pushdown.

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


Included topics:
CDSCore Data ServiceAggregationCOUNTSUM
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP Tools - Work with Eclipse (CDS Analysis)

Category - ABAP

In the complex world of CDS views, it is important to have the right tool at hand to ensure an analysis of the structures and data sources.

08/25/2023

CDS - View Entity

Category - ABAP

In this article you will learn more about the new Core Data Service entities, what they bring and what makes them different from the old views.

07/29/2022

CDS - Learnings

Category - ABAP

In this article we summarize the learned content and show you the way for what you will need the CDS Views in the future.

06/10/2022

CDS - Virtual fields

Category - ABAP

This article is about virtual fields in Core Data Services and how you can subsequently deliver such complex data.

06/03/2022

CDS - Authority check

Category - ABAP

How and where are the permissions for a Core Data Service delimited? Check out this article for the details.

05/20/2022