Tuesday 31 October 2023
Case Study: Digital Transformation of Complex Engineering Spreadsheets into a Modern Web-App
Digital transformation projects often reveal the power of leveraging standard tools within a modern cloud architecture. In our work with Threepwood Consulting for the Energy Networks Association (ENA), we moved their intricate Excel-based PCB statistical model to the cloud, improving its efficiency, functionality and security.
The Shortcomings of the Excel-Based System
ENA's previous system revolved around an Excel master spreadsheet that was cumbersome, prone to data loss and file corruption, and incredibly slow in performance. A single technical person was responsible for collecting, cleaning, and inputting data from various members. Once the data was processed, the results were manually circulated among team members, an additional step that consumed valuable time.
Transition to Cloud-Based Architecture
We migrated this to a cloud-based solution using SST (Serverless Stack), React for the front-end, and Google BigQuery for data storage. SST is an open-source framework for building serverless applications. It's designed to work on top of AWS's CDK, allowing for the use of familiar programming languages like TypeScript. In this context, SST provided several advantages. It simplified the deployment of serverless resources, enabling us to focus more on business logic rather than cloud infrastructure intricacies. Its compatibility with existing AWS services also made it an excellent fit for this project.
Architectural Components and Testing
The architecture involved using DynamoDB tables for storing metadata and Google BigQuery for the main data model. The complex formulae from the Excel model were translated into a TypeScript component, allowing for the exact replication of the original model's calculations.
We adopted a robust testing strategy focused on unit tests. Leveraging real examples from the Excel model, we created targeted tests both for the entire model and for its individual calculations. This ensured that the TypeScript-based model was not just an accurate representation of the original Excel model but also one that could be verified for correctness through automated tests.
Improved Operational Efficiency and Security
In the new system, data can be uploaded by any team member through a dedicated uploader, eliminating the need for a single point of contact. Results are generated and made available at the click of a button. Added to this is the implementation of multi-level access controls (Admin, Team-Admin, and User), enhancing security and offering customised views based on user roles.
Conclusion
The project wasn't about groundbreaking innovation but rather about the practical application of modern cloud-based solutions to real-world challenges. By using SST and standard cloud tools, we managed to transform a cumbersome, error-prone Excel model into a more efficient and secure cloud-based system. The use of targeted unit testing assured the reliability and accuracy of the new model. It serves as a textbook example of how standard tools in a modern cloud environment can offer substantial benefits for digital transformation projects.