Thursday May 19, 2011 New Feature – More Data to Audit and Monitor


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 ( we added this Auditing and Monitoring capability, which enables you to monitor who access to which report at what time.

With this new release of, 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.


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 Production on Mon May 2 13:53:49 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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.


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


Synonym created.

old 1: CREATE OR REPLACE SYNONYM &&2..xmlpserver FOR &&1..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

    You can download the above files and upload them to your BI Publisher 11g ( 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 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.

  • Thursday May 12, 2011

    BI Publisher 11g - is here!

    New Oracle BI Publisher 11g Release is released! Here is a list of the new features.


    Much Faster Interactive Reporting Experience

    Using BI Publisher to interact with your data is snappy. Generating interactive report output uses less memory and is faster than ever. BI Publisher’s highly optimized, three layer caching mechanism provides a highly responsive, interactive reporting experience.

    New Interactive Component - List

    The List component is a nifty way to filter data in an interactive report. When you click values in the list, BI Publisher automatically filters the data in the report. In the Layout Editor you can set the orientation of the List to be horizontal or vertical and over 30 other options to make it visually appealing for your consumer needs. Lists are another great way to interact with your report to gain more insight. See a quick demo



    Both Native and Web Apps for iPhone & iPad

    More and more business tasks are performed on mobile devices like iPhone and iPad, so why not browse reports and interact with your data on your mobile device with BI Publisher? Simply access your BI Publisher reports with Safari or other browsers on your iPad or iPhone, or install the Oracle BI Mobile App for iPhone and iPad to enjoy the same rich, fast interactive reporting experience as on your desktop or laptop. See sample pictures.

    Enterprise Reporting Platform for Oracle - E-Business Suite

    BI Publisher now supports Oracle E-Business Suite’s data level security based on user responsibility and allows you to switch responsibilities on the fly. You can use BI Publisher to view, manage, and deliver reports against E-Business Suite data and join and aggregate with data from other data sources. Utilities are available to help you convert from Oracle Reports and upgrade to BI Publisher 11g. Start taking advantage of many of the great BI Publisher 11g new features with your E-Business Suite data today.



    New Data Source – Local Excel File

    Often times you need data in Excel to supplement other data or you need to visualize it quickly. BI Publisher added Excel files as a data source so you could create a report against Excel data located on the server. BI Publisher goes one step further. Now you can upload Excel spreadsheets from your local machine directly to a Data Model. Create reports from the uploaded Excel spreadsheet or join it and aggregate it with other data sources. Reporting with local Excel files is a great way to empower your users to visualize and share data easily and still maintain a centralized report environment. See a quick demo

    New Template Type - Excel Template

    Some users just love Excel, so we added Excel as a report layout design option. In addition to the existing template options like BI Publisher Templates, RTF, PDF, Flash, and others, you can now use Excel to design your report the way you want, using custom functions and Excel Macros to split data across multiple sheets, calculate values dynamically, then share with your colleagues.



    Auditing and Monitoring

    Auditing is not just about compliance, it’s a way to improve customer service by understanding what your users like to do, when and how they access and view reports. BI Publisher included a framework to help IT Administrators collect data required for auditing. BI Publisher enhances the audit and performance information captured and introduces a method to store the data into a database. Now you can use BI Publisher to visualize, analyze and report on your auditing and performance information. See a quick demo.

    New Output Format Type – Native Excel 2007 Output

    Now you can download your report as Excel 2007 format and open it with the latest Excel on both Windows and Mac with high fidelity formatting and layout. You can use repeating groups in your report to automatically group data in separate worksheets. With this format option the downloaded Excel file is 70% smaller compared to other Excel output types, which makes it easier to share your report in Excel with your colleagues.



    Reports Development Life Cycle Management

    Developing reports often involves multiple phases and processes before going live. Reports need to be moved to a testing environment after the development and then to the production environment. You can use the new BI Publisher Catalog Utility to move your reports either one at a time or in batch from one environment to another all without shutting down servers so users can continue to generate the mission critical documents and reports they need.

    Also, we have many more enhancement requests implemented in this release and fixed bugs from the previous release. Try download from here and start BI Publisher 11g today!

    Want to learn more about BI Publisher?


    Follow us @bipublisher for our latest news, inside scoop, or tweet us your feedback!


    Connect with other BI Publisher professionals!


    Find How-to and solutions over 500 articles!


    Subscribe our channel and check our introduction and How-to videos!


    Ask any question you have and get answered at our Discussion Forum!


    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!


    « July 2016