SAP Datasphere - Triggering a Stored Procedure using a Data Flow

[RichT]

Please refer to the updated version 2 of the solution SAP Datasphere – Triggering a Stored Procedure using a Data Flow v2

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,

	PRIMARY KEY ("SP_NAME","DATE","RUN_NUMBER")

);

 

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.

 

 

 

I hope you find this guide useful and happy to hear any feedback or alternative workarounds that have been used.