Data Management,

Integrating Oracle Data Integrator (ODI) On-Premises with Cloud Services

January 24, 2017 | 16 minute read

Text Size 100%:

－＋

Disclaimer

The content of this article pertains to on premise technology and was last validated in January 2017. It is still made available for its educational value.

Introduction

This article presents an overview of how to integrate Oracle Data Integrator (ODI) on-premises with cloud services. Cloud computing is now a service or a utility in high demand. Enterprises have a mix of on-premises data sources and cloud services. Oracle Data Integrator (ODI) on-premises can enable the integration of both on-promises data sources and cloud services.

This article discusses how to integrate ODI on-premises with three types of cloud services: Software as a Service (SaaS), Platform as a Service (PaaS), and Infrastructure as a Service (IaaS). The first part of this article discusses the required components in order to integrate ODI on-premises with SaaS applications. The ODI on-premises integration with PaaS is illustrated with three Oracle PaaS products: Oracle Database Cloud Service (DBCS), Oracle Exadata as a Service (ExaCS), and Oracle Business Intelligence Cloud Service (BICS). The last part of this article discusses the integration of ODI on-premises with Oracle Storage Cloud Service (SCS), an IaaS product.

Integrating Oracle Data Integrator (ODI) On-Premises with Cloud Services

This article defines ODI on-premises as having ODI installed and running on computers on the premises of a company. More specifically, ODI on-premises refers to having the ODI agent and the ODI repository installed on computers and databases on the premises of a company. Additionally, on-premises data sources are data servers running on computers on the premises of a company. Figure 1 below shows the integration of ODI on-premises with several cloud services:

Figure 1 - ODI On-Premises Integration with Cloud Services

Figure 1, above, illustrates the ODI on-premises integration with both Oracle and non-Oracle cloud services. ODI on-premises can integrate with Oracle cloud services such as Database Cloud Service, Business Intelligence Cloud Service, Oracle Storage Cloud Service, and Oracle Sales Cloud, among others. Likewise, ODI on-premises can integrate with non-Oracle cloud services such as Salesforce, Google Analytics, and Success Factors.

The Oracle cloud services illustrated above are part of the Oracle Public Cloud, which can be organized into three categories: Oracle Software as a Service (SaaS), Oracle Platform as a Service (PaaS), and Oracle Infrastructure as a Service (IaaS). Oracle SaaS includes a variety of software services that can be categorized in several areas: Supply Chain, Human Resources, Analytics, Social, CRM, Financials, and many others. Oracle PaaS includes database and middleware, as well as development, management, security and integration. Oracle IaaS offers a set of core infrastructure capabilities like elastic compute and storage to provide customers the ability to run any workload in the cloud.

The next sections of this article provide an overview of how to integrate ODI on-premises with these three types of cloud services.

ODI On-Premises Integration with Software as a Service (SaaS)

ODI on-premises can integrate with Software as a Service (SaaS) applications. Figure 2, below, below shows a list of both Oracle SaaS applications and non-Oracle SaaS applications:

Figure 2 - ODI On-Premises with SaaS Applications

In order to integrate ODI on-premises with SaaS applications, at a minimum, three components are required: a web service API or JDBC driver, an ODI Technology, and a set of ODI Knowledge Modules (KMs).

Today, most SaaS applications provide web service APIs to extract data from the SaaS underlying database. These web service APIs are based on one of two technologies: RESTful or SOAP web services. ODI supports both RESTful and SOAP web services; thus, ODI users can extract data from any SaaS applications that provide RESTful or SOAP web services. When users use a web service to extract data from a SaaS application, the web service returns data in XML or JSON format. These datasets can be consumed and integrated in ODI with other data-stores such as SQL tables, big data Hive tables, or message queues. To use a web service, a connection and an API method are required in order to access the web service and extract data.

Alternatively, ODI users may use a JDBC driver to establish a connection, between ODI and the SaaS application, and extract data from the SaaS application. The JDBC driver is a wrapper that uses the underlying web service APIs to facilitate the connection and consumption of data between the client (ODI) and the web service (SaaS application). Some of these drivers are supplied by third-party vendors such as Progress Data Direct or by Oracle. These drivers can connect to Oracle SaaS applications such as Oracle Marketing Cloud, Oracle Service Cloud, and Oracle Sales Cloud. In addition to Oracle SaaS applications, JDBC drivers, such as those offered by Progress Data Direct, can connect to other non-Oracle SaaS applications such as Salesforce Cloud, Microsoft SQL Azure, and Google Analytics. For a list of cloud data sources supported by Progress Data Direct, go to “Progress Data Direct Connectors.” Out-of-the-box, ODI provides JDBC drivers for Salesforce.com and Oracle Business Intelligence Cloud Service. For a complete list of JDBC drivers and ODI knowledge modules available for SaaS applications, see the following Oracle article: “Need to Integrate your Cloud Applications with On-Premise Systems… What about ODI?”

The second component that is required to establish a connection between ODI on-premises and a SaaS application is the ODI Technology. The ODI Technology is a connection object in the ODI Topology that uses the JDBC driver or the web service to enable connectivity between ODI and the SaaS application. If the ODI technology is based on JDBC, the ODI technology is already configured to correctly map the data-types of the JDBC driver with those data-types found in the ODI technology. If the ODI technology is based on RESTful or SOAP, the ODI technology provides a form to define the web service connection and the methods that the user needs to invoke in order to extract data from the SaaS application.

The third component that is required to establish a connection between ODI on-premises and a SaaS application is an ODI Knowledge Module (KM). ODI Knowledge Modules are required for two purposes: to reverse-engineer the attributes or objects found in the SaaS application, and to perform the integration task of pulling and sending data - respectively - from and to the SaaS application. Bristlecone, an Oracle partner, offers the following types of ODI knowledge modules for SaaS applications: Reverse-Engineering Knowledge Modules (RKMs), Loading Knowledge Modules (LKMs), and Integration Knowledge Modules (IKMs). For a list of knowledge modules and SaaS applications supported by Bristlecone, go to “Bristlecone ODI Cloud Integration Pack.”

In summary, users can use web services (RESTful or SOAP) or a JDBC driver (supplied by Oracle or by a third-party vendor) to access a SaaS application. If a web service is used to access a SaaS application, the existing ODI Technologies (RESTful Service or SOAP Web Service) can be used to configure the web service call. If a JDBC strategy is used to access a SaaS application, then an ODI Technology of type SQL should be used to configure the JDBC connection. For more information on how to configure a ODI Technology that uses a JDBC connection for a SaaS application, go to "A Universal Cloud Applications Adapter for ODI." An ODI webcast is also available, which shows how to configure a JDBC driver and an universal cloud application adapter for SaaS applications: "Oracle Data Integrator Special Topic: Salesforce.com & Universal Cloud."

ODI On-Premises Integration with Platform as a Service (PaaS)

Oracle Platform as a Service (PaaS) includes a variety of cloud products. These cloud products are divided into several categories. Two of these categories include Data Management, and Business Analytics. Data Management includes products such as Database Cloud Service (DBCS), Big Data Cloud Service (BDCS), MySQL, and Big Data – Compute Edition (BDCS-CE), among others. Business Analytics includes cloud products such as Business Intelligence (BICS), Big Data Preparation (BDP), and Big Data Discovery (BDD).

The following sections focus on how to integrate ODI on-premises with three Oracle PaaS products: Database Cloud Service (DBCS), Exadata Cloud Service (ExaCS), and Business Intelligence Cloud Service (BICS). For a complete list of Oracle PaaS products and categories, visit the Oracle PaaS website.

ODI On-Premises Integration with Oracle Database Cloud Service (DBCS)

Oracle Database Cloud Service (DBCS) offers database features and database capabilities. DBCS offers, among others, the following three products: Database as a Service (DBaaS), Database Schema Service (DBSS), and Database Exadata Cloud Service (ExaCS).

Each DBCS product offers a predefined set of features and options. For instance, DBaaS offers a dedicated virtual machine for running the Oracle database instance, full SQL*Net access, and full administrative OS and SYSDBA access. Both DBaaS and DBSS offer RESTful web services, which enable web applications to access data in the database service. ExaCS offers the Oracle database on Exadata, Oracle Enterprise Manager, Exadata performance optimizations, and database rack options. For additional information on Oracle Database Cloud Service offerings, go to “Oracle Database Cloud Service Offerings.”

ODI on-premises can integrate with these three DBCS products. Figure 3, below, shows how ODI on-premises can integrate with DBaaS. Three methods are illustrated:

Figure 3 - ODI On-Premises to Database as a Service (DBaaS)

The first method uses a JDBC driver to access the Oracle database located in the Oracle Public Cloud. Using the ODI Topology, a JDBC connection is configured under the Oracle technology. The steps to configure this connection are similar to configuring an on-premises Oracle database. The JDBC cloud connection can be secured via a Secure Socket Shell (SSH) protocol; thus, ODI can send data via a secure channel to the Oracle database in the Oracle Public Cloud.

The second method uses the Oracle Datapump technology. ODI on-premises can use this technology to extract data from either an on-premises Oracle database or an Oracle Database as a Service. Using a Secure Copy Protocol (SCP) tool, datapump files can be copied from an on-premises Oracle database into an Oracle Database as a Service, or vice versa. Datapump files can be uploaded into an Oracle database – on-premises or as a service – by executing a mapping from ODI on-premises. Datapump technology is an Oracle technology; thus, when using datapump to extract data from an on-premises Oracle database, the database technology must be Oracle. For additional information on how to use Oracle Datapump with ODI, go to “Using Oracle Data Pump in Oracle Data Integrator (ODI).”

The third method uses text files. ODI on-premises can extract data from either an on-premises SQL database or an Oracle Database as a Service, and convert data into text files. Using a Secure Copy Protocol (SCP) tool, text files can be copied from an on-premises SQL database into an Oracle Database as a Service, or vice versa. Text files can be uploaded into a SQL database – on-premises or as a service – by executing a mapping from ODI on-premises. If the target database is Oracle, ODI on-premises can use the Oracle External Table technology to load the text files.

An example of how to copy text files from an on-premises data server into an Oracle Database as a Service can be found in the following blog: “ODI 12c and DBCS in the Oracle Public Cloud.” Also, this blog discusses how to configure a secured JDBC connection using SSH, and how to load the text files into the Oracle Database as a Service using Oracle external tables.

Oracle database tools such as Oracle SQL*Loader can also be used to load text files from an on-premises data server into an Oracle Database as a Service. However, a secured SSH connection must be established prior invoking these Oracle database tools. An example of how to create a secured SSH connection and how to use Oracle SQL*Loader to load text files from an on-premises data server into an Oracle Database as a Service can be found in the following blog: “Tips for ODI in the Cloud: ODI On-Premise with DBCS.”

For additional information on how to work with text files in ODI, go to “Working with Files in Oracle Data Integrator (ODI).”

ODI On-Premises Integration with Oracle Database Exadata Cloud Service (ExaCS)

The Oracle Database Exadata Cloud Service (ExaCS) provides the highest-performing and most-available platform for running Oracle databases in the cloud. This cloud service, based on the Oracle Exadata Database Machine, includes customized combinations of hardware, software, and storage that are highly tuned for maximum performance.

ODI on-premises integration with DBaaS can be extended to ExaCS as well. ODI on-premises can access and transform data in ExaCS using three methods: JDBC, datapump (if both the source and the target data servers are Oracle databases), and text files. Secured connections can be accomplished via a Secure Socket Shell (SSH) protocol, and files can be transferred from the on-premises source data server into ExaCS – and vice versa – using a Secure Copy Protocol (SCP) tool.

Figure 4, below, shows how ODI on-premises can upload and transform data in ExaCS:

Figure 4 - ODI On-Premises to Exadata Cloud Service

Figure 4, above, also illustrates a fourth method for accessing and transforming data in the cloud: DBLINK. This fourth method uses Oracle database links to orchestrate data transfers between DBCS and ExaCS. For instance, a database link can be created in ExaCS to access data from DBCS. In ODI on-premises, this database link can be used in an ODI mapping to select data from the DBCS and insert it into ExaCS.

Additionally, Oracle database links can be used in conjunction with Oracle file transfer utilities to transfer data between Oracle database servers in the cloud. Thus, ODI on-premises can also be used to orchestrate file transfers – such as datapump files or text files – between DBCS and ExaCS.

For additional information on how to load data into an Exadata Cloud Service instance, go to “Loading Data into the Oracle Database in an Exadata Cloud Service Instance.” For additional information on how to configure network access to an Exadata Cloud Service instance, go to “Managing Network Access to an Exadata Cloud Service Instance.”

ODI On-Premises Integration with Oracle Business Intelligence Cloud Service (BICS)

Oracle Business Intelligence Cloud Service (BICS) offers agile analytics for customers interested on analyzing data from a variety of sources, including on-premises and other services in the cloud.

In order to store and manage the data that users analyze in BICS, a database cloud service is needed. BICS can integrate with Oracle Database Cloud using one of two options: Database Schema Service (DBSS) or Database as a Service (DBaaS). BICS comes integrated with DBSS, so there is no additional configuration required if users want to use this database schema service.

When using BICS with DBSS, data from on-premises sources can be loaded into BICS using the Oracle BI Cloud Service (BICS) REST API. BICS REST API is based on the DBSS RESTful web services. ODI on-premises can load data from on-premises data sources into BICS using the BICS REST API. This strategy is illustrated on Figure 5, below, method 1:

Figure 5 - ODI On-Premises to BI Cloud Service

ODI users and developers can use BICS REST API to programmatically create, manage, and load schemas, tables, and data into BICS. ODI Knowledge Modules can be used to invoke the BICS REST API and mappings can be designed to load data from on-premises data sources into BICS. An example of how to use ODI Knowledge Modules to invoke the BICS REST API can be found in the following article: “ODI Knowledge Modules for BI Cloud Service (BICS).”

ODI 12c supports a new technology to directly integrate with BICS: Oracle BI Cloud Service. This new ODI technology uses the BICS RESTful APIs to load data into BICS environments that are based on DBSS. An example of how to use this new ODI technology with BICS can be found in the following article: “Using Oracle Data Integrator (ODI) to Load BI Cloud Service (BICS).”

When BICS is integrated with the Database as a Service (DBaaS), data from on-premises sources can be loaded directly into DBaaS using various methods such as JDBC, datapump, and text files. Thus, the BICS REST API is not required. This strategy is also illustrated on Figure 5, above, methods 2, 3, and 4.

The BICS REST API does not include methods for extracting data from the underlying BICS database tables. However, if BICS has been integrated with DBaaS, ODI on-premises can select data from DBaaS and export it as datapump files or text files. ODI on-premises can transfer these files from the cloud and load them into an on-premises data server using a Secure Copy Protocol (SCP) tool or using the Oracle database file transfer tools.

The BICS REST API does not include methods for extracting data from BICS reports. Alternatively, Oracle Application Express (APEX) can be used to create RESTful web services to extract data from BICS reports. An example of how to use APEX to extract data from BICS reports can be found in the following Oracle article: “Extracting Data from BICS via APEX RESTful Web Services.“

ODI On-Premises Integration with Infrastructure as a Service (IaaS)

Oracle Infrastructure as a Service (IaaS) offers three products: Oracle Compute Cloud Service, Oracle Network Cloud Service, and Oracle Storage Cloud Service. The Oracle Compute Cloud Service provides virtual compute environments, lifecycle management, dynamic firewalls, and secure access. The Oracle Network Cloud Service provides connectivity services – such as VPN and FastConnect – between on-premises networks and the Oracle Public Cloud. The Oracle Storage Cloud Service (SCS) provides storage for files and unstructured data.

The following section illustrates how to integrate ODI on-premises with Oracle Network Cloud Service (NCS), and Oracle Storage Cloud Service (SCS).

ODI On-Premise Integration with Oracle Network Cloud Service (NCS)

Oracle cloud services such as DBaaS and ExaCS offer service-specific security controls. For instance, users can access DBaaS via SSH – supported by ODI – and establish secured connections between the user’s on-premises data servers and DBaaS. At the database level, Oracle offers additional security features such as encryption of network data, user authentication, user accounts, user privileges and roles, among others.

For customers looking to secure the network connectivity between their on-premises data centers and the Oracle Public Cloud, the Oracle Network Cloud Service (NCS) offers this level of security. For instance, NCS offers Virtual Private Network (VPN) connectivity between the user’s on-premises network and the Oracle Public Cloud. The NCS VPN connectivity is based on the Internet Protocol Security (IPSec) technology, which creates a virtual tunnel between the user’s on-premises data center and Oracle’s cloud. IPSec is a suite of protocols designed to authenticate and encrypt all IP traffic between two locations.

When the NCS VPN option is configured between the user’s on-premises data center and the Oracle Public Cloud, users can use ODI to perform data upload operations– over the IPSec protocol – from the user’s on-premises data servers to Oracle cloud services. Furthermore, the NCS VPN option enables ODI users to perform data download operations from Oracle cloud services to the user’s on-premises data servers. Figure 6, below, illustrates an example:

Figure 6 - ODI On-Premises Integration with Oracle Network Cloud Service

Figure 6, above, shows four secured data upload strategies that can be integrated with ODI on-premises. The first method shows the NCS VPN connectivity between the on-premises data server and DBaaS. The NCS VPN option creates a secure tunnel over IPSec to send and receive data between the on-premises data sever and DBaaS. The second method uses SSH to securely load data via JDBC from the on-premise data server to DBaaS. Notice that since the NCS VPN option is used, data can also be loaded from DBaaS to the on-premises data server via JDBC. The third and fourth methods both use secure copy protocol (SCP) to move files from the on-premises data server to DBaaS, and vice versa. Oracle Network Cloud Service includes other network cloud offerings such as VPN for Dedicated Compute, and FastConnect. For additional information on Oracle Network Cloud offerings, go to “Oracle Network Cloud Offerings.”

ODI On-Premises Integration with Oracle Storage Cloud Service (SCS)

The Oracle Storage Cloud Service can be used by applications that require long-term data retention or as a staging area for data integration tasks in the cloud. Oracle cloud services - such as BICS, BDCS-CE, and BDP – can use SCS as a staging area for data consumption.

Users can programmatically store and retrieve content from SCS using the SCS REST API. Additionally, SCS offers Java libraries to wrap the SCS REST API. In ODI, tools and packages can be designed to invoke the SCS REST API, and ODI can upload files from an on-premises data server into SCS. Likewise, using the SCS REST API, ODI on-premises can download files from SCS into an on-premises data sever. This strategy is illustrated on Figure 7, below, methods 1, and 2:

Figure 7 - ODI on Premises to Storage Cloud Service

Examples of how to use the ODI Open Tools framework to invoke the SCS REST API can be found in the following Oracle article: “ODI Integration with Oracle Storage Cloud Service.” Rittman Mead, an Oracle partner in the data integration space, has an example on how use ODI Open Tools to copy files from an on-premises data server into SCS: “Oracle Data Integrator to Load Oracle BICS and Oracle Storage Cloud Service.” This article also discusses how to load data into Business Intelligence Cloud Service (BICS) using the BICS REST API. For additional information on how to develop and use ODI Open Tools, go to “Oracle Data Integrator Tools Reference.”

ODI 12c supports RESTful services out-of-the-box as well. A RESTful Service connectivity, resource URL, methods and parameters can be configured in Topology configurations like any other data source connectivity. For additional information on ODI RESTful support, go to “New and Changed Features for Release 12.2.1.2.6.”

Conclusion

Cloud computing is now a service or utility in high demand. Enterprises have a mix of on-premises data centers and cloud computing services. Oracle Data Integrator (ODI) on-premises can enable the integration of both on-promises data sources and cloud services.

This article presented an overview on how to integrate ODI with on-promises data sources and cloud services. The article covered ODI on-premises integration with the following cloud services: SaaS, PaaS, and IaaS.

For more Oracle Data Integrator best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-team Chronicles for Oracle Data Integrator (ODI).”