Technical Articles
ABAP CDS Consumption View Features Ultimate Test: Part 5
In first part, second part, third part and fourth part of my blog I put ABAP CDS Consumption View to test and fulfilled some of most typical BW Query requirements. In fifth part of my blog I will challenge ABAP CDS Consumption View again. This time demonstrating exception aggregation using formula and count
I created CUBE views and finally created CONSUMPTION view. I will not go much into details of implementation because all that you can explore on your own. What is more important is to understand ABAP CDS views capabilities.
As you can see from screenshot below using exception formula aggregation count of low occupied flight count (occupancy below 96 %) and high occupied flights (occupancy is above 96 %) is calculated as well using exception count calculation overall flight count is calculated.
Drill down on Airline, Flight and Date level demonstrates how measures are calculated
ZSAPBC_FLIGHT_CUBE5
@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE5’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Flights’
define view zsapbc_flight_cube5 with parameters
p_display_currency : s_currcode
as select from zsapbc_flight_fact( p_display_currency: $parameters.p_display_currency ) as flight
association [1..1] to zsapbc_carr_dimension as _carr on
$projection.carrid = flight.carrid
association [1..1] to zsapbc_region_dimension as _region on
$projection.region = flight.region
{
@ObjectModel.foreignKey.association: ‘_carr’
key flight.carrid,
key flight.connid,
@EndUserText.label: ‘Date’
key flight.fldate,
@EndUserText.label: ‘Region’
@ObjectModel.foreignKey.association: ‘_region’
flight.region,
_carr,
_region,
@Semantics.calendar.yearMonth: true
@EndUserText.label: ‘Month’
flight.flmonth,
@Semantics.calendar.year: true
@EndUserText.label: ‘Year’
flight.flyear,
@Semantics.currencyCode: true
@EndUserText.label: ‘Booking Currency’
flight.currency,
@Semantics.amount.currencyCode: ‘Currency’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Booking (bc)’
flight.payment,
@EndUserText.label: ‘Display Currency’
cast(‘CAD’ as abap.cuky( 5 )) as disp_curr,
@Semantics.amount.currencyCode: ‘disp_curr’
@EndUserText.label: ‘Booking (dc)’
@DefaultAggregation: #SUM
flight.payment_disp_curr,
@Semantics.unitOfMeasure: true
@EndUserText.label: ‘UOM’
flight.unit,
@Semantics.unitOfMeasure: true
@EndUserText.label: ‘%’
cast( ‘%’ as abap.unit( 3 ) ) as unit_percent,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Max Econ.’
flight.seatsmax,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Max Bus.’
flight.seatsmax_b,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Max 1st’
flight.seatsmax_f,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Max Total’
seatsmax_total,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Occ Econ.’
flight.seatsocc,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Occ Bus.’
flight.seatsocc_b,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Occ 1st’
flight.seatsocc_f,
@Semantics.quantity.unitOfMeasure: ‘unit’
@DefaultAggregation: #SUM
@EndUserText.label: ‘Seats Occ Total’
seatsocc_total,
@Semantics.quantity.unitOfMeasure: ‘unit_percent’
@DefaultAggregation: #MAX
@EndUserText.label: ‘100%’
100 as hundred
}
ZSAPBC_FLIGHT_QUERY5
ZSAPBC_FLIGHT_CUBE5
@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY5’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck:#CHECK
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@EndUserText.label: ‘Flight Query’
define view zsapbc_flight_query with parameters
@EndUserText.label: ‘Display Currency’
@Consumption.defaultValue: ‘CAD’
p_display_currency : s_currcode
as select from zsapbc_flight_cube5(
p_display_currency:
$parameters.p_display_currency ) {
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.displayHierarchy: #ON
@Consumption.filter: { selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.hierarchyInitialLevel: 3
@AnalyticsDetails.query.variableSequence: 1
region,
@AnalyticsDetails.query.axis: #FREE
connid,
@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.variableSequence: 2
carrid,
@AnalyticsDetails.query.axis: #FREE
fldate,
@AnalyticsDetails.query.axis: #FREE
flmonth,
@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.variableSequence: 3
flyear,
@AnalyticsDetails.query.axis: #FREE
currency,
@AnalyticsDetails.query.axis: #FREE
disp_curr,
@AnalyticsDetails.query.axis: #FREE
unit,
@AnalyticsDetails.query.axis: #COLUMNS
seatsocc_total,
@AnalyticsDetails.query.axis: #COLUMNS
seatsmax_total,
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.decimals: 1
@EndUserText.label: ‘Seats Occ (%)’
@AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total
) /
NODIM( seatsmax_total
) ) * hundred ‘
1 as seats_occ_prc,
@EndUserText.label: ‘Low Occupied Flight Count’
@AnalyticsDetails: {
exceptionAggregationSteps: [{
exceptionAggregationBehavior: #SUM,
exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]
}
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.formula: ‘case when $projection.seats_occ_prc
< 96 then 1 else 0 end’
0 as flight_cnt_low_occ,
@EndUserText.label: ‘High Occupied Flight Count’
@AnalyticsDetails: {
exceptionAggregationSteps: [{
exceptionAggregationBehavior: #SUM,
exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]
}
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.formula: ‘case when $projection.seats_occ_prc
>= 96 then 1 else 0 end’
0 as flight_cnt_high_occ,
@EndUserText.label: ‘Flight Count’
@AnalyticsDetails: {
exceptionAggregationSteps: [{
exceptionAggregationBehavior: #COUNT,
exceptionAggregationElements: [‘carrid’, ‘connid’,’fldate’ ] }]
}
0 as flight_cnt,
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.axis: #COLUMNS
payment,
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.axis: #COLUMNS
payment_disp_curr
}
CDS Views can be downloaded from here:
Hi,
Is there any difference on capability or functionality between creating CDS Consumption query and creating BEx query on top of CDS cube?
Seems like the effort to build the CDS Consumption query could be overwhelming since everything must be typed down.
Hi Donni,
it sounds like a good idea first, but then considering different lifecycles it is actually not.
Regards, Uladzislau