Skip to Content
0
Mar 08, 2017 at 10:22 AM

Five approaches to execute a Predictive AFL function from BW on HANA

1534 Views Last edit Mar 13, 2017 at 09:59 AM 2 rev

Hello Experts,

First of all, I am very sorry for this long question. Please, feel free to contribute at any step.

I am trying to accomplish the simple exercise of executing a AFL predictive function (i.e. Single Exponential Smoothing) into a BW on HANA. After some research, I managed to find 5 different ways to approach this problem.

But first, let me state the problem: I have a table in BW. I want to access to this table, pre-process it and apply a AFL predictive function (Single Exponential Smoothing). Then, I want to store the results and access them from BW.

To do so, I found 5 possible approaches:

(#1) Using SQLScript from SAP HANA Studio

My first approach is to use a SQLScript (in this example the PAL 74 SES from the HANA Academy). In this case, I need to create a proper input data view to run the AFL function (pre-processing step). Then, I setup the other necessary tables required by the algorithm (parameters, results, stats and signature tables). Finally, I can call the procedure from HANA Studio and see the results.

PAL 74 Time Series - Single Exponential Smoothing Measures.sql
[...]
CALL "_BW_P_SES" ("V_DATA", "#PARAMS", "RESULTS", "STATS") WITH OVERVIEW;

Even if I am pointing into a BW table, my environment to trigger the Procedure is the HANA Studio. Using the CALL above I managed to successfully run the procedure.

Then I tried to create a trigger in HANA Studio to activate my procedure when new information is inserted in different table:

-- Trigger
Create trigger "ASJ"."ASJTEST" 
after insert on ASJ.PEOPLE for each row
begin
    INSERT INTO ASJ.ASJTIMESTAMP VALUES(CURRENT_TIMESTAMP, CURRENT_USER);
end
;
-- Activate the trigger
insert into "ASJ"."PEOPLE" values('AS','TEST',37,'M','CAT','C++')

I trigger code posted above works. However, when I try to substitute the "INSERT INTO ASJ.ASJTIMESTAMP VALUES(CURRENT_TIMESTAMP, CURRENT_USER);" by "CALL _SYS_AFL._BW_PAL_TS (ASJ.V_TS_DATA, ASJ.#TS_PARAMS, ASJ.TS_OPTIMAL_PARAMS, ASJ.TS_RESULTS) WITH OVERVIEW" it does not work. I also tried different ways to call a procedure without success:

(1) CALL "_BW_P_SES" ("V_DATA","#PARAMS","RESULTS","STATS") WITH OVERVIEW;
(2) CALL "_BW_P_SES" ("V_DATA","#PARAMS","RESULTS","STATS") IN DEBUG MODE;
(3) CALL "_BW_P_SES" ("V_DATA","#PARAMS", ?, ?);
  • My Question Is: How should I modify my trigger to be able to launch my AFL procedure?



(#2) Using Application Function Modeller (AFM) from SAP HANA Studio

Another approach is to use the Application Function Modeller (AFM). In this case I created a simple model to call data (I could also add a filter in the middle), call the SES and write the results. The model is as follows:

This model creates a function with no parameters.

CALL "ASJ"."AFM.PAL::LOVELY_CAT"();

When I call this procedure everything runs straightforward. Since this procedure does not have any parameter (not as the previous SQLScript procedure), I though that it may work within a HANA trigger. However, when I try use this call in the above trigger, it failed to run again.


  • My Question Is: How should I modify my trigger to be able to launch my AFM procedure?

(#3) Using SAP Predictive Analytics (SAP PAA)

My third approach is using the SAP Predictive Analytics Software. In this case I created a simple model to read data from HANA, apply the SES and return the results to HANA:

When I run the model from SAP PAA it works well and writes the results in HANA. It also generate many new tables, views and Procedures:

Then I exported the chain to my HANA. But when I try to run the procedure from my HANA Studio, I failed.

CREATE PROCEDURE "ASJ"."SES_BW_PAA"(OUT result "SES_BW_PAA_OUT_TYPE")...

-- Then I can call the Procedure
CALL "ASJ"."SES_BW_PAA"();


  • My Question Is: How should I do to call my SAP PAA procedure from my HANA Studio?

(#4) Use BW report (ABAP) to trigger a SQL PROCEDURE

My next approach is to avoid triggering the procedures from HANA Studio. Instead, with a college help, we created an ABAP code that calls my SQLScripts. With the ABAP we can also pre-process the data and create all necessary tables required to run the SQLScripts.

On the one hand we failed to run the SQLScript approach #1. We tried different variations in the CALL sentence with any success.

(1) CALL "_BW_P_SES" ("V_DATA","#PARAMS","RESULTS","STATS") WITH OVERVIEW;
(2) CALL "_BW_P_SES" ("V_DATA","#PARAMS","RESULTS","STATS") IN DEBUG MODE;
(3) CALL "_BW_P_SES" ("V_DATA","#PARAMS", ?, ?);

On the other hand, we succeed when using the same ABAP call but using the CALL of the AFM approach #2.

CALL "ASJ"."AFM.PAL::LOVELY_CAT"();

The problem might be related to the CALL parameters or to the use of "With Overview / In Debug Mode".


  • My Question Is: How can I call procedures with parameters in ABAP?

(#5) Using Predictive Algorithms native in BW

My final approach is using the native functions in BW. In this case all the setup is done in the BW environment with no necessity to go to HANA Studio. However, the options are very limited to a few algorithm options.

This case works well.

  • My Question Is: Is it possible to include all the AFL Predictive Function to this menu?

Well, as you can see there are many options to face this simple example. However, it is difficult to success in all of them. I tried to solve all of the above issues but at some point I got stack.

It would be awesome if any of you could contribute and provide ideas to make my example work, as well as if you contribute with a new approach.

Thank you all for your help and time.

AlexS