Introduction

In December, 2016 Oracle announced Oracle Data Integrator (ODI) release 12.2.1.2.6 which contains significant new features including RESTful services. This three-part series details a method of extracting and loading data from Oracle Field Service Cloud (OFSC) into Oracle Analytics Cloud (OAC).

Part I details Using ODI to call the RESTful Service which calls the Cloud Service API and produces a file containing the JSON response.

Part II details Using ODI to Load the JSON Response which sources the file containing the JSON response and populates an OAC staging table.

This post details Controlling the Process Flow which controls the load type (Full | Incremental), performs both the extraction and loading procedures, determines when the last page of data has been retrieved and finishes the process.

Links to all documents used, including a smart export of the ODI components, are in the References section at the end of the post.

On May 19th, 2017 this topic was presented in an ODI Webcast. This link takes you to the archive, ODI Webcast Archive , for the recording.

Note: These ODI methods are for demonstration purposes only and are not intended for enterprise production use.

Controlling the Process Flow

This section defines the variables and package steps to orchestrate the extraction from OFSC and the loading into OAC. It contains the following sub-sections:

Determining the Load Type and Starting Page

Preparing the Initial RESTful Call

Loading JSON Responses into OAC

Determining the Last Page Condition and the Next Page

Adding a Next RESTful Service Step

Ending the Process Flow

Testing the Package

Determining the Load Type and Starting Page

In this section, the load type (FULL | INCREMENTAL) and the starting page number are retrieved from the OFSC_SUBSCRIPTION_PAGE table populated in the Part 1 - Inserting the Subscription section. A full load truncates the OAC staging table and inserts all available pages starting with the FIRST_PAGE column identified in the Part I - Identifying the First Page Number section. An incremental load appends all available pages starting with the NEXT_PAGE column from the previous load process.

Creating Two Variables

One for the run type and one for the starting page number.

Expand Projects and your project in the Designer tab.

Right-Click Variables and Click New Variable.

In the Definition pane, Enter a Name e.g. OFSC_Run_Type.

In the Refreshing pane:

Use the dropdown to select the OAC Schema e.g. OAC_OFSC_USER

Enter the Select Query as

Select Load_Type From Ofsc_Subscription_Page

Click  to validate the query.

Click 

Right-Click Variables and Click New Variable.

In the Definition pane, Enter a Name e.g. OFSC_Start_Page

In the Refreshing pane:

Use the dropdown to select your OAC Schema e.g. OAC_OFSC_USER

Enter the Select Query as

Select First_Page From Ofsc_Subscription_Page Where Load_Type='Full'
Union
Select Next_Page From Ofsc_Subscription_Page Where Load_Type='INCREMENTAL'

Click  to validate the query.

Click 

Adding the Variables to the Package.

Expand the Project folder.

Expand Packages.

Double-Click the package created in the Part 1 - Preparing a REST Package Step section e.g. OFSC_Package to open it.

Drag both variables to the package canvas. Place Run Type above Start Page.

For both variables:

Click on the variable to select it.

Add Refresh to the Step name in the General tab of the Properties pane e.g. Refresh OFSC_Run_Type. Note: the default value for Type is Refresh Variable.

Right-Click on the Refresh Run Type step and Click First Step.

In the Advanced tab of the Properties pane,

            In the Processing after success section,

    Check the Execute Step button.

    Use the dropdown and Select the Refresh Start Page step.

Click 

Drag the Run Type variable again onto the package canvas below the Refresh Start Page step.

Click on the variable to select it.

Add Evaluate to the Step name in the General tab of the Properties pane e.g. Evaluate OFSC_Run_Type.

Use the dropdown to Change the Type to Evaluate Variable.

Enter FULL as the Value.

Click on the Refresh Start Page step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section,

    Check the Execute Step button.

                            Use the dropdown and Select the Evaluate Run Type step.

Click 

Preparing the Initial REST Call

In this section, the initial REST call uses the values of the Start Page and Run Type variables. The Run Type variable is evaluated before the first call and the OAC staging table is truncated if the value is FULL.

Creating a Procedure to Truncate the OAC Staging Table

This procedure truncates the OAC staging table. It is used for full loads.

Right-Click on Procedures and Click New Procedure.

In the Definition pane:

Enter a Name e.g. Truncate OFSC Staging Table

In the Tasks pane:

Click  to add a task.

Enter a Task Name e.g. Truncate

Enter a Target Command e.g.

Truncate Table OFSC_EVENT_ACTIVITY Drop Storage

Use the dropdown to select Oracle for Target Technology

Using the menu bar, Click Window > Properties.

In the Target Command pane:

Use the dropdown to select your Logical Schema e.g. OAC_OFSC_USER

Click  Close Truncate-Properties

Adding the Truncate Procedure to the Package

Drag the Truncate procedure to the package canvas below the Evaluate Run Type step.

Click on the Evaluate Run Type step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section:

    Check the Execute Step button.

                            Use the dropdown and Select the Truncate OFSC Staging Table step.

Connecting the Initial REST Call

The Initial REST Call step was added to the package in the Part 1 - Preparing a REST Package Step section. It requires the value of the Start Page variable.

Click on InvokeRESTfulService step to select it.

In the Properties > General tab > Parameter section:

Use the ellipse icon to Change the Value of Request Template parameter

Click  to add a parameter.

Enter pageNo as the Name.

Enter the Value as the Start Page variable name preceded by a pound (#) sign e.g. #OFSC_Start_Page

Click OK

Click 

Move the InvokeRESTfulService step to the right of the Evaluate Run Type step.

Click on the Evaluate Run Type step to select it.

In the Advanced tab of the Properties pane:

In the Processing after failure section:

    Check the Execute Step button.

                            Use the dropdown and Select the InvokeRESTfulService step.

Click on the Truncate OFSC Staging Table step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section:

    Check the Execute Step button.

                            Use the dropdown and Select the InvokeRESTfulService step.

Loading JSON Responses into OAC

This section details creating the procedure to convert and load the JSON response file and then details adding the conversion procedure and mapping to the package.

Creating a Procedure to Load the JSON file

This procedure uses the RESTful topology to explicitly load the contents of the JSON file created by the RESTful service call and store the contents in the XML related tables in the target OAC database.

Right-Click on Procedures and Click New Procedure.

In the Definition pane:

Enter a Name e.g. Load JSON File Note: In the screen shots below the Load JSON File procedure is shown as Convert JSON File.

In the Tasks pane:

Click  to add a task.

Enter a Task Name e.g. Synchronize

Enter a Target Command e.g. SYNCHRONIZE SCHEMA <schema name> FROM FILE.

If your XML schema name is OFSC_XML then the command is:

SYNCHRONIZE SCHEMA OFSC_XML FROM FILE

Use the dropdown to select Complex File for Target Technology

Using the menu bar, Click Window >Properties.

In the Target Command pane:

Use the dropdown to select your Logical Schema e.g. OFSC DATA SERVER.OFSC_XML

Click  and Close the tab.

In the Tasks pane:

Click  to add a second task. It is a best practice to commit the step explicitly.

Enter a Task Name e.g. Commit

Enter a Target Command e.g. COMMIT

Use the dropdown to select Complex File for Target Technology

Using the menu bar, Click Window  > Properties.

In the Target Command pane:

Use the dropdown to select your Logical Schema e.g. OFSC DATA SERVER.OFSC_XML

Click  and Close the tab.

Adding the JSON Load Procedure to the Package

Double-Click on your package e.g. OFSC_Package to open it.

Drag the Load JSON File procedure to the package canvas to the right of the InvokeRESTfulService step.

Click on the InvokeRESTfulService step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section:

    Check the Execute Step button.

                            Use the dropdown and Select the Load JSON File step.

Click  

Adding the Mapping to the Package

This adds the mapping that will load the OAC staging table from the XML related tables.

Expand the Project Folder.

Expand Mappings.

Drag the mapping e.g. OFSC Mapping to the package canvas to the right of the Load JSON File step.

Click on the Load JSON File step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section:

    Check the Execute Step button.

                            Use the dropdown and Select the OFSC Mapping step.

Determining the Last Page Condition and the Next Page

This section creates two variables used to identify when the last page of data is reached and the next page of data to extract. The last page variable is first refreshed and then evaluated. If the last page has not been reached then the next page variable is refreshed.

Creating Two Variables

Expand Projects and your project in the Designer tab.

Right-Click Variables and Click New Variable.

In the Definition pane:

Enter a Name e.g. OFSC_Last_Page.

Use the dropdown to Select Numeric for the Datatype.

In the Refreshing pane:

Use the dropdown to select the OAC Schema e.g. OAC_OFSC_USER

The two tables used for the select query are <XML_Schema>_<Root_Element> and <XML_Schema>_Links. The SQL is similar to that used in the mapping to obtain the current page number and the next page number.  When they are the same, the last page has been reached.

Enter the Select Query as

SELECT CASE WHEN T.CNT = 0 THEN 0 ELSE 1 END LAST_PAGE
FROM
(
SELECT COUNT(*) CNT
FROM
OFSC_XML_OFSC INNER JOIN OFSC_XML_LINKS
ON OFSC_XML_OFSC.OFSCPK = OFSC_XML_LINKS.OFSCFK AND OFSC_XML_LINKS.REL = 'canonical'
WHERE OFSC_XML_OFSC.NEXTPAGE = SUBSTR( OFSC_XML_LINKS.HREF, INSTR(OFSC_XML_LINKS.HREF,'=',1,2)+1)
) T

Click  to validate the query.

Click 

Right-Click Variables and Click New Variable.

In the Definition pane, Enter a Name e.g. OFSC_Next_Page

In the Refreshing pane:

Use the dropdown to select your OAC Schema e.g. OAC_OFSC_USER

Enter the Select Query as

SELECT OFSC_XML_OFSC.NEXTPAGE FROM OFSC_XML_OFSC

Click  to validate the query.

Click 

Adding the Variables to the Package.

Double-Click the package e.g. OFSC_Package to open it.

Drag the Last Page variable to the package canvas to the right of the OFSC Mapping step.

Click on the Last Page variable to select it.

Add Refresh to the Step name in the General tab of the Properties pane e.g. Refresh OFSC_Last_Page.

Click on the OFSC Mapping step to select it.

In the Advanced tab of the Properties pane,

            In the Processing after success section,

    Check the Execute Step button.

    Use the dropdown and Select the Refresh Last Page step.

Click 

Drag the Last Page variable again onto the package canvas to the right of the Refresh Last Page step.

Click on the variable to select it.

Add Evaluate to the Step name in the General tab of the Properties pane e.g. Evaluate OFSC_Run_Type.

Use the dropdown to Change the Type to Evaluate Variable.

Enter 1 as the Value.

Click on the Refresh Last Page step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section,

    Check the Execute Step button.

                            Use the dropdown and Select the Evaluate Last Page step.

Click 

Drag the Next Page variable to the package canvas below the Evaluate Last Page step.

Click on the Next Page variable to select it.

Add Refresh to the Step name in the General tab of the Properties pane e.g. Refresh OFSC_Next_Page.

Click on the Evaluate Last Page  step to select it.

In the Advanced tab of the Properties pane,

            In the Processing after failure section,

    Check the Execute Step button.

    Use the dropdown and Select the Refresh Next Page step.

Click 

Adding a Next RESTful Service Step

This step calls the OFSC service again using the Next Page variable. It then proceeds to the Load JSON File step.

Right-Click the InvokeRESTfulService step and Click Duplicate Step

Move the copy under the Load JSON File step

Click on the copy to select it.

Change the Step Name on the General Properties pane e.g. Next RESTful Call

Enter Values on the General tab Parameter section as follows:

For Request Template, use the ellipse icon to Change the Value of pageNo to #OFSC_Next_Page

Click OK

Click on the Refresh Next Page step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section,

    Check the Execute Step button.

                            Use the dropdown and Select the Next RESTful Call step.

Click on the Next RESTful Call  step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section,

    Check the Execute Step button.

                            Use the dropdown and Select the Load JSON File step.

Ending the Process Flow

This section creates a procedure to update the OAC Subscription table with the last page number, when the last page has been reached, and adds the procedure to the package. The process flow then ends.

Creating a Procedure to Update the Subscription Table

This procedure issues an update statement to update the OAC Subscription table with the last page number extracted and to set the next load type to Incremental. This is the starting page number for the next incremental load.

Right-Click on Procedures and Click New Procedure.

In the Definition pane:

Enter a Name e.g. OFSC Update Subscription Table

In the Tasks pane:

Click  to add a task.

Enter a Task Name e.g. Update

Note: The table used for next page is named <XML_Schema>_<Root_Element>

Enter a Target Command e.g.

UPDATE OFSC_SUBSCRIPTION_PAGE
SET NEXT_PAGE = (SELECT NEXTPAGE FROM OFSC_XML_OFSC),
LOAD_TYPE = 'INCREMENTAL'

Use the dropdown to select Oracle for Target Technology

Using the menu bar, Click Window  > Properties.

In the Target Command pane:

Use the dropdown to select your Logical Schema e.g. OAC_OFSC_USER

Click  and Close the tab.

In the Tasks pane:

Click  to add a second task. It is a best practice to commit the step explicitly.

Enter a Task Name e.g. Commit

Enter a Target Command e.g. COMMIT

Use the dropdown to select Oracle for Target Technology

Using the menu bar, Click Window >Properties.

In the Target Command pane:

Use the dropdown to select your Logical Schema e.g. OAC_OFSC_USER

Click  and Close the tab.

Adding the Update the Subscription Table Procedure to the Package

Double-Click on your package e.g. OFSC_Package to open it.

Drag the Update Subscription Table procedure to the package canvas to the right of the Evaluate Last Page step.

Click on the Evaluate Last Page step to select it.

In the Advanced tab of the Properties pane:

In the Processing after success section:

    Check the Execute Step button.

                            Use the dropdown and Select the Update Subscription Table step.

Click  

Testing the Package

This section tests the package. Correct any errors encountered.

Click  to run the package.

Click the Operator tab on the left pane. Expand All Executions. Click  to refresh if necessary. The top execution is yours and should have the  mark.

A successful execution determines the load type and starting page number, extracts and loads all pages until the last page is reached, and then updates the subscription table with the Next Page number to end the process.

Summary

This part of the three-part series detailed a method of controlling the process flow for full and incremental loads. The method used variables, RESTful service calls, procedures and a mapping to extract pages from an OFSC web service using the REST API and load them into an OAC staging table.

Part I details Using ODI to call the RESTful Service which calls the Cloud Service API and produces a file containing the JSON response.

Part II details Using ODI to Load the JSON Response which sources the file containing the JSON response and populates an OAC staging table.

For more BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS.

References

ODI Smart Export – Part III