Oracle BI EE 10.1.3.4.1 – Direct Database Requests & Essbase - Workaround

I got an email (almost 2 weeks back) from one of the BI EE engineers who works on the BI EE – Essbase connectivity. Looks like one of the bugs that i had pointed out in a very old blog entry here has been fixed (not exactly fixed but a workaround has been found). Also thanks to Roman K from Russia for reminding me about this yesterday. One of the major issues in the current BI EE – Essbase connectivity was the fact that Direct Database requests on Essbase did not work properly. For example, consider the below MDX.

With
  set [Accounts4]  as '[Accounts].Generations(4).members'
  set [Market3]  as '[Market].Generations(3).members'
  set [Product3]  as '[Product].Generations(3).members'
  set [Year3]  as '[Year].Generations(3).members'
  set [Axis1Set] as 'crossjoin ({[Accounts4]},crossjoin ({[Market3]},crossjoin ({[Product3]},{[Year3]})))'
select
  {[Scenario].[Actual]} on columns,
  NON EMPTY  {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

This is an MDX query generated by a very simple report given below.

If you try to fire the same query from the direct database requests page of BI EE, you will get an error as shown below

The fix is to just add one space to the From clause of the MDX. Who would have ever thought this?

Looks like the BI EE MDX parser does not understand the new line (before the from clause alone). Hence one would have to explicitly add a space to the from clause. Only if Oracle had exposed the source of the BI Server Essbase DLL’s, it would have been a lot easier to fix/identify these issues. But again this fix is very good especially if you want to test MDX without going to Essbase Admin Console or Smart-view.