AMDP – ABAP consume Hana SQL / View Directly – Part I

Introduction

The important requirement in today’s business scenario is the reporting and that too with how quickly the  management user will execute the report that uses complex calculations and if and buts scenarios. Writing a query at ABAP end and do all calculations based on the if and buts may slow down the performance of report.

For an example – a SQL kind as shown below: (though it looks simple, assume of having some case statements as well)

SELECT
YEAR(C.BUDAT) AS "GJAHR", TO_NUMBER(MONTH(C.BUDAT)) AS "MONAT",
C.BUDAT AS BUDAT, C.MATNR AS MATNR, T.MAKTX AS MAKTX,
C.WERKS AS WERKS, C.ERFMG, C.ERFME
FROM (
(SELECT MANDT,MATNR,WERKS,BUDAT,SUM(ERFMG) AS ERFMG,ERFME
FROM AFFW GROUP BY MANDT,MATNR,WERKS,BUDAT,ERFME ) AS C
LEFT JOIN (SELECT MANDT,MATNR,MAKTX FROM MAKT WHERE SPRAS='E') AS T
ON (C.MANDT=T.MANDT) AND (C.MATNR=T.MATNR)
) WHERE C.BUDAT >= BEGDA AND C.BUDAT <= ENDDA ;

Hence the approach to database is taken up. Since Hana as a powerful database – pushing all these complex calculations and cases, – as a SQL / Views / Procedures into Hana database and call them as a object from ABAP as a query and print the report.

AMDP – ABAP Managed Database Procedure – A feature in ABAP that allows the developer to handle the database objects directly in ABAP.

We shall see below in detail.

Note : There are number of articles available in internet as blog posts.  The intention of this document is a self documentation that collects all what we learned and put it one place to refer future and may be helpful to others as well.

In this section we shall see how the AMDP class is created. Next section Part II – we shall see how we can pass parameters ( single, range ) to the Database view/SQL.

Brief Snapshot

ABAP Managed Database Procedures (AMDP) is a class-based AS ABAP framework for managing and calling database procedures and database functions in the SAP HANA database. Following picture presents the idea in simple way.

We shall see further how to create this class and how it is consumed in the ABAP programs.

Class structure

First of all let us have the class structure. The class is directly handling the SQL.  Since we are having the where clauses at SQL – the class is having the way to get input parameters from the ABAP and provide output as Dataset results from SQL.  in the next section we shall see in detail the different ways of input viz. Parameters, Range of Data etc.

The Class should have following

• Class Definition
  • The Table data type that matches with the SQL output
  • Standard Interface – IF_AMDP_MARKER_HDB.
  • Method
• Class Implementation
  • Method implemented with SQL call

Pre-Request

Before you start creating the class make sure you have completed the following items

1. The Actual SQL Script ( finalized SQL ).  Because any further change in the SQL may call for the change in Data dictionary structures, class implementation code.
2. Once SQL is finalized, create the table structure using SE11. In our case we created ZTT_COGI.

see below structure that we used for this class

Steps to create AMDP Class

1. Open Eclipse IDE – Create the Project – (for ABAP) – and Create the Class – using the Wizard.  (The Wizar will take you to define the name of the class, package and the appropriate transport requests). Complete this step.
2. Once completed you will get the screen to write the class definition and implementation code. We shall take an example of getting COGI detail.
3. The following screen shows the class definition written – example COGI.
5. 
6. You can see class Zcl_fi_cogi is defined. Refer to the blue highlight – an Interface to be declared to enable the AMDP.
   • An Interface IF_AMDP_MARKER_HDB – AMDP interface for Hana database
   • IT_OUTPUT – is defined as ZTT_COGI – where ZTT_COGI is a table type structure created which is similar to the SQL output. (make sure the table type structure is exactly similar to the SQL output columns)
   • Method definition – which has two input parameter and one output parameter.
7. Now we shall create the implementation method. Following screen shows the implemented method.
8. 
9. In the above method we have following points that to be noted.
   • The method has some reserved words used. METHOD GET_COGI

   • BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING. These key words are mandatory.
     
     The last two words, AFFW, MAKT are the tables that are going to be used in the SQL.

   • Next is the Actual SQL query (as if it is written in the database) – that output is stored in the GT_OUTPUT – which is belonging to the table type structure ZTT_COGI
10. Now save and activate the Class
11. Now the class is ready for use.

Consume the Class in ABAP report.

Now in ABAP report, you can use the Class as follows.

• First you should define the variable to hold the output of the class

DATA : gt_output TYPE ztt_cogi,
       ga_output TYPE zli_cogi,
       it_output TYPE ztt_cogi,
       wa_output TYPE zli_cogi,
       wa_output1 TYPE zli_cogi.

• Call the class (no need to instantiate). Directly call

  zcl_fi_cogi=>get_cogi( EXPORTING begda = s_dob-low endda = s_dob-high IMPORTING gt_output = gt_output ).

  LOOP AT gt_output INTO wa_output.
    MOVE-CORRESPONDING wa_output TO wa_summary.
    COLLECT wa_summary INTO it_summary.
  ENDLOOP.

The zcl_fi_cogi=>get_cogi – is the method call with appropriate parameters (Export parameter)

the output of the sql is stored in gt_output (Import Parameter)

Summary

Now we have completed the class creation and usage of the same in ABAP report.   We shall recap the steps again as a bullet points.

• Finalize the SQL
• Create the table type structure that holds the data set return from the SQL
• Define the AMDP Class & Implement the method
• Consume the class in ABAP report

Now as we see, in the SQL we have used Begda and Endda as the where clause parameters.  These parameters are passed to SQL through method export parameter. In our next blog – we can see how a select-option can be passed to class that will be used in SQL