1. Using Oracle Analytics Cloud - Essbase
  2. Build Dimensions and Load Data
  3. Build Dimensions and Load Data by Streaming from a Remote Database

Build Dimensions and Load Data by Streaming from a Remote Database

If the data or dimensions you want to load to a cube are in a remote database, you can use the stream option in the Oracle Command Line Interface (CLI) utility, to push the data or members to your cube, using a rule file.

When you use the stream option for the CLI Dataload: Load Data to a Cube or Dimbuild: Load Dimensions to a Cube command, you must also reference a saved JDBC connection that reflects your driver and connection strings.

Before you Begin

  1. The rule file must exist in the Files section for the relevant database.

  2. The database query used to load data or build dimensions must have the same dimensionality as the columns in the rule file. (For example, see Build Dimensions Using SQL , where the order of dimensions in the rule file must match the order of dimensions in the SQL query).

Limits

  • Substitution variables are not supported in SQL statements used in load rules.

  • Only use SQL functions that are supported by JDBC. ODBC scalar functions are not supported in CLI.

Workflow for Streaming Dimension Builds and Data

  1. Create a saved JDBC connection string that reflects your data source’s driver and connection strings, using the CLI Createlocalconnection: Save a JDBC Connection command.

  2. (Not required for Oracle database) Set an environment variable EXTERNAL_CLASSPATH to point to the .jar file for your database driver. See theExamples of EXTERNAL_CLASSPATH Environmental Variablessection in this topic.

  3. Run the CLI Dataload: Load Data to a Cube or Dimbuild: Load Dimensions to a Cube command with the streaming option, providing the saved connection name.

    You can optionally specify the database query in the dataload or dimbuild command. Otherwise, you can specify it in the load rules, in the Select section of the Data Source tab. For examples, see Build Dimensions Using SQL and Load Data Using SQL.

Examples of EXTERNAL_CLASSPATH Environmental Variables

You must set the EXTERNAL_CLASSPATH environment variable before you can stream from any data source other than the Oracle database. Set the variable to point to the location of relevant database driver .jar file.

DB2

Set the external classpath variable to point to the location of the DB2 driver jar file.

C Shell Example

setenv EXTERNAL_CLASSPATH /scratch/db/jars/db2jcc.jar

Korn or Bash Shell Example

export EXTERNAL_CLASSPATH=/scratch/db/jars/db2jcc.jar

MySQL

Set the external classpath variable to point to the location of the MySQL driver jar file.

C Shell Example

setenv EXTERNAL_CLASSPATH /scratch/db/jars/mysql-connector-java-5.1.43-bin.jar

Korn or Bash Shell Example

export EXTERNAL_CLASSPATH=/scratch/db/jars/mysql-connector-java-5.1.43-bin.jar

Microsoft SQL Server

Set the external classpath variable to point to the location of the SQL Server driver jar file.

C Shell Example

setenv EXTERNAL_CLASSPATH /scratch/db/jars/sqljdbc4-3.0.jar

Korn or Bash Shell Example

export EXTERNAL_CLASSPATH=/scratch/db/jars/sqljdbc4-3.0.jar

Teradata

Set the external classpath variable to point to the location of both Teradata driver jar files.

C Shell Example

setenv EXTERNAL_CLASSPATH /scratch/db/jars/tdgssconfig.jar:/scratch/db/jars/terajdbc4.jar

Korn or Bash Shell Example

export EXTERNAL_CLASSPATH=/scratch/db/jars/tdgssconfig.jar:/scratch/db/jars/terajdbc4.jar