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