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)
Is it possible to discover and delete those unnecessary records which were written by “User Group Details” option from BO Auditing database leaving only those rows which should have been recorder without “User Group Details” option checked?
Yes, you can delete the extra records for sure. You'd just need to delete the user-group detail rows for all but 'logon' events. Feel free to share the SQL you use so others can copy! 😉
Thanks for your universe, reports and your blog!! It has been most helpful to me recently when trying to assist a different team who have lost their BO Administrator recently.
One of the things they need help with is know user and usergroup changes. I see from your comment above that we can see when a user is added to a group but is there any property that shows deletions? They had an issue in January where a bunch of users access changed all at the same time and they are desperately trying to discover why.
Unfortunately not all the required auditing parameters were switched on at that stage so I am trying to help them set it up so that in future if they have problems, they can see what changed.
Thanks for your feedback.
I believe the deletion will be captured, but it may not be recorded as a 'deletion', but just a change. The audit details I believe will show the state before and the state afterwards. So you can then deduce what users where added/removed. Best to make a change in your system that you know about (user and date/time) and then see what the audit captures for that change. Regards, Matthew
You will only remove user group details? I have an incident where they ask me to remove query and folder path details. I think I will miss some functionality if I not choose to audit these two functionalities. We need to see the sql of a report and the folderpath of a document. The reason for removing them are better performance in the system.
Thanks for the great work, is it possible to download the Universe and reports as I can’t see any download link
Appreciate your help
Please use the link in the blog " Auditing Universe solution " . Regards, Matthew
Is it possible to retrieve the list of users using(means refreshing the Webi reports) specific universe from the last year?