Tag Archives: essbase

Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error

Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

Applies to:
Business Intelligence Answers Option – Version: 10.1.3.2.1 to 10.1.3.4.1 [1900] – Release: 10g to 10g

Symptoms

When running a query in Direct Database Request in OBIEE against Essbase, the following error occurs:

ERROR
———————–

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

The query works in Answers screen but not in Direct Database Request screen.

STEPS
———————–
By following these steps the issue can be reproduced using Essbase as data source:
1. Create a report in Answers
2. Verify that the query returns results
3. Now copy the physical SQL of the above query from the query log (section ‘…sending query to database…’)
5. Copy it into the Direct Database Request SQL statement field
6. Verify that the columns in SELECT statement are displayed in the Results Column section
7. Run the results and the above error is generated

Cause

This issue has been caused by Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE”.

There is an issue with the code that checks for FROM clause in the SQL. The parsing code expects a space before and after the FROM clause.

Solution

The behavior is reproducible in the latest version of OBIEE, 10.1.3.4.1.

The recommended workaround is in the MDX  query to enter space before the “FROM” clause in the physical SQL and execute the query.

A single space before the FROM clause resulted in the following error (the error moves from line 1003 in the error described above to contrary to line 1050 in error below):

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: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1050. (HY000)

Adding two spaces in front of the FROM clause so that the FROM clause is aligned with the text in the upper line of the query, resolved the error and it was possible to run Direct Direct Database requests in OBIEE on Essbase.

Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE” is planned for resolution in the next release of OBIEE.

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

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.