Audit Trail Storage

About large objects with character

A while ago during a Siebel CRM upgrade project, a consultant approached me and asked how and why Oracle has changed the data model of Audit Trail from version 7 to version 8.

After a brief discussion we found out that he referred to a new column in the S_AUDIT_ITEM table. The column is named AUDIT_LOG and has the data type of CLOB (Character Large OBject - newly supported in Siebel 8.0 and above), which enables it to carry a whopping 2 gigabytes of data for each record.

A little bit of investigation reveals that this column stores a string that documents the changes made to the audited record during an update or insert operation. The following screenshot gives you a general idea how this string looks like.

click to enlarge

In earlier versions of Siebel CRM, the S_AUDIT_ITEM table stored one record for each field that was audited, so the table grew very large. Creating a single string with all fields for each update reduces the size of the table significantly.

What was a little unfortunate in the specific project is that they accessed the S_AUDIT_ITEM table with Informatica to incorporate the Audit Trail data into their data warehouse. After the Siebel CRM upgrade, the ETL did no longer work because of the new column.

Furthermore, because of the data type of CLOB and the cryptic nature of the string it was impossible to retrieve Audit Trail data with simple SQL.

I proposed a solution which encouraged accessing the Siebel business component layer using EAI techniques such as Siebel ASI (Application Services Interfaces) and web services. This would eliminate the need to access the physical table directly.

The business component used to display the Audit Trail data is based on a specialized class CSSBCVAuditTrail which makes it a virtual business component. But you can still use it in an integration object and create a read-only ASI. Please find more information about Siebel EAI and ASIs in the Siebel Bookshelf and the Integrating Siebel Applications course.

As the more inquisitive among you might have already found out, the data string in the AUDIT_LOG column looks quite familiar to the one that stores the data modifications for Siebel Remote in the S_DOCK_TXN_LOG table (see screenshot below). But this is a different story...

click to enlarge

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The intention of this blog is to share findings about Siebel CRM and Oracle Business Intelligence Enterprise Edition (Oracle BI EE formerly known as Siebel Analytics) from technical and other (sometimes unprecedented) perspectives.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today