Fetch Delta Records from Multiple Entities in SuccessFactors Using OData API and Groovy Script

[rasesh_thakkar]

Hello All,

Greetings for the Day!!

Fetching delta records in Success Factors can be straightforward when dealing with a single entity using filters and the lastModifiedDateTime field. However, the process becomes more complex when multiple entities are involved, each with its own lastModifiedDateTime field. Traditional methods, such as using properties or XSLT mapping, can be cumbersome and may lead to inefficient full loads from the system.

To address this challenge, I have developed a Groovy script that simplifies the process of retrieving delta records from multiple entities in Success Factors using the OData API. This script ensures efficient data retrieval without the complexity of traditional methods.

In this blog, I will walk you through the code and demonstrate how to use it to fetch delta records from various entities in Success Factors.

Step 1: Declare Properties in the Content Modifier

The first step is to declare the necessary properties in the Content Modifier. Below is an image showing the configuration of the Content Modifier:

Content Modifier Configuration

Explanation of Properties

Full_Dump

• Action: Create
• Source Type: {{Full Load}} // Externalised Parameter
• Description: This property indicates whether a full data dump is required. If set to true, the integration will fetch all records, not just delta records. This property is externalised.

Timestamp

• Action: Create
• Source Type: Expression
• Source Value: ${date:now:ddMMyyyy}
• Data Type: java.lang.String
• Description: This property captures the current timestamp in the specified format. It is used to mark the time of the current execution.

ThisRun

• Action: Create
• Source Type: Expression
• Source Value: ${date:now:yyyy-MM-dd'T'HH:mm:ss.SSS}
• Data Type: java.lang.String
• Description: This property stores the timestamp of the current run, which will be used as the LastRun timestamp in the next execution.

LastRun

• Action: Create
• Source Type: Local Variable
• Source Value: Last_Execution
• Description: This property holds the timestamp of the last successful run. It is used to determine the starting point for fetching delta records.

Initial_Timestamp

• Action: Create
• Source Type: Constant
• Source Value: {{Initial Timestamp}} // Externalised Parameter
• Description:  This needs to be provided when you are deploying the integration for the first time. Essentially, this will be your integration start date. You can also mention that the date could be the Go Live Date.

Need_Initial

• Action: Create
• Source Type: Constant
• Source Value: {{Need Initial}} // Externalised Parameter
• Description: When you are running the integration for the first time, this has to be set to true so that your Initial Timestamp becomes your Last Execution date. Since your integration won't have the Last run available, for the first run, Need_Initial has to be true. After deployment, you can remove "true" and redeploy the integration. This will provide you with a full load, and subsequent runs will only fetch delta changes.

From_Date

• Action: Create
• Source Type: Constant
• Source Value: {{From Date}} // Externalised Parameter
• Description: This property allows users to specify the starting date for fetching delta records. If provided, it does not override the LastRun timestamp but ignores the last execution and runs based on the dates provided by the admin, giving more control over the data retrieval period. The From_Date is the start date of the range. It will provide the delta records that have changed from this date.

To_Date

• Action: Create
• Source Type: Constant
• Source Value: {{To Date}} /// Externalised Parameter
• Description: This property allows users to specify the ending date for fetching delta records. If provided, it does not override the LastRun timestamp but ignores the last execution and runs based on the dates provided by the admin, giving more control over the data retrieval period. The To_Date is the end date of the range. It will provide the delta records that have changed up to this date.

Troubleshooting

• Action: Create
• Source Type: Constant
• Source Value: {{Troubleshooting}} // Externalised Parameter
• Description: This property should be set to True whenever you are using From_Date, To_Date, or Full_Dump. This ensures that the scheduled integration is not disturbed, allowing for smooth troubleshooting and testing without affecting the regular data processing schedule.

Declare necessary properties. The Content Modifier is connected to the Router.

Step 2: Router Condition

Next, you need to set up a Router Condition to store the Initial Timestamp as the last execution time when the Need_Initial property is passed as true. This ensures that the integration can handle initial loads and subsequent delta loads effectively. The Expression Type in the Router will be NON XML, and the condition will be ${property.Need_Initial} = 'true'.

When you remove the Need_Initial property, the integration will perform a normal run and will be passed through the Default Route.

Set up a Router Condition. Initial Run Path connects to Write Variable; Normal Run connects to Groovy Script.

Step 3: Configuring Write Variable

In this step, you will configure the Write Variable to store the Initial_Timestamp property with the current timestamp into the Last_Execution property. This ensures that the last execution time is updated correctly for future runs.

Write Variable Configuration:

• Set the target property to Last_Execution.
• Assign the value of the Initial_Timestamp property, which contains the current timestamp.

This step ensures that the integration accurately tracks the last execution time, enabling efficient delta record fetching in subsequent runs.

Connect Write Variable to End Message.

Step 4: Groovy Script Explanation

Here's the Groovy script that helps fetch delta records from multiple entities in Success Factors:

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

def Message processData(Message message) {
    // Retrieve properties from the message
    def pMap = message.getProperties();
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssXXX");
    dateFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
    Date date = new Date();

    // Pull the data stored in Write Variable as Property
    def lastRun = pMap.get("LastRun");
    def fromDate = pMap.get("From_Date");
    def toDate = pMap.get("To_Date");
    def fullDump = pMap.get("Full_Dump");

    def qFromDate;
    def qToDate;

    // Determine the query date range
    if (fullDump == "true") {
        // Set to a very early date for full dump
        qFromDate = "1900-01-01T00:00:00Z";
        qToDate = dateFormat.format(date);
    } else {
        // Use provided fromDate or lastRun if fromDate is not provided
        qFromDate = fromDate != "" ? fromDate : lastRun;
        // Use provided toDate or current date if toDate is not provided
        qToDate = toDate != "" ? toDate : dateFormat.format(date);
    }

    // Construct the query filter
    def stb_lastRun = new StringBuffer();
    def lastModifiedFields = [
        "lastModifiedDateTime",
        "userNav/lastModifiedDateTime",
        "employmentNav/lastModifiedDateTime",
        "employmentNav/personNav/lastModifiedDateTime",
        "employmentNav/personNav/personalInfoNav/lastModifiedDateTime"
    ];

    // Create conditions for each lastModifiedDateTime field
    def conditions = lastModifiedFields.collect { field ->
        "($field ge datetimeoffset'$qFromDate' and $field lt datetimeoffset'$qToDate')"
    }.join(" or ");

    // Append conditions to the query filter
    stb_lastRun.append(conditions);

    def val = stb_lastRun.toString();

    // Set the constructed query filter as a property in the message
    message.setProperty("QueryFilter", val);

    return message;
}

Script Breakdown

Imports and Initial Setup:

• The script imports necessary classes and sets up date formatting to UTC.

Fetching Properties:

• It retrieves properties like LastRun, From_Date, To_Date, and Full_Dump from the message.

Determining Query Dates:

• The script determines the from and to dates for the query. If fromDate is provided, it uses that; otherwise, it uses lastRun. Similarly, it sets toDate to the current date if not provided.

Constructing the Query Filter:

• It constructs a query filter using the lastModifiedDateTime fields from multiple entities. The filter checks if the lastModifiedDateTime is within the specified date range.

Setting the Query Filter:

• Finally, the script sets the constructed query filter as a property in the message.

(Optional) Code Modification to Fetch Delta Records from Other Entities of Success-factors:

• We have added the paths of EmpJob, EmpEmployment, User, PerPerson, PerPersonal to fetch the delta records from these entities. If you want to add other entities, kindly add the path in the part of the code shown below:

• This is where we have provided the paths of the lastModifiedDateTime fields for the specified entities. To include other entities, simply add their respective paths to this list.

def lastModifiedFields = [
    "lastModifiedDateTime",
    "userNav/lastModifiedDateTime",
    "employmentNav/lastModifiedDateTime",
    "employmentNav/personNav/lastModifiedDateTime",
    "employmentNav/personNav/personalInfoNav/lastModifiedDateTime"
];

Connect Groovy Script to Request Reply.

Step 5: Using Request Reply & Connecting with the Receiver

Request Reply:

• Use the Request Reply step to connect with the Success Factors OData V2 Adapter.

Configure Connections:

• Configure the connections to your Success Factors system. Ensure that you have the correct credentials and endpoint URLs.

Select Entities and Fields:

• In the Processing section, select the entities and fields you need to fetch delta records from.

Add Filter Condition:

• After configuring the entities and fields, click on Finish.
• Add the filter condition: &$filter=${property.QueryFilter}. This ensures that the query uses the filter constructed by the Groovy script to fetch only the delta records.

Connect Request Reply to Process Call.

Step 6: Process Call Configuration in Main Integration Process

The Process Call is used to update the LastRun date to ensure accurate tracking of the last execution time. It also checks if the run is for troubleshooting by verifying the Troubleshooting property, allowing for specific handling or logging during debugging. Configuration involves selecting the Local Integration Process, such as a Troubleshooting Process.

Set up Local Integration Process to handle troubleshooting & to Update Last Run property.

Step 7: Configuring Local Integration Process

In this step, we configure the Local Integration Process to check if the run is for troubleshooting. This involves using a router to verify the Troubleshooting property. If the Troubleshooting property is set to true, the integration will consider this as a test run or debugging is taking place, and it will not update the Last_Execution date. If it is blank, the process will update the Last_Execution  date to ensure accurate tracking of the last execution time.

Step 8: Router Condition in Troubleshooting Process

Next, you need to set up a Router Condition to verify if the Troubleshooting property is passed as true. If it is true, the integration will pass through Route 1. The Expression Type in the Router will be NON XML, and the condition will be ${property.Troubleshooting} = 'true'.

If the Troubleshooting property is not passed as true, the integration will pass through Route 2 (False), which is the default route. In this case, it will store the ThisRun timestamp into the Last_Execution property, ensuring that the last execution time is updated correctly.

True Route connects to End; False Route connects to Write Variable.

Step 9: Configuring Write Variable in Troubleshooting Process

In this step, you will configure the Write Variable to store the ThisRun property with the current timestamp into the Last_Execution property. This ensures that the last execution time is updated correctly for future runs.

Write Variable Configuration:

• Set the target property to Last_Execution.
• Assign the value of the ThisRun property, which contains the current timestamp.

This step ensures that the integration accurately tracks the last execution time, enabling efficient delta record fetching in subsequent runs.

Connect Write Variable to End.

Final Integration Overview

After performing all the steps mentioned above, this is how your integration should look like. We have started it with the Start Timer, but this can vary based on your specific requirements. The integration is designed to handle both initial and subsequent runs efficiently, ensuring accurate data retrieval and processing. By following the outlined scenarios, you can customise the integration to meet your specific needs, whether it's for a full load, date range, or delta records.

As this blog focuses on fetching delta records, we haven't configured the Target System, which could be FTP or any third-party system. You might need to structure the data according to the target system using Message Mapping or XSLT Mapping. Additionally, you may need to handle exceptional subprocesses and process successful and failed responses from the third-party system. Ensure to add the Process Call at the end so that it only stores the run when the integration is successfully completed.

Integration Deployment Configuration:

Go Live Configuration

1. Enter the Initial Timestamp with the Go Live date or any required date (format: 9999-12-31T00:00:00).
2. Set Need_Initial to true to consider the Initial Timestamp.
3. Save and deploy the integration.
4. Go back to Configure and remove true from Need_Initial.
5. Deploy the integration again.
6. The first run will perform a full load, and subsequent runs will provide delta changes.

Date Range Configuration

1. Enter the From_Date and To_Date to fetch records updated between these dates (format: 9999-12-31T00:00:00).
2. Set Troubleshooting to true so this deployment won't be considered the last run.
3. Save and deploy the integration.
   
4. Go back to Configure, remove true from Troubleshooting and clear the dates from From_Date and To_Date.
5. Save and Deploy the integration again.
6. The integration will now run as scheduled and provide delta records.
   

Scenario 3: Full Dump Configuration

1. Set Full_Dump to true to fetch all active records.
2. Set Troubleshooting to true so this deployment won't be considered the last run.
3. Save and deploy the integration.
   
4. Go back to Configure, remove true from Full_Dump and Troubleshooting.
5. Save and deploy the integration again.
6. The integration will now run as scheduled and provide delta records.
   

Note: Always save and deploy the integration after changing its configuration.

Additional Note: If you are using a Start Timer in your integration, make sure to change it to run once, or it will provide the output as scheduled.

Conclusion

In this blog, we explored how to efficiently fetch delta records from multiple entities in SuccessFactors using the OData API. By using a combination of Groovy scripts, Content Modifiers, and Router Conditions, we can streamline the process and avoid the complexities of traditional methods. This approach ensures that we only retrieve the necessary data, improving the efficiency and performance of our integrations.

By following these steps, you can effectively manage delta records in SuccessFactors, ensuring that your data is always up to date without overloading the system. If you have any questions or need further assistance, feel free to reach out. Happy integrating!

Best Regards,
Rasesh Thakkar.