Monday, April 20, 2009

Special characters in EVALUATE-wrapped MDX

Greetings, programs!

Something I ran into recently is a little bug in the way OBIEE produces MDX out of an EVALUATE wrapper when the MDX contains special characters. (yes, it is a bug)

Let's start with a basic and pure OBIEE report. We want to see the average coverage % by the 3rd generation of our product hierarchy:

Now let's switch out the presentation column for an EVALUATE function with MDX as what we really want is the average coverage % for the scenario "Actual":

EVALUATE('(%1.dimension.currentmember, [Scenario].[Actual],[Account].[Coverage % Avg]).value' as INTEGER, Product."Gen3,Product")

Let's run it.

"Unknown Member Coverage % Avg"? Liar. I see it directly in front of me. Off to checking the log:

set [Product3] as 'Generate([Product].Generations(2).members, Descendants([Product].currentmember, [Product].Generations(3), leaves))'
member [Account].[MS1] as '([Product].Generations(3).dimension.currentmember,[Scenario].[Actual],[Account].[Coverage % Avg])).value'
member [Account].[MS2] as 'RANK([Product].Generations(3).dimension.currentmember,[Product].Generations(3).members)'

MS2} on columns,
{{[Product3]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube.Ess931_ASO]

-------------------- Query Status: Query Failed: Essbase Error: Unknown Member Coverage % Avg used in query

Do you see the difference? While on the guy, it states "Coverage % Avg", the log and the MDX which is actually fired, reference "Coverage % Avg". TWO spaces!
So let's work around this by changing the EVALUATE to include the "StrToMbr" function:

EVALUATE('(%1.dimension.currentmember, [Scenario].[Actual],StrToMbr("[Account].[Coverage % Avg]")).value' as INTEGER, Product."Gen3,Product")

When running this, we get the correct results again:

So if you want to reference members containing special characters, use StrToMbr since otherwise you will get a non-functioning MDX from the BI server.

End of line.

No comments:

Post a Comment