How to Use AMDP Scripts in SAP BW/4HANA Transformations

SAP BW/4HANA is a modern data warehouse solution that simplifies and optimizes the way you manage and analyze your data. One of the features of SAP BW/4HANA is the AMDP script, which is a SQLScript code that can be used in transformations to implement complex logic and leverage the power of SAP HANA database.

In this blog post, you will learn what AMDP scripts are, why and when to use them, how to create and activate them, and how to use them in different types of routines.

What are AMDP scripts?

AMDP stands for ABAP Managed Database Procedure, which is a framework that allows you to create and execute database procedures using ABAP methods. SQLScript is a scripting language that allows you to write SQL statements and expressions that can be executed on SAP HANA database.

AMDP scripts are SQLScript codes that are embedded in ABAP methods that are marked with the interface IF_AMDP_MARKER_HDB. These methods are executed on SAP HANA database instead of ABAP runtime environment, which enables code pushdown and performance optimization.

AMDP scripts can be used in SAP BW/4HANA transformations to implement complex logic that cannot be achieved by standard rule types or ABAP routines. For example, you can use AMDP scripts to perform data type conversions, string operations, calculations, aggregations, joins, unions, etc.

Why and when to use AMDP scripts?

AMDP scripts have some advantages over ABAP routines, such as:

They can leverage the power and features of SAP HANA database, such as parallel processing, columnar storage, built-in functions, etc.
They can reduce data transfer between SAP BW/4HANA and SAP HANA database, which improves performance and reduces memory consumption.
They can simplify the code and make it more readable and maintainable.

However, AMDP scripts also have some limitations and challenges, such as:

They require SAP HANA database as the underlying database for SAP BW/4HANA.
They require ABAP development tools (ADT) in Eclipse for development and debugging.
They have different syntax and semantics than ABAP language, which requires learning and adaptation.
They have some restrictions on data types, variables, exceptions, etc.

Therefore, you should use AMDP scripts only when the existing rule types or ABAP routines are not sufficient or efficient for your transformation logic. You should also consider the complexity and maintainability of your code when using AMDP scripts.

How to create and activate AMDP scripts?

You can create and activate AMDP scripts using the SAP BW/4HANA Modeling Tools in Eclipse. The steps are as follows:

In the Project Explorer, right-click on the transformation where you want to use the AMDP script and choose Open.
In the transformation editor, go to the Routines tab and select the type of routine where you want to use the AMDP script. You can choose from Start Routine, End Routine or Expert Routine.
In the routine editor, click on the Create button to create a new AMDP script. You can also copy an existing AMDP script from another transformation or from a template.
In the wizard, enter a name and a description for the AMDP script. You can also specify whether the AMDP script is reusable or not. A reusable AMDP script can be used in multiple transformations or routines. Click Next.
In the next screen, you can define the input parameters and output parameters for the AMDP script. The input parameters are the tables or fields that are passed from the transformation to the AMDP script. The output parameters are the tables or fields that are returned from the AMDP script to the transformation. Click Finish.
The AMDP script is created in the inactive version and is displayed in the editor. You can write your SQLScript code in the method body using the input parameters and output parameters. You can also use local variables or constants as needed.
To activate the AMDP script, right-click on the method name and choose Activate.

How to use AMDP scripts in different types of routines?

AMDP scripts can be used in different types of routines in SAP BW/4HANA transformations, such as:

Start Routine: This is a type of routine that is executed before any rule type is applied on the source data. You can use an AMDP script in a start routine to perform operations on the source data before it is passed to the rule types. For example, you can use an AMDP script to filter, sort, join or aggregate the source data.
End Routine: This is a type of routine that is executed after all rule types are applied on the source data. You can use an AMDP script in an end routine to perform operations on the result data before it is passed to the target. For example, you can use an AMDP script to calculate, enrich or validate the result data.
Expert Routine: This is a type of routine that is executed instead of any rule type on the source data. You can use an AMDP script in an expert routine to implement complex logic that cannot be achieved by standard rule types. For example, you can use an AMDP script to perform transformations, conversions or lookups on the source data.

Here are some examples of AMDP scripts in different types of routines:

Example 1: Start Routine

In this example, an AMDP script is used in a start routine to join the source data with a master data table and filter the records based on a condition.

METHOD start_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES /BIC/AZCUSTOMER.
-- Declare output table
outtab = SELECT s."CALYEAR", s."COUNTRY", s."CUSTOMER", s."MATERIAL", s."QUANTITY", s."AMOUNT", c."CUSTGRP"
FROM :sourcetab as s
INNER JOIN "/BIC/AZCUSTOMER" as c
ON s."CUSTOMER" = c."CUSTOMER"
WHERE s."COUNTRY" = 'US';
ENDMETHOD.

METHOD start_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES /BIC/AZCUSTOMER.

-- Declare output table

outtab = SELECT s."CALYEAR", s."COUNTRY", s."CUSTOMER", s."MATERIAL", s."QUANTITY", s."AMOUNT", c."CUSTGRP"

FROM :sourcetab as s

INNER JOIN "/BIC/AZCUSTOMER" as c

ON s."CUSTOMER" = c."CUSTOMER"

WHERE s."COUNTRY" = 'US';

ENDMETHOD.

Example 2: End Routine

In this example, an AMDP script is used in an end routine to calculate the total price and the average price for each country and material combination.

METHOD end_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES.
-- Declare output table
outtab = SELECT r."CALYEAR", r."COUNTRY", r."MATERIAL",
SUM(r."QUANTITY") as "QUANTITY",
SUM(r."AMOUNT") as "AMOUNT",
SUM(r."AMOUNT") + SUM(r."TAX") as "TOTAL_PRICE",
(SUM(r."AMOUNT") + SUM(r."TAX")) / SUM(r."QUANTITY") as "AVG_PRICE"
FROM :resulttab as r
GROUP BY r."CALYEAR", r."COUNTRY", r."MATERIAL";
ENDMETHOD.

METHOD end_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES.

-- Declare output table

outtab = SELECT r."CALYEAR", r."COUNTRY", r."MATERIAL",

SUM(r."QUANTITY") as "QUANTITY",

SUM(r."AMOUNT") as "AMOUNT",

SUM(r."AMOUNT") + SUM(r."TAX") as "TOTAL_PRICE",

(SUM(r."AMOUNT") + SUM(r."TAX")) / SUM(r."QUANTITY") as "AVG_PRICE"

FROM :resulttab as r

GROUP BY r."CALYEAR", r."COUNTRY", r."MATERIAL";

ENDMETHOD.

Example 3: Expert Routine

In this example, an AMDP script is used in an expert routine to convert the currency and unit of the source data using conversion factors from SAP BW/4HANA.

METHOD expert_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES /BI0/TCURR /BI0/TUNIT.
-- Declare output table
outtab = SELECT s."CALYEAR", s."COUNTRY", s."CUSTOMER", s."MATERIAL",
s."QUANTITY" * u1."/BIC/CONVFACT" as "QUANTITY_LB",
s."AMOUNT" * c1."/BIC/UKURS" as "AMOUNT_USD",
s."TAX" * c1."/BIC/UKURS" as "TAX_USD"
FROM :sourcetab as s
INNER JOIN "/BI0/TCURR" as c1
ON s."CURRENCY" = c1."/BIC/FCURR"
AND 'USD' = c1."/BIC/TCURR"
AND s."CALDAY" BETWEEN c1."/BIC/GDATU" AND c1."/BIC/GDATU_TO"
INNER JOIN "/BI0/TUNIT" as u1
ON s."UNIT" = u1."/BIC/FUNIT"
AND 'LB' = u1."/BIC/TUNIT"
AND s."CALDAY" BETWEEN u1."/BIC/GDATU" AND u1."/BIC/GDATU_TO";
ENDMETHOD.

METHOD expert_routine BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BIC/AZSALES /BI0/TCURR /BI0/TUNIT.

-- Declare output table

outtab = SELECT s."CALYEAR", s."COUNTRY", s."CUSTOMER", s."MATERIAL",

s."QUANTITY" * u1."/BIC/CONVFACT" as "QUANTITY_LB",

s."AMOUNT" * c1."/BIC/UKURS" as "AMOUNT_USD",

s."TAX" * c1."/BIC/UKURS" as "TAX_USD"

FROM :sourcetab as s

INNER JOIN "/BI0/TCURR" as c1

ON s."CURRENCY" = c1."/BIC/FCURR"

AND 'USD' = c1."/BIC/TCURR"

AND s."CALDAY" BETWEEN c1."/BIC/GDATU" AND c1."/BIC/GDATU_TO"

INNER JOIN "/BI0/TUNIT" as u1

ON s."UNIT" = u1."/BIC/FUNIT"

AND 'LB' = u1."/BIC/TUNIT"

AND s."CALDAY" BETWEEN u1."/BIC/GDATU" AND u1."/BIC/GDATU_TO";

ENDMETHOD.

Conclusion

In this blog post, you have learned how to use AMDP scripts in SAP BW/4HANA transformations, what AMDP scripts are, why and when to use them, how to create and activate them, and how to use them in different types of routines. AMDP scripts are SQLScript codes that can be used in transformations to implement complex logic and leverage the power of SAP HANA database.

Disclaimer: This content is generated by AI.