Deep dive into the BI 4.x Audit data model

Overview:

Auditing is an important out of the box solution, to keep a track on the usage pattern of the SAP BOE platform. Audit data is relevant both from an administration perspective, as well as from compliance perspective for maintaining audit trail for a specified interval of time. While sample audit universe acts as a starter kit to start reporting on audit data (http://scn.sap.com/docs/DOC-53904), a knowledge on the underlying data model helps us build our own queries & reports and optimize them better for performance. The starting point of understanding how auditing works and what information is audited, refer to the relevant chapters in the BI Platform admin guide, downloadable at : http://help.sap.com/boall_en/. e.g. in sbo41sp3_bip_admin_en.pdf, chapter 21 and 33 talk about auditing. There are also several insightful blog posts on auditing and audit reporting by ‘Manikandan Elumalai’ on SCN.

Any SQL examples shown in this blog post are based on audit database hosted in Oracle. However, the same can easily be adapted to any other query language syntax, as the table structures remain same.

Audit Data Model:

Audit database is designed for both transactions and querying. Audit data is continuously being written to this database by BOE and at the same time audit reports / queries can be fired on it to report near real time audit information.

There are two main transaction tables in audit database: ADS_EVENT and ADS_EVENT_DETAIL. Remaining tables are either lookup or bridge tables. Any auditable action in BOE is captured as a unique Event_Id stored in ADS_EVENT and each Event_ID will have one or more detail records (Event_Detail_Id) in ADS_EVENT_DETAIL. Both the Event and its corresponding Detail can be of specific types and can have other supporting attributes.

This core concept of auditing has remain unchanged since BO XI 3.1, though the number of tables have increased significantly in BI 4.x audit database. The increase in number of tables is primarily due to increase in the attributes being captured and more normalization of the data structures.

BO XI 3.1 Audit Data Model

BI 4.x Audit Data Model

Audit Data Dictionary:

The best way to analyze audit database, is to use a GUI based database client like Oracle SQL Developer. The following queries are helpful in listing the data dictionary:

——-

select owner, object_name, subobject_name, object_type
from all_objects
where owner = '<Schema Name where audit tables are created>'
order by object_type, object_name;

—–

select owner, index_name, index_type, uniqueness, table_name, table_type
from all_indexes
where owner = '<Schema Name where audit tables are created>';

——

desc <each table name>;

——

A clear trend which comes up, based on the output of the above queries:

• Only tables and indexes are present in audit database. No views, procedures, materialized views etc. exists
• There is no enforced referential integrity between the tables i.e. no primary and foreign keys
• Index type is normal and either unique or non-unique
• Due to multilingual support being available by default in BI 4.x, all lookup tables (names ending with _STR) have ‘Language’ as an additional field
• The field EVENT_DETAIL_VALUE in ADS_EVENT_DETAIL is of datatype CLOB. Remaining columns in all the tables are of either varchar2, numeric or date datatypes.

Building Audit Queries:

Common audit reporting scenarios may have metrics like Count of Events, Last <Event Type> Timestamp, Count of Users. All these metrics can be derived from the table ADS_EVENT. Supporting details for an event can be obtained from ADS_EVENT_DETAIL. Description of attributes can be obtained from the lookup tables after joining with either ADS_EVENT or ADS_EVENT_DETAIL tables. It is important to apply suitable filter to the queries to optimize performance. Common filter criteria may be based on date, event type, detail type, language etc.

Example scenario:  Reporting user group membership details for users, who have logged into BOE in past 30 days:

—-

SELECT DISTINCT USER_NAME, USER_GROUP FROM (
SELECT ae.USER_NAME USER_NAME,
dbms_lob.substr(ad.EVENT_DETAIL_VALUE,2000,1) USER_GROUP
FROM ADS_EVENT ae, ADS_EVENT_DETAIL ad WHERE ae.EVENT_ID = ad.EVENT_ID
AND ad.EVENT_DETAIL_TYPE_ID = 15 --Denotes detail type: User Group Name
AND ad.event_detail_value not like 'Everyone%' --To eliminate the 'Everyone' group records
AND exists
(select 1 from ads_event X where X.event_type_id = 1014 --Denotes event type: Logon
and X.event_id = ae.event_id and X.start_time >= sysdate-30))
WHERE rownum < 50001
ORDER BY USER_NAME;

——

The above query converts CLOB data type to varchar. Once converted, regular string functions can be applied on the results like order by, distinct etc.

Concluding Remarks:

The above write-up is not an exhaustive reference on audit database. Readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time 🙂