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

Modified on Wed, 12 Aug at 5:18 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 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.

Tip — Haven't received the Pipeline Template and Stored Procedure code files from the BI Connector Team yet? Contact BI Connector

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 — Map pipeline fields and enter parameter values

Shortcut — Pipelining to a Fabric Data Warehouse? You can skip this step and go straight to Step 6.

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

For all other destinations — choose your pipeline path to open the step-by-step guide for your combination. It has your exact mappings and all remaining steps, so you can finish there without returning to this article.

Step 6 — Schedule automated runs

Click the Schedule option on the top ribbon 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 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 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.