Introduction
There are use cases where one has to publish huge volume of data from SAP HANA to Salesforce, this blog describes options that are available with the Advantco Salesforce adapter for SDI. We describe how to use virtual tables to upsert (update/create) standard or custom objects in Salesforce. We will also handle platform events in case of more complex use cases where one has parent and child objects.
Configure the Remote Source for batch processing.
Configure the Process Prepare statement(s) Mode as Collect And Process Each Batch Later.
Option 1: Using virtual tables to create or update objects in Salesforce
Any operation (CREATE, UPDATE, DELETE) on a virtual table in HANA results to the same in Salesforce. For example, insert a new account into the Account virtual table, results in creation of a new Account object in Salesforce.
In this example, we create a trigger that will be activate whenever there is an insert or update into the table real table CUST_REV_SQL. The trigger will select any new or modified record and insert it into the virtual Account table.
Import Account virtual table.
Define the trigger.
CREATE trigger "SFDC_US_DEMO"."TRIGGER_CUST_REV_SQL"
AFTER INSERT OR UPDATE ON "SFDC_US_DEMO"."CUST_REV_SQL"
REFERENCING NEW ROW as newAccount
FOR EACH ROW
BEGIN
INSERT INTO "SFDC_US_DEMO"."VT_Account"("Name","SAP_Source_ID_c","BillingCity","BillingPostalCode","BillingStreet","AccountNumber","SDI_test_c")
VALUES(:newAccount."NAME",:newAccount."CUST_ID",:newAccount."CITY",:newAccount."POST_CODE",:newAccount."STREET",:newAccount."CUST_ID",'US_AGENT');
END;
We update the records in the table CUST_REV_SQL, which results into firing the trigger that was defined for this table.
In Salesforce, we see that a job was created for the account objects.
Option 2: Using Salesforce platform events.
When we have an use case where we have to create or update multiple objects in Salesforce, like Account and Contact, we can use platform event.
Create a platform event AccountContactEvent in Salesforce. This event is used to create an Account with a Contact.
Define the Custom Fields and Triggers
In SAP HANA, we import the virtual for the platform event AccountContactEvent.
We define new trigger, this time we insert any new or updated record of the table CUST_REV_SQL into the virtual table VT_AccountContactEvent__e.
CREATE TRIGGER "SFDC_US_DEMO"."TRIGGER_CUST_REV_SQL_PLATFORM_EVENT"
AFTER INSERT OR UPDATE ON "SFDC_US_DEMO"."CUST_REV_SQL"
REFERENCING NEW ROW NEWACCOUNT
FOR EACH ROW
BEGIN INSERT INTO "SFDC_US_DEMO"."SFDC_US_SANDBOX2_AccountContactEvent__e"("Account_Name__c","Contact_LastName__c","Account_ExtID__c","Account_BillingCity__c","Account_BillingStreet__c","Account_BillingZipcode__c","Contact_Title__c","Contact_Email__c","Account_BillingState__c")
VALUES(:newAccount.NAME,:newAccount.NAME,:newAccount.CUST_ID,:newAccount.CITY,:newAccount.STREET,:newAccount.POST_CODE,'Mr','test@advantco.com','NC');
END
After running the update script, we see the following in Salesforce.
Summary: The Advantco Salesforce adapter for SDI provides bi-directional integration with Salesforce and HANA. In case of very large data volume, publishing data directly from HANA to Salesforce is option that valid option.
Appendix: For completion, this is the example code of the APEX trigger. This is not a production ready code, use with caution.
trigger InsertAccountContactRecord on AccountContactEvent__e (after insert) {
List<Account> accts = new List<Account>();
List<Contact> conts = new List<Contact>();
Integer counter = 0;
String replayId = null;
for(AccountContactEvent__e event : Trigger.New) {
replayId = event.ReplayId;
// Increase batch counter.
counter++;
// Only process the first 200 event messages
if (counter > 200) {
// Resume after the last successfully processed event message
// after the trigger stops running.
// Exit for loop.
break;
}
Account a = new Account(
Name = event.Account_Name__c,
SAP_Source_ID__c = event.Account_ExtID__c,
BillingCity = event.Account_BillingCity__c,
BillingStreet = event.Account_BillingStreet__c,
BillingPostalCode = event.Account_BillingZipcode__c,
BillingState = event.Account_BillingState__c);
accts.add(a);
Contact c = new Contact(
LastName = event.Contact_LastName__c,
Email = event.Contact_Email__c,
Title = event.Contact_Title__c);
conts.add(c);
}
try {
// Insert Account records
Database.SaveResult[] srAccountList = Database.insert(accts, false);
List<String> accountIds = new List<String>();
for (Database.SaveResult sr : srAccountList) {
if (sr.isSuccess()) {
// Operation was successful, so get the ID of the record that was processed
accountIds.add(sr.getId());
}
}
Integer i = 0;
for (Contact c : conts) {
c.AccountId = accountIds.get(i++);
}
// Insert Contact records
Database.SaveResult[] srContactList = Database.insert(conts, false);
List<String> contactIds = new List<String>();
for (Database.SaveResult sr : srContactList) {
if (sr.isSuccess()) {
// Operation was successful, so get the ID of the record that was processed
contactIds.add(sr.getId());
}
}
// Set Replay ID after which to resume event processing
// in new trigger execution.
if (replayId != null) {
EventBus.TriggerContext.currentContext().setResumeCheckpoint(replayId);
}
} catch (DmlException e) {
System.debug('err: ' + e);
}
}
No comments:
Post a Comment