You would clean up the physical files…. Many of the same problems exist in the database. Incorrect information, incomplete information, missing required information, not enough metadata to classify, corrupt data, out of date information, incomplete deletes, etc… Trust me, the database is not perfect. So, how do we cleanse? There are several levels to a database cleanup.
First, have some reports written. Reports that show data exceptions for empty names or record types, out of range dates, a number too high or low, account numbers not the right length, etc… Think through every field and all combinations. If you have ever converted a database think back to what the exceptions were. It’s the next data conversion you are preparing for while managing the records correctly today. The database will out-live the product currently managing it!
The next step is to do some searches. Audit what you think should be in the database over a period of a year, audit multiple clients or accounts. See if this matches your billing systems for how many records/years you should have for them and if it doesn’t clean up the errors.
Bottom level – NetTrax.NET has a powerful Saved Queries utility that will let you write custom SQL queries to run against the database. Not technical enough for this? Have a resource from I.T. sit with you and do it with them. Sort each column one at a time – 90% of your clean-up will be blanks and special characters that sort to the top or bottom of each column. And you can remove or repair at this level of connectivity whereas the application running your database simply can’t query or edit this way. Don’t have I.T. resources that can help you with this? No problem, contact a representative from Bradford Systems today to help. We can provide hourly rates for our experienced database developers to write queries and even come on-site to assist with the cleanup. This hourly approach lets you use our resources as little or as much as you need.
The lesson here is to cleanse the data as often as you would clean up your physical files, or maybe even more.
Link Bradford