1. Database Administrator's Guide for Oracle Essbase
  2. Work with Load Rules
  3. Define Rules that Query External Sources
  4. Stream from a Remote Database

Stream 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) utility to push the data or members to your cube using a rule file.

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.

Workflow for Streaming

  1. Log in to the Essbase web interface and download the CLI from the Console. See Download and Use the Command-Line Interface (CLI).

  2. Log in to the CLI. See Login/Logout: CLI Authentication.

  3. Create a saved JDBC connection string that reflects your data source’s driver and connection strings, using the createlocalconnection command. Example (for Teradata): 

    esscs createLocalConnection -name TeraConn -connectionString jdbc:teradata://192.0.2.110:1025/TBC -driver com.teradata.jdbc.TeraDriver -user TBC –p TBC

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

  5. Run the CLI dataload or dimbuild commands with the streaming option, providing the saved connection name.

    You can optionally specify the database query in the dataload or dimbuild command, as in the Teradata example below. Otherwise, you can specify it in the load rule.

    esscs dataload -v -application Sample -db Basic -rule Data.rul -S -conn TeraConn -query "Select * from tbc.product"

EXTERNAL_CLASSPATH Examples

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