Oracle

ODI Integration with Salesforce

featured800

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 / Technologiescloudtechcloudapps
  • 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
      rkm

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

sfdc_dataserver

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

jdbc_details

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

oracledataserver

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
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.sfdc_physchemaodi_physchema

Create Context                                                                                                         Create SFDC Logical Schema

contextsfdclogical

Create SFDC Model

sfdcmodel

  • Go to Reverse Engineer Tab and Select below shown optionsreverseengr
  • Now Click on Reverse Engineer button and All Salesforce tables should appear as shown belowsfdc_tables

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:

 

mapping

  • 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

flow

  • 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 data-loader
  • 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.

Thoughts on “ODI Integration with Salesforce”

  1. Venkata Gireesh

    Hi Pradeep,

    Thank you for the blog on salesforce integration with ODI. While trying to implement these steps on ODI (12c version), I am facing issues in getting the Salesforce specific JDBC driver and Cloud technology xml as mentioned in this blog. Please find the below pointers for details :

    1. Unable to get JDBC Driver over the link provided : https://www.progress.com/datadirect-connectors
    2. Cloud Technology : Below link is not active to download and import the cloud technology as given in the steps above.
    https://java.net/downloads/oracledi/ODI/Knowledge%20Modules/TECH_Cloud_Applications_11g.xml

    If you can help on the above at the earliest it would be helpful. Thanks.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Pradip Devadiga

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram