Office 3295556 1920

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'.

Fig 1 - Enabling the BI Publisher Audit Services schema in the RCU

Fig. 1 - Enabling the Audit Services schema in the RCU

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.

Fig 2 - Adding a new data source in the BI Publisher Administration Console

Fig. 2 - Adding a new data source in the Administration Console

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

Fig 3 - Select “Generic Data Source” to create a new Oracle data source

Fig. 3 - Select “Generic Data Source” to create a new Oracle data source

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

Fig 4 - Specifying the basic database details

Fig 4. - Specifying the basic database details

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'.

Fig 5 - Setting the database driver

Fig 5 - Setting the database driver

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

Fig 6 - Fill in the connection properties for the database

Fig. 6 - Fill in the connection properties for the database

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.

Fig 7 - Screen allowing you to test the database connection

Fig. 7 - Screen allowing you to test the database connection

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'.

Fig 8 - Select the targets to deploy your data source to

Fig. 7 - Screen allowing you to test the database connection

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

Fig 9 - Ensure that the data source has been created and click Activate Changes

Fig. 9 - Ensure that the data source has been created and click Activate Changes

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'.

Fig 10 - Navigating to the security provider configuration in Enterprise Manager

Fig. 10 - Navigating to the security provider configuration in Enterprise Manager

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

Fig 11 - Change the audit service properties

Fig. 11 - Change the audit service properties

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.

Fig 12 - Selecting the new data source

Fig. 12 - Selecting the new 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

IAU_BASE

XMLPSERVER

XMLPSERVER

Fig. 13 - Extracts from the IAU_BASE and XMLPSERVER tables after a single login/logout

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!