Business Intelligence – Oracle

Archive for June 8th, 2008

Oracle BI EE – MDX and Essbase – Direct Database Requests

Posted by Venkatakrishnan J on June 8, 2008

As i have mentioned before BI EE uses the C-API of Essbase to fire queries against Essbase. The queries fired are standard MDX queries. Today, lets look at the MDX queries fired by BI EE and see what these queries actually return in Essbase Administration Services. Also, lets see what kind of MDX queries does BI EE accept. In order to do that, we would have to first enable the logging level of the user to 2 or 3.


Now, lets create a simple report out of Sample->Basic cube.


Now, lets check the MDX query which is being fired back by BI EE to Essbase.

  set [Market3]  as '[Market].Generations(3).members'
  set [Population2]  as '[Population].Generations(2).members'
  set [Q] as 'crossjoin ({[Market3]},{[Population2]})'
  {} on columns,
  {[Q]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sample.Basic]

Lets copy this query and fire this in Essbase Administration Services


Now, lets enable Direct Database Request and lets fire the above query from there.


So far so good. It looks like the query is correct and it gets the columns out properly. But lets see what happens when we view the results.


Ouch. Looks like direct database requests do not work with Essbase. Strange. Since the reports work i was under the impression this would also work. I am pretty sure that i am doing something wrong here. One other strange thing is that this is not a BI EE specific error but rather a C-API specific error. I am not sure what to conclude here since i cannot continue my testing here untill i get this to work. My idea was to test all the possible MDX queries that BI EE would accept. I cannot do that since it looks like direct database requests do not work on Essbase.

Posted in All Posts | 1 Comment »