ABAP CDS on HANA-15

cdsCreating 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.

  1. Creating 3 BASIC VIEWs
  2. Creating 1 COMPOSITE VIEW
  3. 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 directly1.jpg

 

 

 

 

 

 

 

 

 


@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

4.jpg

 

 

 

 

 

 

 

 

 

 

 

 


 

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. 2.jpg

 

 

 

 

 

 

 

 

 

 

 

 


 

@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 Preview5.jpg

 

 

 

 

 

 

 

 

 

 

 

 


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.3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


@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 Preview6.jpg

 

 

 

 

 

 

 

 

 

 

 


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.


7.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


@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

8.jpg

 

 

 

 

 

 

 


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.


 9

 

 

 

 

 

 

 

 

 

 

 


@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

10

 

 

 

 

 

 

 

 

 


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

11

 

 

 

12

 

 

 

 

 

 

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.

13

 

 

 

 

 

 

 

 

 

 

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

21

 

 

 

 

 

 

 

 

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

14

 

 

 

 

 

 

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.

15

 

 

 

 

 

 

 

 

 

 

 

 

 

Select LH and click on transfer.

16

 

 

 

 

 

 

 

 

 

 

 Now the selection result list reduced to 67 records. Choose filer for Connection Number. Select 400 and select Transfer.

17

 

 

 

 

 

 

 

 

 

 

Now with the filters we have few records now select Graphical Display.

18

 

 

 

 

 

 

 

 

 

 

19

A graphical view is displayed. 

20


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s