OBIEE Blog

A. “Ago function returning wrong results in answer when comparing this year versus last year” This actually happened to me once – and i thought it was an incorrectly configured time dimension. You can experience this error when your “Time Series functions gives incorrect results when leap years are involved”. Oracle support suggested the following action plan :

1) Leave the logical column definition as earlier i.e. Ago(Measure, Year, 1)
2) Change the filter condition in the request to filter on year and months instead of date range e.g.
year = 2009 and month in (1,2,3,4)

B. “Value Suppression doesn’t work for Level Based Measures”. Someone had a requirement where they wanted to use level-based measures for reports accessing several fact tables in the future. To realize a prototype before starting with the necessary reports they tried to implement an easy example to test the ‘value suppression’ functionality

Unfortunately they are not able to build a nice looking report with four simple columns showing the year, # of orders (per year), quarter and # orders. Their problem is that the level based measure ‘# of order (per year)’ is shown line by line. They wanted to suppress these values and only show the value once per year. They tried to use the ‘value suppression functionality’ without success. They were able to Work around this issues by using a Pivot Table.

C. This issue in pivot table is very common –

OBIEE says “governor exceeded error”. The pivot tables and the charts might give these errors:

Error Generating Chart
An exception occurred in the cube.
Error Details
Error Codes: MOJ5XVNL:QBVC92JY
Governor limit exceeded in cube generation (Maximum data records exceeded.)

and for pivot table

View Display Error
Governor limit exceeded in cube generation (Maximum data records exceeded.)
Error Details
Error Codes: QBVC92JY

Theses messages mean that users are trying to return more rows to the pivot table then the governor will allow. One needs to adjust the following parameters until the report runs successfully.

Adjust the parameters below in instanceconfig.xml (add those tags if you don’t have them – since those values are by default) and then restart Presentation Server (sawserver)

< CubeMaxRecords >40000< /CubeMaxRecords >
< CubeMaxPopulatedCells >150000< /CubeMaxPopulatedCells >

I found an answer to something that bothered me for a while.

In setting up a Time dimension, there are requirement of the physical Tables that can be used, for e.g. Time Dimension table cannot join any physical table other than the fact table Time Dimension sources on the physical layer cannot be part of a complex join In the BM layer any logical Dimension defined as a Time Dimension cannot be part of any other logical tables . In the current 7.9.5 rpd the Date Dimension does not meet some of these requirements So if we were to designate the out of the box OBIApps rpd Date dimension as time dimension we can not have complex join to w_day_d in the physical layer. Currently out of the box OBIApps rpd has several complex join defined with the w_day_d_common alias table which is the detailed level LTC in the Date logical dimension. This issue causes error if you then try to check the time dimension flag and do a consistency check.

A comment on this from one of our Consultant as below “apps 7.9.5 was not ready to convert it to a true time dim. If you check the checkbox you will see all of the consistency errors. Part of it is due to the date fields being used in the inner joins of LTSs on other Dims and Facts which is a no-no for the Time Dim. It is easier just to create your own Time Dim that is used for the Time Series formulas. Or you could configure time series the old school way.” However the OBI 7.9.6 apps does have the Date Dimension checked as a Time Hierarchy. Also an additional response from Engineering as below

==================== The simple answer was that we didn’t use the OBIEE Time Series functions in BI Apps 7.9.5, and used them in BI Apps 7.9.6, hence configuring the Date dimension as a time dimension. In BI Apps 7.9.5, none of the new OBIEE Time Series functions were used. This was because the functionality was immature and had many bugs. These bugs were fixed in OBIEE 10.1.3.4.1 and BI Apps 7.9.6 has uptaken and used the new Time Series functions quite a bit. So for correct functionality of BI Apps 7.9.6, OBIEE 10.1.3.4.1 is a must.

This is interesting. My favorite part is “In BI Apps 7.9.5, none of the new OBIEE Time Series functions were used. This was because the functionality was immature and had many bugs. These bugs were fixed in OBIEE 10.1.3.4.1″ – soif you’re on 10.1.3.2. – you should upgrade as soon as possible (although this might mean months). I knew time dim was broken for a while – it’s just an official confirmation.

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.

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′

If you’re unable to configure proxy “Act as” feature maybe this can help you.

The customer had OID/SSO users in lowercase but had the users in the proxy table in upper case.

Also the string (‘VALUEOF(NQ_SESSION.RUNAS)’) was not in uppercase in both the proxyblock and proxylevel init blocks

Initially, on selecting ‘act as’ the following error was reported:
“This functionality has not been correctly configured by your administrator (Can’t retrieve the list of target users).”

The customer had OID/SSO users in lowercase but had the users in the proxy table in upper case. Advised the customer to add upper function to proxy related init block queries and in the XML template file queries as well. This resolved this error message and the customer was able to see the list of target users.

Then on selecting a particular user from the list of target users it failed. It gave the error message : “Not logged in”.

Since the customer had OID/SSO setup, the customer was advised to test with rpd users.

Even with rpd users the functionality still failed to work and reported the following error:
“Access for requested connection is refused. Failed to get the value of PROXY session variable”.

Finally, what seemed to resolved the problem for the customer was changing the string (‘VALUEOF(NQ_SESSION.RUNAS)’) in all uppercase in both the proxyblock and proxylevel init blocks.