Oracle Business Intelligence Publisher Beta

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

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

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 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.

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, we re-import them in every sync. As a result, these tables lead to slower syncs and increase your MAR usage.

Schema information

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.

NOTE: Oracle Fusion Cloud Applications connectors use Standard Renaming Rules for schema and table names in the destination.

Schema notes

• Fivetran only supports Universal Content Management (UCM) for storing the scheduled report data files.

• Fivetran creates, manages, and schedules the BIP report jobs.

• Fivetran marks the source tables as Excluded By System in the connector dashboard, if:

  • The table doesn't have any columns.
  • The table doesn't have a primary key column.

• Fivetran syncs a table if it is active and selection is allowed on it.

• Fivetran syncs active columns of the selected tables.

• 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 table which is a free re-import.

Syncing empty tables and columns

Fivetran can sync empty tables for your Oracle Business Intelligence Publisher connector. 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