Auditing Data Capture Performance & Reducing its size
In this blog, I talk about the SAP BI Platform auditing performance in terms of getting the audit events into the Auditing database. I share how you can gain an insight into any delay and how you can improve the performance and potentially dramatically reduce the size of the audit database. I also explain how you can ask the question “Which groups has a user been added or removed from?”
Gaining insight in Auditing Performance
My Auditing Universe solution provides a document (SYS2 – System – Delay in Recording of events to Audit Database) that gives you an analytical insight into this delay:
The graph show the delay over time. In this example, time is shown by hour and ‘day of the month’, meaning all the months and years are aggregated. You can clearly see delays at times of the month.
We can see the blue line shows the maximum delay, so it’s about 3 days on this system for all the events to reach the audit database.
With SAP BI Platform 4.2 significant performance improvements were made. This means if you experienced a delay before, then updating to BI 4.2, should provide a great improvement. Your feedback is welcome, please post a comment (version, which audit settings you have enabled, RDBMS, delay comments)
In some rare cases, you might had felt the need to disable auditing. We really hope that’s not the case anymore, especially since BI 4.2. Again, your comments are very welcome.
How can I improve the auditing performance?
If you have the Central Management Console-Auditing option ‘User Group Details’ selected, then for each event the groups that user is a member of are captured. Let’s say a user is a member of 200 user groups, this wouldn’t be unusual. Then, let’s say a user opens a Web Intelligence document and refreshes it having entered values for say 10 prompts.
Now, each prompt is an event. So, the action of opening a Web Intelligence document, answering 10 prompts and refreshing it to display a page is at least 12 events, if not more. For each event, 200 user groups are captured. This means 200 user groups x 12 events = 2400 rows of data are stored in the audit database. This is quite a lot of data for just one document.
Using my SYS2 document, you can see which BI Platform Server Service is the one with the longest delay to get its data into the audit database. You’ll almost certainly find the worst offender is Web Intelligence (assuming you’ve enabled the ‘User Group Details’). Again, comments welcome.
Improving Auditing performance and dramatically reduce Audit Database size
Quite simple, if you want to improve performance and dramatically reduce the size of your audit database, then you should de-select the ‘user groups details’ option:
Often I hear of very large audit databases and these can be problematic to manage and query.
Will I lose functionality if I de-select this option?
A little, yes. You won’t be able to filter any query to the auditing database to restrict it by a user-group. However, you won’t lose this functionality for long.
In BI 4.2 Support Pack 5 we plan that even when this option is not selected, the user group information will be captured but only for the logon event to prevent the database from becoming too full of redundant and duplicated data! This assumes the logon event is selected as something to be audited, if its not selected, then in BI 4.2 SP5, none of the user groups will be captured either. Capturing the user groups just once for each session, at logon, will have only a very small impact and will be much better than capturing it for every event!
This means from BI 4.2 SP5, you’ll be able to filter any query to the auditing database to restrict it by a user-group, removing the loss of functionality I just mentioned. You’ll be able to do this, because we can filter the logon event by the user group, and then we can filter all the events by the session IDs found in those logon events.
Also, regardless of which BI 4.x version you’re using, you can use the predefined Business Filter in my universe to restrict the query by a list of user groups:
This filter is defined in the Universe Business Layer as:
We filter on the Session ID to only Session IDs that correspond to a chosen list of user groups. Hence this filter will continue to work with BI 4.2 SP5 and the option ‘User Group Details’ de-selected.
Which groups have users been added/removed from?
Regardless of which BI 4.x version you’re using, you can always run a query to ask ‘which user groups has a user been added or removed from’. The query filter will need to look like this:
If you use my SYS1 (event log) document, then the results would be something like this:
The corresponding Property Value for the Property Name ADM_ADD_USERGROUPS_TO_USER shows the groups ‘Monitoring Users’ and ‘Universe Designer Users’ where added to the user object ‘Matthew’ by the user ‘Administrator’.
We’re keen to get your feedback on the performance of auditing (the capturing of audit events). Please share any lessons learnt here for the benefit of others. You might de-select the option I mentioned and seen an improvement and a reduced database size. Comments on the universe and the query time performance are best placed on this blog unless they relate directly to the options I’ve mentioned here.
Matthew (Twitter: MattShaw_on_BI)