Oracle Business Intelligence Publisher

Oracle Business Intelligence Publisher (BIP) is Oracle's enterprise reporting product that provides the ability to create and manage highly formatted reports from a wide range of data sources. Oracle BIP connects directly to the underlying databases to extract data from Oracle Fusion Cloud Applications.

Features

Supported deployment models

We support the SaaS Deployment model for the connector.

Setup guide

Follow our step-by-step Oracle Business Intelligence Publisher setup guide to connect your Oracle Business Intelligence Publisher with your destination using Fivetran.

Sync overview

Database tables

Fivetran connects with your Oracle Business Intelligence Publisher(BIP) instance and syncs data from the database tables of the FUSION schema.

Once we connect with your Oracle BIP instance, we then create the following objects in the Oracle Business Intelligence Publisher catalog to sync the database tables:

• Data Model: serves as the source for generating reports. Here, we define the query to extract data from the source.
• Report: generated using data from a data model and a report template, in the form of a document or an output.

Once we create these objects for every table, we then schedule a report job to deliver the data files to the Universal Content Management (UCM) storage. We create these objects only if they don't exist else, we update them in every sync.

• For re-import tables, we create one data model and one report object to extract the data.
• For incremental tables, we create two data models, one to extract incremental data and the other to extract active primary key rows. We create one report for each data model.

To re-import tables, we use bursting definitions to split data into multiple files. We need to split the data across multiple files due to a limitation on the file size that the scheduler can deliver.

Learn more about the Oracle BIP use cases in our documentation.

Historical sync

To perform a historical sync of any table, we extract the table's creation_date from the source and begin extracting data starting from that date till the sync start time. If we are unable to extract the creation_date or if it is unavailable in the source, we then start the historical sync from 2010-09-01 (the date of the Oracle Fusion Cloud launch).

Incremental sync

We incrementally update the tables with columns where the column names have last_update_date as a substring in them. We use these columns as incremental filters to extract data from the source.

Capturing deletes

We capture delete for the incremental tables. To capture deletes for a table, we extract all the active primary key rows in every sync and mark the non-active primary key records as deleted.

Re-import tables

If a table doesn't have any incremental columns or primary keys, we re-import them in every sync. As a result, these tables lead to slower syncs and increase your MAR usage.

Custom data models

Fivetran connects to your Oracle Business Intelligence Publisher (BIP) instance and fetches all data models from the folder paths you specify during the connection setup.

We support syncing a data model if either:

• it contains no parameters, or
• all its parameters have default values.

Custom data models with pagination support

A data model supports pagination (required for extracting more than 2 GB or 2 million rows) only if all of the following are true:

• The data model contains a single dataset of type Standard SQL.
• The data model has a single output group.
• The output column names match the SQL output column names exactly. Column renaming in the Structure section is not supported.

If the data model exceeds 2 GB or 2 million rows and does not meet these criteria, we exclude it from the sync.
Otherwise, Fivetran re-imports it during every sync using fivetran_id (a hash of all column values) as the primary key.

Defining primary key columns

You can define primary keys in SQL by adding the FPK_ prefix to the column name. For example, SELECT ID1 AS FPK_ID1, ID2 AS FPK_ID2, COL1 FROM TABLE.

If no primary key is defined, we use the _fivetran_id column as the primary key.

Defining incremental columns

You can define incremental columns in SQL by adding the FINC_ prefix to the column name. For example, SELECT LAST_UPDATE_DATE AS FINC_LUD, UPDATED_AT AS FINC_UA, ID AS FPK_ID, COL1 FROM TABLE.

Incremental data models

A data model that defines both primary key and incremental columns supports incremental sync.

Extracting data

1. A wrapper data model with parameters mapped to the incremental columns is created for your data model.
2. A report is then generated to extract the output data from the data model.
3. During the initial (historical) sync, data is extracted from APPLICATION_START_DATE up to the sync start time.
4. In subsequent syncs, only data between the last sync time and the current sync start time is extracted.

Capturing deletes

• A wrapper data model is created to extract all active primary keys from your data model.
• A report is generated to extract the output data.
• During each sync, all active primary key rows are extracted, and records with non-active primary keys are marked as deleted.

Re-import data models

A data model that does not define incremental columns, or defines incremental columns without a primary key. For such a data model, we re-import the data during every sync.

Extracting data

1. A wrapper data model is created to extract all active primary keys.
2. A report is generated to return the output data.
3. During each sync, active primary key values are re-extracted.
4. Any rows missing from the result are marked as deleted.

Schema information

Database tables

The connector syncs the database tables of the FUSION schema available in the Oracle Fusion Database.

All the tables in Oracle BIP are originally part of the FUSION schema. We have split these tables across multiple schemas in our Schema tab and grouped them based on their APPLICATION_SHORT_NAME mentioned in the source. We extract APPLICATION_SHORT_NAME and TABLE_NAME from the FND_TABLES table in the source.

In destination, the schema name would be destinationSchemaPrefix_applicationShortNameInSource. destinationSchemaPrefix is nothing but the schema name set by the user in the setup form.

Custom Data Models

• Data Models inside the folder path will be shown as the tables.
• And its schema will be the Schema name you specify in the setup form.
  In destination, the schema name would be destinationSchemaPrefix_SchemaName.

• If the data models don’t have primary keys, we use _fivetran_id as the primary key, which is a hash of every column.
  Since _fivetran_id is a hash of all columns, column selection or de-selection is not allowed.

Sync notes

• Fivetran only supports UCM for storing the scheduled report data files.
• Fivetran creates, manages, and schedules the BIP report jobs.
• To capture deletes, we use Fivetran Teleport Sync which generates a list of non-active primary key records by comparing the primary key records of the current and previous sync. If the teleport sync fails to calculate the difference, we then re-import the data which is a free re-import.
• For tables or custom data models without a primary key, schema changes on the source side will trigger a MAR, as each row's hash must be recalculated for _fivetran_id.

Database tables

• Fivetran marks the source tables as Excluded By System in the Fivetran dashboard, if the table doesn't have any columns.
• Fivetran syncs a table if it is active and selection is allowed on it.
• Fivetran syncs active columns of the selected tables.

Custom Data Models

• If the Schema change setting is set to Allow all, all data models will be synced from all provided paths.
• If the primary key is changed, a re-sync is required to avoid DI issue. MAR will be charged according to the new pricing (for old pricing re-sync triggered from UI is free), as the rows will be treated as new records. To avoid a MAR spike, create a new data model with the updated primary key and a different model name.

Syncing empty tables and columns

Fivetran can sync empty tables for your Oracle Business Intelligence Publisher connection. For more information, see our Features documentation.

Supported data types

Fivetran supports the following data types for the Oracle Business Intelligence Publisher connector:

• NUMERIC
• DATE
• TIMESTAMP
• CHAR
• VARCHAR