* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + Source Fusion 20D (22.214.171.124.0) + Infrastructure (OCI) Object Storage + Target ADW 18c - by Jay Pearson (A-Team) *
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
Procurement and Spend
Product Information Management
Supply Chain Planning
Supply Chain and Order Management
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'
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.
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
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.
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.
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.
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.
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
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.
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.
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.