Calling Stored Procedure from SAP ABAP Application [BW on HANA, BW/4HANA, S/4HANA]

Stored Procedure – Introduction

Stored Procedure are frequently used Objects in SAP HANA. Stored Procedures allows to group the SQL statement into a single block and can return data in the form of output parameters.

In Mixed modelling Scenario calling a Stored Procedure from SAP Application can also be a good approach. Below is one Scenario we have faced in our Project.

Scenario: We had created a S/4HANA Report based on a Transient Provider and when checking for InfoProvider report gives a System Generated Tech name 2H*.

The InfoProvider here is a calculation View and instead of its technical Name the report shows a system generated Name 2H*. This is not a meaningful Information for a BW Developer and to find out the actual name of the Calculation View leads to the below development

Solution:

The generated name 2H* is in ABAP layer metadata and the Actual Calculation View Name is present in the HANA Metadata.

Hence, we are looking for a solution which combines both ABAP and HANA to find the required meaningful information.

We end up creating a report in ABAP which inside looks in HANA database tables/views by calling a AMDP Script and HANA Stored Procedure.

For this solution a small set of development is required:-

1. A Stored Procedure to get the Meta data information of Calculation View.

2. An AMDP Class (created in ABAP Perspective) with Method to call the stored procedure.

3. ABAP Program (Main program) where user input the  system generate calculation view name (2H*) and gets the actual Calculation View name. In this program we are calling the AMDP Class.

The Metadata of Calculation view is present in the Schema  “_SYS_BI” view name “BIMC_ALL_CUBES”.

The below stored procedure will get the desired metadata information for all the calculation view present in the HANA system.

Stored Procedure:-

create procedure "_SYS_BIC"."user_space.shankpre::clv_tech_name" ( 

OUT tab_out table (

              "NAMESPACE" nvarchar(256),

              "OBJECT_NAME" nvarchar(512)) )

   LANGUAGE SQLSCRIPT

   SQL SECURITY INVOKER 

   DEFAULT SCHEMA _SYS_BIC

   READS SQL DATA AS

/********* Begin Procedure Script ************/ 

BEGIN       

   tab_out =  select 

CATALOG_NAME as NAMESPACE,

CUBE_NAME  as OBJECT_NAME 

from 

"_SYS_BI"."BIMC_ALL_CUBES" ;

END 

/********* End Procedure Script ************/

This above Stored procedure will be called into SE38 Program via AMDP class.

Please find below SE38 ABAP program:-

REPORT ZBW_HCPR_CLV.

parameters: p_clv type c length 30.

    types: begin of l_s_objxref_hana,

             namespace      type c length 256,

             object_name    type c length 512,

           end of l_s_objxref_hana.

    data: l_t_hana_xref  type standard table of l_s_objxref_hana,

          ip_name type rsinfoprov.

    field-symbols: <ls_xref> like line of l_t_hana_xref.

ZCL_AMDP_CLV=>CLV_TECH_NAME( importing out_rows = l_t_hana_xref ).

loop at l_t_hana_xref assigning <ls_xref>.

  call method cl_rodps_hana_model=>hash_odpname

    exporting

      i_package = <ls_xref>-namespace

      i_name    = <ls_xref>-object_name

    receiving

      r_odpname = ip_name .

  concatenate '2H' ip_name into ip_name.

  if

    ip_name = p_clv.

    write: / <ls_xref>-namespace, at 100 <ls_xref>-object_name.

  endif.

endloop.

As you have noticed the statement in the above program, we are calling an AMDP class to get the data from Stored Procedure.

AMDP Class:-

Please see the below AMDP class, the generated code has been simply modified as below

class zcl_amdp_clv definition

  public

  final

  create public .

  public section.

  interfaces if_amdp_marker_hdb.

  class-methods CLV_TECH_NAME exporting value(out_rows) type ZBI_CLV.

  protected section.

  private section.

ENDCLASS.

CLASS ZCL_AMDP_CLV IMPLEMENTATION.

  method CLV_TECH_NAME BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

CALL "_SYS_BIC"."user_space.shankpre::clv_tech_name"(:out_rows);

  endmethod.

ENDCLASS.

In the second last row there is a call to the stored procedure, which brings the calculation view data from HANA database.

The above development can give us the desired result as below.

Results:

Input of the Program User gives the CLV 2H* Name

Input of Program

Output of the Program User gets the actual technical calculation view name.and the package name..

Output of the Program

Result shows the actual Name of the Calculation view along with the Package path.

Logic Explained: The program brings all the calculation view present in the system from “_SYS_BI”.”BIMC_ALL_CUBES”  by calling the stored proc and then this values will be passed into the SAP Standard class method cl_rodps_hana_model=>hash_odpname and then the result will be compared against the user input value to get the desired result.

◉ If huge number of CLVs present in the system then this program might be a little slow, as the Stored Procedure is calling all the available calculation view in the system, please test the performance in Dev system first.

◉ Additional development will be of Table Type ZBI_CLV, it is used in the AMDP class, the structure will be same as the structure of tab_out table of Stored procedure.

◉ If the same thing needs to be done in BW system where report is built on top of a composite Provider (which has underlying calculation View).