Monitoring Business Objects end user activity using Oracle Enterprise Manager

Recently, one of our clients upgraded their Business Objects environment to version 4. Reports are run on datamarts in the datawarehouse, but there are also older reports running directly on operational databases. These are subject to move to the dedicate datawarehouse environment eventually. From an instrumentation perspective, we couldn’t see what was going on exactly using the Oracle Enterprise Manager during the day. Especially, the question of which end user was doing what and how many resources a particular report in total would consume in the database was a real pain. Also an insight on if, and how much a particular report was used would be valuable information. The only thing we could see is the name of the originating BO machine, and the executable firing off the BI queries (WireportServer.exe)

So we tried to find a way to expose the current Business Objects universe, report- and end user name in the Oracle Database’s session information and came up with this solution:

Be sure the user you connect through has execute rights on DBMS_SESSION.
In your Business Objects Universe Designer goto:
Menu->Tools->Connection->Edit
and add the following to the ConnectInit parameter:

BEGIN DBMS_SESSION.SET_IDENTIFIER('@Variable('BOUSER')'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO('@Variable('BOUSER')'); DBMS_APPLICATION_INFO.SET_MODULE('@Variable('UNVNAME')', '@Variable('DOCNAME')'); END;

Above will set the client identifier, client info, module and action in the session info, ready for us to do some aggegration upon in Enterprise Manager:

As you can see in the above screenshot, you can view the actions (report names) per module(BO universe) and enable aggegration. This wil save statistics on a particular module/action to be viewed in detail:

If you need to find out what a specific user is executing, simply view the session information filtering on client identifier:

I hope this helps in providing more insight in database activity.
In fact, this approach can of course be used for any PL/SQL program or third party tooling which has the possiblity to execute some code when the connection to the database is set up.