Many a times developers face difficulty to find Fusion ERP table query. This approach can be used to identify underlying Fusion physical query and table detail. OTBI provides easy way of drag and drop subject area columns to create report / analysis which is based on BI server logical query. Below steps explain how one can get physical query based on the BI logical query.
- Create OTBI Analysis using Subject Area
- Run the analysis from Results tab
- Go to Advanced tab
- Copy the content from SQL Issued section, select the logical SQL after removing initial Set command.
- Navigate to BI Administration area from top right menu link. If this is not visible then add BI Admin privilege to the user.
- Navigate to Issue SQL section under Maintenance and Troubleshooting
- Copy the logical SQL from the earlier step in the input box. Select log level as “2” and click Issue SQL.
- Click on View Log. This will show log detail containing physical SQL as well. Search for text “Sending query to database”. Related query will be starting with “WITH” Clause and end with 75001 (number of rows returned).
- This query can be used to query database directly using BI report. Create Data Model and run the query to get the same output as was in OTBI.
