Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

In the case of the “Duplicate Symbol” bug that we discussed at length in a previous article (see “Multi-Member MDX – The Duplicate Symbol Bug” ), we have an example of a source system that sends syntactically invalid code to a target system.  But as we cannot get access to the source system we are unable to correct the error where it should be corrected, at source.

 

Syntactic bypass is a general principle that can be used to develop workarounds for bugs that belong to this category.  Since the syntactically invalid code that is generated by the source system cannot be altered, the only option is to add extra code to that which is transmitted by default.  This extra code causes the target system to bypass the syntactically invalid code and provides an alternative version of the code that works as intended.  As is so often the case, the approach to syntactic bypass that we’ll use to workaround the “Duplicate Symbol” bug involves code injection of the target system’s set of comment markers at carefully selected locations within the syntactically invalid code fragment.

 

 

The Principle

 

Let’s begin where we left off in the previous article (see the section “Characterizing the Bug”) with an example of the offending MDX:

 

The first observation on which this fix is based is that the “Evaluate” function does not check the syntax of the code that it sends to the back-end data source: it simply checks for the existence of a column substitution token, “%1”, replaces it, and then sends the code out the back door.

 

The second observation on which this fix is based is that Essbase is very tolerant in respect of the placement within an MDX expression of the comment markers “/*” and “*/”.  As you can see from the example above, comment markers are recognized even when they occur within the quotes that define the text of “set” clauses, a fact we have used to good effect to simplify the construction of MDX expressions using “Evaluate” (see the article entitled “Making ‘Evaluate’ MDX-Friendly” ).

 

As we discussed previously, the presence of the three duplicate “set” clauses in the example above is the source of the “Duplicate Symbol” bug.  Now if we can’t delete these clauses, perhaps we can comment them out, and then add back in at the end of the list of “set” clauses the code that a bug-free OBIEE would have generated.

 

The problem we face is that we only have access to the text that occurs between the quotes of a “set” clause; for example:

 

set [Evaluate<n>] as '{<we have access to this>}'

 

Let’s start by adding a leading comment marker, “/*”, to each of our three “Evaluate” column formulae.  This produces the following MDX:

 

The fact that we have a leading comment marker, “/*”, followed by another won’t break us out of a comment that we have already entered.  However, the presence of a trailing comment marker, “*/”, will.  Since the trailing comment markers were only added to terminate the comments used for generic column substitution we can omit them if required.  Let’s do so, except for the “Evaluate” function call corresponding to the final, right-most column in the request.  The critical fact about this final column is that the set clause it generates is not duplicated and it occurs right at the end of the list of “set” clauses.  Our MDX becomes:

 

If we look at what Essbase sees when it parses this code fragment then it is equivalent to the following MDX:

 

set [Evaluate0] as '{ }'

 

This change in the code is reflected in a new error message which reads:

 

  Unknown Member Evaluate 1 used in query (HY000)

 

We have now completed the first element of the syntactic bypass: we have eliminated the faulty code by making it invisible to Essbase.  The final element is to add back in the correct code.  Now the code that OBIEE should have generated in the absence of the bug (we can ignore the column substitutions within comments) is as follows:

 

We can see that we already have the following opening prefix:

 

set [Evaluate0] as ‘{

 

and also the following postfix:

 

}’

 

So what we need to add in between is the following code fragment:

 

If we use this code as a replacement for the MDX expression in the third “Evaluate” column formula we get:

 

Note that the trailing comment marker, “*/”, has been added right at the beginning of the first argument to close the comment that was started after “[Evaluate0]”, making the code in the first argument visible to Essbase.

 

When we try to save the column formula that contains this “Evaluate” function call we get an Answers error since the text we have added contains embedded single quotes.  If we escape these embedded quotes by doubling them up:

 

then “success” – we get a request that runs correctly and produces the following MDX:

 

Now we can do a little tidying up.  The code in the “Evaluate” function calls for the first two columns is never processed by Essbase as it is commented out within the MDX.  So we can replace these calls with dummy calls that simply serve as placeholders:

 

Evaluate( '/* %1', Year."Gen2,Year" )

Evaluate( '/* %1', Market."Gen2,Market" )

 

With these replacements the request still runs correctly and the MDX simplifies to:

 

 

The Practice

 

Having read the previous section, you might be forgiven for thinking that the “cure” is almost as bad as the “disease”!  As is always the case with syntactic bypass, great care is needed in placing the comment markers in exactly the right places.

 

In this section we’ll reduce the workaround to a pair of generic templates, so that we won’t have to mentally rehearse the “sleight-of-hand” using the comment markers each time the workaround is applied.

 

Let’s suppose that we have “n” columns that are going to be used for member selection, each of which makes use of an “Evaluate” function call.

 

Rule 1

 

For each column in the range (1 – n-1), create a column formula based on the following template:

 

The choices made for the dimensions and generation numbers that define the presentation column names don’t matter, but the collection of column names must be distinct.

 

Rule 2

 

For the “nth”, right-most “Evaluate” column create a column formula based on the following template:

 

substituting for each occurrence of “<MDX column m>” the corresponding MDX expression that you wish to evaluate.  Set the value for “<n-1>” in the final “set” clause to one less than the total number of “Evaluate” columns.  As before, the choice made for the dimension and generation number that defines the presentation column name doesn’t matter, but the column name must be distinct from those used for all the other “Evaluate” columns (note that the same dimension can be used for multiple columns; it is only the combination of dimension and generation number that must be unique). 

 

Mixing “Evaluate” Columns with Default Columns

 

This workaround for function calls to “Evaluate” does not preclude the use of default columns that just select the set of members belonging to a particular OBIEE level.  These default columns can be intermingled with the “Evaluate” columns in a request without causing any problems.  The reason this is possible is that OBIEE generates the “set” clauses for “Evaluate” columns as a contiguous block of code.  The workaround operates exclusively on this code block, so that code that precedes it, or that follows it, is not altered or excised in the process.

 

 

Making the Practice Perfect

 

To finish off, let’s apply these templates to a few specific examples to illustrate that, despite its derivation, this workaround can be easily applied in practice.

 

Two “Evaluate” Columns

 

In this example we have two “Evaluate” columns.  The first column will display the four quarters of the year, “Q1”, “Q2”, “Q3”, and “Q4”, from the “Year” dimension.  The corresponding MDX is:

 

[Year].Generations(2).Members

 

The second column will display the member “Boston” and all of its ancestors, “Boston”, “East”, and “Market”, from the “Marketing” dimension.  The corresponding MDX is:

 

Ancestors( [Boston], 2 )

 

In this example “n” equals two, so selecting “Year” for the dimension and “1” for the generation number in the template:

 

yields the following column formula:

 

The second column is the last, right-most “Evaluate” column, so substituting for the two MDX formulae and selecting “Market” for the dimension and “1” for the generation number in the template:

 

yields the following column formula:

 

On running the request the following MDX is generated:

 

which creates the following report:

 

Three “Evaluate” Columns

 

In this example we have three columns.  Let’s assume that the first two columns are the same as in the previous example.

 

The first column will display the four quarters of the year, “Q1”, “Q2”, “Q3”, and “Q4”, from the “Year” dimension.  The corresponding MDX is:

 

[Year].Generations(2).Members

 

The second column will display the member “Boston” and all of its ancestors, “Boston”, “East”, and “Market”, from the “Marketing” dimension.  The corresponding MDX is:

 

Ancestors( [Boston], 2 )

 

The third column will display the product categories, “Audio” and “Visual”, from the “Product” dimension.  The corresponding MDX is (using “levels” rather than “generations” for the sake of variety):

 

Product.Levels(1).Members

 

In this example “n” equals three, so selecting “Year” and “Market” for the dimensions and “1” for the generation number in the template:

 

yields the following column formulae for columns one and two:

 

The third column is the last, right-most “Evaluate” column, so substituting for the three MDX formulae and selecting “Product” for the dimension and “1” for the generation number in the template:

 

yields the following column formula:

 

On running the request the following MDX is generated:

 

which creates the following report:

 

 

Summary

 

This workaround greatly extends the range of Essbase-based reports that can be generated from within OBIEE.  While deriving this workaround has involved a certain amount of “slight-of-hand”, it is easy to apply in practice.  The relevant values can be substituted into the templates without understanding their derivations, and without impacting developer productivity.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved