X

Best Practices from Oracle Development's A‑Team

Using Oracle Data Integrator Cloud Service (ODICS) to Integrate Field Service Cloud with Business Intelligence Cloud Service (BICS): An ODICS Perspective

Introduction

 

This article discusses how to use Oracle Data Integrator Cloud Service (ODICS) to integrate Oracle Field Service Cloud (FSC) with Business Intelligence Cloud Service (BICS).  ODICS provides pushdown data processing with high performance ETL for the Cloud. ODICS executes data transformations where the data lies without having to unnecessarily copy data to remote locations.

A video demonstration of how to use ODICS to integrate OFSC with BICS can be found on the following Oracle Data Integration Webcast page: Using Oracle Data Integrator Cloud Service (ODICS) to Integrate Field Service Cloud (FSC) with Business Intelligence Cloud Service (BICS). The examples provided in this article can be used as a recipe to integrate other cloud services or SaaS applications with ODICS.

ODICS offers out-of-the-box integration with many on-premises technologies such as Oracle, Teradata, MySQL, Files, Big Data, and many others.  Some of these technologies are now available as data cloud services, and ODICS can integrate with these cloud services as well.    Software as a Service applications (SaaS) provide a new set of opportunities for customers that would like to subscribe to application services on the Cloud, and ODICS can also provide integration with many of these SaaS applications.

Figure 1, below, illustrates the capabilities of ODICS.  On the Oracle Public Cloud (OPC), ODICS is provisioned on the Java Cloud Service (JCS).  Within the OPC, ODICS can integrate with other cloud services such as Business Intelligence Cloud Service (BICS), Database Cloud Service (DBCS), and Big Data Cloud Service (BDCS), among others.  When using the VPN options of the Oracle Network Cloud Service, ODICS can upload data from cloud data services into on-premises data servers, as illustrated on Figure 1 below.

 

Figure 1 – Integrating On-Premises Data Servers & Cloud Services with ODICS

Figure 1 – Integrating On-Premises Data Servers & Cloud Services with ODICS

 

ODICS can integrate with Oracle SaaS applications such as Marketing Cloud, Sales Cloud, and Field Service Cloud.  The integration of ODICS is not limited to Oracle SaaS applications – ODICS can integrate with non-Oracle SaaS applications such as Microsoft Dynamics CRM, Salesforce Cloud, Google Analytics, and many others.  These integrations are also illustrated on Figure 1, above.

When integrating cloud services and SaaS applications with ODICS, two methods can be used:  web services or prepackaged solutions.  When users subscribe to a cloud service or a SaaS application, the underlying data of the service or application can be accessed via web services.  Most cloud services and SaaS applications support at least one of two types of web services:  RESTful or SOAP.  Out-of-the-box, ODICS supports both RESTful web services and SOAP web services; thus, ODICS users can access data from cloud services or SaaS applications using either RESTful web services or SOAP web services.

ODICS can also integrate with cloud services and SaaS applications using prepackaged solutions.  Out-of-the-box, ODICS offers prepackaged connectivity for Salesforce, Amazon Redshift, and BICS.  For other cloud services - such as Service Cloud, Sales Cloud, Google Analytics, and others – prepackaged solutions can be purchased from third-party vendors such as Progress DataDirect and Bristlecone.  These third-party vendors refer to these prepackaged solutions as data connectors.

ODICS users can integrate data between two cloud services or between two SaaS applications via web services or prepackaged solutions.  For example, ODICS users can extract data from Field Service Cloud (FSC) and load it into BICS for additional business intelligence insights.  This example can be extended to other data services such as DBCS, and data from FSC can be loaded and transformed on the DBCS database engine as well.  Figure 2, below, shows an example of how to use ODICS to integrate FSC, DBCS, and BICS:

 

Figure 2 – Integrating Field Service Cloud and BICS with ODI Cloud Service

Figure 2 – Integrating Field Service Cloud and BICS with ODI Cloud Service

 

On Figure 2, above, the first step extracts data from FSC using the FSC RESTful web services.  The second step uses DBCS to load and transform data from FSC.  The third step loads data from DBCS into BICS using the ODICS prepackaged solution for BICS.

 

Tip 1 - Integrating Field Service Cloud and BICS with ODI Cloud Service

 

Consuming Data from Oracle Field Service Cloud with ODI Cloud Service

 

In FSC, the consumption of data is done via RESTful web services.  When ODICS invokes the FSC RESTful web service, the web service returns data in JavaScript Object Notation (JSON) format.  Using ODICS, the data in JSON format can be converted into text files and stored on a staging area - such as data directory on JCS - for further processing.  This strategy is illustrated on Figure 3, below.

 

Figure 3 – Consuming Data from Field Service Cloud with ODI Cloud Service

Figure 3 – Consuming Data from Field Service Cloud with ODI Cloud Service

Tip 2 - Integrating Field Service Cloud and BICS with ODI Cloud Service

 

In ODICS, the following steps are required in order to consume data from a RESTful web service:

  • In the ODICS Topology, create a new data server and schema using the RESTful Service Technology. Enter the REST Service URL, Resource URI, and the operations allowed by the service.
  • Create an ODICS package to invoke the RESTful web service using the ODICS tool called OdiInvokeRESTfulService .  The OdiInvokeRESTfulService tool must be associated with the RESTful server that has been defined in the ODI Topology.  The OdiInvokeRESTfulService tool is where the user defines the RESTful operation, and the filename and location where the JSON data will be saved.
  • For a detailed overview of how to use RESTful web services with ODICS, go to “Using RESTful Web Services with Oracle Data Integrator Cloud Service.”

For a detailed overview of how to configure the FSC RESTful web service in ODICS, go to “Loading Data from Oracle Field Service Cloud into Oracle Analytics Cloud using ODI and REST – PART I.”

 

Loading the FSC JSON Files into Oracle Database Cloud Service (DBCS)

 

As previously illustrated, the invocation of the FSC RESTful web service returns data in JSON format, and ODICS can convert this data into text files and save them on disk.  Using ODICS, the content of these JSON files can be loaded into a database service such as DBCS for further processing and integration.  In ODICS, JSON files are processed using the ODI Complex File Technology.  This strategy is illustrated on Figure 4, below:

 

Figure 4 – Loading JSON Files into Database Cloud Service (DBCS)

Figure 4 – Loading JSON Files into Database Cloud Service (DBCS)

In ODICS, the following steps are required in order to process JSON files:

  • In the ODICS Topology, create a new data server and schema using the Complex File Technology. To see additional information on how to configure the ODI Complex File Technology, go to “Oracle Data Integrator Driver for Complex Files Reference.”
  • Using a sample JSON file, create a new XSD file – the XSD file describes the structure of the JSON file, which is used by ODI to create a relational schema of the JSON data file. Use the ODI or Fusion Middleware Native Format Builder to create the XSD file.  The ODI Native Format Builder wizard can be run from the ODI Data Server page by selecting the Edit nXSD
  • Use the Properties section of the new ODI Data Server to customize your complex file parameters.
  • Optionally, configure an external database to load the content of the JSON data files directly into a database instead of using the ODI Agent’s In-Memory database (default). Using the Agent’s In-Memory database may require additional memory for the ODICS Agent if the JSON files are large files.

 

Tip 3 - Integrating Field Service Cloud and BICS with ODI Cloud Service

 

For a detailed overview of how to configure the ODICS Complex File Technology to process FSC JSON data files, go to “Loading Data from Oracle Field Service Cloud into Oracle Analytics Cloud using ODI and REST – PART II.”

 

Loading Field Service Cloud Data into BI Cloud Service with ODI Cloud Service

 

As previously illustrated, JSON data files can be loaded into a database service for further processing and transformation.  Nevertheless, ODICS can transform JSON data in memory and load it directly into BICS without a database service.

BICS supports various ways of uploading data into its underlying tables, depending on the BICS subscription.  If the BICS subscription is based on the Database Schema Service (default), then ODICS users can upload data into BICS using either the BICS RESTful web services or the ODICS prepackaged solution for BICS.  If the BICS subscription includes DBCS, then ODICS users can upload data directly into the database instance of DBCS that is configured with BICS – this can be accomplished by using the Oracle technology that is already included in ODICS.

The ODICS prepackaged solution for the BICS Database Schema Service is called Oracle BI Cloud Service, and it is one of the technologies listed in the ODI Topology.  The ODICS prepackaged solution for BICS works in conjunction with two knowledge modules, RKM Oracle BI Cloud Service and IKM SQL to Oracle BI Cloud Service, to reverse engineer the BICS tables and upload data into BICS, respectively.  Alternatively, ODICS users can upload data into the BICS Database Schema Service using the BICS RESTful web services, but this option may require additional configuration in ODICS.  Thus, it is recommended to use the ODICS prepackaged solution for BICS when loading data into BICS Database Schema Service.  To upload data into BICS Database Schema Service, users must create an ODICS mapping and define the BICS tables as target datastores in the mapping.  The source datastores of the mapping can be from other technologies such as tables from a database instance of DBCS.  These tables can be used for additional transformation prior uploading data into BICS.  This strategy is illustrated on Figure 5, below:

 

Figure 5 – Loading Data into BICS Database Schema Service with ODI Cloud Service

Figure 5 – Loading Data into BICS Database Schema Service with ODI Cloud Service

 

In ODICS, the following steps are required in order to upload data into BICS Database Schema Service:

  • In the ODICS Topology, create a new data server and schema using the Oracle BI Cloud Service Technology. For details on how to configure the Oracle BI Cloud Service Technology, go to “Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator, Oracle Business Intelligence Cloud Service.”
  • Create a BICS data model in ODICS and use the RKM Oracle BI Cloud Service to reverse-engineer the BICS tables.
  • Use the BICS data model to construct an ODICS mapping that uses the BICS tables as target datastores in the mapping. Use the IKM SQL to Oracle BI Cloud Service to transform the source dataset and load the data into BICS.
  • Execute the ODICS mapping to perform the data upload operation.
  • Create a BICS dashboard to analyze the new data.

For a generic approach on how to use ODICS to upload data into the BICS Database Schema Service, go to “Using Oracle Data Integrator (ODI) to Load BI Cloud Service (BICS).”

For a detailed overview of how to use ODICS to upload data from FSC into the BICS Database Schema Service, go to “Loading Data from Oracle Field Service Cloud into Oracle Analytics Cloud using ODI and REST – PART III.”

Figure 6, below, shows a dashboard featuring data from Field Service Cloud.  In this example, the Field Service events have been loaded into BICS, and the BICS dashboard shows the total number of Field Service events by Event Date.

 

Figure 6 – BI Cloud Service Dashboard – Field Service Cloud Data

Figure 6 – BI Cloud Service Dashboard – Field Service Cloud Data

 

Conclusion

 

ODICS provides pushdown data processing with high performance ETL for the Cloud.  ODICS executes data transformations where the data lies without having to unnecessarily copy data to remote locations.  This article illustrates how to use ODICS to integrate Field Service Cloud with BI Cloud Service.  The examples provided in this article can be used as a recipe to integrate other cloud services or SaaS applications as well.

For more Oracle Data Integrator 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 Oracle Data Integrator (ODI).”

 

ODI Related Articles

Webcast: Using Oracle Data Integrator Cloud Service (ODICS) to Integrate Field Service Cloud with Business Intelligence Cloud Service (BICS)

Oracle Data Integrator Cloud Service (ODICS)

Oracle Field Service Cloud (FSC)

Oracle Business Intelligence Cloud Service (BICS)

Using RESTful Web Services with Oracle Data Integrator Cloud Service

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

Using Oracle Data Integrator (ODI) to Load BI Cloud Service (BICS)

Integrating Oracle Data Integrator (ODI) On-Premises with Cloud Services

Lift and Shift to Oracle Data Integrator Cloud Service (ODICS) - Moving your Repository to the Cloud

Deploying ODICS Studio on a Windows Image (Compute Cloud Service)

Oracle Data Integrator Cloud Service (ODICS) - Where to Run Studio

Oracle Network Cloud Service

Oracle Platform As a Service (PaaS)

Oracle Infrastructure As a Service (IaaS)

Database Cloud Service (DBCS)

Exadata Cloud Service (ExaCS)

Big Data Cloud Service (BDCS)

Compute Cloud Service

Storage Cloud Service

Oracle Public Cloud (OPC)

Java Cloud Service (JCS)

Oracle Field Service Cloud REST APIs

Oracle Database As a Service (DBaaS)

JavaScript Object Notation (JSON)

ODI Complex File Technology

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content