AMDP class and methods and how to use in an extractor

Introduction

An AMDP class is a global class in the class library that contains one or more of the following tag interfaces:

◈ IF_AMDP_MARKER_HDB for the SAP HANA database

The names of the interfaces all start with IF_AMDP_MARKER and a suffix indicates the database system for which the ABAP Managed Database Procedures can be implemented in AMDP methods of the class.

An AMDP class can contain both regular methods and AMDP methods. It can contain one or more AMDP methods for each database system specified by a tag interface.

In simple words to select from HANA DB tables instead of using ABAP you can use  AMDP which has a slightly different syntax and is way more faster.

Getting started

Creating a class implementation and method.

Create a class and method using HANA studio which automatically populates definition and implementation

Modified the class method to add interface  IF_AMDP_MARKER_hdb

Added a Type and table to be returned from the class method get_result and raise exception CX_AMDP_ERROR.

CLASS zcl_test_extractor_amdp DEFINITION

PUBLIC

FINAL

CREATE PUBLIC.

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb .

BEGIN OF ty_attributes,

object_id      TYPE crmt_object_id_db

,guid           TYPE crmt_object_guid

,process_type   TYPE crmt_process_type_db

,P_DESCRIPTION  TYPE crmt_description

,process_desc    type crmt_description

END OF  ty_attributes.

TYPES:

tt_attributes     TYPE STANDARD TABLE OF  ty_attributes,

CLASS-METHODS get_result

IMPORTING

VALUE(iv_clnt)             TYPE mandt

VALUE(ip_filters)          TYPE string

EXPORTING

VALUE(et_attribute_details) TYPE   tt_attributes

RAISING

cx_amdp_error.

ENDCLASS.

Now start with the implementation statement as below

CLASS zcl_test_extractor_amdp IMPLEMENTATION.

and below starts the actual code

Method get_XXX_details by database procedure for hdb language sqlscript

using ekko bseg bkpf.

  METHOD get_result

      BY DATABASE PROCEDURE

      FOR HDB

      LANGUAGE SQLSCRIPT

      OPTIONS READ-ONLY

      USING  crmd_orderadm_h    crmc_proc_type     crmc_proc_type_t

   lt_result =

     SELECT DISTINCT

     oh.object_id     AS object_id

    ,oh.process_type  AS process_type

    ,pd.P_DESCRIPTION as P_DESCRIPTION

    ,oh.created_at    AS created_at

    ,oh.created_by    AS created_by

  FROM crmd_orderadm_h            

AS oh

  JOIN crmc_proc_type             

AS pt ON pt.process_type = oh.process_type and pt.client = IV_CLNT

  JOIN crmc_proc_type_t           

AS pd ON pd.process_type = pt.process_type AND pd.langu = 'E'

WHERE oh.object_type = 'BUS2000126' AND oh.process_type = 'ZBKG' and

oh.client = IV_CLNT

ORDER BY oh.object_id;

 et_attribute_details  = APPLY_FILTER( :lt_result, :ip_filters );

   ENDMETHOD.

Syntax for common operations

Select based on importing parameter coming in the class

Here IV_FROMDATE and IV_TODATE are coming as incoming paramaters in the class.

* Fetched the Changed PO details from ECC

lt_ebeln = SELECT ebeln

FROM   ekko

WHERE  aedat BETWEEN iv_fromdate

AND      iv_todate;

Select based on an internal table

Select data based on internal table lt_ebeln.

Note; the internal table is named using a colon:

 lt_ebeln =   SELECT i.ebeln

    FROM  bkpf as h

    INNER JOIN bseg as i

    on i.bukrs = h.bukrs AND

       i.belnr = h.belnr AND

       i.gjahr = h.gjahr

    UNION ALL SELECT ebeln from:lt_ebeln;

Select Unique records

Select unique records using keyword DISTINCT

lt_re_hyp = SELECT DISTINCT * FROM :lt_re_hyp;

To filter unique values from an internal table

To filter specific value based on value in internal table or variable use Apply_filter.

lt_na = apply_filter( :lt_base, :iv_proj_type_na );

Combine select from 2 internal tables

Get the final result by combining data from multiple internal table

Example lt_cc and lt_wbs using UNION by first selecting from lt_cc and lt_wbs to get the final result in lt_details.

    lt_details =

*Cost Center Items

    SELECT DISTINCT

    base_cc.gl_account as gl_account,

    base_cc.ryear as ryear,

    cc_hyp.zz_hpp_project as hp_proj_id,

    from :lt_cc as base_cc

    left outer join :lt_cc_hyp as cc_hyp

    on base_cc.kostl = cc_hyp.kostl

*WBS Items

    union all

    select distinct

    base_wbs.gl_account as gl_account,

    base_wbs.ryear as ryear,

    wbs_hyp.zz_hpp_project as hp_proj_id,

    from :lt_wbs as base_wbs

    left outer join :lt_wbs_hyp as wbs_hyp

    on base_wbs.projk = wbs_hyp.pspnr ;

To add select criteria based on a range 

When selecting data use keyword IN and specify the range

 LEFT OUTER JOIN tj02t    AS ct ON ct.istat = cs.stat

 AND ct.spras = 'E' AND ct.txt04 IN ('CANC','DEF')

Using case and end case

CASE and end case to add a specific value

 et_details =

   select

    base.gl_account as gl_account,

    '' as activity,    case

    when base.period = '001' then 'Jul'

    when base.period = '002' then 'Aug'

    when base.period = '003' then 'Sep'

    when base.period = '004' then 'Oct'

    when base.period = '005' then 'Nov'

    when base.period = '006' then 'Dec'

    when base.period = '007' then 'Jan'

    when base.period = '008' then 'Feb'

    when base.period = '009' then 'Mar'

    when base.period = '010' then 'Apr'

    when base.period = '011' then 'May'

    when base.period = '012' then 'Jun'

    else 'NA'

    end as period,

    '' as scenario,

    base.year as year,

    SUM ( base.hsl ) as hsl

    from :lt_details as base

    group by gl_account, zzlocbrd, hp_proj_id, prctr, year, period;

Syntax for using SUM 

To sum up the value using the keyword SUM though I am not still sure if it works as expected

I prefer to get all values and then sum outside of AMDP

lt_sum = SELECT DISTINCT

oh.object_id as object_id ,

sum( pi.net_value ) as total_value

      FROM crmd_orderadm_h     AS oh

       inner JOIN crmd_orderadm_i     AS oi ON oi.header = oh.guid

      JOIN crmd_pricing_i      AS pi ON pi.guid = oi.guid

      LEFT OUTER JOIN tj02t    AS ct ON ct.istat = cs.stat AND ct.spras = 'E' AND ct.txt04 IN ('CANC','DEF')

      inner JOIN  :et_attribute_details  as base

    on oh.object_id = base.object_id

      AND ct.txt30 IS NULL

      GROUP BY oh.object_id , pi.net_value  ;

Endnotes

◈ Important to add IV_MANDT to ensure that specific client data is only selected

◈ AMDP classes are best called when you want to send data to the BI team and include it as a part of an extractor as they are very fast compared to traditional way of calling a ABAP based select statement

◈ When you select value it should have same select sequence example if your return table has a sequence as  object_id, GUID, Process_type then select statement should also select in the same sequence else it gives an error “SQLSCRIPT Return type mismatch”

◈ There is no need to explicitly declare the tables you can mention lt_table = SELECT *  …. and it would define the structure to be same as what you select

◈ AMDP class and methods can only be edited in HANA studio and can only be debugged from there

◈ Not easy to debug them (or maybe I don’t know how to )

◈ If you require calculations to be done its best to get the data in AMDP and do the remaining calculations outside.

◈ I found a comparable difference between the speed of result returned from an ABAP report vs AMDP select query AMDP query took 2 min to fetch 10000 records and  ABAP query (with same select parameters) took 14 mins

◈ AMDP class does not allow a lot of calculation to be done and would rather be used just as a select query post calculations, summing calculations etc to be done manually

◈ Using AMDP in an extractor. Transaction RSA2 for creating an extractor and add Z_ Function module as shown

Inside this FM I am calling the AMDP class