OBIEE Using the EM NQQuery log

After I did this article http://obiee101.blogspot.com/2011/07/obiee11-how-to-use-log.html on how to read the NQquery.log with notepad I got a couple of emails telling me that the log can also be found in the enterprise manager.

First of all I know but I don’t like it because of the time it takes to refresh every time. (My laptop isn’t that fast).

But here is how you use it:

Open the enterprise manager and go to the business intelligence core application:

Click on the diagnostics tab and open the Log Messages tab

Open the server log:

You might have to wait a bit:

Check the trace box

and add “Sending query to database” in the Message contains box

press the search button

Now you can plough trough the log:

Till Next Time

OBIEE11 How to use the log

If you come from a 10g background you might be missing the NQquery.log. Don’t worry it’s still there. In 11.1.1.5 you will find it in <<OBIEE_HOME>>\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

Along with the NQserver log.

Now how do I set the loglevel? Two options:

- Set it for everbody. (!A No No on production machine’s!)

In the Administration tool goto TOOLS > Options

- Set it for a individual user:

You can’t do it in the Administration tool, since the users are no longer there.

You have to do it in the report under investigation, go to the advanced tab of your report:

Scroll down to the prefix part:

and enter:

SET VARIABLE LOGLEVEL=2;

Don’t forget the semicolon!

Open the NQquery.log in a text editor (fi Notepad++)

Run your report and reload the log in the text editor:

Scroll down to the bottom to check your sql:

Till Next Time

OBIEE Optimizing NQQuery.log for debugging

When building and debugging OBIEE repository’s and reports you will often go back to the NQQuery.log. Depending on the security setting of the user the actual query send to the database will be logged here.

Because the log can grow very quickly over time it often means having to scroll to the end of the log to find the information needed.

By default the maximum size for the NQQuery.log is 10MB. This setting is found in the NQSConfig.ini under USER_LOG_FILE_SIZE:

If the log goes beyond this size OBIEE will rename it as “NQQuery.log.old” after it has finished the last request.

In a development environment you might consider setting this value to 50KB (or even as low as 10KB).
That way the log only contains the very last actions you are interested in.
Be aware that if the new log goes beyond the USER_LOG_FILE_SIZE the old “NQQuery.log.old” will be overwritten.

Till Next Time