1. Database Administrator's Guide for Oracle Essbase
  2. Design Rules for Data Load or Dimension Build
  3. Define Rules that Query External Sources
  4. Access Data Using ODBC Connectivity
  5. Special SQL Configuration Options

Special SQL Configuration Options

Configure esssql.cfg on the Essbase domain when you want to connect to a database using ODBC drivers not included by Oracle with Essbase, or when you want to change the default settings for any of the drivers that are distributed with Essbase.

Use Case

One use case requiring a change to the default settings is when you need to enable multi-threaded SQL data loads (which are not the same as parallel data loads) using any driver (whether JDBC, ODBC, included with Essbase, or not included with Essbase). For example, when loading SQL data into aggregate storage cubes, you can use up to eight rule files to load data in parallel. Each rule file must include the same authentication information (SQL user name and password).

Essbase initializes multiple temporary aggregate storage data load buffers (one for each rules file), where data values are sorted and accumulated. When the data is fully loaded into the data load buffers, Essbase commits the contents of all buffers into the cube in one operation, which is faster than committing buffers individually.

In the following MaxL example, SQL data is loaded from two rules files (rule1.rul and rule2.rul):

import database AsoSamp.Basic data 
   connect as TBC identified by 'password' 
   using multiple rules_file 'rule1' , 'rule2' 
   to load_buffer_block starting with buffer_id 100 
   on error write to "error.txt";

Change the SQL Configuration

To enable multithreaded data loads from external SQL sources as in the use case above, you must change the default settings for the drivers that are distributed with Essbase, by modifying esssql.cfg. Otherwise, concurrent users attempting data loads will encounter an error such as: ERROR - 1021025 - SQL driver [driverLibrary] for [DriverName] is in use already and does not allow multiple connections. Please try later.

You also need to add entries to esssql.cfg when you want to connect to a database using ODBC drivers not included by Oracle with Essbase.

To change the SQL configuration settings,

  1. Locate the SQL configuration file on the Essbase domain: 

    <DOMAIN HOME>/config/fmwconfig/essconfig/essbase/esssql.cfg

  2. Save a copy of the file with a different name. For example, make a copy named esssql_cfg.orig.

  3. Open esssql.cfg for editing.

    You configure this file using multiple blocks (one for each driver). Blocks are delineated by square brackets []. You can copy and paste a block, and edit the settings as needed for each driver.

  4. Make the necessary changes, depending on your requirements, and save the file. See esssql.cfg Options below. To enable multi threaded operations, for example, change the value of SingleConnection to 0.

  5. Stop and restart the Essbase server, as described in Start, Stop, and Check Servers.

Sample esssql.cfg File

The following sample esssql.cfg has 6 configuration blocks. The first one is there by default, and the next five are added as examples of configuring different sources to enable multi-threaded SQL data loads. 

[
Description "Oracle BI Server"
DriverName libnqsodbc
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
ConvertUTF16toUTF8 1
]
[
Description "SQL Server"
DriverName SQLSRV
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]
[
Description "Oracle SQL Server Wire Protocol"
DriverName ARSQLS
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 1
]
[
Description "Oracle DB2 Wire Protocol"
DriverName ARDB2
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 1
]
[
Description "Oracle MySQL Wire Protocol"
DriverName ARMYSQL
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 1
]
[
Description "Teradata"
DriverName TDATAODBC_SB64
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]

For the Teradata DriverName, you can use either TDATA or TDATAODBC_SB64.

esssql.cfg Options

The esssql.cfg configuration file must contain at least the driver file name (DriverName). Optionally, it can include a description (Description). The configuration file may contain additional keywords, the values for which are 0 or 1.

To find the DriverName on Linux, look in odbcinst.ini, located in 
<Essbase Product Home>/modules/<driver class>/<driver version>
The driver name is at the end of the path given for the driver.

For example, from the following entry in odbcinst.ini, we know that the driver name is ARora (no need to include the version information, 28): 

Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARora28.so

To find the DriverName on Windows, look in the Windows 64-bit ODBC Data Source Administrator utility, on the Drivers tab.


ODBC Data Source Administrator tool with Drivers tab selected. Oracle 8.0 Oracle Wire Protocol is highlighted. Its driver file name is ARORA28.DLL.

Keyword Value Value = 0 Value = 1
Description (Optional) A description of the driver, enclosed in double quotation marks. The default value is "" N/A N/A
DriverName (Required) The driver file name; for example ARORA N/A N/A
UserId 0 or 1 User ID not required (default) User ID required
Password 0 or 1 Password not required (default) Password required
Database 0 or 1 Database name not required (default) Database name required
Server 0 or 1 Server name not required (default) Server name required
Application 0 or 1 Application name not required (default) Application name required
Dictionary 0 or 1 Dictionary name not required (default) Dictionary name required
Files 0 or 1 File name not required (default) File name required
SingleConnection 0 or 1 Driver thread-safe -- Multiple active connections permitted Driver not thread-safe -- One active connection permitted
IsQEDriver 0 or 1 Driver is not shipped with Essbase (default) Driver is shipped with Essbase
ConvertUTF16toUTF8 0 or 1 No conversion of UTF16 data to UTF8 (this is the default). Convert UTF16-encoded data from an Oracle BI data source to UTF8. This is required on UNIX for SQL data loads to Essbase from OBI.

Defaults apply to values that are not specified. The defaults applied within configuration files differ from the Essbase default values that apply if no esssql.cfg file exists.

Keywords and values must be separated by at least one space, and the set of keywords and values for each driver must be enclosed within brackets ( [ ] ).

Different drivers may require additional values. See the driver documentation for specific information.