1. Database Administrator's Guide for Oracle Essbase
  2. Performing and Debugging Data Loads or Dimension Builds
  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 Essbase Command Line Interface (CLI) to push the data or members to your cube.

When you use the stream option for the CLI dataload or dimbuild commands, you must also reference a saved JDBC connection that reflects your driver and connection strings.

Before you Begin

  1. The load rule file must exist in the cube directory.

  2. The database query used to load data or build dimensions must have the same dimensionality as the columns in the load rule (in other words, the order of dimensions in the rule 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 command.

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

  3. Run the CLI dataload or dimbuild 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 rule (in SQL Properties).

Examples of Setting EXTERNAL_CLASSPATH Environment Variable

You must set the EXTERNAL_CLASSPATH environment variable before you can stream from any data source other than Oracle Database. Set the variable to point to the location of the 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

Windows Example

set EXTERNAL_CLASSPATH=C:\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

Windows Example

set EXTERNAL_CLASSPATH=C:\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

Windows Example

set EXTERNAL_CLASSPATH=C:\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

Windows Example

set EXTERNAL_CLASSPATH=C:\db\jars\tdgssconfig.jar;C:\db\jars\terajdbc4.jar