Oracle Business Intelligence Cloud Service

Introduction

Oracle Business Intelligence Cloud Service (BICS) uses entities called Datasets and Tables for storing data that then get used in an analytics solution.

Table 21-1 Datasets versus Tables

Datasets	Tables
Does not have index	Can have index
Creation and insertion of data can be a single operation	Creation and insertion of data are two distinct steps with different payloads
Loading data supports batching explicitly	Loading data involves more fine-grained controls For example, maximum number of errors to be allowed, while loading Also, more controls exist over column definition

Datasets

Tables

Does not have index

Can have index

Creation and insertion of data can be a single operation

Creation and insertion of data are two distinct steps with different payloads

Loading data supports batching explicitly

Loading data involves more fine-grained controls

For example, maximum number of errors to be allowed, while loading

Also, more controls exist over column definition

Note:

Both the Datasets and the Tables have the parameters ‘firstBatch’ and ‘lastBatch.’ They are backed by a DBCS schema. The DBCS schema information is not published. Data is loaded into Datasets/Tables as application/octet-stream format part of a multi-part message. The stream can be Text stream with delimiters or Java object array stream. ODI will load data only into the BICS Dataset or the Table. ODI will not read data from the BICS Dataset or the Table.

For both the Dataset and the Table, you must define a BICS target. As each of these entities are bound to a different URL endpoint, an ODI Datastore container will be bound to either Dataset or Table, but never to both. This implies that an ODI Model (and by inference the associated Logical and Physical Schema) can be only bound to either the Datasets endpoint or the Tables endpoint.

Since loading data into a BICS target involves Mappings, you must model a BICS Dataset or Table as a Datastore in ODI. BICS Logical Schema cannot be used for staging, and BICS Datastores cannot be used as source in a Mapping.

Setting up the Topology

Setting up the topology consists of:

Creating an Oracle BICS Data Server

BICS Dataserver defines the endpoint URL and the dataloader suffix. The data source suffix part of the URL depends on whether we are exploring Datasets or Tables and will be exposed in the Physical Schema page. This will allow a single BICS Dataserver to work with both the Dataset and the Table.

The Data Server page contains fields for the Dataserver name, base URI, username, password, and the Identity domain.

The following is a full BICS URI:

https://service-identity_domain.analytics.data_center.oraclecloud.com/resource-path

The base URI is the BICS service instance’s first paths segment.

The following is a base URI:

https://service-identity_domain.analytics.data_center.oraclecloud.com

The Data loader path field is a constant, auto-filled. This enables you to see the path segment.

The following is a data loader path segment:

/dataload/v1

Creating an Oracle BICS Physical Schema

Once the BICS Dataserver is configured, you can configure its Physical Schema. BICS Physical Schema will prompt for choosing either Dataset or Table. This in turn will control the Resource URI.

Note:

Resource URI can be chosen from the list or typed in, but once chosen/typed in and then saved, it cannot be edited again.

Choice of whether the Physical Schema is to be bound to BICS Tables or Datasets triggers the association of REST Operations. The Operations are unique and pre-defined for Datasets and Tables.

Reverse Engineering a BICS Model

Once the BICS Logical Schema is set, you can create a Model based on this Logical Schema, and then reverse engineer. You must select the RKM Oracle BI Cloud Service to reverse engineer the BICS tables or datasets metadata.

Note:

After reverse engineering, make sure to manually fix the column datatypes, after seeing the BICS Table/Dataset.

Table 21-2 KM Options

Option	Type	Default	Description
GET_TABLE_INDEXES	Table	True	Whether or not to retrieve table indexes.
DEFAULT_DIRECTORY	Table	java.lang.System.getProperty("java.io.tmpdir")	Directory for generated temporary (return) files by REST calls. All temporary data files generated by REST calls will be deleted at the end of RKM execution.

Option

Type

Default

Description

GET_TABLE_INDEXES

Table

True

Whether or not to retrieve table indexes.

DEFAULT_DIRECTORY

Table

java.lang.System.getProperty("java.io.tmpdir")

Directory for generated temporary (return) files by REST calls.

All temporary data files generated by REST calls will be deleted at the end of RKM execution.

Designing a Mapping

Similar to the IKMs for Hyperion, BICS IKM is also multi-connect. It uses batching capabilities of the BICS Dataset/Table.

Note:

The IKM SQL to Oracle BI Cloud Service does not support loading the Oracle SDO_GEOMETRY data type column to the BICS target table.

Oracle BI Cloud Service cannot be used as the staging area, and does not support incremental update or flow/static check. Therefore, the following KMs will not work with the Oracle BI Cloud Service technology:

RKM SQL (JYTHON)
LKM File to SQL
CKM SQL
IKM SQL Incremental Update
IKM SQL Control Append
LKM SQL to SQL (JYTHON)

BICS Datastore as target for Mapping

The IKM SQL to Oracle BI Cloud Service exposes Dataset/Table loading options as KM options.

Table 21-3 Supported KM Options

Option	Type	Default	Description
TRUNCATE_TARGET_TABLE	Boolean	False	Deletes data before starting to load data. This is only applicable for BICS Table.
DROP_TARGET	Boolean	False	Drops the target Table/Dataset before starting to load data.
CREATE_TARGET	Boolean	False	If the target Table/Dataset does not exist, creates it first. Note: The REST API Dataset data load semantics is ‘create-if-does-not-exist’. So this setting is optional for Datasets.
DATA_WRITE_MODE	Choice	Insert all	Choice between Insert all, Insert missing, Upsert, Update only. This is applicable only if the target is BICS Table. Choosing Upsert/Update only will fail, if the BICS Table does not have unique indexes.
NUM_RETRIES	Text	0	Each dataload batch operation could error out. This is a numeric option that will allow retry. Default is not to retry at all.
RETRY_DELAY	Text	5	Time delay in seconds between each retry attempt.
REMOVE_DUPLICATES	Boolean	False	Applicable only for BICS Table to indicate whether or not to remove duplicate data from within the batch that is being sent. Does not touch data already in the BICS Table.
BATCH_SIZE	Text	1000	Number of rows to be send at one time (in one POST request).
VALIDATE_COLUMNS	Boolean	False	Whether to validate the BICS target’s column names before trying to load data.
MAX_ERR_PER_BATCH	Text	0	Maximum number of errors per batch that Oracle BICS will allow. Applicable only for Tables.
TRACE_FILE	Text	Empty	Location of file to which trace of all the REST calls made by the IKM are logged. If left empty, no trace will be created.

Note:

Datasets

BICS Datasets do not have indexes.
No unique index errors will be raised on loading data.
Only possible errors are when data does not match the datatype of the target column.

Tables

BICS Tables support unique indexes.
The insert/update modes depend on unique indexes being present and being part of the data load operation.
‘Remove duplicates’ also requires unique indexes.
Insert missing/Update only/Upsert all require unique index be part of the data load
‘Insert all’ does not need unique index as long as the columns involved in the data load are nullable.