Enabling BI Publisher Auditing - Part 2

In the second part of a two-part blog series, we go through the steps you’ll need to take if you want to send the logs to Oracle’s pre-built Auditing Services (AS) schemas.

Introduction #

In the first of this 2-part series I took you through the steps involved in setting up the Monitor and Audit features of Oracle's BI Publisher application. After completing part 1, you should be at a point where BI Publisher is sending the audit logs directly to a log file. In part 2, we will go through the steps you will need to take if you would like to send the logs to Oracle’s pre-built Auditing Services (AS) schemas, something I would definitely recommend doing if you’re serious about monitoring the performance of your BI Publisher reports.

Here are the high-level steps involved (please note that step 1 should have been completed in part 1):

1. Enable BIP auditing
2. Create the audit schemas
3. Create an audit data source in the Administration Console
4. Register the audit data source in Enterprise Manager

Create the Audit Schemas #

The first step in directing the audit logs to the database is to use the Repository Creation Utility (RCU) to create the common AS schemas. You can create the AS schemas in any database but I would recommend putting them alongside your already existing BIPLATFORM and MDS schemas for simplicity.

Download the RCU from here - be sure you download the right version!

Extract the zip file you’ve just downloaded and run 'rcu.bat' which sits inside the 'rcuHome' directory. Go through the installer, specifying the database you would like to use, until you get to the 'Select Components' screen. At the Select Components screen create a new prefix or select the one already containing the MDS / BIPLATFORM schemas; Then select 'Audit Services' from the the list of components before selecting 'Next'.

Run through the rest of the RCU until the new schemas have been created. You should then see the following new schemas in the database:

<prefix>_IAU

<prefix>_IAU_APPEND

<prefix>_IAU_VIEWER

Create Data Source in Administration Console #

The next part of the process involves configuring a new JDBC data source using the Administration Console. Log into the WebLogic Administration Console (http://[hostname]:7001/console)

Go to 'Services > Data Sources' in the Domain Structure pane then click 'Lock & Edit' in the Change Center pane.

Click on 'New > Generic Data Source' to add a new data source.

Fill in the details of the new data source then click 'Next'.

Ensure that Oracle's Driver (Thin XA) Versions: 9.0.1 or later is selected if you are using Oracle as your data source. Click 'Next'.

Fill in the database connection properties and click 'Next'. Be sure to use the connection details to your own database and not mine!

In the next screen you can test the connection to the database. Click on the 'Test Configuration' button at the top of the screen to see if your connection has been setup correctly.

Note: If you have problems connecting to your database then you may have to use the connect descriptor instead of the basic URL- for example:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCLDWH)))

If everything works then click 'Next' to define the target, make sure that 'AdminServer' and 'bi_cluster' are checked and click 'Finish'.

You should now see your new data source in the list, be sure to click 'Activate Changes' from the Change Center panel.

Register Audit Data Source in Enterprise Manager #

Now you will need to register the Audit Data Source to your domain, log into Enterprise Manager (http://[hostname]:7001/em) and expand 'WebLogic Domain'. Right-click on 'bifoundation_domain' and go to 'Security > Security Provider Configuration'.

Expand the 'Audit Service' accordion pane and click on 'Configure'.

From the Audit Service Configuration screen click the magnifier icon and select the data source you just deployed and click

'OK'. Make sure you click 'Apply' after setting the data source.

Do a full restart of OBIEE including Weblogic. You'll notice that the log entries that were stored in the audit.log file have been pushed into the database tables and all future logging will go directly to the database.

The two tables which get populated after you activate the Monitoring and Auditing are IAU_BASE and XMLPSERVER in the <prefix>_IAU schema. The IAU_BASE table is where all of the auditing data gets written if you've activated auditing for other services. For BI Publisher, you will want to use both the IAU_BASE and XMLPSERVER tables. You can join the IAU_BASE and XMLPSERVER tables together using the IAU_ID since you’ll want to use columns from both tables.

Here is an example of the IAU_BASE and XMLPSERVER tables after logging into BI Publisher and then logging back out again. Notice that the IAU_BASE table contains the name of the user I logged in as (weblogic) and the XMLPSERVER table contains other information. Note that there are many more columns in these tables but I’ve cut it down for our purposes.

IAU_BASE

XMLPSERVER

Now that BIP is logging audit information into an Oracle database you can easily report over it using a number of different methods. Your best options for reporting over the audit data will depend on your specific situation but here are two suggestions:

Create a Subject Area #

Import the tables into your BI repository and create a Subject Area specific to BIP auditing. This is my favorite option as it gives you the full power and flexibility of the BI Server and associated BI components.

Create BI Publisher Reports #

Add the database containing the IAU schema as a new data source into bi publisher and build reports directly off the audit tables. If you have a stand-alone BI Publisher installation then this will be your best bet since you probably won’t be using a BI repository.

Conclusion #

If you've followed through this series from part 1 you'll now know how to set up auditing and monitoring for BI Publisher and configure it so that the audit data gets pushed to an Oracle database. Capturing this information will allow you to gain some valuable insights into how your BI Publisher instance is being used and how it is performing. By creating reports and dashboards over the audit data you can easily keep on top of any potential issues and performance problems, reducing the risk of downtime and improving end user experience.

Find out more #

Boxfusion Consulting are an Oracle Platinum Partner and are recognised by Oracle as Specialised in the implementation of Oracle BI technology. If you would like to hear more about how Oracle BI might be used to help your business please give us a call on +44 203 283 4315 or contact us here!