Wednesday, 13 November 2013

Tracking the running reports in OBIEE 11g

I faced a rather strange but relatively simple requirement a few days back. The client had employed a resource to refresh the sessions page and capture the running queries from it and mail it to a team that dealt with performance tuning. This is certainly not the most efficient resource management.

It is strange that OBIEE has an elaborate mechanism of Usage Tracking but this system does not capture the running analysis. It captures the info only after the queries have fetched the desired results and not while they are still running.

This post talks about devising a programmatic way of capturing the info about running analysis/reports in OBIEE 11g.
The process is plain and simple. We first have to capture the info about the running tasks and then parse it.

Now, capturing and parsing of info can be done using any of the programming languages.

Follow the following steps if you wish to use PL/SQL to capture info

1. Create an ACL and grant connect privileges to your OBIEE server.

Check this for the creation of ACL:

2. Write the code to fire http request from PL/SQL and store the response.
Check the following article to fire http request from PL/SQL

The destination URL for firing the request will be: http://<obiee_ip>:<obiee_port>/analytics/saw.dll?Sessions&NQUser=<obiee_user>&NQPassword=<obiee_pwd>

This is a close relative of OBIEE goURL. However, in this case, we are not calling any OBIEE analysis. We are invoking the 'Manage Sessions' page of OBIEE.

3. The result of the above invocation can be stored in a CLOB. The result will have info about all the active OBIEE sessions. We can parse the HTML in the CLOB to get the info about the running analysis.


We can also invoke http://<obiee_ip>:<obiee_port>/analytics/saw.dll?Sessions&NQUser=<obiee_user>&NQPassword=<obiee_pwd> from JAVA and do the necessary parsing in JAVA.

The following are a few API for HTML parsing in JAVA

Let me know if you need more info in this..


kris said...

Hi Vishal, I have the same requirement and neet your help. Can you please elaborate the process to create an ACL and PL/SQL to get this job done?

Thanks & Regards,

Vishal Pathak said...

Links to documents explain the process