3Integration With Oracle Business Intelligence Cloud Service

About Oracle Field Service – Oracle Business Intelligence Cloud Service Integration

Integrating Oracle Field Service and Oracle Business Intelligence (Oracle BI Cloud Service) allows you to use Oracle Field Service data in Oracle BI Cloud Service. As a result, you get powerful reporting and dashboard capabilities.

Note: Oracle BI Cloud Service is a separate Oracle product distinct from Oracle Field Service. You must have a valid subscription to the Oracle BI Cloud Service to use the integration between the two applications.

This integration lets you use the comprehensive analytic tools and intuitive interface of Oracle BI Cloud Service to process Oracle Field Service data to present information and analyze your business. The integration supports the following features:

  • Data synchronization with Oracle BI Cloud Service generates Oracle Field Service reports in Oracle BI Cloud Service.

  • A total of 50 GB is available for data storage for data migrated from Oracle Field Service to Oracle BI Cloud Service schema as a service.

  • Data can be pushed from Oracle Field Service to Oracle BI Cloud Service once in a day or in real-time based on the configuration.

  • Example Data models and reports are provided as a quick reference to the user. Oracle Field Service sample reports, code and documentation is available for download at http://www.oracle.com/technetwork/middleware/bi-foundation/bics-sample-2283629.html .

Embed Oracle Business Intelligence Cloud Service Reports in Oracle Field Service

You can embed Oracle BI Cloud Service reports into Oracle Field Service through the Action Management screen. The prerequisite is to log in again to view the embedded Oracle BI Cloud Service screen. You can use Oracle Field Service as the single location to view complex reports without the need to navigate between applications to access data. Embedding also helps share content and data between Oracle Field Service and Oracle BI Cloud Service.

High level process to embed reports is as follows:
  1. Allow the Oracle Field Service domain in Oracle BI Cloud Service.

  2. Find the Business Intelligence Cloud Services URL of the object you want to embed.

  3. Sign in to Oracle Field Service. Embed the Oracle BI Cloud Service content inside the button and use the copied URL.

To embed Oracle Oracle BI Cloud Service reports in Oracle Field Service:

  1. In Oracle BI Cloud Service, add the domain name associated with Oracle Field Service to the Allow embedding in allowlist for Oracle BI Cloud Service. For example adding *.etadirect.com in this field indicates that the access to etadirect.com specific content is approved.

    Note: Only administrators have the privilege to add safe domains to the allowlist. For information about safe domains, see the Oracle BI Cloud Service documentation.
  2. Get the Oracle BI Cloud Service URL you want to embed.

    1. Open the Catalog screen.

    2. Open the Object (Analysis/ Dashboard/ Visual Analyzer)

    3. Copy the URL displayed in the new opened window.

  3. In Oracle Field Service, perform the following steps:

    1. Click Configuration.

    2. In the Configuration screen, click Action Management.

    3. In the Action Management screen, click the Add button link.

    4. In the Add button window, complete the fields in the General tab to add the Oracle BI Cloud Service dashboard link as a button. For instructions on how to create an action link, see the Action Management section in the Administering Oracle Field Service

    5. Complete the following fields in the Plug-in detail tab:

      • TYPE—the HTML5 application

      • Use Plugin API—False

      • URL—Your URL, for example: https://businessintellt-identity-domain.analytics.oraclecloud.com/analytics/saw.dll?Dashboard&

        PortalPath;=%2Fcompany_shared%2FOFSCDEMO%2F_portal%2FOFSC%20Sample%20Dashboard

      • Main menu items—default value, True

      • Tab or Iframe layout—default value, True

    6. Click Configuration.

    7. In the Configuration screen, click User Types.

    8. In the User Types screen, click Screen Configuration.

    9. In the Action Management screen, click the Add button link.

    10. Set the visibility to Read only for the appropriate user.

Add Parameter Values in Embedded Reports

Users can add any parameter values to the reports/dashboards embedded into Oracle Field Service. The reports / dashboards are refreshed based on these parameter values. To add parameter values in embedded reports, users can create corresponding filter in Oracle BI Cloud Service reports/dashboards.

To add parameter values:

  1. Add resource tree parameters {pid} in the Oracle BI Cloud Service URL:

    1. p0 represents the no of parameters included in the URL

    2. p1 indicates the operators, for example eq.

    3. p2 stands for the column name in Oracle BI Cloud Service

    4. p3 indicates the value of the attribute to be passed.

  2. Create a filter in the Oracle BI Cloud Service report / dashboard with PID. Add resource tree parameters {pid} in the URL as follows:

    • https://businessintellt-identity-domain.analytics.oraclecloud.com/analytics/saw.dll?Go&Path;=%2Fcompany_shared%2FOFSCDEMO%2F_portal%2FOFSC%20Sample%20Dashboard& p0=1&p1;=eq&p2="provider"."pid"&p3;="{pid}"

      Note: For displaying the dashboard for both parent and leaf nodes, the URI requires two PID placeholders.
    • https://businessintellt-identity-domain.analytics.oraclecloud.com/analytics/saw.dll?Dashboard&PortalPath;=%2Fcompany_shared%2FOFSC-DEMO%2F_portal%2FOFSC%20DEMO%20Dashboard&Action;=Navigate&P0;=2&P1;=eq&P2;=PROVIDER.RESOURCE_ID&P3;={pid}&P4;=eq&P5;=PROVIDER.PARENT_RESOURCE_ID&P6;={pid}&options;=mdr

      • P1- Operation btw first parameter set, "eq" stands for equal to

      • P2- Column name in the format of table name column name, PROVIDER.RESOURCE_ID - for considering resource owned activity in reports

      • P3- Value , the placeholder {pid}

      • P4 - Operation between second parameter set

      • P5 - PROVIDER.PARENT_RESOURCE_ID - for aggregating child resources owned activities in reports

      • P6- Value, the placeholder {pid}

  3. Configure SSO in both Oracle Field Service and Oracle BI Cloud Service to avoid multiple login sessions.

Configure Real-Time Data Integration With BICS and DBaaS

The Outbound Integration icon in the Configuration, Subsystems page acts as a single page to configure and view all outbound delivery channels in Oracle Field Service.

Users can use the Outbound Integration icon to create new channels for Business Intelligence Cloud Service (BICS) and DBaaS to transfer data to their BICS and DBaaS instances. Also, users can configure real-time data transmission for Oracle Field Service entities. The data is transferred to the BICS or DBaaS instances when the events corresponding to the entities are triggered in the Oracle Field Service system.
Note: By default, the Daily Extract option is available in the Outbound Integration Channels page and you can click the option to view the daily extract configurations.

To complete real-time data integration with BICS and DBaaS:

  1. Log in to Oracle Field Service Manage interface.

  2. Select the Navigation button.

  3. Click Configuration.

    The configuration page of the user displays.
  4. Click Outbound Integration.

    The Outbound Integration Channels page displays.
  5. Click Add Channel to configure a new channel.

    The Add Channel page displays.
  6. Select Business Intelligence Cloud Service Access from the Channel Type drop-down list to configure a BICS channel and enter the details in the following fields:

    1. Name: Enter a name for the channel.

    2. URL: Enter the URL of the BICS instance.

    3. Identity Domain: Enter the identity domain of the BICS instance.

    4. User Name: Enter the BICS user name.

    5. Password: Enter the BICS password.

    6. Confirm Password: Re-enter the BICS password.

  7. Click OK to create the BICS channel.

  8. Select Database as a Service Access from the Channel Type drop-down list to configure a DBaaS channel and enter the details in the following fields:

    1. Name: Enter a name for the channel.

    2. URL: Enter the URL of the DBaaS instance.

    3. Host: Enter the address of the database server or the host name.

    4. Port: Enter the port number on which the database server listens for incoming connections.

    5. Select the Connect with SID option and enter the name of the Oracle database instance in the SID field.

      OR
    6. Select the Connect with Service Name option and enter the network service name of the database.

    7. User Name: Enter the database user name.

    8. Password: Enter the password to access the database.

    9. Confirm Password: Re-enter the database password.

    A new BICS or DBaaS channel is created.
  9. Click the BICS or DBaaS channel and click Add New to create a new entity.

  10. Specify the required details in the following fields:

    1. Entity: Select a list of Oracle Field Service fields that you want to view in the BICS or DBaaS instance.

    2. Table Name: Enter the table name that you want to create in the BICS or DBaaS instance.

    3. Data Transfer: Select one of the following options:

      • Once Daily: Data is transferred to the BICS or DBaaS instance once in a day as per the daily extraction period configured in the system.

      • Real-time: Data transfer from Oracle Field Service to BICS or DBaaS occurs in near real time.

  11. Click Submit.

    The entity is added to the channel with the specified details.
  12. To add fields to the entity, click the Properties icon corresponding to that row and select Fields from the drop down menu.

    This figure shows the Properties icon to configure fields:

    Image shows Properties icon.

  13. Click the Plus icon and select the required field that you want to add to the entity.

  14. Click Add and Click OK.

    The selected fields are added as columns to the corresponding tables in the BICS or DBaaS instance.
After you add a new outbound channel:
  • The Outbound Integration Channels configuration window displays the updated list of channels.

  • Monitoring data or information displayed in the Outbound Integration Channels screen is reset every day (24 hours).

  • The data processing and transmission depends on the volume of data stored. You may expect a delay in transferring big volumes of data for real-time data transfer. Also, the daily transmission is initiated as per the daily extraction period.

  • The data transfer for the entities, namely, Activity, Inventory, ActivityLink, Resources, Users, and Route entities is not started immediately when the user opts for real-time data transfer.

    The real-time data transfer for the entities is initiated only from the next day after the first run of the daily extraction process is completed.

Consider the following configurations for the BICS channel:
  • Chat Fields and Chat Message Fields entities are configured.

  • Real-time data transfer method is selected.

  • The Oracle Field Service instance is connected to a BICS instance.

Note that the total number of chat messages and total number of helpdesk messages are displayed in the BICS instance. If a user logs in to the Oracle Field Service instance and sends a helpdesk chat request, then the total number of chat messages and total number of helpdesk messages are incremented in the BICS instance. However, if a one-to-one chat is initiated between the users, then only the total number of chat messages is incremented in the BICS instance.

Configuration Statuses For Outbound Channels

The following table lists the different configuration statuses available for outbound channels:

Message displayed on the Channel Configuration Status User Interface Behavior Action Item for the User
No tables configured User created a new channel The BICS or DBaaS icon is in enabled state.

Message is displayed in Grey color.

Indicates that you should configure entity or tables that you want to create in Oracle Business Intelligence Cloud Services (BICS) or Oracle Database as a Service (DBaaS) instances.

Click the pencil icon to edit the channel details.
Data Transfer has not been started Entities or tables configured but Daily Job or Real-time transfer has not started The BICS or DBaaS icon is in enabled state.

Message is displayed in Grey color.

Tables have been created in the system but data transfer from Oracle Field Service to BICS/ DBaaS is not initiated.

Real-time data transfer is initiated when the configured event is triggered in Oracle Field Service.

Daily data transfer is initiated when the daily job is triggered in Oracle Field Service.

N/A
Success Rate displays Data Migration via Daily Job or Real-time completed for the selected channel Data Transmission Success Rate is displayed.

Percentage of successfully transferred data = (Total number of events successfully Transferred in Event API + Total number of successful row migrated in daily) / (Total number of rows in daily + Total number of events in real time)

A message displays the last updated time.

All the monitoring data or information is reset every 24 hours.

N/A
An error message displays. Daily Job or Real time communication error with the Endpoint The BICS or DBaaS icon is in disabled state. The following scenarios can occur:
  • User configured an invalid host: The "Host unreachable. Please check your credentials" message is displayed. The user must configure the correct host ID.

  • Authentication error: The “Authentication Failed. Please check credentials.” message is displayed. The user must configure the correct user name and password.

  • Authorisation error: The “Provided user is not authorized” message is displayed. You need to configure access permission for the user in BICS or DBaaS.

  • Account Locked: The “Account locked. Please reset your credentials” message is displayed. The user has to reset the BICS or DBaaS credentials.

User has to update the channel credentials based on the error message.
Complete Daily Extract Configurations. Available by default to the user. The BICS or DBaaS icon is in enabled state.
A message is displayed with the following details:
  • Last time: Time of the last update.

  • Size: Size of the Daily Extract file.

  • Status: Status of the daily extraction process.

  • Values: New or Completed or Failed.

The following screen shows the different items displayed for fields or entities:
Outbound Integration Screen

Item Details
Table Name Name of the table created in BICS or DBaaS.
Data Transfer Mode of data transfer selected for BICS or DBaaS.
Status Status of the data transfer. Values: New, Completed, In Progress, Failed.
Last Time Indicates the last updated time.
Rows Updated Number of rows updated in last 24 hours.
Rows Skipped Number of rows failed to transfer.
Data Transfer Successful Icon Indicates that the data transfer is successfully completed.
Data Transfer Failed Icon Indicates that the data transfer failed to complete. The system will retry to transfer the missed data.

Fields not Available for BICS/DBaaS data Transmission

This section lists those fields that are removed from BICS/DBaaS transmission.

This table provides the fields that are not available for BICS/DBaaS transmission:

Field Type

appt.travel_estimation_method

INTEGER

appt.a_locationid

INTEGER

appt.aupdate_flags

INTEGER

appt.auto_routed_to_date

DATE

appt.auto_routed_to_provider_id

INTEGER

appt.auto_routed_to_provider_name

STRING

appt.continuous_traveling_time

INTEGER

appt.number

STRING

appt.first_manual_operation_interface

INTEGER

appt.a_visitid

INTEGER

appt.access_schedule

STRING

appt.acoord_accuracy

INTEGER

appt.acoord_status

STRING

appt.afeatures

INTEGER

appt.a_templateid

INTEGER

provider.pcapacity_bucket

INTEGER

provider.pdate_fid

INTEGER

provider.pinitial_ratio

FLOAT

provider.p_rprid

INTEGER

provider.ptime_fid

INTEGER

provider.skip_days_for_stats

INTEGER

queue.final_travel_estimation_method

INTEGER

queue.final_travel_from_aid

INTEGER

queue.qid

INTEGER

time_zone.zoneinfo_name

STRING

appt.travel_from_aid

INTEGER

Supported Fields for Data Transmission

The following table describes the list of entities and the supported data transmission method for the entities.

Fields Daily Once Data Transfer Real Time Synchronization

Activity Fields

Yes Yes

Activity Link Fields

Yes Yes

Activity Work Skill Fields

Yes

Chat

Yes

Chat Broadcast Delivery Status

Yes

Chat Message

Yes

Chat Participant

Yes

GPS Track Fields

Yes

Inventory Fields

Yes Yes

Message Fields

Yes

Message Text Fields

Yes

PAS Answer Fields

Yes

PAS Question Fields

Yes

Property Fields

Yes

Property Lookup Fields

Yes

Required Inventory

Yes

Resource Fields

Yes Yes

Resource Location Fields

Yes

Resource Preference

Yes

Resource Property Fields

Yes

Resource Work Skill Fields

Yes

Route Fields

Yes Yes

Service Request Fields

Yes Yes

Time Slot Fields

Yes

Type List Fields

Yes

User List Fields

Yes Yes

User Resource Relation Fields

Yes

Export and Import BICS/DBaaS/Daily Extract Configurations

Daily extract/BICS/DBaaS configurations can be created or updated by importing the configuration from an external source. Daily extract configuration is imported as an XML file containing data of individual daily extract files. If the configuration to be imported contains the same filename as the existing daily extract configuration, the file is overwritten in the process of import. Also, field level validations are performed and errors and warnings are shown on the Import window while importing files.

To export or import configurations:

  1. Click Configuration

  2. Click Outbound Integration.

    The Outbound Integration Channels page displays.
  3. Click Import, browse and select the XML file to be imported.

  4. Click Validate.

    The selected file is validated and the errors and warnings (if any) are shown on the Import window. The figure shows the Import window:
    Image shows the Import window.
  5. To export daily extract configuration, click Export.

    The configuration is exported as an XML file.

Configure SSO

You can configure single sign-on (SSO) login credentials in bothOracle Field Service and Oracle Business Intelligence.

To configure SSO:
  1. In Oracle Field Service, follow these steps:

    1. Log into Oracle Field Service as an administrator–enter username and password.

      Note: Ensure that the username in Oracle Field Service matches the username in Oracle Business Intelligence.
    2. Click Configuration.

    3. Click Login Policies.

    4. Click Add New.

      The Add Policy dialog box is displayed. The values for SAML identity provider Encryption and Signing Certificates and SAML identity provider login/logout URLs are automatically populated from the Metadata xml.
    5. In the Authenticate using field, enter the SAML value.

    6. In the IdP Metadata XML field, enter the name of the Metadata file provided by IdP.

    7. In the label field, enter the unique identifier label. This is used for the SSO login screen.

    8. In the OFSC Metadata XML field, download and update the SAML Idp metadata file.

    9. Enter the Policy name assigned to this policy.

    10. In the Specify SAML IdP field, select Upload metadata XML.

    11. Click Add.

  2. In Oracle Business Intelligence, follow these steps:

    1. Log into Oracle Business Intelligence as an administrator–enter the username and the password.

      Note: Ensure that the username in Oracle Business Intelligence matches the username in Oracle Field Service.
    2. To enable SSO, click SSO Configuration.

    3. Click Configure SSO.

    4. Select Import IP metadata. Browse and select the same SSO file that you used for Oracle Field Service from SAML IDP Provider.

    5. In the SSO protocol field, enter HTTP POST.

    6. Export Oracle Business Intelligence Provider metadata and update the SAML IDP provider in Oracle Field Service.

    7. Click Test and then select Start SSO.

    8. When you enter the administrator user name and password, a message indicating a successful login session is displayed.

    9. Click Enable SSO.

      Note: Oracle Business Intelligence instance must be active while accessing the embedded reports in Oracle Field Service. If not, a blank page is displayed with error details.

Oracle Business Intelligence Cloud Service Data Models

Data models in Oracle Field Service provide data for business analysis purposes.

A data model structures business information in such a way that it can be used to analyze and report on the business. Oracle Field Service provides example data models for use with Oracle BI Cloud Service. The data models use fact tables, which contain numeric data for measuring business performance, and dimension tables, which contain values that represent the business entities you measure. The data from Oracle Field Service populates both kinds of tables in Oracle BI Cloud Service.

The example data models include the following:

Activity Data Model

The Activity data model represents activity-related business data in Oracle BI Cloud Service reports and dashboards.

The following tables are associated with the Activity data model.

  • Fact table

    • Activities

  • Dimension tables

    • Time [Total, Year, Quarter, Month, Day]

    • Activity Type [Total]

    • Work Zone [Total]

    • Provider [Total]

    • Geography [Total, State, City, Zip]

    • Status [Total]

This figure shows the data model diagram for Activity showing Activity type, date, address, work zone and provider details.


Image shows activity data model diagram

Message Data Model

The Message data model represents message-related business data in Oracle BI Cloud Service reports.

The following tables are associated with the Message data model.

  • Fact table

    • Messages

  • Dimension tables

    • Time [Total, Year, Quarter, Month, Day]

    • Message Type [Total]

    • Activities [Total, Activities, Provider]

    • Result [Total]

This figure shows the message data model diagram for message-related business data in Oracle BI Cloud Service reports.


Image shows message data model diagram.

User Data Model

The User data model represents user-related business data in Oracle BI Cloud Service reports.

The following tables are associated with the User data model.

  • Fact table

    • User

  • Dimension tables

    • Time [Total, Year, Quarter, Month, Day]

    • Provider [Total]

This figure shows the data model diagram for the User with provider and date details.


Image shows user data model diagram.

Oracle Business Intelligence Cloud Service Reports

Reports in Oracle BI Cloud Service answer your business questions by querying your organization’s data, including the data that is migrated from Oracle Field Service.

Oracle Field Service provides example reports that can be accessed through Oracle BI Cloud Service. Following example reports are available:
  • Activity by Activity Type/Job Count by Type—Displays the total number of activities by work type per resource for the month so you can quickly see the most active job types.

    This figure shows the Activity by Activity Type/Job Count by Type report with the most active job types.


    Image shows the activity by activity type/job count by type report.
  • Activity/Region-wise JobDistribution—Displays a representation of how jobs are distributed across regions for the month.

    This figure shows the Activity/Region-wise Job Distribution report indicating how jobs are distributed across regions.

    Image shows the activity/regionwise job distribution report.

  • Technician Overview—Lists route date, start time, stop time, job duration, and travel time data for each technician.

    This figure shows the overview of a technician's route.

    Image shows the Technician overview report

  • OFSC Tiles— Displays the company-wide total number of resources, work orders, completed jobs, cancelled jobs, and work orders per technician.

    This figure shows Tiles report displaying total number of resources, work orders, completed and cancelled jobs for each technician.

    Image shows the Tiles report showing technician details.

  • OFSC Dashboard—Displays the previous four reports in a single dashboard.

    This figure shows the reports dashboard combining all BICS reports into a single report.

    Image shows the reports dashboard

Understand Database Update Strategy

Oracle Field Service requires credentials for Oracle Business Intelligence Cloud Service to access the application and update the database. Before updating the database on Oracle Business Intelligence Cloud Service side, the structure of the tables must be synchronized. Oracle Business Intelligence Cloud Service REST API methods are called to update the database and push data to Oracle Business Intelligence Cloud Service.

Use the following table to understand Oracle Business Intelligence Cloud Service database update strategy.

This figure shows Daily Extract entities, table names and update types during synchronization for Oracle Business Intelligence Cloud Service database update strategy.


Image shows daily extract entities, table names and update strategies in a table.