Benefits of Executing Stored Procedures via API in SAP SuccessFactors Incentive Management

[Yogananda]

This blog provides some tips and points to be considered while executing stored procedures via API, specifically for SAP SuccessFactors Incentive Management customers/partners/Comp developers working on the project:

Benefits of Executing Stored Procedures via API

1. Performance Optimization:

   • Reduced Network Traffic: Stored procedures can minimize the amount of data sent over the network by processing data on the server side.
   • Execution Plan Reuse: Stored procedures can reuse execution plans, which can lead to faster query execution times.

2. Security:

   • Parameterization: Using stored procedures helps prevent SQL injection attacks by parameterizing inputs.
   • Enhanced Data Security: Using stored procedures helps secure sensitive compensation data by limiting direct access to the database
   • Access Control: You can grant permissions to execute stored procedures without giving direct access to the underlying tables(Only EXT Schema) .

3. Maintainability:

   • Centralized Logic: Centralizing business logic in stored procedures makes it easier to manage and update incentive compensation rules and calculations
   • Version Control: Stored procedures can be versioned and managed more easily compared to inline SQL queries.
   • Simplified Updates: Updates to compensation plans can be made in one place, reducing the risk of inconsistencies

Performance Considerations

1. Execution Time:

   • Precompiled Execution Plans: Stored procedures are precompiled, which can reduce execution time compared to ad-hoc queries.
   • Batch Processing: They can handle batch processing efficiently, reducing the number of round trips between the application and the database.

2. Resource Utilization:

   • Efficient Resource Use: Stored procedures can be optimized to use server resources more efficiently.
   • Scalability: They can help in scaling applications by offloading complex processing to the database server.

Useful Tasks for End Users

1. Data Retrieval:

   • Complex Queries: Stored procedures can handle complex queries and return results quickly.
   • Data Aggregation: They can perform data aggregation and summarization tasks efficiently.
   • Real-Time Insights: They provide real-time payment and performance insights, helping sales users make informed decisions quickly

2. Data Manipulation:

   • Insert/Update/Delete Operations to EXT Schema: Stored procedures can handle data manipulation tasks securely and efficiently.
   • Automated Calculations: Stored procedures can automate complex compensation calculations, ensuring accuracy and consistency
   • Transaction Management: They can manage transactions, ensuring data integrity and consistency during compensation processing.

3. Automation:

   • Scheduled Tasks: Stored procedures can be scheduled to run at specific times, automating routine compensation tasks.
   • Event-Driven Actions: They can be used in conjunction with triggers to automate responses to specific events such as achieving sales targets.

Note :

1. This process is only available on K8 Stack (Hyperscaler) and not available on legacy stack.
2. Stored Procedure execution is only for EXT Schema
3. Stored Procedure should get completed within 5 seconds and if exceeded it will be timed-out
4. Grants to be kept on mind and no parallel execution

Do the following steps to validate the Stored Procedure and how to execute via APIs

CREATE TABLE store_log_details (
    startdate DATETIME,
    enddate DATETIME,
    date DATE
);

----------------------------------------------------------

CREATE PROCEDURE EXT.log_details(IN P_TIMESTAMP TIMESTAMP, IN P_DATETIME DATETIME,IN P_DATE DATE)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER

AS
BEGIN
    INSERT INTO store_log_details (startdate, enddate, date)
    VALUES (:P_TIMESTAMP, :P_DATETIME, :P_DATE);
END;

Execute the Procedure manually to check the logs time is inserted into table. 

CALL EXT.log_details("2024-06-07 23:02:20","2023-01-01 12:23:33", "2024-05-07")

Screenshot of Table Creation, Stored Procedure and CALL Statement

Thumps up(Hit Like or Kudos) on this blog and kindly share the blog to all your friends who can get benefited

### Below snippets copy and paste it in your Visual Studio Code ####

### Request to trigger a stored procedure
POST {{host}}/cng-pipeline/automation-pilot/procedure
Content-Type: application/json
Authorization: Bearer {{accessToken}}

{
  "procedureName" : "log_details", "parameters" : ["2024-06-07 23:02:20","2023-01-01 12:23:33", "2024-05-07"]
}

### Get the Job id from above call to get the status of the stored procedure
GET {{host}}/cng-pipeline/automation-pilot/procedure/13
Content-Type: application/json
Authorization: Bearer {{accessToken}}

Follow Me on LinkedIn