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

[asj_bcn]

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

[former_member197234]

Did any one find an answer on how to call Flowgraph procedure in a trigger script in HANA

[lbreddemann]

To be honest, I'm not quite sure what exactly it is you want to do.

If you just generally want to call a HANA procedure from ABAP there's the option for using an ABAP Managed DB Procedure (AMDP) or writing native SQL code (call ...). Both of these options allow for parameters.

Looking at your "trigger" idea seems to mix up problems about data loading/staging/processing with how to integrate the two different processing realms (BW and HANA) properly.

You might also look into how you expose BW data (direct table access is rarely a good idea here).

[asj_bcn]

Dear Lars, thank you a lot for your answer.

My objective is to automate the application AFL Predictive Functions to BW data. I am trying to avoid using HANA Studio or Sap Predictive Analytics to trigger the analysis. In my perfect scenario, I trigger the Predictive Analysis from my BW, where I can process the data and creates the input views and tables, apply the AFL Algorithms, and store the results in BW.

As you can see I am a little lost. I am stack in:

(1) trying to to loop over distinct values to create views (https://answers.sap.com/questions/147838/how-to-loop-over-distinct-values-to-create-filtere.html) since doing it in ABAP takes almost 3 hours.

(2) trying to trigger the analysis from a BW report. We have tried using an ABAP Managed DB Procedure (AMDP) with no success.

(3) closing the loop: process data, apply algorithm and store results.

Everything appear to be easy for a one-time-analysis (trigger the algorithm from Hana Studio - AFM - SQL, Sap Predictive Analytics), but gets more difficult when trying to automate the process out of these sofware platforms. Maybe my approach is not well focused. Any help will be appreciated.

Kind regards,

AlexS

[achab]

Hi Alejandro, are you trying to achieve something specific with SES or you are trying to test different integration options? You could also use Automated Analytics to create your forecasts and then publish them to BW on HANA. On your point 3 did you refer already to the Expert Analytics user guide? https://help.sap.com/viewer/94dbf2ba9d4047618880187451c3b253/3.1/en-US I think you cannot export a chain built with a time series model as per https://help.sap.com/viewer/94dbf2ba9d4047618880187451c3b253/3.1/en-US/d555b053816840e8b6dbfbedd159c...

Kind regards

Antoine

[asj_bcn]

Dear Antoine. Thanks for your answer.

I am trying to test different integration options as I want to know which can be then easily applied to a real business scenario. Thank you for your link to the "Supported Components in Export of Entire Chain", I was not aware of it.

Do you know any other resource that I could check or any blog post that would be useful to make my test work?

Thanks again.

AlexS