SAP ABAP Central: ABAP CDS Consumption View Features Ultimate Test: Part 3

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

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

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,