Azure Data Factory: Steps to Automate Data Pipelines from Oracle Fusion BI Publisher Data Models to Your Preferred Data Warehouse or Lakehouse

Using the BI Connector with Azure Data Factory (ADF), you can set up and automate data pipelines from Oracle Fusion Cloud ERP, SCM, HCM, CX application data sources (via the BI Publisher Data Models) to your preferred Data Warehouse (DW) or Lakehouse sinks, such as SQL Server, Snowflake, Azure SQL, Azure Lakehouse, Amazon S3, Oracle database, etc.

This article will show the steps to set up these pipelines.

Before you start

Who is this guide for?

This guide is for BI Connector/ADF users looking to set up data pipelines from the BI Publisher Data Models (created out of their Oracle Fusion cloud applications data) to their desired data warehouse.

Compatibility

BI Connector Versions

Prerequisites

Required Setup

1. Windows Server with Microsoft Integration Runtime and BI Connector
   

   • Install and configure Microsoft Self-hosted Integration Runtime (SHIR) from Microsoft
     Note: Its possible to use an existing SHIR as well

   • Install BI Connector Server edition and activate it using a license key.

2. Admin Access to Gateway Server
   

   • Admin access is required to set up System DSNs in ODBC.

Recommended Configurations

1. Windows Server: 
   1. Windows Server 2016 or above (can also be set up on a VM on the cloud)
   2. .NET Framework 4.7.2 or above installed
   3. Minimum of 8 CPU cores, 8 GB RAM, and 80 GB hard disk space
   4. Strongly recommended to disable hibernate/sleep on this server. 
      Note: The SHIR wouldn't work when the server enters hibernate/sleep mode

Oracle Fusion and Sink DW Authentication & Permissions

• Oracle Fusion Authentication and Permissions

  • Basic authentication is required (Username/password only).

    • To confirm: Log in to Oracle Fusion in a private browser window. If prompted for only a username and password, you have Basic Authentication.

    • If MFA is enabled: Contact your Oracle Fusion admin to create a service account for BI Connector.

  • Role Assignments

  • Assign the BI Publisher Data Model Developer role to your Oracle Fusion account.

  • Add additional roles based on data access needs:

    • Example: Budget Manager and Budget Analyst roles for budget data.

  • Custom Roles and Role Hierarchies are supported.
    
    Tip: To ensure the account has access to the right set of data, its recommended that you assign all the roles and privileges of the Oracle reporting user. In addition, add the BI Publisher Data Model Developer role.

• Sink Data Warehouse (DW) or Lakehouse Authentication and Permissions
  • The necessary credentials or auth tokens to make a connection to the Sink DW or Lakehouse
  • Write permissions to the desired Database/Schema/Table
    
    
• ADF Permissions
  • The ADF user must have access to use the following and publish them:
    • Create Linked Services
    • Access to use an existing SHIR or create a new one
    • Datasets
    • Pipelines

Downloads

Step-by-Step Process

Step 1: Create BI Connector Data Source on the Server

1. Log in to the Windows Server as an Admin user.

2. Open BI Connector (“Run as Administrator”).

3. Click New Data Source and provide a name that's convenient for you.
   Example: Oracle_Fusion_ERP_Prod_Data_Model

4. Select Data Model as the Data Source Type.

5. Choose your Oracle application:

   • Fusion (Oracle Fusion Cloud ERP/SCM/HCM/CX)

   • Analytics Cloud (Oracle Analytics Cloud)

   • OBIEE (Oracle Analytics Server)

   • Custom (if using a custom SSO setup—contact BI Connector Support).

6. Enter your Oracle Account URL:

   • Log in to Oracle Fusion, navigate to the Analytics/OTBI/Catalog page, and copy the URL up to oraclecloud.com.
     Example: https://acmeprod.ocp.oraclecloud.com

7. Enter Oracle Username and Password.

8. Add the folder path:

   • Locate the Data Model folder in Oracle Fusion, click More, select Properties, and copy the Location.
     Example: /shared/Custom/ERP/GL

9. Paste the folder path into the BI Connector Folder Path field.

   • To add multiple folder paths, repeat the steps above.

   • Sub-folders are automatically included.

10. Click Verify & Add to validate the folder path.
11. Test the connection by clicking Test & Save.

Step 2: Create a BI Connector Table

1. Open the Tables tab in the BI Connector application.

2. Click Add Table and provide a name. Note that the table name cannot contain spaces.
   Example: Monthly_GL_Trial_Balance

3. Select the Data Source created in Step 1.

4. Navigate through the folder paths to locate the desired Data Model and dataset. Or write your own SQL query by choosing the appropriate option.

5. Enter parameter values if applicable:

   • Enter values for mandatory parameters.

   • Use flexible date options (TODAY, YESTERDAY, etc.) based on your needs.

6. Preview the data and click Save.

Step 3: Create a Linked Service and Dataset For Oracle Fusion Source in ADF

1. Login to your ADF account
2. Click on Manage in the left-side pane
3. Navigate to Linked Services, and click the + New button
4. Search and select ODBC, click Continue
5. Fill out the required info for creating the Linked Service to Oracle Fusion:
   • Name: Give an appropriate name to the Linked Service
     Example: Source_Oracle_Fusion
   • Select the appropriate Self-hosted Integration Runtime, which also lives with the BI Connector Server Edition installed on the same server
   • Connection String: Enter the name of the BI Connector Data Source, preceded by "dsn="
     Ex: If your BI Connector Data Source is Oracle_Fusion_ERP_Prod_Data_Model, then the connection string is:
     dsn=Oracle_Fusion_ERP_Prod_Data_Model
   • Keep the Authentication Type as Basic, and enter your Oracle Fusion username and password in the appropriate fields
   • You may want to click on the "Test Connection" at the bottom right
   • Finally, click the Create button
6. Next, click on Author in the left pane. Hover over Datasets, click on the 3 dots, and select New Dataset
   • Search and select ODBC, click Continue
   • Name the dataset:
     Example: Source_Monthly_GL_Trial_Balance_Table
   • Select the Linked Service (that was created in the previous step) from the drop-down
   • Select the Integration Runtime from the drop-down
   • Select the table you created in BI Connector from the drop-down
   • Click Ok
     

Step 4: Create a Linked Service to the Sink System and a Dataset For the Appropriate Destination table

1. In ADF, click on Manage in the left-side pane
2. Navigate to Linked Services, and click the + New button
3. Search and select the appropriate option, click Continue
4. Name the Linked Service appropriately and fill out the required info for creating the Linked Service to your sink system
5. Next, click on Author in the left pane. Hover over Datasets, click on the 3 dots, and select New Dataset
6. Name the Dataset appropriately  and fill out the required info by pointing to your Sink system, and selecting the appropriate destination table

Step 5: Create a Pipeline in ADF and Publish it

1. In ADF, click on Author in the left pane. Hover over Pipelines, click on the 3 dots, and select New Pipeline

2. Name the Pipeline appropriately

3. Under activities, search for Copy Data

4. Drag and drop the Copy data activity on to the Pipeline canvas, and click on it

5. In the General tab at the bottom, give a name for the activity, and use other settings as required

6. In the Source tab, pick the source dataset from drop-down, and keep other settings as required

7. In the Sink tab, pick the sink dataset from drop-down and keep other settings as required

8. In the Mapping tab, click Import Schemas to make ADF pull the columns from both source and sink datasets and map them automatically

9. Select the columns needed in the Sink system's destination table

10. Click Debug at the top to do a trial run. 

    • On success of the trial run, click on the Add Trigger option 

    • Select New/Edit

    • Under Choose Triggers drop-down, click + New

    • Name the trigger

    • Select the Type as Schedule

    • Set the appropriate timeframe, say every x hours or days or weeks or months to automatically run the pipepline.
      

11. Next Click on Publish All button on top left, and publish the pipeline, datasets, trigger and Linked Services.