ADF and Synapse: Create and Run Fusion Pipelines for BI Publisher Data Models and OTBI Reports

Modified on Sat, 8 Aug at 10:09 AM

Before you start

You have created a Linked Service connection in ADF or Synapse to the BI Connector Data Source. If not, set up the Linked Service connection first.
You have the Lookup table loaded in your destination platform. If not, create and load a Lookup table for BI Publisher Data Models or Lookup table for Analysis/OTBI Reports first.
You have a connection established to your destination platform using an account with permissions to create tables and write data in the destination schema.
You have the BI Connector pipeline template file shared by the BI Connector team.
You have any stored procedure code and pipeline field mapping details shared by the BI Connector team, if applicable.

Steps

Step 1 — Log in and open the Author section

Step 2 — Import the BI Connector pipeline template

Click the + icon to the right of the search box, select Pipeline, then select Import from pipeline template. Browse to and select the pipeline template file shared by the BI Connector team, then click Open.

Tip — Using Synapse? Click the Integrate icon in the left pane instead, then click the + icon above the search box, and select Import from pipeline template.

Step 3 — Map your connections

Map the appropriate Linked Services to the pipeline:

Source: select the ODBC Linked Service you created to Oracle Fusion via BI Connector.
Destination: select the Linked Service for your destination platform (such as Databricks, Snowflake, SQL Server, or Oracle database).

Click Use this template at the bottom left.

Tip — Using Synapse? The button reads Open Pipeline instead of Use this template. Click that to proceed.

Step 4 — Add stored procedure code

If the BI Connector team has shared stored procedure code for your destination platform, add it now. This code handles post-processing steps and updates the Lookup table automatically after each successful run to prepare for the next incremental load.

Tip — If no stored procedure code was shared, skip this step and proceed to Step 5.

Step 5 — Map pipeline fields and enter parameter values

Map the pipeline fields as instructed by the BI Connector team. Enter the values for any parameters shown at the time of pipeline trigger.

Step 6 — Set up a trigger for automated runs

Click Add trigger at the top and set up the refresh schedule for automated pipeline runs.

Step 7 — Run the pipeline

Trigger the first pipeline run by clicking the Run option at the top. Enter the Lookup table name and Destination schema name on the prompted parameter values.

After the run completes, you can verify if the pipeline executed successfully by checking the tables loaded on to the destination schema. The next run would automatically kick-in based on the schedule you set in Step 6.

How the pipeline works: The first run performs a full load of all tables. Subsequent runs perform incremental refresh based on the configuration in each row of the Lookup table. The pipeline also automatically creates the destination tables and their column definitions if they do not already exist in the schema. It handles idempotency (updating existing rows based on the Primary Key defined in the Lookup table), identifies and adds new rows, and includes built-in error handling and retry mechanisms.

Tip — Need to pipeline additional tables later? Add the new rows to the Lookup table in BI Connector and update the Lookup table in your destination platform. The pipeline will automatically pick up and process the new rows in its next scheduled run.

Step 8 — Publish the changes

Click Publish all at the top left. Once the validation passes, click Publish to complete.