Business Intelligence – Oracle

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.

One Response to “Oracle BI EE – MDX and Essbase – Direct Database Requests”

  1. Krishna Yamarthy said

    hi venkat,
    Direct database request accepts only SQL queries.
    so, maybe it is not yet engineered to validate MDX queries.
    even in the error, it shows “SQL Issued”.
    may be there should be a way of differentiating MDX from SQL.

    i don’t know if i am correct or not.

    I thank you sincerely for all your posts and the knowledge you are sharing.
    it helped me a lot.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: