Parallel SQL Server Data Loads with ASO Essbase

Brian Marshall

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:

Parallel01

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.

Parallel02

Click on the System DSN tab:

Parallel03

Now add a new connection:

Parallel04

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.Parallel05We 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).

Parallel06

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.

Parallel07

Enter your credentials.

Parallel08

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

Parallel09

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:

Parallel10This 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.

Parallel11

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.

Author:

5 Comments

  1. April 9, 2016 at 6:43 am

    […] This week I blogged about loading SQL data into ASO in parallel. […]

  2. SamT
    SamTReply
    May 24, 2016 at 10:30 am

    Hi,

    Great Blogs. Just discovered this site. I must say one of the cleanest easy to follow!!

    We are just about ready to try this. We are on 11.1.2.3.508

    1 – We are on Windows Server 2008 R2 and when we go to Administrative Tools we only see ‘Data Sources (ODBC). Nothing to differentiate between 32bit and 64bit. Is that an issue?

    2 – We use Oracle DB not SQL Server. Our current ODBC Driver is “DataDirect 7.0 Oracle Wire Protocol”. Will that work for Oracle or do we have to choose a different one?

    3 – What would our esssql.cfg file look like? I think:

    [
    Description “DataDirect 7.0 Oracle Wire Protocol”
    DriverName ARORA26
    UpperCaseConnection 0
    UserId 1
    Password 1
    Database 1
    SingleConnection 0
    IsQEDriver 0
    ]

    • Brian Marshall
      Brian MarshallReply
      May 28, 2016 at 8:22 am

      That is a very detailed comment Sam! I will have to try it out on the Oracle side. I’ve only attempted this on SQL Server. I tried to get to this last week, but I was having trouble replicating my SQL Server test database to Oracle! I’ll let you know what I find out.

  3. SamT
    SamTReply
    May 28, 2016 at 8:31 am

    I was playing around with it last week. I do have log files to post. I think it’s successful but not sure how to tell. A co-worker of mine said he thinks the esssql.cfg file only applies to SQL Server. I tried researching and cannot find any documentation on it.

    Also wondering if we need any settings in our Essbase.cfg file as what I ran seemed to be slower than just running two loads one after the other. So I think I got something wrong, but hard to tell to be honest.

    I will post log files on Monday when back in office. Don’t want to waste your time re-creating something. If we determine it’s successful on Oracle the way I have it, then maybe you can update the blog based on our findings?

    • Brian Marshall
      Brian MarshallReply
      May 31, 2016 at 4:42 pm

      I did get a chance to give it a try. I executed two parallel loads from a 12c database into an ASO model and it seems to load without modification. I can’t speak to the esssql.cfg only applying to SQL Server, as I don’t see anything in the docs about that, but it doesn’t appear to need a change to get it working anyway. I used the 7.1 version of the same driver you mentioned. I’ll have to do some more testing on performance to see how that is. I ran the same query twice to two different intersections. It took about the same amount of time to load both in parallel as it did to run each one at a time. So it looks like I got a pretty linear increase in performance.

Leave a Reply

Name*
Email*
Url
Your message*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>