Well. It's been an interesting 6 months.
New job.
New home.
New book project.
New Laptop (Rest in peace, worthy Dell Vostro).
So I'm long over due for a new blog post.
The last post is badly in need of a follow up, but that is going to have to wait a bit longer still--too much on my plate to do a decent job at the moment.
My last work project was an enhancement to an Access/SQL Server database for a unit in organization which handles financial transactions. They have aery interesting business model, but the part that is of most interest to me as far as this blog installment was the requirement for a robust auditing function. We were asked to provide an audit record of every transaction in the daily flow of work. providing a "before" and "after" look at the data. This is not a high-volume operation, but they do need to be able to account for every change that affects cash flows between accounts.
We came up with a pretty interesting technique, if I do say so myself. One word of caution, though, is in order. This would probably not work as well with really large database.
Step One. We create a "snapshot" of thirteen relevant tables prior to Start of Day. The largest one holds just under 175,000 records at this point. The snapshot tables are flushed and refilled by a scheduled job that runs on the Server. The snapshot gives us a point-in-time look at the data.
Step Two. We create an "audit" copy of the relevant tables at the End of Day. The audit tables are also flushed and refilled by a job that runs on the Server. However, the stored procs which refill the audit tables have filters on them so the audit tables contain only records with a change date of the current day. Change dates on tables are kept current by triggers.
Step Three. We run a VBA routine that compares every record in the audit tables to the corresponding record in the the snapshot table, field by field. The VBA function appends each changed field into a final audit table. In the final audit table, we capture the source table name, the field name where a change occured, the "old" value from the snapshot table, the "new" value from the audit table, "changed date and time" and "changed by" from the audit table, and a value for a descriptor field. In each table identified one field to provide the end user a more user-friendly guide to the changes. For example, in the check register audit, we include the Check Number as the Descriptor for all audit records, rather than the Primary Key, because the end user finds that more meaningful than the Primary Key field.
The Final Step was to create an ODBC link to the Audit Table from an Excel Pivot Table. The result is a dynamic pivot table which the end user can crack open anytime they want to review recent changes to critical data (invoices, invoice details, checks, etc.)
We go live this week (or next) and we're looking forward to user feedback on this tool.
We took advantage of the fact that the back end is in SQL Server to write triggers, views and stored procs to do the heavy lifting. We took the Audit Table append process back to VBA because I found it easier to use ADO recordsets for the field-by-field comparison than trying to manage SQL Server cursors.
We think this approach offers a big benefit in that we are able to use our Audit table as a recordsource for the Excel Pivot Table. Time will tell if the ability for an end user (i.e. the manager who oversees this unit) to get to the audit data in a format that he finds familiar is worth the work that went into designing and implementing. We do know that our primary end user likes the concept.
So, a question for you. How do you go about implementing auditing requirements in your applications? What are the pros and cons? In this case, for example, we moved the entire process to the End of Day, partly to avoid the additional overhead of adding the audit during regular daily data input. However, there is a downside to this approach. We capture only the LAST transaction for any given record on any given day. In a different business model, a more detailed audit trail might be more appropriate.
George
