Oracle Data Integrator (ODI) and BI Publisher JDBC connections to Salesforce

Oracle Data Integrator (ODI) and BI Publisher JDBC connections to Salesforce

October 17, 2012 0 Comments

With the conclusion of the Dreamforce and Oracle OpenWorld conferences in SFO last month, I am now seeing several new POCs using technology learned from both worlds.  While DataDirect attended both events, all I am willing to share is that one had better music and the other had better breakfast selections.  However having unique expertise on both sides, I want to share what I have learned from these new POCs requiring DataDirect Connect XE for JDBC Salesforce drivers to connect both Oracle BI Publisher and Oracle Data Integrator to Salesforce.com, Database.com and Force.com.

And read my previous post on real-time SQL connectivity via ODBC drivers from the Oracle BI/DW stack including Oracle Business Intelligence (OBIEE) and Informatica PowerCenter using DataDirect Connect XE for ODBC Salesforce.com drivers.

How to build a real-time Oracle BI Publisher Report with Salesforce data

1 . Install Progress DataDirect ConnectXE for JDBC using the instructions in the Progress DataDirect Connect for JDBC Installation Guide.  Click here to download a free 15-day trial.  (article assumes installation directory is C:\Jdbc\Connect_Series_for_JDBC_50)

2. Launch java cloud service for connectivity to Salesforce from command prompt:

C:\jdbc\Connect_Series_for_JDBC_50\lib>java -classpath .;sforce.jar com.ddtek.cloud.sql.Server [Server@576a9d42]: Initiating startup sequence... [Server@576a9d42]: Server socket opened successfully in 1045 ms. [Server@576a9d42]: Listening at port: 19928 [Server@576a9d42]: Startup sequence completed in 1052 ms. [Server@576a9d42]: 2012-06-18 21:05:27.579 DataDirect Connect Salesforce JDBC/OD BC server is online [Server@576a9d42]: Use [Ctrl]+[C] to terminate.

3. Copy C:\Jdbc\Connect_Series_for_JDBC_50\lib\sforce.jar into: C:\Program Files\Oracle\BI Publisher Trial Edition\bipserver\domains\bipdomain\lib

4. Start or Restart BI Publisher and Sign in to BI Publisher

5. Click Administration

6. Select JDBC Connection

 

7. Click "Add Data Source"

8. Enter Connection String:  jdbc:datadirect:sforce://login.salesforce.com;SecurityToken=xxxxxx;SQLEngineMode=server

9. Once the test-connection succeeds, you can start developing in BI Publisher.

Below is a simple BI Publisher report I created that fetches data directly from my Salesforce.com developer account:

How to get started with Oracle Data Integrator 11gR1 (ODI)

Oracle supports third party JDBC drivers for use with ODI such as the DataDirect Connect XE for JDBC Salesforce driver.  Refer to section "2.4.1 Add Additional Drivers and Open Tools" in the Oracle Data Integrator documentation for installation of third party JDBC drivers on Unix/Linux/Windows.  Oracle Data Integrator 11gR1 documentation refers to the installation of DataDirect drivers, but this does not include Salesforce.

Which ODI knowledge module is recommended for use with Salesforce.com

Use the Generic SQL Knowledge Modules per Oracle documentation since the DataDirect Connect XE for JDBC driver supports SQL-92 access to Salesforce.

screenshot from oracle.com

 

Interested in learning more?

Contact us to learn how other organizations are making progress with Salesforce and Oracle connectivity from DataDirect.Oracle Data Integrator (ODI) and BI Publisher JDBC connections to Salesforce

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments
Comments are disabled in preview mode.
Topics
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
232x131_resourceimage
Getting Ahead of the Hybrid Data Curve
Read More
 
570x321__Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Read More
 
232-132_How to Simplify Application Usage & Deployment with Microapps
How to Simplify Application Usage & Deployment with Microapps
Read More