Developer ToolsFree generatorPDF, Excel, Word exportSAP BW/4HANAAll SAP BW and BW/4HANA tools

SAP BW Transformation Routine Generator: Start, End, Field and AMDP Routines

This generator produces the code you paste into BW transformation routines: an ABAP start routine that drops records, an ABAP end routine that looks up a field from an ADSO, a field routine body, and the equivalent AMDP SQLScript start and end routines. It targets BW 7.5 and BW/4HANA, where routines are methods of a generated class.

Fill in

Source field to filter on

Upper case, as the column is named in SOURCE_PACKAGE and in the AMDP inTab.

Value to drop in start routine

Lookup ADSO active table

BW/4HANA ADSO active data table is /BIC/A<adso name>2.

Lookup key field

Lookup result field

Target field to fill

Generated code

Download:

1. ABAP start routine: drop records

METHOD start_routine.
*   Body only: keep the generated signature (SOURCE_PACKAGE, LOG, ...)
    DELETE source_package WHERE COMP_CODE = '9999'.

*   To stop the whole load on a fatal condition:
*   RAISE EXCEPTION TYPE cx_rsrout_abort.
ENDMETHOD.

2. ABAP end routine: lookup from an ADSO

METHOD end_routine.
    TYPES: BEGIN OF ty_lkp,
             lkp_key TYPE /BIC/AZSD_O012-/BIC/ZCUSTOMER,
             lkp_val TYPE /BIC/AZSD_O012-/BIC/ZREGION,
           END OF ty_lkp.
    DATA lt_lkp TYPE STANDARD TABLE OF ty_lkp WITH DEFAULT KEY.

    IF result_package IS NOT INITIAL.
      SELECT /BIC/ZCUSTOMER AS lkp_key, /BIC/ZREGION AS lkp_val
        FROM /BIC/AZSD_O012
        FOR ALL ENTRIES IN @result_package
        WHERE /BIC/ZCUSTOMER = @result_package-/BIC/ZCUSTOMER
        INTO CORRESPONDING FIELDS OF TABLE @lt_lkp.
      SORT lt_lkp BY lkp_key.
      DELETE ADJACENT DUPLICATES FROM lt_lkp COMPARING lkp_key.
    ENDIF.

    LOOP AT result_package ASSIGNING FIELD-SYMBOL(<ls_res>).
      READ TABLE lt_lkp INTO DATA(ls_lkp)
        WITH KEY lkp_key = <ls_res>-/BIC/ZCUSTOMER BINARY SEARCH.
      IF sy-subrc = 0.
        <ls_res>-/BIC/ZREGION = ls_lkp-lkp_val.
      ENDIF.
    ENDLOOP.
ENDMETHOD.

3. ABAP field routine body

*   Inside the generated field routine method for the target field.
*   SOURCE_FIELDS holds the source fields you assigned to the rule.
    RESULT = SOURCE_FIELDS-/BIC/ZCUSTOMER.

*   Skip the whole record instead of filling a value:
*   RAISE EXCEPTION TYPE cx_rsrout_skip_record.

4. AMDP start routine (SQLScript): drop records

METHOD procedure BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
-- Keep the method name and signature the framework generated.
-- In a start routine outTab has the same structure as inTab.
  outTab = SELECT * FROM :inTab
           WHERE "COMP_CODE" <> '9999';

  errorTab = SELECT '' AS ERROR_TEXT,
                    '' AS SQL__PROCEDURE__SOURCE__RECORD
               FROM DUMMY
              WHERE DUMMY <> 'X';
ENDMETHOD.

5. AMDP end routine (SQLScript): lookup from an ADSO

METHOD procedure BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
  USING /BIC/AZSD_O012.
-- List every outTab column in the generated order; only the lookup
-- column changes, the rest are passed through from inTab.
  outTab = SELECT i."/BIC/ZCUSTOMER",
                  COALESCE(l."/BIC/ZREGION", '') AS "/BIC/ZREGION",
                  i."RECORD",
                  i."SQL__PROCEDURE__SOURCE__RECORD"
             FROM :inTab AS i
             LEFT OUTER JOIN "/BIC/AZSD_O012" AS l
               ON l."/BIC/ZCUSTOMER" = i."/BIC/ZCUSTOMER";

  errorTab = SELECT '' AS ERROR_TEXT,
                    '' AS SQL__PROCEDURE__SOURCE__RECORD
               FROM DUMMY
              WHERE DUMMY <> 'X';
ENDMETHOD.

Field names are case sensitive in SQLScript and must be in upper case with double quotes; in ABAP they are not. Compare your generated method signature (start_routine, end_routine, field routine method, or the AMDP method) with these bodies and keep the generated one. AMDP routines need the transformation flagged to run in SAP HANA (BW 7.5 on HANA or BW/4HANA). Verify the ADSO active table name in SE11 before use.

Get your routines reviewed for HANA push-down

Send us a transformation with ABAP routines and we will tell you what an AMDP version would need and what it would gain.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Open the transformation in the BW Modeling Tools (or RSA1 on 7.x), create the routine type you need and let BW generate the method. Fill in the fields here: the source field to filter on, the value to drop, the lookup ADSO active table, its key and result fields, and the target field. Copy only the body between METHOD and ENDMETHOD into the generated method, so you keep the framework's parameter typing. In ABAP routines source and result fields are components of SOURCE_PACKAGE and RESULT_PACKAGE named after the InfoObjects (for example COMP_CODE or /BIC/ZCUSTOMER). In AMDP routines the same fields appear as upper case, double-quoted columns of inTab and outTab.

Start routine: runs once per data package before the rules.
End routine: runs once per package after the rules, on target structure.
Field routine: runs once per record for one target field.
Expert routine: replaces all rules; only use it when nothing else works.

ABAP versus AMDP routines

ABAP routines run on the application server: BW reads a package, hands it to your method, then writes the result. AMDP routines are SQLScript procedures that run inside SAP HANA, which keeps the whole transformation pushed down and is usually much faster for large volumes. The price is stricter rules: you can only use AMDP routines in transformations set to run in SAP HANA, every table you read must be listed in USING, outTab must return every target column including RECORD and SQL__PROCEDURE__SOURCE__RECORD, and errorTab must be returned even when empty, which is what the DUMMY select does. Mixing an ABAP routine into a transformation turns off HANA execution for it.

Worked example and the end routine setting

With the defaults, the start routine removes every record whose company code is 9999 before mapping. The end routine collects customers in the package, reads /BIC/ZCUSTOMER and /BIC/ZREGION from the active table of ADSO ZSD_O01 once with FOR ALL ENTRIES, sorts the result and fills /BIC/ZREGION per record with a binary search. One database call per package instead of one per record is the main performance rule for lookups. If the target field has no rule, set the end routine's update behaviour so that all target fields are updated, independent of active rules; otherwise the value filled in the end routine is not written.

How to debug a DTP routine

For ABAP routines, set a session breakpoint in the routine method, then run the DTP in debug mode: on 7.x set the processing mode to serially in the dialog process for debugging, or use simulation from the DTP with breakpoints in the transformation; in BW/4HANA start the DTP simulation with breakpoints from the DTP editor. Limit the data with a DTP filter so you step through one small package. AMDP routines cannot be debugged with the classic ABAP debugger; use the AMDP debugger in ABAP Development Tools with a breakpoint in the SQLScript, and simulate the DTP so the procedure is called in your session.

Frequently Asked Questions

What is a start routine in SAP BW, with an example?

A start routine is a method that runs once per data package before the transformation rules, with the package in SOURCE_PACKAGE. A typical example deletes unwanted records, such as DELETE source_package WHERE comp_code = '9999', so they are never mapped or loaded.

What is an end routine example in SAP BW?

An end routine runs after the rules on RESULT_PACKAGE, in the target structure. The common example is a lookup: read a field from another ADSO for all records in the package with one FOR ALL ENTRIES select, then fill the target field in a loop with a binary search.

How do I write an AMDP transformation routine?

Flag the transformation to run in SAP HANA, create the AMDP routine, and write SQLScript that fills outTab from :inTab and returns an errorTab. List lookup tables in the USING clause and return RECORD and SQL__PROCEDURE__SOURCE__RECORD in outTab.

When should I use an expert routine?

Rarely. An expert routine replaces all rules and gives you full control of the result package, but you lose rule-level transparency and must fill every field yourself. Use it for row multiplication or complex pivots that start, end and field routines cannot express.

If you want business users to ask questions of the data your BW loads in plain English on your own servers, we can add that layer without changing your transformations.

Talk to an Expert Explore More Free Tools