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.
Click on Advanced and copy the query under “Issue SQL“
Now click on Administration
If Administration is not available then BI Admin privilege should be added to get access.
Now click on “Issue SQL” under Maintenance and Troubleshooting
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).
Click “Issue SQL” now.
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.
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.
