Matt Penner

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.

Back in the good old days, when MS Office simply was Word, Excel, PowerPoint and Access, we all knew what Office was and why we needed it.  It was similar with other market leading programs such as WordPerfect and Lotus 123, Photoshop, QuickBooks or PeachTree, etc.

By my list of software above you may be trying to figure out what I mean by "way back when".  Let’s pinpoint this time period as cerca 1995.

The world was great.  Well, not great, but it was less confusing.   For the general consumer there was only a few operating systems to choose from, one or two office packages, etc.

Back then we knew we needed Microsoft Word or Word Perfect.  We knew we needed Excel or Lotus 123.  If we wanted to know how to use it we bought a book, took a class, or (gasp!) read the manual.  Yeah, manuals were actual paper books bundled with the software back then too.  This meant that companies could charge for in-depth books and training programs for their software.  It was the "if you build it people will pay to learn how to use it" era.

Now come back to the present.  It’s a lot more complicated.  Do I use Office Home and Student, Standard, Small Business, Professional or Ultimate?  What about this Open Office thing I keep hearing about?  What in the world is Groove, InfoPath, OneNote and why do I care?  People keep telling me that OneNote changed their entire work process.  How?

Notice I’m just sticking to the MS family (except for the Open Office reference I threw in there).  It seems like with every new version Microsoft throws a new product or two into the Office family.  If I were to list all the non-MS alternatives this post would be a mile long.

So, what’s Microsoft’s answer?  As you probably have noticed much of the Office website (and really any major Microsoft product site these days) is mostly devoted to helping you understand what each product does and how to use it.  It’s still pretty confusing, but there is a wealth of how-to articles, videos, webinars, trial demos and sometimes even online interactive demos.  All free.

It’s not like the world woke up and suddenly Microsoft realized they needed to convince us why to buy their product.  That has always been the case.  It’s just now they are freely telling us how to actually use it.  That’s great. 

This is definitely more of an industry trend.  Most software companies offer free product demos, videos and training on their website.  In fact, if a new product can’t show me in a 5 minute video why I would want to use their software I tend to move on.

It’s nice to be able to spend a half an hour in the morning beefing up on a new product.  I’m trying to set this as a weekly (perhaps daily) habit.  Currently I’m a OneNote training junkie.