Filters Push down in ABAP CDS views – For a better performance of the application! SAP FIORI.

In this blog post I have discussed on various technics those can be used to push down the consumption level filters to the database table level

1. Pushing down the single selection filters from the fiori application using parameterized ABAP CDS views
2. Pushing down the multiple selection filters from the fiori application using the dynamic where clause – In combination with the ABAP Managed Database Procedure (AMDP ) table functions and ABAP CDS views.

Pushing down Single selection filters

In the below example, there is  a mandatory filter criterion  “Ad Effective Date” in the fiori application layer. Let us see how to taken the filter till the database level selection instead of using it as a consumption filter.

Single Select Filter example

Place this filter as a parameter in the final Consumption CDS view and pass it down all through your layers till it reaches the actual table selection.

“P_adeffdate” is the parameter in my example. ZI_PROD_FILTER_BYDATE = > is the actual CDS view which hits the database.

My Consumption view – ZC_GEN_REPORT

@AbapCatalog.sqlViewName: 'ZCGENREPORT'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Filter the products by date'
@ObjectModel: {
  usageType.serviceQuality: #D,
  usageType.sizeCategory: #XXL,
  usageType.dataClass: #TRANSACTIONAL
}
@Metadata.allowExtensions: true
define view ZC_GEN_REPORT
  with parameters
    p_adeffdate :char8    
  as select from ZI_PROD_FILTER_BYDATE(p_adeffdate:$parameters.p_adeffdate) as _Product
 {
  key  _Product.mandt                as Clnt,
  key  _Product.node_id              as NodeId,
  key  _Product.prod_id              as ProdId,
  key  _Product.prod_hr_id           as ProdHrId,
       _Product.prod_name_uc         as ArticleName      
}

@AbapCatalog.sqlViewName: 'ZIPRODSEARCH'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Filter the products by date'
define view ZI_PROD_FILTER_BYDATE
  with parameters
    p_adeffdate : dats
  as select from    /dmf/prod_nd_xr  as _Product
    inner join      /dmf/prod        as _ArticleChar on _Product.prod_id = _ArticleChar.prod_id
    association [0..1] to /dmf/prodt as _ArticleTxt  on _Product.prod_id = _ArticleTxt.prod_id
{
  key  _Product.mandt                as Clnt,
  key  _Product.node_id              as NodeId,
  key  _Product.prod_id              as ProdId,
  key  _Product.prod_hr_id           as ProdHrId,
       _ArticleTxt.prod_name_uc      as ArticleName   
}
where
      _Product.valid_fr <= $parameters.p_adeffdate
  and _Product.valid_to >= $parameters.p_adeffdate

Below are the snippets on how to call the parameterized ABAP CDS views from different layers of the application

Calling from the UI layer for an analytical application

/sap/opu/odata/sap/ZC_GEN_ANALYTICAL_REPORT_SRV/ZC_GEN_ANALYTICAL_REPORT(p_adeffdate=datetime'2020-07-26T00:00:00')/Results 

Calling from the UI layer for a normal application

/sap/opu/odata/sap/ZC_GEN_REPORT_SRV/ZC_GEN_REPORT(p_adeffdate=datetime'2020-07-26T00:00:00')/Set

Calling a parameterized CDS view from  ABAP layer

select * from ZIPRODSEARCH(p_adeffdate = @lv_adeffdate) into table  @data(lt_prod).

Calling the parameterized CDS views in AMDP method for a table function – SQL layer

Table function

@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Table function for Prod att'
@ClientHandling.type: #CLIENT_INDEPENDENT
define table function ZTABL_PROD
  with parameters
  p_adeffdate : dats
returns
{
  key clnt         : abap.clnt;
  key ProdHrId     : /dmf/hierarchy_id;
      ProdId       : /dmf/product_id;
}
implemented by method
  ZCL_COM_FINAL=>GET_PROD;

AMDP method

method get_prod
        by database function
        for hdb
        language sqlscript
        options read-only
        using ZIPRODSEARCH.
     Return select clnt, ProdHrId , ProdId from ZIPRODSEARCH(:p_adeffdate) as _Prod
            Where _Prod.clnt = session_context('CLIENT');
Endmethod.

Calling the parameterized CDS inside another CDS View

Association example

@AbapCatalog.sqlViewName: 'ZCRETAIL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Retail Report'
}
define view ZI_RETAIL
  with parameters
    p_addeffdate : dats
as select from ZI_RETAIL as _Main
association [0..1] to ZI_PROD_FILTER_BYDATE as _Prod  on _Main.ProdId = _Prod.ProdId
{
     Key _Main.ProdHrId as ProdHrId,
         _Prod(p_adeffdate:$parameters.p_adeffdate).prodId as ProdId
}

Using inner join

Inner join  ZI_PROD_FILTER_BYDATE(p_adeffdate:$parameters.p_adeffdate) as _Prod  on _Main.ProdId = 
                                                                                              _Prod.ProdId
{
     Key _Main.ProdHrId as ProdHrId,
         _Prod.prodId   as ProdId
}

Pushing down the multiple selection(select options) type filters

Sometimes it takes a lot to pass the multiselect filters from the UI layer to the database layer. But when performance of the application is a key factor, we can make use of the dynamic where clause in combination with the AMDP table functions and achieve the desired filter push down

Here in my current example , I have considered a CDS entity Set , redefined the Get_entityset method of the DPC_EXT class in my ODATA project and converted the multiselect filters into a dynamic where clause. After which I have passed the where clause down to the database table in my AMDP method through the Table function via the Consumption CDS view.

Along with Ad effective date which is a single select filter , I have two multiselect filters – Department and Sales Org

Multiple selection Filters example

Let me start with the consumption CDS view and then take you through the code in the ABAP layer.

My consumption CDS view will look like this

“Dept” and “SalesOrg” are my multiselect filters

@AbapCatalog.sqlViewName: 'ZCGETOFFER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Get offers'
@ObjectModel: {
  usageType.serviceQuality: #D, 
  usageType.sizeCategory: #XL,
  usageType.dataClass: #TRANSACTIONAL
}
define view ZC_GET_OFFER
  with parameters
    @Consumption.hidden: true
    P_WHERE      : abap.char(1333)
    as select from ZTABL_ALL_OFR(P_WHERE:$parameters.P_WHERE) as _AllOfr
    {
 
     key _AllOfr.OfrId     as OfrId,
     key _AllOfr.OfrIntId  as OfrIntId,
         _AllOfr.Dept      as Department,
         _AllOfr.SalesOrg  as SalesOrg
     
    }

Let me expand the table function – ZTABL_ALL_OFR

@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #CLIENT_DEPENDENT
@EndUserText.label: 'Offer details / Min adzone'
define table function ZTABL_ALL_OFR
  with parameters    
    P_WHERE      : abap.char(1333)
returns
{
  key clnt                   : abap.clnt;
  key OfrId                  : /dmf/ext_offer_id;
  key OfrIntId               : /bobf/conf_key;
      Dept                   : char2; 
      SalesOrg               : /dmf/org_unit_id_so;
      
}
implemented by method
ZCL_COM_FINAL=>ALL_OFFERS;

Let’s get more deeper into the AMDP method

method all_offers
         by database function
         for hdb
         language sqlscript
         options read-only
         using  ztabl_ofr. 
itab_ofr   =  SELECT _Ofr.mandt as clnt , _Ofr.OfrId , _Ofr.OfrIntId ,_Ofr.Dept , _Ofr.SalesOrg FROM 
              ztabl_ofr AS _Ofr where _Ofr.mandt = session_context('CLIENT');
itab_final = apply_filter ( :itab_ofr , :P_WHERE) ;
return select * from itab_final;
endmethod

After all the drill down , lets complete the top layer

Building a dynamic where clause and passing it as a parameter to the CDS entity

 method zc_Offer_get_entityset.

 constants : lc_dept           type c length 4 value 'DEPT',
             lc_salesorg       type c length 7 value 'SALESORG'.

 data: dept       type /iwbep/t_cod_select_options,
       salesorg   type /iwbep/t_cod_select_options.

 data:    lo_tech_request_clone type ref to /iwbep/cl_mgw_request,
          lt_headers            type tihttpnvp,
          lr_request            type ref to /iwbep/if_mgw_core_srv_runtime=>ty_s_mgw_request_context.          

data(ls_request) = cast /iwbep/cl_mgw_request( io_tech_request_context )->get_request_details( ).
data ls_key like line of ls_request-technical_request-key_tab
try.
 data(lt_dep) = it_filter_select_options[ property = lc_dept ]-select_options.
catch cx_sy_itab_line_not_found.
  clear lt_dep.
endtry.

try.
    data(lt_salesorg) = it_filter_select_options[ property = lc_Salesorg ]-select_options.
catch cx_sy_itab_line_not_found.
   clear lt_salesorg.
endtry.          

data(lv_where) = 
cl_shdb_seltab=>combine_seltabs( it_named_seltabs = value #( ( name = 'Dept' dref = ref #( lt_Dep[] ) )
                                                             ( name = 'SalesOrg' dref = ref #( 
                                                                               lt_salesorg[]  ) )  ) ).
    ls_key-name = 'P_WHERE'.
    ls_key-value = lv_where.
    DESCRIBE TABLE ls_request-technical_request-key_tab lines DATA(lv_lines). 
    Add 1 to lv_lines.
    insert ls_key into ls_request-technical_request-key_tab index lv_lines .

    get reference of ls_request into lr_request.
    data(lo_model_fw) = cast /iwbep/if_mgw_odata_fw_model( cast /iwbep/cl_mgw_dp_facade( me- >/iwbep/if_mgw_conv_srv_runtime~get_dp_facade( ) )->/iwbep/if_mgw_dp_int_facade~get_model( ) ).
    lo_tech_request_clone = new #( ir_request_details = lr_request it_headers = lt_headers io_model = 
                                                                                         lo_model_fw ).
  try.
        call method super->zc_get_offer_get_entityset
          exporting
            iv_entity_name           = iv_entity_name
            iv_entity_set_name       = iv_entity_set_name
            iv_source_name           = iv_entity_set_name
            it_filter_select_options = it_filter_select_options
            is_paging                = is_paging
            it_key_tab               = it_key_tab
            it_navigation_path       = it_navigation_path
            it_order                 = it_order
            iv_filter_string         = iv_filter_string
            iv_search_string         = iv_search_string
            io_tech_request_context  = lo_tech_request_clone
          importing
            et_entityset             = et_entityset
            es_response_context      = es_response_context.
      catch /iwbep/cx_mgw_busi_exception .
        clear : et_entityset,es_response_context.
      catch /iwbep/cx_mgw_tech_exception .
        clear : et_entityset,es_response_context.
    endtry.
endmethod.

This way we can take down the multiple select options till the database layer and not disturb the other query options rendered by the framework!

Now you are all set for a lightning fast application !

Do let me know how it worked for you! I will meet you in my next blog post where I shall discuss more on the analytical CDS views and their graphical representation in the fiori application