3 Solutions to Ending Data Chaos: Spreadsheets vs. Off-The-Shelf Solutions vs. Custom Web Based Applications
A new line of business is introduced in your company and you’re in charge of making it a success. There’s a mountain of new data that you now have to track that didn’t exist before. Things like, client demographics, service requests, process flow info and sales data. Your IT department doesn’t have the time or resources to respond to your requests so you do the do the only thing you can do. You open Microsoft Excel and start typing away, problem solved!
Things start to get a little complex so you add a few more tabs and even create entirely different spreadsheets to manage different types of data. You have one for clients, one for new orders, and one for tracking where the client is in the service process. You know where everything is and this seems like a big help. Then you realize that when working on service orders you have to be able to see the related client info on the same spreadsheet tab at the same time. That’s easily solved by a little cut and paste, right?
Then managers from other departments start asking for information contained in your spreadsheets so you start emailing the spreadsheets around. You quickly discover the dangers of having other people updating your beautify-styled spreadsheets: entire parts get destroyed by mistake. To prevent this you have been unanimously elected as the “Spreadsheet Traffic Cop”. Since you were the one who created this, it’s now your job to integrate everyone’s changes into the master copy and redistribute it.
Remember that duplicated data? A client changed their phone number and you realize that you need to update it in 13 different places, more or less (you can’t quite remember all the places you copied the data).
Now the executives are starting to ask for reporting on the new business line. You find it next to impossible to give them what they want without spending hours cutting and pasting, a process that is both time consuming and error prone.
Welcome to the world of spreadsheet chaos. This is only the beginning of your problems.
A trend we are seeing is that creative corporate employees cobble together departmental solutions based on tools they are familiar with such as Excel, Word and Access. Why? They don’t have to get approval or funding to have the IT department build it. If they asked, the answer would either be “no” or “get in line and we’ll have it done in a year, maybe.” Internal IT departments are often the problem, not the solution. They’re frequently understaffed and underfunded. The IT director becomes a master firefighter spending most of his or her time putting out fires and only working on the most mission critical applications. Small departmental solutions fall by the wayside and are left to the employees to deal with themselves. And left to their own devices, creative employees *will* create a solution, albeit a not so well thought out solution.
These solutions are adequate in the beginning when there’s very little data and they solve an immediate problem. But over time, as the amount of data grows and the differing needs of the department grow, these homegrown solutions no longer meet the needs of the department or the organization as a whole. They become increasingly difficult to work with and get downright unwieldy. At some point in time they become a hindrance to the growth of the organization.
Using Excel as a Database
Spreadsheets are great tools. They allow us to put together columns of numbers with labels, do summations, complex calculations, data analysis and forecasting. They can even produce pretty graphs! But what they’re not good at is storing data, managing the relationships between that data and getting critical business intelligence to the right person.
An example of this problem with spreadsheets can be found at one of our clients who specializes in laying gas pipelines. The foreman in the field fills out a scrap of paper that gets sent back to the corporate office. That paper is then entered into a series of spreadsheets and later some of the data is reentered into their accounting system. It takes them weeks to prepare an invoice after a project completes. As you may have heard, the gas industry is growing by leaps and bounds so this process is busting at the seams. Management has a difficult time knowing what’s going on in any given project. Valuable data is spread across multiple spreadsheets making any type of reporting either impossible or very cumbersome at best.
Spreadsheets vs. Custom Web Based Applications
Let’s take a look at two strategies for managing data and contrast the differences. After reading this you will be able to better understand the solution that is right for you.
The following is broken down by major functional areas you must consider when comparing these two approaches to managing data.
Spreadsheet – The free form nature of a spreadsheet means everything is editable. If your elbow hits the keyboard when the cursor is on some random field, you could wipe out a value and not even know it.
Custom Web App/Database – The database cannot be directly accessed by the end user. It can only be modified through a web application user interface according to very well defined rules of access written into the application.
Spreadsheet – Any time you duplicate data, weather in a spreadsheet or in a database, you create a maintenance problem. Each copy of the data runs the risk of being out of date with the other copies. To effectively update the data you must find every occurrence of a given data item even if it’s stored in separate spreadsheet files. That’s both time consuming and error prone.
Custom Web App/Database – A well designed database will have little to no data duplication in it. This also lends itself to ease of reporting and flexibility to expand in the future. A solid database design is the foundation upon which all great applications are written.
Spreadsheet – Typically a spreadsheet is file based, meaning that only one person at a time can view or edit at a time. Sure it can be put on a central file server but one person may save his or her changes over another person’s edits. If multiple people need to view and edit the data at the same time it’s next to impossible to scale up that process. Non-file-based spreadsheets, like a Google docs spreadsheet, allow multiple concurrent users but they still suffer from a lack of control over the data, i.e. one user can wipe out another user’s changes.
Custom Web App/Database – Industrial strength databases such as SQL Server are designed to handle a high volume of concurrent users. Likewise the web applications built with ASP.Net or PHP that store their data in a database are also designed to handle multiple concurrent users and there can be logic built into the application to prevent users from stepping on each other’s data.
Risk of Data Corruption
Spreadsheet – Any data medium that is file based, such as spreadsheets, runs a greater risk of file corruption when saving, especially with multiple users involved.
Custom Web App/Database – A database provides a central location to store, secure, and control data. Data corruption in a SQL Server database is very rare.
Spreadsheet – Although there is a scripting language built into Excel, most users don’t know how to use it to do data validation. Also, data validation can be more complex than just verifying that you didn’t put text into a numeric field. For example, limiting a user’s response to only a limited number of options based on some business rule, such as in a dropdown, is a common validation task.
Custom Web App/Database – Data validation can be placed both in the web application logic and in the database itself to prevent bad data from ever getting in and to enforce the business rules.
Spreadsheet – Again, the fact that a spreadsheet is file based allows someone with a thumb drive to grab a copy without anyone ever knowing that it was taken.
Custom Web App/Database – The only person who can directly access the database is the database administrator so the data is much more secure. For even more security, the data in a database can be encrypted.
Spreadsheet – Extracting relevant data from a spreadsheet and organizing it into a report is cumbersome. If you need to aggregate data across multiple spreadsheet files it becomes extremely difficult.
Custom Web App/Database – Reporting in a web application is a breeze because standardization of the data has been enforced in the database. Complex data relationships can be “joined” on the fly to create very complex reports. Bottom line, if the data is in the database and you have a sound database architecture, you can create just about any type of report you can think of.
Spreadsheet – Often decisions about what to ask for next are dependent on previous data entry. This process of moving the user through a sequence of intelligent steps, only showing the user what is needed for each step, isn’t really possible with a spreadsheet.
Custom Web App/Database – This is where web applications shine. The business logic built into a custom web application allows the developer to guide the user through each step of a complex process only showing the user what is necessary for that step while validating data entry along the way.
Spreadsheet – Spreadsheets are hard enough to concurrently share amongst people as it is but now throw in the need to display your data on a mobile device and you can pretty much forget it.
Custom Web App/Database – Access in Web applications can be designed specifically around “user roles” where the developer controls what type of user sees what type of data. The shop floor worker may see a different set of screens than the department head. Since mobile devices have browsers built into them it’s easy to extend the application to employees in the field by creating a mobile responsive design where each screen in the application automatically reconfigures itself to work on the mobile device. No more filling out paper forms in the field!
A Third Solution: Off-The-Shelf Software
Off-the-shelf software solutions are great as long as your business process exactly matches the vision the programmer had in mind of how your business is supposed work. This software is designed to have a broad appeal and function as the least common denominator across a number of industries. So it may solve 80% of your business need but that last 20% could be a real show stopper. Also, applications that are designed to be generic tend to have feature bloat; a thousand options that you will never use that only serve to complicate the user experience. Future expandability of the system may not be possible. Likewise integration with your existing legacy systems may not be possible unless the software vendor has an incentive to do so (i.e. being able to sell that same feature to other clients).
Choosing the Right Solution for Your Business Need
Returning back to the gas piping contractor example, what was the solution to their spreadsheet chaos? They decided to go with a custom web application. Their business rules were so complicated that no off-the-shelf application could capture and organize their data. Their rules even differed from client to client depending on the terms of each client’s contract.
The first step was to sit down with a business analyst and figure out exactly how the company operated. There were hundreds of business rules that just a few employees had in their head concerning how to manage the data on these spreadsheets. All of these rules had to be fleshed out and documented in a specification for the programmers.
As the rules were being defined the data entry screens were designed concurrently so we could get stakeholder input while the business rules discussion was still fresh in mind. A flexible database architecture was developed that would allow any type of reporting and allow for easy expansion of the system in the future. As the screens were designed, extra attention was given to make them automatically reconfigure themselves when being viewed on mobile devices so the foremen in the field could easily interact with the system. This gave management the flexibility to generate up-to-the-minute reports showing exactly what was going on in every project as well as the ability aggregate data across projects, something that was never possible before with the spreadsheets.
- Spreadsheets are great for what they were designed for; number crunching. But if you have lots of data, users and business rules they will become a hindrance in the long run and should not be used as a substitute for a real database.
- Unless you are prepared to retool your business process to match a given software package, an off-the-shelf solution may not be the right approach for you either.
- The maximum amount of flexibility to get exactly what you need to grow your business will only come from developing a custom web application. This approach puts you in the driver’s seat and gives you the ability to custom tailor a solution that exactly meets the needs of your organization.