OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service ?

Obiee Ceim

Quick Way

A quickway to see the content of the Nqquery.log to grab the SQL information is to follow this quick instructions.

Click Settings > Administration > Manage Sessions > View Log to view the query log and examine the Physical SQL generated by the request.

To be able to see the log, you must set the log level at least with the level 2. See this article : OBIEE 10G/11G - Setting a Query Logging Level

Detail Way

Logged in the Oracle Interactive Dashboard :

  • Click Settings > Administration to open the Oracle BI Presentation Services Administration Window.

Obiee Setting Admnistration

  • Click the Manage Sessions link to open the Session Management window.

Obiee Admin Manage Sessions

  • In the Session Management window, under Cursor Cache, click the View Log link for the last entry.
  • The log displays the last query. The log file should look similar to the picture.

Obiee Query Log

Locate :

  • the SQL Request section. This section contains the logical SQL issued from Answers.
  • the General Query Info section, just below the SQL Request section. This section identifies the repository, subject area, and presentation catalog from which the query was run.
  • the Sending query to database named SH section, just below the General Query Info section. This section identifies the physical data source to which Oracle BI Server is connecting and the physical sql that was generated.

The rest of the file contains information such as query status, number of rows returned, and so forth.

Security

To be able to access to the Session Management, the user must have the privileges :

  • Access to Administration
  • Manage Session because Presentation Service will call this saw url saw.dll?ManageSessions&Action=ViewLog&ID=logId

    in order to see the log.

Support

No Log Found

If when clicking on the link “View log”, you obtain this message :

No Log Found

Obiee Bi Server Nolog Found

  • set the log level for the user to a level greater than 0.
  • and verify that it has an integer as datatype

Log Could Not Be Retrieved

Error
Log Could Not Be Retrieved
Odbc driver returned an error (SQLExecDirectW).
  Error Details
Error Codes: OPR4ONWY:U9IM8TAC
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43100] Log Viewer process reported error 107:@Û.à�. (HY000)

When you click on the log link, you are calling the log viewer utility. Verify that you can call it from the command line. In my case, the file permissions was not good.

It was:

[oracle@Exalytics-01 bin]$ ls -l|grep nqlog
-rw-r--r-- 1 oracle oinstall  1671920 Jan 24 16:41 nqlogviewer

As you can see the script as no permission to execute. After giving the right to execute on the owner (0744), the problem disappeared.

[oracle@Exalytics-01 bin]$ ls -l|grep nqlog
-rwxr--r-- 1 oracle oinstall  1671920 Jan 24 16:41 nqlogviewer

Reference





Discover More
Obiee Query Log
OBIEE - Physical SQL

The physical SQL is the SQL that send the BI Server to the data sources in order to retrieve data. This SQL is most of the time generated by the query compiler during the query processing of the logical...
Obi Edition
OBIEE - The Log Viewer (nQLogViewer)

Use the Oracle BI log viewer utility nQLogViewer (or a text editor) to view the query log. Each entry in the query log is tagged with the user ID of the user who issued the query, the session ID of the...
Obi Edition
OBIEE 10G/11G - Nqquery.log (Query logging)

It's the log file of BI Server showing information the process of a logical SQL query. It's located in the directory: 10g: BI_ORACLE_HOME/server/Log 11g: ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1...
Obiee11g Default Logging Level
OBIEE 10G/11G - Setting a Query Logging Level

The login level is a parameter which control the number of information that you will retrieve in the log file from nothing (level 0 - no_log_found) to a lot of information (level 5). You can enable logging...



Share this page:
Follow us:
Task Runner