Oracle_Data_Integrator

ODI

by Alex MacDonald

This guide will show you the resources to learn the basics of using ODI, how to extract data from Oracle SaaS to Autonomous Database, and other information.

Here is the main ODI documentation. Everything is always in the documentation. Coming from 11g? See what’s new in ODI 12c. Click here to see ODI’s product page on Oracle.com for webcasts, data sheets, and more.

Getting started

You will need ssh & API keys to connect to your compute node and object storage. You can get both of those easily from here. Remember to run chmod 400 on your ssh key.

You can find ODI on the OCI marketplace at this link or you can start local development by spinning up ODI Studio on VirtualBox. If you are using the marketplace image, you will need VNCViewer or TigerVNC in order to see the Linux GUI.

How to install ODI from the marketplace - blog with more pictures

How to install ODI from the marketplace - official documentation

If you’re starting from scratch, follow along with either of the above two links to get ODI running on OCI, here is a PDF version of the above links. You use will use Resource Manager on OCI to manage your deployments with terraform.

While the marketplace image comes with an embedded MySQL database which can be used for your ODI repository, alternatively, you can follow this guide to use an Autonomous Database as your Repository instead.

Flat file to database - beginner

How to use ODI lab - advanced (note: use embedded MySQL Repository to have tables in this lab)

The above workshops are the best places to get started using ODI before proceeding with more complex use cases below.

What does you by ODI uses ELT instead of ETL? What is a knowledge module? How can ODI work with GoldenGate? And more questions can be answered in this overview of ODI on Youtube.

Getting data out of Fusion Applications with ODI

Business Intelligence Cloud Connector —> OCI Object Storage —> Autonomous Database

In general, for users seeking to extract high volumes (or incremental) of data from Fusion, they can use BICC to send extracts to Oracle object storage. ODI can then read the CSVs from there and write them to the target database of your choice. Details on this usage parttern are explained below. However, data can also be extracted with ODI via BI Publisher reports.

Complete guide on the ODI marketplace + BICC + Object Storage + Autonomous Database

This blog post starts from scratch, spinning up ODI, then it proceeds to enable ODI to integrate BICC extracts from Fusion applications to the Autonomous Data Warehouse. After completing this walkthrough, you can start setting up ODI scenarios & plans which will automate your data pipeline. If you have problems with this walk-through or want to know more, consult the documentation below.

Manipulating Data from Object Storage to Autonomous Database using ODI

Connecting to Object Storage with ODI + screenshots

These two blog posts focus on connecting object storage and ADW/ATP to ODI. To connect to object storage in ODI, the PEM key you use must have a passphrase. In order to work with the compressed BICC extracts in object storage, you must first make an object storage topology and then the BICC toplology. If you have problems with loading your data into ADW, please consult the load operation queries, in this link. You may need to reconfigure the target tables in ADW to match the columns in your BICC extract if something like your column size is different from the default.

Using Object Storage with ODI

Using ADW with ODI

Using BICC with ODI

These three links are the main documentation pages for using Object Storage, ADW, and BICC with ODI. While you can work with BICC extracts if they are being written to object storage, you can also work with BICC from the UCM Server on Fusion applications.

More Use cases & Administration

How to connect to RESTful Services with ODI - tutorial

10 hours of tutorials for various ODI uses from Oracle

Configure High Availability for ODI

Cost control - included with start_stop.py python script

You can limit the cost of your ODI setup by starting and stopping the compute node on which it runs (you could also do the same with the Autonomous Database if using it as a repository). In order to start and stop, you have some options.

Explanation of code

You will need to update line 12 with the location of your private API key. Update lines 17, 19, 21 with the relevant credentials.

This code is made for the ashburn region, you will need to update lines 115, 129, 144, 159, and 174 with the new endpoint, or add a way to pass that as an inpute. Leave the code between lines 32 and 107 alone, it handles authentication with the OCI API.

To get started with the compute API, visit this link. For specific information on starting and stopping, visit here

This repository shows how to start & stop a different OCI resource via the API.

The CLI command to start and stop your instance can be found here. You could also have your code invoke a bash/CLI scipt like here on lines 155 and 204.

Connecting to SQL Server

To connect to SQL Server, it needs the JDBC driver & JRE installed on the SQL Server.

Reading files from remote servers

Click here to read about the standalone Agent that comes with the ODI Marketplace.

To read a file from a remote server, you must install an agent on that server so it can access the files. this links describes on how & why.

Note: Using an agent in this manner is not possible solely with the ODI Marketplace - in place of the agent you can create a VPN connection, whitelist the IP address of the compute node ODI is running on, and/or have FastConnect.

Connecting to salesforce

Main salesforce/ODI doumentation

Salesforce connects via JDBC as well, this is the syntax for salesforce’s JDBC connection under physical topology:

jdbc.weblogic:sforce//login.salesforce.com;User=email@email.com;Password=password12345;SecurityToken=6gaFzpiuetpyubD6Yhadk;ljadDTlNKpX

More salesforce + ODI information:here & here

More Oracle Cloud

For moving smaller quantities of data or enabling app-to-app integration, consider Oracle Integration.

Click here to start developing cloud-native applications on OCI.

See how you can load a CSV to ADB with DBMS_CLOUD

ODI Misc

File mapping project - youtube

How to recover the supervisor password

Create an API key for OCI user

The above is requisite for connecting to object storage with ODI, you will need to create a passphrase with your key.

ssh-keygen -t rsa -b 2048

This is a bash command that can be used to generate ssh keys for the compute node where the marketplace image runs. If you give them to another user to connect, that user needs to run ‘chmod 400’ on that key after receiving it.