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: Create 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.

1a. 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 

1b. 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 hostname of the destination Oracle DB.
    • 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 2: Create the Lookup Table in the Oracle Database

Before creating the table, let’s understand what it contains and how each column is used.

Look Up Table Columns:

1. TABLE_SRC
2. TABLE_DEST
3. SCHEMA_DEST
4. PRIMARY_KEY
5. WATERMARK_COLUMN
6. WATERMARK_COLUMN_FORMAT
7. FREQUENCY_IN_DAYS
8. REFRESH_TYPE
9. LAST_RUN_DATE
   
   

Understanding the columns in Lookup Table

• Source and Destination Information

1. TABLE_SRC
The source table for the pipeline, which refers to the table name defined in the BI Connector from which the data will be pulled.

2. TABLE_DEST
The name of the destination table in the target database where the data will be loaded.

3. SCHEMA_DEST
The schema in the destination database where the TABLE_DEST exists.

• Primary Key and Uniqueness

4. PRIMARY_KEY
The primary key column(s) of the source data.

• Used to uniquely identify rows and track changes.
• Can be a single column. Example: ERROR_ID
• Or multiple columns separated by commas. Example: PERSON_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE
  
  

• Watermarking (Incremental Load Logic)

5. WATERMARK_COLUMN
The column(s) used to detect new or updated records since the last run. Typically a timestamp or numeric column.

• Simply provide the name of the column used to track new or updated records
• Example: START_DATE
  
  

6. WATERMARK_COLUMN_FORMAT
Specifies the format of the datetime column of the WATERMARK_COLUMN field. It ensures that ADF can correctly interpret timestamp values for incremental data filtering.

• Required only if the watermark column is date/time column.
• Not required (can be left empty) if the column is not date/time column.
• Example: YYYY-MM-DD"T"HH24:MI:SS

               Watermark Column Configuration Scenarios using example columns

• Execution Control
  7. FREQUENCY_IN_DAYS
                  Indicates how often this pipeline should run.

• Example: 1 = daily, 7 = weekly.

            8. REFRESH_TYPE
               Defines the type of data load:

• FULL_LOAD : 

1. Set the REFRESH_TYPE to FULL_LOAD in the lookup table for the tables you want to do full load.
2. Make sure the LAST_RUN_DATE is NULL for the rows configured for full load.
3. To set FULL_LOAD for all rows in the lookup table, you can run the following SQL command:

UPDATE <your_lookup_table_name>
SET 
  REFRESH_TYPE = 'FULL_LOAD',
  LAST_RUN_DATE = NULL;

4. To set FULL_LOAD for specific rows in the lookup table, you can run the following SQL command:
   

UPDATE <your_lookup_table_name>
SET 
  REFRESH_TYPE = 'FULL_LOAD',
  LAST_RUN_DATE = NULL
WHERE 
  TABLE_SRC IN ('BIC_PJB_ERRORS', 'BIC_PER_ALL_PEOPLE_F', 'BIC_PAY_RUN_BALANCES');

                                         5. This query will update rows for the tables listed in the IN clause.
Note: In the IN clause, these are BI Connector table names (that you created in BI Connector) inserted within '' and separated by commas.

• IF NOT FULL_LOAD:  

1. First , it will check for the maximum value of the watermark column in the destination table.
2. If the Destination Table is empty or the maximum value is empty, then it will do full load
3. Else, It will do an incremental refresh. It does this by comparing each source row's watermark value (like LAST_UPDATED_DATE) against the maximum value already present in the destination.Only the records with newer values — meaning data that was added or updated since the last load — are fetched and loaded.

              9. LAST_RUN_DATE
              Timestamp when the pipeline was last executed successfully.

• It is used to track the last execution and determine when the pipeline should run next, based on the value of FREQUENCY_IN_DAYS
• For example, LAST_RUN_DATE= 2024-12-20 00:00:00
• FREQUENCY_IN_DAYS=2 
• Today’s date: 2024-12-22, the pipeline will execute since 2 days have passed since the last run
  
  

2a. Run the Table Creation Script:

CREATE TABLE "<<your_schema_name>>"."<<your_table_name>>" 
(
    "TABLE_SRC"               VARCHAR2(255), 
    "TABLE_DEST"              VARCHAR2(255), 
    "PRIMARY_KEY"             VARCHAR2(255), 
    "SCHEMA_DEST"             VARCHAR2(255), 
    "WATERMARK_COLUMN"        VARCHAR2(255), 
    "WATERMARK_COLUMN_FORMAT" VARCHAR2(255), 
    "LAST_RUN_DATE"           DATE, 
    "FREQUENCY_IN_DAYS"       NUMBER(3,0), 
    "REFRESH_TYPE"            VARCHAR2(50)
);

2b. 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,
  'incr_load'
);

2c. (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 = 7
WHERE TABLE_SRC = 'BIC_TABLE_PER_ALL_PEOPLE_F';

• FUSION_TO_ORACLE_PIPELINE_V2 → your lookup table name

• FREQUENCY_IN_DAYS = 7 → sets the pipeline to run every 7 days

• 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.
  
  

2d. 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