Hierarchy Evaluation using CDS Table Function

For space requirements this blog has been split in 4 parts:

In my previous blog Core Data Services in ABAP I have explained features of ABAP CDS Views, here I am introducing one more feature CDS Table function and its usage.

In this blog I am going to explain how to evaluate hierarchy using CDS table function. Two cases needs to be considered here:

1. Display of hierarchy on the UI, in which user can navigate, (expand/collapse) nodes.
2. Perform a hierarchy evaluation in the backend and only return the result of that evaluation.

The hierarchy annotations in CDS are used for the first type: Fiori apps can display hierarchies accordingly. Additionally, the infrastructure that display the hierarchy, can aggregate numbers along the define hierarchy. This case I will explain in my next blogs.

Here I am going to explain second case hierarchy evaluation in the backend. Hierarchy evaluation, however, is currently not yet pushed down to HANA. An alternative would be to evaluate the parent/child recursion “on your own” within a view. This is currently not possible by pure CDS technology but you would require a CDS table function with embedded native HANA script code to evaluate the recursion.

CDS table function uses ABAP Managed Database Procedure(AMDP) class and method to evaluate the hierarchy.

Here I use manager employee hierarchy as an example as shown below:

To achieve the above result below are the steps to be followed:

1. Create a AMDP class and method for table function as show below:

CLASS ZCL_L_MANGE_EMP_HIER DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
   INTERFACES IF_AMDP_MARKER_HDB.
    CLASS-METHODS GET_MANG_EMP
        FOR TABLE FUNCTION Z_L_MANGEMP_HIER
     .
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

Class definition is shown above. AMDP Class(ZCL_L_MANGE_EMP_HIER) can only be created from HANA Studio or ABAP Development tools(ADT) in ABAP perceptive because it is supported only in ADT or HANA studio. Class becomes AMDP class if interface IF_AMDP_MARKER_HDB is implemented in public section as shown above. Create a class method i.e static method get_mang_emp for table function Z_L_MANGEMP_HIER. 

Here Z_L_MANGEMP_HIER  is a CDS table function created similar to CDS view creation.

2. Class and Method implementation shown below:

method declaration needs to have BY DATABASE FUNCTION syntax so that it will return the results to CDS table function, FOR HDB define database used, LANGUAGE SQLSCRIPT meant only native SQL code is allowed in this method implementation, OPTIONS READ-ONLY specifies read only method, after USING clause all the SQL tables, classes which are used for method implementation needs to be mentioned.

3. Method implementation is shown below:

CLASS ZCL_L_MANGE_EMP_HIER IMPLEMENTATION.

  METHOD GET_MANG_EMP
          BY DATABASE FUNCTION
          FOR HDB
          LANGUAGE SQLSCRIPT
          OPTIONS READ-ONLY
          USING IEMPLOYMENTMGR IEMPLOYEE.

   declare lv_count integer;

   declare uname NVARCHAR(12);

   declare clnt NVARCHAR(3);

uname:=  session_context('APPLICATIONUSER');

clnt := session_context('CLIENT');

employee = select Employee from IEMPLOYEE
where UserID = :uname;

var_root = select mandt, employmentinternalid, manageremployee
   from iemploymentmgr  where manageremployee = '00000203'
   union
    select clnt, '00000203', '' from dummy;

    var_out = select * from :var_root;
     select count ( * ) into lv_count from :var_root;
while :lv_count > 0

do
    var_childs = select distinct f.mandt, f.employmentinternalid, f.manageremployee from iemploymentmgr as f
    inner join :var_root as outp
    on f.manageremployee = outp.employmentinternalid;

var_except = select f.mandt, f.employmentinternalid, f.manageremployee from :var_childs as f
inner join :var_root as outp
    on f.employmentinternalid = outp.manageremployee;

var_root = select * from :var_childs EXCEPT ( select * from :var_except ) ;

var_out = select * from :var_root
union all
 select * from :var_out;
select count ( * ) into lv_count from :var_root;
    end while;

return select distinct mandt, employmentinternalid, manageremployee from :var_out
group by mandt, employmentinternalid, manageremployee ORDER BY manageremployee;


  ENDMETHOD.

the logic for employee manger hierarchy is written in AMDP method which returns employee and manger results to CDS table function. Return statement supports only select query. Here In my case I am using tables IEMPLOYMENTMGR, IEMPLOYEE to evaluate the hierarchy.

4. CDS table function definition is as shown below:

@ClientDependent: true
define table function z_l_mangemp_hier

returns
{
  key mandt:s_mandt;
  key EmploymentInternalID : pernr_d;
  ManagerEmployee      : pernr_d;

}

implemented by method
  zcl_l_mange_emp_hier=>GET_MANG_EMP;

Z_L_MANGEMP_HIER is table function created similar to CDS view creation. You can see in my previous blog Expose CDS view as OData Service

client handling needs to be explicitly handled i.e mandt should be the first field for table function. @ClientDependent:true annotation is used to specify that table function is client dependent. DEFINE TABLE FUNCTION table-function-name is the syntax used to define the table function.

Fields which are returned from AMDP method are written in returns{ } block as shown above. Here mandt, employee, manger fields are returned from AMDP method. IMPLEMENTED BY METHOD  is used to specify implemented AMDP class and method.

Execute the CDS table function to see the result. You will get output as expected:

CDS table function can be consumed as data source in other CDS view so that you add additional measures, fields to the definition.

Note: CDS table function needs to be used only in exceptional cases where you can’t achieve functionality using pure CDS technology. In such a case a central review and approval is mandatory. By default the definition and usage of table functions within the VDM is forbidden and will result in ATC check errors.

For requesting a CDS table function review and approval drop a mail to DL VDM_CDS_GOVERNANCE providing purpose, which context it is used.

Hope you find this blog helpful!!

In my next blog Step by Step Hierarchies in S/4 HANA Analytics I will show standard way in which infrastructure handles the hierarchies in S/4, How hierarchies are displayed on UI using SAPUI5 application.

Your suggestions, feedback, comments on this blog are most welcome.