SAP Datasphere – Triggering a Stored Procedure using a Data Flow

I was recently involved in a project that was migrating to SAP Datasphere.

One of the solutions had a complex stock allocation routine that required row by row processing that could not be achieved directly in Datasphere but could be done by utilising the SQL Open Schema Space to create a stored procedure with a cursor.

Problem

One of the problems with using this approach is the ability to trigger the stored procedure to run directly from Datasphere.

Solution

I have seen a few post asking if this is possible so I have detailed the workaround I used to achieve this with the execution of a Data Flow.

The solution uses the HANA Job Scheduler in the Open SQL Schema Space to run a stored procedure every minute.

The stored procedure then checks two control tables, one that is pre-populated with an entry for each run that can occur a day in the Open SQL Schema Space and a second table that is populated from the execution of data flow in Datasphere.

If the correct conditions are met then the stored procedure will update the run start time in the Open SQL Schema control table then run any additional steps/stored procedures, once this has completed the Open SQL Schema control table will then be updated with the End Time and the Run value will be set to 1.

Steps

1 – Create Table SP_CONTROL_TABLE_DSP – Main Space

Create the table SP_CONTROL_TABLE_DSP in your space, this will be used as the target of the Data Flow execution.

2 – Create View SP_CONTROL_VIEW_DSP – Main Space

Create the Graphical view SP_CONTROL_VIEW_DSP in your space, this will use SP_CONTROL_TABLE_DSP as the source and will be marked as expose for consumption to allow access to the Open SQL Schema Space.

3 – Create Table SP_CONTROL_TABLE_OSS – Open Schema Space

Create an Open SQL Schema Space and launch the Database Explorer to create the table SP_CONTROL_TABLE_OSS in the Space, this will need to be pre-populated with an entry for number of times the stored procedure can run in a single day and will be updated with the start/end time of the run.

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name

*/
CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."SP_CONTROL_TABLE_OSS"(
	"SP_NAME" NVARCHAR(100) NOT NULL,
	"DATE" DATE NOT NULL,
	"RUN_NUMBER" INTEGER NOT NULL,
	"SP_START_DATE_TIME" LONGDATE,
	"SP_END_DATE_TIME" LONGDATE,
	"RUN" INTEGER NOT NULL
);

4 – Create Table SP_TEST – Open Schema Space

Create a target table called SP_TEST in the Open Schema Space that will be populated by the execution of the stored procedure

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name

*/

CREATE COLUMN TABLE "<SPACE_NAME>#<OSS_NAME>"."SP_TEST"
(TEST VARCHAR(100));

5 – Create Stored Procedure – Open Schema Space

Create a stored procedure that checks both control tables to determine if additional steps should be executed

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure

*/

CREATE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>"
    AS
BEGIN

IF 
	(
	SELECT 1 as RUN_STATUS 
	FROM "<SPACE_NAME>#<OSS_NAME>"."SP_CONTROL_TABLE_OSS" OSS
	INNER JOIN "<SPACE_NAME>"."SP_CONTROL_VIEW_DSP" DSP 
	on OSS."SP_NAME" = DSP."SP_NAME" 
	and OSS."RUN_NUMBER" = DSP."RUN_NUMBER"
	and OSS."DATE" = DSP."DATE"
	WHERE
	OSS."DATE"  = CURRENT_DATE
	AND OSS."SP_NAME" = '<STORED_PROC_NAME>'
	AND OSS."RUN" = 0
	AND DSP."RUN" = 1
	and OSS."SP_START_DATE_TIME" IS NULL
	) = 1 
THEN

	--Updater SP Start Time
	
	MERGE INTO "<SPACE_NAME>#<OSS_NAME>"."SP_CONTROL_TABLE_OSS" OSS 
	USING "<SPACE_NAME>"."SP_CONTROL_VIEW_DSP" DSP 
	ON 	
		OSS."SP_NAME" = DSP."SP_NAME" 
		AND OSS."RUN_NUMBER" = DSP."RUN_NUMBER"
		AND OSS."DATE" = DSP."DATE"
		AND OSS."DATE"  = CURRENT_DATE
		AND OSS."SP_NAME" = '<STORED_PROC_NAME>'
		AND OSS."RUN" = 0
		AND DSP."RUN" = 1
	WHEN MATCHED THEN UPDATE SET OSS."SP_START_DATE_TIME" = CURRENT_TIMESTAMP ;
	
	COMMIT;
	
	-- Call other Stored procedures or steps
	INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."SP_TEST" ("TEST") 
    VALUES (CONCAT('Test Entry - ',(current_utctimestamp)));



	-- --Updater SP End Time
	MERGE INTO "<SPACE_NAME>#<OSS_NAME>"."SP_CONTROL_TABLE_OSS" OSS 
	USING "<SPACE_NAME>"."SP_CONTROL_VIEW_DSP" DSP 
	ON 	
		OSS."SP_NAME" = DSP."SP_NAME" 
		AND OSS."RUN_NUMBER" = DSP."RUN_NUMBER"
		AND OSS."DATE" = DSP."DATE"
		AND OSS."DATE"  = CURRENT_DATE
		AND OSS."SP_NAME" = '<STORED_PROC_NAME>'
		AND OSS."RUN" = 0
		AND DSP."RUN" = 1
	WHEN MATCHED THEN UPDATE SET OSS."RUN" = 1, OSS."SP_END_DATE_TIME" = CURRENT_TIMESTAMP ;

END IF;

END

6 – Schedule Stored Procedure – Open Schema Space

Schedule the stored procedure to run every minute

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure

*/

-- Schedule Stored Procedure to run every minute
CREATE SCHEDULER JOB <STORED_PROC_NAME>_JOB CRON '* * * * * * 00' 
ENABLE PROCEDURE "<SPACE_NAME>#<OSS_NAME>"."<STORED_PROC_NAME>" ;

7 – Populate CONTROL_TABLE_OSS – Open Schema Space

Populate the CONTROL_TABLE_OSS with an entry for each time the stored procedure can run a day.

Create Tally Table Function

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name

*/

--Create Tally Table Function
CREATE FUNCTION "<SPACE_NAME>#<OSS_NAME>".TALLY_TABLE (maxnumber int)
 RETURNS table (rownum int)
AS
BEGIN
 RETURN
  SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_OID) AS ROWNUM
  FROM SYS.OBJECTS
  LIMIT :maxnumber;
END

Load entries adjusting number of instances (default 100) and date range

(Dependent on Time Tables being created in space)

/*
Find and replace the following with your values
<SPACE_NAME> - Space Name
<OSS_NAME> - Open SQL Schema Space Name
<STORED_PROC_NAME> - Name of Stored Procedure

*/

--Load Run schedule into Control table (Space Time Dimension - SAP.TIME.VIEW_DIMENSION_DAY and TALLY_TABLE function)
INSERT INTO "<SPACE_NAME>#<OSS_NAME>"."SP_CONTROL_TABLE_OSS"
("SP_NAME","DATE","RUN_NUMBER","RUN")

SELECT 
'<STORED_PROC_NAME>' as SP_NAME
,"DATE_SQL" as DATE
,b."ROWNUM" as RUN_NUMBER
,0 as RUN
FROM "<SPACE_NAME>"."SAP.TIME.VIEW_DIMENSION_DAY" a 
CROSS JOIN "<SPACE_NAME>#<OSS_NAME>"."TALLY_TABLE"(100) b
WHERE "DATE_SQL" Between CURRENT_DATE and '2026-04-01'

8 – Create Data Flow – Main Space

Create a Data Flow that will populate CONTROL_TABLE_DSP causing the stored procedure to be executed.

SOURCE – Import the table SP_CONTROL_TABLE_OSS into your repository and use as source for data flow

PROJECTION 1 – add a projection and remove the Start & End Time columns and add a filter condition based on the following conditions

SP_NAME = <STORED_PROC_NAME>

AND RUN = 0

AND DATE = CURRENT_DATE()

AGGREGATION – Add an aggregation node that returns the minimum RUN_NUMBER

PROJECTION 2 – Remove the field RUN and add a new calculated field called RUN with the value 1

TARGET – Add SP_CONTROL_TABLE_DSP as the target ensuring all fields are mapped and set the Load Mode to APPEND

9 – Run the Data Flow – Main Space

You’re now ready to run the data flow but be aware that the job is not complete when the the data flow execution has finished, you should use SP_CONTROL_TABLE_OSS to confirm when the stored procedure execution has completed.