Trigger HANA stored procedure from SAP Analytics Cloud - Multi Action

[sukanya_krishnan]

What-if simulations or algorithms can be implemented as part of SAC Planning scenarios. These algorithms can either be implemented in SAC or in backend HANA system based on the data availability and complexity. With the introduction of new feature "bidirectional exchange of data between Datasphere & SAC", Datasphere can serve as source for SAC planning use cases also and there by some of the what-if simulations can also be deployed in Datasphere.

Datasphere data flows can be used to implement such algorithms and scheduled based on the planning cycle time frame. Stored Procedures can also be leveraged for simulation algorithms. In the blog "https://blogs.sap.com/2023/04/05/access-sap-hana-cloud-underneath-of-sap-datasphere/" , we discussed on options to leverage the HANA Database in which Datasphere is hosted. Stored procedure can be written in HANA of Datasphere and scheduled using Xsjob scheduler. But if we want the business users to trigger such simulations on demand from SAC, we have option to get this triggered through SAC Multi Actions using API step.

Let's now look into steps at high level to implement this

• Create stored procedure in HANA ( HANA underneath Datasphere)


• Create a 1:1 calculation view to view the output of stored procedure. We can leverage the table as such but only the calculation views in HDI container are visible in Datasphere data builder


• Create API service for the procedure


• Create HTTPS API connection in SAC


• Create SAC Multi Action with API step and define/configure the procedure call and parameters


• Trigger the multi action from planning input templates or analytical applications

Create Stored Procedure in HANA

Create a project in BAS with native hana artifacts. Test procedure is created as below

PROCEDURE INVOKEPROC_WITH_INPUT(IN msg_txt nvarchar(200))

  language sqlscript AS 

BEGIN   

DECLARE v_hist INT := 0;

SELECT CASE WHEN MAX("SIMULATION_SEQ") IS NULL THEN 0 ELSE MAX("SIMULATION_SEQ") END INTO v_hist FROM "PROC_TRIG"; 

v_hist = v_hist + 1;

INSERT INTO "PROC_TRIG"("EXECUTION_TIME","SIMULATION_TXT","SIMULATION_SEQ") VALUES 

(current_utctimestamp, :msg_txt, v_hist);

END;

Test executing this procedure from BAS

Create a 1:1 view on top of table loaded using procedure

Add CAP service to this project in BAS and expose procedure as API

• Add CAP model to the Project


• Create service definition

          service CatalogService {   

               function fun_invokeproc_with_input(msg_txt: String) returns Boolean;  

              action act_invokeproc_with_input(msg_txt: String) returns Boolean;

           }

• Create JavaScript to  invoke the procedure on using the service

           const { msg_txt } = req.data;

           let dbQuery = `Call "CALL_WITH_INPUT"("MSG_TXT" => '${msg_txt}' )`;

           let result = await cds.run(dbQuery, {})

           console.log(result)

           return true

• Bind CAP with HDI


• Establish authentication and validate from BAS

Create HTTPS API connection in SAC

• Get the data service url from the service running in BAS

From the Instance and Subscription section, select the auth service and get the credentials for the service. Get the client id, secret and url from the credentials. Append /oauth/token to the url and include the same in token url

• Create HTTP API connection and also add data service url to trusted origin in SAC

Create Multi Action in SAC

• Create Multi Action and in the API URL - mention the data service URL and append it with /catalog/<<proc call service>> like /catalog/act_invokeproc_with_input and key in the input parameter in the body section

Create a story to trigger multi action

• Create a SAC story and add the multi action.


• Add the HDI container where project is deployed within Datasphere and create view in datasphere out of the cv_procview. Here I have created a test artifact. Ideally any required calculations can be performed in views based on the requirement


• Configure connection to Datasphere in SAC and create a table widget leveraging the datasphere view. Now we can see the output of stored procedure execution in SAC

Conclusion

As part of planning, if we want to present outcomes of system suggested simulations based on user parameters, same can be designed and implemented in many ways based on the requirement. These can either be designed as Value Driver Trees in SAC Or Data Actions can be written to calculate the simulation outcomes based on user input. Another method is to design algorithms in stored procedures and the same can be triggered from SAC. With BTP including Datasphere and SAC as services in it, we can access artifacts of HANA cloud underneath Datasphere in Datasphere data builder. Procedures in HANA cloud can be either scheduled and output can be accessed in Datasphere and thereby in SAC OR procedures can be directly triggered in SAC by business users on demand.