Using PMML Model as Input to a AFL Wrapper Generated PAL Function

This blog page discusses how to use a PMML model exported from SAP Predictive Analysis or from some PAL functions via a stored procedure generated by afl_wrapper_generator for a PAL function. We will use multiple linear regression (MLR) as the driver for this topic.

Walk Through

It does appear that some PAL functions can export PMML according to SAP HANA Predictive Analysis Library PAL Reference. Using this reference, we can see that SAP Predictive Analysis is using LRREGRESSION as the PAL function to train MLR against. Then when we export the model as a stored procedure, we get a wrapper stored procedure that calls the FORECASTWITHLR generated stored procedure (ie, maybe called something like _SYS_AFL.I838604_PAS00AMYWGCT0Y_ZE4LISJ2MWMY_MLR_PMML_FORECASTWITHLR). By default, the stored procedure that is generated is set up to deal with a coefficient input table; however, we can see from the aforementioned reference, that there is an alternative option:

From the above picture, we can see that second input table for FORECASTWITHLR can take a PMML model as an input for the coefficient input table. Also, the third input table, would need to be changed to set the MODEL_FORMAT row equal to “1” to tell it to use a PMML model.

These differences mean we need to regenerate the stored procedure to the PAL FORECASTWITHLR function unfortunately. Thus, this means using the stored procedure generated by SAP Predictive Analysis will do us no good if we want to use a PMML model. In other words, we must manually run SQL to get to the point of using afl_wrapper_generator.

The following is the SQL that I have used to test this, which is mostly self contained. Make sure your user has appropriate privileges (ie, see Error: Insufficient privilege). One of the main changes to use the PMML is to change the second input table’s second column to a VARCHAR (5000) column (a CLOB is an option as well, though SAP Predictive Analysis has an NCLOB used by the output table type).

So, running the above script as is, would generate output like the following:

Changing the coefficient to coefficient=“-0.1834675”, yields:

Functions with PMML Export Option → Import Option

GEOREGRESSION → FORECASTWITHGEOR

LNREGRESSION → FORECASTWITHLNR

CREATEDT → PREDICTWITHDT

CREATEDTWITHCHAID → PREDICTWITHDT

EXPREGRESSION → FORECASTWITHEXPR

LOGISTICREGRESSION

LRREGRESSION → FORECASTWITHLR

POLYNOMIALREGRESSION → FORECASTWITHPOLYNOMIALR

APRIORIRULE

LITEAPRIORIRULE

Functions without PMML EXport

KNN

NBCTRAIN

NBCPREDICT

SINGLESMOOTH

DOUBLESMOOTH

TRIPLESMOOTH

Conclusions

In conclusion, we can see from this blog page that it is possible to use a PMML model against a PAL function. There are some issues however. We would have to regenerate the wrapper stored procedure to the PAL function, so that it knows to use a PMML model and not a straight coefficient (Ai). So, this means we probably wouldn’t want to export from SAP Predictive Analysis as a stored procedure if we want to use PMML. We can export as PMML and then we would need to have a script, similar to the one included on this page, to run the afl_wrapper_generator against. We then would need to copy and paste the PMML into the script and make any desired changes to the XML (before or after Copy + Paste). This could be a brittle solution. Of course, exporting a stored procedure from SAP PA is probably not the best option if you are expecting to have multiple end users which may or may not be on the same HANA instances. Using the PMML also self contains the settings needed; however, it may be nice to have a GUI to import, validate and update the database with PMML changes.