How to Examine the SQL in a BI Answers Report

Introduction

In a standalone OBIEE environment, or within the Analytics Reporting home page of Fusion Applications, there may be occasions where it would be useful to find the SQL being generated against the underlying database by the BI Presentation Server.  This post presents one method to locate this SQL.

Main Article

The first step is to make sure logging is turned on for the user that will be running the report.

Using the BI Administration tool, open the RPD repository file.  (Note:  For an explanation on how to install the BI Administration tool see this post).  Since this is a minor change, you may chose to do this in ‘on-line’ mode to avoid having to upload the new RPD file to the BI Server.

Go to ‘Manage’ and then ‘Identity’:

 

1

 

Select ‘Action’ and ‘Set online user filter’ and enter the name of the user who will be running the report.  You should consider using an administrative or test user for this.  If you set logging to be verbose for a regular report user, then each time that user runs a report, additional logging will be captured which could impact performance.

Double click on the user when listed in the search results.  If you are making the change in on-line mode, check out the user as requested.

Change the logging level.  The higher the number, the more detailed the logging will be.  Oracle recommends for administrators you use 1 or 2, as anything higher than that is only necessary for working with support or triaging an issue.  In my example I’m setting it to 2.

 

Mac OS X 10.7 64-bit

 

Next, commit and save the changes to the RPD.  If you made the changes to an off-line RPD, go to the Enterprise Console and upload the latest version.

Whichever method you used, you should re-start the BI Server through Enterprise Manager to make sure the log level is changed in the reporting portal.

 

4-1

 

Now that logging is set up, log into BI Answers and run the report in question.

Once it has been run, return to the Home Page and select ‘Administration’ and then ‘Manage Sessions’.

 

5-1

 

Locate the recently run report and select the ‘View Log’ – this will take you into the detailed log.

 

Mac OS X 10.7 64-bit-5

Note – if you hadn’t set up the logging level in the earlier step, and the user is set to the default log level of 0, then no log will be available.

At the top of this log you will find the internal SQL that the BI Presentation Server is using.  This is using the naming conventions from the Presentation Layer, as defined in the RPD, so this SQL will not work directly against your database.

SET VARIABLE QUERY_SRC_CD=’Report’;SELECT
0 s_0,
“Example Presentation Subject Area”.”Customer Details”.”Assigned to” s_1,
“Example Presentation Subject Area”.”Customer Details”.”Created by” s_2,
“Example Presentation Subject Area”.”Customer Details”.”Customer ID” s_3,
“Example Presentation Subject Area”.”Customer Details”.”Customer Name” s_4,
“Example Presentation Subject Area”.”Customer Details”.”Issue Identifier” s_5
FROM “Example Presentation Subject Area”
ORDER BY 1, 6 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST

 

Search lower into the log for details of the query itself that was sent to the database.  This will be after a string format similar to the example below, with details of the user who ran the report and the connection pool name from the RPD that the report is based on.

[username: USER_WHO_RAN_REPORT] ——————– Sending query to database named DATABASE_NAME, connection pool named CONNECTION_POOL

 

Just beneath that, you’ll find the actual SQL that was sent to the database.  You’ll notice the way the BI Presentation Server created a sub-select statement to get the data, and then a second select to group and order that.  I’ve highlighted the sub-select below in red.

SAWITH0 AS (select T4929.ISSUE_ID as c1,
T4929.NEW_CUSTOMER as c2,
T4929.JIRA_ID as c3,
T4929.CREATED_BY as c4,
T4929.ASSIGNED_TO as c5
from
ORA_NEW_CUSTOMERS T4929)
select distinct 0 as c1,
D1.c5 as c2,
D1.c4 as c3,
D1.c3 as c4,
D1.c2 as c5,
D1.c1 as c6
from
SAWITH0 D1
order by c6, c4, c5, c2, c3

 

If the SQL is simple – you can take that sub-select, and run it directly against the database.

If you wish to maintain the grouping and ordering, then you can quickly re-arrange the SQL code by taking the sub-select – in my example called ‘SAWITH0′ and cutting and pasting that to replace the alias ‘SAWITH0′ in the second select statement.

In this example – the re-arranged SQL would look like this and can be run directly against the source database:

select distinct 0 as c1,
D1.c5 as c2,
D1.c4 as c3,
D1.c3 as c4,
D1.c2 as c5,
D1.c1 as c6
from
(select T4929.NEW_CUSTOMER as c1,
T4929.JIRA_ID as c2,
T4929.ISSUE_ID as c3,
T4929.CREATED_BY as c4,
T4929.ASSIGNED_TO as c5
from
ORA_NEW_CUSTOMERS T4929) D1
order by c4, c5, c6, c3, c2

 

Summary

This post presented the steps that can be used to obtain the SQL for an Oracle BI Answers report.

Add Your Comment