Querying many entities in SuccessFactors thru oData in SAP CPI

[ananda_paul]

Hi All,

I am trying to restructure the existing integration flow. The current flow grabs the CompoundEmployee data from SuccessFactors through SOAP protocol. Please see the query. The WHERE clause is checking the lastModifiedOn. As its fetching the data from CompoundEmployee, its listing down all the fields from the entities mentioned in the query, because of which the volume of the data is huge. I tried to reduce the number of fields by changing the query. Unfortunately, I could not do as its not allowing me to do so.

I thought of using the oData where I can choose the particular field from the specific entity. I am able to find the respective entities and fields. The huge query is fetching data, but not all. When I googled about the $filter conditions, the SAP doc says that the $filter is not applied to entities that has one-to-many mapping. Can you please guide me to fix this? The volume of data from oData query is supposed to be huge, but its not. I think, as the doc says, the $filter is not applied to the child entities.

SOAP Query:

SELECT person, compensation_information, personal_information, address_information, email_information, employment_information, paycompensation_recurring, job_information, deduction_recurring, payment_information, national_id_card, phone_information FROM CompoundEmployee WHERE last_modified_on > to_datetime('${property.LRT}')

oData Query:

https://api10preview.sapsf.com/odata/v2/PerPersonal?$select=personIdExternal,customDate1,customStrin...,
maritalStatusNav/externalCode,salutationNav/externalCode,
personNav/dateOfBirth,
personNav/lastModifiedOn,
personNav/homeAddressNavDEFLT/stateNav/externalCode,
personNav/homeAddressNavDEFLT/address1,
personNav/homeAddressNavDEFLT/address4,
personNav/homeAddressNavDEFLT/city,
personNav/homeAddressNavDEFLT/state,
personNav/homeAddressNavDEFLT/zipCode,
personNav/homeAddressNavDEFLT/country,
personNav/homeAddressNavDEFLT/startDate,
personNav/homeAddressNavDEFLT/endDate,
personNav/homeAddressNavDEFLT/addressType,
personNav/homeAddressNavDEFLT/lastModifiedOn,
personNav/phoneNav/phoneTypeNav/externalCode,
personNav/phoneNav/phoneNumber,
personNav/phoneNav/phoneType,
personNav/phoneNav/lastModifiedOn,
personNav/emailNav/emailType,
personNav/emailNav/emailTypeNav/externalCode,
personNav/emailNav/emailAddress,
personNav/emailNav/lastModifiedOn,
personNav/employmentNav/userId,
personNav/employmentNav/startDate,
personNav/employmentNav/lastModifiedOn,
personNav/employmentNav/jobInfoNav/eventNav/externalCode,
personNav/employmentNav/jobInfoNav/employeeClassNav/externalCode,
personNav/employmentNav/jobInfoNav/startDate,
personNav/employmentNav/jobInfoNav/endDate,
personNav/employmentNav/jobInfoNav/workscheduleCode,
personNav/employmentNav/jobInfoNav/costCenter,
personNav/employmentNav/jobInfoNav/customString7,
personNav/employmentNav/jobInfoNav/employeeClass,
personNav/employmentNav/jobInfoNav/event,
personNav/employmentNav/jobInfoNav/payScaleLevel,
personNav/employmentNav/jobInfoNav/businessUnit,
personNav/employmentNav/jobInfoNav/lastModifiedOn,
personNav/employmentNav/compInfoNav/payGroup,
personNav/employmentNav/compInfoNav/startDate,
personNav/employmentNav/compInfoNav/endDate,
personNav/employmentNav/compInfoNav/lastModifiedOn,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/payComponent,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/paycompvalue,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/customString1,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/customString2,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/startDate,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/endDate,
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/lastModifiedOn,
personNav/employmentNav/paymentInformationNav/effectiveEndDate,
personNav/employmentNav/paymentInformationNav/effectiveStartDate,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/paymentMethod,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/customPayType,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/amount,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/routingNumber,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/accountNumber,
personNav/employmentNav/paymentInformationNav/toPaymentInformationDetailV3/accountOwner,
personNav/nationalIdNav/cardType,
personNav/nationalIdNav/nationalId,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/RecurringDeduction_effectiveStartDate,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/endDate,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/payComponentType,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/amount,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/cust_MemberNumber,
personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems/cust_USInumber,
personNav/employmentNav/userNav/usersSysIdOfEmpCostDistributionNav/items/EmpCostDistribution_effectiveStartDate,
personNav/employmentNav/userNav/usersSysIdOfEmpCostDistributionNav/items/costCenter,
personNav/employmentNav/userNav/usersSysIdOfEmpCostDistributionNav/items/percentage,
&$expand=maritalStatusNav,salutationNav,personNav,personNav/homeAddressNavDEFLT,personNav/phoneNav,
personNav/emailNav,personNav/emailNav/emailTypeNav,personNav/employmentNav,personNav/employmentNav/jobInfoNav,
personNav/employmentNav/compInfoNav,personNav/employmentNav/compInfoNav/empPayCompRecurringNav,personNav/phoneNav/phoneTypeNav,
personNav/homeAddressNavDEFLT/stateNav,personNav/employmentNav/jobInfoNav/eventNav,personNav/employmentNav/jobInfoNav/employeeClassNav,personNav/employmentNav/userNav/userSysIdOfRecurringDeductionNav/recurringItems,
personNav/employmentNav/userNav/usersSysIdOfEmpCostDistributionNav/items
&$filter=(lastModifiedOn ge ${property.LastRun} or 
personNav/employmentNav/jobInfoNav/lastModifiedOn ge ${property.LastRun} or 
personNav/homeAddressNavDEFLT/lastModifiedOn ge ${property.LastRun} or
personNav/phoneNav/lastModifiedOn ge ${property.LastRun} or
personNav/emailNav/lastModifiedOn ge ${property.LastRun} or
personNav/employmentNav/lastModifiedOn ge ${property.LastRun} or
personNav/employmentNav/jobInfoNav/lastModifiedOn ge ${property.LastRun} or
personNav/employmentNav/compInfoNav/lastModifiedOn ge ${property.LastRun} or
personNav/employmentNav/compInfoNav/empPayCompRecurringNav/lastModifiedOn ge ${property.LastRun})&$format=json

Reference to oData in SAP doc:

Link to oData - $filter condition

Note: You can only filter top level entities and child entities that have 1:1 relationships with their parents. When you apply $filter on an entity that has one-to-many (1:M) relationship with its children, the child entities will not be filtered. The query returns all parents with full list of child entities even though only one child meets the condition in the filter.

[Sriprasadsbhat]

Hello Ananda,

As mentioned in the document its standard behavior wrt filter condition at association levels.If you are not able to get what you are expecting from OData due to feature restrictions you can still go with CompoundEmployee and optimize the IFlow with considering below points.

1) Usage of Looping Process to query Compound Employee data in batches.

2) Make sure you will normalize the CE structure in very first step of your IFlow ( may be inside looping process )

Reference:

https://help.sap.com/viewer/368c481cd6954bdfa5d0435479fd4eaf/Cloud/en-US/f58c2ba1c8d345fab64c565c187...

https://blogs.sap.com/2017/03/17/sap-cp-is-sap-hci-looping-process-call-in-detail/

Regards,

Sriprasad Shivaram Bhat