In an ideal world you would design your applications so that your users could never enter bad data. Well, that’s not always possible for some technical reasons and for 3rd party apps you’re at the mercy of the vendor.
One case in point is our Student Information System (SIS) at the Val Verde Unified School District. It does a great job and we love it, but as a developer it does have its cons. The database isn’t as normalized as I would prefer and sometimes their data model just seems weird. Granted it is a Microsoft Access UI that links to a SQL back end, however, it still doesn’t change the way things are.
In this case we need to perform audits on our data. Anyone who is in data management knows that if it’s possible to enter an invalid value (such as a 5 digit phone number) some user will do it someday. In some cases bad data is rare and other cases just plain prevalent. The other issue is how that impacts your organization. Sometimes the invalid data will just be informational and not a big impact, however, in other cases this can impact reporting, automated processes, data exports/imports and many other systems.
So, I came up with our current data auditing system. We started it up back in June, 2006 and it’s been a great help. We currently have 60 checks on our SIS that run various times of the day. Each check is really just a simple SQL statement that looks for errors and stores these in a violations table. After each job a report with the results is sent to the relevant users. Typically we have only a few jobs, most of which run at night and by the morning staff have the latest error report in their email.
This has been a real labor saver as data entry errors are caught within 24 hours. Those who are responsible for the data usually have the information fresh in their minds or the reference paperwork readily at hand. We have to send various reports to the state several times in the year. In the past, before we had audits, many of the reported fields were not actively maintained and we would get thousands of errors that needed to be corrected before we submitted our reports. Now, the errors are corrected by the next day. When it comes time to submit the reports it’s a much faster and less stressful process.
Currently our audit system is in a very 1.0 stage. It only runs on our SIS data since that is what it was originally created for. Also, it’s entire maintained in SQL by our database managers (3 of us). There is no UI at all. It works, and is great for a 1.0 venture if I am allowed to say so. But it’s definitely time for a 2.0 version.
Here are just a few features I’m looking to put into the 2.0 version:
- Easy to use web UI
- Make use of ASP.Net security and roles
- Able to audit ANY data through various data providers:
- SQL
- Active Directory
- Exchange
- File System
- IIS logs
- Etc.
- Better reporting system
- Easily setup generic audits on fields such as:
- Non-empty
- Alpha
- Numeric
- Real-time audits (such as using triggers in SQL)
I’m thinking about putting this on CodePlex once I have the basic framework up and running. I have no ETA on this project since it’s rare that we get a lot of “free” time to work on things like this.