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 or the DataDirect 7.0 SQL Server Native Wire Protocol (on 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 or ARSQLS26.DLL (on


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:

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

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.

Drillbridge: Enabling Upper Level Drill-through

In our last post about Drillbridge, we did the basic introduction and then ran through the steps to get it up and running. But, in the interest of simplicity, we kept it simple. Now we are ready to get a little more complex.

For today, a little more complex will be upper level drill-through.  This is one of those things that can make Essbase admin’s and DBA’s run from a building like it’s on fire.  Why?  Because upper level drill-through relies upon the end-users ability to not do something stupid.  As we all know…it’s a virtual certainty that they will.  This is where Drillbridge really shines.  We have several mechanisms to control the level of stupidity that we allow our users to engage in before we shut them down.

So let’s get started.  Log into your Drillbridge server and start by changing up the Deployment Spec.  If you remember from our first post, we started with this:

@RELATIVE("Year", 0),@RELATIVE("Measures", 0),@RELATIVE("Product", 0),@RELATIVE("Market", 0),"Actual","Budget"

We also checked the box indicating that this was Level-0 only.  Now let’s change that specification to this:


We have switched our Year dimension to use the IDESC function.  We have left the Measures dimension at level-0, since many times we can’t tie numbers out at upper level due to upper-level aggregation operators.  We have also switched Product and Market to IDESC as well.  We’ve left Actual and Budget alone since there is no upper level in the Scenario dimension.  We’ll also un-check the Level-0 members only option.  At the end, it should look something like this:

DrillbridgeUpper01This is our first method of preventing stupid user tricks.  We only allow drill-through that makes sense.  Now that we have our deployment spec configured, we can move on to the report.

Before we get too deep into the report, let’s first talk about how upper-level drill-through really works.  If a user chooses to drill-through on Qtr1 for instance, what does that mean?  Essbase is simply going to pass that value through, Qtr1.  But Drillbridge takes that member and uses the Essbase JAPI to determine the level-0 descendants of the member.  In our simple case, this is just Jan, Feb, and Mar.  But wait, we can’t use an equals sign in our SQL statement and evaluate multiple values, can we?  No, no we can’t.  So let’s look at a sample SQL statement that will enable our upper-level drill-through:

	,CONVERT(VARCHAR(10),CAST([DimYear] AS DATETIME),101) as [Date]
    DimMarket  IN {{ "name" : "Market",    "expression": "#Market", "drillToBottom" : "true" }} AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct IN {{ "name" : "Product", "expression": "#Product", "drillToBottom" : "true" }} AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  IN {{ "name" : "Year", "expression": "#Year", "drillToBottom" : "true" }}

In our last post, we didn’t really talk about the query, we just used the simple sample.  Let’s take some time now to actually understand how the query engine in Drillbridge works.  First, you’ll notice double curly braces in our where clause.  This is what tells Drillbridge to evaluate an expression.  Inside of our curly braces, we have three settings that we need to define to get back upper-level members.

First we specify the dimension that we want to pull back from Essbase.  This is the Name property.  Next we have the actual value and how we want to format it.  This is the Expression property.  If you take a look at the PDF that included with Drillbridge, you can get an idea of all of the possible expressions, but for today we’ll keep this part simple and just use #DimensionName.  Our last property is optional if we don’t care about upper-level data.  But, because we care, we must set the drillToBottom property to true.

This is the setting that tells Drillbridge to take Qtr1 and find all of the level-0 descendants.  Now all we need to do is modify our SQL statement to use an IN instead of an =.  And with that we should be ready to deploy our new report.  So let’s try it out.  Let’s start by drilling into level-0 for everything but our Year dimension.  For the Year dimension we’ll try an upper-level query:


Once we drill-through, we see that we have Qtr1 in our POV and multiple months of data:


Excellent!  We have upper-level drill-through.  But wait, what about stupid user tricks?  Let’s take a look back at our report:


Here we have two ways to prevent long-running queries executed by users.  First, we can set a row limit.  This is generally enough, but sometimes, a user can find a way to execute a query that returns a low record count, but it still takes forever.  So as a safe-guard, we can also add a query timeout.  This way we are protected on both fronts.  So let’s start with the row limit.  If we enter a row limit of 10 and try out the drill-through again, we get this:


Great!  Only 10 rows!  But wait…what about the other rows?  The downside to the Community edition is that it does not offer a message to users to let them know that additional rows were suppressed.  Bummer.  But don’t worry too much, as we have a way around this that I’ll cover in my next Drillbridge post.  We’ll also cover query time limits in a later post as I still have to resolve the issue I’m having.

And with that, you should now have working upper level drill-through with at least a row limiter.  Our next Drillbridge post will cover some of the advanced features.

Drillbridge Aquired by Applied OLAP and Community Edition 1.5.5 Released

Applied OLAP has officially completed the acquisition of Jason Jone’s Drillbridge product.  This is great news for those of us that are fans of the product as it gives Drillbridge the backing of a company with a long-standing record of great support and other great products.  Jason will now be working directly for Applied OLAP.  Congrats Jason!  We’re all excited to see what comes next for Drillbridge.  His official update.

Speaking of what comes next, there has also been a new release of the Community Edition.  Version 1.5.5 is available here once you register.  Stay tuned for another post on upper level drill-through with Drillbridge!

Drillbridge: Introduction

***Updated 2016/03/10 for Firefox Issue Fix***

So you have an existing Essbase model and you are finally ready to provide your end-users with drill-through.  But wait…EIS is dead and buried and while Essbase Studio can be made to function with an existing cube, it is somewhat painful to actually make it work properly.  On top of that, it just isn’t very flexible in the way that it provides drill-through.  So what do we do?

I guess we could technically go build our own custom drill-through definitions and figure out a way to make it work.  There’s always FDMEE, but that’s not cheap and doesn’t offer upper-level drill-through.  Enter Jason Jones (a fellow Oracle ACE) and his amazing tool just for drill-through.  Drillbridge is one of the coolest third-party tools I’ve used in the EPM space.  So how do we get started?

First, let’s head over to the Drillbridge website and download the Community Edition (1.5.4).  This is the free version and a great place to start.  You can download 1.5.4 here.  You will need to register and login to the site to actually see the download link.  Once we have the ZIP file downloaded, we can move on to installation.

Before you install Drillbridge, make sure that you have Java installed and in the PATH.  You can verify this by opening up a command prompt and typing java -version.  Once we verify that Java is working properly, the installation is pretty straight forward.  We just unzip the file that we downloaded into a directory on our server.  I chose E:\Drillbridge.  You should end up with something looking like this:

Drillbridge01Open up a command prompt change directories to the bin folder and test Drillbridge by typing Drillbridge.bat console.  Assuming everything work, you should see something like this:


You can go ahead and stop the service and now we are ready to install the service by typing in Drillbridge.bat install.


This should create a new service.  We can run Services.msc to make a quick change to the service.  I like all of my services using my Hyperion Service account, so I right-click on the new service and click Properties.  Once on the properties screen, click on the Log On tab and enter your credentials.


Then click OK and you should be ready to start up your service:


Now we can log into the system and start our configuration!  Let’s fire up the website and take a look.  You should be able to navigate to [Servername]:9220 which should provide you with the splash screen:


We’ll start by creating a connection.  Click on the Connections button at the top of the screen and you should be prompted for a username and password.  The default username is admin and the default password is drillbridge.


Once you are logged in, you are ready to click the New Connection button:


In case you want to test this with everyone’s favorite Essbase application, I’ve created a SampleBasic SQL Server database for you to play with.  I’ve also included a SQL Essbase Load Rule to load data our Sample.Basic Essbase database.  The database includes a Fact table with 1.25 million records of data to load along with level 0 meta-data tables and a full extract of the meta-data from Sample.Basic courtesy of the Advanced Outline Extractor from AppliedOLAP.  You can get both here.  If you end up using my sample, your connection should look something like this:


Now that we have a SQL data destination for our drill-through, we are ready to configure the Essbase side of things.  First, we’ll add an Essbase server.  Click on the Servers button at the top of the screen and then click on New Server button.


Enter your Essbase details and click Save.


Now that we have our Essbase server configured, we can add our actual Essbase database.  Click on the newly created server:


Once you click on the server, you should see a new tab.  Click on the Cube Mappings tab:


Click on the New Cube Mapping button:


Enter the details for your Essbase application and click save.  I’ll be using Sample.Basic to go with my sample data:


Now that we have our database configured, we need to configure the deployment specs.  What’s a deployment spec?  Basically we need to specify the intersections in our Essbase database that will be enabled for drill-through.  We specify the deployment spec once because we may have multiple drill-through options for each of our intersections.  To add our deployment spec, click on Admin in the top right corner and then click on Deployment Specs.


Click on the New Deployment Spec button:


Enter a name and a set of intersections in the specification.  Also, check the box indicating Level-0 only.  We’ll start simple in this blog post and in our next post we’ll head to the upper levels where the fun stuff happens.


The code that I used basically gives us level 0 for all of our dimensions except the Scenario dimension.  I’ve just specified Actual and Budget as the Variance members are calculated anyway.

@RELATIVE("Year", 0),@RELATIVE("Measures", 0),@RELATIVE("Product", 0),@RELATIVE("Market", 0),"Actual","Budget"

We have all of the foundation ready to go, now all we need is a drill-through report.  Click on Reports and then click on New Report.


We’ll give it the report a name, choose a connection, and provide a query.  We’ll also select an Essbase connection and choose a few options at the bottom.


And here’s the code for the query:

	,CONVERT(VARCHAR(10),CAST([DimYear] AS DATETIME),101) as [Date]
    DimMarket  = '{{ "name" : "Market",    "expression": "#Market"      }}' AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct = '{{ "name" : "Product", "expression": "#Product" }}' AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  = '{{ "name" : "Year",  "expression": "#Year"  }}'

Once we create the report, we still have a few things left to do.  Click on the report name to modify the report.


Click on the Validate tab to verify that the connections to both Essbase and our Relational database are good along with the report definition.


Click on the test tab and enter an intersection and click Build It to see a preview of the drill-through report.


And here’s the sample:


Finally, we’ll choose our Deployment Spec and  Server and click the Deploy button to finish off our first drill-through with Drillbridge.


Now we can fire up Smart View and try it out.  We’ll drill into a level-0 intersection and make note of our value ($249,683):


And here’s our nice drill-through report courtesy of Drillbridge:


Our numbers up top tie (since this is pulled from Essbase…it should).  You can click on the Excel link and tie out the numbers.

If you are using Firefox as your default browser, beginning in, it requires the Oracle Smart View for Office extension.  If you don’t have this installed, you will likely receive something like this:


That was my error until I installed this:


And that’s it for today.  This is one very long post, but there’s no great way to break it apart.  Go grab Drillbridge and give it a try.  Exciting things are coming and I can’t wait to use this product at several of my clients.  As we continue to explore this tool, we’ll move onto upper level drill-through and then move onto some of the more advanced features.

Essbase Format Strings in Smart View, The Excel Add-In, and Financial Reports

Last year I had a post about working with strings in Essbase.  To illustrate how to work with strings, I used a features called Essbase Format Strings.  This basically allows us to return a value other than that of the intersection based on MDX logic.  For instance, if a number is great than zero, return the text Positive or if it less than zero, return Negative.  This allows us to show text values inside of an Essbase retrieve.

Today we will dive a little bit deeper into how this works in each of our popular interfaces with Essbase:  Smart View, the Excel Add-In, and Financial Reports.  First we’ll look at the classic Excel Add-In.  This is pretty simple in that we really have no control over what we show.  The Excel Add-In will only show the value of the format string:EssbaseFormatStrings1

As we can see, the value of the format string is displayed.  There are no settings to turn that off in the Excel Add-In (that I can find).

Next up, we have Smart View.  Smart View is a little bit more aware of the concept of Essbase Format Strings.  It actually has a setting that we can turn on and off.  Using the default settings, we get this:


This looks nearly identical to the Excel Add-In retrieve.  Now let’s look in our Smart View options:


Here we see an option to enable and disable our format strings.  So what happens when we turn this setting off?


Now we see the 0 that we configured as the value to be returned by our calculated member, instead of the format string.  So what does this look like in Financial Reports?EssbaseFormatStrings5

We’re back to the format string showing!  And like the Excel Add-In, Financial Reports just displays the value of the format string and there’s no way to turn this off.  Hopefully this provides a little clarity as to how Format Strings work and how we can display them in our various popular interfaces.


Working with Strings in ASO

I needed a break from the Planning repository and recently had a colleague inquire about getting the name of a member as a string value in ASO.  This is a relatively simple process, but it occurred to me that when working with strings in Essbase, sometimes it helps to actually see what you are working with.  So how do we display text in Essbase based on an MDX formula?

First, we have to turn on typed measures.  In Essbase, the idea of typed measures are things like dates and text lists.  Typed measures also give us the ability to display formula-based text from Essbase in Smart View.  I specify Smart View because this functionality does not work in the Excel Add-In.  So let’s start by enabling typed measures:

  1. Make sure you really want to do this, because there is no undo.
  2. Open your outline (I’m using ASOsamp in this example) and go to the properties tab.
  3. Change Typed measures enabled to True.MemberName1
  4. Click OK when prompted.MemberName2

Now that we have typed measures enabled, we can try to display some text.  First let’s open the Measures dimension and add a member.  I chose ProductName as my member.  Once we have that member added, we can follow a few more steps and be ready for a retrieve:

  1. Open the Properties of the newly created member (in my case, ProductName).
  2. On the information tab, modify the Associate Format String to the following: MdxFormat([Products].CurrentMember.Member_Name)MemberName3
  3. Next we need to associate a formula for the member.  We do this because Essbase will not display a format string for a missing member.  I just used a literal zero to ensure we always have a value.MemberName4
  4. Save and restructure your cube.

We should be ready to see some text in Smart View:MemberName5

Now that we have some text, we can start to manipulate that text and see things.  First let’s do something simple, like get the length of our member name:

Set our format string: MdxFormat(NumToStr(Len([Products].CurrentMember.Member_Name)))

And in Smart View:MemberName6

Quick side-note…notice that we use the NumToStr function because the MdxFormat function requires a string parameter.

As one last sample, here’s how we use a text function like Left:

Set our format string:


And in Smart View:MemberName7

There we have it.  Text manipulation that you can see.  Once you get your formula just right, you can use the strings in other formulas.  You can use all of your regular functions and go nuts with strings.