Thursday, January 17, 2008

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.

8 comments:

Jason said...

I have observed this a lot too -- when I say I work with databases, a lot of peoples' first remarks are: "Oh, like SQL and stuff?" I used to make the mistake of trying to explain that it was multi-dimensional and all that, but I gave up when I noticed that most peoples' eyes would glaze over.

Anonymous said...

"..combined with the fact that VB has a sunset that is fast approaching."

Any elaboration on this? Are they simply moving to .Net framework only? Or ditching MS all together?

Tim Tow said...

I can elaborate, or perhaps clarify, the statement. Microsoft is sunsetting VB and replacing it with .NET (including VB.NET). Oracle continues to support the Essbase VB API.

Vinny said...

Hi Tim,

Do we have any tool with which we can fire MDX queries on a cube and retrieve the data similar to the tools like toad and SQLdeveloper in case of SQL language.

Anonymous said...

Hi Tim

it's always feel si good to read your blogs and see th passion you have for essbase. Like many others, I am another essbase freak. I've done deep dive in oracle as well. I am implementing oracle and essbase combination projects in banks. From my last couple of years I've started feeling that people have started preferring oracle over essbase. Specially when it can store one number with multiple columns e.g. Sales number can be stored with 30 different parameters like RM details and customer details. It's very easy to implement such things using oracle with obiee as front end but it's difficult to implement same in essbase.

Moreover non tech customers prefer flash obiee rather than excel addin and obiee marries well with oracle as compare to essbase.

Looking at all these things I am getring bit spectical about future for essbase. As oracle database getting powerful and stronger to store big Combinations.

Hence can you please share your thoghts on future for essbase.

Thanks a million.

Tim Tow said...

Oracle database and Essbase serve two very different purposes. I recently posted a blog entry that talks about the growth of Essbase in the Oracle ecosystem: http://timtows-hyperion-blog.blogspot.com/2010/01/more-evidence-that-oracle-loves-essbase.html

Tim

Anonymous said...

how do we replace the use of the outline extractor? and is essbase studio a good replacement for it?

Tim Tow said...

The outline extractor has a specific purpose, to extract member information from an Essbase database outline. It does not use SQL. Essbase Studio is for building Essbase databases, not for extracting dimension information (although it may support the ability to reverse engineer dimensions and, if it doesn't, perhaps it should!)

Tim