ODI — Extracting data from E-Business Suite(EBS)

For several reasons sometimes our customers need to extract data from E-Business Suite (EBS) and load into a Data Warehouse like Autonomous Data Warehouse(ADW).

You can do it easily with ODI, you just need to configure your physical connection to ADW and EBS (For example, APPS schema) and create the appropriate Logical schema.

Reverse Engineering

We can bring EBS data models in two different ways:

Customized Reverse Engineering

Import Knowledge model —“ ”RKM E-Business Suite” and choose the Application you want to bring the model, for example:

Click on the reverse engineering button and it will bring tables and views of the application “SQLAP”.

Simple reverse engineering

Here you don’t need to import a new RKM, you just need to use your EBS connection. You can use a mask to filter the object you want to import. For example:

And click Reverse Engineer.

Extracting and loading data

Create a new mapping and drag and drop one of EBS tables. That will be your source table. Now do the same for target table (this table should exist in your target, in this case ADW).

Your mapping should look like this:

Click on your target table and click on properties →Target →Integration Type change to “Increment Update”.

Go to Physical and click on the intermediary object that ODI creates to load data into target. Choose the “LKM SQL to ADWC Copy Direct”:

Configure the following options:

Change to your Logical Object Storage schema that you configured previously.

ADD_FORMAT_PROPERTIES: ‘timestampformat’ VALUE ‘YYYY-MM-DD HH24:MI:SS.FF3’, ‘dateformat’ VALUE ‘YYYY-MM-DD HH24:MI:SS’, ‘numericcharacters’ VALUE ‘.,’, ‘delimiter’ VALUE ‘X’’3B’’’, ‘trimspaces’ VALUE ‘lrtrim’, ‘characterset’ VALUE ‘UTF8’

Run the mapping and analyze the log:

If you have any doubt, reach out to me through LinkedIn or Medium.