Lookup Tables for Analysis & OTBI Reports: Steps to Create and Load in Destination Platform

Modified on Sat, 8 Aug at 10:26 AM

Before you start

You have a BI Connector Analysis Data Source and Table set up, with your report folder path added. If not, set up your Analysis Data Source and Table first.
You know your destination schema and which destination platform you are loading into (for example, SQL Server, Snowflake, Databricks, Oracle, Fabric SQL).
You have the list of Analysis or OTBI reports you want to pipeline.
Open the BI Connector file in C:\ProgramData\Guidanz\BI Connector as Administrator. Add any of these three lines that are not already present to the top of the file, then save:
async=0
ShowFunction=0
SuppressSelectStar=0
Restart the Gateway/Integration Runtime service and the BI Connector Service in the Windows Services app.

Part A — Create the Lookup Table in BI Connector

What is a Lookup Table? A table of tables. It holds the list of Oracle Fusion reports you want to move to your destination platform, along with attributes like Primary Key, Watermark column, and refresh frequency. Your pipeline reads it to know what to fetch and how.

Step 1 — Open the Data Pipeline section

Click Data Pipeline on the left pane, then click + New Lookup Table.

Step 2 — Fill in the Lookup Table details

Fill in these fields:

Lookup Table Name — a name for this Lookup Table. This is the name that will appear in your destination platform.
Select Data Source — pick your Analysis Data Source from the drop-down (for example, Fusion OTBI).
Destination Schema — the schema in your destination platform where the Lookup Table will be created.
Destination Warehouse — select your destination platform from the drop-down (for example, SQL Server, Snowflake, Databricks, Oracle, Fabric SQL).

Step 3 — Pick the reports to pipeline

In the Analysis Reports field, add the reports you want to pipeline. You can type a report name and pick it from the list, paste comma-separated names, or enter one report per line.

Note — Unlike the Data Models Lookup Table (which has raw and BIC tables), BI Connector here simply lists every report found in the folder path you set during the Analysis Table setup. You just pick the ones to pipeline.

To remove a report, click the × next to its name. To remove all reports at once, click Remove All.

Step 4 — Load the Lookup Table

Click Next: Load Lookup Table. BI Connector loads the table and auto-fills the four mandatory columns. Review the pre-filled values, edit any cell if needed, then fill in the manual columns.

Auto-filled (mandatory) — edit only if needed:

REPORT_NAME — the report name as it will appear in the destination platform.
REPORT_PATH — the full catalog path of the report in Oracle Fusion.
TABLE_DEST — the table name as it will appear in the destination platform.
SCHEMA_DEST — the destination schema.

Fill in manually:

PRIMARY_KEY — the column(s) that uniquely identify each row and let the pipeline track changes. Use one column (e.g. ERROR_ID) or several separated by commas (e.g. PERSON_ID,EFFECTIVE_START_DATE).
WATERMARK_COLUMN — the column used to detect new or updated records since the last run. Usually a timestamp or numeric column (e.g. LAST_UPDATE_DATE).
IS_WATERMARK_COL_TIMESTAMP — enter Y if the Watermark column is a timestamp field, or N if it is not.
FREQUENCY_IN_HOURS — how often this report refreshes in the pipeline, in hours (e.g. 2 = every 2 hours, 24 = once a day).
USE_ROLLING_WINDOW — enter Y to re-pull the last X days on every run, or N for a standard incremental load. Use Y when the Watermark is a derived or calculated date that a normal high-watermark check would miss.
ROLLING_WINDOW_DAYS — when USE_ROLLING_WINDOW is Y, the number of days to look back on each refresh (e.g. 180). Leave blank when it is N.

Step 5 — Save the Lookup Table

Once all details are complete, click Save. A success message appears at the top right, and you are redirected to the Lookup Tables list where your new table appears.

Part A complete! Your Lookup Table is now created in BI Connector. Continue to Part B to load it into your destination platform.

Part B — Load the Lookup Table into your destination platform

The Lookup Table now needs to exist as a real table in your destination platform, so your Fabric or ADF/Synapse pipeline can refer to it when executing queries.

Step 6 — Download the Create and Insert statements

On the Lookup Tables list, click the ⋯ (three dots) next to your Lookup Table, then hover over Download SQL. Two options appear:

Create Statement — downloads the SQL script that creates the Lookup Table structure in your destination platform.
Insert Statement — downloads the SQL script that inserts the Lookup Table rows.

Step 7 — Execute the statements in your destination platform

Log in to your destination platform and run the Create Statement first, then the Insert Statement. Once both run successfully, the Lookup Table is loaded and ready for your pipeline to use.

Important — Always run the Create Statement before the Insert Statement. Running the Insert Statement on a table that does not exist yet will cause an error.

Tip — Need to add more reports later? Open this Lookup Table in BI Connector and add them. Then download the Insert Statement and run it for only the new rows. No need to re-run the Create Statement.

What would you like to do next?

What's your goal?	Go to
Create and run a pipeline in Microsoft Fabric	Create a Gateway Connection in Fabric
Create and run a pipeline in ADF or Azure Synapse	Create a Linked Service in ADF/Synapse