Cascading Data Services

Cascading Data Services are my synonym for a cascade of CDS-views and in this blog-post, i want to show problems, that can be solved elegant with a cascade of CDS-views.

The problem

In many applications we have unaggregated key figures mixed with aggregated key figures. We consider in this blog-post a query from the flight model, which should show the profit from every flight. The first key figure are the total costs, that the airline has to operate the flight. The second key figure are the prices the customer have paid (earnings). These prices are aggregated (sum) and the difference between total costs and aggregated earnings is the profit.

The database model

The database model consists of the table SBOOK containing the earnings from the customer.

The operating costs are stored in table ZFLIGHT_COSTS.

Solving the problem with OpenSQL joins

When we join the tables SBOOK and ZFLIGHT_COSTS we must use either an aggregate function for zflight_costs~costs or must add this field to the group by-clause like in the code sample below.

  SELECT c~carrid, c~connid, c~fldate, c~costs AS operating_costs, c~currency, sum( b~loccuram ) as earning
    FROM zflight_costs AS c INNER JOIN sbook AS b
    ON b~carrid = c~carrid AND b~connid = c~connid AND b~fldate = c~fldate
    INTO TABLE @DATA(flight_profits)
    GROUP BY c~carrid, c~connid, c~fldate, c~currency, c~costs.

Personally i’m not a friend of this solution, as the SQL statements tend to become unmaintainable for real business applications with a lot of different key figures.

Solving the problem with Cascading Data services

The problem can be solved with CDS-views in a more elegant and clear way.
For the aggregated key figure we just need to define a separate CDS-views. The following listing shows the CDS-view for the earnings.

@AbapCatalog.sqlViewName: 'zflight_ear'
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Flight earnings'
define view zflight_earnings as select from sbook {
    key carrid,
    key connid,
    key fldate,
    sum( loccuram ) as earning,
    loccurkey as currency
} group by carrid, connid, fldate, currency

The CDS-view ZFLIGHT_EARNINGS can be joined with the table ZFLIGHT_COSTS in an another CDS-view.

@AbapCatalog.sqlViewName: 'zflight_prof'
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Flight profit'
define view zflight_profit as select from zflight_costs as c
  inner join zflight_earnings as b on b.carrid = c.carrid and b.connid = c.connid
  and b.fldate = c.fldate {

  key c.carrid,
  key c.connid,
  key c.fldate,
  c.costs as operating_costs,
  c.currency,
  b.earning
}

This is the cascade of CDS-views i mention in the introduction.

Conclusion

Building a cascade of CDS-views can be helpful for building queries in a reusable and clear way. More information about CDS-views can be found in the following books:

• Practical Guide to SAP Core Data Services (CDS) (Espresso-Tutorials, english, covering HANA-CDS)
• SAP-Praxishandbuch ABAP Core Data Services (CDS) (Espresso-Tutorials, german, covering ABAP-CDS)