We believe Auditing and Monitoring capability for Reporting is crucial for the IT management and governance regardless if it’s enterprise level or department level deployment. So we are always committed to improve our BI Publisher Auditing and Monitoring capability since we introduced it for the first time
With the first release of 11g (11.1.1.3) we added this Auditing and Monitoring capability, which enables you to monitor who access to which report at what time.
- How to Audit and Monitor BI Publisher Reports Access?
- How to Visualize your Audit Data with BI Publisher?
With this new release of 11.1.1.5, we added more data to audit and monitor especially more BI Publisher reports specific information such as:
- Report output format type
- Layout Template name
- Reporting locale
- If the reports is run online (on-demand) or through the scheduler
- Processing time
- Reporting data size
- Report error message
- Scheduled job information
And these information can be all available through the database tables so you can use BI Publisher to generate your Auditing reports or visualize the data to gain more insights about how your users are doing on your reporting platform.
If you are interested in the power of the new BI Publisher Auditing and Monitoring, here is a video.
And, if you want to know how to enable the Auditing with the new data sets I’ll cover that below.
Enable Auditing Data
There are three things you need to do to enable the Auditing data.
Setup basic Auditing configuration
I have already talked about these steps before, take a look at these two posts for the detail.
Run ‘xmlpserver_audit.sql’
There is a SQL script that you can run to create an additional table, which will store more BI Publisher reports specific data to audit and monitor.
Let’s take a look how to run the script step by step.
1. Change the current directory to ‘%BI_HOME%user_projects/domains/bifoundation_domain/config/bipublisher/repository/Admin/Audit’.
This directory should contain a file called ‘xmlpserver_audit.sql’ file.
2. Run this script as SYS or SYSTEM user and alter the session user to the schema who owns the AUDIT tables.
Example:
Let’s say you have created the Audit schema with the RCU with ‘KAN_IAU’, then you can follow something like below.
| $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon May 2 13:53:49 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: SQL> alter session set CURRENT_SCHEMA = KAN_IAU; Session altered. |
Now you can run the ‘xmlpserver_audti.sql’ script. Note that, when you run you'll be prompted to enter the following three schema names.
- Basic Auditing Schema name
- Edit Auditing Schema name
- View Auditing Schema name
With the above example of using ‘KAN_IAU’ as your basic schema, it would be something like below.
- KAN_IAU
- KAN_IAU_APPEND
- KAN_IAU_VIEWER
Here is the example:
| SQL> @xmlpserver_audit Table created. Index created. Enter value for 1: KAN_IAU old 1: GRANT ALL on xmlpserver to &&1 new 1: GRANT ALL on xmlpserver to KAN_IAU Grant succeeded. Enter value for 2: KAN_IAU_APPEND old 1: GRANT INSERT on xmlpserver to &&2 new 1: GRANT INSERT on xmlpserver to KAN_IAU_APPEND Grant succeeded. Enter value for 3: KAN_IAU_VIEWER old 1: GRANT SELECT on xmlpserver to &&3 new 1: GRANT SELECT on xmlpserver to KAN_IAU_VIEWER Grant succeeded. old 1: CREATE OR REPLACE SYNONYM &&3..xmlpserver FOR &&1..xmlpserver new 1: CREATE OR REPLACE SYNONYM KAN_IAU_VIEWER.xmlpserver FOR KAN_IAU.xmlpserver Synonym created. old 1: CREATE OR REPLACE SYNONYM &&2..xmlpserver FOR &&1..xmlpserver new 1: CREATE OR REPLACE SYNONYM KAN_IAU_APPEND.xmlpserver FOR KAN_IAU.xmlpserver Synonym created. |
After that you need to restart the WebLogic Server to take that in effect. Once that’s done, BI Publisher will start feeding all the auditing and monitoring related information into the auditing tables, and you should be able to start access to the data to create reports and visualize it with BI Publisher.
Create Reports to Visualize
If you have already finished the steps mentioned on the previous blog posts then you should already have a JNDI/JDBC connection created. If you haven’t yet, then take a look at the following post.
Make sure you assign appropriate application roles to this data source connection so that you or any users who need to create reports for the BI Publisher Auditing and Monitoring data will have an access to the data source.
With this new set of data available, you can now start creating reports to gain more insights about how your users use the reporting platform and prepare for a better reporting experience by responding and adjusting promptly.
Start with Sample Reports
We’re planning to release a set of sample Auditing and Monitoring reports as part of our sample reports inside the catalog some time soon but till then you can start creating your own reports against the Auditing tables by using BI Publisher. For those looking for something quick, I’m attaching a sample report here
- Sample Auditing Report
- Sample Auditing Data Model
You can download the above files and upload them to your BI Publisher 11g (11.1.1.5) environment from the BI Publisher Catalog UI, but make sure the following three points before you run.
1. Change the Data Model reference inside the Auditing report to point to the Auditing Data Model that you uploaded
2. Change the Data Source inside the Auditing Data Model to your JNDI connection
3. Update the SQL query to point to appropriate schema name.
Path to Improve Reporting Experience
Again, the Auditing is not just about collecting the auditing data and record it. It’s about how you as IT manager or administrator understand your users and reports on the reports environment in order to improve the reporting experience. The more you understand them the better service you can provide. We believe that the new release of BI Publisher 11.1.1.5 would provide you a wide range of data to help you understand your reporting environment better and quickly. Hope you’ll enjoy the power of new BI Publisher Auditing and Monitoring capability.
Also, as always, we’re very interested in your feedback. Please let us know what do you think about the data currently available, the sample reports, any additional requirements or improvement suggestion.
