As we all already aware, In BI 4.x the capabilities provided by as-is sample of Audit reporting suite is limited and we can see there are so many requirements flowing in SCN around this for quite some time with reference to additional requirements from Auditing .
Can I extend the Auditing capabilities and How?
We can enhance the existing audit capability from the as-is sample. Besides the default sample reports provided, I do have few more requirements something like below.
- Frequently used reports
- List of most active users
- Who are all my Mobile BI users?
To achieve the requirements above I have adopted following approaches.
Below are some of the approaches I have considered for Audit reporting enhancements.
1. Creating customized Audit reports from the existing Audit schema
We can create enhanced audit reports from the existing Audit schema based on our requirement. We can create extended reports by referring existing report and modify the report prompts/filter etc.
For example to get the Mobile report access use “Application_Type_Name” from table “ADS_APPLICATION_TYPE_STR” which provides the application type from which the access is from i.e. mobile device. It is available as “Client Application Type” in class “Events” in the Universe)
2. Creating Custom tables in Audit schema for the reporting
We can create custom tables in Audit schema based on our requirement. One of such option is to create derived tables in Audit universe based on the Custom SQL statements than can be run directly on Audit database.
More active users can be obtained by running the below SQL on Audit database
ADS_EVENT.USER_NAME AS USER,
COUNT(ADS_EVENT.EVENT_ID) AS COUNT,
RANK() OVER (ORDER BY COUNT(ADS_EVENT.EVENT_ID) DESC) AS RANK,
GROUP BY ADS_EVENT.USER_NAME
Create a derived table in Audit universe with the above SQL and then you can directly run reports on top of the derived table column/objects.
Alternatively if the Custom SQL extracts large dataset we can skip the derived table approach which is meant for less number of rows and create a materialized view at database side and refresh it periodically and then do the reporting from there.
3. Creating a metadata repository and start the reporting by creating the multi source universe which points to both Auditing schema as well as metadata schema.
This approach will be very useful whenever we need to create reports that need to capture the information from both Audit and BO repository. Some of the information such as number of Named users/Concurrent users cannot be extracted from Audit schema in which metadata reporting along with Audit reporting will be handy.
BI 4.x Audit reporting references:
Thanks for reading. Appreciate all your thoughts, comments, ideas & feedback.