OBIEE Blog

If you had problems using EVALUATE function -  please look at the following SR that I just found. Apparently, the work-around is to use CAST function.

QL Request: SET VARIABLE QUERY_SRC_CD=’Report’,SAW_SRC_PATH=’/users/administrator/last_working_day test 1′;SELECT “Task Type”.Type saw_0, “Task – Creation Date”.Date saw_1, EVALUATE(‘num_of_working_days(%1,%2)’,”Task – Creation Date”.Date,”Task – Last Updated Date”.Date) saw_2, “Task – Last Updated Date”.Date saw_3 FROM “SPS – Operation” ORDER BY saw_0, saw_1, saw_2, saw_3

The query fails and the following error occurs.

ERROR
———————–
Query Status: Query Failed: [nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

These columns are from W_DAY_D. These are type date which the function is expecting. The function returns a number.

BUSINESS IMPACT
———————–
The issue has the following business impact:
Due to this issue, this is holding up resolution of a critical issue on the delivery of the project – which plans to go live next Friday (30th October).

Cause

Comments
——–
The cause of this issue is that EVALUATE expects to return data with a data type that is the same as the data
type of the parameter.
This has been raised in : EVALUATE FUNCTION SHOULD RETURN DATA TYPE OF FUNCTION

The Bug explains that when you use this function:

EVALUATE(‘my_function(%1)’, logical_table.ROWID)

It will set the data type of the column to INT because ROWID is also INT. However my_function does not return an INT but a REAL.

============================
The above is what you are experiencing and why you get the error suggesting converting double precision to timestamp is not supported

[nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

Proposed Solution

Customer was asked to do an explicit CAST to set the datatype to the datatype of the value that will be returned by my_function.

So something like

CAST(EVALUATE(‘my_function(%1)’, logical_table.ROWID) AS FLOAT)

The above resolved the issue.

There’s an interesting discussion about IIS and OC4J on OTN- OC4J discussion

I think there’s a lot of confusion as to what is a difference between web server and web application server (apparently web server just serves static HTML (it can also server PHP/ASP pages), however, doesn’t run applications — on the other side – it’s tough to distinguish between dynamic web applications written in script languages and Java container OC4J). If you

I think that it’s clear from the discussion that OBIEE requires IIS (in Windows server) and OC4J is an quasi-optional module for BI Publisher and Scheduler.

Another thing -check your connection pool settings. According to Oracle, BI Server might crash if there’re “Several Connection Pools in the custom RPD had ‘Execute Queries Asynchronously’ enabled. This is no longer recommended in OBI 10g as it can cause the OBI EE (NQServer) Server to crash.”

This is addressed in Bug No.BUG 7634602 – ‘OBI SERVER CRASH – 3-818156441′

Symptoms
After migration from 10.1.3.2.1 to 10.1.3.4 Prompt selection window not rendering correctly when a Multi Select Prompt is created for OBI EE Dashboard Prompts.

In Answers, On clicking to go to the selector screen for the Multi-Select Prompt, in it we see on the right:
match: begins with…. the right side is empty. Also the left side (‘Selected’) is empty.
So, No values display in the list.

Details:
- Customer has tried entering a value in the textbox and clicking go, nothing happens.
- The issue does not happen on customers 10.1.3.2.1 instance. It only happens on their 10.1.3.4 instance which they have migrated from 10.1.3.2.1.
- Web Server is Apache\Tomcat 5.5
- Java version is JDK 1.5.18

Steps to reproduce:

- Log into Answers
- At top of left Answers window select ‘New Dashboard Prompt icon, & select a subject area
- from the list on the left select a dimension (eg Calendar Month Desc)
- change Control to ‘Multi-select’
- select ‘Constrain’
- change Defaults To to ‘Specific Value’
- from that list select a value
- top right of screen select the Preview icon
- next to default value, select the icon to bring up the Multi-select window.
- the issue is seen here, no values in the list.
Cause

Client migrated from 10.1.3.2 to 10.1.3.4. During the migration they replaced the ‘res’ folder with the copy from their 10.1.3.2 version and then regenerated the war file. The same war file was deployed in Tomcat.

This is the root of the problem. The ‘res’ folder should not be copied from the older version instance.
Solution

Obtain a fresh 10.1.3.4 ‘res’ folder and replace the copied 10.1.3.2.1 version with that.

I thought this might be useful for someone struggling with Essbase.

Client was on OBI 10.1.3.4.0 QF 170 and Oracle’s Hyperion Essbase 9.3.1 and running a report in Answers based on ASOSample Essbase cube which is generating an error.

In Answers Customer has created a report with a measure column (selected twice) which is being filtered with 2 different values using the filter function as below:

Add the 1st column
a. In Answers, Navigate to the Criteria Tab,
b. Select the column “Original Price”
c. On the column, click ‘edit formulae’,
d. in the edit formulae box, click the ‘Filter’ button
e. Insert a filter for the column, by selecting from the selection pane
column “Products”.”Product SKU” = ‘Boomboxes’.
.
Add the second column
Repeat the above steps a-d
In step e, Insert a filter for the column, by selecting from the selection pane column “Products”.”Product SKU” = ‘DVD’

The report can also be generated by directly entering the following SQL in Advanced Tab
select
Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘Boomboxes’ ),Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘DVD’ )
from ASOSamp

————–
When the report is run the error message that is generated is as below

Query Failed: [nQSError: 42043] An external aggregate is found in an outer query

The MDX generated is

With
set [Products5] as ‘{[Products].[Boomboxes], [Products].[DVD]}’
set [Axis1Set] as ‘crossjoin ({[Age3]},crossjoin ({[Attribute Calculations2]},crossjoin ({[Geography5]},crossjoin ({[Income Level2]},crossjoin ({[Payment Type2]},crossjoin ({[Products6]},crossjoin ({[Promotions2]},crossjoin ({[Stores4]},crossjoin ({[Time5]},crossjoin ({[Transaction Type2]},{[Years2]}))))))))))’
select
{[Measures].[Original Price]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [ASOsamp.Sample]
Cause

By default in the OBI rpd, the Aggregation rule is set to “External Aggregation”. As per Engineering this needs to be change, so OBI EE knows what those rules are.

Filter By clauses are not supported by MDX (or Essbase), so OBI EE has to perform those internally. If the Aggregation rule is set to “External Aggregation”, then OBI EE doesn’t know what those rules are and hence throws this error.
Solution

It is possible to fix the error by doing the below

1. Changing the OBI Aggregation rule in both the Physical and BMM layer to the corresponding rule in Essbase, would stop the error.

2. Alternatively, using OBI Answers filter clauses would allow the BI Server to re-write the MDX to push down the filter to Essbase and may be more efficient anyway.

There was an enhancement raised within Oracle to address this matter in a future release, as the client stated that changing the aggregation rule in the rpd is not a suitable option.

When running a report using eight BETWEEN clauses or less for a report that should bring back zero rows, for example

SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
ORDER BY saw_0

the following warning is displayed:

“…

No Results
The specified criteria didn’t result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please check your Request Filters and try again. The filters currently being applied are shown below ..”

This is the correct result.

However, when an additional BETWEEN clause is added (i.e nine BETWEEN clauses in total) for example

SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
OR (Products.Brand BETWEEN ’8′ AND ’9′)
ORDER BY saw_0

the result should also return zero rows but this report returns all rows in the table.

In the query using nine BETWEEN clauses, the WHERE clause of the SQL generated does not include the BETWEEN filter conditions hence all rows in the table are returned.

It was determined to be a bug – OBI SERVER GENERATES INCORRECT SQL WHEN BETWEEN AND OR CLAUSES EXCEED EIGHT, has been raised to address this sissue.

The issue is that for the case when there are more than 8 filters, e.g 9 filters in a query, OBI Server seems to drop the filter with the result that it erroneously returns incorrect number of rows.