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:
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.
I needed to bounce my BIServer service
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
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.
