Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

Had OBIEE had been designed with Essbase in mind then specialized functions would have been created to generate column- and row-based MDX.  So, for example, when function calls like:

 

MDXOnCol( '<column expression>' )

 

and

 

MDXOnRow( '<row expression>' )

 

were inserted into the OBIEE column formula editor, then the following MDX would be sent to Essbase:

 

Select { <column expression> } on columns,
       non empty { <row expression> } on rows
  From [ <application>.<database> ]

 

To make the following discussion less abstract, let’s take two simple MDX expressions that we can run against the “Basic” database from the “Demo” application.  The sum of sales for the first four months of the year is an “on column” expression:

 

Sum( { [Qtr1], [Apr] }, [Sales] )

 

and the list consisting of the “Product”, “Audio”, and “Camera” members, spanning all three generations of the “Product” dimension, is an “on row” expression:

 

{ [Product], [Audio], [Camera] }

 

Ideally, we’d like to be able to enter these MDX expressions into the OBIEE column formula editor as follows:

 

and

 

Let’s see what we can do to approximate this objective with the limited toolset at our disposal.

 

 

The Problem with “Evaluate”

 

Unfortunately, instead of functions like “MDXOnCol” and “MDXOnRow” the “only game in town” when it comes to creating MDX expressions is “Evaluate”, a function that is singularly ill-suited to the task.

 

If we code:

 

Evaluate( '<expression>' )

 

then we get an error since “Evaluate” requires at least two arguments, the second and subsequent of which must be column names.  If we add a column name as the second argument:

 

Evaluate( '<expression>', <column name> )

 

then we are told that:

 

  The argument number 1 is not referenced in the evaluate expression

 

So, we’re not going to get past OBIEE’s syntax checking unless we add “%1” somewhere within the first argument.

 

Now many of the MDX expressions that we wish to write – such as the two examples above – don’t need to make a reference to a column name.  But if we just add “%1” to the first argument at random we’re almost certain to get invalid syntax or an invalid result when the expression is evaluated.  So we’re forced to think of ways of inserting a reference to “%1” into the first argument that will have no impact on the value of the MDX expression.

 

Now a column name in the form:

 

<Dimension>."Gen<n>,<Dimension>"

 

is converted after a “%1” substitution to the MDX sub-expression:

 

[Dimension].Generation(<n>)

 

so we need to find a way of using this sub-expression that will have no impact on the value of the MDX expression that we are trying to evaluate.

 

Let’s look at our first example:

 

Sum( { [Qtr1], [Apr] }, [Sales] )

 

Since this is a sum, it will make no difference to the result if we both add and subtract the same quantity.  We can convert “[Dimension].Generation(<n>)” into a number as follows:

 

[Dimension].Generation(<n>).Dimension.CurrentMember.Value

 

So an “Evaluate” expression of the form:

 

Evaluate (
   '%1.Dimension.CurrentMember.Value -
   %1.Dimension.CurrentMember.Value +
   Sum( { [Qtr1], [Apr] }, [Sales] )',
   Year."Gen1,Year"
)

 

will give the intended result.

 

Now, let’s look at our second example:

 

{ [Product], [Audio], [Camera] }

 

As these three members belong to the “Product” dimension, the intersection of this target set with the set consisting of all members of the “Product” dimension will still yield the same target set.

 

We can convert “[Dimension].Generation(<n>)” into a set of dimension members as follows:

 

[Dimension].Generation(<n>).Dimension.Members

 

So an “Evaluate” expression of the form:

 

Evaluate (
   'Intersect (
      %1.Dimension.Members,
      { [Product], [Audio], [Camera] }
   )',
   Product."Gen1,Product" )
)

 

will give the intended result.

 

Clearly, the requirement to add a “no-effects” reference to an MDX expression is both error prone and time wasting.  And a developer tasked with maintaining the code has to work out whether the reference to “%1” is a necessary component of the MDX expression, or has just been added as padding to overcome the limitations imposed by the syntax of “Evaluate”.  The extra padding also makes the MDX expression difficult to read, which is not helped by the inability of the column formula editor – surely a candidate for one of the world’s worst code editors – to preserve newlines:

 

and

 

 

Taming “Evaluate”

 

To make “Evaluate” more developer friendly what we need is a generic method of making a reference to “%1” within the first argument.  It must be a method that will work for all MDX expressions, so that we don’t have to waste time inventing custom workarounds.  It should also be a method that makes it immediately clear whether a reference to “%1” is a necessary part of the MDX expression, or whether it’s just a generic placeholder needed to comply with the syntactic demands of the “Evaluate” function.

 

An approach that meets all these criteria is to code our “want-to-be”:

 

MDXOn(Col|Row)( '<expression>' )

 

in the following form:

 

Evaluate( '<expression> /* %1 */', <column name> )

 

This formulation will always work because (1) OBIEE will find a reference to “%1” in the first argument which will satisfy its syntactic requirements; and (2) when the code is function-shipped to Essbase, the substituted column reference that lies between the pair of MDX comments will be ignored and never evaluated.

 

From an OBIEE developer perspective we can apply the same solution for all MDX expressions, and it will be obvious that any reference to “%1” placed within a comment in the trailing portion of the MDX expression is just there as a generic workaround and is unrelated to the MDX expression itself.

 

So we have

 

instead of

 

and

 

instead of

 

As OBIEE doesn’t allow us to define our own functions or doesn’t have a column formula pre-processor this is about the best we can do to make “Evaluate” Essbase-friendly at present.  We can but hope that 11g will have better functionality on offer.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved