Business Intelligence – Oracle

Archive for December 2nd, 2008

Oracle BI EE & Essbase Connectivity – Understanding MDX Queries and Reports Tuning – Introduction

Posted by Venkatakrishnan J on December 2, 2008

Its been sometime now (more than a month) since i last blogged about anything. I was attending to quite a few customer engagements and the schedule was pretty hectic. Now that i am wrapping up one of the projects, i had some time to blog about the intricacies of BI EE and the Essbase connectivity which was introduced in version of BI EE. As you might have seen on some of my previous blog entries on this subject before, i would have mentioned that BI EE is not yet multi-dimensional aware. That is still true but if one understands MDX, Essbase and BI EE together one would still be able to produce some complex reports. Of course, not all kinds of complex financial reports are possible yet, but we still can achieve some complex reports. But in order to create such reports, we need to be aware of how BI EE treats Essbase. Also, we need to understand how the MDX queries are created. We shall go through that step by step in this and the next couple of blog entries. First lets start with Importing the Demo (Application) and Sample (Database) into BI EE.

As you see, BI EE basically imports the entire outline into the physical layer. But the major difference is that we have the entire Accounts dimension imported as a flat list i.e members like Sales, COGS etc have lost their hierarchy during the import. One of the major reasons for that is that BI EE expects a measure for consistency check i.e a measure is mandatory. By default, BI EE assumes the Accounts dimension to be a Measure dimension. But currently we have the capability in BI EE to make any dimension as a measure dimension. So, lets take a look at the hierarchies that have been imported.



As you see, we have actually not lost the Accounts dimension hierarchy. We shall see the advantage of changing the hierarchy types later. But for now lets take a look at the measures. By default, each measure would have a AGGR_EXTERNAL as aggregation. That means the aggregation would be pushed to the server(again this does not necessarily point to Essbase aggregation. Rather it just means that MDX would be generated irrespective of whether all the dimensions exist or not). Also, what this means is that one does not need all the dimensions to get the data out. We shall see the difference in switching this aggregation in a later article. So as a thumb rule try to follow the guidelines listed below while reporting out of a Essbase Cube (Some of these could vary but majority of them would hold good).

1. Try designing a report by comparing the MDX queries – This is absolutely critical to achieve certain reports.
2. Try to push everything down to the Essbase Server – This is not possible in all the cases currently. But wherever possible use MDX functions using EVALUATE
3. Try to minimize the use of BI Server (in memory) specific calculations like String manipulations etc – This is critical to achieve good performance. Wherever possible use MDX with EVALUATE
4. Fix the measure dimension after considering the layout of all the reports that you are designing.
5. Wherever applicable try to create as many necessary multidimensional calculated members within Essbase itself. Do not create them in BI Server (though it is possible)
6. Explicit conditional GO URLs through a html url is not possible out of the box. Wherever possible try to use Navigate.
7. If you only have BI EE in your organization, try to leverage the capabilities of Hyperion Financial Reporting. If you do not have the expertise in Hyperion Financial Reporting and if you feel that some reports are not possible in BI EE, use BI Publisher instead. BI Publisher can achieve a lot of complex reports though one would have to fire the MDX queries directly as of now.

Now lets go to the physical layer and switch the measure hierarchy to the Scenario dimension. When we make this switch, we need to add the measures manually i.e members of the Scenario dimension would have to be added manually. Set the aggregation of these members as AGGR_EXTERNAL


If you see in the above screenshot, there are 2 options. Check both the options. The first option (Default Member ALL) means that if a dimension is missing in our report, a default value for that dimension would be used. To explain further, when you go to Essbase Excel Add-in to get data we need to provide atleast one member from each dimension. But in MDX there is a way to get to the data without specifying a member from all the dimensions. Wherever the dimension is not specified an implicit default member (the dimension top member itself) would be assumed. We can go into this in detail later. And the second option is to be checked in all the cases. This would be used whenever there are no duplicate members in the outline (which would be in most of the cases). Now lets try creating a simple report out of this Cube from Answers. Lets pull in Gen2, Year, Gen2, Products as dimensions and pull in Actual and Budget as measures.


Now lets take a detailed look at the MDX query fired by the above report.

set [Product2] as '[Product].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
{ [Scenario].[Budget], [Scenario].[Actual] }
on columns,
NON EMPTY {crossjoin ({[Product2]},{[Year2]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows

Currently BI EE fires very basic MDX queries. The structure of the MDX query would be like this. All measures would be going on the columns and all dimensions would be coming in rows. So, as we add more and more dimensions to the report we would have more dimension members coming in the rows clause. The screenshot below would give you more details.


As you see, the MDX can be easily understood for this simple report. Basically it tries to bring in all non zero values for any combination of Year and Product dimension. On that resultset only the valid Generation 2 member combination of Product and Year are produced. Now lets fire this query in Essbase to see the actual resultset.


Any member or a tupule or a set included in the ROWS clause would come in as an Axis. There is also an axis specification which we shall look later. Then the properties ANCESTOR_NAMES and GEN_NUMBER basically produce the properties necessary for BI Server repository validation. This resultset is then internally reformatter by BI EE to produce the resultant output. What we have seen so far is a very simple report. In the next blog entry we shall see how understanding MDX would help us to produce more complex reports.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 4 Comments »