Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Direct MDX Requests – Multiple Measure Dimensions, Varying Attributes & Format Strings

Venkatakrishnan J -

In the last blog entry, i had shown a very simple work around to use direct database requests in BI EE. This capability has quite a few advantages and actually provides a means wherein one can actually create reports using this, wherever BI Server directly cannot(due to inherent limitations in the connectivity). There are quite a few use cases wherein we can put this to good use. Also one point to note is that, unlike Direct Database requests in relational databases (which has huge potential risks of opening up the database), the MDX direct database requests can accept only MDX queries. So, there is less chance of someone breaking/hacking into your Essbase applications. I will cover some of the use-cases here.

Filters on Evaluate Columns:

This is something that can quite be a deal breaker/showstopper while doing an BI EE implementation on Essbase. Even though one can function ship MDX functions using EVALUATE in the repository, the major drawback is one cannot apply any filtering on these columns even using Presentation Variables. The only way one can apply any sort of filtering on such columns is to use Go URL, Session Variables and updating the Session Variables through a presentation variable. But the complexity of such reports can quite get too difficult to manage. Direct Database requests can quite easily negate this. For example, lets consider a report shown below

Its a very simple report containing all the Scenario members against all Product members for the COGS, Sales and Profit measures. The MDX for the above report is given below

With
  set [Scenario2]  as '{[Scenario].members}'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
{[Product].dimension.members} on axis(1),
  NON EMPTY {{[Scenario2]}} on axis(0)
 from [Sample.Basic]

Now, if you look at either the Product  or the Scenario members, we are bringing in the entire Dimension set instead of members from a specific Generation. So, if we had designed this in the repository, we would not have been in a position to filter either on the Scenario nor on the Product All member EVALUATE column. So, to enable that lets re-write the MDX as shown below to reference 2 presentation variables. And paste this MDX in the Direct Database requests page of BI EE.

With
  set [Scenario2]  as 'Intersect({[Scenario].members},{[@{ScenVar}{Actual}]})'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
Intersect({[Product].dimension.members},{[@{ProdVar}{100-10}]}) on axis(1),
  NON EMPTY {{[Scenario2]}} on axis(0)
 from [Sample.Basic]

So basically we are referencing 2 presentation variables inside the MDX query. If you create a prompt containing both the presentation variables (and a custom SQL to display all members in both the dimensions), the filters can be applied on to the report.

Switching Measure Dimensions:

The above use case was pretty straight forward at least from a report creation standpoint. But what is good about this is the fact that the end user has complete control on Measure dimension. One can have 2 reports side by side wherein the Measure dimensions are completely swapped as shown below

All we need to do in the new report is to swap the AXIS numbers and we will get a new measure dimension. The MDX for this is given below as well.

With
  set [Scenario2]  as 'Intersect({[Scenario].members},{[@{ScenVar}{Actual}]})'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
Intersect({[Product].dimension.members},{[@{ProdVar}{Cola}]}) on axis(0),
  NON EMPTY {{[Scenario2]}} on axis(1)
 from [Sample.Basic]

This is exactly what we expect out of a multi-dimensional reporting tool. Ability to change the Slice axis at will is such a feature of Essbase that end users will surely want them even in BI EE. If this is a necessity (which i believe will be in many cases), Direct Database requests will be the way to go atleast for now. 11g hopefully will provide more flexibility in this regard.

Varying Attributes:

This is another nice feature that was introduced in the EPM 11 release. If you are not sure what Varying attributes in Essbase are, i have covered it before in my blog entry here. This probably is very unique to a multi-dimensional tool. This is similar to Slowly Changing Dimensions in a relational data warehouse implementation. But the difference is, in Essbase the attributes can vary based on multiple dimensions(one being time). This offers multiple perspective view of the data. Unfortunately, BI EE currently does not have native support for Varying Attributes. The only way to create a multiple perspective view of the data is to use Direct Database requests. Lets take a couple of examples for illustration. I will be using the same example used in my Varying Attributes blog entry i.e Product dimension has an attribute dimension called Caffeinated (True or False) which in turn varies over time and Market. Time is the continuous varying dimension for the Caffienated attribute dimension. Any end user who generally uses Varying attributes would like to look at the data in 2 different perspectives.

  1. Reality Perspective – Current Attribute values of a product driving the output of a query. For example, a report containing all the products & its sales, that are currently sold in US which are not Caffeinated

  2. Historical Perspective – Historical Attribute values of a product driving the output a query. For example, a report containing all the products & its sales, that were sold in the US market as not caffeinated in June of this year(history).

The MDX queries to get both the perspectives vary in 2 ways. The WITH clause of MDX requires a perspective specification. And also the MDX attribute function varies. Since i have covered the details of these 2 already, i will just list the MDX queries below.

WITH PERSPECTIVE (@{Month}{Jul}) for Caffeinated
SELECT
 { Qtr1, Qtr2, Qtr3, Qtr4}
ON COLUMNS,
 {WithattrEx(Caffeinated, "=", "True", ANY,
 ([New York], Jan), ([New York], Dec))}
ON ROWS
 FROM Sample.Basic
WITH PERSPECTIVE REALITY for Caffeinated
SELECT
 { Qtr1, Qtr2, Qtr3, Qtr4}
ON COLUMNS,
 {AttributeEx(Caffeinated, ANY, ([New York], @{Month}{Jul}), ([New York], Dec))}
ON ROWS
 FROM Sample.Basic

Format Strings:

This is another feature like Varying Attributes that was introduced in the EPM 11 release of Essbase. Though the title of the blog entry would have given you a feeling that Format Strings work with BI EE using Direct Database requests, unfortunately that is not the case. Format Strings do not work with BI EE. For more details on Format Strings, refer my blog entry here. BI EE does some post processing on the MDX data obtained and hence does not display the custom MDX formatted measures. The 2 screenshots below show the MDX output in EAS and the BI EE output using the same MDX

Direct Database requests, due to the current nature of BI EE – Essbase integration, can form quite an integral part of the reports that you create using this integration. If some reports are not performing well then take the MDX query of those reports and run them through a couple of checks

  1. Check whether the MDX query is an efficient query hitting only the correct & necessary Essbase cell intersections

  2. Take the tuned MDX query and then fire the MDX from the Direct Database Requests page. If this is fast enough and generates the right data, then there is scope for further tuning in your BI EE repository. If not, then the issue is probably you are hitting an Essbase bug or an MDX bug or there is further scope for MDX query tuning(first 2 are remote and in most cases it will be the query tuning part).

One other thing i had noticed recently is the fact that though MDX as a reporting language in Essbase is comprehensive, it is not quite there yet when you compare it with the features offered by Microsoft SSAS. In SSAS, sub-queries in MDX are supported. Also, there are some more comprehensive Formatting, intrinsic member property support etc(probably because Microsoft developed this). Essbase outline itself supports around 25 odd intrinsic member properties which we can find in the JAPI member properties of Essbase. Unfortunately, those have not been exposed yet to MDX. Having said that, to an extent, BI EE and Essbase compliment each other pretty well and it will be interesting to see how BI EE 11g handles Essbase when it comes out sometime next year.