Excel Is Not a Database (Part 1)


In the first year after the Operational Research team was formed, there was this person (if you’re reading this, you know who you are, Mike) who was the owner of a vast and “sophisticated” Excel spreadsheet, from where various items to do with raw material usage at the blast furnaces were controlled. Everybody apart its loving owner agreed that we should replace it with a system that was more transparent and not depending on one person overseeing this unsightly mess – although we didn’t tell it to his face, we weren’t that cruel.

Still, in the final year prior to his retirement it became a running joke that at every monthly meeting, when we reported on our highlights from the past month, he declared how his system was now even more automated than before, and only required one click instead of three before the change – in short everyone knew when a turd was being polished. Even then, he had the audacity to refer to his spreadsheet as his “database” !

Fortunately he was not around to see how soon after his retirement his sophisticated system fell out of favour, and was dropped. The main reason ? It took the person who attempted to look after the system several days a week to even produce some useful feedback – in the end the effort could not be maintained, and another, more simple means of controlling raw materials feed for the blast furnaces was adopted.

Unfortunately he was not the only one who produced this type of sophisticated spreadsheet: I suppose it gave the person in charge great satisfaction to have all its functionality at his fingertips. The problem was just that: usually there was only the one person in charge of the spreadsheet who had a full understanding of how everything fit together, and although useful, the amount of time wasted in getting the spreadsheet to crank out its desired output had to be seen to be believed. I used to quip that every graph or report handed into senior management for their use should show the number of man hours it took to produce it. Maybe then the BMT would not be so cavalier about time spent serving sometimes frivolous reporting needs if they saw how much time otherwise would be available doing more productive tasks.

But to return to the concept of Excel as a database: there is no such thing !!!

A database may hold data in a format suitable to facilitate reporting, but unlike spreadsheets the data layer and the display layer are not inextricably linked. Meaning that if you want to create a variety of reports based on the same set of data, you don’t have to start creating new worksheets and linking cells, and basically rearrange your data layer to produce a new report. But for some reason or another, people accustomed to spreadsheets fail to appreciate this glaringly obvious discrepancy. In fact I’d say that those who have grown up with spreadsheets have to unlearn everything they know about them and acquire a new look-out on how a set of reports fed from the same database, but not physically linked with it, reduces maintenance times to a fraction of that required for a spreadsheet (after the initial development phase, obviously).

The problem with spreadsheets is that they’re easy to use and it doesn’t take long to become quite proficient getting results from them. What people often fail to notice is that it’s also a limited tool, because in the end “it does the job”. But the cost is one of those creeping poisons: initially it’s something that you can handle, but as the requirements change and new functionality needs to be added (thereby creating the sophisticated monster some people were so proud of) it becomes an all-consuming entity that fills people’s time and engulfs their PCs with gigabytes of historical data.

The more I started to think about it, the more I felt that I needed to highlight the unacknowledged evils of maintaining business systems using Excel spreadsheets. First I created a wiki article in the Strip UK wiki entitled “Excel is not a database”, and when I got wind that the BICC intended to investigate Power Pivot as an alternative to proper databases, I created a presentation entitled “Why Excel should not be used for business reporting”. But that will be part of further blog entries.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s