For many Oracle Data Integrator professionals, there is growing demand to consume SaaS data such as Oracle Eloqua Marketing Cloud. Let’s get real, you’re responsible for supporting a wide range of data sources and may have never heard of Eloqua. But with standards based JDBC connectivity to SaaS data – you become an instant marketing data scientist delivering aggressive requirements from demanding business sponsors.
My favorite feature of ODI remains the standard JDBC support and the platform’s commitment to heterogeneous data sources. JDBC support is the first key feature listed in the data sheet.
“Out-of-box integration with databases,
ERPs, CRMs, B2B systems, flat files,
XML data, LDAP, JDBC, ODBC”
How does JDBC access to Eloqua work?
Oracle Data Integrator developers are accustomed to using the bundled DataDirect Type 4 JDBC drivers for connectivity to relational sources. The Eloqua JDBC driver shares code with that proven JDBC connectivity available out-of-box, and provides a full SQL-92 interface to Eloqua data leveraging the published web service APIs.
Real-time SaaS data connectivity was introduced by DataDirect in 2006 starting with Salesforce.com; and this technology is running in production at hundreds of shops today.
Steps to configure Eloqua JDBC connectivity with Oracle Data Integrator (ODI)
Getting Started with JDBC connectivity to Eloqua:
2. Select Eloqua as the data source to configure.
3. Configure your connection information to Eloqua. My Eloqua admin setup an account and I received an e-mail from email@example.com with the connection information.
I set user name, password and company using the custom properties as follows: “Company=Progress;FailOnIncompleteData=1″.
4. Click Downloads and select DataDirect Cloud JDBC Drivers to install.
Getting Started with ODI connectivity to Eloqua:
1- Install the Oracle VirtualBox with ODI 12 from: http://www.oracle.com/technetwork/middleware/data-integrator/odi-demo-2032565.html
2- From the DataDirect Cloud JDBC client installation, copy ddcloud.jar to /home/oracle/.odi/oracledi/userlib
3- Start Oracle Data Integrator Studio 12c (restart it if already opened).
4- From Designer tab, click drop down from Models and select “New Model and Topology Objects”
5- Configure the New Model and Topology Objects as follows:
- Name: Eloqua; Technology: Generic SQL; Logical Schema: Eloqua
- For Data Server: Technology: Oracle; Name: Eloqua; User and Password are your DataDirect Cloud account credentials.
- Enter the JDBC Class and URL for DataDirect Cloud JDBC client for Eloqua.
- JDBC Driver: com.ddtek.jdbc.ddcloud.DDCloudDriver
- JDBC URL: jdbc:datadirect:ddcloud://service.datadirectcloud.com;databaseName=eloqua
- For Physical Schema: Name: Eloqua.Eloqua; Schema: Eloqua; Schema (Work Schema): Default
6- Right click new Model > Reverse Engineer. You will now see all of the Eloqua objects and fields exposed as relational tables and columns.
7- In the attributes for the Eloqua Campaign Object, change the source data types from VARCHAR to VARCHAR2. In this example, I’m loading data from CAMPAIGN.ID and CAMPAIGN.NAME.
8- Create and run an ODI mapping. In this case, I used the Eloqua CAMPAIGN object as the source; and created a target Oracle table with a subset of columns in the ODI_DEMO schema as: “CREATE TABLE ODI_DEMO.TRG_CAMPAIGN (ID NUMBER(20,0),NAME VARCHAR2(255), CREATAT DATE)”.
Note: Since the Eloqua JDBC connector provides a SQL-92 interface, we can use “LKM SQL to SQL (Built-In)” for the Loading Knowledge Module.
9- Run the mapping and verify the session ran successfully; and query the Oracle target table from SQL Developer to verify the Eloqua data was loaded.
Summary | When to integrate Eloqua data using ODI?
This tutorial demonstrated how to integrate Eloqua Marketing Cloud data into the enterprise using ODI.
It’s true that Eloqua Insight reporting capabilities work well for common requirements in marketing organizations. However, marketing data continues to prove value well beyond marketing operations; and the Oracle enterprise already runs business intelligence and integration platforms such as Oracle Business Intelligence (OBIEE), ODI, Hyperion Performance Management, Oracle Data Warehouses. This combined with talented Oracle professionals delivers sophisticated 360 degree intelligence across the business unrivaled by canned reports.
The Eloqua JDBC source enables exciting opportunities for collaboration; and IT Architects are proactively engaging marketing groups to share the new and exciting capabilities with ODI and the marketing data. I’m struggling to think of a reason not to integrate Eloqua data with ODI?