Skip to Content

In first part and second part of my blog I put ABAP CDS Consumption View to test and fulfilled some of most typical BW Query requirements. In third part of my blog I will challenge ABAP CDS Consumption View one more time. This time demonstrating time dependent dimensions / hierarchies and language dependent texts.

I created BASIC views to prepare data, also create TEXT, HIERARCHY, DIMENSION views, CUBE view 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 Consumption Views capabilities.

This is what I have got. I run the query for 31.10.1993 Key Date

Note that Germany and Italy belongs to Europe hierachy node of time dependent hierarchy

Now I run the query for 01.11.1993 Key Date

Note that Germany and Italy now belongs to European Union hierarchy node of time dependent hierarchy

Similarly Main Region attibute of Region dimention time dependency works. For 31.10.1993 key date Main Region attribute of Germany and Italy is Europe

Note that for 01.11.1993 key date Main Region attribute of Germany and Italy is European Union

Finally I logon in German language region texts in German.

 

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

 

BASIC Views:

ZSAPBC_REGION3


@AbapCatalog.sqlViewName: ‘ZREGION3’

@AccessControl.authorizationCheck:#NOT_REQUIRED

@EndUserText.label: ‘Flight – Region ‘

define view zsapbc_region3 as select distinct from zsapbc_carr {

key region,

case

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,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto 

}

where region <> ‘Australia’ and region <> ‘Germany’ and region <> ‘France’ and region <> ‘Italy’ and

       region <> ‘UK’ and region <> ‘Austria’ and region <> ‘Swirzerland’

 

union

 

select distinct from zsapbc_carr {

key region,

‘Europe’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘19931031’ as abap.dats ) as dateto

}

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

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

 

union

 

select distinct from zsapbc_carr {

key region,

‘EU’ as main_region,

cast( ‘19931031’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

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

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

 

union

 

select distinct from scarr {

key ‘Europe’ as region,

‘World’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘19931031’ as abap.dats ) as dateto

}

 

union

 

select distinct from scarr {

key ‘EU’ as region,

‘World’ as main_region,

cast( ‘19931101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

 

union 

 

select distinct from scarr {

key ‘North America’ as region,

‘World’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

 

union

 

select distinct from scarr {

 


key ‘Asia’ as region,

‘World’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

 

union

 

select distinct from scarr {

key ‘Australia’ as region,

‘World’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

 

union

 

select distinct from scarr {

key ‘Africa’ as region,

‘World’ as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto

}

 

union

 

select distinct from scarr {

key ‘World’ as region,

as main_region,

cast( ‘00010101’ as abap.dats ) as datefrom,

cast( ‘99991231’ as abap.dats ) as dateto 

}


 

TEXT, HIERARCHY, DIMENSION Views:

ZSAPBC_REGION_TEXT3


@AbapCatalog.sqlViewName: ‘ZREGION_TEXT3’

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

@AccessControl.authorizationCheck: #NOT_ALLOWED

@EndUserText.label: ‘Flight – Region Text’

define view zsapbc_region_text3 as select from zsapbc_region3 {

key region,

@Semantics: {language: true }

‘E’ as lang,    

@Semantics.text: true

case

when region = ‘EU’ then ‘European Union’

else region

end as region_text   

}

union

 

select from zsapbc_region3 {

key region,

@Semantics: {language: true }

‘D’ as lang,    

@Semantics.text: true

case

when region = ‘EU’ then Europäische Union’

when region = ‘World’ then ‘Welt’

when region = ‘US’ then Vereinigte Staaten

when region = ‘Canada’ then Kanada

when region = ‘Fiji’ then Fidschi

when region = ‘South Africa’ then Südafrika        

when region = Singapure then Singapur

when region = ‘France’ then Frankreich

when region = ‘Italy’ then Italien   

when region = ‘UK’ then Vereinigtes Königreich                  

when region = ‘Germany’ then ‘Deutschland’

when region = ‘Austria’ then Österreich      

when region = Swirzerland then Schweiz       

when region = ‘Europe’ then ‘Europa’     

when region = ‘North America’ then Nordamerika    

when region = ‘Asia’ then Asien

when region = ‘Australia’ then Australien

when region = ‘Africa’ then Afrika

else region end as region_text   

}


 

ZSAPBC_REGION_HIERARCHY3


@AbapCatalog.sqlViewName: ‘ZREGION_HIER3’

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

@ObjectModel.representativeKey: ‘REGION’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Hierarchy’

@Hierarchy.parentChild.name: ‘REGION_GEO’

@Hierarchy.parentChild.label: ‘Region
Geography’

@Hierarchy.parentChild: { recurse: parent: ‘ParentNode’,  

                                    child:  ‘HierarchyNode’   } }

define view zsapbc_region_hierarchy3 as select distinct from zsapbc_region3

 association[0..*] to zsapbc_region_dimension3 as _region_dim on

   $projection.HierarchyNode = _region_dim.region

{

@ObjectModel.foreignKey.association: ‘_region_dim’

key region as HierarchyNode,

@Semantics.businessDate.to: true

key dateto,

@Semantics.businessDate.from: true

datefrom,

main_region as ParentNode,

_region_dim

}


 

ZSAPBC_REGION_DIMENSION3


@AbapCatalog.sqlViewName: ‘ZREGION_DIM3’

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

@ObjectModel.representativeKey: ‘region’

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘Flight – Region Dimension’

define view zsapbc_region_dimension3 as select from zsapbc_region3

  association [1..1] to zsapbc_region_text3 as _region_text on

        $projection.region = _region_text.region

  association[0..*] to zsapbc_region_hierarchy3 as _region_hier on

    $projection.region = _region_hier.HierarchyNode

{

@ObjectModel.text.association: ‘_region_text

@ObjectModel.Hierarchy.association: ‘_region_hier

key region,

@Semantics.businessDate.to: true

key dateto,

@Semantics.businessDate.from: true

datefrom,

@EndUserText.label: ‘Main Region’

main_region,

_region_text,

_region_hier

}


 

CUBE View:

ZSAPBC_FLIGHT_CUBE3


@AbapCatalog.sqlViewName: ‘ZSFLIGHT_CUBE3’

@AbapCatalog.compiler.compareFilter: true

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

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label: ‘Flight Cube’

 

define view zsapbc_flight_cube3
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 [0..*] to zsapbc_region_dimension3 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,

@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) Year 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_FLIGHT_QUERY3


@AbapCatalog.sqlViewName: ‘ZFLIGHT_QUERY3’

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck:#CHECK

@VDM.viewType: #CONSUMPTION

@Analytics.query: true

@EndUserText.label: ‘Flight Query’

define view zsapbc_flight_query3 with parameters

@Consumption.hidden: false

@Environment.systemField: #SYSTEM_DATE

@EndUserText.label: ‘Key Date’

p_keydate: abap.dats,

@EndUserText.label: ‘Display Currency’    

@Consumption.defaultValue: ‘CAD’

p_display_currency : s_currcode,

@EndUserText.label: ‘Year 1’    

@Consumption.derivation: {lookupEntity: ‘ZSAPBC_CURR_YEAR_ADD_N’,                                                                                  resultElement: ‘p_year’ ,

                         binding: [{ targetParameter: ‘p_offset’,

                                    type: #CONSTANT,

                                    value: -1 }]}

p_year_1: rscalyear,

@EndUserText.label: ‘Year 2’    

@Consumption.derivation: {lookupEntity: ‘ZSAPBC_CURR_YEAR_ADD_N’,

                         resultElement: ‘p_year’,

                         binding: [{ targetParameter: ‘p_offset’,

                                    type:#CONSTANT ,

                                    value: 0 }] }    

p_year_2: rscalyear    

as select from zsapbc_flight_cube3(

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.display: #TEXT

@AnalyticsDetails.query.displayHierarchy: #ON

@Consumption.filter: { selectionType: #HIERARCHY_NODE,

                      multipleSelections: true,

                      mandatory: false }  

@AnalyticsDetails.query.hierarchyInitialLevel: 3

zsapbc_flight_cube3._region[datefrom >= $parameters.p_keydate and

                            dateto <= $parameters.p_keydate].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

payment_year_1,   

@AnalyticsDetails.query.axis: #COLUMNS

payment_year_2,   

@AnalyticsDetails.query.axis: #COLUMNS

payment_disp_curr_year_1,       

@AnalyticsDetails.query.axis: #COLUMNS

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,

nalyticsDetails.query.axis: #FREE

unit

}


 

CDS Views can be downloaded from here:

zsapbc_region3

zsapbc_region_text3

zsapbc_region_hierarchy3

zsapbc_region_dimension3

zsapbc_flight_cube3

zsapbc_flight_query3

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply