ODI — Extracting data from ERP Cloud

6 min readNov 30, 2020

An often request from our customers is to showing how to extract data from Oracle ERP Cloud (Fusion Applications). We can do it with ODI, and we have a very good article from A-TEAM where they explain step by step all what you need to extract that data.

So, why am I writing this article?

Because there is a different way to do it where ODI handles the extractions instead of using Extract configuration of Fusion Applications.

The main goal of this article is to show how to load data from ERP Cloud with ODI. I will assume that you have already created an ODI marketplace, an ADW/ATP, object Storage bucket and all policies.

What do you need?

A fusion user with the role — ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT (A-TEAM article);
Configure the external connection on Fusion;
User OCID and Tenant OCID;
API Key;
A bucket in Object Storage.

STEP 1 — Gather all parameters for connections

If you have everything that I listed above, you can skip this step and go directly to Step 2.

Tip: Save all parameters in a notepad.

User OCID and Tenant OCID

Open the Profile menu and click Tenancy (blue square) to see your tenancy OCID and do the same to have your user OCID clicking on your user name (red square).

API Key

On your ODI instance run these commands to generate you private and public key:

mkdir ~/.ociopenssl genrsa -out ~/.oci/oci_api_key.pem -aes128 2048Add passphrase (mandatory for ODI)chmod go-rwx ~/.oci/oci_api_key.pemGenerate public key:openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem

Go to the OCI console and add public key:

Go back to command line and check if the fingerprint generated is the same:

openssl rsa -pubout -outform DER -in ~/.oci/oci_api_key.pem | openssl md5 -c

The fingerprint generated must be equal the one that we have in OCI console.

Now, we need to generate the Token. Be careful and copy the token for a notepad and save it. You won’t be able to see you again.

Object Storage Namespace

STEP 2— BICC External connection (Fusion applications)

Go to https://<url fusion>/biacm/faces/setup

Fill in all the parameters and generate API Signing Key:

The API key it will download automatically to your machine. Edit the API Key and copy it.

Now, go to OCI Console and this to API key. Check the fingerprint generated is the same as you have on you BICC connection.

STEP 3— ODI CONFIGURATION

Object Storage Connection

Go to your ODI on marketplace and on topology create a new connection to Object Storage:

Fill in all parameters:

Test the connection.

Now let’s create the physical schema. Right click on the connection previously created:

Choose your bucket and enter a work schema.

BICC Connection

The object storage connection is created, now let’s create the Oracle BI Cloud Connector (BICC) connection.

Your BICC URL, the user name with the role that I mentioned in the beginning of this article and choose the Object Storage connection that you configured previously. In External Storage Name, introduce the same connection name that you define in the step 2.

Let’s create the physical schema:

Here you need to choose with schema you want to extract data. You can create several physical schemas.

Logical Schema

We need to create a logical schema for physical connections that we created before. In this case I’m choosing Global context, but you can choose the one you prefer.

Logical Schema for Object Storage

Logical Schema for BICC:

Reverse Engineer

Let’s create the model to bring all the tables that we need from ERP Cloud. On Designer -> Models -> New Model:

Give a name to your model:

You can use a mask to filter tables or remove characters from the data set:

If you don’t have this RKM, you can add it from Designer -> Global Objects → Global Knowledge Modules -> Reverse-Engineering (RKM) -> Right click -> Import Knowledge Modules and search for this knowledge module name:

Click on Reverse Engineer and it will start a job to bring all the View Object (VOs) for that schema:

You can look at the log:

Now your model should look like this one:

Create target on ADW

An easy way to create the target table that does not exist in ADW is to create a New Diagram (On ADW model) and drag and drop the table that you want to create in your model:

Give a name to your new table:

Generate the mapping

You can generate easily a mapping from ERP Cloud to ADW using this ODI feature. Right click on your target table and “Generate Mappings IN”:

It will map automatically your source and your target.

On physical tab change the SUBMIT_BICC_JOB to true and in case you don’t want to create a credential you should change this option to True as well.

About SUBMIT_BICC_JOB, as you can see, we have an option LAST_LOAD_DATE we should fill in this option to have incremental loads and not full loads. With configuration you are always loading everything that you have on your source. I created this article to show how use a variable to pick always the last date loaded.