# Oracle Data Integrator (ODI)

This guide is based on Oracle Database 23ai and Oracle Data Integrator (ODI) 14c (14.1.2.0.0) running on Oracle Linux (64-bit).

ODI is a powerful ELT (Extract, Load, Transform) tool, mainly used for data integration and transformation. We will be using ODI to query an Oracle database and ingest the data to Apica Ascent.

## ODI Installation

Download and install ODI from this link https://www.oracle.com/middleware/technologies/data-integrator-downloads.html

## Basic ODI Concepts

Before you dive in, here are a few key terms:

* **Repository:** Where ODI metadata is stored (Master & Work repositories).
* **Topology:** Configuration for your physical and logical data servers.
* **Designer:** Where you define data models, mappings, and procedures.
* **Operator:** Monitor and manage your ETL executions.
* **Agent:** Executes the ODI scenarios or jobs.

## Creating a Repository Connection

1. Start the ODI Studio (GUI):

$ODI\_HOME/odi/studio/odi.sh

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FbDf20okaqDAnctS097HO%2Fimage.png?alt=media&#x26;token=74d9c229-dd58-435b-8fd8-fb2073fa7d9e" alt=""><figcaption></figcaption></figure>

The initial configuration it to create a Master Repository (stores global settings) and Optional Work Repository (stores project-level data like mappings and interfaces):

2. Under ODI UI, go to File -> New -> Create a New ODI Repository LogIn

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FwsxFqGU4IWEntbqntIVd%2Fimage.png?alt=media&#x26;token=249abd6c-048f-4a56-84d4-29f5ffe9878c" alt=""><figcaption></figcaption></figure>

3. Enter all the details

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FyBVmw5DW3RWX89op2vaX%2Fimage.png?alt=media&#x26;token=4ce04b4d-7c49-4297-bc82-dd7ef78ecdb9" alt=""><figcaption></figcaption></figure>

4. Click Test to validate the connection and hit OK on the dialog box

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FfMDw2qIOQwbCas9Jt6ne%2Fimage.png?alt=media&#x26;token=76dc513a-1841-4b62-9eaf-81641ec66f91" alt=""><figcaption></figcaption></figure>

5. Click OK to Save the connection
6. Once connected to Repository you can switch to Topology and view source and target Data Servers under the Physical Architecture

## Create a Work Repository

1. Navigate to Topology -> Repositories -> Work Repository
2. Right click and select New Work Repository

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FL130iqPBrOgkOrrUlKVn%2Fimage.png?alt=media&#x26;token=061622f4-a182-41cc-a287-fedd1ac131d7" alt=""><figcaption></figcaption></figure>

3. Fill in the required details at Step 1

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FAdJNLg6L37zhae8KkONd%2Fimage.png?alt=media&#x26;token=ba608a45-d8eb-4735-b3a1-b9fad6c423a4" alt=""><figcaption></figcaption></figure>

4. At Step 2 enter all the details and click Finish

&#x20;    a. Name: e.g., WORKREP1

&#x20;    b. Password: set one for this Work Repo

&#x20;    c. Type: Development

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FOgM6IzPvKp03zK0jAkvr%2Fimage.png?alt=media&#x26;token=0f207a2b-49bc-4813-99fc-8610be23d93b" alt=""><figcaption></figcaption></figure>

At this point, ODI will create the necessary tables in the same database schema (or another one if specified).

## &#x20;Reconnect with the Work Repository

1. Click on Disconnect from Repository icon
2. Reconnect to Repository after selecting Work repository WORKREP1

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FXV23jZBfmySZWsHm82Sl%2Fimage.png?alt=media&#x26;token=bdd05530-e604-4394-98da-857019614798" alt=""><figcaption></figcaption></figure>

## Groovy Script Automation for data ingestion from Oracle DB to Apica Ascent

1. Navigate to Tools -> Groovy -> New Script

{% file src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2F0z3IEgv3m2bMzgIZEByk%2FODI_Groovy_Automation_ApicaAscent.txt?alt=media&token=2f49bdf5-9a46-486f-9244-2e7fe965e01b" %}

2. A sample script is attached (above) that:

* Connects to an Oracle database
* Retrieves data from the EMPLOYEES table one row at a time
* Builds a JSON payload per row
* Sends each JSON object to Apica Ascent API endpoint using an HTTP POST
* Retries up to 3 times if there's a failure
* Logs any failures (with error reason and payload) into a local file called failed\_rows.log

3. Execute the script

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FhoEgxAg8D5FkaLs7bwLi%2Fimage.png?alt=media&#x26;token=f07ae93c-162f-4362-a71a-3c0c3ff5b951" alt=""><figcaption></figcaption></figure>

## Validate that the data is now ingested to Apica Ascent

1. Navigate to Ascent Explore page and verify the data ingested

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FACvrvznJAOPCPiNc0hvp%2Fimage.png?alt=media&#x26;token=3f0a5460-a520-414e-8e5e-aa74e77e3f41" alt=""><figcaption></figcaption></figure>

<figure><img src="https://2948796384-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LmzGprckLqwd5v6bs6m%2Fuploads%2FaSBo2lNC18gz4nZBZBk4%2Fimage.png?alt=media&#x26;token=2c5ea882-e207-4b77-8a4f-512ee49e571d" alt=""><figcaption></figcaption></figure>

**Tip:** To start polling for new rows from Oracle DB and ingest into Apica Ascent using Oracle Data Integrator (ODI) at regular intervals, you could use Change Data Capture (CDC) or Incremental Load techniques (Using a Timestamp Column). Use ODI Agent to schedule the scenario/package to run at regular intervals.
