Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 & 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 10.1.3.3.2 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.

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

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.

Advertisements

4 Responses to “Oracle BI EE 10.1.3.4 & Essbase Connectivity – Understanding MDX Queries and Reports Tuning – Introduction”

  1. Alex said

    Hi Venkat,

    I’m developing a project based on OBIEE and EssBase and struggling a lot with various technical issues, mainly in the integration between OBIEE and EssBase.
    Therefore I am very happy to see your attention to the OBIEE-EssBase connectivity.
    Thanks for your blog – I’ve got many useful ideas and tips here!

    Best Regards,
    Alex

  2. […] Administrator Passwords – Impact on BI Scheduler and BI Publisher – Understanding ImpersonationOracle BI EE 10.1.3.4 & Essbase Connectivity – Understanding MDX Queries and Reports Tuning – In…Oracle BI EE 10.1.3.3/2 – Using LDAP/OID AuthenticationYour QuestionsContact MeOracle BI EE […]

  3. jboy said

    Hi Venkat,

    I want to know if its possible to drill between multidimensional cubes. The scenario is as below It would be very helpful if you could help me with this

    World->Region->Country to work on cube 1
    and country -> City to work on cube 2

    Cube 1 and 2 are identical, Cube 1 has aggregated data at the Country Level and Cube 2 has Detail data at the City level both the cubes has have the same measures Measure1 and Measure 2.

    How is this achievable?

    Thanks and Regards,
    jboy

  4. Raj said

    Hi venkat,

    Thanks for your Blog. This is very helpful for Essbase beginners easy to understand Essbase thru your blog.

    Like Essbase topics could you please walk thru on Hyperion Financial Management topics aswell that would be greatfull.

    Thanks in Advance,
    Raj.

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

 
%d bloggers like this: