Using OCI Data Integration to Move Fusion BI Publisher Data to an On-Prem Database

Most enterprise teams work with data spread across different systems. At some point, that data needs to be pulled together so it can be reviewed, validated, reported on, or used by downstream applications. This becomes even more important as organizations invest in analytics and AI, where the quality and availability of data directly affect the usefulness of reports, dashboards, models, and AI-driven insights.

Oracle Cloud Infrastructure Data Integration, or OCI-DI, makes it easier to move and transform data through a visual, easy-to-follow approach. In this post, I’ll walk through one complete flow from beginning to end: extracting data from an Oracle Fusion BI Publisher report and loading it into an on-premises Oracle Database. At the end, I’ll include a short addendum with other common OCI-DI data asset types that often come up in similar integration patterns.

• Zero-code designer: A drag-and-drop interface that makes it easier to build data flows without writing a lot of custom code.

• Data preview: A quick way to review source data and transformation results while building the flow.

• Connectivity: Support for connecting to Oracle databases, Fusion applications, third-party sources, and on-premises data stores.

Use Case: Oracle Fusion to On-Premises Database

A client needed to move Fusion ERP data into an on-premises Oracle database for downstream reporting. Since Fusion Cloud does not provide direct database access, we used OCI Data Integration with a BI Publisher report to extract, map, and load the data into the on-premises target.

• Source: Oracle Fusion Applications data exposed through a BI Publisher report.

• Integration layer: OCI-DI data assets, data flow, integration task, and optional pipeline.

• Target: On-premises Oracle Database table used for reporting, downstream processing, or further validation.

Before You Start (Prerequisites)

Before building the flow, confirm these basics are ready:

At a high level, OCI-DI resources sit under your tenancy, identity domain, region, compartment, workspace, and project structure.

• OCI access: Access to the right tenancy, identity domain, users, groups, and policies to create and manage OCI-DI resources.

• Region and compartment: Select the OCI region and compartment where the workspace, data assets, and related resources will be created.

• OCI-DI workspace: Create or select the workspace used for data assets, projects, flows, tasks, pipelines, and applications.

• Network access: Confirm OCI-DI can reach any private or on-premises source or target through the required routing, security rules, or firewall changes.

Core OCI-DI Components (Quick Overview)

OCI-DI mainly uses these building blocks:

• Workspace: The main area where OCI-DI work is organized.

• Data assets: Connections to source and target systems, such as Fusion BI Publisher and Oracle Database.

• Projects and data flows: Projects group related objects, while data flows define mapping, filters, transformations, and movement from source to target.

• Tasks: Executable objects created from data flows, SQL steps, or pipelines and published for execution.

• Applications: Used to run, schedule, monitor, and review published tasks.

• Pipelines: Optional orchestration layer used when multiple steps need to run together.

 

Step 1: Create the Source and Target Data Assets

Start by opening OCI Data Integration and selecting the right workspace, then define the two data assets used in this walkthrough: one for the Oracle Fusion BI Publisher source and one for the on-premises Oracle Database target.

1. In the OCI Console, go to Analytics and AI and select Data Integration.

2. Select the appropriate OCI region.

3. Select the compartment and workspace created for your OCI-DI implementation.

4. Create the Oracle Fusion BI Publisher source data asset.

5. Create the on-premises Oracle Database target data asset.

   
   

Source Data Asset: Oracle Fusion BI Publisher

• Go to Data Assets → Create Data Asset and select Fusion Apps – BI Publisher.

• Enter the Name, Identifier, and Fusion host, such as yourcompany.oraclecloud.com without https://.

• Confirm Type is set to Oracle Fusion Applications and enable the required NUMBER handling options.

• Set Default Connection Type to Oracle BI Publisher and provide the integration user credentials.

• Test the connection.

Target Data Asset: On-Premises Oracle Database

• Go to Data Assets → Create Data Asset and select Oracle Database.

• Enter the Name, Identifier, host, port, and service name.

• Confirm Type is set to Oracle Database and enable the required NUMBER handling options.

• Provide the database credentials under Default Connection.

• Test the connection.

Note: In this walkthrough, the Oracle Database data asset is used as the target. The addendum at the end provides a quick reference for other common data asset types, including on-premises Oracle Database.

Step 2: Build the Data Flow

With the source and target data assets ready, create the data flow that connects the Fusion BI Publisher report to the on-premises database target table.

• Open the project and create a new data flow.

• Select the Oracle Fusion BI Publisher data asset and choose the report that will provide the source data.

• Select the on-premises Oracle Database data asset as the target.

• Map the report fields to the target table columns and add any required filters, expressions, joins, or transformations.

• Validate the data flow to catch mapping or configuration issues before creating the integration task.

Step 3: Create the Integration Task

• Go to Tasks, select Create task, and choose Integration as the task type.

• Enter a clear task name, such as IntegrationTask_Fusion_To_OnPrem_Load, and review the generated identifier.

• Select Disable simultaneous execution of the task if overlapping runs should be avoided.

• Choose the project or folder, then select the data flow created in Step 2.

• Validate the task configuration, then save and close the integration task.

Step 4: Publish the Integration Task

• Open the integration task created in Step 3.

• Select Publish to Application.

• Choose the appropriate application.

• Submit the publish request.

• Open the application and check the Patches section to confirm that the task was published successfully.

 Step 5: Optional - Build a Pipeline

A pipeline is optional for this simple Fusion-to-database flow. Use it only when you need to orchestrate multiple steps, such as parameter generation, multiple integration tasks, SQL processing, or post-load validation.

• Open the project and create a new pipeline under Pipelines.

• Enter a clear name, such as Pipeline_Fusion_To_OnPrem_Load.

• Add the required nodes, such as Start, optional Expression, Data Integration, and End.

• Connect the nodes in the order they should run and set each link to continue after successful completion.

• Validate the pipeline before creating the pipeline task.

• For a single data flow load, the integration task may be enough. For a multi-step process, the pipeline becomes the orchestration layer.

5.1 Configure Pipeline Parameters

• Open the Data Integration node and go to the Configuration tab.

• Review the incoming parameters for the selected integration task.

• Enter fixed values or map values from earlier pipeline steps.

• Validate the parameter mapping before publishing the pipeline task.

5.2 Create and Publish the Pipeline Task

• Go to Tasks, select Create task, and choose the pipeline task option.

• Enter a clear name, such as PipelineTask_Fusion_To_OnPrem_Load, and select the pipeline created in Step 5.

• Disable simultaneous execution if overlapping runs should be avoided.

• Validate and save the pipeline task, then publish it to the appropriate application.

 

Step 6: Run and Monitor the Load

• Open the application where the task was published.

• Run the published integration task directly, or run the pipeline task if a pipeline was created.

• Monitor the execution from the application runs page.

• Review row counts, status messages, and any error details.

• Validate the target table in the on-premises database to confirm that the Fusion data loaded successfully.

 

Common Data Asset Options in OCI-DI

The main walkthrough uses Oracle Fusion BI Publisher as the source and an on-premises Oracle Database as the target. The following data asset types are commonly used in related OCI-DI patterns, especially BICC-based flows where Fusion data is staged in OCI Object Storage and loaded into ADW.

1. Data Asset for BICC

For BICC, create a data asset using Fusion Apps - BICC with Type set to Oracle Fusion Applications. Enter the Fusion host without https://, use Oracle BI Cloud Connector as the default connection type, provide the BICC integration user credentials, select the external storage configuration, enable the required NUMBER handling options, and test the connection.

2. Data Asset for OCI Object Storage

For OCI Object Storage, create a data asset using OCI Object Storage and confirm the tenancy OCID, namespace, and OCI region. Provide a default connection name, verify the object storage connection details, and test the connection before using it for BICC extracts, staging, or ADW load support.

3. Data Asset for ADW

For ADW, create a data asset using Oracle ADW with Type set to Oracle Autonomous Data Warehouse. Select the region, tenancy, compartment, and target Autonomous Database, provide database credentials, choose the required TNS alias, configure the Object Storage staging location, and test the connection.

Final Thoughts

This setup is a good starting point when you need to move Fusion report data into an on-premises database without building a lot of custom integration code. The main flow stays focused on the required pieces: data assets, data flow, integration task, publishing, and execution. If the load becomes part of a larger multi-step process, a pipeline can be added later as the orchestration layer.