You are currently viewing Troubleshoot OTBI by identifying Physical SQL Query behind it
Troubleshoot OTBI by identifying Physical SQL Query behind it

In this article we will look into how to find the underlying SQL Query which runs when we run OTBI Report. It will be helpful to debug any issues with OTBI analytics.

First navigate to BI Catalog and click on edit for the OTBI Analytics for which we need to analyze the issue.

image - Troubleshoot OTBI by identifying Physical SQL Query behind it

Click on Advanced and copy the query under “Issue SQL

image 1 - Troubleshoot OTBI by identifying Physical SQL Query behind it

Now click on Administration

image 2 - Troubleshoot OTBI by identifying Physical SQL Query behind it

If Administration is not available then BI Admin privilege should be added to get access.

Now click on “Issue SQL” under Maintenance and Troubleshooting

image 3 - Troubleshoot OTBI by identifying Physical SQL Query behind it

Paste the SQL statement in to the box and set “Oracle BI Server Logging Level”=2 to obtain the physical SQL (SQL sent by
OBIEE Server to the database/datasource).

image 6 - Troubleshoot OTBI by identifying Physical SQL Query behind it

Click “Issue SQL” now.

image 7 - Troubleshoot OTBI by identifying Physical SQL Query behind it

Click on View Log, it will open up the log file in browser. This log file is called nqquery.log and this log needs to be provided to Oracle as part of SR if you raise an issue with OTBI Analytics.

Now copy it and put it on Notepad++ or Textpad.

Now search for “Sending query to database named HCM_OLTP” and you will find the query below that text.. Query will start with “WITH” and end with 75001 which is the number of rows that will be retrieved.

image 8 1024x350 - Troubleshoot OTBI by identifying Physical SQL Query behind it

Now, you can run this query in the Data Model and diagnose the issue.

Hope this helps you when you have issues with OTBI Analytics.