Oracle BI Cloud Service Part II: Loading Data


In the first post of the Oracle BI Cloud Service (BICS) series we reviewed some of its key features. In the following posts, we will explain how the service actually works, starting with the data provisioning.

All the data used in the BI Cloud Service has to be stored in the Database Schema Service or Database as a Service, so uploading your data to the cloud is the first step towards a cloud OBI. There are several ways to do it, depending on your specific requirements:

Data Loader.

The default method of uploading your data to the cloud is by using the built-in Data Loader accessible from the BICS homepage.


Figure 1: Accessing the Data Loader tool (from Oracle BI Cloud Service - A First look )


Data Loader is an easy-to-use browser-based application that allows you to upload data from files (txt, csv, xsl, xlsx) with a maximum of 500,000 rows and formatted as number, date, or character (for the moment only UTF-8 encoding is supported). There is also the possibility to perform simple transformations such as Uppercase, Lowercase, Trim Spaces, and Format as a Number.

You can use Data Loader to perform full manual refresh, incremental data loadings, upsert (update/insert), add data to the model, view the load history and correct errors.

SQL Developer

Those who have worked with other Oracle Database products will already know SQL Developer. Now the application features a new cloud connection that you can configure to load data from files (with no row limit) and relational sources to your cloud service. It runs on your local machine, so you need to download it from the OTN downloads webpage and install it locally. Once configured, SQL Developer connects to the cloud service through a set of RESTful web service calls and can be used to load data using SFTP (SQL*Loader utility). You can also create “carts” with multiple objects to be uploaded, and schedule incremental loads.



Figure 2: Loading carts (from Loading Relational Tables Using SQL Developer)

BI Cloud Service (BICS) Data Sync

Data Sync is another application for loading your data to the cloud service and available for download on the OTN downloads webpage. It is a wizard-driven tool supporting the load of data from CSV files and relational sources into the Database Schema Service. It also supports the load and merge of data from different sources: DB2, SQL Server, MySQL, Teradata, and TimesTen, apart from Oracle relational sources.

Use BICS Data Sync to perform incremental data loads, rolling deletes, and loads according to appended load strategies. You can also schedule and monitor data loads (called “jobs”).

Be aware however that Data Sync is not officially released and Oracle does not support it and does not guarantee that it will be supported in future releases (future upgrades are not guaranteed either), so care should be taken when using it.


Figure 3: BICS Data Sync (from Loading On-Premises Relational Sources and Files to the Cloud Using Oracle BI Cloud Service Data Sync)

BICS & Database Schema Service REST APIs

You can also use the BI Cloud Service and Database Schema Service REST APIs to define a customized API and programmatically load data into the Database Schema Service. With the BICS REST API, you can update statistics, drop or create indices on tables, insert, update, upsert, and delete records. You can also combine the API with any other application and script in any programming language, offering endless possibilities such as invoking applications and scripts with an on-premises scheduler of integrating the load process with ETL tools.

Similarly, with the Database Schema REST API you can call out SQL queries to read data and return results, call PL/SQL scripts to read, write, modify or delete data, and define your own customized API to be invoked from your on-premises environment. Moreover, using the APEX_WEB_SERVICE package within a PL/SQL block, you can invoke any REST/SOAP API supported by cloud applications, and retrieve data from external systems.

PL/SQL Scripts

Finally, you can use PL/SQL scripts to load data from external, generic web services. Scripts can be created using SQL Workshop, a robust browser-based tool part of APEX that allows you to create and execute SQL queries and database procedures against objects in the Database Schema Service.


Check out part III of the BICS series in which we will cover how you can model your data in the cloud according to your business requirements.


Oracle BI Cloud Service Part I: Introduction & Key Features


Last year Oracle released the Business Intelligence Cloud Service (BICS) as part of its Platform as a Service (PaaS) offering, and it has been releasing upgrades regularly since then, some with important new features. In this BICS post series we will be giving an overview of the BI cloud service and walking you through some of its key features and functions:

  • Part I: Introduction & Key Features
  • Part II: Loading Data
  • Part III: Modeling Data
  • Part IV: Creating analyses, reports and dashboards
  • Part V: Managing the Service & Conclusion

Cloud technologies are not something from the future anymore but a reality, with some operating systems like Google Chrome OS relying almost completely on cloud applications. Trends indicate that the shift towards cloud environments will accelerate even more in the next years, so companies are rushing to release cloud versions of all their software solutions. Oracle has been releasing many of its software platforms into the cloud over the past years, and last year was the turn of OBI with the Business Intelligence Cloud Service.

Captura 1

Figure 1: Business Intelligence Cloud Service Web Page



Below are some of its key features.

The service is 100% cloud-based, with almost no local installs needed (some data loading tools might be required locally), and it comes bundled with the Oracle Database Schema Service, as BICS can only report against data stored in the cloud. With the Database Schema Service you get 1 schema on Oracle Database 11g with 50GB of storage, full PL/SQL support, multiple tools for loading the data, Oracle Exadata hardware running in the background, RESTful web services, and Oracle Application Express (APEX), but no SQL*Net access. Since the last release, there is also the possibility to connect the BI service to a Database as a Service (DBaaS), which includes a dedicated virtual machine running Oracle Database 11g or 12c with full SQL*Net access and all the storage capacity we may need.

One of the most interesting features of the service is that it is 100% mobile ready: all the content you create in the cloud will be immediately available in any iOS or Android device downloading the Oracle BI Mobile App from the App Store or Google Play and with no extra programming required (touch gestures such as zoom or swipe are already built in).

Capture 2

Figure2: 100% mobile ready. 

(From the Oracle BI Cloud Service & Data Discovery Webinar from Oracle Deutschland B.V. & Co. KG,

available at

 The service comes with two instances: PROD and pre-PROD for developing and testing, and provides automatic full system backup & restore, role based grain security, and simple self-administration. All the patching and system maintenance is handled by Oracle so you don’t have to worry.

So what are the benefits of the BI Cloud Service compared to the traditional on-premise OBI? The first benefit we can think of is the huge cost reduction: you only have to pay a monthly subscription of $250 per month and per named user (10 users minimum) for the BI Cloud Service plus $1000 per month for the 50GB of cloud storage in the Database Schema Service, so a minimum of $3500 per month, which is much cheaper than an OBIEE license. Not having to purchase annual licenses and being able to pay just for the storage capacity you require are big bonuses too. Moreover, working in the cloud means working with a standardised and consolidated infrastructure (there is no need for servers, storage, or any other network components). Therefore, you get to dramatically reduce your capital and operation expenditures compared to the expenditures you would incur with on-premises OBI.

As you need fewer database elements and you get standardised operating systems, servers, database versions and configurations, the administrative burden is also dramatically reduced. Also given that the system maintenance is fully managed by Oracle, you get guaranteed system availability. This means that overall the environment will be more reliable and manageable, resulting in lower risks for the user.

Finally, another key benefit of using the BI Cloud Service is agility. The rapid deployment of environments for development and production and the shorter upgrade cycle make the system highly agile, and thanks to the Oracle Real Application Clusters (RAC) you get adaptive cloud databases for workload volumes.

Be sure to check out the rest of our BICS series posts for more detailed information on the basic features of the service.


privacy policy - Copyright © 2000-2010 ClearPeaks