Wednesday, March 7, 2012
Preserve History - Auditing in Intellimas 3.1
A highly requested feature for Intellimas 3.1 was the ability to preserve field-by-field change history. For example, when using Intellimas for Costing, an audit trail would allow you to monitor the changes in cost and margins over a period of time.
We decided the best approach would be audit tables that had the same columns as the actual tables. A row would be created in the audit table for each insert, update, and delete operation. Since we use NHibernate as our ORM, there was an existing library NHibernate Envers that we used to provide this functionality. Open Source Rocks!
For performance reasons, we didn't want to automatically audit everything so we allow the administrator to enable auditing for each TrackPoint Definition. When auditing is enabled, new tables suffixed by _Aud are created for the trackpoint, requestee, and request tables.
The illustration below shows the TrackPoint table on the left and the corresponding Audit table on the right. You can see that each _Aud table contains two additional fields, Rev and RevType. Also notice that WhenModified is not present in the audit table. The Rev field is used to join to another table, st_RevInfo, to get the WhenModified. The reason is that one Rev value is used to tie audits across many tables so you can group one user's save transaction across all the affected tables. RevType specifies the type of revision:
0 = Insert, 1 = Update, or 2 = Delete.
The audit tables are intended to be used in a reporting environment, so we don't currently provide any user interface to the audit tables. We may provide some types of views in the future, but we first wanted to see how our customers would utilize the audit data.
Here's and example query that returns the FOB changes over time:
select e.ud_CompNo as Style, ud_CalcFOB as FOB, stRevInfo.REVTSTMP as WhenModified, aud.RevType, aud.WhoModified
from st_FOBBD_T_AUD aud
inner join strevinfo on strevinfo.Rev = aud.rev
inner join st_CompBD_E e on aud.EntityId = e.Id
where aud.id like '3a3%'
order by stRevInfo.REVTSTMP
As you can see, the Auditing feature can produce some great information. You could easily create charts showing the rise and fall of FOB(or any other field) over a period of time.
If you would like to learn more about Intellimas and how it can be used in your organization, give us a call at (908) 277-0498 or email us at email@example.com
You can also learn more about Intellimas on our website.