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

Introduction

In December, 2016 Oracle announced Oracle Data Integrator (ODI) release 12.2.1.2.6 which contains significant new features including support for 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.

This post 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 a 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.

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

This post contains the following main sections:

Preparation Prior to Using ODI

Using ODI to call the RESTful Service

Useful Information

The following contains information on the REST API used and API testing tools.

About the OFSC REST API

The document REST API for Oracle Field Service Cloud Service should be used extensively, especially the Authentication, Paginating, and Working with Events sections. Terms described there such as subscription, page, and authorization are used in the remainder of this post.

This post uses the Events end point and specifically uses only Activity events.

In order to receive events, a subscription is needed listing the specific events desired. The creation of a subscription returns both a subscription ID and a page number to be used in subsequent REST calls to receive events. At this time, a page contains 0 to 100 items (events) along with the next page number to use in a subsequent call.

Using API Testing Tools

The REST requests should be developed in API testing tools such as cURL and Postman. Links to these tools are provided in the References section.

Note: API testing tools such as SoapUI, CuriousConcept, Postman, and so on are third-party tools for using SOAP and REST services. Oracle does not provide support for these tools or recommend a particular tool for its APIs. You can select the tool based on your requirements.

Preparation Prior to Using ODI

The following are steps taken prior to using the ODI application.

Encoding User Credentials

This post uses the Basic framework for authorization and mandates a base64 encoded value for the following information: user-login “@” instance-id “:” user-password. A handy website is the Base64 Decoding and Encoding Testing Tool. Use this tool to encode the user credentials and instance. The result of the exact string above is dXNlci1sb2dpbkBpbnN0YW5jZS1pZDp1c2VyLXBhc3N3b3Jk 

Subscribing to Receive Events

A subscription is required prior to receiving events. A subscription specifies the types of events that you want to receive. Multiple subscriptions are recommended. However, only one is used in this post. A subscription should only contain events that have the same response fields e.g. All Activity events return the same set of fields.

The OFSC REST API document describes how to subscribe using a cURL command. Postman can also easily be used. Below is a sample cURL request to create a subscription:

cURL -X POST -H “Authorization: Basic dXNlci1sb2dpbkBpbnN0YW5jZS1pZDp1c2VyLXBhc3N3b3Jk” -d ‘{
“events”: [
“activityCreated”,
“activityUpdated”
]
}’ “https://OFSC-hostname//rest/ofscCore/v1/events/subscriptions”

Either tool provides a response as shown below:

{
“subscriptionId”: “a0fd97e62abca26a79173c974d1e9c19f46a254a”,
“nextPage”: “170321-2211,0”,
“links”: [ … omitted for brevity ]
}.

Note: The default nextPage is for events that occur after the subscription is created.

Identifying the First Page Number

The OFSC REST API document describes how to use the since parameter to obtain the first page number created for a specific subscription after a specific date and time.

The following cURL statement retrieves the first page number created after March 1, 2017 for the subscription created above.

cURL -X GET -H “Authorization: Basic dXNlci1sb2dpbkBpbnN0YW5jZS1pZDp1c2VyLXBhc3N3b3Jk” -d “https:// OFSC-hostname//rest/ofscCore/v1/events?subscriptionId=a0fd97e62abca26a79173c974d1e9c19f46a254a&since=2017-03-01%2000:00:00″

An example response is below.

{
“found”: true,
“nextPage”: “170301-3,0”,
“links”: [ … ]
}

Note: The nextPage value above is used as the first date extracted in a full load.

Using ODI to call the RESTful Service

This section creates a topology to define and test the REST API call to the OFSC service and also creates a step in an ODI package to define and test the creation of the JSON response file.

This section contains the following sub-sections:

Preparing the REST Topology

Preparing a REST Package Step

Preparing the REST Topology

A topology is required that includes a physical data server, a physical schema, and a logical schema. Note: an ODI Model is not required.

Creating a REST Data Server

This server details the URL of the OFSC service.

Use the Topology tab of ODI Studio. Expand Technologies. Right-Click on RESTful Service Technology. Click New Data Server.

In the Definition pane:

Enter a Name e.g. OFSC REST Data Server

Enter a REST Service endpoint URL e.g. https://<OFSC-URL>.com

I-1

Click Test Connection

An ODI-1168 Authentication Failed … warning is acceptable. This warning occurs because the Authorization header is not sent with the request. Resolve the cause of any other errors before proceeding.

If the error is related to a missing SSL certificate, refer to section 11.10.1 Creating an SSL Certificate in Oracle® Fusion Middleware Administering Oracle Data Integrator 

Click or Choose Save. Close all open tabs.

Creating a REST Physical Schema

This schema details the resource URI and the operation used to receive events.

In the left pane, Right-Click on the Data Server just created and Click New Physical Schema.

In the Definition pane:

Enter the Resource URI e.g. /rest/ofscCore/v1/events

1-2

In the Operations pane, Click plusSign  to add an operation.

Enter a Name e.g. getEvents

Enter a Method e.g. GET from the dropdown.

The complete Operation pane is below. Individual parameter details follow:

1-5

Enter the Query Parameter

Click on the symbol to the right of the box

Click plusSign twice to add two query parameters:

The first Name is page. Because the Value changes before different pages are retrieved enter a template parameter placeholder e.g. {pageNo}

The second Name is subscriptionId and the Value is the subscription ID obtained in the Subscribing to Receive Events step.

Click OK

1-3

Enter the Header Parameter

Click on the symbol to the right of the box

Click plusSign to add a parameter:

Enter Authorization as the Name

Enter the Value as the string ‘Basic ‘ concatenated with the encoded credentials created in the Encoding User Credentials section e.g. Basic dXNlci1sb2dpbkBpbnN0YW5jZS1pZDp1c2VyLXBhc3N3b3Jk

Click OK

1-4

Enter the Template Parameter

Click on the symbol to the right of the box

Click plusSign  to add a parameter:

Enter the Name as used above e.g. pageNo

Enter the Value as the nextPage received from the Identifying the First Page Number step e.g. 170301-3,0

Click OK

In the Context pane, Click plusSign  to add a context

Choose a Context from the dropdown e.g. Global. 

Note: If it is possible to not choose a context, leave it blank so that it may be specified at run time. At the time of this writing, it is not possible to leave it blank and also enter the logical schema below.

Enter the Logical Schema e.g. OFSC REST Logical Schema

Click or Choose Save All

1-6

In the Operations pane, Click Test Restful Service

Click Send Request. You should see valid Response Content returned. If not, copy the Request URL into an API testing tool to determine and resolve the failure.

Click OK

Click or Choose Save. Close all open tabs.

The completed Schema operation pane:

1-9

Preparing a REST Package Step

This step defines the JSON response file location and name.

From the Designer tool of ODI Studio, Create a Project.

In the Definition pane:

Enter a Name e.g. OFSC_Project

Click or Choose Save.

Expand the Project and Expand the Folder created with the project

Right-Click on Packages and Click New Package.

Give it a Name e.g. OFSC_Package.

From the Toolbox, Double-Click or Drag the OdiInvokeRestfulService tool onto the Package canvas.

Click on the step in the canvas to select it.

1-7

Enter Properties on the General tab Parameter section as follows:

Change the Step Name e.g. InvokeRESTfulService

Use the dropdown to Select the Context e.g. Global

Use the dropdown to Select the Logical Schema e.g. OFSC REST Logical Schema

Use the dropdown to Select getEvents for Main Operation

Enter a Response File path and file name. e.g. <C:\<path>\ OFSC_Page.json

Click or Choose Save.

1-8

Click Test Restful Service.

Click Send Request. Click OK.

Click  to execute the step. 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 stores the JSON response in the Response File specified.

Click or Choose Save. Close all open tabs.

Summary

Part I of this three-part series detailed a method of extracting data from Oracle Field Service Cloud (OFSC) using ODI and a REST API. The method extracted a JSON-formatted response and placed it in a file.

Part II of this series uses the file to load the response into 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.

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 I

ODI Creating an SSL Certificate

REST API for Oracle Field Service Cloud Service

Postman Testing Tool

Base64 Decoding and Encoding Testing Tool

 

Add Your Comment