OBIEE Calculations, MDX Functions, Flattened Measures, and Essbase Sources
When you starting working with Essbase sources and OBIEE, how you create calculations quickly becomes one of the more interesting aspects of working with the two technologies. With Essbase, you've got a powerful multi-dimensional database that features many functions and calculation capabilities that you'd like to use with your analyses and dashboards, and the BI Server does an excellent job of hiding the complexity of working with sources such as these. But if you want to get the best out of Essbase, or at least understand how OBIEE and the BI Server interacts with the Essbase server, it's worth taking a look under the covers to see how things work. If you know a bit of Essbase and MDX, the query language that OBIEE uses to communicate with Essbase, it's also interesting to see how OBIEE's MDX compares with the MDX you'd write using a tool such as Essbase Administration Services or the MAXL/MDX command-line tool, essmsh.
To illustrate what I'm talking about, take a simple analysis created against the Sample.Basic Essbase database, which I've imported into an OBIEE 11.1.1.5. repository using the default settings, and then created physical cube columns for the UDAs within the Market dimension, and the Default alias table within the Product dimension.
Notice how, at this stage, the measure hierarchy has been brought in as-is, with the full hierarchy preserved and a single measure, in a physical cube column called Basic - measure. To get access to a particular measure (for example, Profit), you'll need to include this measure in an analysis criteria and filter on the Gen2, Measures, Gen3, Measures or Gen4, Measures logical column; or alternatively, access the measure dimension via a hierarchical column, and drill-down or use selection steps until you get to the one you want.
So let's start then by creating an analysis where I have the Marketing and Payroll measures along the columns, and the East and West regions on the rows, and the numbers are for the Budget scenario. In MDX terms, using the essmsh command-line tool this would look something like:
MAXL> select {Measures.Marketing, Measures.Payroll} ON COLUMNS,
2> {Market.East, Market.West, Market.South} ON ROWS
3> from Sample.Basic
4> where {Scenario.Budget};
Axis-1 (Marketing) (Payroll)
+-------------------+-------------------+-------------------
(East) 11210 7100
(West) 16800 11660
(South) 6120 5090
In OBIEE 11g, an equivalent analysis would look like this, using a pivot table view:
The criteria to create such an analysis would look like this:
Now this criteria is relatively simple, as we've selected measures that are within the same generation (Marketing and Payroll, both within the third generation in the Measure hierarchy). In reality, your measures are likely to come from different measure hierarchy generations, which is the reason you'll often flatten the measures in the OBIEE physical model, but I'll come on to the why and wherefores of that in a moment. For now though, the criteria is fairly simple and results in the following MDX sent to the Essbase server.
With
set [_Market2] as '{Distinct({[Market].[East], [Market].[South], [Market].[West]})}'
set [_Measures4] as '{Distinct({[Measures].[Marketing], [Measures].[Payroll]})}'
select
{ [Attribute Calculations]
} on columns,
NON EMPTY {crossjoin({[_Market2]},{[_Measures4]})} properties GEN_NUMBER, [Measures].[MEMBER_UNIQUE_NAME], [Measures].[Memnor], [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
where ([Scenario].[Budget])
OK, not so bad. It's a variation on what we coded by hand (using WITH blocks to define the set of rows and columns upfront, and a crossjoin to return the product of the two dimension member sets, but it's recognizable as being more or less equivalent to our hand-coded MDX.
So what if we try a simple calculation? Let's add the Marketing and Payroll measures together to create a new measure called Direct Costs. In MDX, I'd write this as:
MAXL> with member Measures.[Direct Costs] as
2> 'Measures.Marketing + Measures.Payroll'
3> select {Measures.Marketing, Measures.Payroll, Measures.[Direct Costs]} ON COLUMNS,
4> {Market.East, Market.West, Market.South} ON ROWS
5> from Sample.Basic
6> where {Scenario.Budget};
Axis-1 (Marketing) (Payroll) (Direct Costs)
+-------------------+-------------------+-------------------+-------------------
(East) 11210 7100 18310
(West) 16800 11660 28460
(South) 6120 5090 11210
So let's try and create this now using OBIEE. Going back to the analysis criteria, I look for a way to add the two measures together.
OK, so this is interesting. There's no Marketing or Payroll measure to select and use as part of an expression, as the measure name is actually a dimension member and there's only one measure as-such. This is the drawback with bringing your measure dimension into OBIEE - you don't then have individual measures you can select from to include in an analysis, create or include in calculations, and so on.
Now one way around this is to use the new "selection steps" feature in OBIEE 11g to calculate this value post-aggregation, after the initial filtering has happened and using the resulting result set. I therefore create a new selection step, like this:
and Bob is indeed your uncle.
So apart from figuring out the selection step move, this was actually quite straightforward. So how does the MDX look?
With
set [_Market2] as '{Distinct({[Market].[East], [Market].[South], [Market].[West]})}'
set [_Measures4] as '{Distinct({[Measures].[Marketing], [Measures].[Payroll]})}'
set [_Measures4FILTER1] as '{Distinct({[Measures].[Payroll]})}'
set [_Measures4FILTER2] as '{Distinct({[Measures].[Marketing]})}'
member [Attribute Calculations].[_MSCMFILTER1] as 'AGGREGATE(Intersect([_Measures4FILTER1], {[Measures].CurrentMember}))', SOLVE_ORDER = 102
member [Attribute Calculations].[_MSCMFILTER2] as 'AGGREGATE(Intersect([_Measures4FILTER2], {[Measures].CurrentMember}))', SOLVE_ORDER = 102
select
{ [Attribute Calculations],
[Attribute Calculations].[_MSCMFILTER1],
[Attribute Calculations].[_MSCMFILTER2]
} on columns,
NON EMPTY {crossjoin({[_Market2]},{[_Measures4]})} properties GEN_NUMBER, [Measures].[MEMBER_UNIQUE_NAME], [Measures].[Memnor], [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
where ([Scenario].[Budget])
OK, so what's going on here is the situation Venkat described back in some earlier blog posts, where selection steps involves the BI Server generating MDX that calculates lots of additional values which it then combines back together to give you your result set. Running this MDX statement through the MAXL command shell gives us this output:
Axis-1 Axis-1.properties (Attribute Calculat (_MSCMFILTER1) (_MSCMFILTER2) +-------------------+-------------------+-------------------+-------------------+------------------- (East, Marketing) (GEN_NUMBER = 2, ty 11210 #Missing 11210 (East, Payroll) (GEN_NUMBER = 2, ty 7100 7100 #Missing (South, Marketing) (GEN_NUMBER = 2, ty 6120 #Missing 6120 (South, Payroll) (GEN_NUMBER = 2, ty 5090 5090 #Missing (West, Marketing) (GEN_NUMBER = 2, ty 16800 #Missing 16800 (West, Payroll) (GEN_NUMBER = 2, ty 11660 11660 #Missing
The BI Server takes this result set, plus some additional member information it gets via another MDX query, and adds the results together as per the selection step calculation to give you your direct costs calculation. So whilst it's very easy for the end-user to create their calculation (and in the case of using selection steps, just as easy to select measures from different measure hierarchy generations), but it's not actually Essbase that's doing the calculation, its the BI Server (and in fact Essbase is doing a lot of extra work to return all these subtotals, at least compared to my hand-written MDX).
An alternative to using selection steps though, would be to flatten the measure hierarchy into a flat list of measures, the same way that OBIEE 10g did by default when importing Essbase outlines. How does this affect the MDX that's generated for a calculation like this?
I start then by duplicating the Essbase database in the OBIEE repository physical layer, and then select the option to flatten the measure hierarchy, giving me a physical model looking like this:
Now creating my calculation in the analysis criteria is really easy, as I've got individual measures for the Marketing and Payroll members, which I can just add together to create a new measure in the criteria.
So how do the numbers look? One advantage of working with a flattened list of measures, is that you can use a regular table view to display the results, rather than having to use a pivot table view as you do with a measure hierarchy. Taking a first look, the figures look correct:
What about the MDX? Well, it's actually a lot closer to the MDX I wrote by hand; instead of adding the two measures together, it creates a set of the two (giving the same result, in this instance), but other than that, it looks pretty close to the MDX I wrote.
With
set [_Market2] as '{Distinct({[Market].[East], [Market].[South], [Market].[West]})}'
select
{ [Measures].[Payroll],
[Measures].[Marketing]
} on columns,
NON EMPTY {{[_Market2]}} properties GEN_NUMBER, [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
where ([Scenario].[Budget])
Just as a quick check, I create another analysis that randomly adds up, divides and otherwise applies simple operators to some measures.
Now if I were to write an MDX statement to retrieve numbers like this, I'd probably write something along the lines of:
MAXL> with member Measures.[Sales Divided by Marketing]
2> as 'Measures.Sales / Measures.Marketing'
3> member Measures.[Sales Plus Total Expenses]
4> as 'Measures.Sales + Measures.[Total Expenses]'
5> member Measures.[Sales times Margin]
6> as 'Measures.Sales * Measures.Margin'
7> select {Measures.Sales, Measures.Marketing, Measures.[Sales Divided by Marketing], Measures.[Sales Plus Total Expenses], Measures.[Sales times Margin]} ON COLUMNS,
8> {Market.children} ON ROWS
9> from [Sample.Basic]
10> ;
Axis-1 (Sales) (Marketing) (Sales Divided by M (Sales Plus Total E (Sales times Margin
+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------
(East) 87398 14721 5.93696080429319 112708 4323666458
(West) 132931 23529 5.64966636916146 173096 9308626206
(South) 50846 8281 6.14007970051926 66504 1469246016
(Central) 129680 19706 6.5807368314219 164544 9482979680
Looking at the MDX generated by the BI Server though, using flattened measures, gives us a bit of a surprise:
With
set [_Market2] as '[Market].Generations(2).members'
select
{ [Measures].[Marketing],
[Measures].[Sales],
[Measures].[Total Expenses],
[Measures].[Margin]
} on columns,
NON EMPTY {{[_Market2]}} properties GEN_NUMBER, [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
All the BI Server does, then, is just request the basic values for measures from Essbase. The BI Server then performs the calculations, and you'll find this happens all over the place. Where you would have assumed that the BI Server would ship-down calculations to the Essbase server, instead it just requests the basic data and performs the calculation in-memory, in the BI Server.
But if you particularly want to make use of an Essbase MDX function, there is actually a way you can do this, and it's through the EVALUATE, EVALUATE_AGGR and EVALUATE_PREDICATE functions within OBIEE. These functions provide a way to pass through a request to the underlying datasource for an analysis to use a particular function, and you can use this approach to make use of Oracle database functions that either don't have an equivalent within OBIEE, or perhaps you've written yourself. But you can also use this approach for working with Essbase sources, and it even allows you to do things that would be tricky to do in the ROLAP-world of OBIEE but are easy to do within a multi-dimensional database such as Essbase.
Take, for example, a situation were we want to list out, in an analysis, a list of markets together with sales amount AND the average sale amount for the states within each of the regions. In this case, getting the average sale amount for each region's constituent states would actually be quite tricky with a regular relational source, as the dimension members your calculating the average for are at a different level of granularity to the one used in the query, but for Essbase this is easy. Let's take a look at how we'd write this in MDX first.
MAXL> with
2> set regions as 'Market.children'
3> member Measures.Children_AVG as 'AVG(Market.currentmember.children,Measures.Sales)'
4> select
5> { Measures.Sales,
6> Measures.Children_AVG
7> } on columns,
8> NON EMPTY {regions} on rows
9> from [Sample.Basic];
Axis-1 (Sales) (Children_AVG)
+-------------------+-------------------+-------------------
(East) 87398 17479.6
(West) 132931 26586.2
(South) 50846 12711.5
(Central) 129680 21613.3333333333
Now moving over to OBIEE, what we want to use now is an EVALUATE_AGGR function, to leverage the AVG MDX function. This function takes two arguments:
AVG(set, numeric_value)
In other words, you pass it a set of members (or "tuples" in Essbase-speak), and a number to perform the average over. So now, if we want to use this in OBIEE to return an aggregated value, we need to substitute a dimension member reference and a measure reference which in turn will be passed down, along with the call to the AVG MDX function, to Essbase.
To do this I create a new logical column in the business model holding my Essbase data (the one with flattened measures), and edit the logical table source expression list to reference this function.
I also set the default aggregation type for this new column to AGGR_EXTERNAL, and then copy it across to the presentation layer subject area.
Now I can create an analysis that lists out the regions, their total sales, and then the average sale value for the states below each region, something that it'd actually be quite tricky to do with a relational source.
Taking a look at the MDX generated by the BI Server, it looks along the same lines as the one we wrote by hand:
With
set [_Market2] as '[Market].Generations(2).members'
member [Measures].[_MSCM1] as 'AVG(Market.currentmember.children,[Measures].[Sales])', SOLVE_ORDER = 101
select
{ [Measures].[Sales],
[Measures].[_MSCM1]
} on columns,
NON EMPTY {{[_Market2]}} properties GEN_NUMBER, [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
Now what we've seen there is an example of an MDX function that returns an aggregated value, hence our use of EVALUATE_AGGR. But we can also return values that can be used as attributes for a dimension member, for example the count of immediate "children" under a dimension member. In this example, if we were to write a query in MDX that returned total sales for a region and also the number of states underneath it, the query would look like this:
MAXL> WITH
2> member Measures.Num_Children AS
3> 'count(Market.currentmember.children)'
4> select {measures.sales, measures.num_children} on columns,
5> {market.children} on rows
6> from Sample.Basic;
Axis-1 (Sales) (Num_Children)
+-------------------+-------------------+-------------------
(East) 87398 5
(West) 132931 5
(South) 50846 4
(Central) 129680 6
To make use of this dimension member count in OBIEE, we'd need to use the EVALUATE function, and pass to it a reference to the currently selected dimension members' children, and the name of the dimension that we'll be calculating this for.
Taking a look at the MDX generated by the BI Server, it's as you'd expect.
With
set [_Market2] as '[Market].Generations(2).members'
member [Measures].[_MSCM1] as 'count([Market].Generations(1).dimension.currentmember.children)'
select
{ [Measures].[Sales],
[Measures].[_MSCM1]
} on columns,
NON EMPTY {{[_Market2]}} properties GEN_NUMBER, [Market].[MEMBER_UNIQUE_NAME], [Market].[Memnor] on rows
from [Sample.Basic]
So it's a bit of a mixed picture with calculations with an Essbase source. On the one hand, OBIEE doesn't really make much use at all of MDX functions, instead retrieving just the basic measure values from the Essbase database and performing the calculations itself, within the BI Server. In many cases this is fine, but it can trip you up if you're expecting to offload processing to the Essbase server but instead, you find the BI Server is actually doing most of the work. In some cases there's good reasons for the approach Oracle have taken - not all MDX functions work across all storage types, for example - but it's worth being aware of this before you get too deep into working with Essbase.
The flip side of this though is being able to access MDX functions directly via the EVALUATE_AGGR and EVALUATE functions. These functions actually give you the ability to perform calculations, across levels and dimensions, that you would even be able to do with regular sources and OBIEE functions, and on most projects using Essbase sources you're going to be using these functions alot, both to compensate for lack of push-down from the BI Server, but mostly to take advantage of the MDX functions and cross-hierarchy and level calculations that you can perform from Essbase.
There is one exception to all this though, and it's an interesting one. The one type of OBIEE function that does get pushed down correctly to Essbase, is time-series functions. The AGO, TODATE and (new in 11g) PERIODROLLING do indeed get translated down to their equivalent MDX functions, which means that you can use the time-series calculations and wizard available in OBIEE to create all your time-based derived measures.
For example, consider a situation where you wish to calculate the value of sales for the previous quarter. In OBIEE, you'd create a time-series calculation looking like this:
Then your analysis would look like this:
Taking a look at the MDX generated by the BI Server, it does indeed use the PARALLELPERIOD MDX function.
With
set [_Year2] as '[Year].Generations(2).members'
member [Measures].[_MSCM1] as '(ParallelPeriod([Year].[Quarter], 1,[Year].currentmember), [Measures].[Sales])'
select
{ [Measures].[Sales],
[Measures].[_MSCM1]
} on columns,
NON EMPTY {{[_Year2]}} properties GEN_NUMBER, [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows
from [Sample.Basic]
So, it's been a long post, but what observations and rules-of-thumb can we draw therefore for Essbase sources and calculations?
- Measure hierarchies are great for end-users, particularly if they come from a traditional Essbase background. Coupled with selection steps, it's easy to select measures from a hierarchy and perform calculations all from the Answers UI
- But measure hierarchies can often generate quite complex and costly MDX queries, and it's often easier, as a developer, to work with flattened lists of measures. You can create these in a separate physical database model, or combine a measure hierarchy with a flattened list of measures in the same business model
- OBIEE out-of-the-box actually makes very little use of MDX functions in the Essbase database, instead choosing in most cases to just request measure values and then performing any calculations itself
- The exception to this is time-series queries, which do get pushed-down to their equivalent MDX function
- In some cases, you can compensate for this lack of push-down through the EVALUATE and EVALUATE_AGGR functions, but they don't cover all situations and also limit you in terms of drill-down etc. EVALUATE and EVALUATE_AGGR also provide you with some extra functionality though, as they let you harness the ability of Essbase to calculate numbers across different hierarchy levels and dimensions.
That's it for Essbase for a while now; keep an eye on the blog for a bit more on OBIEE 11g and Oracle OLAP, plus possibly some content on OBIEE 11g and Microsoft Analysis Services, to see how well support for Microsoft's OLAP server compares to Essbase.