Integration of SAP HANA Application Function Library (AFL)capabilities into CAP Projects
Purpose of the AFL is to do complex calculations and computations at Database Level rather than at application level to save network traffic and bandwidth along with making the executions much faster, so the Application Function Library (AFL) defines functions that can be called from within SAP HANA SQL Script procedures to perform analytic algorithms.SAP HANA’s SQL Script is an extension of SQL. It includes enhanced control-flow capabilities and lets developers define complex application logic inside database procedures.You can dramatically increase performance by executing complex computations in the database instead of at the application sever level. SAP HANA provides capabilities and techniques to move application logic into the database, one of the most important is the use of application functions.More details HANA AFL Functionality
AFL is a group of functions, one of that function is Predictive Analysis Library(PAL). This post explains how can any SAP BTP application which is developed using Cloud Application Programming (CAP) model can integrate with Predictive Analysis Library(PAL) and utilize the Analytical Capabilities of HANA to achieve complex business use case.
SAP Returnable Packaging Management Solution is developed using Cloud Application Programming Model and hosted on SAP Business Technology Platform. More details about the application can be found here. RPM Help
Business Requirement and Use Case
- Packaging Material planning is important just like for finished goods
- Consumption based planning
- Demand based planning
- Planning of Returnable packaging in advance is a very important activity in the organisation as shortage of returnable may lead to ‘no shipment’ or delay in the delivery, thus disrupting the complete production line of the customer.
- Demand from the customers are received in various forms i.e. forecast delivery schedule, JIT delivery schedule, Sales Order, Planned Independent requirements, Just-In-Time (summarised & sequential) calls.
- Plan Returnables
The above needs extensive AI techniques and involves various I/O, and since Time series forecasting functionality is provided by PAL, we can leverage capabilities of HANA AFL to do the complex calculations for the prediction of returnable materials for the future time frame.
Returnable packaging materials that are being uploaded or existing posting documents which are created in the system can be utilised to prepare the training data , passed into HANA AFL functions and time series forecasting is expected out of these functions of returnable packaging materials for a required time frame.
Functions/Algorithms which are used to achieve the above use case is ARIMA and AUTO ARIMA.
- Login to BTP Global account ->Subaccount->Space where the application is deployed.
- Create a User provided service instance grant the permission to execute the HANA SQL procedure which contains PAL functions
3.Create a grant file and save as .hdbgrants extension.
4. Create a synonym as .hdbsynonym exetsion.
5. Include .hdbgrants and .hdbsynonym files in CAP projects db/src folder/
7. Call the procedure in the CAP Java project as below.
- Set the tenant Id dynamically of the requested user in the Persistence context.
- Making the Procedure Call by Standard JDBC Template provided by Spring Framework.
Points to be Noted
- Enable the Script Server for the HANA Cloud Instance you have created
- Hana Cloud Instance should be allocated with minimum 45 GB of memory
- Latest version of HANA cloud instance needs to be used. In case if you are using the older version then update the version
By Now you will be able to call the HANA AFL procedures in your applications which is developed using Cloud application Programming.