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