Business Intelligence – Oracle

BI EE 10.1.3.4 – BI Publisher and MDX – Essbase Connectivity

Posted by Venkatakrishnan J on August 7, 2008

One of the new features of the BI EE 10.1.3.4 release is the new connectivity to Essbase from BI Publisher. Lets see how this works today. As a first step, lets set up the OLAP Connection to Essbase from within BI Publisher.

The connection is pretty straight forward. All one would have to do is to enter the Essbase server name. Now lets, go into BI Publisher and create a simple report.

    

As you see, there is no level or generation selection that is possible in a wizard interface. One would have to manually enter the MDX Query. This is a bit of a downer since i was atleast expecting a wizard interface to choose the levels (if not members). But anyway lets try entering a simple MDX query as shown below

SELECT
{[100-10], [100-20]}
ON COLUMNS,
{[Qtr1], [Qtr2], [Qtr3], [Qtr4]}
ON ROWS
FROM Sample.Basic

Lets save the above report. Now, lets click on data and see what happens. As you see below, the data is properly retrieved which is very good.

    

Now, lets try a complex MDX query as shown below

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.BasicWHERE {[Jan]}

    

    

This is something that BI Server lacks currently as one cannot enter MDX queries directly in the “Direct Database Requests”. If you are not sure what i mean, lets enter the same query above in the direct database requests page in BI Answers.

    

As you see, the above errors out as BI Server expects only columnar data through the MDX. The support for MDX is very limited from a BI EE standpoint. Now with the support of BI Publisher, the MDX support is more enhanced. But the drawback still is the lack of a wizard driven interface and also lack of true multi dimensional aware reporting.

About these ads

4 Responses to “BI EE 10.1.3.4 – BI Publisher and MDX – Essbase Connectivity”

  1. Tim said

    Hey Venkat
    An MDX query builder is coming, it just missed the 10.1.3.4 oat. With an end user and developer mode. Point it at your cubes and start building queries if you are a developer. If you’re an end user, point it at a cube and start slicing and dicing with tabular and graphical visualizations!
    Tim

  2. […] SQL Server 2000 and 2005 Analysis ServiceSAP Business Information Warehouse (SAP BW) as Venkatakrishnan already described in his blog, do not expect more than the possibility to enter an MDX […]

  3. nice…

  4. Roman K. said

    Hi Ventak!
    I’m worked with Bug 6869282: DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE.
    If you want to direct database request you must enter a space before the from clause.
    I talked about this issue at the presentation of BI Beer 2009 in Moscow. You can see the problem in the presentation (it is in Russian, but it is clear from the pictures) http://www.oraclebi.ru/files/bibeer09/BI_beer_RK.rar.

    Thanks,
    Roman

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: