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 188.8.131.52) or the DataDirect 7.0 SQL Server Native Wire Protocol (on 184.108.40.206) 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 220.127.116.11) or ARSQLS26.DLL (on 18.104.22.168).
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 22.214.171.124:
[ 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.
May 24, 2016 - 10:30 am
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 126.96.36.199.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”
May 28, 2016 - 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.
May 28, 2016 - 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?
May 31, 2016 - 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.