This article serves as a Frequently Asked Questions (FAQ) guide for managing Azure Data Factory (ADF) pipelines from Oracle Fusion Cloud to Oracle on-premise Database using BI Connector. It covers common challenges encountered during pipeline execution and provides step-by-step solutions to ensure smooth and efficient pipeline operations.

FAQ 1 : How can I add a new table in existing ADF Pipeline ?

To add a new table to an existing ADF pipeline, follow the steps below:

Step 1:  Create a Table in BI Connector

• Open the BI Connector UI and navigate to the Tables section.
• Click on "Add New Table" and map it to the appropriate DSN (Data Source Name).
• If using a Custom SQL Query:
• Choose the “Custom SQL” option and enter your query in the editor (e.g., SELECT * FROM PER_ALL_PEOPLE_F).
• Click “Run” to preview the data and confirm it loads as expected. Once verified, click “Save” to add the table.
• If using an Existing Data Model:
  • Select the desired Data Model and Dataset from the Dataset Tree.
  • Click “Preview” to ensure data is loading correctly.
  • After confirming, click “Save” to create the table.

Step 2: Create the Actual Table in Oracle Database

• In the BI Connector UI, navigate to the Tables section.
  
• Click the three-dot icon under the Actions column for the desired table. 
• Select "Download Table Meta Data", then choose "Create Table Statement" and select Oracle as the database. 
• Use that query to create the table in your Oracle Database.

Step 3: Insert Configuration into the Lookup Table

• Using Insert query add the new row to the lookup table for the table you created.

INSERT INTO <<your_lookup_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'
);

FAQ 2 : How to Configure FULL_LOAD for All/Specific Pipeline Tables ?

• Set the REFRESH_TYPE to FULL_LOAD in the lookup table for the tables you want to do full load.
• Make sure the LAST_RUN_DATE is NULL for the rows configured for full load.
• 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;

• 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');

This will update rows for all 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.

FAQ 3 : How do I schedule tables to get data daily ?

• To get daily data, set the value=1 of the FREQUENCY_IN_DAYS column in the lookup Table for the tables you need daily data. 
• Suppose If you want 3 out of 10 Tables executes daily then set the FREQUENCY_IN_DAYS value to 1 for those 3 tables in the lookup table.
• Make sure that the REFRESH_TYPE for the row is not FULL_LOAD.
• You can set the REFRESH_TYPE column for the row to NULL.

FAQ 4 : How do I schedule tables to get data weekly ?

• To get weekly data, set the value=7 of the FREQUENCY_IN_DAYS column in the lookup Table for the tables you need weekly data. 
• Suppose If you want 4 out of 10 Tables executes weekly then set the FREQUENCY_IN_DAYS value to 7 for those 4 tables in the lookup table.
• Make sure that the REFRESH_TYPE for the row is not FULL_LOAD.
• You can set the REFRESH_TYPE column for the row to NULL.

FAQ 5 : If the BIC Desktop Edition and BIC Server Edition are in 2 different machine , then How a Desktop user loads data into ADF ? 

Desktop users can load data into ADF by following these steps:

Step 1:  Create BIC Server Integration in BIC Desktop Edition

• In desktop Edition , On the left side click on BIC Server.
• Create a new BIC Server Integration:
  • Give server integration an appropriate name .
  • Add the BI Connector Server edition url .
  • Add the API key (You can get the api key from the server edition , In server Edition on the left side Menu click on API key , copy the key and paste it in the Desktop Edition).

Step 2: Published the table to the Server edition

• In Desktop Edition , Go to the Tables , In the Action column the first icon is for publishing the table.
• Click on the icon and map it to the BIC Server Integration and publish the table.
• If you want to publish multiple tables , select the tables that you want to publish by enabling the checkbox on the left side of the table name , and there is a button (Publish) at top right to publish the table. 
• Now the Tables and DSN is published to BI Connector Server edition.

Step 3: Set Up The Lookup Table 

• Insert New rows in the lookup Table for the tables you published to the Server Edition.
  
  

FAQ 6: What will happen if REFRESH_TYPE is not FULL_LOAD ?

• First , it will check for the maximum value of the watermark column in the destination table.
• If the Destination Table is empty or the maximum value is empty, then it will do full load

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

FAQ 7: How can I run the ADF Pipeline On-Demand ?

• Set the LAST_RUN_DATE to NULL for all the rows of the look Up table.
• Run This query to set LAST_RUN_DATE to NULL for all rows.

UPDATE <your_lookup_table_name>
SET LAST_RUN_DATE = NULL

FAQ 8: How can I schedule the ADF Pipeline run ?

• Click on the Add Trigger option and Select New/Edit
• Under Choose Triggers drop-down, click + New
• Name the trigger and Select the Type as Schedule
• Set the appropriate time frame, say every day at 12:00 AM run the pipeline.