Technical Articles
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
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)
Great! Easy and useful!
Regards