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.
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.
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.
- 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.
How to connect to RESTful Services with ODI - tutorial
10 hours of tutorials for various ODI uses from Oracle
Configure High Availability for ODI
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.
- CLI
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.
To connect to SQL Server, it needs the JDBC driver & JRE installed on the SQL Server.
Getting started with the JDBC Driver
Download & install the Java Runtime Environment for SQL Server connection
Setting the JAVA_HOME variable in windows
Knowledge modules for SQL Server on ODI
Connecting with the JDBC Driver
You can execute SSIS packages from SQL Server stored procedures
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.
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;[email protected];Password=password12345;SecurityToken=6gaFzpiuetpyubD6Yhadk;ljadDTlNKpX
More salesforce + ODI information:here & here
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
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.
Where to install the standalone agent
Differences between the agents
Differences between agent types