Performing Multiple SQL Data Loads in Parallel to Aggregate Storage Databases

When loading SQL data into aggregate storage databases, you can use up to eight rules files to load data in parallel. Each rules file must use 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 database in one operation, which is faster than committing buffers individually.

Note:

This functionality is different than using the import ... data to load_buffer with buffer_id grammar to load data into a buffer, and then using the import ... data from load_buffer with buffer_id grammar to explicitly commit the buffer contents to the database. For more information on aggregate storage data load buffers, see the Oracle Essbase Database Administrator's Guide.

In MaxL, use the import database MaxL statement with the using multiple rules_file grammar. See the Oracle Essbase Technical Reference.

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

import database AsoSamp.Sample 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";

In specifying the list of rules files, use a comma-separated string of rules file names (excluding the .rul extension). The file name for rules files must not exceed eight bytes and the rules files must reside on Essbase Server.

In initializing a data load buffer for each rules file, Essbase uses the starting data load buffer ID you specify for the first rules file in the list (for example, ID 100 for rule1) and increments the ID number by one for each subsequent data load buffer (for example, ID 101 for rule2).

By default, SQL Interface disables parallel connections for the DataDirect ODBC drivers that are provided with Essbase. This feature requires parallel SQL connections; therefore, you must create a configuration file (ARBORPATH/bin/esssql.cfg) to change the default settings for the ODBC driver you are using. The following example of an esssql.cfg file for the SQL Server Wire Protocol driver provided with Essbase enables parallel SQL connections:

[
Description "SQL Server Wire Protocol"
DriverName ARMSSS
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]

You must restart Essbase Server for the change to take affect.