Oracle Data Transforms - Setup and Configuration

This guide walks through the complete infrastructure and connectivity setup required to extract Fusion ERP data using ODT. It is written for data engineers, analytics engineers, and cloud architects who want a reliable, repeatable extraction foundation.

This document assumes:

  • Basic familiarity with Oracle Fusion ERP subject areas (helpful but not required)
  • Access to Oracle Cloud Infrastructure (OCI)
  • Permission to configure BICC in Fusion ERP

Step 1 – Provision Autonomous Data Warehouse (ADW)

1.1 Provision the ADW Instance

  1. In the OCI Console, navigate to Autonomous Databases
  2. Select the target compartment
  3. Click Create Autonomous Database

Choose:

  1. Workload Type: Data Warehouse / Lakehouse
  2. Database Version: 19c
  3. Network Access: Configure per security standards
  4. Ensure Oracle Data Transforms is enabled
  5. Save the Admin password securely

1.2 Connect to ADW Using SQL Developer

  1. Download the Wallet.zip from the ADW Console

  2. Open SQL Developer

  3. Create a new connection:

    • Connection Type: Cloud Wallet
    • Upload Wallet.zip
    • Authenticate using the Admin credentials

1.3 Create a Dedicated ODT Database User

Important: Do not use the Admin database account for ODT operations.

Execute the following steps in SQL Developer:

  • Create a dedicated database user
  • Grant required roles and privileges
  • Enable network access for BICC
  • Enable OCI Object Storage PL/SQL access

Best Practice: Keep this script in source control and treat passwords and ACL values as environment‑specific.

SQL Scripts

-- CREATE USER SQL
CREATE USER "XXMIT_FUSION_ERP" IDENTIFIED BY "Your_Tricky_Password"
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP";

-- ALTER USER QUOTA CONFIG
ALTER USER "XXMIT_FUSION_ERP" QUOTA UNLIMITED ON "DATA";

-- GRANT USER ROLES
GRANT "CONNECT" TO "XXMIT_FUSION_ERP" ;
GRANT "RESOURCE" TO "XXMIT_FUSION_ERP" ;
GRANT "ODI_DEVELOPER" TO "XXMIT_FUSION_ERP" ;
GRANT "DWROLE" TO "XXMIT_FUSION_ERP" ;
GRANT "ODIADMIN" TO "XXMIT_FUSION_ERP" ;
GRANT "DATA_TRANSFORM_USER" TO "XXMIT_FUSION_ERP" ;

-----Enable HTTP Requests
set define off;
grant execute on utl_http to XXMIT_FUSION_ERP;
grant execute on dbms_lock to XXMIT_FUSION_ERP;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'bicc.xml',
description => 'API DEV - ACL functionality',
principal => 'XXMIT_FUSION_ERP',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;
/

... (additional SQL commands) ...

commit;

--OCI Object Storage PL/SQL SQL
grant execute on dbms_cloud to XXMIT_FUSION_ERP;
... (additional grant commands)

Step 2 – OCI Object Storage

  1. Create an OCI Object Storage bucket
  2. Record the following values:
    • Bucket Name
    • Namespace
    • Bucket OCID
    • Region These values are used later for BICC and ODT configuration.

Step 3 - Fusion ERP BICC Configuration

3.1 Configure External Storage

  1. Log into Fusion ERP

  2. Navigate to BI Cloud Connector Console https:///BIACM

  3. Select Configure External Storage

  4. Choose OCI Object Storage Connection

Completed Setup

  1. Enter Setup values:
    • Name: YOURSTORAGENAME
    • Host: objectstorage..oraclecloud.com (example: objectstorage.us-ashburn-1.oraclecloud.com)
    • Tenancy OCID  - From User Profile -> Tenancy link
    • User OCID - From User Profile
    • Bucket Name – Saved from earlier configuration
    • Namespace – Saved from earlier configuration
    • Region – OCI Region

3.2 Configure API Key Authentication

  1. Export the public key from BICC

  2. In OCI Console:

    • Navigate to the OCI User
    • Upload the public key under API Keys
    • Select the .pem file and click “Add”

This establishes secure authentication between Fusion ERP and OCI Object Storage.

Step 4 – Oracle Data Transforms (ODT)

4.1 Access Oracle Data Transforms

  1. In OCI, open your ADW instance
  2. Navigate to Tool Configuration → Data Transforms
  3. Copy and open the public access URL

4.2 Configure ADW Connection

  1. Create a new Database Connection
  • Upload Wallet.zip
    • Select service name
    • Enter the dedicated ODT database user credentials
    • Test and save the connection

4.3 Configure Fusion BICC Connection

Important: This configuration must be completed in a single session.

  1. Create a new Application Connection

  2. Select Oracle BI Cloud Connector

  3. Provide:

    • Fusion ERP base URL (ending in .com/)
    • External Storage Name
    • Bucket Name
    • Namespace
    • OCI Region
    • OCI User
    • OCI Auth Token

  4. Test and save the connection

  5. Enter the configuration details shown below:

At this point, all required infrastructure and connectivity setups are complete.

What’s Next?

With infrastructure configured, you are ready to:

  • Create your first Oracle Data Transforms project
  • Select Fusion View Objects for extraction
  • Run initial extracts
  • Validate data in ADW
  • Schedule and monitor pipelines

Summary

You have completed the most complex portion of the Fusion ERP → ODT → OCI → ADW architecture. With secure connections, proper roles, and a scalable foundation in place, building and operating ERP extraction pipelines becomes significantly simpler and more reliable.