Most companies using Oracle Fusion Cloud have a pressing need to push the Fusion data to their Oracle on-premise database to maintain a single source of truth. These companies typically come across any of the following combinations of Data Migration/ETL needs for each table they want to pull from Oracle Fusion:

1. Type of data pull from source table - Full data pull or Incremental data pull
2. Data insertion in target table - Insert new rows or update existing rows or both
3. Frequency of automated refresh - daily/weekly/bi-weekly or once in x days

In some cases, they might have a composite primary key (a set of concatenated values from different columns of the table to uniquely identify each row). Some tables may not have time-based columns to facilitate incremental loads, for which the primary key is used as a reference to perform the incremental load.

This article will help you to handle all of the above scenarios with a single Azure Data Factory (ADF) pipeline (that refers to a Lookup table). 

The lookup table acts as a configuration controller for ADF pipelines. Each row in the table represents a data load job, including source/destination table details, load type, execution frequency, and watermark column information for incremental loads.

Prerequisites:
1. Required Setup:

1. Windows Server with Microsoft Integration Runtime and BI Connector
   
   1. Install and configure Microsoft Self-hosted Integration Runtime (SHIR) from Microsoft
      Note: Its possible to use an existing SHIR as well
   2. Install BI Connector Server edition and activate it using a license key.
2. Admin Access to Gateway Server
   
   1. Admin access is required to set up System DSNs in ODBC.
      

2. Recommended Configurations:

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

Step 1: Download the ADF Pipeline Template

You can Download the ADF Template from this link.

Step 2: Import the ADF Pipeline using the template:

Note: Before proceeding, ensure the BI Connector DSN is configured and the required BIC tables (source tables created in BI Connector) are already available. These are essential for the ODBC Linked Service to connect successfully and retrieve data.

For guidance on how to create a DSN and BIC tables, refer to this article.

2a. Import the Pipeline Template

• Click the + (plus) icon under Factory Resources.
• Select Pipeline, then choose Import from pipeline template from the options shown.
• Add the ZIP folder containing your pipeline template 

2b. Map the services 

After importing the pipeline template, you need to map the linked services.

• Oracle Linked Service
  Either:
  • Select an existing Oracle Linked Service configured to point to your destination Oracle database.
    OR
  • Click + New to create a new one:
    
    • In the New Linked Service window:
      • Give Name to Oracle Linked Service
      • Select Self Hosted Integration Runtime under Connect via Integration Runtime.
      • Choose Version as 2.0
    • Under Server name: Enter the full oracle connection string in this format: host:port/serviceName
    • Under Authentication type: Choose the appropriate method (Basic).
    • Enter database username and password that has write access to destination schema.
    • Click Test Connection to ensure successful connectivity.
    • Click Create.

• ODBC Linked Service
  Either:
  • Select an existing ODBC Linked Service that is already configured with the BI Connector DSN.
    OR
  • Click + New to create a new one:
    • In the New Linked Service window:
      • Give Name to the ODBC Linked service.
      • Select Self Hosted Integration Runtime under Connect via Integration Runtime.
    • Under Connection String:
      • Add the DSN name that was created using BI Connector, in the format: 
        dsn=<<your_dsn_name>>
    • Under Authentication type: Choose the appropriate method (Basic)
    • Provide the username and password of your Oracle Fusion Account.
    • Click Test Connection to verify.
    • Click Create.
      
      

Step 3: Create the Lookup Table in the Oracle Database

3a. Run the Table Creation Script:

CREATE TABLE <<your_schema_name>>.<<your_table_name>> (
  TABLE_SRC               VARCHAR(255),	-- Source table name from which data is extracted
  TABLE_DEST              VARCHAR(255),	-- Destination table name where data is loaded
  PRIMARY_KEY             VARCHAR(255),	-- Primary key column(s) used to uniquely identify records
  SCHEMA_DEST             VARCHAR(255),	-- Target schema for the destination table
  WATERMARK_COLUMN        VARCHAR(255),	-- Column used for incremental loading
  WATERMARK_COLUMN_FORMAT VARCHAR(255), -- Format of the watermark column
  LAST_RUN_DATE           DATE,			-- Date when the data pipeline last ran successfully
  FREQUENCY_IN_DAYS       INT,			-- Number of days between scheduled data refreshes
  REFRESH_TYPE            VARCHAR(50)	-- Type of data refresh: FULL_LOAD, INCR_LOAD
);

3b. Insert Rows into the Lookup Table using SQL INSERT Query 

Sample Query:

INSERT INTO <<your_table_name>
(
  TABLE_SRC,
  TABLE_DEST,
  PRIMARY_KEY,
  SCHEMA_DEST,
  WATERMARK_COLUMN,
  WATERMARK_COLUMN_FORMAT,
  LAST_RUN_DATE,
  FREQUENCY_IN_DAYS,
  REFRESH_TYPE
)
VALUES (
  'BIC_TABLE_PER_ALL_PEOPLE_F',
  'ORACLE_TABLE_PER_ALL_PEOPLE_F',
  'PERSON_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE',
  'BIC_TEST',
  'START_DATE',
  'YYYY-MM-DD"T"HH24:MI:SS',
  '',
  1,
  'full_load'
);

3c. (Optional) Updating and Deleting Rows in the Lookup Table 

This step is required only if you want to make any changes to the Lookup table. If not, you can move to the next step.

To Update a Row:

Add the Update statement in the sql editor and execute it .

UPDATE FUSION_TO_ORACLE_PIPELINE_V2
SET FREQUENCY_IN_DAYS = 1
WHERE TABLE_SRC = 'BIC_TABLE_PER_ALL_PEOPLE_F';

• FUSION_TO_ORACLE_PIPELINE_V2 → your lookup table name

• FREQUENCY_IN_DAYS = 1 → specifies that the pipeline will execute for this table daily.

• TABLE_SRC = 'BIC_TABLE_PER_ALL_PEOPLE_F' → applies the change to the row where source table is BIC_TABLE_PER_ALL_PEOPLE_F
  
  

To Delete a Row:
Use the DELETE statement with a proper condition:

DELETE FROM FUSION_TO_ORACLE_PIPELINE_V2
WHERE TABLE_SRC = 'BIC_TABLE_PER_ALL_PEOPLE_F'

• FUSION_TO_ORACLE_PIPELINE_V2 → your lookup table name
• This condition ensures that only the row(s) where the TABLE_SRC column has the value 'BIC_TABLE_PER_ALL_PEOPLE_F' will be deleted.
  
  

3d. Set the Lookup Table Name in ADF Pipeline

• In the TO_ORACLE_USING_BIC pipeline (e.g., FUSION_TO_ORACLE_USING_BIC) in ADF, replace the existing default value of the table_name parameter with the name of the lookup table you created.  
• Once done, Click Publish All in the ADF to save and deploy your changes
  

To learn more about Lookup Table, please refer this article.

Step 4: Run the ADF Pipeline

4a. To Run the pipeline Manually

• Click Add Trigger and then click Trigger now

• Enter the table_name

• Under table_name: Enter the name of the lookup table you created 
• Click OK

4b. To Run the Pipeline on a Schedule 

• Add the Trigger

       Click the Add Trigger and New/Edit

• Create the Trigger

       Click Choose trigger and Click + New 

• Configure the Trigger

• Under Name: Enter trigger name
• Under Type: Choose Schedule
• Under Start date: Choose appropriate date with time
• Under Time zone: Choose appropriate time zone
• Under Recurrence: Choose Every 1 day(s)
• Click OK

• Enter the table_name

• Under table_name: Enter the name of the lookup table you created 
• Click OK

• Publish the Pipeline

    After creating the trigger, click Publish All.

To see more on Frequently Asked Question, please refer this article.