Troubleshoot OTBI by identifying Physical SQL Query behind it
Troubleshoot OTBI by identifying SQL Query behind it

 391 total views

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.

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

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.

Leave a Reply