ODI Integration with Salesforce

In this blog post, I’ll explain how you can use Oracle Data Integrator (ODI) to extract data from the Salesforce cloud and thereby perform detailed Analysis / Reporting in order to make better decisions.

Prerequisites

• Salesforce Developer Edition account to access the Salesforce data.
• JDBC driver to interact with Salesforce from ODI.
  • Get this from https://www.progress.com/datadirect-connectors
• Download Cloud Technology and import to ODI technologies to support Salesforce.
  • Open Cloud Technology  and save this xml file
  • Go to Topology manager and Import the saved xml file under Pysical Architecture / Technologies
• Download RKM and import to ODI to read the data from any cloud technology in generic format.
  • Open RKM and save this xml file
  • Open the Designer in ODI => Right Click on the Global Knowledge Modules => Import Knowledge Modules
    

Now that all Prerequisites are in place, follow the steps below to implement Salesforce Integration with ODI.

Create Salesforce Data Server:

• Password = Salesforce login password + security token

• On JDBC tab, Please enter details as below
  • User = Salesforce Login User
  • Password = Salesforce login password + security token

• Now Test the connection using the Local Agent and make sure you get a Successful Connection message.

Create Oracle Data Server

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

• Go to JDBC tab and provide the JDBC connection details and Test the Connection using Local Agent and make sure you get a Successful Connection message.

Create Physical Schemas

• Create Schema sforce and odi_target with necessary roles to create and load tables.

Create Context                                                                                                         Create SFDC Logical Schema

Create SFDC Model

• Go to Reverse Engineer Tab and Select below shown options

• Now Click on Reverse Engineer button and All Salesforce tables should appear as shown below

Create Oracle Model

• Create a Model for Oracle say  ODI_TARGET as well and Import an already created sample table say Opportunity. Use below mentioned scriptCreate Table OPPORTUNITY(ROWWID VARCHAR2(20), ACCOUNTID VARCHAR2(20), SYS_NAME VARCHAR2(200),  STAGENAME VARCHAR2(40), AMOUNT NUMBER(20,2), PROBABILITY NUMBER(3) );
• Create a Project say “SalesForce” and Create an Interface with below mentioned details to load data from SalesForce to Oracle .
• Name it as OpportunityLoad_From_Salesforce_To_Oracle.
• Now go to mapping Tab and Drag opportunity table from SFDC to Left Source Layer and Opportunity table from Oracle ODI_Target to right Target Layer as shown below:

• Go to Flow Tab, Click on the Source and select the LKM SQL to SQL KM and Click on the Staging area and select the IKM SQL Incremental Update KM as shown below

• Execute the interface and now the data is populated.
• Go to Oracle database and verify the data for target table Opportunity.

We have loaded the data from Salesforce to Oracle using ODI and the loading from Oracle to Salesforce is not possible in ODI using cloud applications technology as they do not allow DDL statements and the data types and syntax is different in both the Oracle and Salesforce. However Salesforce provides an inbuilt Apex Data loader tool to load data into Salesforce.

Download Apex Data Loader from Salesforce account in order to load data into Salesforce from CSV files.

• Login to Salesforce => Setup => Administer => Data Management => Data Loader => Click on Download Data Loader
• Install Data Loader and Run Data Loader from Start menu 
• Use Options mentioned above to perform Insert / Update /Upsert or Delete on Salesforce Data .

Hope the above mentioned write-up was helpful. Do reach out to me if you have any questions.