Oracle Fusion BI Publisher Data Models to Snowflake via ADF: Steps to Plug-n-Play BI Connector's ADF Pipeline Template

Modified on Mon, 10 Aug at 9:50 AM

Before you start

You have completed Steps 1–3 in ADF: Create and Run Fusion Pipelines for BI Publisher Data Models and OTBI Reports, including importing the pipeline template, mapping your connections, and adding the stored procedure code.
Your Data Models Lookup Table is created and loaded in Snowflake. If not, see Lookup Tables for Data Models.
You have created your Snowflake Azure Blob Storage Integration and the storage container/stage that the mappings below refer to.
You know your Snowflake connection details.

This path — covers pipelining BI Publisher Data Models to Snowflake using ADF. It continues from Step 3 of the ADF pipeline guide, gives the exact field mappings for Step 4, then the steps to trigger, run, and publish.

Steps 4 to 7 — Map, trigger, run, and publish

Step 4 — Map the pipeline fields (Data Models to Snowflake)

Refer the screenshot of a sample Plug-n-play Pipeline template for quick navigational reference:

BI Connector's Pipeline Template for Fusion

Tip — Haven't received a Plug-n-Play Pipeline Template file like above from the BI Connector Team yet? Contact BI Connector

Complete the mappings row by row by referring to the table below.

For each row, start from the cell in the left. Complete the steps in that left-most cell, and move on to the steps on the cells in the right. After the steps in a row is completed fully, start from the left cell in the next row.

Finish the steps in all the rows.

Pipeline activity (and sub-activities nav)	Activity tab	Mappings
→ Click LOOP_THROUGH_MAPPINGS in the top pane → Click Edit in the Activities box → Double-click GET_ALL_DATA_FROM_FUSION	→ Click the Sink tab at the bottom pane	Map the following in the bottom pane: → Storage Integration — Click the Refresh icon next to the dropdown, then select the Snowflake Azure Blob Storage Integration you created earlier
	→ Click the Settings tab at the bottom pane	Under Staging Settings in the bottom pane: → Storage Path — Click Browse and select the storage container for which the stage was created
→ Double-click GET_DELTA_DATA in the top pane	→ Click the Sink tab at the bottom pane	Map the following in the bottom pane: → Storage Integration — Click the Refresh icon next to the dropdown, then select the Snowflake Azure Blob Storage Integration you created earlier
→ Double-click GET_DELTA_DATA in the top pane	→ Click the Settings tab at the bottom pane	Under Staging Settings in the bottom pane: → Storage Path — Click Browse and select the storage container for which the stage was created

Tip — The Storage Integration and storage container are the Azure Blob staging objects you set up for Snowflake earlier. Both GET_ALL_DATA_FROM_FUSION and GET_DELTA_DATA use the same values.

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.