Consume Dynamic SQL in AMDP within BW/4HANA TRFN : generating table name dynamically

Context

This blog post has a post reference to my previous blog post, where I had explained a scenario which involved aloading scenario where data target was an aDSO having several source aDSOs with similar structure. In the runtime we read the active table of the respective source aDSO using a common transformation between two infosources consisting of AMDP start routine. In the code used in my previous blog post had the direct reference to the source aDSOs (in ‘USING’ clause & then left outer joins with :inTab). However, I went on trying to make this derivation dynamic. Imagine we end up having more source aDSOs for business need & everytime we have to modify the existing AMDP to add & hardcode the new aDSO’s active table reference. In order to do that, we sought help of dynamic SQL where we derive the active table name based on the AMDP parameter :i_req_src_name (the technical name of source aDSO of the TRFN).

Pre-observations

It’s important to state before I start explaining the actual solution I followed, that I have made this illustration based on HANA 2.0 & I tried to build a procedure which upon passing the aDSO’s name as input parameter would prepare the active table name of the same & use that in the from clause of the select & derive the maximum billing date available from that table into output parameter to be used later when being called from the encapsulating AMDP start routine. However it did not work for the following reasons – firstly, the created procedure would be residing in a schema which is outside SAP DB owned DDIC dictionary object schema & thus it would be unknown to the AMDP. Also, the prerequisite to use the procedure is that the procedure should be within AMDP namespace, meaning the name of the procedure should start with ‘/1BCAMDP/’. But due to obvious authorization reasons, the stored procedure could not be created within the SAP DB owned schema, so the stored procedure continued to show errors while being called from the AMDP start rouine. Secondly I stumbled upon creating the procedure or a function within the same AMDP start routine since the DDL statements like CREATE/DROP are only allowed in a read only AMDP in case of tables & not for proceudres or functions.

Case Study

I will explain the requirement as introduction of the case study. Let’s take the same example from my previous blog post : I have 3 aDSOs which load data to a single aDSO target. The 3 source aDSOs have billing dates respectively for 3 different countries, for a particular combination of store & article in the delta data we need to find the maximum available billing date in the active tables of the aDSOs & stamp that into the target aDSO as shown in the example in the blog post. Now imagine if we add a fourth aDSO as source, we need to change the AMDP to include the name of the active table in the join condition, similarly everytime we get a new country as source aDSO. To avoid modification in the code everytime, I tried to make the code dynamic, so that it can derive the max(BILL_DATE) from the active table of the source of the load without being hardcoded. As explained in earlier blog post, the technical name of the source aDSO is already available as parameter :i_req_src_name in global area of the AMDP. So, basically I needed a variable to derive the table name by concatenating “/BIC/’A’ & ‘2’ in beginning & at the end of the parameter value :i_req_src_name.

Now as I stated the problems regarding creating a procedure or function residing in a schema outside the DB owner schema, the solution was to write the code in another AMDP method. This way it is secured to be placed within the same DB owned schema as DDIC definitions. Below is the AMDP method for preparing the active table name from the techical name of the aDSOs & deriving the max(BILL_DATE) with help of EXECUTE IMMEDIATE statement.

** Declaration of input & outptut parameters :

TYPES: BEGIN OF LS_INTAB,
         ADSO TYPE c LENGTH 9,
         STORE TYPE c LENGTH 4,
         ARTICLE TYPE c LENGTH 18,
         END OF LS_INTAB.
  TYPES: LT_INTAB TYPE TABLE OF LS_INTAB.


  TYPES: BEGIN OF LS_OUTTAB,
         STORE TYPE c LENGTH 4,
         ARTICLE TYPE c LENGTH 18,
         BILL_DATE TYPE c LENGTH 8,
         END OF LS_OUTTAB.
  TYPES: LT_OUTTAB TYPE TABLE OF LS_OUTTAB.

  CLASS-METHODS MAX_BILL_DATE
      IMPORTING
        VALUE(INTAB) TYPE LT_INTAB
      EXPORTING
        VALUE(OUTTAB) TYPE LT_OUTTAB.


** Implementation of the AMDP method :

CLASS ZCL_MAX_BILL_DATE IMPLEMENTATION.

  METHOD MAX_BILL_DATE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

    DECLARE LV_ADSO NVARCHAR(9);
    SELECT DISTINCT ADSO INTO LV_ADSO FROM :INTAB;

    EXECUTE IMMEDIATE
     'SELECT STORE, ARTICLE, MAX(BILL_DATE) as "BILL_DATE" FROM "<SCHEMA_NAME>"."/BIC/A' || :LV_ADSO  || '2" GROUP BY STORE, ARTICLE' into OUTTAB;

  ENDMETHOD.

ENDCLASS.

In the input parameter of the reusable AMDP we have to pass the store, article information along with the aDSO’s technical name retrieved from :i_req_src_name. This is storded in a temporary variable lv_adso to form the active table name using the concatenation function with help of dynamic sql statement EXECUTE IMMEDIATE.

Now the passing of inpur parameter table & retriving the output parameter table happens from the caller AMDP in BW/4 TRFN. That part works like below :

*** Prepare placeholder local table to store output from called AMDP
DECLARE tt_outp TABLE ( "STORE" NVARCHAR(4),
       "ARTICLE" NVARCHAR(18),
       "BILL_DATE" NVARCHAR(8) );

*** Prepare placeholder local table to store input for called AMDP
tt_inp = select distinct :i_req_src_name as "ADSO", STORE, ARTICLE from :intab;


*** Call AMDP to derive last billing date from source ADSO's active table  

"ZCL_MAX_BILL_DATE=>MAX_BILL_DATE"( INTAB=>:tt_inp, OUTTAB=> :tt_outp);

outTab = select 
to.BILL_DATE as BILL_DATE,
ti.STORE,
ti.ARTICLE,
ti.RECORD,
ti.SQL__PROCEDURE__SOURCE__RECORD

from :intab as ti INNER JOIN :tt_outp as to
ON ti.STORE = to.STORE AND
   ti.ARTICLE = to.ARTICLE;

errorTab = select '' as ERROR_TEXT, '' AS SQL__PROCEDURE__SOURCE__RECORD from DUMMY where DUMMY <> 'X';

ENDMETHOD.
ENDCLASS.

This way it is totally independent of how many source aDSOs are used to load into the target aDSO in future, it is capable of preparing the active table name by itself & derive the maximum billing date for an article-store combination from the source aDSO’s active table of the respective load requests.

Note 

This illustratation is meant to explain the usage of a reusable script within an AMDP which is drafted in BW/4 transformation. There can be other ways to eke out the same without using the dynamic SQL.
Moreover, usage of dynamic SQL in BW/4 TRFN related AMDP routines ofcourse has some downsides & one needs to decide based on proper investigation & comparison between the flexibility & the downsides of using dynamic SQLs. Few of the main drawbacks are :
1. The possibility to look through the where used list of the source aDSO tables are lost in HANA, it’ll  no longer be shown in OBJECT_DEPENDENCIES view since the table names are prepared dynamically.

2. The scope for optimization is little or none in case of complicated scenarios leading to performance penalty.

There could be more such reasons & thus usages of dynamic SQL in AMDPs are rather generally discouraged. But again, it’s the developer or solution maker’s decision based on the comparison between the drawbacks & the flexibility gained.

Hope this blog post will help someone seeking for solution similar to one in context. Also if the readers have some experience/comments/feedbacks to share regarding the same topic, you’re more than welcome 🙂

For Q&As on similar topics, please use the following Q&A section.

Best Regards,
Abhi