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).
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 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
