OAC Data Replication: 4 Easy Steps to Start Your Data Flow

Oracle Analytics Cloud delivers simple self-service capabilities for data exploration and analysis. It is a great Business Intelligence solution that appeals to not only power users and data analysts but also to Sales & Marketing Managers and Customer Service Professionals who can easily take advantage of business data.

Long ago, Oracle used to provide a free utility called Oracle Data Sync which was marketed as the “lite” ETL solution to push data into the cloud. They later re-engineered their data replication capabilities and morphed it into a new feature called OAC Data Replication. With OAC Data Replication, you gain more convenience and simplicity while copying data from Oracle SaaS into OAC.

This article will walk you through four simple stages for setting up and running your own OAC Data Replication flow. What if you already have one and want to copy your data to another location? We’ve covered those steps as well. What’s more, this guide will also help you learn the differences between Oracle Analytics Cloud (OAC) (cloud-based analytical service) and Oracle Analytics Server (OAS) (on-premise service) to help you choose the best analytical platform for your business needs. 

What is Oracle Analytics Cloud?

Organizations today use various data sources like Spreadsheets, Marketing and Sales Dashboards, CRMs, and Cloud-based Data Warehouses to collect, analyze, and store data. Inherent to these systems is a need to unify data across all environments for better understanding and analysis.

Oracle Analytics Cloud is a single and complete platform for performing Collaborative Analytics. It includes a rich set of features and capabilities to let you and your workgroup answer questions, on any platform and any device. 

Oracle Analytics Cloud or OAC is Oracle’s cloud-hosted service that provides all the capabilities required to address the entire Data Analytics stack: from Data Ingestion and Modeling, through Data Preparation and Enrichment, to Data Visualization and Collaboration without any compromise on security or governance. It comes with embedded Machine Learning and Natural Language Processing technologies to help increase productivity and spring up your analytics-driven culture. 

In addition, using OAC, you can also take advantage of its flexible service administration capabilities that enable rapid deployment, simple scaling and patching, and automatic lifecycle management to streamline and simplify your operations. 

It also has an App Service that makes intelligent suggestions based on your usage habits and data preferences. You can avail more information on Oracle Analytics Cloud here: Oracle Analytics.

More:

• Top Big Data Ingestion Tools in 2022
• Business Data Analysis: 4 Comprehensive Aspects
• Big Data Analytics: 5 Comprehensive Aspects
• Ultimate Guide on the Best Data Ingestion Methods for Data Lakes

Prerequisites for OAC Data Replication

You should fulfill the following requirements to make the best use of this OAC Data Replication guide.

• Oracle Analytics Cloud Enterprise edition. OAC Data Replication is only available in the Oracle Analytics Cloud Enterprise edition.
• A supported Oracle Data Source. You can find the list of supported sources under the section Setting Up a Replication Flow for OAC Data Replication.
• A supported Oracle Data Target. The supported target list is provided after supported sources. 

What Information Do You Need For Data Replication?

OAC Data Replication makes data more accessible and available for you and your workgroup. It prepares data for visualization and analysis without running costly queries or redundant Data Extraction processes on the original Data Source.

This OAC Data Replication tutorial requires you to have the following details to get the most out of this content.

• Oracle BI Cloud Connector
• Oracle Fusion Applications
• Oracle Cloud Infrastructure Storage

Oracle BI Cloud Connector

You would need the https://{fa_url}/biacm link for your Oracle BI Cloud Connector.

Oracle Fusion Applications

You need the hostname and connection details for your Oracle Cloud Applications instance.

Oracle Cloud Infrastructure Storage

You should possess the following:

• Hostname, 
• Storage service name, and
• Container name of your Oracle Cloud Infrastructure storage instance. 

This information will be used to point Oracle BI Cloud Connector to your Oracle Storage Cloud storage instance. You must also get a hold of your REST Endpoint URL for your Oracle Cloud Infrastructure storage instance (Oracle Cloud Infrastructure Classic Console >  Storage Classic > Account). It would look something like this:

https://uscom-{location}.storage.oraclecloud.com/v1/Storage-mystoragecloudclassic

Additionally, you would need the following information about your object storage bucket in Oracle Cloud Infrastructure, which includes:

• Name of the storage bucket, 
• Namespace in which the bucket resides,
• Oracle Cloud Identifier (OCID) for the tenancy where the bucket resides, and
• Oracle Cloud Identifier (OCID) for the user with access to the storage bucket.

Setting Up a Replication Flow for OAC Data Replication

You can use OAC Data Replication to replicate data from these supported Oracle Data Sources to these supported Oracle Data Targets. The following is a list of them:

Supported Oracle Data Sources

• 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

Supported Oracle Data Targets

• Oracle Autonomous Data Warehouse (ADW)
• Oracle Autonomous Transaction Processing
• Oracle Database

Step 1: Setting Up the Data Connections

OAC Data Replication is a new method to easily replicate Oracle SaaS data into your Oracle Analytics Cloud for a comprehensive understanding and intelligent analysis of your data. This approach relies on Oracle Fusion Application Storage Container to replicate your data on a schedule basis. 

To get started with OAC Data Replication, ensure that your OAC Data Lake or Enterprise versions of Analytics Cloud have been patched with version 18.3.3 or higher. Currently, OAC Data Replication is only limited to these Oracle SaaS: Oracle Fusion Applications, Oracle Service Cloud (RightNow), Oracle Talent Acquisition Cloud (Taleo), and Oracle Eloqua.

Before we go into the finer details of the OAC Data Replication process, please remember that OAC Data Replication requires a user with the ‘BI Service Administrator‘ role. Once you have the necessary access to set up your data connection, proceed with the steps below to create your OAC Data Replication flow.

Setting Up the Source

OAC Data Replication requires configuring your source, target, and replication connections. You can start this by setting up your source connection as follows:

Step 1: Visit your Home Page and select the “hamburger” icon in the top left corner of your screen. From the available options on the left pane, select Data.

Step 2: Head to the Connections tab. Under this tab, click Create > Replication Connection and select your SaaS data source. 

Step 3: Enter your source connection details at the Create Connection dialog box.

Setting Up the Target

The next part of your OAC Data Replication setup is configuring your target connection. Here are the steps to take:

Step 1:  From your Home Page, choose Create > Replication Connection and select the data source you want to copy your data into.

Step 2: Similar to what we did earlier, you’ll have to specify the connection details of your data target. Do ensure that the database user has the necessary permissions to create, delete, and modify tables. 

Step 2: Creating a Replication Flow

To create an OAC Data Replication flow, visit your Home Page and navigate to the Data Replications section.

Step 1: Select Create > Data Replication.

Step 2: In the Source Connection dialog box, pick the source connection that you created in the previous steps.

Step 3:  A main screen will present you with options to:

• Change your replication source. 
• Change schema for some sources (RightNow / ServiceCloud) using the ‘schema’ option.
• See a List of all available objects (left pane). If you know the complete View Object name, you may see it by selecting the ‘Show Data Store Paths‘ option at the bottom of the screen. You can also duplicate custom view objects by selecting ‘Add a custom view object‘. 

Step 4: In the Replicate Objects area, select the object(s) that you want to replicate. You can do so by checking the box next to the object(s) you wish to duplicate.

If the view you wish to duplicate isn’t visible in the list for Fusion Applications data sources, select the Add a custom view object option below the list. Here you are required to enter the view’s complete path and name.

By default, when you pick a table, you include all attributes. Use the check boxes on the right-hand pane to select or deselect any attribute. 

Step 5: Select Assign Primary Key or Reorder Primary Key from the ‘hamburger’ menu icon to modify a Primary Key. 

The Primary Key is utilized in upsert operations to determine whether or not a record exists. You can rearrange the columns so that the most selected columns come first and the least selective columns come last. This helps improve indexing. To do this, click the Reorder Primary Key option from the context menu of any of the primary key columns. You can even select the key icon next to each column to include in the key to utilize several columns as a Primary Key.

Step 6: You can duplicate a subset of data based on a filter. Open the Edit Filter option and enter a filter expression (without the closing semicolon). The expression format you use is determined by the filter language supported by your data source. Common filter languages include SQL, XML, and so on.

Step 7: Use the Validate option to confirm the expression and save it before clicking OK.

After selecting the objects for replication, you must pick the Replication Target where the duplicated data will be loaded. You can do this by clicking on the ‘Select’ icon beside Replication Target connection. 

Step 8: If you wish to replicate a subset of data based on a timestamp, click on the Replicate From calendar icon and enter a start date to replicate a subset of data based on a timestamp. This option is only available for tables with at least one incremental identifier field.

Step 9: Use the Load Type to define whether an incremental or complete load should be performed.

When you pick Incremental, you duplicate all data on the first run and just new data on future runs. Tables having a main key and at least one incremental identifier field are required for incremental updates. If you choose Full, the target table is created and all data is replicated.

Finally, save your OAC Data Replication workbook. Now you’ll be able to start the data load by clicking on Run Replication Flow.

Step 3: Running OAC Data Replication

After the successful completion of your OAC Data Replication flow, you can edit, schedule or check on the status of your previous runs by visiting the Data Replications section.

You can either choose to run the OAC Data Replication flow manually as a one-off with ‘Run’, or schedule it using the ‘New schedule’ option. To view these options, you can click on the hamburger icon beside the name of your OAC Data Replication flow.

Step 4: Troubleshooting a Data Replication and Checking Status

It’s just as simple to troubleshoot your OAC Data Replication flows as it is to run them. If you encounter an OAC Data Replication riddled with warnings, you may click the ‘Run Details‘ option to see additional details on that run.

You can view run details for a corrupt OAC Data Replication with specific mention of the errors as shown in the image below:

Also Read: Data Replication Best Practices

Moving Replicated Data to a Different Target Database

If you decide to move your replicated data to a different target database, you can do so by reconfiguring your current target database connections. For instance, you might wish to migrate your organization data from Oracle Cloud Infrastructure – Classic to Oracle Cloud Infrastructure, which is a different target than your prior one.

To execute OAC Data Replication to a different destination, you must complete the following steps:

Step 1: Ensure that your new target schema has the required privileges and permissions.

Step 2: You can copy the replicated tables as well as the replication system tables (listed below) to the new target schema.

• All replicated tables (along with corresponding indexes, and constraints)
• REPL$_ERR_SUMMARY
• E$_*
• SDS_*

Step 3: You must set up a replication connection for your new target database.

• If your new target database is the same kind as your old one, modify your current replication connection and change the connection information. Locate the replication connection on the Connections page, click Inspect, and then use the General tab to set the details for your new target database.
• If your new destination database is of a different type, you must build a new replication connection and describe the connection details. Click Create, then Replication Connection, then input the specifications for the appropriate type.

Step 4: Next, you must update each Data Replication item that is configured to use your previous target database connection information.

• Visit the Data Replications page and choose the one you want to edit.
• In the Replication Target area:
  • If your new target database is the same type as your previous one, ensure that Schema is appropriately configured for the new one.
  • If your new target database is of a different type, click Select and choose the new target connection, then click Schema and configure it properly for the new database.

Step 5: Navigate to Data and Connections from the Home page. Locate your target database’s replication connection, click Inspect, and then utilize the Tables tab to validate the table details for the new target schema.

With the successful completion of this new setup, your OAC Data Replication processes will resume in an incremental mode in your new target database.

OAC vs OAS: Key Differences

Oracle Analytics Cloud (OAC) and Oracle Analytics Server (OAS) represent two industry-leading Business Intelligence and Data Analytics services from the Oracle Corporation. Both deliver a full range of analytical capabilities like interactive dashboards,  data visualizations, augmented analytics, etc., for a wholesome understanding of your business data and actionable intelligence. The primary difference between these two stems from the fact that the former is a cloud-hosted technology and the latter an on-premise deployment.

Oracle Analytics Cloud (OAC) combines the power of fast, fluid Self-Service Data Visualization to simple-yet-sophisticated Data Loading and Blending in the cloud. It provides automatic visualization for your data and a wide variety of Data Connectors to collect and ingest data from Oracle and non-Oracle sources. A significant benefit of using OAC Data Replication is that it reduces your effort to deploy, manage and maintain analytics. 

Oracle Analytics Server (OAS) is an evolution of Oracle’s previously known Oracle Business Intelligence Enterprise Edition (OBIEE). This on-premise deployment is built using a strong technological foundation to help your business breeze through the highest workloads and most complex deployments with ease. It comes with Augmented Analytics for deeper insights, Self-Service Data Visualization for instant clarity on Data Analytics,  Ad Hoc Analysis and Interactive Reporting, and Proactive Detection and Alerts for real-time notifications to concerned stakeholders.

This section discusses the differences between OAC and OAS across four segments: Data Handling, Administration, Maintenance, and Convenience.

• Data Handling
• Administration
• Maintenance
• Convenience

Data Handling

On a high level, both OAC and OAS offer the same set of features when it comes to handling data. 

Data Replication is a feature that is currently only supported by OAC. For OAS the workaround is to use the Dataflows feature. 

Moreover, OAC also supports Thin Client Modeler, a web-based Data Modeler that is easy to use and allows agile metadata modeling. For OAS, there is no workaround for this since  the feature works only with Cloud Analytics service.

Administration

When it comes to administration, given the gigantic shift to cloud infrastructure and changing preferences of many businesses, cloud-hosted OAC gets preferred more over OAS. And the simple reason being OAC is a governed cloud service that lowers your cost of ownership and reduces your effort to deploy, manage, and maintain your analytics. 

A downside to using OAC is that being a governed cloud service, OAC doesn’t allow you and your workgroup to gain access to its host server and its configuration files.

Nevertheless, if your priority is security, granular control, and analytical process optimization for your use case and business applications, OAS might be the optimal solution.  You can also choose to connect your OAS to your on-premise Data Lake for better control and monitoring of Data Collection, Aggregation, and Processing. OAS being an on-prem service gives you complete control over the server and its configuration files. 

Maintenance

In OAC, upgrades are delivered automatically, and enhancements and bug fixes are usually delivered once or twice every quarter. With OAS, upgrades to the system have to be done manually and enhancements and bug fixes happen much occasionally, like once a year.

Convenience

In this OAC Data Replication guide, we will compare the ease of using OAC and OAS across three parameters:

• Data Processing Capabilities: OAC comes with Natural Language Processing (NLP) and Natural Language Generation (NLG), which allows search and voice-driven analysis and automatic visualizations. Using NLP and NLG, you can speak into your microphone, adjust your commands and gather insights with ease. On OAS, there is no NLP; instead, you can use Augmented Analytics to gain deeper insights and simplify data discovery.
• Mobile Friendliness: Oracle Analytics Day by Day is a cutting-edge tool that delivers relevant analytics to your mobile devices. It provides suggestions based on your interests and shows the data in ready-to-use analytical charts. The analytical charts are shown as cards in the app. This feature is currently available only on OAC.
• BI Connector: OAC and OAS come with BI connectors to provide seamless integration with other BI services like Power BI or Tableau.

Final Thoughts

This tutorial provided detailed information on the OAC Data Replication process and the actions required to complete it. We looked at methods to configure our Oracle Source and Oracle Target connection and our Data Replication runs. We also looked at the steps to migrate duplicated data to a new destination database.

Loading data from your data sources into a Data Warehouse or Data Lake can become a lot easier, more convenient, and cost-effective when you use ETL/ELT platforms like Hevo Data. Hevo Data can connect your frequently used databases and SaaS applications like Oracle Database, Oracle Database, Oracle on Amazon RDS, Amazon RDS on Oracle, MySQL, PostgreSQL, and other 150+ Data Sources (50+ free Data Sources) to a Data Warehouse with a few simple clicks.

Hevo features a No-Code, highly intuitive interface, using which you can set up a Data Pipeline in minutes effortlessly in just a few clicks. Hevo can not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis in BI tools like Power BI or Tableau.

Using Hevo is simple, and you can set up a Data Pipeline without worrying about any errors or maintenance aspects. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database.

Visit our Website to Explore Hevo

Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be analyzed in BI Platforms.

Why not try Hevo and the action for yourself? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Thank you for reading! Post your opinions on learning about OAC Data Replication in the comment section below.