Execute Stored Procedure from SAP Analytics Cloud with NodeJS App

In this post, we’ll learn how to create a custom widget in SAP Analytics Cloud, Analytic Application to execute stored procedure directly from the UI by clicking a button. After the execution is complete, the message box will pop-up with the status.

Flow Diagram

There are two components that we’ll build: Custom Widget and NodeJS app.

We start by creating the SAC custom widget JavaScript Web Component for main and styling panel and define the widget id, name, web components, properties, methods and event objects in a JSON file.

And then we’ll create the NodeJS app to receive and execute the command from the widget to run the stored procedure and tell the custom widget when is done. The NodeJS app will call the JavaScript HANA database client to establish the connection with SAP HANA database and execute the stored procedure.

The simplified flow diagram can be shown below.

1. SAC Custom Widget

The SAC custom widget comprises of the following files:

◉ stroreproc.json

The JSON file that defines the Custom Widget with id, name, web components, properties, methods and events objects.

◉ storeproc.js

Implements the custom element of the Custom Widget (Web Component).

◉ aps_storeproc.js

Implements the custom element of the Styling Panel of the Custom Widget.

◉ socket.io.js

The Socket.IO JavaScript file.

1.1 Styling Panel

The Styling Panel configuration is implemented in aps_storeproc.js.

It has the following properties:

◉ Socket URL: The URL to NodeJS server

◉ Button Text: Text in Button UI

◉ Command: Command to execute the stored procedure. There are two commands here, cmd_req is to populate the data. And cmd_req_del to delete the data.

◉ Widget Name: Name of the instance of the custom widget.

1.2 Web Component

The Web Component is implemented in storeproc.js.

Let’s take a look a few functions in this file.

1.2.1 loadthis() function

This function will perform the following actions:

◉ We use Socket.IO to establish the connection with NodeJS app. It tries to load the Socket.IO JavaScript library socket.io.js to establish the connection.

◉ If the library can be loaded, it tries to connect to NodeJS app and call UI5() function to draw the UI5 sap.m.Button UI.

◉ It listens to the incoming message from NodeJS app and call UI5() function to update the button UI.

1.2.2 UI5() function

It shows the button UI on the Analytics App canvas.

Once user presses the button, it will send the command to NodeJS via a socket connection. See onPress() function below. Also it shows the message “Stored Procedure is complete with status: XX” once the execution is complete.

2. NodeJS App

We’ll be using JavaScript HANA Database Client https://github.com/SAP/node-hdb to establish the connection to HANA database and run the stored procedure.

The below snapshot shows Socket.IO receive events on “cmd_req” to run the stored procedure SP_1 (populate data) and “cmd_req_del” to run the stored procedure SP_2 (delete data).

var SP_1 = '_REPLACE_WITH_STORED_PROCEDURE_TO_POPULATE_DATA_';

var SP_2 = '_REPLACE_WITH_STORED_PROCEDURE_TO_DELETE_DATA_';

3. Usage

◉ Update the config.json with your HANA database information.

{

  "host": "localhost",

  "port": 30015,

  "user": "USER",

  "password": "PASSWORD"

}​

◉ Run the NodeJS app.

◉ Create Analytics App and add the custom widget: storeProc_1 to populate data and storeProc_2 to delete data.

◉ Create a model and insert a table.

◉ In storeProc_1 Style Panel, add the following properties:

◉ In storeProc_2 Style Panel, add the following properties:

◉ Add Application.refreshData() in onStart() function for each widget. This is to refresh the table once the execution of stored procedure is complete.

◉ Save and click “Run Analytics Application”.

◉ Click “Populate Data” to run the stored procedure.

◉ Once is done and without error, you will see the message box as shown below. The table will be automatically refreshed.

◉ From the NodeJS App, you will see the similar message.

◉ Try to delete the data by clicking the “Delete Data” button.

Congratulations, we’ve built a SAP Analytics Cloud, Analytics App custom widget to execute the stored procedure directly from the UI !. Do let me know if you have any questions.