Art of BI: Data Sync with Oracle BICS

In 2014, Oracle launched its cloud BI tool, called Business Intelligence Cloud Service (BICS), which offers data analytics through SaaS. In order to adopt this service for their organizational needs, businesses first have to migrate their relational data to a cloud-hosted Oracle data warehouse.

With the initial release, Oracle offered a few alternative solutions to achieve this, such as:

• Data Loader
• SQL Developer Carts
• REST API

In the recent upgrades of BICS, Oracle has introduced a new utility called Data Sync – a topic covered extensively in Datavail’s recently released white paper, Getting Started with Oracle BI Cloud Service.

The purpose of the Data Synch tool is to load the original file source — be it relational databases or flat files — to the cloud data warehouse. This blog post looks at this feature in-depth and explains how to use it.

Getting to Know Oracle BICS Data Sync

In order to use the Data Sync feature, you need to install the tool into the on-premise data store environment. The latest Data Synch version (Oracle BICS Data Sync 2.2.1) can be downloaded here. You can also download the Remote Data Connector 1.2, which is used to query data from your cloud BI setup without migrating.
Data Sync feature can be used with the following databases:

• Oracle
• MS SQL Server
• DB2
• Teradata
• MySQL
• Oracle Times Ten

The following image, courtesy of Oracle, shows the main UI of the Data Sync tool:

There are three main parts of the tool:

• Connections: Define the source and target connections for the databases.
• Project: Define the files and attributes for the source and target, and the load strategies for the tables in the schema.
• Jobs: A work unit responsible for the task of uploading data from one or more sources to the target cloud database. A project usually consists of one or more jobs. You can also view the status of data loads when the data sync is underway.

Running a data sync job is as easy as defining source and target connections, selecting the tables to sync data from, and starting the loading process. The source connection is the on-premise database from which to load the data, and the target connection is the Oracle BI cloud service. Once you start uploading, you can see the status of the data transfer from each table, under the Jobs tab, as shown below.

Using a Load Strategy

There are three load strategies available with the Data Sync tool. They are:

1. Replacing Data in Table

At each data sync run, the tables in the cloud database are truncated and fresh data is loaded. 

2. Appending Data in Table

Without checking if the data already exists in the cloud database, data from the source is appended to the target table.

3. Update Table

The tool will look for existing data in the target table and decide whether to add the incoming records or update the existing ones.

Scheduling Jobs

The Data Sync tool allows users to schedule jobs to load data from the source to target database. This can be done under the Jobs Schedule tab, as shown in the following screenshot,

Overall, Oracle BICS introduced the Data Sync feature to facilitate easy loading of existing data from an on-premise database to the BICS data warehouse. Data Sync is a very straightforward tool that makes data migration easier.

To learn more about Data Sync and several other new features of Oracle BICS, please download our white paper, Getting Started with Oracle BI Cloud Service, or contact Datavail today.

Datavail is the largest provider of data and database administration (DBA) services in North America with more than 600 DBAs, analysts, developers and consultants. We offer 24×7 managed services and project consulting in database administration, BI/Analytics, data warehousing, and enterprise performance management. Datavail’s expert consulting team specializes in integrations, implementations, and upgrades for applications across the Oracle stack, including Hyperion, OBIEE, OBIA, GoldenGate, Weblogic and more.