# 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

```sql
-- 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://<fusion_url>/BIACM
3. Select **Configure External Storage**

4. Choose **OCI Object Storage Connection**

Completed Setup

5. Enter Setup values:
   - Name: YOURSTORAGENAME
   - Host: objectstorage.<OCI Region>.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.