Enhance Service Class to Handle Complex Virtual Cube Selections

[uladzislau_pralat]

    Virtual Cube Function Module can be very easily implemented using CL_RSDRV_REMOTE_IPROV_SRV class services (there is an example in class documentation). I like its simplicity, but unfortunatelly it can not handle complex selections. In my blog, I will explain how to keep Virtual Cube Function Module implementation simple and in the same time handle complex selections enhancing service class.

      Below is Function Module that implementation Virtual Cube reading from SFLIGHT table

*---------------------------------------------------------------------*
*      CLASS lcl_application  DEFINITION
*---------------------------------------------------------------------*
CLASS lcl_application DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      get_t_iobj_2_fld RETURNING VALUE(rt_iobj_2_fld) TYPE
                    cl_rsdrv_remote_iprov_srv=>tn_th_iobj_fld_mapping.

ENDCLASS.

*---------------------------------------------------------------------*
*      CLASS lcl_application  IMPLEMENTATION
*---------------------------------------------------------------------*
CLASS lcl_application IMPLEMENTATION.
*---------------------------------------------------------------------*
* get_t_iobj_2_fld
*---------------------------------------------------------------------*
METHOD get_t_iobj_2_fld.

  rt_iobj_2_fld = VALUE #( ( iobjnm = 'CARRID'    fldnm = 'CARRID' )
                          ( iobjnm = 'CONNID'    fldnm = 'CONNID' )
                          ( iobjnm = 'FLDATE'    fldnm = 'FLDATE' )
                          ( iobjnm = 'PLANETYPE' fldnm = 'PLANETYPE' )
                          ( iobjnm = 'SEATSOCC'  fldnm = 'SEATSOCC' )
                          ( iobjnm = 'SEATSOCCB' fldnm = 'SEATSOCC_B' )
                          ( iobjnm = 'SEATSOCCF' fldnm = 'SEATSOCC_F' ) ).

  ENDMETHOD.
ENDCLASS.

FUNCTION z_sflight_read_remote_data.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"    VALUE(INFOCUBE) LIKE  BAPI6200-INFOCUBE
*"    VALUE(KEYDATE) LIKE  BAPI6200-KEYDATE OPTIONAL
*"  EXPORTING
*"    VALUE(RETURN) LIKE  BAPIRET2 STRUCTURE  BAPIRET2
*"  TABLES
*"      SELECTION STRUCTURE  BAPI6200SL
*"      CHARACTERISTICS STRUCTURE  BAPI6200FD
*"      KEYFIGURES STRUCTURE  BAPI6200FD
*"      DATA STRUCTURE  BAPI6100DA
*"----------------------------------------------------------------------

  zcl_aab=>break_point( 'Z_SFLIGHT_READ_REMOTE_DATA' ).

  DATA(iprov_srv) = NEW
    cl_rsdrv_remote_iprov_srv( i_th_iobj_fld_mapping = lcl_application=>get_t_iobj_2_fld( )
                                i_tablnm              = 'SFLIGHT' ).

  iprov_srv->open_cursor(
    i_t_characteristics = characteristics[]
    i_t_keyfigures      = keyfigures[]
    i_t_selection      = selection[] ).

  iprov_srv->fetch_pack_data( IMPORTING e_t_data = data[] ).

  return-type = 'S'.

ENDFUNCTION.

This how BW Query is defined which sends complex selection to Virtual Cube Function Module.

As you can see the Query reads number of seats occupied in Airbus Airplanes Types (global restriction) for All Carriers, Lufthansa and American Airlines in each 2015 and 2016 years.  Following selection is sent to Virtual Cube Function Module

Expression 0 correspinds to global restriction and expressions 1 through 6 correspond to restricted key figures (All Carriers 2015, All Carriers 2016, Lufthansa 2015, Lufthansa 2016, American Airlines 2015 and American Airlines 2016).

Service class in our Virtual Cube Function Module used in such a way that generates wrong SQL Where clause expression. It is not a problem with Service Class as such, but the way it is used.

BW Query results are wrong (All Carries data is a sum of Lufthansa and American Airlines. e.g. other carriers data is missing).

The problem is that generated SQL Where clause expression does not follow the rule below:

E0  AND (  E1 OR E2 OR E3 ... OR EN ),

where E0 corresponds to the global restrictions and E1, E2, E3 ... EN to other restrictions.

The problem can easily be fixed enhancing CL_RSDRV_REMOTE_IPROV_SRV service class. What it takes is to:

• Creation of BUILD_WHERE_CONDITIONS_COMPLEX method;
• Implemention of Overwrite-exit for OPEN_CURSOR method.

Creation of BUILD_WHERE_CONDITIONS_COMPLEX method

METHOD build_where_conditions_complex.
DATA: wt_bw_selection TYPE tn_t_selection.
DATA: wt_where TYPE rsdr0_t_abapsource.

* E0 AND ( E1 OR E2 OR E3 ... OR EN )
  LOOP AT i_t_selection INTO DATA(wa_bw_selection)
    GROUP BY ( expression = wa_bw_selection-expression )
              ASCENDING ASSIGNING FIELD-SYMBOL(<bw_selection>).
    CLEAR: wt_bw_selection,
          wt_where.
    LOOP AT GROUP <bw_selection> ASSIGNING FIELD-SYMBOL(<selection>).
      wt_bw_selection = VALUE #( BASE wt_bw_selection ( <selection> ) ).
    ENDLOOP.
    build_where_conditions( EXPORTING i_t_selection = wt_bw_selection
                            IMPORTING e_t_where    = wt_where ).
    CASE <bw_selection>-expression.
    WHEN '0000'.
      IF line_exists( i_t_selection[ expression = '0001' ] ).
        APPEND VALUE #( line = ' ( ' ) TO e_t_where.
      ENDIF.
      APPEND LINES OF wt_where TO e_t_where.
      IF line_exists( i_t_selection[ expression = '0001' ] ).
        APPEND VALUE#( line = ' ) AND ( ' ) TO e_t_where.
      ENDIF.
    WHEN OTHERS.
      IF <bw_selection>-expression > '0001'.
        APPEND VALUE #( line = ' OR ' ) TO e_t_where.
      ENDIF.
      APPEND VALUE #( line = ' ( ' ) TO e_t_where.
      APPEND LINES OF wt_where TO e_t_where.
      APPEND VALUE #( line = ' ) ' ) TO e_t_where.
      IF ( line_exists( i_t_selection[ expression = '0000' ] ) ) AND
        ( NOT line_exists( i_t_selection[ expression = <bw_selection>-expression + 1 ] ) ).
        APPEND VALUE #( line = ' ) ' ) TO e_t_where.
      ENDIF.
    ENDCASE.
  ENDLOOP.

ENDMETHOD.

BUILD_WHERE_CONDITIONS_COMPLEX method contains logic to build selection accorindg to the rule. It is calling original

BUILD_WHERE_CONDITIONS method using it as buling block. New LOOP AT ... GROUP BY ABAP Syntax is used to split selection table into individual selections converting then them into SQL Where clause expressions and combining them into final expression as per the rule.

Implemention of Overwrite-exit for OPEN_CURSOR method

CLASS lcl_z_iprov_srv DEFINITION DEFERRED.
CLASS cl_rsdrv_remote_iprov_srv DEFINITION LOCAL FRIENDS lcl_z_iprov_srv.
CLASS lcl_z_iprov_srv DEFINITION.
PUBLIC SECTION.
CLASS-DATA obj TYPE REF TO lcl_z_iprov_srv. "#EC NEEDED
DATA core_object TYPE REF TO cl_rsdrv_remote_iprov_srv . "#EC NEEDED
INTERFACES  IOW_Z_IPROV_SRV.
  METHODS:
  constructor IMPORTING core_object
    TYPE REF TO cl_rsdrv_remote_iprov_srv OPTIONAL.
ENDCLASS.
CLASS lcl_z_iprov_srv IMPLEMENTATION.
METHOD constructor.
  me->core_object = core_object.
ENDMETHOD.

METHOD iow_z_iprov_srv~open_cursor.
*"------------------------------------------------------------------------*
*" Declaration of Overwrite-method, do not insert any comments here please!
*"
*"methods OPEN_CURSOR
*"  importing
*"    !I_T_CHARACTERISTICS type CL_RSDRV_REMOTE_IPROV_SRV=>TN_T_IOBJ
*"    !I_T_KEYFIGURES type CL_RSDRV_REMOTE_IPROV_SRV=>TN_T_IOBJ
*"    !I_T_SELECTION type CL_RSDRV_REMOTE_IPROV_SRV=>TN_T_SELECTION .
*"------------------------------------------------------------------------*
  DATA:
    l_t_groupby    TYPE rsdr0_t_abapsource,
    l_t_sel_list  TYPE rsdr0_t_abapsource,
    l_t_where      TYPE rsdr0_t_abapsource.

  core_object->build_select_list(
    exporting
      i_t_characteristics =  i_t_characteristics
      i_t_keyfigures      =  i_t_keyfigures
    importing
      e_t_sel_list = l_t_sel_list
      e_t_groupby  = l_t_groupby ).

  core_object->build_where_conditions_complex(
    exporting
      i_t_selection = i_t_selection
    importing
      e_t_where = l_t_where ).

* #CP-SUPPRESS: FP secure statement, no user input possible
  open cursor with hold core_object->p_cursor for select (l_t_sel_list) from (core_object->p_tablnm)
    where (l_t_where)
    group by (l_t_groupby).

ENDMETHOD.
ENDCLASS.

OPEN_CURSOR  Overwrite-exit method has the same logic as original method except that BUILD_WHERE_CONDITIONS_COMPLEX method is called instead of BUILD_WHERE_CONDITIONS

Now when the changes are in place, lets run the report again and see what SQL Where Clause expression is generated

Finally, lets run the report again and see if shows correct data.

Now data is correct. All Carriers includes all data not only Lufthansa and American Airlines.