Demo: Call another procedure in AMDP

Task
Call the method from the previous demo with language E in a new AMDP method GET_COUNTRIES_TEXT_EN.

Performance gain with HANA execution

The higher performance with SAP HANA execution has several reasons:

• No copying of data between ABAP and HANA.
• Larger data packages (typically: 1,000,000 vs. 50,000) reduce overhead for status management and monitoring.
• By processing directly on the SAP HANA database, data processing can be well parallelised.
• Existing routines are implemented in SQLScript for SAP HANA transformations. These can then be optimised well.

Transformation routines as AMDP

All routines of a transformation must be implemented in the same technology: Either ABAP or AMDP.

The generated AMDP class

BW on HANA

• For each routine, a separate AMDP class is generated in the $TMP package with a cryptic GUID name in the /BIC/ namespace.
• The connection from routine to transformation is stored in the transformation.
• This always contains exactly one method with the name PROCEDURE.

BW/4HANA

• For each transformation, two AMDP classes are generated in the /BIC/ namespace.
• The TRF name is contained in the class name.
• The classes have the ending _M(=Modified) and _A (=Active).
• The classes contain one method per routine.

Simulation of a transformation routine in the console.

Testing transformations repeatedly is time consuming. Yet it is relatively easy to test the code of a transformation in the SQL console.

Procedure:

• Create an anonymous block: DO BEGIN..END
• Insert the transformation code
• Define and fill the table INTAB at the beginning of the block
• Query the OUTTAB at the end

DO BEGIN
  intab = SELECT *, 
                 '' AS record, 
                 '' AS sql__procedure__source__record
            FROM "/BIC/A<DSO name>2"            
            --if necessary WHERE condition or LIMIT 1000 to keep the amount of data small 
--------------------------------
  --Here comes the routine code
--------------------------------
  SELECT * FROM :outtab;
END;

Signature of the AMDP methods

The interface of the PROCEDURE method is generated to match the transformation. There are the same parameters for all four routine types:

• Input Table INTAB - The data is passed to the routine via the IN parameter with the name INTAB.
• Output table OUTTAB - The result of the routine is written to the table parameter OUTTAB. To activate the AMDP class, it is necessary that this parameter is filled with an appropriate SELECT query.

As of BW/4HANA, only if error processing is switched on:

• Output table ERRORTAB - Incorrect records are written to this table. It consists of two fields:
  • ERROR_TEXT - Description of the error
  • SQL__PROCEDURE__SOURCE__RECORD - Field from the corresponding record of the INTAB.
• scalar parameter I_ERROR_HANDLING - This parameter has the value TRUE if the current procedure call queries the error table ERRORTAB.

The RECORD field in the tables

The RECORD field is a 56 character string.

This may be relevant for the order of insertion into the target of the transformation. Initially, the RECORD field contains the concatenated REQUESTID, DATAPAKID and RECORD fields from the source of the transformation.

For successive transformations, it contains the value of the RECORD field of the previous transformation.

SQL__PROCEDURE__SOURCE__RECORD

The field SQL__PROCEDURE__SOURCE__RECORD contains a unique key for records in the table INTAB.

This key can be used to assign entries in the error table ERRORTAB to the respective record from INTAB. If new data records are generated in a routine, the value of the respective source data record is to be taken over.

Assignment of the output tables

The output table OUTTAB and possibly also ERRORTAB must be assigned in the SQLScript source code, otherwise there will be syntax errors. This is also necessary if you do not want to perform error processing.

 METHOD global_end BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
    OUTTAB = SELECT * FROM :INTAB;
    ERRORTAB = SELECT '' AS ERROR_TEXT,
                      '' AS SQL__PROCEDURE__SOURCE__RECORD
                 FROM DUMMY WHERE DUMMY <> 'X';
  ENDMETHOD.

This code corresponds to the initial code of the generated method of an end routine on a BWoH 7.50, although it has been formatted a little more attractively in the example.

• The table OUTTAB is filled 1:1 from the INTAB.
• The table ERRORTAB obviously remains empty.

ERRORTAB = SELECT * FROM :ERRORTAB;

Tables & Views of the ADSOs

The table and view names for the ADSOs are formed according to the following scheme:
/BIC/A<DSO name><suffix>

NOT NULL

There must be no NULL values in the result tables. If there are, an error message will be displayed:

The automatic initialisation exists from BW/4HANA 2021:

Start routines

The start routine is run at the beginning of the transformation, i.e. before the assignment of the individual fields and their processing with the field rules takes place. Accordingly, the two table parameters 'INTAB' and 'OUTTAB' include the fields of the data source.

Typical tasks of the start routines are:

• Filtering records if the selection criteria in the DTP are not sufficient, e.g. because they can only be determined by a join.
• Adding data records, which are then processed in the further steps of the transformation exactly like the original data.

Both can be done in the same way in the end or expert routine!

Expert routines

The expert routine replaces the entire transformation logic. So if an expert routine is created, field rules (1:1 mapping, formulas, lookups, etc.) are no longer possible. The table 'INTAB' contains only the fields of the source and the table 'OUTTAB' the fields of the target of the transformation.

Typical use cases

• Fields from source and target are needed
• The logic would otherwise be spread over many places (field rules, start and end routine)
• Trigger DSOs

Error processing and error stack

TLDR - It does not work cleanly!

Error processing can occur in all four types of routines. This means that the routine writes the values of the SQL__PROCEDURE__SOURCE__RECORD field of the erroneous records from the INTAB to the ERRORTAB table. There, a suitable error text can also be entered in the ERROR_TEXT column.
Please test error handling thoroughly

In the past, the area of error handling and error stack had repeatedly caused problems. It is advisable to test these functions thoroughly before using them, even after importing new SPs.

Processing sequence in the data transfer process

Every transformation, and therefore every routine, is run through twice when executing a data transfer process.
In the first run, the incorrect data records are determined and in the second run, the actual processing takes place.

Alternatives are:

• Large package sizes. However, one must consider that with under also data must be completely rebuilt. Then the package size of the daily loads may no longer be sufficient...
• Re-reading the complete source data for the keys:

 new_intab = select *
                from "/BIC/AZBR_E2_S2"
                WHERE ( "BUDAT", "ACCOUNT" ) in (select budat,
                                                         account
                                                    from :intab);

The status of the breakpoints

The circle of the breakpoint changes colour depending on its status:

After 10 minutes of inactivity, the debugger deactivates itself.

Debug Mode vs. Optimised Mode

In order for procedures to be analysed with the AMDP debugger, they are recompiled in the so-called debug mode. This means that some optimisations are not performed that are taken into account in the normal, optimised mode.

After debugging, the routine remains in the debug version for a while (about 1h).

In BW on HANA: Orphaned AMDP classes

If you create an AMDP routine from the transformation in transaction RSA1, an ABAP class is generated and saved for it. However, the transformation is not yet saved at this point - this only happens when you click on Save in the transformation.

It happens again and again that you implement the AMDP method PROCEDURE in the generated class, but do not save the corresponding transformation. This means that the logical assignment between transformation and AMDP class has disappeared.

You can use the table 'TADIR' to determine the old class name and thus copy the code.

Block marking

Alt+Shift+A - Very useful to copy the field lists from the declaration.

Element Info with F2 or as View

With F2, details about method definitions can be displayed in AMDP routines.

Code completion with 'Ctrl + Space

Compare with Local History