Loading Data from Oracle Field Service Cloud into Oracle Analytics Cloud using ODI and REST – Part II

 

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). It is a companion to the A-Team post Loading Data from Oracle Field Service Cloud into Oracle BI Cloud Service using REST and offers a method to complement the standard OFSC Daily Extract described in Oracle Field Service Cloud Daily Extract Description.

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

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

Part III details Controlling the Process Flow which controls the load type (Full | Incremental) and determines when the last page of data has been retrieved.

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.

Preparation Prior to Using ODI

This post uses Oracle Database as a Service (DBaaS) for the ODI repository, transformation tables and the OAC staging table.

Creating an OAC User and Schema Objects in the Database

An OAC user and two tables are created.

The two tables are:

OFSC_EVENT_ACTIVITY for OFSC Event data

OFSC_SUBSCRIPTION_PAGE for subscription information

The complete SQL to prepare the DBaaS may be viewed here.

Encrypting the OAC User Password

The JDBC driver associated with the ODI Complex File Technology needs an encrypted password to connect to the same database and schema as the OAC staging table in order to store the XML related tables that will hold the contents of the JSON response file. Make a note of it for use in that section.

To encrypt the password, the name of an ODI agent is required e.g. OracleDIAgent1

From a terminal window on Linux or a CMD window on Windows, change to the bin directory of the ODI domain of the agent e.g. <domain-home\bin>.

Run the encode (cmd|sh) script with the agent name and the OAC user password. For example, encode -INSTANCE=OracleDIAgent1 Password, returns njiFXjQ9YjunsCyRS57ECQ==

Inserting the Subscription

Use SQL*Plus or SQL Developer to insert a row into the OFSC_SUBSCRIPTION_PAGE table. This table is used to store the first page number and the next page number for each subscription. The first page number is used as the starting page in a full load. The next page number is used as the starting page in an incremental load.

Use the following values from the Part I – Subscribing to Receive Events section for the insert:

subscriptionId for the SUBSCRIPTION_ID.

nextPage for the NEXT_PAGE column.

Use the nextPage value from the since request in the Part I – Identifying the First Page Number for the FIRST_PAGE column.

Enter Full as the LOAD_TYPE. Note: After a full load, the column can be updated to Incremental.

An example insert statement is here

Note: After a successful load, the NEXT_PAGE column in this table is updated with the nextPage value from the last page extracted.

Using ODI to Load the JSON Response

ODI internally transforms the JSON response into XML and exposes the XML as relational tables. These tables are then used via SQL to populate the OAC staging table.

This section contains the following sub-sections:

Preparing the Complex File Topology

Preparing the Oracle Topology

Preparing a Model of the Oracle Schema

Preparing a Mapping to Load the Data

Preparing the Complex File Topology

This topology defines how ODI converts a file containing JSON content into an XML definition and then into relational tables.

This section contains the following sub-sections:

Preparing a Sample JSON File

Creating a Complex File Data Server

Creating a Complex File Physical Schema

Preparing a Sample JSON File

Prepare a sample file that contains an OFSC page of JSON data. This file is used to create the nXSD XML definition. A Sample File that you may start with is included here

Note: It is recommended that all fields in the sample file have a value of String. Further transformation, after the loading the staging table, assigns proper data types. The values of the fields in the Sample file are not important, only the names and a value as the datatype e.g. string.

Note: OFSC populates up to a set maximum (currently 100) number of events (items) into a page. If using your own file, remove all but one item from the file. Leave the items outside of the array as is e.g. Links

Note: All fields you want loaded either from the REST API for Oracle Field Service Cloud Service document or any additional custom fields must be specified in the sample file or they are not available to load into OAC. It is unlikely any one page has all the fields so you need to manually insert any that are missing into the sample file.

Validate the contents by placing them into a JSON testing tool such as CuriousConcept. Use a period e.g. “.” without the quotes as the expression and test. When results are returned correctly, the JSON data is valid.

Creating a Complex File Data Server

This server details the conversion of the JSON response file into a relational XML set of tables.

Use the Topology tab of ODI Studio. Right-Click on Complex File Technology. Click New Data Server.

In the Definition pane, enter a Name: e.g. OFSC Data Server

In the JDBC pane:

Enter the JDBC Driver: oracle.odi.jdbc.driver.file.complex.ComplexFileDriver

Enter the JDBC URL: jdbc:snps:complexfile?f=drive:\filepath\filename  Note: use the file name of the JSON file created in Part 1 of this series e.g. <C:\<path>\ OFSC_Page.json

2-1

 Click Edit nXSD to the right of the JDBC URL to start the Native Format Builder

On the Welcome Screen, Click Next.

On page 1 of 4, enter the File Name e.g. OFSC_schema.xsd and the Directory Name and Click Next

2-2

On page 2 of 4, Check the JSON Interchange Format box and Click Next

2-3

On page 3 of 4,

For the File Name, enter the file name of the sample JSON file created above e.g. Sample_OFSC_One_Page.json

For the Root Element, enter a value of your choice e.g. OFSC and Click Next

2-4

On page 4 of 4, examine the XSD and Click Test.

Click the greenArrow button to view the JSON response converted to XML format and then Click OK

Click Next

On the Finish screen, Click Finish

Click or Choose Save. The XSD file created from the sample JSON may be viewed here

In the JDBC Properties section:

Note: In this section, you decide whether the XML relational representation is created in the ODI In-Memory database or in an external database. Choosing the same database as the OAC staging table simplifies the run-time SQL that ODI generates. This post uses the same external database as the OAC staging table.

Enter a non-default Value for the properties shown in the table below:

Category Property Value
XML Properties dtd The directory and file name of the nXSD file created above.
  root_elt The root element name chosen in the nXSD section above e.g. OFSC
  schema Choose a schema name e.g. OFSC_XML. Make a note of it as it is used is several upcoming sections.
Complex File Properties translator_type Select  json from the dropdown
External DB Properties dp_driver oracle.jdbc.OracleDriver for an Oracle database
  dp_numeric_length Increase to 14 
 
dp_password The encrypted password for the OAC User created in the Encrypting the OAC User Password section above
  dp_schema The OAC username/schema in the target database.
  dp_url The JDBC URL for the database e.g. jdbc:oracle:thin:@localhost:1521/pdb1
  dp_user The OAC username/schema in the target database.
  dp_varchar_length Increase to 2048 to match staging table lengths.
 
 

The various properties are pictured below:

2-5

2-6

2-7

Click or Choose Save.

Creating a Complex File Physical Schema

Use the Topology tab of ODI Studio. Expand Complex File. Right-Click on the Data Server just created and Click New Physical Schema.

In the Definition pane:

Use the dropdown for Schema (Schema) and select the XML schema name entered in the Data Server properties above e.g. OFSC_XML.

Use the dropdown for Schema (Work Schema) and select the same.

2-8

In the Context pane:

Click plusSign  to add a context.

Choose a context from the dropdown e.g. Global

Enter a name for the Logical Schema e.g. OFSC Data Server.OFSC_XML

2-9

Click or Choose Save.

From the Data Server tab, Click Test Connection in the upper left corner of the pane. After a successful test, the tables related to the XML representation are created in the target OAC schema.

Click or Choose Save. Close all open tabs.

Preparing the Oracle Topology

This topology defines the target OAC database and schema.

This section contains the following sub-sections:

Creating an Oracle Data Server

Creating an Oracle Physical Schema

Creating an Oracle Data Server

Use the Topology tab of ODI Studio. Right-Click on Oracle Technology. Click New Data Server.

In the Definition pane:

Enter a Name e.g. OFSC Oracle Data Server

For the Connection:

            Enter the OAC User e.g. OAC_OFSC_USER

            Enter the Password (the unencrypted password)

2-12

In the JDBC pane:

For the JDBC Driver, ensure the value is oracle.jdbc.OracleDriver.

Modify the JDBC URL to reflect the OAC target database e.g.  jdbc:oracle:thin:@localhost:1521/pdb1

2-11

Click or Choose Save.

Creating an Oracle Physical Schema

Use the Topology tab of ODI Studio. Expand the Complex File Technology. Right-Click on the data server just created and Click New Physical Schema.

In the Definition pane:

Use the dropdown for Schema (Schema) and select the schema name that matches the OAC user name in the target database e.g. OAC_OFSC_USER.

Use the dropdown for Schema (Work Schema) and select the same.

2-12

In the Context pane:

Click  to add a context.

Choose a context from the dropdown e.g. Global

Enter a name for the Logical Schema (it can also be the OAC user name) .

2-13

From the Data Server tab, Click Test Connection in the upper left corner of the pane.

Click or Choose Save. Close all open tabs.

Preparing a Model of the Oracle Schema

The model details the actual tables and columns stored in the OAC target database and is used to construct the mapping that loads the data.

Use the Designer tab of ODI Studio. To the right of Models. Click newModelNew Model.

In the Definition pane:

Enter a Name e.g. OFSC_Model

Use the dropdown for Technology and select Oracle. Note: Ignore the ODI-26174 Database connection is not available error at this point if it occurs.

Use the dropdown for Logical Schema and select the name associated with the OAC user name e.g. select the schema name that matches the OAC user name e.g. OAC_OFSC_USER.

Use magGlass to select the Project and Folder.

2-14

In the Reverse Engineer pane, Click rEngineer to reverse engineer the objects from the database schema.

Expand the model in the left pane under Models and see the XML related tables as well as the two tables created in the Creating an OAC User and Schema Objects in the Database section.

2-15

Click or Choose Save. Close all open tabs.

Preparing a Mapping to Load the Data

The mapping declares the joins and filters for the XML tables and then maps the output columns to the OAC staging table.

From the Designer tab of ODI Studio, Open your Project. Expand the Folder created with the project, Right-Click on Mappings and Click New Mapping.

Give it a Name e.g. OFSC Mapping.

Uncheck Create Empty Dataset if it is checked.

From the Designer tab: Expand the Models folder; Expand your model.

Identify the XML related tables. They are named <XML schema name>_<XML element name>. They begin with the value of the XML schema property set in the Creating a Complex File Data Server section above e.g. OFSC_XML. Do not use tables that begin with OFSC_XML_SNP* as they are ODI internal tables.

Drag these tables to the canvas.

Click the layouticon to arrange the tables.

The Components pane should be visible. If not, from the menu bar Click Window>Components.

Drag the join component to the canvas.

Connect each table to the Join component. Click OK if prompted on each connection.

Drag the OFSC_EVENT_ACTIVITY table to the right of the Join component.

Connect the Join component to it.

Accept the defaults on the Attribute Matching window and Click OK.

Click the layouticon to arrange the tables.

Your Mapping should look like this:

2-16

Click the Join component to focus on it.
Using the menu bar, Click Window > Properties to see Join – Properties.
On the Join – Properties Condition pane:

Uncheck the Generate ANSI Syntax box.

Hover over the Join Condition and Click on the gear  icon.

Use the dropdown to change the SQL type to SQL (Generic SQL). 

In the Edit Join Condition for “JOIN” box, enter the following SQL:

OFSC_XML_OFSC.OFSCPK =  OFSC_XML_LINKS.OFSCFK AND

OFSC_XML_OFSC.OFSCPK =  OFSC_XML_ITEMS.OFSCFK(+) AND

OFSC_XML_ITEMS.ITEMSPK =  OFSC_XML_ACTIVITYDETAILS.ITEMSFK(+) AND

OFSC_XML_ITEMS.ITEMSPK =  OFSC_XML_ACTIVITYCHANGES.ITEMSFK(+) AND

 OFSC_XML_LINKS.REL = ‘canonical’

Click OK.

Note: This SQL describes the inner and outer joins for the tables as well limiting the rows in the Links table to just the ‘canonical’ row.

2-17

Close the Join – Properties tab.

Click the OFSC_EVENT_ACTIVITY table to select it.

Using the menu bar, Click Window > Properties.

Expand Attributes. For each attribute without an expression enter the corresponding expression from the following table:

 

Name Expression
PAGE_NUMBER SUBSTR(  OFSC_XML_LINKS.HREF , INSTR(OFSC_XML_LINKS.HREF,’=’,1,2)+1)
TIME OFSC_XML_ITEMS.TIME
EVENT_RESOURCE_ID OFSC_XML_ACTIVITYDETAILS.RESOURCEID
EVENT_DATE OFSC_XML_ACTIVITYDETAILS.DATE_
EVENT_APPT_NUMBER OFSC_XML_ACTIVITYDETAILS.APPTNUMBER
EVENT_CUST_NUMBER OFSC_XML_ACTIVITYDETAILS.CUSTOMERNUMBER

 

2-18

Close the OFSC_EVENT_ACTIVITY – Properties tab.

Click greenCheck  to validate the mapping. Ensure there are no errors. A few warnings are probably OK.

Click greenArrow  to run the mapping. Click OK and OK again.

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

A successful execution reads the XML related tables and loads the Target OAC staging table.

Click or Choose Save. Close all open tabs.

Summary

This part of the three-part series detailed a method of using ODI to load a JSON response file into the OAC staging table. The method converted the JSON response into a set of XML related tables and then used these tables to load the OAC staging table.

This post detailed Using ODI to Load the JSON Response which sources the file containing the JSON response and populates 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 III completes the ODI package and controls the process flow for full and incremental loads.

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 II

CuriousConcept JSON Testing Tool

Add Your Comment