Jekyll & Hyde

Over the years I have come across companies who end up with a huge reliance on spreadsheets to manage critical functions of their business. Unfortunately spreadsheets were never intended as a replacement for properly designed database systems yet it doesn’t stop people trying. It is common to see spreadsheets being used in the budgeting & forecasting process as well as time recording, invoicing, resource & capacity management and so on.

It is understandable that this happens, it is very easy to start a new spreadsheet and in no time at all someone has what looks like a good system. It is a quick fix but people love it because it solves an immediate need. Very quickly a department or team come to rely on the spreadsheet and overtime more people add new functionality to it because it is so easy to do.

Then it starts to get ugly. Within no time at all there are multiple “versions” of the spreadsheet but no control, no security, no support, no documentation and no change control. Worse still there are often individual spreadsheets per week, per client, per team and suddenly all the information the business needs is spun across hundreds of spreadsheets. It only when someone starts to look for a report that trends the information over time or filter it for a particular project that people realise that they just can’t do it. 

Inevitably the whole “system” has grown into a monster, too large for anyone to understand it or maintain it. Comprehensive reporting (or Business Intelligence), if even possible, is very difficult to do due to the multiple versions, different structures and inconsistencies between spreadsheets.

Another major issue with using spreadsheets this way is the significant reliability problem. Research studies (Stephen G. Powell, Kenneth R. Baker, Barry Lawson (2007-12-01)) estimate that 94% of spreadsheets deployed in the field contain errors. I have seen spreadsheet “systems” that were used to determine weekly payroll commissions and bonuses which were far from accurate. Sometimes an employees bonus had another nice unexpected bonus on top of it. But it was impossible to diagnose the system issue when it involves hundreds of spreadsheets all with their own formulae and equations. Worse still, the employee who was the spreadsheet wizard and had originally built all the spreadsheets had since left the company.

It is important that we understand when to use spreadsheets but more importantly when to stop using them before you hit the problems outlined above. Spreadsheets are great for end-user data analysis and to an extent for prototyping new systems. But just keep an eye out for the tell tale signs that you are beginning to see the start of the spreadsheet monster. It is possible to replace spreadsheets with a proper database system and it is something I would strongly recommend.

dilbert on databases (c) Scott Adams, Inc./Dist. UFS

Part of what I do for a living is building information systems. These systems, generally web based applications, solve business needs such as time recording, billing, forecasting, expense management and so on.

I thought it might be useful to give some pointers on how you can start learning exactly how to build a web application. I’ll provide an approach on where to start, what good learning resources there are and will show sample code along the way.

Obviously there are a lot of different technologies you can use to build web applications. I use Microsoft technologies such as ASP.Net and SQL Server (a database server).

Don’t get too bogged down just yet on the specifics. I am going to start at the beginning, the core of any web application…the database and in particular SQL (the code that is used to talk to a database).

What is a database?

The database is the most important part of a system. If you understand databases and sql you are well on your way to building a web app. This is where all the data is stored. You need to be good at dealing with the data, know how to use it correctly and ensure that the database is designed correctly (very important). If you don’t then you may as well be a Formula 1 mechanic who doesn’t understand engines. No matter how flashy and aero-dymanic the cars bodywork is it just isn’t going to perform or win any races.

Databases are everywhere. So, for example, when you use your online banking all the transactions you see on the screen were retrieved from a database and displayed on screen. You can use SQL to retrieve that data and also to update any changes to that data back into the database.

Here is a very simple SQL statement that would retrieve your transations in your online banking.

Select TransactionDate, Description, Amount
from Transactions
where CustomerID=340

As you can see SQL can be very simple and easy to read. The above statement is asking the database to retrieve data from a table called “Transactions” related to a CustomerID of 340. A table is a bit like a single spreadsheet where all the data is stored in rows with each column representing a piece of data. In this case the spreadsheet would have 3 columns, one for TransactionDate, one for Description, and one for Amount. Now, please be aware that some database people will get upset by comparing a table to a spreadsheet. It is a purist thing but to help you get some analogy into your head it is a good comparison for starters. 

It goes without saying that the above is a very simple example and there are lots of very long books written about just partial aspects of databases. But don’t worry, you are just starting out and you can learn more as you gain experience.

So, what are your next steps?

• Read about what a database is (search google for “What is a database?”)
• A good SQL book (one I used in college) is LAN Times Guide to SQL. This is a good place to start.
• Check out these useful resources from Michael Lang (lecturer in NUI, Galway)
• Start learning SQL, W3Schools has an online tutorial and demowhere you write SQL.
• There are other beginner books on Amazon such as Databases Demystified

In a future post I will cover more on databases and SQL. Just remember that databases and SQL are absolutely core to building systems. You need build up your skills in this area first.