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”
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.
Getting Started with JDBC connectivity to Eloqua:
1. Sign up and log-in to your DataDirect Cloud account to establish a real-time JDBC connection to Eloqua. This tutorial applies to any data source supported by DataDirect Cloud.
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 firstname.lastname@example.org 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:
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.
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?
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.