Technical Articles

Jerome Zhao

February 18, 2020 6 minute read

An example to call PAL Apriori via AMDP

Introduction

SAP Predictive Analysis Library (PAL) is delivered with SAP HANA. This application function library (AFL) defines functions that can be called from within SAP HANA SQLScript procedures to perform analytic algorithms.

ABAP-based SAP applications are able call the functions (Classification, Regression, Clustering, Association Rule, Recommender Systems, etc.) provided by PAL. One common way is to do it via AMDP.

ABAP-Managed Database Procedures (AMDP) is one of the recommended patterns for use in ABAP code optimization within the context of ABAP development on SAP HANA.

Step-by-Step Example

Let’s use an example to show how that is done. The PAL function used is Apriori.

Step 1 (Optional). Familiarize yourself with PAL function using SQLScript

If you are already familiar with PAL’s HANA procedure interface and how to call it, you may skip this step.

Connect to the HANA Database via HANA Studio. Run the following script:

SET SCHEMA ZHAOJE;

DROP TABLE PAL_APRIORI_PARAMETER_TBL;
CREATE COLUMN TABLE PAL_APRIORI_PARAMETER_TBL (
“PARAM_NAME ” VARCHAR(100),
“INT_VALUE” INTEGER,
“DOUBLE_VALUE” DOUBLE,
“STRING_VALUE” VARCHAR (100)
);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_SUPPORT’, null, 0.1, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_CONFIDENCE’, null, 0.3, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MIN_LIFT’, null, 1.1, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘MAX_CONSEQUENT’, 1, null, null);
INSERT INTO PAL_APRIORI_PARAMETER_TBL VALUES (‘PMML_EXPORT’, 1, null, null);

DROP TABLE PAL_APRIORI_TRANS_TBL;
CREATE COLUMN TABLE PAL_APRIORI_TRANS_TBL (
“CUSTOMER” INTEGER,
“ITEM” VARCHAR(20)
);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (2, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (2, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (3, ‘item4’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (4, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (4, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (5, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (5, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (6, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (6, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (0, ‘item5’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (1, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (1, ‘item4’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item3’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (7, ‘item5’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item1’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item2’);
INSERT INTO PAL_APRIORI_TRANS_TBL VALUES (8, ‘item3’);

CALL _SYS_AFL.PAL_APRIORI(PAL_APRIORI_TRANS_TBL, PAL_APRIORI_PARAMETER_TBL, ?, ?);

You’ll see the result of mined rules as below:

Step 2. Write AMDP Code to Call PAL procedure

Below is an example class of AMDP. It calls the PAL Apriori procedure. You could edit your own AMDP code in ADT (ABAP Development Tools)

CLASS zcl_amdp_pal DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.TYPES: BEGIN OF ty_apdata,
customer TYPE i,
item TYPE C LENGTH 10,
END OF ty_apdata,
tt_apdata TYPE STANDARD TABLE OF ty_apdata,

BEGIN OF ty_apparams,
name TYPE c LENGTH 60,
intargs TYPE i,
doubleargs TYPE float,
stringargs TYPE c LENGTH 100,
END OF ty_apparams,
tt_apparams TYPE STANDARD TABLE OF ty_apparams,

ty_metric TYPE p LENGTH 5 DECIMALS 4,
BEGIN OF ty_aprules,
antecedent TYPE c LENGTH 20,
consequent TYPE c LENGTH 10,
support TYPE ty_metric,
confidence TYPE ty_metric,
lift TYPE ty_metric,
END OF ty_aprules,
tt_aprules TYPE STANDARD TABLE OF ty_aprules,

BEGIN OF ty_appmml,
row_index TYPE i,
model_content TYPE c LENGTH 500,
END OF ty_appmml,
tt_appmml TYPE STANDARD TABLE OF ty_appmml.

METHODS apriori_proc_call
IMPORTING
VALUE(it_vapdata) TYPE tt_apdata
VALUE(it_apparams) TYPE tt_apparams
EXPORTING
VALUE(et_ap_rules) TYPE tt_aprules
VALUE(et_ap_pmml) TYPE tt_appmml.

PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_amdp_pal IMPLEMENTATION.

METHOD apriori_proc_call BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
CALL _SYS_AFL.PAL_APRIORI(:it_vapdata, :it_apparams, et_ap_rules, et_ap_pmml);
ENDMETHOD.

ENDCLASS.

Step 3

Write an ABAP program to call the AMDP method.

*&———————————————————————*
*& Report ZZ_APRIORI_TEST
*&———————————————————————*
*&
*&———————————————————————*
REPORT ZZ_APRIORI_TEST.DATA: lt_data TYPE zcl_amdp_pal=>tt_apdata,
ls_data LIKE LINE OF lt_data,
lt_param TYPE zcl_amdp_pal=>tt_apparams,
ls_param LIKE LINE OF lt_param,
lt_rules TYPE zcl_amdp_pal=>tt_aprules,
lt_pmml TYPE zcl_amdp_pal=>tt_appmml,
lr_wrapper TYPE REF TO zcl_amdp_pal.

ls_data-customer = 2. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 2. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 3. ls_data-item = ‘item4’. APPEND ls_data TO lt_data.
ls_data-customer = 4. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 4. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.
ls_data-customer = 5. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 5. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.
ls_data-customer = 6. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 6. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 0. ls_data-item = ‘item5’. APPEND ls_data TO lt_data.
ls_data-customer = 1. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 1. ls_data-item = ‘item4’. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.
ls_data-customer = 7. ls_data-item = ‘item5’. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = ‘item1’. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = ‘item2’. APPEND ls_data TO lt_data.
ls_data-customer = 8. ls_data-item = ‘item3’. APPEND ls_data TO lt_data.

CLEAR ls_param. ls_param-name = ‘THREAD_NUMBER’. ls_param-intargs = 2. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = ‘MIN_SUPPORT’. ls_param-doubleargs = ‘0.1’. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = ‘MIN_CONFIDENCE’. ls_param-doubleargs = ‘0.3’. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = ‘MIN_LIFT’. ls_param-doubleargs = ‘1.1’. APPEND ls_param TO lt_param.
CLEAR ls_param. ls_param-name = ‘MAX_CONSEQUENT’. ls_param-intargs = 1. APPEND ls_param TO lt_param.

CREATE OBJECT lr_wrapper.
CALL METHOD lr_wrapper->apriori_proc_call
EXPORTING
it_vapdata = lt_data
it_apparams = lt_param
IMPORTING
et_ap_rules = lt_rules
et_ap_pmml = lt_pmml
.

TRY.
cl_salv_table=>factory(
IMPORTING
r_salv_table = DATA(lr_table)
CHANGING
t_table = lt_rules ).
DATA(lr_functions) = lr_table->get_functions( ).
lr_functions->set_default( abap_true ).
DATA(lr_columns) = lr_table->get_columns( ).
DATA(lr_column_1) = lr_columns->get_column(‘ANTECEDENT’).
lr_column_1->set_long_text(‘ANTECEDENT’). lr_column_1->set_medium_text(‘ANTECEDENT’). lr_column_1->set_short_text(‘ANTECEDENT’).
DATA(lr_column_2) = lr_columns->get_column(‘CONSEQUENT’).
lr_column_2->set_long_text(‘CONSEQUENT’). lr_column_2->set_medium_text(‘CONSEQUENT’). lr_column_2->set_short_text(‘CONSEQUENT’).
DATA(lr_column_3) = lr_columns->get_column(‘SUPPORT’).
lr_column_3->set_long_text(‘SUPPORT’ ). lr_column_3->set_medium_text(‘SUPPORT’). lr_column_3->set_short_text(‘SUPPORT’).
DATA(lr_column_4) = lr_columns->get_column(‘CONFIDENCE’).
lr_column_4->set_long_text(‘CONFIDENCE’). lr_column_4->set_medium_text(‘CONFIDENCE’). lr_column_4->set_short_text(‘CONFIDENCE’).
DATA(lr_column_5) = lr_columns->get_column(‘LIFT’).
lr_column_5->set_long_text(‘LIFT’). lr_column_5->set_medium_text(‘LIFT’). lr_column_5->set_short_text(‘LIFT’).
lr_table->display( ).
CATCH cx_salv_msg. “#EC NO_HANDLER
CATCH cx_salv_not_found. “#EC NO_HANDLER
ENDTRY.

After successful execution, you’ll see the following result in ABAP.

The result in ABAP can be used by the corresponding applications.

1 Comment

You must be Logged on to comment or reply to a post.

Shivam Shukla

February 18, 2020 at 10:12 am

This is really great , Thanks for sharing this stuff , we are touching ML from any of the end 🙂