A Technical exercise: Provisioning Siebel Audit Trail data in a datawarehouse

Plenty of my colleagues have been asking me on this purely technical exercise of leveraging Siebel Audit trail data in the warehouse via ETL hence I thought of posting this article for the techie aficionado!

Basically, those who have used Siebel Audit Trail data earlier know for a fact that it is next to impossible to try and get all the historical data changes to e.g. an 'Opportunity' (what typically in the 'Customer Relationship Management' world means a person/ company/entity who can be a lucrative customer for the company) and what has been done to a particular opportunity:

Is the Opportunity been processed through the various stages of being approved, having a progressive 'confidence level' (how confident are you as a sales person to be able to sell to this customer? That is a percentage in CRM like 100% meaning you are sure to be able to sell to your customer)

Audit Trail also has the data to track the duration of days between the status updates give you an excellent view as to how pro-active your sales people have been with regards to selling your business! Basically a 360 degree view of any audited business process within your Siebel CRM application of choice is what this gives you.

However, the manner in which Siebel stores this data has changed hugely in the interests of disk space savings for such a costly activity for a transactional system.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 encoded string with all fields for each update reduces the size of the table significantly.

What was unfortunate 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 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.

That meant us Oracle BI datawarehouse guys were left with one option: make a custom build logic that would decode this complicated bit of data. Just to illustrate, here is a screenshot below that will illustrate the point of what the data looks like in the Siebel table:

Looking at the cryptic data set above, one can see that notations are being used to indicate which Siebel 'Business Component' (Business Components are nothing but encapsulated modules of CRM code for a certain business function in the CRM department of your company) has been updated , what is the new updated value and what was the exact previous value prior to the update.

Another good look at the data below and you can tell roughly what the update is in the first line below:

First line indicates that an Opportunity's Revenue Amount and the Opportunity's Win Probability Percentage has been entered, as the operation is a new record by the user identified by the USER_ID above. But what to do of all those intrepid codes in between and how to interpret them systematically into a datawarehouse table for analytical purposes?

Here is my solution that works in Informatica using a complicated bit of sql written after days of understanding the code format...and it works:

select
tbl_name,
Record_id,
Operation_dt,
Operation_cd,
substr(column_list, instr(column_list,',',1,seq)+1, instr

(column_list||',',',',1,seq+1) - instr(column_list,',',1,seq)-1) Field_name
--substr(old_value, instr(old_value,',',1,seq)+1, instr

(old_value||',',',',1,seq+1) - instr(old_value,',',1,seq)-1) Old_value,
--substr(new_value, instr(new_value,',',1,seq)+1, instr

(new_value||',',',',1,seq+1) - instr(new_value,',',1,seq)-1) New_value
from (select
aud.operation_cd,
aud.operation_dt,
aud.tbl_name,
aud.record_id,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*C')+4, DBMS_LOB.INSTR( audit_log, '2*N')- (DBMS_LOB.INSTR( audit_log, 

'2*C')+4)),'1234567890',' '),'*',',')Column_List,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*O')+4),'1234567890',' '),'*',',') Old_Value,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*N')+4, DBMS_LOB.INSTR( audit_log, '2*O')- (DBMS_LOB.INSTR( audit_log, 

'2*N')+4)),'1234567890',' '),'*',',') New_Value
FROM siebel.s_audit_item aud
WHERE
aud.buscomp_name = 'Opportunity'
AND aud.OPERATION_DT >= sysdate - 1), (select level seq from dual connect by 

level <= (select max(regexp_count(column_list,',')) from (select
aud.operation_cd,
aud.operation_dt,
aud.tbl_name,
aud.record_id,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*C')+4, DBMS_LOB.INSTR( audit_log, '2*N')- (DBMS_LOB.INSTR( audit_log, 

'2*C')+4)),'1234567890',' '),'*',',')Column_List,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*O')+4),'1234567890',' '),'*',',') Old_Value,
replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, 

'2*N')+4, DBMS_LOB.INSTR( audit_log, '2*O')- (DBMS_LOB.INSTR( audit_log, 

'2*N')+4)),'1234567890',' '),'*',',') New_Value
FROM siebel.s_audit_item aud
WHERE
aud.buscomp_name = 'Opportunity'
AND aud.OPERATION_DT >= sysdate - 1))) seqgen
where instr(column_list,',',1,seq) > 0

This SQL will need to be on the SQL Override of the Source Qualifier in the Informatica mapping pulling data off Siebel's S_AUDIT_ITEM table.

Land this data on a staging table and have a good look at the staged data: You will find that the history of every activity on every audited item like an Opportunity or an Agreement is in the staged data! However, you might notice some duplicate rows coming out so have an Aggregator Transformation handle that and give you a single record!

Now have a second ETL mapping to lookup the user key, opportunity key , update date key e.t.c back to the conforming dimensions within your warehouse and you are set to use this insightful information of what's being audited in your Siebel system.

Now 'detractors' might inform you that Oracle BI Apps gives you a procedure called 'FIND_AUDIT_VALUES' that give the immediate audit history of the same audited data anyway. And there lies the limitation with this Oracle supplied package:

The package only gives you the most recent update that was audited, not the whole history of the audits to that audited item in Siebel! Hence this custom ETL approach gets the 'coin' rolling further, giving more insight in your CRM system.

Hope this has been useful, have a great 2017 ahead of you all, thanks for reading.