Fixing “A REPORT totalling function must be in a leaf query block. (HY000)” error in obiee

today one question (http://forums.oracle.com/forums/thread.jspa?threadID=941347) in OTN, how to fix the following error.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. NQODBC SQL_STATE: HY000 nQSError: 10058 A general error has occurred. nQSError: 42038 A REPORT totalling function must be in a leaf query block. (HY000)

Cause: A Report totalling function, e.g., REPORT_SUM or REPORT_MAX, is included in a non-leaf query block. This is not allowed.

Remedy is: De-select the option Report-Based Total (when applicable) for all grand totals or subtotals you have applied.

Oracle BI EE 10.1.3.3/2 – Top 10 Common Errors

Now that BI EE is gaining traction among a lot of users, i thought i would document a list of most common errors that users might encounter while using OBI EE. I will also be giving in the possible workarounds/solutions possible for these errors. Feel free to chip in with the error that you got and the solution which you used in the comments section.

1.   Failed to load the DLL D:\Oracle\OracleBI\server\Bin\nqsdbgatewayoci10g.dll. Check if ‘Oracle OCI 10G’ database client is installed.

         There can be many variations of the above error. But what this error essentially means is that BI Server is not able to identify the Oracle OCI drivers. So, first thing to check is whether you have installed Oracle client or database on the same machine as the BI Server. If you have these installed already but still getting this error, then if you are on windows check the Path variable. Check my blog entry here to know what your path should have. If you are Linux/Solaris check whether you have ORACLE_HOME, TNS_ADMIN, LD_LIBRARY_PATH properly set. For AIX, check whether ORACLE_HOME, TNS_ADMIN, LIBPATH are properly set. This should get you started and hopefully solve it. Of course there could be many other reasons, but the common reasons are that the environment variables are not properly set.

2.   Unable to start the BI Scheduler Service.

      For this one would have to go through a list of configuration steps. Scheduler is not configured by default since it requires a database to run the schedules on. Check my blog entry here.

3.   Unable to save an ibot. Errors out saying “Authentication Failed”.

      The most probable cause for this is that cryptotools utility was not run in the Scheduler configuration step. Check the 7th and 8th step of my blog entry here

4.   Errors while using Writeback option.

      a.   Template not found.

            This error occurs if the XML template that you have created is not identified by BI Server. Place the Templates in {OracleBI}/web/msgdb/custommessages folder.

      b.   Errors while doing the writeback.

            Check this forum entry here. Try all the options that are mentioned here. Always use OCI drivers for updating/inserting back to the database.

      For detailed step by step instructions, check my earlier blog entry here

5.   Unable to view the charts in BI EE.

      There can be a couple of reasons why this could happen. The display of charts depend on the Java Host service. Try starting that service. If it still does not work, then it could be so that you do not have a flash plugin installed. Check whether the other chart types like SVG, PNG are working. To set up SVG or PNG charts use my blog entry here

6.   Unable to start/stop the BI Server/Presentation Services/ Java host.

      If you are unable to stop/start the services, try starting them from command line. Use the following commands to start/stop the services from command line.

      net start/stop “Oracle BI Server” – For BI Server
      net start/stop “sawsvc” – For presentation Services
      net start/stop “sawjavahostsvc” – For Java Host
      net start/stop “Oracle BI Scheduler” – For scheduler

7.   While setting up external authentication, one might get error messages like init block not initialized.

      One of the possible reasons, if you think everything has been properly setup, is the overlap of users between the repository and the external directory. Remove the overlapping users from the RPD and test it out. For more info on how to setup LDAP/OID authentication check my blog entryhere and here

8.   While using Evaluate function, common errors like “Union of non-compatible types. (HY000)”.

      I believe this is a bug in the Evaluate function implementation. One must cast all the arguments to the same data type before passing them as arguments.

9.   Unable to create a new style by following the documentation.

      Again, you have not done anything wrong. I believe there is a documentation bug on this. Check my blog entries here and here

10.   Unable to view/change the images in Answers Title view using FMAP.

      This is again a common problem due to a documentation bug. Try using my blog entry here. One would have to copy the images to 2 folders instead on 1 as given in the documentation.

There could be many other reasons why you might get these errors. But the above list would give you an idea of where to start looking if you are new to OBI EE. Feel free to share your thoughts here.

Tracking Crashes Memory Heap Errors For OBIEE Processes

For capturing process characteristics when the server hangs or terminates unexpectedly, we can use User Mode Process Dumper. The User Mode Process Dumper (userdump) dumps any running Win32 processes memory image (including system processes such as csrss.exe, winlogon.exe, services.exe, etc) on the fly, without attaching a debugger, or terminating target processes. Generated dump file can be analyzed or debugged by using the standard debugging tools.

Userdump: http://www.microsoft.com/downloads/details.aspx?FamilyID=E089CA41-6A87-40C8-BF69-28AC08570B7E&displaylang=en

Instructions for creating Dump

http://support.microsoft.com/default.aspx?scid=kb;en-us;241215

Analysis of the dump can be done by the debugging tools for Windows

Debugging Tools for Windows:

32 bit: http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx

64 bit: http://www.microsoft.com/whdc/devtools/debugging/install64bit.mspx

For tracking memory log issues there are # of utilities

Perfmon is the most comprehensive

Memlog is another utility that gives memory log for a process and writes to a file

Sysinternals has memory utilities

Blat is a program to set emails automatically on an event

Also for Windows 32 bit /3G switch can be set using the instructions in

http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

nQSError: 61013 Can not save changes to the repository

If you come across following error don’t be panic.

warning: can not save changes to the repository because,

[nQSError: 61013] File error: path= C:\OracleBI\server\Repository\paint.rpd , code=31: There has been a sharing violation.

May be its because of, you have started working on repository (offline mode) then started BI Server, after server services start, if you try to save repository you will see above message.

If you want to save all your changes just stop BI Services, and then save repository it will save all changes made to repository.

Best practices: Auto-Notification on Dashboard Errors

Many people (at least myself) are ignorant, once development complete and shipped to production, then developers will be least bothered about how dashboards are working.

Even though DEV/QA people thoroughly verified for data and functionality, some times end user can perceive error on dash board, because of QA slippage, resources unavailability or wrong combination of objects(listed at end).

To avoid these kind-of-errors in future, schedule an iBot with Error Track report every day or every hour (as you needed) and fix them in advance before end user asks or notifies about the error.

Here is a sample error track report

Error Track -iBot scheduled every day.

Here is a list of common errors, that end users can perceive at any time.

[nQSError: 14026] Unable to navigate requested expression: ToDate("xyz").

[nQSError: 14026] Unable to navigate requested expression: XYZ.Column. Please fix the metadata consistency warnings.

[nQSError: 15018] Incorrectly defined logical table source (for fact table XYZ) does not contain mapping for [XY Column].

[nQSError: 16001] ODBC error state: IM002 code: 0 message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

[nQSError: 17001] Oracle Error code: 12541, message: ORA-12541: TNS:no listener

[nQSError: 17001] Oracle Error code: 28000, message: ORA-28000: the account is locked

[nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "XYZ.Column": invalid identifier

[nQSError: 17010] SQL statement preparation failed."

[nQSError: 17014] Could not connect to Oracle database."

[nQSError: 22024] A comparison is being carried out between non-compatible types.

[nQSError: 22036] Level in TODATE function (Quarter) must be a rollup of (or equivalent to) the measure level (Year).

[nQSError: 22047] The Dimension used in TODATE function must be referenced in the query.

[nQSError: 23006] The session variable, NQ_SESSION.XYZ, has no value definition.

[nQSError: 27002] Near < 'OR' > or <> 'or' <>: Syntax error [nQSError: 26012] .

[nQSError: 27005] Unresolved column: "XYZ"

[nQSError: 27007] Ambiguous column: "XYZ"

[nQSError: 27015] XYZ: column in GROUPBY clause not in the projection list.

[nQSError: 27020] The repository variable "XYZ" does not exist.

[nQSError: 42020] GROUP BY clause, blah blah, is inconsistent with the projected columns in

[nQSError: 46066] Operation cancelled.

[nQSError: 52061] Cache entry, C:\OracleBIData\cache\NQS_xyz, is removed unexpectedly, could not finish ConstructHeader() operation. Restart server to resolve the issue.

[nQSError: 59001] Binary Logical operation is not permitted on DOUBLE PRECISION, VARBINARY operand(s).

[nQSError: 59001] Subtract operation is not permitted on TIMESTAMP, INTEGER operands.

--Hope it helped you

Obiee Error: Id 31 not Found

Obiee Error: Id 31 not Found

Error: Id XX not Found

Some times when we try to select some function from "Edit Column Formula" this kind error will pops as "Id not found ".

Solution:

1)Say ok.

2)Cacel Insert Function.

3)Save your request(optional)

4)Close the browser and re-open the request.

Now column will allow you to insert functions.

Error : [nQSError: 46036] Internal Assertion

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46036] Internal Assertion: Condition listNode.GetGroupBy() == NULL, file .\Src\SQOIGeneratorBuiltIn.cpp, line 1497. (HY000)

As per my understanding this error happens due to corrpution in report file on disk.

Solution:

Try to change something in report and resave the report, this should solve problem if not, try to get same report from previous release,code base or from backup using Catalog Manager bring the report to new development by Export/Import report.

In this way it worked for me.

Hope it may help you.

error “access denied for user to path

I just had a terrible catalog security situation, and while looking for solution stumbled into this bug. I think it’s important, because the error message is confusing and it’s really hard to troubleshoot this sort of problem.

Catalog Manager copy/paste removes correct permissions on Users subfolders, causes error “access denied for user to path..” at OBI login

Applies to:
Business Intelligence Server Administrator – Version: 10.1.3.2 to 10.1.3.4.0 [1900] – Release: 10g to 10g

In OBIEE 10.1.3.4, users are copied from one web catalog A (TEST environment) to another web catalog B (PRODUCTION Environment), using the Catalog Manager. After loading the new web catalog B, users are unable to login into OBI and see the following error:

access denied for user to path /users/…/_portal/dashboard layout.
Error Details
Error Codes: O9XNZMXB

Cause

In the Catalog Manager, when copying users in the catalog manager, permissions are not copied. The users are part of the system folder (i.e Catalog Manager > Users > Properties > Owner Account = System Account) , which is why Catalog Manager does not transfer the permissions.

The behavior was reproduced with 2 copies of Paint web catalog A and B.
Note: Before copying from Web Catalog A, here are the privileges for

a) Users folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

b) Users > Paint Folder – Owner – System Account
Explicit Permission – Paint (change/delete)

c). Users > Paint > _portal folder – Owner – paint
Explicit Permission – paint (change/delete)

After pasting user folder in web catalog B, here are the permissions:
Note how the properties and permissions changed after pasting the user to the following:

a) Users > Paint Folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

b). Users > Paint Folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

Solution
The following has been raised to address a product enhancement request:

BUG 8316638 COPY AND PASTE USERS IN CATALOG MANAGER DOES NOT COPY PERMISSIONS

The current workarounds are:

a). Manually change the permissions on the user_id, _portal and other subfolders in the target web catalog so that they are the same as the source web catalog.

b). Use SAWREPA utility to promote the changes from TEST to PRODUCTION instead. The process works online, so you do not lose any up-time, and it should promote the users permissions correctly too.

Information about SAWREPA is documented in the following:

Oracle Business Intelligence Presentation Services Administration Guide > Administering the Oracle BI Presentation Catalog > Replicating Presentation Catalogs

Please note that SAWREPA requires that both the PROD and TEST webcatalog were originally developed from the same web catalog. If the PROD webcatalog was created from scratch, it could cause problems with SAWREPA since it relies upon common attributes in both catalogs.

OBIEE Error messages

One of the things that I’d like Oracle to improve is the Analytics error messages (both in the Repository and in the Answers). Sometimes, they’re just too broad – for example – Internal Assertion Error 46036 – which could mean anything: from corrupted RPD after transitioning or upgrading to logical levels not being set correctly in the hierarchy levels. Next set of errors that people have problems with is the installation errors  – usually those aren’t very descriptive as well, however, with time you sort of learn to distinguish those. Third problem is the debugging .cpp errors appearing in the RPD or Answers (such as – which should have been caught by system and a human-error message to appear – such as “.cpp, line 506
This application has requested to terminate the runtime in an unusual way”. Overall, troubleshooting OBIEE is a logical step-by-step process well-described in their Oracle Business Intelligence Troubleshooting Guide, however, by making error messages human-friendlier -they’d make OBIEE consultants job easier.