20 Oracle Business Intelligence Cloud Service

It is important to understand how to work with Oracle Business Intelligence Cloud Service (BICS) in Oracle Data Integrator.

This chapter includes the following sections:

20.1 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 20-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

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.

20.2 Setting up the Topology

Setting up the topology consists of:

20.2.1 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

20.2.2 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.

20.2.3 Importing BICS Certificate into Trust Store of Standalone Agent

ODI Studio’s local agent uses the JDK’s certificate store, whereas the standalone agent does not. It is therefore possible, and quite likely, that while the local agent will provide a successful connection to the BICS server, the standalone agent may produce an error in establishing the connection. To resolve this issue, the BICS Certificate needs to be added to the trust store used by the standalone agent.

Perform the following steps to import the BICS certificate to the trust store of the standalone agent:

  1. In a browser, open the BICS /analytics portal and then click on the padlock icon. This opens an information box.
  2. Click View certificates from within the information box. This opens the Certificate dialog box.
  3. In the Details tab of the Certificate dialog box, click Copy to File. This opens the Certificate Export Wizard dialog box.
  4. Select the DER encoded binary X.509 (.CER) format and click Next.
  5.  Choose a path and file name for the certificate and click Next.
  6. Click Finish to export the certificate.
  7. Copy the certificate file created in the previous steps to a file system accessible by the host running the standalone ODI agent.
  8. Set JAVA_HOME to the path of the JDK used while installing the standalone agent. For example, export JAVA_HOME=/u01/oracle/jdk1.8.0_111/bin.
  9. Browse to the bin directory of the ODI Domain Home.
  10.  Run the setODIDomainEnv script.  In a linux environment, this would be: ./setODIDomainEnv.sh.

    The DemoTrust.jks keystore used by the agent should be located in the following path:

    $ORACLE_HOME/wlserver/server/lib

    Note:

    There may be a number of DemoTrust.jks key stores on the file system. So it is important to ensure that the correct one is updated.  If this process fails to resolve the error with the standalone agent, search the file system to check whether it is using a different trust store.

  11.  Browse to the required directory and confirm that the DemoTrust.jks file exists. In the same directory, run the keytool command to import the certificate created earlier.

    The syntax for the command is as follows:

    keytool -importcert -file $CERTIFICATE -alias $ALIAS -keystore $KEYSTORE

    where $CERTIFICATE references the name/path for the certificate file downloaded from the BICS environment through the browser, $ALIAS is a name for this file, and $KEYSTORE is the name/path of the key store.

    For example, keytool -importcert -file /u01/oracle/Downloads/BICS.cer -alias BICS -keystore DemoTrust.jks.

    This displays the details of the certificate, and a prompt to ‘Trust this certificate?’ appears.
  12. Type yes and then hit enter.
    If the import is successful, a confirmation that the certificate was added to the keystore is given.
  13.  Return to ODI and run the mapping, this time selecting the standalone agent, and confirm that it runs successfully.

20.3 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 20-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.

20.4 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 20-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.