X

Best Practices from Oracle Development's A‑Team

Extracting Data from Fusion SaaS via View Objects with Data Sync

For other A-Team articles by Richard, click here

Introduction

The Oracle Data Sync tool provides the ability to extract from both on-premise, and cloud data sources, and to load that data into Oracle Analytics Cloud Service (OAC), Oracle Autonomous Analytics Cloud Service (OAAC), and other oracle relational databases, or into Essbase.

In the most recent 2.5 release, Data Sync added the ability to pull data directly from Fusion SaaS view objects.  The same view objects that other BI reporting, and extract tools use.

In a recent article I covered how Data Sync can consume files created and saved into UCM by the BI Cloud Connector (that article is here).

In this article I'll cover how the Data Sync tool can be set up to pull the same data, directly from the Fusion view objects.

There are pros and cons to both approaches.  Your use case will dictate which best fits your needs:

  • The BI Cloud Connector, for some use cases, will be the preferred solution for extracting data.  It already has a full list of all view objects available to choose from, and will allow you to select as many as you like in a single extract, as well as easily picking the fields you need.  It can generate full, or incremental extracts for you, it can create the data and primary key extracts too, as well as providing a metadata file to help understand the data better.  So the set-up of the extract is definitely easier.  But then you need to build a process to take the extract file, and load that into the target database (as this article showed).
  • With the Data Sync tool going directly against the view objects, you need to know the name of the view object(s) ahead of time, and the fields that you want to extract, so the set-up will take more time.  But the Data Sync tool has the ability to orchestrate the whole process, from scheduling, to extracting the data, and to loading that data into the target database.  It does also have the ability to set up full and incremental extracts, and to do primary key extracts.  So while set-up will take longer, the same tool could be used for the whole extract and load process.  To improve performance, and lessen the load on the BI Server, for larger data sets (100K rows +), it is recommended to make use of partitioned reads, and / or incremental updates.  Both of which are covered in this article.

Note:

Licensing for the Data Sync tool was intended originally for customers of BICS, OACS, or OAAC.  

However, if the customer licenses Oracle products, then they can use the Data Sync tool based on the OTN license agreement.

This means the product is available to other customers, for instance Fusion SaaS customers, who would like to set up data extracts from Fusion and load that to an oracle DBCS instance, or on premise.  

For more details, check the OTN Licensing Agreement link on this download page.

 

Download the Latest Version of Data Sync Tool

Be sure to download and install the latest version of the Data Sync Tool from OTN through this li