You could almost see this as an addendum to the “Excel is not a database” and “Why Excel should not be used for business reporting” blogs. It is also a real life example of how updating a customer is automatic and requires no manual intervention when using a database, but is virtually impossible, and definitely very time consuming if done using an Excel spreadsheet.
I used to have a system for recording complaints by extracting data from the CADS mainframe system. In parallel I also made an extraction of the current customer list from a different mainframe system. A daily automated extract was all that was needed to make sure that the customers from the complaints database matched the list of current customers.
In addition we also had a manually maintained customer list SQL table which contained more extensive customer-related data. If necessary this table could be kept up-to-date through a web-based entry screen, or block changes could also be made behind the scenes using customised SQL commands.
Compare this with the system in place for maintaining a customer list in support of Supply Chain Transformation (a separate customer list was deemed necessary since the customer groupings were specific to the needs of this project): you received an email letting you know that a number of unspecified changes had been made to the Excel spreadsheet which resided on a Sharepoint server.
Now how I had handled the initial SCT customer list was to extract the contents of the initial Excel spreadsheet into an equivalent SQL table and used this table in combination with my complaints table to create a listing of complaints with their specific SCT category. However, since the changes to the customer spreadsheet were not specified I had made up my mind that I was not going to waste any time on figuring them out, and let users flag up any gaps that might appear as a result of mismatches between my complaints database and the now outdated SCT customer list.
I could now make the required changes necessary to fill the gap in the customer list. It works, after a fashion, but it can hardly be considered an elegant solution – and besides, it always requires someone to step in and make the manual alteration. Now imagine if the SCT customer list had been held in a database: at worst I would have to perform a daily extract from Oracle to SQL, but since this is an automated process, the SCT customer list would always be up-to-date without the need for any manual intervention apart from the first one needed to alter the records in the Oracle table.
There couldn’t be a better example of how spreadsheets always need to be shepherded along, whereas a database runs automatically once set in motion. Being essentially a lazy person, I prefer the automated option.