Microsoft Fabric: Create and Run Fusion Pipelines for BI Publisher Data Models and OTBI Analysis Reports

Modified on Sat, 8 Aug at 10:10 AM

Before you start

You have created a Gateway connection in Fabric to the BI Connector Data Source. If not, set up the Gateway 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 create a new Pipeline

Log in to your Fabric workspace. Click New item at the top left, search for Pipeline in the search box, and select Pipeline.

Step 2 — Name the Pipeline

Enter a name for the pipeline — for example, Fusion to Warehouse — and click Create.

Step 3 — Import the BI Connector pipeline template

On the top ribbon, click the Import icon (the arrow pointing into a bar: →|). Browse to and select the pipeline template file shared by the BI Connector team, then click Open.

Tip — About the template file: This file is pre-configured to refer to your Lookup table and push data to your specific destination platform (such as Databricks, Snowflake, SQL Server, Azure Dedicated SQL Pool, Fabric SQL, or Fabric Warehouse).

Step 4 — Select your connections

Select your destination platform connection and your ODBC connection (the one connected to your BI Connector Data Source) from the drop-downs, then click Use this template.

Step 5 — Add stored procedure code

If the BI Connector team has shared stored procedure code for your destination platform, add it now.

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

Step 6 — 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 7 — Schedule automated runs

Click the Schedule option on the top ribbon and set up the refresh schedule for automated pipeline runs.

Step 8 — Run the pipeline

Trigger the first pipeline run by clicking the Run option on the top ribbon. 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 7.

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.