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.