Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Create Fusion SaaS Data Replication into Oracle Autonomous Data Warehouse using OCI Data Integration

Introduction

Oracle Cloud Infrastructure Data Integration is a fully managed, multi-tenant service that helps data engineers and developers with data movement and data loading tasks. Powered by Spark extract, transform, and load (ETL) or extract, load, and transform (ELT) processes, a large volume of data can be ingested from a variety of data assets; cleansed; transformed and reshaped; and efficiently loaded to Oracle Cloud Infrastructure (OCI) target data assets.

There are two tools that can be used to export bulk data from Oracle Fusion Cloud ERP (Oracle Cloud ERP): Oracle Business Intelligence Publisher (BI Publisher) and Oracle Business Intelligence Cloud Connector (BICC).

• Business Intelligence Publisher is essentially a reporting tool. It is not recommended to use BI Publisher reports for general data extraction purposes. For extracting medium to high volumes of data, BICC is the preferred tool.

• Oracle Business Intelligence Cloud Connector is an Oracle tool used to ingest Oracle Fusion Cloud ERP data to an external storage for BI consumption. It is available as a part of Oracle Cloud Applications subscription. BICC comes with numerous features, including ingesting data to storage, scheduling, and automating extracts.

Objective

• In this tutorial, we will show you how to create a connection to your Fusion SaaS environment through BICC into Oracle Autonomous Data Warehouse (ADW) using OCI Data Integration service. OCI Data Integration service is a cloud native serverless ETL and ELT service for moving, integrating, and transforming data. It is a fully managed service with easy-to-use graphic design and visualize data flow prior to loading.

Prerequisites

• Access to an OCI tenancy.

• Provisioned ADW and OCI Object Storage.

Task 1: Provision Network Connectivity

To set up OCI Data Integration, we need to provision the network components first.

While creating network components, you have two options depending on your use case to spin up your data integration workspace in.

1. Log in to the OCI Console, navigate to Networking and Virtual Cloud Networks.

   

2. Select the compartment where you want your resources and click Start VCN Wizard.

   

3. Select Create VCN with Internet Connectivity. This will spin up all related resources required for your VCN to connect to the internet.

   

   

   This VCN setup will automatically create public and private subnet for you with internet gateway for connectivity to the outside internet. Your VCN will look like:

   

   The route tables for public and private subnets are created automatically. If you are creating your resources in the private subnet, make sure that a route to All Services in Oracle Services Network is added in your route table.

   

If you have resources in private subnet and have concerns about security, see Understanding VCN Configuration for Oracle Cloud Infrastructure (OCI) Data Integration.

Task 2: Create OCI Object Storage Bucket

1. Go to the OCI Console, navigate to Storage, Buckets and click Create Bucket.

   

   

   Your bucket is created.

   

Task 3: Connect with Autonomous Database

Assuming you have already provisioned ADW, from the OCI Console,

1. Go to the OCI Console, navigate to Oracle Databases and Autonomous Data Warehouse.

   

2. In Compartment, select your ADW.

   

3. Click Database connection to download your wallet.

   

   

4. Click Download and save it on your local system.

   

Task 4: Provision OCI Data Integration

To create data integration you need to have certain policies.

1. Go to the OCI Console, navigate to Identity & Security and Policies.

   

2. In the root compartment, click Create Policy, select Show manual editor and add the following policies.

   • For creating data integration workspace and view users:

     allow group <group-name> to manage dis-workspaces in compartment <compartment-name>
     
     allow group <group-name> to manage dis-work-requests in compartment <compartment-name>
     
     allow service dataintegration to inspect users in tenancy
     

   • Enable private access in workspace:

     allow service dataintegration to use virtual-network-family in compartment <compartment-name>
     
     allow group <group-name> to manage virtual-network-family in compartment <compartment-name>
     

   • For OCI Object Storage required for autonomous database and Oracle Fusion Applications:

     allow group <group-name> to use object-family in compartment <compartment-name>
     
     allow any-user to use buckets in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>’}
     
     allow any-user to manage objects in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>’}
     
     allow any-user to manage buckets in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>', request.permission = 'PAR_MANAGE'}
     

   • For autonomous database:

     allow group <group-name> to read autonomous-database-family in compartment <compartment-name>
     

   

3. Click Create. Once all the policies are in place, go to the Data Integration service to create workspace.

4. Go to the OCI Console, navigate to Analytics & AI and Data Integration.

   

5. Select Workspaces, select your compartment and select Create Workspace.

   

6. Enter the workspace Name and select Enable private Network as Network selection.

   

7. Click Create.

Task 5: Extract Data from Oracle Fusion Cloud ERP into ADW

Task 5.1: Connect OCI Bucket to BICC

We will now create a connection in BICC, which is the component of Oracle Fusion Applications, that allow you to interact and extract data from different applications.

We will connect BICC to external storage which is OCI bucket in our case. OCI bucket acts as a staging area where all files extracted from BICC will be stored before getting extracted into ADW through OCI Data Integration.

1. Enter the URL for your BICC, which is in the following format: https://fa-eqgj-dev11-saasfademo1.ds-fa.oraclepdemos.com/biacm.

   

   You will be directed to the BICC Console.

   

2. From the right panel, select Configure External Storage.

   

3. Click OCI Object Storage Connection and + to add new OCI Object Storage connection and you will be directed to the new connection console.

   

   

4. In the OCI Parameters section, enter the following information.

   • Name: Enter a name for your storage connection.

   • Host: Host will be one of the following depending on your region.

     • objectstorage.ap-mumbai-1.oraclecloud.com

     • objectstorage.ap-seoul-1.oraclecloud.com

     • objectstorage.ap-sydney-1.oraclecloud.com

     • objectstorage.ap-tokyo-1.oraclecloud.com

     • objectstorage.ca-toronto-1.oraclecloud.com

     • objectstorage.eu-frankfurt-1.oraclecloud.com

     • objectstorage.eu-zurich-1.oraclecloud.com

     • objectstorage.sa-saopaulo-1.oraclecloud.com

     • objectstorage.uk-london-1.oraclecloud.com

     • objectstorage.us-ashburn-1.oraclecloud.com

     • objectstorage.us-phoenix-1.oraclecloud.com

     Note: To get other required parameters, you need to go to the OCI Console.

   • Tenancy OCID: Enter tenancy OCID.

     

     

   • Namespace: Enter namespace.

     

   • User OCID: Go to the OCI Console, click your user name and copy the OCID.

     

     

   • Bucket name: Enter OCI Object Storage bucket name created in the Prerequisite section.

   

   

5. Click Generate API Signing Key, this will generate the Fingerprint and Export Public Key to export the public key in your local system.

   

   

6. Go to the OCI Console. In the User Details page, click API Keys, Add API Key and paste the oci_api_key file.

   

   

7. Click Choose Public Key File and select the file downloaded in the last step.

   

   This will generate the Fingerprint and add that fingerprint to the list of API keys to your user. You will see that the fingerprint created is the same as the one generated in the BICC Console.

   

8. Go to the BICC Console and select Test Connection.

   

9. It will give a Successful connection message. Click Ok and save the connection.

   

Once the connection to the OCI Object Storage bucket has been established from the BICC Console, we will create the connections in OCI Data Integration service.

Task 5.2: Create Connections in OCI Data Integration

1. Go to the OCI Console, navigate to Analytics & AI and Data Integration.

   

2. Click Workspaces, select the compartment and data integration workspace created in Task 4.

   

3. In the OCI Data Integration Console, create the data assets.

   In our data asset, our source system is the BICC connection and target connection is ADW. To create an ADW data asset, the prerequisite is to create an OCI Object Storage Data Asset as well. So, we will create 3 data assets.

   • BICC
   • OCI Object Storage
   • ADW

   Click Create data asset.

   

4. From the list of data sources, select the Fusion Apps – BICC connector.

   

   1. In the General Information section, enter the following information.

      • Name: Enter a name (for example, ERP_CloudConn).

      • Type: Select Oracle Fusion Applications.

      • Oracle fusion applications host: This is the link to your fusion environment (for example, fa-eqgj-dev11-saasfademo1.ds-fa.oraclepdemos.com).

      

   2. In the Default Connection Information section, enter the following information and Test Connection.

      • Type: Select Oracle BI Cloud Connector.

      • Username: Enter your BICC username.

      • Select Use password.

      • Password: Enter the BICC password.

      • Default BICC external storage configuration: Select the external storage connection created in the BICC Console.

      

5. Once the connection status is Successful, click Create.

   

6. In Select Data asset type, select OCI Object Storage.

   

   Enter the connection Name (ObjectStorageConn). You will notice that rest of the fields are auto-populated in your tenancy OCI Data Integration. Leave the rest of the fields as it is and click Test Connection.

   

   

7. After a successful connection, click Create.

   Note: Make sure to add the policies mentioned in the prerequisites of this document, before creating connection to OCI Object Storage and ADW in OCI Data Integration.

8. Click Create Data Asset. In Select Data asset type, select Oracle ADW.

   

   1. In General information, enter the following information.

      • Name: Enter ADWTarget.

      • Type: Select Oracle Autonomous Data Warehouse.

      • Select Upload Wallet.

      • Wallet file: Select wallet file downloaded in the initial steps of downloading database connection from ADW.

      • Wallet password (Optional): Add wallet password.

      

   2. In Default Connection Information, enter the following information.

      • Username: Enter admin.

      • Select Use Password.

      • Password: Enter the password for your admin user of ADW.

      • TNS Alias: Select from the high, medium, low options available.

      

   3. In Default Staging location, enter the following information.

      • Object Storage data asset: Select the data asset (ObjectStorageConn) created in the previous steps.

      • Connection: Select Default connection.

      • Compartment: From the list of compartments, select the compartment where your OCI Object Storage bucket resides.

      • Bucket: Select the bucket which has been connected to BICC.

      

9. Click Test connection to test the connection and after Successful connection, click Create.

   

10. Go to the OCI Data Integration Console and select Data Assets.

    

11. In the Data assets, we will see our three data assets created. One for BICC, one for OCI Object Storage and one for ADW.

    

Task 5.3: Create Data Flow

In OCI Data Integration, we have two options, one is to create a Data Loader Task and the other is to create a Data Flow.

• Data Loader: It is mostly used for simple loading diverse data sets into data lakes, data marts and warehouses. A Data Loader Task takes a source data entity, applies transformations (optional), and then loads the transformed data into a new target data entity or updates an existing data entity. A data loader task supports transformations at the metadata and data levels.

• Data Flow: It is a logical diagram representing the flow of data from source data assets, such as a database or flat file, to target data assets, such as a data lake or data warehouse. The flow of data from source to target can undergo a series of transformations to aggregate, cleanse, and shape the data. Data engineers and ETL developers can then analyze or gather insights and use that data to make impactful business decisions.

In this tutorial, we will create a data flow. In OCI Data Integration, first we will create a project which will act as a container for design-time resources, such as tasks or data flows and pipelines.

1. In OCI Data Integration Console, select Projects.

   

2. Select Create new.

   

   

3. In the Project details page, click Data flows and select Create data flow.

   

4. In the Data flow page, you can change the name.

   

5. From the Operators page, drag and drop the Source into the canvas. The source connector property details will be opened in the bottom.

   

6. In Properties, enter the following information.

   • Identifier: Enter ERP_Financial.

   • Data Asset: Select the ERP data asset created in OCI Data Integration.

     

   • Connection: Select Default connection.

     

   • Schema: Select Financial schema.

     

   • Data entity: Select View All and Type will be FscmTopModelAM.FinExtractAM.ArBiccExtractAM.CustomerProfileClassExtractPVO and click Enter.

     Click the checkbox for the customer profile Public View Objects (PVO) and Select.

     

     Select Extract Strategy as Full. In case of incremental loads, you can select Incremental.

     Initial extract date is optional, so leave it as is. Else, when needed to schedule the extract tie, you can do so in this field.

   • BICC External configuration storage: Select the name of the external storage connection created in the BICC Console.

     

     

     At the end, you will see all the fields selected for your source table.

     

7. From the Operators page, drag and drop the Target connector into the canvas.

   

8. In Properties, enter the following information.

   • Identifier: Enter ERPCustomerDetail.

   • Select Create new data entity.

   • Data Asset: Select the ADW data asset created in OCI Data Integration.

   • Connection: Select Default connection.

   • Schema: Select View All and Admin.

   • Data Entity: Since we are creating new data entity in target ADW, name the new table that needs to be created.

     

   • Staging location: Select use default staging location.

     

   • Integration Strategy: Select Insert.

     

9. Go to the canvas, connect the source (ERP_Financial) operator with the target (ERPCustomerData) operator.

   

10. Click Validate.

    

11. After validation, click Create and Close.

    

    Once done, we can see the data flow in our project.

    

12. Next, create an Integration task.

    Integration tasks in OCI Data Integration let you take your data flow design and select the parameter values you want to use at runtime. With the help of integration tasks, you can create multiple tasks with distinct configurations for the same data flow.

    In the Project folder, navigate to Tasks, Create Task and Integration.

    

13. In the Create integration task page, enter the following information.

    • Name: Enter the task name.
    • Project or folder: Select project or folder name.
    • Select the data flow that was created from ERP to ADW.

    

14. Once the validation has been Successful, click Create and Close.

    

15. In your project page, you will see one data flow and one task is created.

    

16. In order to publish the task we need to create an Application.

    Application is a container for published tasks, data flows, and their dependencies. You can run published tasks in an application for testing or roll them out into production.

    Go to the OCI Data Integration Console, navigate to Applications and select Create Application, Create new.

    

    

17. Select Create blank Application, enter a Name and click Create.

    

    

18. Go to Projects and select your project folder.

19. Click Tasks, select your integration task and click Publish to application.

    

20. Select Application name and click Publish.

    

21. Go to the OCI Data Integration Console, select Applications and click your application.

    

22. In the Application details page, click Tasks and you can see the integration task.

    

23. Click the three dots.

    

24. Click Run. In order to schedule the run later, you can select Schedule.

    We will see our task running in the Runs in application folder.

    

    

25. After the run has been successful, we will go to our ADW to verify.

Go to the OCI Console, navigate to Oracle Database and Autonomous Data Warehouse. Select your ADW instance.

![Upload Objects](./images/Picture90.png "image")

![Upload Objects](./images/Picture91.png "image")

1. In the Autonomous Database details page, select SQL from the Database actions drop-down menu.

   

   In the SQL editor, you will see in the ADMIN user, the table we created in our data flow has been created.

   

2. Run the following statement in the SQL Worksheet.

   select * from ADMIN.ERPCUSTOMERDATA;
   

   It will show all the data from your financial ERP application data file.

   

3. In addition to ADW, if we go to our OCI Object Storage bucket, we will find our extracted file data in our bucket acting as the staging location.

   

This completes the extraction of data from Oracle Fusion Cloud ERP into ADW using OCI Data Integration.

Next Steps

You have seen a simple use case of how to move the data from Oracle Fusion Cloud ERP into ADW using OCI Data Integration service. You can customize the migration plan to meet your application requirements. For more details, see the OCI Data Integration Service documentation in the Related Links section.

Related Links

• OCI Data Integration

Acknowledgments

• Author - Sugandha Kher (Senior Cloud Engineer, North America Cloud Engineering)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Create Fusion SaaS Data Replication into Oracle Autonomous Data Warehouse using OCI Data Integration

G24813-01

January 2025

Copyright ©2025,

Oracle and/or its affiliates.