Why you can't use SQL to query Essbase

Over the years, I have seen many people ask why you can't use SQL to query Essbase and I have given it a bunch of thought. I saw a similar post today on OTN and decided to finally put it into writing.

Keep in mind that, despite the fact that I have been doing Essbase full time for the last 12 years or so, I did a bunch of SQL stuff before that. In fact, I spent a bunch of time back when I was an 'accountant' working as a technical lead on an Oracle Financials implementation. So here goes with the explanation.

Essbase is not accessible via SQL as the structure of a cube is quite different than that of a table.

If you think about what SQL does, it retrieves a dynamically determined set of rows each of which are specified by a defined set of columns. Conversely, when retrieving data from a cube (in the simplest case), you are retrieving a dynamically determined set of rows and a dynamically determined set of columns filtered by a 'slice' which are determined by "page fields" or headers (which is very comparable, conceptually to a SQL where clause). In the more complex case, you are retrieving sets of sets of dynamically determined rows defined by sets of sets of dynamically determined columns.

Another way of visualizing it would be to think about how you would structure SQL to select a range of *cells* in an series of spreadsheets, say range "C64:M400". That isn't too hard when you think about a single worksheet, but how about when there are 78 worksheets in your workbook that you need the same cells from? What if there were separate subdirectories on your server with the same workbook for all 563 sales centers in your company and you needed the same cells from all of those? Well, at this point we are essentially talking about querying 4 dimensions.. What if your company tracked 12 dimension? What would the SQL look like to get those results?

As you can see, the structure of multidimensional data doesn't lend itself very well to SQL. That being said, there is a 'SQL-like' language that was built to do multidimensional queries, "MDX". Here is a sample MDX query from the 9.3.1 APS java sample code:

SELECT
Union(CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
Union(CrossJoin([Total Expenses].Children, {[Actual]}),
{([Opening Inventory], [Variance]), ([Additions], [Variance %])}))
ON COLUMNS,
CrossJoin([200].Children, {[East], [West]} ) DIMENSION PROPERTIES [Product].Ounces
ON ROWS
FROM Sample.Basic
WHERE {[Jan]}";

MDX query functionality is exposed in the Hyperion/Oracle products primarily via the C and Java APIs and via web services using XML for Analysis ("XMLA"). I did not find any MDX related functionality in the VB API declarations file; that is probably due to the large number of structures and API calls that would have to be defined/supported combined with the fact that VB has a sunset that is fast approaching.

New Hyperion Blog Link

I have just put a new Hyperion blog link in my 'Cool Links'. It is the 'Look Smarter' blog written by my friend Edward Roske at interRel Consulting in Dallas. Edward has been working with Essbase since 'the world was flat', so to speak, and is also the author of the only book that has ever been published about Essbase. He also has the coolest office I have ever been in at 'The Rangers Ballpark in Arlington'; his office overlooks the outfield and, on the day I was there, the Rangers were playing the Detroit Tigers.

Edward's blog has some great technical information, particularly on the future of Hyperion products, and is entertaining as well. The blog is published at http://looksmarter.blogspot.com/

Advanced Security Manager

Oracle recently took down the HDN website and not all of the content, including third party downloads, is currently available on the OTN site. Of course, someone posted on OTN their need to get ahold of the OlapUnderground Advanced Security Manager. In response, I got in contact with the OlapUnderground team and they have sent me the Advanced Security Manager (along with the source code).. We are considering taking over development and management on the ASM but, in the meantime, I have made the Advanced Security Manager files available on our website:

http://www.appliedolap.com/olapunderground/downloads/temp/secmon1.zip

I will leave this download, as received from the OlapUnderground team, on our website until it is determined if it will be available on OTN or until another home is found for it. If we decide to take on management of the ASM development, we will create a separate page for it on our website.

I guess the question is this. How many people actually still use the Advanced Security Manager? Should we spend any cycles working it at all? Note: I have promised not to release the source code unless it is cleaned up a bit. Thoughts anyone?