Wednesday 25 September 2019

Publishing data to Salesforce from SAP HANA using SDI.

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

Define the trigger.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

In Salesforce, we see that a job was created for the account objects.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

Define the Custom Fields and Triggers

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

In SAP HANA, we import the virtual for the platform event AccountContactEvent.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Study Materials, SAP HANA Tutorial and Materials, SAP HANA SDI, SAP HANA Studio, SAP HANA Certifications

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