Spreadsheets are used as an easy way to catalog any data in a tabular format. Spreadsheets were originally developed in the early 1960's for financial applications. Spreadsheet applications, such as Lotus 1-2-3 and Excel, became popular in the 1980's and have been used for multiple scenarios. Many companies use spreadsheets to track important data that is relevant to their business. However, the limitations of maintaining your company’s data within spreadsheets become apparent as your data grows.
Although there are some advantages for quickly capturing data within a spreadsheet, the limitations of maintaining the data in a spreadsheet becomes clear as time goes on. One limitation is that the data can only be modified by one user at a time. Moreover, as multiple people edit a spreadsheet, issues arise when one person overwrites another’s changes. Furthermore, there is no easy way to track the changes or to maintain versions of the spreadsheet. Another limitation is that data entry is not typically governed by any rules and therefore a user can enter incorrect data.
Migrating the data from a spreadsheet to a centralized database offers many advantages such as security, availability, data integrity and scheduled backups. Utilizing a web application will allow the system to be secured and locked down based on permissions. Some users could be given permissions to manage different parts of the data while others could be given read-only views. Another big advantage of using a database driven web application over a spreadsheet is that the data can be viewed and maintained by multiple people at the same time. One user could be viewing a report based on the data while another user is updating data. Instilling data integrity is one of the most useful aspects of having a database driven web application. Business rules can be programmed so that a user cannot enter incorrect data. In addition, database integrity and scheduled backups would insure that the data will remain secure and available. Storing the data into a database also opens up opportunities to use that data in other applications. For example, you could use a tool like Microsoft’s Power BI to analyze the data and visually show patterns that would not be clear when you look at the data within a spreadsheet.
A few years ago, Hallam-ICS was implementing the design of a large Toxic Gas Monitoring System (TGMS) using multiple spreadsheets. A Toxic Gas Monitoring System is used to monitor for gas leaks within the equipment and if one is found the associated equipment is turn off and alarms are signaled to evacuate the affected area. The design of the system, that detailed how one piece of equipment is connected to another, was all being maintained within a few very large spreadsheets. An additional spreadsheet was also being used to manually generate a formatted report that the testers could use to verify the connections out in the field. The problem that the company was having was that the equipment was accidentally getting turned off. This problem was due to values being incorrectly entered within those spreadsheets or being copied incorrectly from one spreadsheet to another. It soon became clear that a more sustainable and accessible solution was needed. Hallam-ICS built a database driven web application that would allow users the ability to enter data which was governed by business and database rules. In addition, the report that was manually created each time in a spreadsheet was now automatically generated.
The impact of implementing this new system eliminated the issues that were caused by using the spreadsheets. In addition, the productivity gains were very substantial. Performing the task of entering the data to connect pieces of equipment in multiple spreadsheets, recreating the report spreadsheet and manually verifying the data would typically take multiple people multiple days. The new web application performed verification automatically and could generate the report on-demand. The process was able to be performed within hours compared to the manual process.
Now that the data was stored within a database (Azure SQL Server), other applications for this data became apparent. One such application allowed the Programmable Logic Controller (PLC) developers the ability to import the data into their applications. This had been a manual process before and was another area where it took a lot of time to complete and values could be entered incorrectly. A third application that was created that utilized the same data was a testing application that ran on a tablet. This allowed the Testing team to verify the physical connections of the equipment in the field. Once the Testers returned to the office and synchronized their verifications with the database, reports were then generated to document those findings.
The system has been running for five years and has continued to be enhanced with new functionality and business rules based on the changes that are happening within the plant.
Using spreadsheets to quickly gather and format data that is relevant to your business makes sense in many short-lived scenarios. However, as you become dependent on these spreadsheets in your daily tasks, the utility that spreadsheet offer quickly breaks down.
Converting your important spreadsheets into a database driven web applications enables your business to effectively use that data among multiple users and in multiple applications. Moreover, the data will be more secure, be validated by programmed business rules and allows you to expand on that data as your business needs grow.
For more information please reach out to our
About the author
Shawn has left Hallam-ICS to pursue other endeavors, but his contributions to the company continue to be valued.
About Hallam-ICS
Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.