BIP BIServer Query Debug

With some help from Bryan, I have uncovered a way of being able to debug or at least log what BIServer is doing when BIP sends it a query request.
This is not for those of you querying the database directly but if you are using the BIServer and its datamodel to fetch data for a BIP report. If you have written or used the query builder against BIServer and when you run the report it chokes with a cryptic message, that you have no clue about, read on.

When BIP runs a piece of BIServer logical SQL to fetch data. It does not appear to validate it, it just passes it through, so what is BIServer doing on its end? As you may know, you are not writing regular physical sql its actually logical sql e.g.

select	 Jobs."Job Title" as "Job Title",
Employees."Last Name" as "Last Name",
Employees.Salary as Salary,
Locations."Department Name" as "Department Name",
Locations."Country Name" as "Country Name",
Locations."Region Name" as "Region Name"
from HR.Locations Locations,
HR.Employees Employees,
HR.Jobs Jobs

The tables might not even be a physical tables, we don't care, that's what the BIServer and its model are for. You have put all the effort into building the model, just go get me the data from where ever it might be.
The BIServer takes the logical sql and uses its vast brain to work out what the physical SQL is, executes it and passes the result back to BIP.

select distinct T32556.JOB_TITLE as c1,
T32543.LAST_NAME as c2,
T32543.SALARY as c3,
T32537.DEPARTMENT_NAME as c4,
T32532.COUNTRY_NAME as c5,
T32577.REGION_NAME as c6
from
JOBS T32556,
REGIONS T32577,
COUNTRIES T32532,
LOCATIONS T32569,
DEPARTMENTS T32537,
EMPLOYEES T32543
where ( T32532.COUNTRY_ID = T32569.COUNTRY_ID
and T32532.REGION_ID = T32577.REGION_ID
and T32537.DEPARTMENT_ID = T32543.DEPARTMENT_ID
and T32537.LOCATION_ID = T32569.LOCATION_ID
and T32543.JOB_ID = T32556.JOB_ID )

Not a very tough example I know but you get the idea.
How do I know what the BIServer is up to? How can I find out what the issue might be if BIServer chokes on my query?

There are a couple of steps:

  1. In the Administrator tool you need to set the logging level for the Administrator user to something greater than the default '0'. '7' is going to give you the max. Just remember to take it back down after you have finished the debug.

  2. I needed to bounce my BIServer service

  3. Now here's the secret sauce. Prefix the following to your BIP query
    set variable LOGLEVEL = 7;

    Set the log level to that you have in the admin tool

  4. Now run your BIP report. With the prefix in place; BIServer will write to the NQQuery.log file. This is located in the ./OracleBI/server/Log directory. In there you are going to find the complete process the BIServer has gone through to try and get the data back for you

A quick note, if the BIServer can, its going to hit that great BIEE cache to get your data and you may not see the full log. IF this is the case. Get inot hte Administration page (via the browser login) and clear out your BIP report cursor. Then re-run.

This will hopefully help out if you are trying to debug that annoying BIP report that will not run or is getting some strange data.

Don't forget to turn that logging level back down once you are done. This will avoid the DBA screaming at you for sucking up all the disk space on the system.

Comments:

Hi Tim, I am working on adding OMR marks to my invoice. But my invoice has @section for ./G_INVOICE element. So my variables are getting reset after every invoice. Is there a way to define global variables? Thank you, Vinodh

Posted by Vinodh on March 23, 2010 at 11:00 AM MDT #

BIP does some parsing before sending the logical sql to BIEE. Recall you can insert "macros" {$if $} within the query and I do not think BIEE parses this before issuing the physical sql to the final relational db.

Posted by Jorge Anicama on March 25, 2010 at 01:14 AM MDT #

I think that the points you have raised are very interesting and I would be interested to hear more. The third commenter has a very good point also. Any way good blog and look forward to hearing more in the future. Regards John Degary http://medcall.com.au

Posted by Medcall on April 22, 2010 at 02:38 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today