OCI Data Integration pulling data increments using BI Publisher

This blog contains information about pulling incremental data using the DIS Fusion Apps connector for the BI Publisher. It also presents a method of pulling the data and maintaining a control table recording the watermark from the previous pulls.

Two relevant blogs for reference are recommended in companion with this one: Extract Data From Fusion Application Using BI Publisher and OCI DIS provides a good understanding of the BIP connector capability and how to use BIP to write and export simple data from the Fusion Apps. Fusion Incremental Extraction using OCI Data Integration provides similar capabilities but using BI Cloud Connector.

We should carefully consider the BI Publisher usage to extract large amounts of data. As with any reporting interface, the tool is streamlined for data sets that can be visually manipulated as reporting entities. Extensive datasets that don’t make sense for visual retrieval or inspection may perform better using BI Cloud Connector (BICC) extracts.

As most of the proposed solutions for controlling incremental pulls managed by the customer, this solution will leverage the DIS SQL Tasks to find the watermark of prior executions in a control table and synchronize the tasks of reading or updating that with a DIS Data Flow using Pipelines. Here is a peak at the proposed tasks:

• A SQL Task reads (or creates) the “From Date” watermark from a metadata table
• An Integration Task pulls the information from a BIP Data Asset parameterized into an ADW parameterized entity
• Another SQL Task updates the From Date parameter upon the execution completion
• A Pipeline Task sequences the tasks in the order above and is used to pass the incremental parameters among the tasks.

At this point, it is necessary to familiarize yourself with creating a BI Publisher report, as stated in the first reference above.

Creating metadata information

For this solution, connecting with an Oracle or Autonomous DB is necessary. Assuming that connection already exists (or follow this tutorial), below is a sequence of creation steps that creates the metadata and connects the store procedures with the SQL Tasks.

Sometimes is desired to have all the metadata management in a separate schema in the database. For this case, these statements provide a quick way to stand up a new user/schema in an ADW instance using any SQL interface available.

The body of both procedures and the table holding metadata can be produced using the scripts below.

The check_bip_jobrun procedure will attempt the read the value “from date” that was last recorded in the metadata table for an existing pipeline. Not encountered will add a record for the job running pipeline setting the “from date” specified in the procedure call (or sysdate if null). As a result of this template, a default value for the parameter on the SQL Task will pass the 03-FEB-2010 to the “from date” first time.

The procedure will verify whether an existing job runs for the pipeline (is_locked column). If locked, the loop will interrogate the metadata every number of seconds specified by the parameter p_wait_in_seconds. The default value for this template is 120 seconds.

Creating a sample BI Publisher Report

Assuming familiarity with first the reference blog mentioned above, here is a sample query specifying the three parameters needed for this use case: P_LAST_UPDATED_DATE, P_CHUNK_SIZE, P_OFFSET: 

Note the highlighted parameters in the Data Model as appears in the picture:

Once the Data Model is saved and a report is created, proceed to the Dataflow Integration Task creation. Note to enable the CSV format in the Data Model and set the Report to Default that format. Please use the document referenced above for more details on where to find these options within the BI Publisher.

Sample report from the Data Model above:

As good practice, inspecting SQL Execution Plan in the Data Model and verifying the BIP Fusion Apps query will not cause a glaring situation that may impact the Fusion Application underneath database execution. In the example, an execution plan collected from the BI Publisher Data Model would look like this:

DIS Tasks

These are tasks created within the DIS Workspace. Below are the essential screenshots that relate to the configuration of the tasks.

• SQL Tasks

Once the SQL procedure is identified in the SQL Task, the following should be available as the configuration parameters.

Similarly, this can be done for the second procedure.

• DIS Dataflow and Integration Task

On the Dataflow, ensure to specify the BIP properties for the Source Data Asset and fulfill the parameters as below:

For the parameters of the Dataflow, these below are necessary to handle the Store Procedure information and make the Report Name, target Schema, and Entity available at the Pipeline execution later in this example.

Create an Integration Task and have all the task modules above tested individually to ensure they run correctly before starting the Pipeline design. Once the individual tasks are designed, publish them into an Application. This will make the tasks available to be used in a Pipeline design that follows.

• Pipeline and Pipeline Task

A Pipeline will then sequence the DIS tasks. An example follows below.

In this example, the Pipeline will make available to the user the runs the following parameters as below in this figure.

• • P_BIP_REPORT_NAME
  • P_TARGET_SCHEMA – schema in ADW where the data is going the reside
  • P_TARGET_ENTITY_NAME – ADW entity name

A. First SQL Task Parameters configuration:

B. Integration Task parameters configuration:

C. Last SQL Task parameters:

Similarly to the Dataflow, create a Pipeline task and publish it into the Application.

• Simple pipeline execution

The screenshot below reports on a pipeline execution with the characteristics designed above.

And a typical execution of the Pipeline will create a record in the metadata schema control table, as such below.

Conclusions

Carefully approaching a solution to get incremental data from Fusion Application using DIS is a good practice. Measuring the benefits and difficulties posed by using the different DIS Connectors BI Publisher and BI Cloud Connector is the best way to create a stable solution with lesser implications on data processing upstream.

This blog covers some of these considerations and suggests a sequence of tasks to be executed by the Data Integration Service that can pull incremental data while maintaining record of the executions. The tasks suggested here can be extended to incorporate additional functionalities, such as maintaining the number of records pulled on each execution, doing transformations on the data extracted, combining multiple Fusion Application source entities in a query, and defining other target data assets than ADW.

We hope this blog helps you learn about Oracle Cloud Infrastructure Data Integration. For more information, check out the tutorials and documentation. Remember to check out all the blogs on OCI Data Integration!