Evaluate Function

EVALUATE function can be used in OBIEE to directly to call a Database Function from OBIEE Answers 

Syntax:-EVALUATE('your db function(%1,%2)', parameter list)
---%1 and %2 are no.of parameters (in this example 2 parameters will be passed to the database function)

%1, ..., %X means - x number of parameters will be passed, and the values for those parameters will be passed in the parameter list.

Example:
EVALUATE('TO_CHAR(%1,%2)' ,"Dim- Date".Start Date,'DD-MON-YY')
or
EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "Dim- Date".Start Date, 'MON-YY')

Special characters in EVALUATE-wrapped MDX

Something I ran into recently is a little bug in the way OBIEE 10.1.3.4.0 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:

With 
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)'

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

-------------------- Query Status: Query Failed: Essbase Error: UnknownMember 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

Capitalizing the Initial character of a Column’s Data in OBIEE

Let’s say, your database containing the data in small letters for one of the columns.

Now, requirement is like, need to capitalize the first character of every value in that column.

One approach is: If you are using Oracle database, you can use Initcap function with Evaluatefunction in obiee. Actually, Initcap function in oracle capitalizes every character which comes after space along with 1^st character.

You need to write functionality as shown below, to achieve your requirement.

Syntax:  EVALUATE(‘INITCAP(%1)‘, Table.ColumnName)

Ex : EVALUATE(‘INITCAP(%1)‘, Products.”Prod Category”)

If it’s not oracle database, you find equivalent and appropriate function for Initcap for your own database and write the same in Evaluate function

Another Approach is:  Write the following functionality in one column of answers

upper(SUBSTRING(Products.”Prod Subcategory” FROM 1 FOR 1)) ||SUBSTRING(Products.”Prod Subcategory” FROM 2)

But this shows only first letter as capital, and remaining all in smaller case.

You can write these functionalities in rpd also… and make use those columns directly in Answers.

You can find my answer in forums for same question here …

Oracle BI EE 10.1.3.3.2 – Handling Sort Order in Hyperion Essbase 9.3.1 – EVALUATE and MDX

Another common question that one would get while working with BI EE and Hyperion Essbase is “How to have the default sort order specified in an Essbase Cube to be available in BI EE reports?”. This is a very valid question since by default BI EE sorts the dimension attributes alphabetically. For example look at the screenshot below,

     

As you see above, BI EE by default has alphabetically sorted the channels report. But if we look at the outline of the channels dimension, the sorting order would be completely different. In most cases, we would like to have the same sort order as the cube (not an alphabetical sort).

     

In order to overcome this, there are 2 approaches. Both of them would involve the use of EVALUATE function and then passing an MDX function to the cube to fetch the rank of each dimensional attribute. So, lets look at the first approach. In this approach we shall be adding a new column and in the formula enter the below formula

EVALUATE(‘RANK(%1.dimension.currentmember,%2.members)’ AS INTEGER,CHANNELS.”Gen4,CHANNELS”,CHANNELS.”Gen4,CHANNELS”)

What this basically does is, it passes the member of the dimension to the MDX function RANK and retrieves the position of the member in the particular level.

     

     

Now hide this new column and apply sorting on it. This will give the default sort order as available in the Essbase Cube.

     

The problem with the above approach is that the sort order would have to be created for each and every report. In order to overcome that, create another logical column in the repository with the evaluate function and then apply sort order on the 4th level based on this column.

     

     

Also, ensure that you have assigned an hierarchy level to the new column.

     

     

Thanks to Alan Lee for sharing this. One can extend this to provide lot of different functionalites that BI EE does not offer out of the box.

Evaluate with Date Functions

If you’re not concerned about database portability – for example, you use Oracle and that’s that, forever – then the evaluate function in OBIEE can be useful. However, using the evaluate function can be tricky, and the documentation could be better.
Here’s an example of using Evaluate with the Oracle next_day function, which returns the date of the next specified day of the week following any given date. This function is useful for calculating the week ending date for any given date. If your standard week ends on Saturday, then the expression you would use would be next_day(<date>, ‘saturday’).  This is quite simple, but how do you use it within OBIEE?
Here’s a simple subject area in OBIEE which I used to test how to use next_day( ). It contains two logical tables: a Days table and a Facts table. The facts table shows how many calls occurred on any day.

As these query results show, there is one call per day.

The objective was to sum up the number of calls for each Saturday-ending week using the Oracle next_day function.
When you use the evaluate function in OBIEE, the expression builder presents you with this template:

Following the template, you would write this:

However, this syntax produced a “Union of non-compatible types” error.

I do not know what this error is really saying. Does the column name have to be enclosed in single quotes to make it a string?

The administration tool accepts this, but you get a SQL error when you use it in a query.
ORA-01858: a non-numeric character was found where a numeric was expected
The reason is obvious when you look at the SQL generated:
select next_day('"XE".""."XE"."DAYS"."DATE1"','saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day('"XE".""."XE"."DAYS"."DATE1"','saturday')
order by C1
Instead of providing a column identifier that had a date data type as an argument in the next_day function, the SQL contained a string value.
My next thought when I encountered this was to include the column name as part of the first “str_Expr”.

However, this produced another Oracle error:
ORA-01741: illegal zero-length identifier
So to fix this I edited the column identifier.

The administration tool accepted this, but it produced a different SQL error at run time:
ORA-00904: "DAYS"."DATE1": invalid identifier
The reason this is an invalid identifier is that the SQL OBIEE generated included a table alias for the DAYS table, T3392.
select next_day("DAYS"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("DAYS"."DATE1",'saturday')
order by C1
To fix this, I deleted the DAYS identifier in the function. This might work, but not if you have a DATE1 column defined in more than one table in the FROM clause. This data contained a DATE1 column both in the DAYS table and DAYSCALLS table. Therefore, the query produced another Oracle error.
ORA-00918: column ambiguously defined
To fix this, a table identifier was needed. Since OBIEE used T3386, I included this in the evaluate function.

The administration tool accepted this, and OBIEE generated correct SQL that Oracle accepted.
select next_day("T3386"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("T3386"."DATE1",'saturday')
order by C1
However, the results in Answers were not correct.

Notice that instead of getting a week ending date, we see something that looks like a month, with the sum by week presented as separate rows within the month. Even though the function next_day returns a date, OBIEE treated it as text.

To fix this, I added AS DATE in the formula to declare that the returned value was a date.

After I refreshed metadata on the presentation server, the query now produced the desired results.

Looking at column properties, everything seemed correct. The column was being treated as a date.

But look what happened – see the X axis -- when I graphed this.  The X-axis showed question marks instead of week ending dates.

After adding the graph, I examined the column properties.

The column had reverted to text!
To get OBIEE to recognize the results of the evaluate function as a date, one solution that seemed to work was to use the Oracle to_date function along with the next_day function.
Now the graph showed week ending dates on the x-axis.

Varchar2 greater than 4000 in OBIEE

I came across one requirement where one column having approximately 7000 char length when we trying to convert that CLOB field into varchar.But as we know oracle has limitation on varchar2 length upto 4000 chars only.

So the workaround I have approached is:

Split the 7000 char clob column into two varchar columns in RPD using DBMS_LOB.substr() function

Syntax for DBMS_LOB.substr() is:

DBMS_LOB.substr(<table.columnname>,<length of characters>,<start position>)

Column1: This brings first 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,1)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Column2:  This brings Next 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,3501)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Now in Answers drag these two columns into criteria and one more column:

In 3^rd column-> go to column properties-> data format and select Treat Text As – Custom Text Format.

And in Custom Text Format give the text liek this:@[html]@1@2

Where:

@1 and @2 are column positions in answers criteria.

In this case BI server doesn’t include the 3^rd column in query to DB as we are Camouflage the concatenation  of columns based on browser capabilities i.e. using HTML which will show the exactly how we do string concatenation.

Special characters in EVALUATE-wrapped MDX

Something I ran into recently is a little bug in the way OBIEE 10.1.3.4.0 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:

With
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)'

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

-------------------- Query Status: Query Failed: Essbase Error: UnknownMember 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.

Use of Evaluate function for Analytics Function (Oracle)

In those rare instances, there is a necessity to utilize the EVALUATE function within OBIEE.  Most cases the use is to utilize a database function that is not available, yet, by OBIEE; but, is available within the database reporting from.  For example, an analytic function within Oracle.  Use of the EVALUATE function will allow to function ship this through the physical sql.  There is not much in Oracle’s documentation that goes over this, whether it be EVALUATE, EVALUATE_AGGR or particularly EVALUATE_PREDICATE.

So playing around with this functionality for an associate the other day, I did notice something quite interesting.  When using the EVALUATE function on an Oracle Analytic function and using physical tables and columns for the bind params you will see something like this in the physical SQL:

State: HY000. Code: 388. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 30483, message: ORA-30483: window functions are not allowed here at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

The physical sql will look something like this:

select distinct D1.c3 as c1,

D1.c1 – D1.c2 as c2

from

(select sum(case  when T1595.METRIC_CODE = ‘ACTUAL’ then T1558.METRIC_VALUE end ) as c1,

sum(case  when T1595.METRIC_CODE = ‘BUDGET’ then T1558.METRIC_VALUE end ) as c2,

cast(ROW_NUMBER() OVER (PARTITION BY T1558.METRIC_VALUE ORDER BY T1558. METRIC_VALUE asc) as  DOUBLE PRECISION  ) as c3

from

ZFCT T1558  ,

ZMETRIC_DIM T1595

where  ( T1558.METRIC_WID = T1595.WID and (T1595.METRIC_CODE in (‘ACTUAL’, ‘BUDGET’)) )

group by cast(ROW_NUMBER() OVER (PARTITION BY T1558.METRIC_VALUE ORDER BY T1558. METRIC_VALUE asc) as  DOUBLE PRECISION  )

) D1

order by c1

As you can see the analytic function, or window function, is within the inner query and part of the group by.  This makes sense, since this is not an aggregate function.  But these functions are not allowed here.  So what to do, in order to get this analytic function to be called after the aggregations, aka in the outer query!?  Well create the logical column expression to “Use Existing logical columns as the source”!  But this will only work if you reference a metric logical column or a metric column with an aggregation rule applied, or you will get the same error and physical sql created.  Now by referencing a logical column which has an aggregation rule set will result in the following physical query:

select distinct cast(ROW_NUMBER() OVER (PARTITION BY D1.c1 - D1.c2 ORDER BY D1.c1 - D1.c2 asc) as  DOUBLE PRECISION  ) as c1,

     D1.c1 - D1.c2 as c2

from 

     (select sum(case  when T1595.METRIC_CODE = 'ACTUAL' then T1558.METRIC_VALUE end ) as c1,

               sum(case  when T1595.METRIC_CODE = 'BUDGET' then T1558.METRIC_VALUE end ) as c2

          from 

               ZFCT T1558 ,

               ZMETRIC_DIM T1595 

          where  ( T1558.METRIC_WID = T1595.WID and (T1595.METRIC_CODE in ('ACTUAL', 'BUDGET')) ) 

     ) D1

order by c1

Since we are referencing a logical column with an aggregation rule set, it makes sense it is created this way.  The aggregation needs to be applied first, since you specified this aggregation column as a bind param.  Then the EVALUATE can be performed afterwards or in our case within the outer query which is what we want.

Lesson learned, when using advanced functionality or for that matter anything look at the physical query created and make sure that is what you want and intended when working with RPD metadata.