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

Modified on Mon, 10 Aug at 4:31 AM

Before you start

You have created a Linked Service connection in Azure 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 extracted the zip file shared by the BI Connector team to a preferred location. If not, extract it now. Inside are:
a. A Pipeline template file, which is a zip file again, but that can be used as-is in the steps below without having to extract the content
b. A Notepad file with Stored Procedure code with dbo as the schema mentioned. Replace dbo in the file with your destination schema name. Once done, save the Procedure in the destination platform.

Steps

Step 1 — Log in and open the Integrate section

Step 2 — Import the BI Connector pipeline template

Click the + above the search box, then select Import from Pipeline Template. Browse to and select the pipeline template file (which is also a zip file on the extracted content) shared by the BI Connector team, then click Open.

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 Open Pipeline at the bottom left.

Step 4 — 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 5 — Set up a trigger for automated runs

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

Step 6 — 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 5.

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 7 — Publish the changes

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