S
Sumit Sarkar
Guest
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:
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 support@eloqua.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- Navigate to Topology > Oracle > New Data Server. In this example we’ve decided to reuse the existing Oracle technology for Eloqua instead of creating a new one.
5- Enter Name “Eloqua” and your DataDirect Cloud account for the user credentials.
6- Click on the JDBC section for the new data server and 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
7- Right click on Eloqua to create a new physical schema and select “Eloqua” as the Schema and Work Schema. For purposes of this test, we’re only reading from Eloqua.
8- Navigate to the Logical Architecture Pane and right click Oracle > New Logical Schema. Enter a Name and select “Eloqua.Eloqua” as a Physical Schema.
8- Navigate to the Designer Tab and create new Model named “Eloqua” and enter Name: Eloqua; Code: ELOQUA; Technology: Oracle; Logical Schema: Eloqua
9- Right click new Model > Reverse Engineer. You will now see all of the Eloqua objects and fields exposed as relational tables and columns.
http://blogs.progress.com/data-connections/files/2014/07/odi12eloqua11.png
10- 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.
11- 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.
12- 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?
http://blogs.progress.com/data-connections/files/2014/07/odi12eloqua6.png
Continue reading...
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:
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.
3. Configure your connection information to Eloqua. My Eloqua admin setup an account and I received an e-mail from support@eloqua.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- Navigate to Topology > Oracle > New Data Server. In this example we’ve decided to reuse the existing Oracle technology for Eloqua instead of creating a new one.
5- Enter Name “Eloqua” and your DataDirect Cloud account for the user credentials.
6- Click on the JDBC section for the new data server and 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
7- Right click on Eloqua to create a new physical schema and select “Eloqua” as the Schema and Work Schema. For purposes of this test, we’re only reading from Eloqua.
8- Navigate to the Logical Architecture Pane and right click Oracle > New Logical Schema. Enter a Name and select “Eloqua.Eloqua” as a Physical Schema.
8- Navigate to the Designer Tab and create new Model named “Eloqua” and enter Name: Eloqua; Code: ELOQUA; Technology: Oracle; Logical Schema: Eloqua
9- Right click new Model > Reverse Engineer. You will now see all of the Eloqua objects and fields exposed as relational tables and columns.
http://blogs.progress.com/data-connections/files/2014/07/odi12eloqua11.png
10- 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.
11- 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.
12- 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?
http://blogs.progress.com/data-connections/files/2014/07/odi12eloqua6.png
Continue reading...