Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
0 Kudos
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

 

In fourth part of my blog I will challenge ABAP CDS Consumption View again. This time demonstrating percentage share calculation and filtering key figures structure.