SAP Datasphere – How to integrate Open SQL Procedures in a Task Chain
Introduction
Within SAP Datasphere a task chain is a structured sequence of tasks or operations designed to be executed in a specific order or concurrently. It automates the process of running multiple related tasks, ensuring that they are executed in a predefined sequence or parallel, depending on the requirements. Such objects can be Remote Table Replication, View Persistency, Intelligent Lookup, Data Flow, Replication Flow and Transformation Flow runs.
SAP Datasphere now allows also the integration of SQL Procedures directly into Task Chains. To achieve this, you start creating a SQL Procedure within the underlying SAP HANA database. Once the procedure is created, it can be incorporated into your Task Chain. This guide provides a quick overview of the necessary steps and considerations for this process.
Setup
In this scenario, the first step is to create a Database User within the relevant space. If the user does not already exist, navigate to your space and create the Database User.
Deploy this user to the space, after which you can access the Database Explorer. Afterwards you are now able to access the HANA Cloud database with this user.
Creation of Database Objects
The initial step involves creating a table that will store specific values generated by your Procedure.
Next, you’ll create a simple procedure that accepts a number as input, updates the previously created table with the current timestamp, and stores the input number. This procedure serves as a basic example to demonstrate the setup process.
A crucial step follows: next you must grant the space permission to execute the Procedure. Without this permission, the Procedure will not be visible in the Task Chain and cannot be added as a step.
Task Chain Creation
With the setup complete, you can now create a new Task Chain in the Data Builder within your space. Under the “Others” tab, you should see the previously created Procedure.
Dragging the Procedure into the Task Chain as a new step will prompt you to input a value for the variable.
These parameters can also be adjusted later in the properties screen.
Finally, save, deploy, and run the Task Chain.
Additionally, replicate the table created in the HANA database to your space, making the values accessible. You can now view the values added via the Procedure.
Remarks
An important note: if you attempt to add an output to your Procedure, as in the example below, an error may occur during Task Chain deployment.
The error generally points to a missing parameter, but the output cannot be configured. While this isn’t a major issue for us, it’s useful to be aware of.
Summary
This integration option enhances the flexibility of SAP Datasphere, allowing you to model your requirements more effectively. By incorporating SQL Procedures into Task Chains, you can orchestrate integrated processes that start or end in the Data Builder while leveraging the functionalities of HANA Cloud instances.
Links
SAP Documentation Running Open SQL Procedures in a Task Chain
Privileges in Database