Complete Guide to Analysis Report Data Pipeline Lookup Table Fields and Settings

In this article, we'll show complete guide to lookup table fields and settings

Let’s understand what it contains and how each column is used.

Look Up Table Columns:

1. BI_CONNECTOR_ID
2. REPORT_PATH
3. REPORT_NAME
4. TABLE_DEST
5. SCHEMA_DEST
6. PRIMARY_KEY
7. WATERMARK_COLUMN
8. IS_WATERMARK_COL_TIMESTAMP
9. HIGH_WATERMARK
10. FREQUENCY_IN_HOURS
11. REFRESH_TYPE
12. ENABLE_FLAG
13. STARTED_ON
14. ENDED_ON
15. STATUS
16. ERROR_DESC
    
    

Understanding the columns in Lookup Table

• Source and Destination Information

1. BI_CONNECTOR_ID

           Auto-generated unique identifier for each row in the lookup table. Used to uniquely identify each lookup table entry.

2. REPORT_PATH

Represents the complete path of the OTBI Analysis Report in the Oracle Fusion catalog, typically in the format:

/Folder Path/Report Name

During lookup table creation in BI Connector, BI Connector automatically generates the REPORT_PATH for each report by combining the folder path with the report name retrieved from that folder.

3. REPORT_NAME

Specifies the name of the OTBI Analysis Report that needs to be extracted. This should exactly match the report name present in the specified REPORT_PATH, as BI Connector uses it to retrieve data from the correct report.

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

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

• Primary Key and Uniqueness

6. 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)

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

8. IS_WATERMARK_COL_TIMESTAMP

          9.  HIGH_WATERMARK

• Represents the maximum watermark value processed in the previous successful run and is used to control incremental loading.
• Based on this value, the pipeline fetches new or updated records
  
  

• Execution Control
  
  10. FREQUENCY_IN_HOURS
  Indicates how often this table should run in pipeline (in hours).
  Examples:
  • 2 → Runs every 2 hours
  • 24 → Runs once daily
    
    

         11. 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. 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'

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

   UPDATE <your_lookup_table_name>
   SET REFRESH_TYPE = 'FULL_LOAD'
   WHERE BI_CONNECTOR_ID IN (1, 2, 3)

4. This query will update rows for the tables listed in the IN clause.

• IF NOT FULL_LOAD:  

1. First , it will check for the high_watermark value.
2. If the high_watermark 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.

         12. ENABLE_FLAG

• Indicates whether the table is active for execution.
• Values:

• 'Y' → Enabled

• 'N' → Disabled

• If set to N, the pipeline will skip that table.
  
  

• Execution Tracking and Monitoring 
  
  

          13. STARTED_ON
            Timestamp when pipeline execution started for that table.

           14. ENDED_ON
           Timestamp when pipeline execution successfully ended for that table.

           15. STATUS

• Stores execution status. 
• Values:

• 'Succeeded'

• 'Failed'
  
  

           16. ERROR_DESC

• Stores detailed error message if execution fails for that table.
• Used for troubleshooting and monitoring.
• Values:

• If Succeeded → Empty or NULL

• If Failed     → Error message