Oracle Fusion Applications using Oracle Business Intelligence Publisher (BIP)

You can extract data from Oracle Fusion Applications in Data Integration by connecting those data sources either with Oracle Business Intelligence Cloud Connector (BICC) or Oracle Business Intelligence Publisher (BIP).

For example, to extract data from an Oracle Fusion ERP or HCM application, you set up an Oracle Fusion Applications data asset as a data source in a Data Integration data flow. When integrating using BIP, Data Integration instructs BIP to run an existing BIP report and uploads the data into a predefined Object Storage bucket. Policies to use BIP, to access the Object Storage bucket, and network requirements for accessing Oracle Fusion Applications must be in place.

Before You Begin

Using Oracle Fusion Applications data assets in Data Integration requires extra setups and access to other services in Oracle Cloud Infrastructure. Before you create an Oracle Fusion Applications data asset, review this section.

Tip

First time using BIP with Data Integration

See the Extract data from BIP in Data Integration blog. This blog provides step by step instructions from setting up prerequisites in Oracle Fusion Applications to defining a data asset and building and running a data flow in Data Integration.

More References

Use the task checklist in the following table to ensure that you have the required setup and information you need for creating and using Oracle Fusion Applications data sources.

Task Requirement
Obtain access to BIP

This topic assumes you have the proper credentials to access and use BIP.

See Accessing Oracle Business Intelligence Publisher in User's Guide for Oracle Business Intelligence Publisher.

Obtain the Fusion Applications connection details

When you create a Fusion Applications data asset, you need to provide the following:

  • Hostname of the inbound server
  • Username and password to connect to the server

Instead of providing the password, you can use a secret in Oracle Cloud Infrastructure Vault to store the password, and provide the OCID of that secret.

See Vault and secret concept descriptions in Oracle Cloud Infrastructure Vault

To copy the secret OCID, see Getting a Secret's Details.

Verify network configuration to access Oracle Fusion Applications.

If the Fusion Applications host is accessible from the internet, and the workspace has an attached virtual cloud network (VCN), the VCN must:

  • Use DNS hostnames
  • Include an internet gateway

See Virtual Networking Quickstart.

If the Fusion Applications host is not accessible from the internet, you can use FastConnect to set up a fast and secure connection between Oracle Cloud Infrastructure and the Fusion Applications host.

Create policies to allow Data Integration to access buckets and objects in Oracle Cloud Infrastructure Object Storage.

To use an Oracle Fusion Application data asset in a data flow design, you need an external storage for staging the data. An OCI Object Storage bucket is the only storage type in BIP that you can use for staging.

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'}

Create a bucket for staging the extracted data.

Create a bucket and gather the following details, which you need to provide for the default staging location fields, when you create the Oracle Fusion Applications data asset in Data Integration.

  • Bucket name
  • Bucket compartment
Create an Object Storage data asset and get information for the staging location.

Create an Object Storage data asset. See Object Storage Data Asset Properties for a description of the properties. Gather the following details, which you need to provide for the default staging location fields.

  • Object Storage data asset name
  • Connection name for the data asset
Note

Policies required also depend on whether the Object Storage instance and Data Integration instance are in the same tenancy or different tenancies, and whether you create the policies at the compartment level or tenancy level. Review more examples and the blog Policies in Oracle Cloud Infrastructure (OCI) Data Integration to identify the right policies for your needs.

Oracle Fusion Applications Data Asset using BIP Connection

To use a secret for the password, see OCI Vault Secrets and Oracle Wallets.

To create a data asset that connects to a BIP source, complete the following properties and connection details.

Field Description
Properties
Name

Enter a name for the data asset.

Identifier

The identifier is a system-generated value based on the name you specify. You can change the identifier value, but after you create and save the data asset, you cannot update the identifier.

Description

Add a description. This field is optional.

Type The field shows the data source type that you selected in the Select data asset type panel (as described in Creating a Data Asset). Data Integration displays the data asset property fields and default connection fields based on this type.
Oracle Fusion Applications host

Enter the host name.

Treat NUMBER columns without precision and scale as VARCHAR

Select this checkbox to treat numbers without precision and scale as VARCHAR to prevent data loss.

For example, if you have a numeric data type with a precision value that's greater than 38, or if you have numeric data types without precision and scale properties.

Retain NUMERIC data type for NUMBER columns without precision

This check box is selected to retain numbers without precision as numeric data type when creating new target entity.

For example, the numeric data type with a precision value that's greater than 38 isn't supported by Spark. If this check box isn't selected, the type source numeric fields are converted as string in target system instead of the numeric data type.

Default connection information
Name Enter a name for the default connection.
Identifier The identifier is a system-generated value based on the name. You can change the identifier value, but after you create and save, you cannot update the identifier.
Description (Optional) Add a description.
Type

Select Oracle BI Publisher.

Username Enter the user name that can connect to the Fusion Applications host.
Use vault secret OCID If you use a secret in Oracle Cloud Infrastructure Vault to store the password, select this option. The following field appears:
  • Vault secret OCID for database password: Copy and paste the OCID of the secret for the password associated with the user from OCI Vault.
Use password

If you select this option, enter the password in the Password field.

Default staging location

Provide the information you gathered for staging the extracted data in the Before You Begin section. When a default staging location is set up, Data Integration uses the specified bucket whenever this data asset is added as a target in a data flow or data loader task.

Object storage data asset

Select an Object Storage data asset.

Connection

Select a connection.

Compartment

Select a compartment.

Bucket

Select a bucket.

The bucket is the Object Storage bucket that Data Integration uses for staging data before loading the data into the target.

(Optional) Test connection
Test connection After you complete all the required fields, click Test connection to ensure that the data asset details have been entered correctly and the connection can be established. A success message appears if the test is successful. If the test fails with an error message, review the connection settings and try again.