Having difficulties creating SAP BO Audit reports?
Just recently, I was tasked to develop a Business Objects Webi report that displays the list of all users within the system along with their last login dates. For users that have never logged on, this last login date should be left empty. As simple as this project may sound, it involved some crucial steps and tweaks to the SAP BO Audit universe that is provided by SAP. In this blog, I will share information on how I was able to successfully accomplish my goals for creating this webi report.
I was working with SAP BI 4.1 sp6 with a SQL server database back-end for storing the infostore and audit data.
Note: You can use the Query Builder tool under Admin tools but this generates a result set that is not automatable or exportable. There are ways to use the SDK to extract this information into excel but this didn’t satisfy my requirements as I needed to build something in webi with scheduling capabalities.
Here is the list of steps that I had to go through in order to successfully complete the project:
Please ensure that Auditing is turned on within CMC with the database connection pointing to the appropriate AUDIT database. If you skipped the AUDIT database creation during the install, you just need to create a new audit database and fill in the database details within the Audit configuration screen in CMC and the system will automatically create the necessary audit tables for storing audit information.
Downloading the AUDIT Universe:
As most of you may be already aware, the AUDIT universe and reports are not part of the standard business objects installation starting with version 4.x. In order to run audit reports, you will first need to download the AUDIT universe along with instructions to deploy the universe within your current system. Here is the link from where to install this: http://scn.sap.com/docs/DOC-53904
Updating the AUDIT Universe:
Once the AUDIT universe has been downloaded and deployed, you must ensure the Universe connection is correctly pointing to the AUDIT database using either ODBC or OLE DB within IDT. In addition, you will need to add the ADS_USER table to the universe as this is not part of the universe data foundation by default. It is necessary to include the ADS_USERS table within the universe because this table holds information about all the users within the system including the ones that have NEVER logged on to the system and hence DO NOT exist in the ADS_Event table. One other thing that is critical is that the join between the ADS_USER table and the ADS_EVENT must be a left outer join as there could be users (that never logged on) in the ADS_USER table that do not exist in the Event tables and we don’t want to miss those users in our audit reporting. Remember to expose the columns of the ADS_USER table within the business view layer.
Please note: The audit tables take a few minutes to reflect any changes to the system.
Develop the Webi Report:
Once you have updated and deployed the universe changes to the repository. You are now ready to build audit reports. Below is an example of a query for an audit report that will list all the users of the system along with the Event Type and Date.
Note: The users object will list all users of the system, including the ones that have never logged on and hence have no events registered within the system.
Furthermore, SAP has included some useful pre-built audit reports in Crystal under the Public/Auditing folder and are grouped by the following categories: