Friday 20 April 2018

ABAP CDS Consumption View Features Ultimate Test: Part 1

ABAP CDS Consumption Views are exposed as BW Queries and can be consumed by S/4 HANA Query Browser and BI Clients like Analysis for Office and Design Studio. With my BW background I was curious to put ABAP CDS Consumption Views to test and fulfill some of the typical BW Query requirements like:
  • Currency Translation
  • Display of Currencies and Units of Measure
  • Use of Attributes
  • Use of Texts
  • Use of Variables
  • Use of Calculated Key Figures
  • Use of Restricted Key Figures
  • Use of Attributes
  • Use of Hierarchies
  • Use of functions like NDIV0 and NODIM
  • Setting Key Figure attributes like Number of Decimals
  • Setting Description (Variables, Characteristics, Key Figures, etc)
  • Defining layout with rows, columns and free characteristics and switching on hierarchy
  • Dynamically setting variable values
So for my ultimate test I built my Flight Y2Y comparison ABAP CDS Consumption View from ground up starting from BASIC views to prepare data, then created TEXT, HIERARCHY, DIMENSION, FACT and CUBE view then finally built by 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 Consumption Views capabilities.

This is what I have got:

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

As you can see from screen shot above it is possible to create hierarchy (Region) and characteristic variables. Also parameters for currency conversion (Display Currency) and restricted key figures restrictions (Year 1 and Year 2).

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

As you  can see from screen shot above it is possible to do:
  • Convert currencies (dc e.g. display currency columns)
  • Create restricted columns (Year 1 and Year 2)
  • Create calculated columns (Seats Occupied %)
  • Activate hierarchy (region characteristic)
  • Display unit of measures (currency, quantity and % for formula)
  • Set number of decimal places (1 for Seats Occupied % columns)
  • Characteristics and Key Figures are given proper descriptive names
Lets see hierarchy node selection variable  in action

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

As you can see from above screen shot node variable filtered hierarchy to Europe and North America data

Let me swap Region with Airline and display some more capabilities

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

From Airline characteristic properties presentation can be changed as well as attribute display activated

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

As you  can see from screen shot above it is possible to:

◈ Activate display of characteristic attribute (URL)
◈ Change characteristic presentation (key and text)

Finally lets test characteristic variable

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

SAP ABAP CDS, SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Learning

As you can from above screen shot Airline characteristic variable is working limiting data to LH and UA.

There are other capabilities that I did demonstrated in my example like:

◈ Text language dependency
◈ Attributes and Hierarchies time dependency

But even with what I demonstrated I can conclude that ABAP CDS Consumption Views are quite powerful to fulfill BW Query type of requirements.

You can create demonstrated ABAP CDS Consumption View and all other underlying view using source code below.

BASIC Views:


ZSAPBC_CARR

@AbapCatalog.sqlViewName: ‘ZCARR’

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Airline’

defineview zsapbc_carr asselectfrom scarr

{

carrid,

url,

currcode,

cast(case

when carrid =‘LH’or carrid =‘AB’then‘Germany’

when carrid =‘AA’or carrid =‘CO’or carrid =‘DL’or

     carrid =‘NW’or carrid =‘WA’then‘US’

when carrid =‘AC’then‘Canada’ 

when carrid =‘AF’then‘France’ 

when carrid =‘AZ’then‘Italy’

when carrid =‘BA’then‘UK’

when carrid =‘FJ’then‘Fiji’

when carrid =‘NG’then‘Austria’

when carrid =‘JL’then‘Japan’

when carrid =‘QF’then‘Australia’

when carrid =‘SA’then‘South Africa’

when carrid =‘SQ’then‘Singapure’

when carrid =‘SR’then‘Swirzerland’ 

when carrid =‘UA’then‘US’

else‘Other’

endas abap.char(13))  as region   

}


ZSAPBC_REGION


@AbapCatalog.sqlViewName: ‘ZREGION’

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight – Region’

define view zsapbc_region as select distinct from zsapbc_carr {

key region,

case

when region = ‘Germany’ or region = ‘France’ or region = ‘Italy’ or

       region = ‘UK’ or region = ‘Austria’ or region = ‘Swirzerland‘ then ‘Europe’

when region = ‘US’ or region = ‘Canada’ then ‘North America’

when region = ‘South Africa’ then ‘Africa’

when region = ‘Fiji’ or region = ‘Japan’ or region = ‘Singapure‘ then ‘Asia’

else ‘Other’

end as main_region 

}

where region <> ‘Australia’

union

select distinct from scarr {

key ‘Europe’ as region,

    ‘World’ as main_region

}

union

select distinct from scarr {

key ‘North America’ as region,

    ‘World’ as main_region

}

union

select distinct from scarr {

key ‘Asia’ as region,

    ‘World’ as main_region

}

union

select distinct from scarr {

key ‘Australia’ as region,

    ‘World’ as main_region

}

union

select distinct from scarr {

key ‘Africa’ as region,

    ‘World’ as main_region

}

union

select distinct from scarr {

key ‘World’ as region,

    ” as main_region

}

TEXT, HIERARCHY, DIMENSION Views:


ZSAPBC_CARR_TEXT

@AbapCatalog.sqlViewName: ‘ZCARR_TEXT’

@Analytics: {dataCategory:  #TEXT, dataExtraction.enabled: true}

@AccessControl.authorizationCheck: #NOT_ALLOWED

@EndUserText.label: ‘Flight – Carrier Text’

define view zsapbc_carr_text as select from scarr {

key carrid,

@Semantics.text: true

carrname as CarrierName   

}

ZSAPBC_CARR_DIMENSION

@AbapCatalog.sqlViewName: ‘ZCARR_DIM’

@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true}

@ObjectModel.representativeKey: ‘CARRID’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Carrier Dimension’

define view zsapbc_carr_dimension as select from zsapbc_carr as scarr

 association [1..1] to zsapbc_carr_text as _carr on $projection.carrid = scarr.carrid

{

@ObjectModel.text.association: ‘_carr‘

key carrid,

url,

currcode,

@EndUserText.label: ‘Region’

region,

_carr

}

ZSAPBC_REGION_HIERARCHY

@AbapCatalog.sqlViewName: ‘ZREGION_HIER’

@Analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }

@ObjectModel.representativeKey: ‘REGION’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Dimension’

@Hierarchy.parentChild.name: ‘REGION_GEO’

@Hierarchy.parentChild.label: ‘Region Geography’

@Hierarchy.parentChild: { recurse:          {
                         parent: ‘ParentNode‘,   child: ‘HierarchyNode‘   } }

define view zsapbc_region_hierarchy as select distinct from zsapbc_region

 association[0..1] to zsapbc_region_dimension as _region_dim on $projection.HierarchyNode = _region_dim.region

{

@ObjectModel.foreignKey.association: ‘_region_dim‘

key region as HierarchyNode,

main_region as ParentNode,

_region_dim

}

ZSAPBC_REGION_DIMENSION

@AbapCatalog.sqlViewName: ‘ZREGION_DIM’

@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: ‘REGION’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Dimension’

define view zsapbc_region_dimension as select from zsapbc_region

association[0..*] to zsapbc_region_hierarchy as _region_hier on
$projection.region = _region_hier.HierarchyNode

{

@ObjectModel.Hierarchy.association: ‘_region_hier‘ 

key region,

@EndUserText.label: ‘Main Region’

main_region,

_region_hier

}

FACT View:


ZSAPBC_FLIGHT_FACT


@AbapCatalog.sqlViewName: ‘zsflight_fact‘

@AbapCatalog.compiler.compareFilter: true

@Analytics.dataCategory: #FACT

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight Fact’

define view zsapbc_flight_fact with parameters

    @Consumption.defaultValue: ‘CAD’

    p_display_currency : s_currcode

    as select from sflight inner join zsapbc_carr as scarr                                                      on sflight.carrid = scarr.carrid {

key sflight.carrid,

key sflight.connid,

key sflight.fldate,

scarr.region, 

cast(substring(sflight.fldate,1,6) as abap.numc( 6 )) as flmonth,

cast(substring(sflight.fldate,1,4) as abap.numc( 4 )) as flyear, 

sflight.currency,

sflight.paymentsum as payment,

‘CAD’ as disp_curr,

currency_conversion(

    amount             => sflight.paymentsum,

    source_currency    => sflight.currency,

    target_currency    => $parameters.p_display_currency,

    exchange_rate_date => sflight.fldate,

    exchange_rate_type => ‘M’,

    error_handling     => ‘SET_TO_NULL’            

) as payment_disp_curr,

cast(‘EA’ as abap.unit(3) ) as unit,

sflight.seatsmax,

sflight.seatsmax_b,

sflight.seatsmax_f,

seatsmax + seatsmax_b + seatsmax_f as seatsmax_total,

sflight.seatsocc,

sflight.seatsocc_b,

sflight.seatsocc_f,

seatsocc + seatsocc_b + seatsocc_f as seatsocc_total   

}

CUBE View:


ZSAPBC_FLIGHT_CUBE


@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE’

@AbapCatalog.compiler.compareFilter: true

@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label: ‘Flight Cube’

define view zsapbc_flight_cube with parameters

p_display_currency : s_currcode,

p_year_1: rscalyear,

p_year_2: rscalyear    

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’

@ObjectModel.Hierarchy.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,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (bc) Year 1′

case when flyear = :p_year_1 then payment

else 0

end as payment_year_1,

@Semantics.quantity.unitOfMeasure: ‘Currency’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (bc) Year 2′

case when flyear = :p_year_2 then payment

else 0

end as payment_year_2, 

@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.quantity.unitOfMeasure: ‘disp_curr‘

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (dc) Year 1’

case when flyear = :p_year_1 then payment_disp_curr

else 0

end as payment_disp_curr_year_1,

@Semantics.quantity.unitOfMeasure: ‘disp_curr‘

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Booking (dc) ear 2’

case when flyear = :p_year_2 then payment_disp_curr

else 0

end as payment_disp_curr_year_2,

@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 Max Year 1’

case when flyear = :p_year_1 then seatsmax_total

else 0

end as seatsmax_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Max Year 2’

case when flyear = :p_year_2 then seatsmax_total

else 0

end as seatsmax_total_year_2,

@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’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Year 1′

case when flyear = :p_year_1 then seatsocc_total

else 0

end as seatsocc_total_year_1,

@Semantics.quantity.unitOfMeasure: ‘unit’

@DefaultAggregation: #SUM 

@EndUserText.label: ‘Seats Occ Year 2′

case when flyear = :p_year_2 then seatsocc_total

else 0

end as seatsocc_total_year_2,

@Semantics.quantity.unitOfMeasure: ‘unit_percent‘

@DefaultAggregation: #MAX

@EndUserText.label: ‘100%’

100 as hundred 

 }

CONSUMPTION View:


ZSAPBC_FLIGH_QUERY


@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY’

@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 urrency’    

     @Consumption.defaultValue: ‘CAD’

     p_display_currency  s_currcode,

     @EndUserText.label: ‘Year 1’    

     @Consumption.defaultValue: ‘2016’    

     p_year_1: rscalyear,

     @EndUserText.label: ‘Year 2’    

     @Consumption.defaultValue: ‘2017’    

     p_year_2: rscalyear    

     as select from zsapbc_flight_cube( p_display_currency:                                                            $parameters.p_display_currency,

                                       p_year_1: $parameters.p_year_1,

                                       p_year_2: $parameters.p_year_2 )

{

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.displayHierarchy: #ON

@Consumption.filter: { selectionType: #HIERARCHY_NODE,

                      multipleSelections: true,                                                                                                                                  mandatory: false }  

@AnalyticsDetails.query.hierarchyInitialLevel: 3

region,

@AnalyticsDetails.query.axis: #COLUMNS

seatsocc_total_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsmax_total_year_1,

@AnalyticsDetails.query.axis: #COLUMNS

@AnalyticsDetails.query.decimals: 1

@EndUserText.label: ‘Seats Occ (%) Year 1′

@AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total_year_1 ) /

                                   NODIM( seatsmax_total_year_1 ) ) *

                                   hundred’

1 as seats_occ_prc_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsocc_total_year_2,   

@AnalyticsDetails.query.axis: #COLUMNS

seatsmax_total_year_2,       

@AnalyticsDetails.query.axis: #COLUMNS

@AnalyticsDetails.query.decimals: 1

@EndUserText.label: ‘Seats Occ (%) Year 2′

@AnalyticsDetails.query.formula: ‘NDIV0( NODIM( seatsocc_total_year_2 ) /

                                   NODIM( seatsmax_total_year_2 ) ) *                                            hundred’

1 as seats_occ_prc_year_2,

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_year_2,   

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_disp_curr_year_1,       

@AnalyticsDetails.query.axis: #COLUMNS

zsapbc_flight_cube.payment_disp_curr_year_2,

@AnalyticsDetails.query.axis: #FREE   

connid,         

@AnalyticsDetails.query.axis: #FREE

@Consumption.filter: {selectionType: #SINGLE,

                      multipleSelections: true,

                      mandatory: false }

carrid,

@AnalyticsDetails.query.axis: #FREE

fldate,

@AnalyticsDetails.query.axis: #FREE

flmonth,

@AnalyticsDetails.query.axis: #FREE

flyear,

@AnalyticsDetails.query.axis: #FREE

currency,

@AnalyticsDetails.query.axis: #FREE

disp_curr,

@AnalyticsDetails.query.axis: #FREE

unit

}

1 comment:

  1. Really is very interesting, I saw your website and get more details..Nice work. Thanks regards, sap-mm training

    ReplyDelete