A Look at BI Cloud Service Data Loading Options

With Oracle BI Cloud Service (BICS), there are multiple options for loading in data from your on-premise applications. This post explores the available approaches.

Back in September 2014 Oracle released Business Intelligence Cloud Service (BICS), a fully managed Business Intelligence environment on Oracle's cloud platform - Oracle's solution for bringing agile, self-service analytics to "everyone". BICS has gained quite a bit of attention since it's release with customers realising the potential in leveraging Oracle's cloud infrastructure to quickly and easily analyse their data, on a subscription basis, without going through the usual complicated IT processes.

Today's blog post is the first in a series of posts where we will cover some of the main features and functionality we've found while working with BICS. In this post I will go though some of the options available to you for migrating data from your on-premise source/s into a BICS instance so that you can get to the fun stuff - analysing the data!

The Importance of the Underlying Database Subscription

One of the questions that inevitably comes up when talking about BICS goes something like "how do we push our data into the cloud?" Well, you have a few options and they depend on what type of BICS instance (database instance actually) you've subscribed for, so let's take a look at these first. Note that I've emboldened the S and B below to highlight the difference between the two acronyms because I'll be using them a lot:

• BICS + Database Schema as a Service (DSaaS)
• BICS + Database as a Service (DBaaS)

Both options provide you with a BI Server in the Oracle cloud so no difference there, the difference is what type of database cloud offering you get. DSaaS is the standard option when you subscribe to BICS and, as the name implies, you get a single database schema (currently only 11g) as a source; DBaaS on the other hand provides you with a fully-fledged database on it's own dedicated virtual machine. You can look at the specific differences between the two here but there is one key difference in terms of how these affect loading data into your BICS instance. With DSaaS you'll need to use the tools and APIs provided by Oracle (described below) to load your data but with DBaaS, along with the same tools and APIs, you are given full SQL*Net access to your database.

Obviously DBaaS's full SQL*Net access is going to give you the most flexibility of the two options - you'll be able to use your existing ETL tools without much change at all - but you'll probably be surprised to learn, as I was, that the "tools and APIs" I mentioned above provided with BICS + DSaaS are actually quite flexible. Here is a summary of the tools and APIs provided to you with each instance:

BICS + DBaaS

• All of the BICS & Schema as a Service tools (below) plus you get full SQL*Net access

BICS + DSaaS

• BICS Data Loader
• BICS Data Sync Utility
• BICS REST API
• SQL Developer

In this post, we'll be covering the four options above.

Data Loading Options

BICS Data Loader

The BICS Data Loader is by far the easiest way to load data into your cloud DBaaS/DSaaS instance; you can access the Data Load Wizard directly from the new BICS home page shown below. You'll then be taken through a set of steps to upload a denormalised flat-file in a variety of formats: xls/x, txt, csv and zip. The Data Loader will allow you to upload a file with a maximum of 50 columns and 750,000 rows, if you need to upload more your best option is to use SQL Developer. Once you've uploaded your data you'll then be able to use the BICS Data Modeler to blend it with an existing subject area.

BICS Data Sync Utility

The BICS Data Sync Utility (not to be confused with the Data Loader above) is a DAC-like utility which needs to be downloaded and installed on a machine, preferably a server, which can access your on-premise database/s or flat-file/s. The Data Sync Utility allows you to move on-premise data residing in a variety of relational sources and CSV files into your cloud database. The advantage of using this over the Data Loader, apart from being able to upload data from a database, is that you can get very detailed with how you want the data to be pushed, here are some of the things you can do with the Data Sync Utility:

• Define connections to multiple targets, sources including non-Oracle and CSV based data sources
• Specify recurring, scheduled jobs to load data into BICS
• Define a 'Load Strategy' to tell the Data Sync Utility to either replace (full), update (Incremental) or append tables in BICS
• Drop or create indexes as well as analyse tables!
• Send email notifications on error or completion of the load
• Define simple data transformations and remove duplicates

We've heard that oracle have a few features in the works for the Data Sync Utility too - like extending the flat-file source support to Excel, XML, JSON and providing the ability to download from BICS - so expect to see some improvements to this tool going forward. Here's what it looks like - DAC anyone?

BICS REST API

The BICS REST API is probably the most flexible of all DSaaS options as it allows you to write your own code, or better yet, use your existing ETL tools to create, manage and load data into BICS. With the REST API you can do things like: get the details of the BICS schema, tables and indexes; update table statistics; create, update, delete tables and indexes; and get the version and usage information on your BICS instance. There isn't much you need to do to get started with the BICS REST API either - all you really need is the URL, username, password you use to log into BICS. The following example uses CURL to send a GET request to retrieve a list of all the tables in your schema:

curl -u <user-name>:<password> -X GET -H "X-ID-TENANT-NAME: <tenantname>" http://<URL>/dataload/v1/tables

Note that the "X-ID-TENANT-NAME" header needs to be included in every request you send to the REST API, it is your Identity Domain which you can derive from your BICS URL - your URL will look something like <servicename-identitydomain>.analytics.<dc>.oraclecloud.com/analytics.

As you can probably tell REST API provides a lot of flexibility - you can do much more than simply listing out the tables! It is definitely something that is geared towards developers however, whereas the Data Loader and Data Sync utilities are more focused towards administrators or power users. As well as being extremely flexible the REST API gives you the option of using your existing on premise ETL tools like ODI - which can already make use of REST APIs to load data into BICS - a much better option to writing custom code.

SQL Developer

Another option that Oracle make available for both DSaaS and DBaaS is the ability to use SQL Developer to manually load data into BICS using data 'carts'. After setting up your cloud connection in SQL Developer you'll see the database show up under the "Cloud Connections" heading. You can then drag tables from other connections in SQL Developer into a cart and deploy it to your cloud database instance. Clicking on the cart in SQL Developer tells you when the deployment has been successful, and you'll then be able to view the new table in SQL Developer and in the BICS Data Modeler tool.

Using SQL Developer is a great way to shift some data up to your DSaaS or DBaaS instance and it's really easy to set up - especially since most people already have SQL Developer installed on their machines. It is a bit of a manual process however, albeit a quick one, so you'd probably only use it for those once-off / infrequent loads into BICS.

As can see there are quite a few options available to you for loading data from your on-premise sources into BICS for both DSaaS and DBaaS and the one you choose will depend on your specific requirements. Even without the full SQL*Net access you get with DBaaS there are still some good options available to you which will suit most of your data loading and ETL requirements. There are even some others that we haven't covered in this post and may cover later in the series: the DBCS Rest API and PL/SQL Data Import.

Hopefully this overview has helped you understand the different types of data loading options available to you with BICS, feel free to contact us if you have any questions or need any advice on what options will best suit your needs.

Find out more

Boxfusion Consulting are an Oracle Platinum Partner, recognised by Oracle as being "Specialised" in the implementation of Oracle BI technology. If you would like to hear more about how Oracle BI or Oracle BI Cloud Service might be used to help your business please give us a call on +44 203 283 4315 or contact us here!