2 Replies Latest reply on Aug 23, 2018 3:01 AM by Sorabh Agarwal

    Modest Proposal: Bi-Directional Salesforce / Database Integration with best achievable latency by combining Real Time (CAI) & Batch (CDI) patterns

    Casey Clayton Seasoned Veteran

      Reposted from Cloud Data Integration Community

      Document Overview

      The goal of this document is to provide an example architectural framework.  This will be done by walking through the solution to a real-world use case involving two-way data synchronization between Salesforce.com (SFDC) & a SQL/Oracle/MySQL Database.  Because one system (Salesforce) has a mechanism for communicating events in the form of Outbound Messages, but the other (a SQL Server DB) does not, the optimal architecture is actually a blend of both Batch & Real Time methodologies.

       

       

      Two-way integration is especially critical in organizations that are looking to increase the ROI of their Salesforce investment.  This is in terms of agent productivity, customer satisfaction, and KPI reports to leadership.  The lack of any ability to correct bad data that comes in from elsewhere and/or the need for “swivel-chair” & “double-entry” in order to get information, already logged in SFDC, to upstream systems, is not just frustrating for both end users and executives.  In fact, the compromise to go with “One Way” integration to SFDC is noted by many Partner/Consultants as the top cause of project failure after release. 

      For this scenario we will be using a mix of the following applications/techniques.

      • ICS DSS/CMD Tasks (for moving Data from Front-End Applications to the DB and from the DB to SFDC)
      • ICRT Real Time Processes (both to ConvertLead and to move Data from SFDC to the DB)
      • SFDC Outbound Messages (to initiate upstream data sync via ICRT, whenever a record is changed)

      Use Case / Scenario

      The scenario is that a company has fairly-recently decided to use Salesforce.com as their primary CRM / Pipeline Management application.  Their current infrastructure includes existing Front-end Applications that consumers interact with, and a Central “Data Warehouse” DB that data from these applications is replicated to.  The Front-end Applications allow a consumer to create a “Trial Account”, to Update Contact or Account details, and, if the consumer decides to purchase, convert their “Trial Account” to a “Paid Account”. 

      The mechanisms for the Front-end Applications to write back to the Central DB table (which we will call “LEAD_DATA”) are already in place.  There is no way to alter the front end applications at this time to allow for full Real Time integration.  Because of this, the architecture that was settled on that plans to use scheduled ICS DSS/CMD tasks to Insert & Update data from the DB Source to the SFDC Target.

      When an Account is first created as a “Trial Account”, it gets inserted by the ICS Task into SFDC as a Lead Record. As part of this, the Lead ID of the newly created SFDC Lead Record must be written back to the “sfdc_lead_id” field in LEAD_DATA.

      If a customer becomes a “Paid Account”, this is indicated by a “paid_indicator” field on the LEAD_DATA table. For all LEAD_DATA rows where the “paid_indicator” has been changed to True, the associated SFDC Lead Record must undergo a “LeadConvert” action.  This will automatically create an Account & Contact record.  As part of this, the “Converted Account ID” & “Converted Contact ID” on the newly converted Lead Record must be written back to the “sfdc_account_id” & “sfdc_contact_id” fields in LEAD_DATA respectively.

      The Combination ICS Batch / ICRT Real Time approach was needed for two reasons

      • Converting a Lead in SFDC is not an ETL/CRUD action, but it’s more like a Process Event. In this case, Field updates via ICS Data Synchronization alone are not sufficient.  SFDC requires the ConvertLead action to be initiated via an Authenticated SOAP Web Service callout

        In our use case, this action still needs to occur functionally as “step” in a Task Flow.  The ICS Batch Task will update the SFDC Lead Record field “Ready to Convert” to TRUE on all LEAD_DATA rows that have had their “paid_indicator” set since the last batch process.  The process then needs the ConvertLead action to be called on all Lead Records that have been updated to have “Ready to Convert” = TRUE. 

       

      • Allow SFDC users to create and update data in SFDC and to have their changes reflected back in LEAD_DATA and the other systems. Removing the “swivel-chair” will greatly improve data quality, usability, and adoption.

        This allows end users to incorrect or out-of-date information, add new Leads/Contacts/etc.   To avoid the risk of existing synchronization tasks overwriting the changed values before they are sent back upstream, ideally these updates form SFDC back to the Central LEAD_DATA table would occur in Real Time.

      Schema & Connection Assumptions

      • Database: “LEAD_DATA” table with Primary Key “ID” and fields for SalesForce Record ID’s
        • Key Fields
          • ID - PK on table, used to populate Source ID (External) field on SFDC Lead Record
          • Sfdc_lead_id - Set when Lead Record is created in SFDC
          • Sfdc_account_id – Set when Lead Record is converted in SFDC
          • Sfdc_contact_id – Set when Lead Record is converted in SFDC
          • “paid_indicator” – A field or a formula that indicates when a Lead should be Converted
      • SFDC: Lead Object fields
        • Key Fields
          • Id – SFDC Record ID.  Synced to “sfdc_lead_id” field on LEAD_DATA row
          • Converted Account ID.  Salesforce System Field to stored Account ID after Convert
          • Converted Contact ID.  Salesforce System Field to stored Contact ID after Convert
          • Source ID – Value of ID (Primary Key) field on LEAD_DATA row
          • “Ready to Convert” - Set from “paid_indicator” field as part of ICS Update Sync, indicates that a lead has met criteria to be converted to an Account & Contact
      • SFDC: User account for “API User”
        • Having a dedicated API user for integrations is a Best Practice
        • Set as the Username in the ICRT Salesforce connection & as the “Send as user” in Outbound Messages
        • Having an API user allows Workflow Rule criteria to determine if a particular update was made by a User, and therefore must be synchronized back to the DB.  If the update it was done by the DB synchronization task, there is no need to write the same data back.
      • Existing ICS CMD/DSS tasks to Insert & Update data in SFDC
      • JDBC Connection to Source DB in ICRT

       

      Solution Components

      1. 1. ICRT Processes – Endpoints for Workflow Initiated Outbound Messages
        1. a. OBMLeadCreated
          • Applies To: Salesforce>Lead
          • Create or Update LEAD_DATA DB table row.  Populates sfdc_lead_id on Leads created from DB insert.  Creates entire record for Leads created in SFDC

       

      1. b. OBMLeadSyncConvert
        • Applies To: Salesforce>Lead
        • Synchronizes updates to Lead records with LEAD_DATA DB table.  Will also execute the ConvertLead action on Lead records that have "Ready to Convert" = TRUE.

       

      1. 2. Salesforce Workflow Rules – Execute Lead Data is Created or Changes
        1. a.    SyncCreatedLead
          • Description
            DB: Sync Leads with "Sendgrid" Source. Includes DW created & SFDC created
          • Evaluate When
            Record is Created
          • Criteria
            Lead: Lead SourceSTARTS WITHSendGrid
          • Action
            Outbound Message: Call OBMLeadCreated
          1. b.    SyncExistingLead
            • Description
              DB: Synchronizes USER changes made in SFDC to back to Data Warehouse and also Converts any "Ready to Convert" Lead.  Only on Leads that have “Source ID”
            • Evaluate When
              Record is created, and every time it’s edited
            • Criteria
            • Action
              Outbound Message: Call OBMLeadSyncConvert

       

      1. 3. Salesforce Outbound Messages – Callouts to ICRT Process Endpoints
        1. a.    Call OBMLeadCreated

       

      1. b.    Call OBMLeadSyncConvert

       

      1. 4. ICS Data Synchronization Tasks – Scheduled Batch Data Sync to SFDC
        1. a.    DB Insert Lead Data in SFDC
          • Source
            Connection to DB
          • Target
            Connection to SFDC
          • Action
            Insert
          • Filters
            Source sfdc_lead_id is NULL

       

      1. b.    DB Update Lead Data in SFDC
        • Source
          Connection to DB
        • Target
          Connection to SFDC
        • Action
          Update
        • Filters
          Source sfdc_account_id is NULL

       

      Procedure for New Leads

      1. 1. Pre-Fill “LEAD_DATA” DB table
        1. a.      Step simulates the Customer/Contact data that comes in from the Front-End Applications
        2. b.      All rows would initially have NULL for “sfdc_lead_id”, “sfdc_account_id”, “sfdc_contact_id”

       

      1. 2. Run ICS Task: DB Insert Lead Data in SFDC
        1. a. This would be the scheduled job that regularly checks for new records in the DB without “sfdc_lead_id” and then Inserts those rows as leads in SFDC
        2. b. Step simulates the Customer/Contact data that comes in from the Front-End Applications
        3. c. The Lead “Source ID” field is the “External ID”, and will get populated with the ID from LEAD_DATA

       

      1. 3. SFDC Workflow Rule “SyncCreatedLead” sends Outbound Message for new Leads
        1. a. Outbound message has API Endpoint for “OBMLeadCreated” Process
        2. b. A separate call out is made for each Lead record

       

      1. 4. ICRT Process does update of LEAD_DATA table
        1. a. Process executes using Id passed in from Outbound Message
        2. b. Process checks for existing LEAD_DATA record and sets “sfdc_lead_id”
        3. c. If no existing record, it means the Lead was created in SFDC. The process will then Create a new row in LEAD_DATA and fully populate it with the data from SFDC
        4. d. The process will also set the “Source ID” field on the Lead Record with the ID of the LEAD_DATA row

       

       

      Procedure for Updated Leads

      1. 1. Update fields on a fixed number of rows in LEAD_DATA
        1. a.      Step simulates an update made in Front-end Application that gets sent to DB.
        2. b.      You will want to change the “paid_indicator” on a few rows to simulate the case where Customer makes a purchase and becomes a Paid Account
        3. c.      Rule is for the matching SFDC Lead Record for these newly Paid Accounts to Convert into Account & Opportunity

       

      1. 2. Run ICS Task: DB Update Lead Data in SFDC
        1. a. This would be the scheduled job that regularly checks for new records in the DB that have “sfdc_lead_id, but no “sfdc_account_id”.
        2. b. Any changes to the LEAD_DATA row should be reflected on the SFDC Lead

       

      1. 3. SFDC Workflow Rule “SyncExistingLead” sends Outbound Message for changed Leads
        1. a. Outbound message has API Endpoint for “OBMLeadSyncConvert” Process
        2. b. Workflow rule only runs on Lead records that have their “Source ID” populated
        3. c. A separate call out is made for each Lead record

       

      1. 4. ICRT Process does update of LEAD_DATA table
        1. a. Process executes using Id passed in from Outbound Message
        2. b. If Process sees the “Ready to Convert” on the Lead set to True, it then Executes the ConvertLead action
        3. c. Converted Leads will have their “Converted Account ID” & “Converted Contact ID” populated by SFDC
        4. d. The process will then update the correlated row in LEAD_DATA and fully populate it with the data from SFDC. 
        5. e. For Converted Leads, this will mean the “sfdc_account_id” & “sfdc_contact_id”  will be populated on the LEAD_DATA row
        6. f. This part works the same for SFDC originated leads and DB originated leads.