Parallel SQL Server Data Loads with ASO Essbase

If you have a ASO cube where timing is everything or a cube with multiple large data sets, waiting for data loads to process in series can waste valuable time. Luckily, Essbase supports parallel data loads! Sadly…while the documentation for this isn’t bad, it is inaccurate.  I’m sure it was accurate a few (or many) versions ago, but the drive names have changed as have the DLL’s.  So first, let’s set up our MaxL rule to execute multiple parallel loads from a single SQL data source.  Here’s the code:

import database RetASO.RetASO data 
   connect as hypservice identified by 'Hyp3r10n!' 
   using multiple rules_file 'dRev', 'dExp' 
   to load_buffer_block starting with buffer_id 100 
   on error write to "e:\\data\\RetASO\\dRev.txt";

So this MaxL code assumes that you have at least two load rule developed.  I’ve built a load rule for revenue and a load rule for expense.  Each of these rules load roughly 2 million rows of data into my ASO cube.  So let’s execute this and see what happens:

If we read the error, it tells us exactly what the problem is.  We need a data source that supports multiple connections.  For this we can turn to the included Data Direct drivers. Let’s go ahead and create a new data source to use for Essbase that will support multiple connections.  We start by firing up our ODBC Data Sources (64-bit) in under Administrative Tools on our Essbase Server.

Click on the System DSN tab:

Now add a new connection:

Scroll down and select the DataDirect 7.1 SQL Server Wire Protocol (on 11.1.2.4) or the DataDirect 7.0 SQL Server Native Wire Protocol (on 11.1.2.3) driver from the list.We also need to know the driver name, so scroll over to the right and make note of the name of the DDL file.  It should be ARSQLS27.DLL (on 11.1.2.4) or ARSQLS26.DLL (on 11.1.2.3).

Once you click Finish you should see a new window that will let you fill in your server and database information.  Enter your information and click Test Connect.

Enter your credentials.

Assuming we’ve done everything correctly, it should establish a connection.

Now we have a connection, but it does not support multiple connections by default.  This means we need to tell it to allow multiple connections.  We follow the docs and we create an esssql.cfg file in the bin directory of our Essbase server:

This is where we have to change it up a little.  The docs haven’t changed in a while so they reference a very old version of the driver.  Try this out instead:

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

Or for 11.1.2.3:

[
Description "DataDirect 7.0 SQL Server Native Wire Protocol"
DriverName ARSQLS26
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]

Once we save our file, we are ready to restart Essbase and give our data load another try.

Success!  Parallel data loads are one of the many ways that we can improve our ASO process performance.  In the future we’ll cover other ways to address ASO load performance.  Hopefully this will help those of you that tried using the docs and did not find success.