Essbase : Multiple SQL Data Loads in Parallel to Aggregate Storage Databases

Most of the time, loading Essbase data from SQL Server  in serial mode works just fine:

alter database EssApp.EssDb initialize load_buffer with buffer_id 1;

import database EssApp.EssDb data

connect as SQLUser identified by SQLPswd

using server rules_file   'Load.rul' to load_buffer with buffer_id 1

on error write to 'errorfile.err';

import database ASOsamp.Sample data from load_buffer with buffer_id 1;

But if you have a lot of SQL data loads that take a long time, you might run into complaints from your client to speed of the data load process.  Well, your in luck!

From the technical reference, you can perform multiple SQL data loads in parallel to aggregate storage databases:

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";

If you modify your maxl statement with the above, and run it, you will most likely get the following error message:

SQL driver [SQLSRV32.DLL] for [SQL_PROD] is in use already and does not allow multiple connections. Please try later.

You can follow the below steps to allow multiple connections.

1. Create an 'esssql.cfg' file in the same directory as your 'essbase.cfg' file
2. Add the following contents to the newly created 'esssql.cfg' file:

[

Description "Microsoft SQL Server 32-bit"

DriverName SQLSRV32

UpperCaseConnection 0

UserId 1

Password 1

Database 1

SingleConnection 0

IsQEDriver 0

]

3. Restart Essbase

Once these changes are made, you should now be able to load multiple files in Parallel from a single SQL Server ODBC connection.  Keep in mind, you can add up to 8 load rules for each import maxl statement.  If you have more, just add another maxl statement (that'll cover 16 SQL data loads)