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.

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

—————————————————————————————————————–

SELECT

            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,

FROM ADS_EVENT

WHERE EVENT_TYPE_ID=1014

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:

BusinessObjects Auditing – What is changed in BO 4.0?

Sample Auditing Universe and Reports for SAP BusinessObjects_4_x

SAP BusinessObjects 4.0 Auditor Configuration & Deployment End to End

BusinessObjects Auditing – Considerations & Enabling


Thanks for reading. Appreciate all your thoughts, comments, ideas & feedback.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. sekhar raj

    Hi Mani,

    For the past few days, i have been reviewing all your blogs related to Auditing. they are very useful and thanks for sharing with us. I am trying to write audit reports to capture users running reports thru Opendoc. any idea how to filter/fetch users information logging thru opendoc?

    – Sekhar

    (0) 
  2. srinivas A

    Is there any possibility to find out the objects in universe(dimensions/measures/details)  which are not used in particular period ???

    (0) 
  3. Patrick Delage

    Hello Manikandan,

       I just like to give my feedback on your suggested point 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.”

    I was thinking doing this too, and stop when I realize that the audit universe which is provided by SAP or not (not sure) is not multi-source at first.  But it didn’t stop me right here, I then try to rebuild the universe in multi-source (because we cannot convert to multi-source 🙁 ) and realize the copy and paste is not very useful.  Then I stop 🙂 Too much work for now.

    So, a sub-question to this, is someone rebuilt it in multi-source.  Us, we used the audit databse for oracle.

    Thanks.

    (0) 
  4. Elli Gu

    Hi  Manikandan,

    I ‘m meeting a problem about mobile viewing event.

    I have checked  table ads_event  in Audit database(Oracle),and found that create/delete/log in and log out events, but no view event  for mobile.

    Is  there any possibility to find out the reason?

    Thank you in advance!

    (0) 

Leave a Reply