In this article we will discuss the use of BI audit reports for streamlining reporting operations. Understanding the application usage is essential for ensuring the day-to-day activities are carried in a proper/efficient manner and BI Audit provides the ability to monitor the report runs, catalog changes, number of errors, poorly performing reports, etc. Good news is that the necessary BI Publisher Audits are turned on by default, so you can start using these features today!

BI Publisher Audit was turned ON by default starting in release 19B. All executed reports are recorded into BIP Audit tables. The audit captures all runtime metrics such as timestamps, row counts, SQL text, error messages, and other metrics that can be analysed for any performance issues, usage anomalies and trends.

Below are the steps to access the BI Publisher Audit Data

  1. Configure the AuditDB data source
  • Navigate to Administration page
  • Under Data Sources select JNDI Connection
  • Click Add Data Source, and enter the following:
    • Data Source Name: AuditViewDB
    • JNDI Name: jdbc/AuditViewDB
  • Click Test Connection to confirm the connection
  • Move roles from the Available Roles list to the Allowed Roles list as necessary.
  • Click apply

Fig 1. BIP Audit – Data Source

  1. Download and Use Audit Reports available from here
  • Download the Sample Audit Report zip file from OTN BI Publisher download page under section “Sample Audit & Usage Reports”
  • Extract the Audit.xdrz file from the zip file to your local machine
  • Login to BI Server using the URL as https://servername/xmlpserver
  • In the Catalog page, navigate to Shared Folders/Custom Folder and upload the Audit.xdrz file
  • Add security and permission as necessary to access the Audit Folder

The sample audit reports contain the following reports.

  • Monitoring Reports
    • BIP Audit Histogram and Concurrency Report
    • BIP Audit Report Summary
    • BIP Audit Single Report Detail
    • BIP Datamodel Details Report
    • BIP Dataset Histogram Report
  • Audit Reports
    • BIPCatalogObjectAuditTrail
    • BIPReportAuditTrailLast

Monitoring Reports

The following section provides details on reports available for monitoring of BIP reports

BIP Audit Histogram and Concurrency Report

The report classifies reports into different time interval. This will help users to review the report executions and runtimes to plan the resources better.

Fig 2. BIP Audit – Histogram by Report Name

Upon reviewing the report, we could see that the ‘Most Run Report 1’ has been executed highest with all executions completing in under 3s. However, the ‘Long Running Report 1, 2 and 3’ had failed for all executions with runtimes between 30m to 1 hr which needs to be reviewed further. We can use few other reports to understand more about these report runs.

 The same report has a ‘Hourly Concurrency’ layout which displays hourly data for the number of report executions. This could be used to review any spike in the executions that could be causing performance issues

              Fig 3. BIP Audit – Concurrency Report

BIP Audit Report Summary

The report provides summary of all report executions along with few additional information like report runtimes, rows processed, timings & error details. It also has a link to ‘BIP Audit Single Report Detail’ which gives additional information on a particular report

Fig 4. BIP Audit Report Summary

Audit Single Report Detail

The report provides information on a particular report. It provides the report statistics and the data model/dataset details, error details of execution for further drill down.

Fig 5. BIP Audit Single Report Detail

BIP Datamodel Details Report

The report provides the execution details of the datasets in a data model tied to the report. This can be used to figure out the dataset which runs for extended time or the one causing the performance issues. The ‘BIP Dataset Histogram Report’ gives additional information on the dataset runtimes across different time interval

Fig 6. BIP Datamodel Details Report

Audit Reports

The following section provides details on reports available for auditing catalog updates & user activity.

Audit Data for Catalog Object Updates

The report can be used to track any action (Update, Delete, Copy, etc) on the catalog objects. There are 3 layouts in this report

BI Publisher Catalog Object Update History

This layout lists all activity on a catalog object performed by users

Fig 7. BI Publisher Catalog Object Update History

User Action History on Catalog Objects

This layout lists the activity on catalog objects grouped by user

Fig 8. User Action History on Catalog Objects

Catalog Activity Report

This layout displays activity count on different catalog objects and different operations being performed on the objects.

Fig 9. Catalog Activity Report

Audit Data for Report Execution

This report displays details of reports execution details and can be used to audit the reports run by various users. There are 5 layouts in the report

Report Execution History by User

This layout displays details of reports executed grouped by users

Fig 10. Report Execution History by User

Report Execution History

This layout displays history of report executions

Fig 11. Report Execution History

Report Data sets & Query Execution Report

This layout gives additional details on the different data sets that are executed for a report

Fig 12. Report Data sets & Query Execution Report

Report Execution Summary

This layout displays details on report executions details on type of report runs, different output formats and if the report is seeded or custom.

Fig 13. Report Data sets & Query Execution Report

Top 10 Reports

This report displays Top 10 reports based on the counts of execution, report execution time, size of the report and users with most report execution

 

              Fig 14. Top 10 Reports

Note:

The Audit tables for BI Publisher may fill up very quickly and can soon have huge amount of data that can take long time to process and may even exceed the data size limits to view these reports online. Therefore, all report properties are unchecked to be viewed online as default and these reports should be run as scheduled Jobs.

The retention period of 90 days has been set for BI Publisher Audit data. You can even create a recurring BI Publisher job to run the Audit reports and deliver the XML or CSV data and the report output to WebCenter Content or any other delivery destination.

With these reports users can identify long running reports, reports which are erroring often, etc and review the usage/performance of reports and take appropriate actions to ensure the resources are used efficiently.