OBIEE with Microsoft Analysis Services - The no parameter Evaluate

In this post (done on OBIEE 11.1.1.7.xxx) I will describe a trick of using the OBIEE Evaluate function without parameters with Analysis Services. In OLAP databases (MS Analysis Services and Essbase it's critical).

Evaluate is a function that enables the OBIEE to call native database functions. You can find an Oracle white paper about it here (and it is described, with all other OBIEE functions, in appendix C of the Repository Developers Guide). 

From that guide:

Syntax of Evaluate:

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:
db_function is any valid database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result.
Use this parameter whenever the return data type cannot be reliably predicted from
the input arguments. However, do not use this parameter for type casting; if the
function needs to return a particular data type, add an explicit cast. You can typically
omit this parameter when the database-specific function has a return type not
supported by the Oracle BI Server, but is used to generate an intermediate result that
does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.

Example of Evaluate:

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Example of Evaluate_AGGR (used for aggregation functions):

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic

From some point in OBIEE, Evaluate can work without parameters as well, for example to get the system date from oracle DB, the following would work EVALUATE('sysdate') .

We will use the regular Evaluate and Evaluate_Aggr here.

When working with Microsoft Analysis Services (and Essbase) I have to use the format of the column the way they described in the Physical Layer.
For example:

• For dimension "My Dim"  with hierarchy "hier1", I have to address the hierarchy as [My Dim].[hier1]
• Based on that Hierarchy lets assume I have a folder "Customer" with column "Customer". It's addressed as usual "Customer"."Customer"

You can see some nice example of Essbase  Evaluate samples here at gerardnico.com and here at essbaseobiee.blogspot.com (the Microsoft Analysis Services cases are very similar).


In Microsoft Analysis Services (and Essbase), there are many cases we want to produce an MDX without any parameters.
Example:
I want to see the name of a column one level above my current customer (Parent), I can issue the following MDX:
[My Dim].[hier1].CurrentMember.Parent.Name.
In most cases if I try to get the same functionality in regular code  created by OBIEE or other BI tools, I'll will have a join between levels of hierarchy, so I would rather call the MDX from EVALUATE function.

Unfortunately running  
Evaluate('[My Dim].[hier1].CurrentMember.Parent.Name' AS CHARACTER(40)) will not work.
I will get something like:

View Display Error

Odbc driver returned an error (SQLExecDirectW). Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 22001] Target data source does not support the Complex Expression operation. (HY000)

I need to pass as parameter a value from that hierarchy level, I working on. On the other hand I don't really need a parameter here. We solve the problem by setting the parameter as remark:
Lets return to my example with   "Customer"."Customer", I have to issue the following code:

Evaluate('/*%1*/ [My Dim].[hier1].CurrentMember.Parent.Name' AS CHARACTER(40), "Customer"."Customer")

 So the actual MDX would be /*"Customer"."Customer"*/ [My Dim].[hier1].CurrentMember.Parent.Name that is the wanted MDX, with a remark. 

This option works well.

So should similar code such as /*%1*/ [My Dim].[hier1].CurrentMember.Properties("THEProperyName")




Another example with EVALUAT_ AGGR that returns the Revenue of the previous 6 month (thank you Dan Wities from Matomy for the example):

Evaluate_AGGR('/*%1*/sum({[Month Set].Item(0).LastChild.Lag(7):[Month Set].Item(0).LastChild.Lag(1)},[Measures].[Revenue])' as DOUBLE, "Customer"."Customer")

It returns the Revenue of the last value from first tuple ( item(0) ) of month set (in that case, the current month) -7,  till the same -1 month, meaning the previous 6 month. (this is specific for that data structure).

P.S. 1: In case some OBIEE Essbase expert have a better solution, I'll be happy to hear.
P.S. 2: There is a similar idea, done on Essbase and OBIEE 10, from everythingoracle.com here