Troubleshoot OTBI by identifying Physical SQL Query behind it
Troubleshoot OTBI by identifying Physical SQL Query behind it
Troubleshoot OTBI by identifying Physical SQL Query behind it
Total Visits: 12
Todays Visits: 3

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.

Troubleshoot OTBI by identifying Physical SQL Query behind it

Click on Advanced and copy the query under “Issue SQL

Troubleshoot OTBI by identifying Physical SQL Query behind it

Now click on Administration

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

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).

Troubleshoot OTBI by identifying Physical SQL Query behind it

Click “Issue SQL” now.

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.

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.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

Sricharan Monigari
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.
Sricharan Monigari
Sricharan Monigari

Leave a Reply