ABAP CDS on HANA-15

cds Creating COMPOSITE & CONSUMPTION View for analytic engine

This post describes creating several BASIC views & then creating a COMPOSITE view by using the BASIC views & finally creating a CONSUMPTION view from the COMPOSITE view which is enabled for analytic query.

Creating 3 BASIC VIEWs
Creating 1 COMPOSITE VIEW
Creating 1 CONSUMPTION VIEW which is enabled for analytics

Basic View 1 on SCARR table- Its a DIMENSION type as it hits the DB table directly

@AbapCatalog.sqlViewName: ‘ZVWSCARR’
@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Airlline’
@VDM.viewType: #BASIC
@Analytics.dataCategory: #DIMENSION
@Analytics.dataExtraction.enabled: true
@ObjectModel.representativeKey: ‘Airline’
define view ZDemo_Scarr as select from scarr
{
key scarr.carrid as Airline,
@Semantics.text: true
scarr.carrname as AirName,
@Semantics.currencyCode: true
scarr.currcode as AirCurrency,
scarr.url as AirlineUrl
}

Data Preview

Basic View 2 on SPFLI table- Its a DIMENSION type as it hits the DB table directly with association to the basic view ZDemo_Scarr.

@AbapCatalog.sqlViewName: ‘ZVWSPFLI’
@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Airline Connection’
@Analytics.dataCategory: #DIMENSION
@Analytics.dataExtraction.enabled: true
@VDM.viewType: #BASIC
@ObjectModel.representativeKey: ‘AirConnection’
define view ZDemo_Spfli as select from spfli
association [0..1] to ZDemo_Scarr as _Scarr
on $projection.Airline = _Scarr.Airline
{
@ObjectModel.foreignKey.association: ‘_Scarr’
key spfli.carrid as Airline,
key spfli.connid as AirConnection,
spfli.countryfr as DepartureCountry,
spfli.countryto as ArrivalCountry,
@Semantics.quantity.unitOfMeasure: ‘AirDistanceUnit’
@DefaultAggregation:#SUM
spfli.distance as AirDistance,
@Semantics.unitOfMeasure: true
spfli.distid as AirDistanceUnit,
_Scarr

}

Data Preview

Basic View 3 on SFLIGHT table- with association to view ZDemo_Scarr and view ZDemo_Spfli. Several semantics are used for different fields on aggregation and currency & unit.

@AbapCatalog.sqlViewName: ‘ZVWSFLIGHT’
@ClientDependent: true
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: ‘Airline Schedule’
@Analytics.dataCategory: #CUBE
@Analytics.dataExtraction.enabled: true
@VDM.viewType: #BASIC
define view ZDemo_Sflight as select from sflight
association [0..1] to ZDemo_Scarr as _Scarr
on $projection.Airline = _Scarr.Airline

association [0..1] to ZDemo_Spfli as _Spfli
on $projection.Airline = _Spfli.Airline
and $projection.AirConnection = _Spfli.AirConnection
{
@ObjectModel.foreignKey.association: ‘_Scarr’
key sflight.carrid as Airline,
@ObjectModel.foreignKey.association: ‘_Spfli’
key sflight.connid as AirConnection,
key sflight.fldate as FlightDate,
@Semantics.amount.currencyCode: ‘FlightCurrency’
@DefaultAggregation: #MIN
sflight.price as FlightPrice,
@Semantics.currencyCode: true
sflight.currency as FlightCurrency,
@DefaultAggregation: #SUM
sflight.seatsmax as MaximumAvaSeats,
@DefaultAggregation: #SUM
sflight.seatsocc as NumberOfAvaSeats,
**/* Associations */
_Scarr,
_Spfli**
}

Data Preview

All fields of scarr, splfi, sflight are available in the view ZDemo_Sflight due to the association with views ZDemo_Scarr & ZDemo_Spfli

Now next step is to build a composite view on the basic view ZDemo_Sflight and select as many fields from the view ZDemo_Sflight so that this composite view can expose many fields to the consumption views for analytic analysis.

@AbapCatalog.sqlViewName: ‘ZVWCSFLIGHT’
@ClientDependent: true
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: ‘Airline Schedule by Country’
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
define view ZDemo_Csflight as select from ZDemo_Sflight
{
@ObjectModel.foreignKey.association: ‘_Scarr’
key ZDemo_Sflight.Airline,
@ObjectModel.foreignKey.association: ‘_Spfli’
key ZDemo_Sflight.AirConnection,
key ZDemo_Sflight.FlightDate,
@Semantics.amount.currencyCode: ‘FlightCurrency’
@DefaultAggregation:#MIN
ZDemo_Sflight.FlightPrice,
@Semantics.currencyCode: true
ZDemo_Sflight.FlightCurrency,
@DefaultAggregation: #SUM
ZDemo_Sflight.MaximumAvaSeats,
@DefaultAggregation: #SUM
ZDemo_Sflight.NumberOfAvaSeats,
**/* Associations */
ZDemo_Sflight._Scarr,
ZDemo_Sflight._Spfli,
ZDemo_Sflight._Scarr.AirName,
ZDemo_Sflight._Scarr.AirlineUrl,
ZDemo_Sflight._Spfli.DepartureCountry,
ZDemo_Sflight._Spfli.ArrivalCountry**

}

Data Preview

Next step is to create a CONSUMPTION View from the COMPOSITE view- ZDemo_Csflight and enabling it for analytics. In the analytics view fields are marked as rows or columns, other fields can be left .

Here the filter semantics provided for the field ‘Departure Country’ and with mandatory option means when we this view is analyzed with analytic engine, we have to pass the departure country value for selection.

@AbapCatalog.sqlViewName: ‘ZVWSFLIGHTQUERY’
@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Flight Analytic Query’
@Analytics.query: true
@VDM.viewType: #CONSUMPTION
@OData.publish: true
define view ZDemo_Sflight_Query as select from ZDemo_Csflight
{
@AnalyticsDetails.query.axis: #ROWS
ZDemo_Csflight.Airline,
@AnalyticsDetails.query.axis: #ROWS
ZDemo_Csflight.AirConnection,
@AnalyticsDetails.query.axis: #ROWS
ZDemo_Csflight.FlightDate,
@Consumption.filter: {selectionType: #SINGLE, multipleSelections: false, mandatory: true }
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Departure Country’
ZDemo_Csflight.DepartureCountry,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Arrival Country’
ZDemo_Csflight.ArrivalCountry ,
@AnalyticsDetails.query.axis: #COLUMNS
ZDemo_Csflight.FlightPrice,
ZDemo_Csflight.FlightCurrency,
ZDemo_Csflight.MaximumAvaSeats,
ZDemo_Csflight.NumberOfAvaSeats

}

Data Preview

Next thing is to test the Consumption View in the analytic tool. In a BW system you can find the Tx- RSRT

Here we have to provide for consumption sql view name which is enabled for analytic query and provide 2C before it and hit enter key.

Now the actual query is on the composite view used in the consumption view. Execute.

As we have made Departure Country as a mandatory filter option in the consumption view, it asks for a value. Provide a value & Execute.

The list of values appear with 131 records. Let’s further filter the selection for Airline(CARRID) & Connection Number(CONNID). Select the filter button for each.