The story of the missing Covid tests

Over the weekend you couldn’t get away from the news that nearly 16,000 positive Covid test results went temporarily missing, causing a spike in the weekend positive test figures and a fair amount of embarrassment for the government.

But how do you just lose data and find it again? It seems that the test results were being stored and managed in an Excel spreadsheet. Excel spreadsheets are often used to store structured data because it is graphical, simple to use and can manage fairly large data sets. Typically Excel can store ~1m rows and ~16k columns. There are tricks of the trade that allow you to expand these numbers with the use of linked data and pivot tables, but then you're getting into the wizardry and magic that is beyond the realms of the simple use case.

Using Excel allows you to rapidly define the rough structure, querying and reporting requirements of the task at hand, but does not provide much in the way of stability, maintainability or scalability should it grow. One incorrect formula or one hidden row in your spreadsheet can cause havoc; I’m sure most of us have spent at least some time trying to understand why something in Excel just isn’t adding up.

This begs the question why Excel was chosen for such data reporting and analysis when dealing with one of the most important and critical issues in generations? During the planning phases of the NHS Track and Trace initiatives they will have identified technical requirements and I'm sure this will have been raised as a critical point of failure given the testing targets that were being proposed.

Databases and storage mechanisms

There are a multitude of more suitable storage mechanisms for such data with a plethora of database engines available to choose from based on the exact requirements. These will have been detailed in the scoping process and if there was an agreement to use Excel in the initial phases, there should have been a concrete plan to migrate to a more suitable system long before the limitations of the current system failed.

Defining risk and mitigation planning during scoping

Defining a risk and mitigation plan is an important part of the scoping process, having a clear understanding of what may go wrong allows for extra steps to be taken to make sure everything runs smoothly. A roadmap can be put in place to upgrade systems when data gets to a particular level, maintaining a robust and reliable system.

We work closely with our clients at the scoping phase of projects to define these issues and ensure solutions are planned and mapped out for potential risks. Scoping is vital to make sure a proposed system is adequately designed and robust enough to grow as the extent of the technology widens.

To read more about the scoping process we go through at Mashbo take a look at our Importance of scoping article or contact us via studio@mashbo.com or call 0151 708 1924.

BuildThings