OraNA :: Oracle News Aggregator

While we are in the midst of our Essbase and BI EE connectivity article series, i got an email from Christian Berg (who used to frequent the BI EE forums when i was active before) asking whether a specific report was possible using MDX without using complex and complicated UNION requests. I thought it was worth publishing here just to emphasize the point that i made in the previous 4 articles. MDX is very powerful. Let us take a look at the report. We shall start with Demo Basic outline. I would not paste the exact outline and report screenshots that he sent. Rather i would rephrase that question so that everyone can try this out on the Demo Basic cube. In this case, we shall be using Scenario as our Measure Dimension.

The report specification goes like this “A report showing value of Actual, Budget and Variance (the measure dimension members) against all the products in Gen3, Products. Actual should show the value of Sales against each product (Accounts dimension member). Budget and Variance should show the value of Sales and Marketing (2 Accounts Members).” In a nutshell the report should look like this

Without Using MDX Functions this report is not possible out of the box. The only way is to use UNION requests and even then it would not produce the data in the correct format. So, lets use MDX to generate this report. As a first step pull in the Gen3, Product in our report. Once that is done, pull in the Actuals measure. In the column formula use the below EVALUATE function.

EVALUATE('(%1.dimension.currentmember,[Actual],[Sales]).value',Product."Gen3,Product")

This will ensure that we are getting the Actual for all the products with Sales as the Account.

Now, the next step is pretty tricky. All we need is a SUM([Product].currentmember,{[Sales],[Marketing]},[Actual]). But unfortunately, MDX would not accept the currentmember function within SUM. And we need to pass a column into EVALUATE. Else it would throw an error. In order to overcome this we shall be using the below evaluate function.

EVALUATE('(%1.dimension.currentmember,[Budget],[Sales]).value - (%2.dimension.currentmember,[Budget],[Sales]).value + SUM({[Sales],[Marketing]},[Budget])',Product."Gen3,Product",Product."Gen3,Product")

So, basically what this does is, we are passing Gen3, Product just to make the EVALUATE function work. The first minus will always result in a zero and that is our intention. The actual function should have the been the SUM part along. Similarly, use the below function for Variance

EVALUATE('(%1.dimension.currentmember,[Variance],[Sales]).value - (%2.dimension.currentmember,[Variance],[Sales]).value + SUM({[Sales],[Marketing]},[Variance])',Product."Gen3,Product",Product."Gen3,Product")

Now, if we look at the report, we would get the desired scenario values without any complicated UNION requests. But remember to disable the drills.

Yesterday we saw the 2 types of MDX functions that are fired by BI EE via the EVALUATE function. The important point to know while using these functions is in understanding when a function is treated as a Row Level MDX function and a Column Level MDX function. In order to understand that lets start with a simple report as shown below

As a generic rule, any EVALUATE when used against the measure dimension would be treated as a ROW Level MDX Function. There are some exceptions to this rule. But for now lets not go into those exceptions. And any EVALUATE against any other dimension (non-measure dimension) would be treated as a Column Level MDX Function. In order to make a column level MDX function to be treated as a Row Level MDX Function, the dimension should also be included in the report. To understand this lets start with the above report. Now in the above report, our aim is to make the 2 column in the report as a EVALUATE Column Level Function. So, basically we want 3 members (in multiple levels. For eg we need Market, West and Dallas in the report) in the Market dimension to be displayed. So, lets change the formula of the second column and include the below EVALUATE function

EVALUATE('Intersect(%1.dimension.members,{[East],[Market],[Dallas]})',Market."Gen2,Market")

As you see, we have got a report that we needed. Lets take a look at the MDX Query

With
set [Year2] as '[Year].Generations(2).members'
set [Evaluate0] as '{Intersect([Market].Generations(2).dimension.members,{[East],[Market],[Dallas]}) }'
select
{
[Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Year2]},{[Evaluate0]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see, the EVALUATE has gone in as a column level MDX function. Now, since multiple column level MDX functions are not supported now (due to a probable bug), lets remove the evaluate from the above report. Now, lets see a version of the EVALUATE function wherein EVALUATE against the same Markets dimension would be treated as a ROW Level MDX Function. For example Rank. Lets add one more column and in the column formula use the below EVALUATE function.

EVALUATE('Rank(%1.dimension.currentmember,%1.dimension.members)',Market."Gen2,Market")

As you see, we have got the Rank of each member in the outline. Now, lets take a look at the MDX Query

With
set [Market2] as '[Market].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
member [Scenario].[MS1] as 'Rank([Market].Generations(2).dimension.currentmember,[Market].Generations(2).dimension.members)'
select
{ [Scenario].[Actual], [Scenario].[MS1] } on columns,
NON EMPTY {crossjoin ({[Market2]},{[Year2]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see the MDX Function has got passed as a Row Level MDX function. A Row Level MDX function would be passed only when the source dimension exist in the report as a seperate column. i.e in our above report, we need to have the Gen2, Market Column to generate a rank. Else it would not be generated. In the next blog entry let us look at a sample use case of these MDX functions.

In the 2 blog entries before here and here we saw the basics of BI EE and Essbase connectivity. In this blog entry we shall see more complex reporting using the EVALUATE function. Wherever possible try using this atleast in this release. This would be made more easier in the coming releases like 11g. But untill then we need to use these approaches. But the advantage of using this approach is that we would have more control on the report. The MDX functions which can be called through EVALUATE can be classified into 2 types. They are

1. Functions that return data (numeric values or measure values) - An example for this would be SUM, RANK etc (I would refer them as Column level MDX Functions)
2. Functions that returns members, sets or tupules - An example for this would be UDA etc (I would refer them as row level MDX functions as they determine the row level members)

Both of these are supported currently. But before we move further we need to understand what the above 2 mean and how they pan out in terms of the MDX queries. Also there are certain limitation in the extent of the usage of these functions. We shall understand each one of them as move forward.

The above screenshot is nothing but a MDX query containing both the types of MDX functions mentioned above. Currently in BI EE both the above function types are supported only for certain dimension types.

1. Column Level MDX Functions - These are only supported on an Essbase Accounts dimension (even if you switch the Measure Dimension within BI EE, this would work only on the Accounts Dimension within the outline). Multiple Column Level MDX Functions are supported.
2. Row Level MDX Functions - These are supported on all the dimensions. But only one Row-Level MDX function per report is supported

With that brief introduction, lets start with a typical requirement which usually comes up in multidimensional reporting. We shall use the same Demo-> Basic Cube. If you had followed my previous blog entries then revert back the aggregation on the Actuals measure to Aggr_External. The requirement is given below

A report showing all the members of Products dimension, Generation 2 of Markets and their corresponding Sales. Basically multi level reporting.

Without the knowledge of MDX Functions, the only way to achieve this is by using UNION requests from within Answers. One of the main reasons for that is BI EE treats each and every level within a dimension as a seperate column. In our report above, we need all the level members for the Products dimension, only one column(Generation 2 of Markets) and their corresponding intersection values for Sales. But understanding of MDX functions makes the task a lot easier. Lets go to answers and pull up the 3 columns as shown below.

Now in the Products column, enter the below formula.

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")

As you see, this is nothing but a row level formula since we are generating sets of members. Anyone with a knowledge of Essbase would appreciate this fact since from an Essbase standpoint we just need to provide valid combinations of members to get at the intersections. Once this is done lets look at the report and the outline of the products dimension.

As you see, we have got the data for all the products, Gen2 of Markets and their corresponding sales. But one would have to disable drills on this EVALUATE column since the drills would not work. The reason for this is the fact that, BI EE still thinks it is at a certain level and would try to fire MDX queries accordingly when we do the drills. So, as a thumb rule, disable the default drills whenever EVALUATE is used. If drills are required use navigate instead. Also, there are certain drawbacks which we shall see below.

Lets try to use the same evaluate function for more than one dimension (for example products as well as markets). So basically, we want a Sales report for all intersections of the Products and Markets dimensions. The evaluate functions are given below

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")
EVALUATE('%1.DIMENSION.MEMBERS',Market."Gen2,Market")

But this would throw an error shown below.

Lets analyze this further. If you look at the error above, this is nothing but an essbase specific error. Which is strange since our evaluate functions are correct. So, lets take a look at the MDX query that is fired back to Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

If you look at the query above, the same set for Evaluate0 is passed twice to the query. This i believe is a product bug when it generates the MDX queries. Now, lets remove one of the Evaluate0 sets in the above MDX query and fire this from Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

As you see, this is clearly a product bug since if we remove one of the Evaluate0 sets (which should actually have been the MDX query for our report above). This is such a shame as the above had such a potential to be used for more complex reporting. But this is something that has to be kept in mind while doing BI EE and Essbase Reporting. Multiple Row-Level MDX Functions are not supported.

In the last blog entry we saw how the basic BI EE and Essbase connecitivity works. Now, lets try to understand this in detail further. Lets start with understanding how the aggregation works for Essbase data sources. To begin with we shall change the level of aggregation for the Actuals Measure in the Business Model layer.

Basically, we have assigned Actuals to Level 1 of both Year as well as Markets. Leave the aggregation to the default “Aggr_External”. So, our intention is that we should get the same value that we get at Level1 of Year for Actuals to all the other levels of the Year dimension. Now, lets go back to answers and create a simple report having Gen1,Year ,Gen2, Year and Actuals.

This works as expected. But typically this is not possible from Essbase alone. MDX cannot generate values which basically show different values than what is actually present inside Essbase. So, to find out how BI EE achieves this, lets take a look at the Physical query.

As you see, BI EE basically fires 2 different MDX queries and makes an in memory join to achieve the result. The MDX queries are provided below

With
set [Market1] as '[Market].Generations(1).members'
set [Year2] as '[Year].Generations(2).members'
set [Axis1Set] as 'crossjoin ({[Market1]},{[Year2]})'
select {} on columns,
{[Axis1Set]}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

With
set [Market1] as '[Market].Generations(1).members'
set [Year1] as '[Year].Generations(1).members'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Market1]},{[Year1]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

The first query above basically gets a cartesian product of Gen1, Market (remember we had set a level aggregation of Level1 for Market in Actuals) and Gen2, Year (this is what was chosen in our report). This query does not get any data. Instead it is just used as a temporary join placeholder. The output of the first query is shown below

The second query actually gets the value of Actuals for Gen1, Market and Gen1, Year (which are the levels for the Actuals measure). BI EE actually does go against the top level node rather than doing the aggregation in-memory. It is OLAP aware atleast in this regard. In the case of relational sources it would fire the group by to get to the top node value.

The result for the report is then obtained by making a cartesian product of the above 2 queries. This cartesian product is achieved in memory of BI Server. So, as much as possible try to avoid setting aggregation levels at the BMM layer. Leave them as defaults. If you are sure that your output results would be less, then use the level based aggregations.

As a next step let us try to understand what the default aggregation (Aggr_External) does. For that let us change the aggregation for Actual measure to none.

And lets test the same report as above (With Gen1 Year,Gen2, Year and Actuals measure).

This is very interesting as well. This basically provides data for all the intersections in your Essbase Cube. Try to avoid this as well whenever you are building your reports. The MDX will basically get every intersection that is possible using a cross join across all the dimensions. There are certain use cases wherein this might be useful. The MDX query is given below

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]

Now that we have understood how the level based aggregations work lets understand how we can use MDX functions using EVALUATE to achieve some complex reports. We shall see what MDX functions are supported and how they can be used in the next blog entry.

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.