Reusing Business Logic in AMDP Transformation Routines
Veröffentlicht am 18. November 2020 von | ABAP | AMDP | BW/4HANA | SQLScript |
Last week I read Lars Breddemann's great article about separating business logic from the underlying database tables without using dynamic SQLScript. This is a problem that also arises again and again in SAP BW/4HANA: the reuse of logic in AMDP transformation routines.
Lars's example is very small, and I want to expand it a bit in this article based on a scenario I've experienced in a project in the past. In addition, I would like to show a more elegant way of calling such outsourced function, which facilitates the application. Here's the problem:
The problem - always the same logic on different tables
A customer has branches in several countries. The data models are strictly separated by country, i.e. they are repeated for each country. The name of the ADSO consists of the ISO Code of the country at the 4. and 5. position:
Examples from the Profit Center Accounting:
PCADE01- Germany, balancesPCADE02- Germany, Statistical key figuresPCAFR01- France, balancesPCAFR02- France, Statistical figuresPCAUK01- Great Britain, balances,PCAUK02- United Kingdom, Statistical figures- ...
For the master data, all InfoObjects are compounded to the sourcesystem characteristic The characteristics clearly correspond to a country.
DE100- Master data GermanyFR100- Master data France- ...
There are, of course, other approaches to deal with data of several countries. But I do not want to discuss in this article whether the modeling chosen here is so optimal. The aim of the project was to provide data models for more than 20 countries in future.
As a developer, I am now particularly interested in the requirement that the business logic should be exactly the same in the different transformation routines. Of course, a lookup for a data model in France should only go to the French tables and the same lookup in Germany to the German tables. And the master data should also be restricted to the source system France or Germany for optimal access. In ABAP, this is a trifle, because in a SELECT statement you can specify only the table name dynamically.
The solution approach
In Lars' example, SQLScript solves this as this:
DO BEGIN
my _customers = SELECT customer _name, location, tx _date
from customers_a
where customer_name = 'ACME'
and location = 'NEW YORK';
SELECT
customer_name, location, tx_date
FROM
earliest_customer_transaction (:my_customers);
END;So the business logic is encapsulated in a EARLIEST_CUSTOMER_TRANSACTION function, and the data is passed through a table variable. The caller selects from the appropriate DB tables. This works very well, but it can be simplified. And of course, as announced, I'll build a more complex example of how to reuse business logic in AMDP transformation routines.
IN-Parameter
First, let's look at the input parameters of the procedure 1. The table parameters defined a structure: Which fields are needed with which data types. This is useful so that the program code can be checked statically within the procedure.
Untyped table parameters - a dead end
But it is not mandatory. Since HANA 2.0 SPS04, you can also do without typing table parameters:
CREATE OR REPLACE PROCEDURE do_something( IN inTab TABLE(...),
OUT outTab TABLE(...))
...However, these untyped table parameters only make sense if you want to access the data dynamically. That is, if the column names are not known until run time. However, we did not want to consider dynamic coding in this article, as written in the introduction.
Partially typed table parameters
It is often overlooked that as a caller to a procedure, you do not have to precisely hit the structure of the table parameters. The order of the columns doesn't matter here 2. Similarly, additional columns are irrelevant,they are ignored by the system and later removed by the Optimizer.
Another interesting aspect is the fact that you can not only pass table variables into a table parameter. The names of database tables, views, or table functions are also a valid table expression and can be used in SQLScript. By specifying the table name directly, we can optimize the above example as such:
DO BEGIN
SELECT
customer_name, location, tx_date
FROM
earliest_customer_transaction (customers_a)
WHERE customer_name = 'ACME'
AND location = 'NEW YORK';
END;Unfortunately, there is a limitation in AMDP, so you always have to use a table variable.
Typing as a kind of interface
In Lars's article, I like perspective at the data as instances of table definitions. He writes:
"Tables/views are the data types that developers work with, while records (rows) are the instances of the data types. "
If we stay in this image, IN table parameters only describe something that corresponds to the concept of an interface in object-oriented programming languages: it determines what properties must have the passed data at least. There may also be additional columns.
OUT parameters
The OUT table parameters of a procedure, but not the RETURNS value of a table function, can also be untyped. However, this can only be used if this procedure is called directly from the application layer via the SQL interface.
Attempting to call such a procedure in an anonymous block or other procedure results in this error message:
Error: (dberror) [7]: feature not supported: nested call on procedure "MULTI_TAB".
"EARLIEST_CUSTOMER_TRANSACTION_PROC" has any table output parameter RESULTThis means that the return tables must be fully typed. It is not possible to return more columns than defined. This means a lot of work in an AMDP transformation routine with 200 InfoObjects. Because at the end of such a procedure, a projection must be made to the expected output format.
Reusing Logic in AMDP Transformation Routines
If we now transfer these findings to the reuse of logic in AMDP transformation routines with the above problem, then a solution might look like this:
The Common AMDP Procedure
The same business logic should be followed for all countries. This is defined in one AMDP procedure. The data is passed to this procedure from the outside as a table parameter. Only the columns actually used are defined per table parameter.
All data or just the dynamic ones?
If all DB tables are passed to the procedure, this has a huge advantage: no database accesses must take place within the procedure. All data comes from the outside. And with that we have something called dependency injection in the object-oriented world: the dependencies are passed on from the outside. This allows us to easily test our business logic from ABAP with unit testing. When you ask ABAP developers about a unit test for ABAP transformation routines, you are often laughed at! (Has already happened to me. ;-) ) This is now quite simply possible.
So we should also pass standard tables and master data tables if they are needed.
CLASS zcl_tr_demo DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
TYPES: BEGIN OF gty_s_totals,
sysid TYPE c LENGTH 6,
ryear TYPE n LENGTH 4,
rbukrs TYPE c LENGTH 4,
prctr TYPE c LENGTH 10,
balance TYPE p LENGTH 000009 DECIMALS 000002,
currunit TYPE c LENGTH 5,
END OF gty_s_totals.
TYPES gty_t_totals TYPE STANDARD TABLE OF gty_s_totals WITH DEFAULT KEY.
TYPES: BEGIN OF gty_s_stat_kyf,
sysid TYPE c LENGTH 6,
ryear TYPE n LENGTH 4,
rbukrs TYPE c LENGTH 4,
prctr TYPE c LENGTH 10,
balance TYPE p LENGTH 000009 DECIMALS 000002,
currunit TYPE c LENGTH 5,
END OF gty_s_stat_kyf.
TYPES gty_t_stat_kyf TYPE STANDARD TABLE OF gty_s_stat_kyf WITH DEFAULT KEY.
TYPES: BEGIN OF gty_s_pplant,
sysid TYPE c LENGTH 6,
plant TYPE c LENGTH 4,
bukrs TYPE c LENGTH 4,
END OF gty_s_pplant.
TYPES gty_t_pplant TYPE STANDARD TABLE OF gty_s_pplant WITH DEFAULT KEY.
TYPES: BEGIN OF gty_s_in_out_tab,
recordmode TYPE rodmupdmod, " InfoObject: 0RECORDMODE
logsys TYPE rsdlogsys, " InfoObject: 0LOGSYS
sysid TYPE c LENGTH 6,
" ...
fiscper TYPE n LENGTH 7,
fiscvar TYPE c LENGTH 2,
" ...
balance TYPE p LENGTH 000009 DECIMALS 000002,
record TYPE c LENGTH 56,
sql__procedure__source__record TYPE c LENGTH 56,
END OF gty_s_in_out_tab.
TYPES gty_t_in_out_tab TYPE STANDARD TABLE OF gty_s_in_out_tab WITH DEFAULT KEY.
METHODS: do_transformation_pca IMPORTING VALUE(it_intab) TYPE gty_t_in_out_tab
VALUE(it_totals) TYPE gty_t_totals
VALUE(it_stat_kyf) TYPE gty_t_stat_kyf
VALUE(it_pplant) TYPE gty_t_pplant
VALUE(iv_sysid) TYPE char6
EXPORTING VALUE(et_result) TYPE gty_t_in_out_tab.
ENDCLASS.As you can see from the example above: The typing of the whole tables can quickly become complex. The return tables must either fully match the structure of the transformation routines. Or it is limited to the fields determined in the routine. But then it has to be mixed with the other data from the INTAB by join. I will show this below.
However, calling the new procedure in the AMDP transformation routine is easy, even if we are not allowed to pass the table names directly to the procedure here:
CLASS /BIC/SKCP38MSW2ZH49HEXE5D_M IMPLEMENTATION.
METHOD GLOBAL_END BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
using ZCL_TR_DEMO=>DO_TRANSFORMATION_PCA
/BIC/APCADE0022
/BIC/APCADE0012
/BIC/PPLANT .
-- *** Begin of routine - insert your code only below this line ***
-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.
lt_stat_kyf = select * from "/BIC/APCADE0022";
lt_totals = select * from "/BIC/APCADE0012";
lt_plant = select * from "/BIC/PPLANT";
"ZCL_TR_DEMO=>DO_TRANSFORMATION_PCA"( it_intab => :intab,
it_totals => :lt_totals,
it_stat_kyf => :lt_stat_kyf ,
IT_PPLANT => :lt_plant,
ET_RESULT => OUTTAB );
-- *** End of routine - insert your code only before this line ***
ENDMETHOD.
ENDCLASS.It is important that all DB tables must be specified in the USING clause.
Same Same, but different - Flexibility and change robustness
Flexible input
With regard to the database tables to be read, we are already very flexible with the above approach. The tables can vary from country to country as long as the columns defined in the table parameters exist. Since we pass the data with SELECT * from the source tables, the type of table parameters can also change later, without the need to adjust the transformation routines.
Decoupling the output
In the example above, the procedure returned an output table ET_RESULT with the exact structure of the transformation OUTTAB routines. This saves some paperwork on the one hand, but it is not very flexible. What happens if the country ADSOs differ marginally? For example, a few additional fields that have nothing to do with the implemented business logic. Or if you want to add a field for all data models. Then this must be done simultaneously for all countries. The above approach is therefore less flexible.
However, if we limit the fields of the output of the procedure to the relevant fields for the procedure, then we can mix their result with the original INTAB by means of the INNER JOIN original. For the join condition, the field RECORD that has a unique value for each record in the INTAB. The INNER JOIN can also filter out records in the procedure.
The result could look like this:
-- *** Begin of routine - insert your code only below this line ***
-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.
lt_stat_kyf = select * from "/BIC/APCADE0022";
lt_totals = select * from "/BIC/APCADE0012";
lt_plant = select * from "/BIC/PPLANT";
"ZCL_TR_DEMO=>DO_TRANSFORMATION_PCA"( it_intab => :intab,
it_totals => :lt_totals,
it_stat_kyf => :lt_stat_kyf ,
IT_PPLANT => :lt_plant,
ET_RESULT => lt_result );
outtab = select it.RECORDMODE,
it.LOGSYS,
it.RYEAR,
" ...
it.CURTYPE,
res.FISCPER,
res.FISCVAR,
it.CHARTACCTS,
" ...
it.CURRUNIT,
res.BALANCE,
it.RECORD,
it.SQL__PROCEDURE__SOURCE__RECORD
from :intab as it
inner join :lt_result as res
on it.record = res.record ;
-- *** End of routine - insert your code only before this line ***The Unit Test
And now a small example of testing the procedure with unit tests. Of course, all parameters must be supplied with appropriate data. This can be done very elegantly with the constructor expression VALUE # The three points in the example only need to be replaced by appropriate data:
*"* use this source file for your ABAP unit test classes
CLASS ltcl_ DEFINITION FINAL FOR TESTING
DURATION SHORT
RISK LEVEL HARMLESS.
PRIVATE SECTION.
DATA mo_cut TYPE REF TO zcl_tr_demo.
METHODS:
setup,
first_test FOR TESTING RAISING cx_static_check.
ENDCLASS.
CLASS ltcl_ IMPLEMENTATION.
METHOD first_test.
mo_cut->do_transformation_pca(
EXPORTING
it_intab = VALUE #( ( ... ) )
it_totals = VALUE #( ( ... ) )
it_stat_kyf = VALUE #( ( ... ) )
it_pplant = VALUE #( ( ... ) )
IMPORTING
et_result = DATA(lt_result)
).
cl_abap_unit_assert=>assert_equals( act = lt_result
exp = VALUE zcl_tr_demo=>gty_t_in_out_tab( ( ... ) ) ).
ENDMETHOD.
METHOD setup.
CREATE OBJECT mo_cut.
ENDMETHOD.
ENDCLASS.Conclusion
Reusing business logic in AMDP transformation routines is also possible in SQLScript. However, you have to know, what you are doing. And you have to write a lot of code. Therefore, it will probably only be worthwhile when more than two data flows require exactly the same logic. The overhead for a flexible solution is larger than in ABAP, because more needs to be typed and mapped. A positive side effect: you get the opportunity to perform unit tests that are decoupled from the database status.
In large BW/4HANA projects with multiple identical data flows, there is no way around outsourcing business logic. If you violate the DRY principle ((Don't Repeat Yourself)) too often, you accept technical debts. These are then due for payment during operation and maintenance of the system.
We are happy to support you in the planning phase of your BW/4HANA in the field of modelling. With training, workshops, advice or through the placement of specialized consultants. Please contact us.
