Best Practices from Oracle Development's A‑Team

SaaS Data Replication in Oracle Analytics Cloud (OAC)

* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + Source Fusion 20D ( + Infrastructure (OCI) Object Storage + Target ADW 18c - by Jay Pearson (A-Team) *

For other A-Team articles by Richard, click here


Oracle Analytics Cloud (OAC) has built-in functionality for SaaS data replication.  This allows OAC to replicate data on a schedule from a number of Oracle's SaaS offerings.  

For many use cases, this will mean that a separate extract and load processes, is no longer required.

The sources that can be replicated are:

- Oracle Cloud Applications (i.e. Fusion with either Oracle Cloud Infrastructure Object Storage or Object Storage Classic)

- Oracle Service Cloud (RightNow)

- Oracle Talent Acquisition Cloud (Taleo)

- Oracle Eloqua

The target database for the replication can be one of the following:

- Oracle Autonomous Data Warehouse (ADW)

- Oracle Autonomous Transaction Processing

- Oracle Database

Behind the scenes, for Fusion SaaS source, Analytics Cloud is orchestrating the BI Cloud Connector tool to extract and load the target database.  This is all seamless to the user, with everything controlled through a new GUI within Analytics Cloud.

This article will walk through the steps to set up, configure, run, and troubleshoot, data replication.

Note - this functionality is available in the Data Lake, and Enterprise versions of Analytics Cloud.  OAC must be version 18.3.3 or higher.


Fusion Application Sources

Note - for Fusion SaaS sources, you will need to identify which View Objects contain the data you need.  If you are not familiar with how to do that, see this article

The following Fusion SaaS pillars / products are supported for extract:

Customer Data Management
Higher Education
Human Resources
Incentive Compensation
Innovation Management
Maintenance Cloud
Procurement and Spend
Product Information Management
Public Sector
Risk Management
Supply Chain Planning
Supply Chain and Order Management


Getting Started

This functionality requires a user with the 'BI Service Administrator' role, and OAC / OAAC version of 18.3.3 or higher.

OCI Object Storage support is available in OAC 5.4 or higher.

From the Analytics home page, select the 'hamburger' icon at the top left of the screen, which opens a menu panel.

Within that panel, select 'Data'

Setup the Data Connections

Before you can set up Data Replication, you first need to set up both the source, and target, replication connections.

Please Note !

The replicator ONLY works extracting from SaaS sources currently.  It can not be used to replicate from one database to another.  This functionality may be added in the future, but is not currently possible.  You could consider scheduling Data Flows to do this, or using the Data Sync tool running in compute.


1. Set up Source

a. Select the 'Connections' menu tab, and then the 'Create' button:

b. Select the 'Replication Connection' option.  Note, traditional connections created with the 'Connection' option will not be available for replications.

c. Select the appropriate SaaS connection.  For Fusion SaaS connections, select the 'Oracle Fusion Application Storage'.  This name is intended to differentiate the connection from the existing run-time Fusion SaaS connection.


d. Enter the connection details. Below are  examples, for Fusion SaaS, Taleo, Eloqua, and Service Cloud / RightNow

Tip !

Check the BI Cloud Connector documentation for the relevant source, to make sure the source system user defined in the connection, has the required roles to extract the data.


Fusion Saas

  • The Fusion user must have the correct application roles to run the extract tool. Follow this document to add those.
  • For Classic Storage - the Fusion SaaS needs to be 13.18.05 or higher.
  • For Oracle Cloud Infrastructure (OCI) Object Storage - the Fusion SaaS needs to be 19C ( or higher.
  • The user must also initially log into the Fusion SaaS BI Cloud Connector console and set up the Cloud Storage Service that will be used.
  • Set up for Fusion SaaS BI Cloud Connector (BICC) to use Classic Cloud StorageClassic Storage can be found here
  • Set up for Oracle Fusion SaaS Business Intelligence Cloud Connector (BICC) to use Oracle Cloud Infrastructure (OCI) Object Storage can be found here.
  • Details of that Oracle Storage cloud connection and a user that can access that, are also required for the connection.
  • For the Storage host URL, get the hostname from Storage Cloud REST URL.
  • The format for the fusion URL is shown below.  Make sure you use HTTPS, and append /bi/ess/esswebservice to the end of your Fusion host. https://fusionhost.oraclecloud.com/bi/ess/esswebservice

For Oracle Classic Storage

Storage Type: Classic

Storage Connection: Leave Blank

For Oracle Cloud Infrastructure (OCI) Object Storage

Storage Type = OCI

Storage Region = Only include the region. Do not use the full hostname. Remove objectstorage prefix and oraclecloud domain. 

Storage Tenancy OCID: Copy from OCI Profile -> Tenancy

Storage User OCID: Copy from OCI Profile -> User Details

Storage Bucket: Name of Bucket created in OCI Object Storage.

Storage API Key: Generate the key and copy to OCI Profile -> User Details -> API Keys.

URL: https://fusionhost.oraclecloud.com/bi/ess/esswebservice

Username: Fusion User Name. Must be a Local Fusion user that has the ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT role.

Password: Fusion User Password

Storage Connection: The name specified in the BICC Connector Console when the External Storage connection was created.



Service Cloud / RightNow


Note there is a 'Permissions' section, where OAC users and roles with access to the connection, can be defined.


2. Setup Target

a. Select 'Create' and then 'Replication Connection'.  Again, this needs to be a specific connection used for Data Replication.  Traditional 'Connections' can not be used in replications.

b. Create the target connection.  In this example we will set up a DBCS target.

c. Make sure the database user has the appropriate rights to create, drop and alter tables.  More information can be found in this document.


Create a Data Replication

Existing Data Replications can be seen within the 'Data Replications' section:

a. Select 'Create' and 'Data Replication'

b. Select the source connection that you created in the previous step.  In this example the FusionSaaS connection will be used.


c. The main Data Replication screen opens, providing many options, some of which allow the user to;

- Change the replication source

- In the case of Fusion SaaS, change the Fusion Offering to Financials, Human Resources, or any of the other offerings.

- For some sources, you may also see a 'schema' option to change schemas (RightNow / ServiceCloud, as an example)

-  In the left hand window, a list of the available objects are displayed.  If you are familiar with the full View Object full name, then you can display these instead of the object name, by selecting the 'Show Data Store Paths' option at the bottom of the screen.

- If you have custom view objects, you can also replicate those by selecting 'Add a custom view object'.  Enter the full path and name for the custom view object, for instance:  'FscmTopModelAM.TaskDffBIAM.FLEX_BI_TaskDFF'

d. In this example, the Fusion 'Financials' offering was selected, and then the 'Customer' and 'CustomerAccount' objects.  Some things to be aware of:

- Click the arrow next to the individual objects, in this case 'Customer', to bring up details of the fields to be included, and the replication options

- You can rename the 'Target Object Name' to something more meaningful, in this example 'FIN_CUSTOMER'

- You can bring all, or a selection of the fields from the object, into your target database.  Some objects can have hundreds of columns, so it is best practice to select only the fields you need.  This will improve load performance.

- You can also rename the Target fields, as well as change their data type, length, and precision (where applicable).  You can also set the fields to be nullable, and whether you want to make that field a new identifier in the target database.  New identifiers must be a date or timestamp columns, used when performing incremental loads.

- You can change the primary key(s) for the object.  You do this by clicking into the field you want to change, then going to the far right of the display, selecting the 'hamburger' menu item within the field, and then the key options


Tip !

While you can change primary keys, data types, lengths and precision - only do this if there is a valid reason.  Changing these incorrectly, could result in load failures.


- Where available, the 'Load Type' can be set to either a full load, or incremental.  To minimize the time taken to load large tables, incremental loads should be used where possible.

In a 'Full Load', the target table will be truncated before being reloaded.

In an 'Incremental', the first time the target is loaded, a full load will be run, and then subsequent loads will just load new records, or updates.  The incremental option is only available for objects where there is an appropriate unique key and date field that records changes.  Where those are not available, a 'Full Load' will be run each time.

- For certain sources (Fusion SaaS currently) you may have the option to 'Include Deletions'.  Selecting this, if a record is deleted in the source table, that record will be mark as deleted in the target table during the next replication.  This is indicated in a metadata column in the target table, CDC$_DM_CODE, that will get a 'D' value to indicate the row was deleted.  This option is only available for 'Incremental' load types.  If you do a 'Full Load', then only current records in the source will be replicated to the target.

- Some view objects can record change history.  To replicate those, check the 'Include History' option, although this may require additional work to change the keys to make sure a unique combination of keys is identified.

- You can set these extract options individually for each object you include in the replication.  You may select one object to be refreshed with a full load, while another is refreshed incrementally.

For large datasets, you have the option to add a 'Replicate From' filter to reduce to data from a specific date.

- There is an option to filter the data set using an expression.  The expression format used will depend on the filter language of the source system.  Common filter languages include SQL, XML.  Refer to the specific documentation for the source system.

In this example with fusion SaaS, a filter is added for the Analysis Year = 2018.  Notice it is the Target Field name that is used in the filter statement.  Select 'Validate' to confirm the syntax before saving.



e. Once the objects for replication have been selected, you must chose the Replication Target where the replicated data will be loaded to.  In this example we will use the DBCS replication connection we created earlier


Once you've selected the Target, you can also change the schema in that target if applicable:

f. Finally, save the data replication and give it a suitable name.

g. To return to the main Data Replication menu, click the 'Back' button in the upper left corner of the screen.


Run a Data Replication

To edit, schedule or check on the status of previous replication runs, go to the 'Data Replications' menu item.

Select the replication task, and then the hamburger icon on the right for details.

You can run the replication manually as a one-off with 'Run', or schedule it with 'New schedule'.

As of the time of writing, Schedule extracts can be run as frequently as once / day.


Checking Status and Troubleshooting a Data Replication

The Data Replication tab will also provide status of previous runs.

In this case, the 'SAAS FIN CUSTOMER' replication shows that it completed with warnings.

Select the 'Run Details' option for the replication you want to check.

Additional details on the error message are displayed.

The full error message is included below.  Notice it recommends checking an E$ error table for more details.

java.sql.SQLWarning : ReplicateType: DATA - 2120400 records with invalid data were encountered while loading FIN_CUSTOMER from source data buffer. An attempt was made to auto-fix the records and load them to table FIN_CUSTOMER. Refer to Error table E$_1448561074 for more details


That E$ table can be found in the target database.  This provides the reason for the error, a unique constraint was violated while trying to insert the records.

I had selected 'include History', but had left the primary key of the object as  'PARTYID'.  The history includes many records with the same PARTY_ID, which violated the unique key.

To resolve this, we need to edit the keys to make each key unique.  I added the 'LASTUPDATEDATE' to the primary key, so the unique key was a combination of PARTYID and LASTUPDATEDATE.  The replication then ran successfully.



This article walked through the steps to set up and configure the Data Replicator in Oracle Analytics Cloud, to extract and schedule data from Oracle SaaS products.

For other A-Team articles by Richard, click here


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