United Kingdom VI: Summer Lovin'

[QualityChimp]

what's important is they realised the error and have corrected for it. I'd be more concerned if this story came out and only then did they admit there was an issue, adjusting the numbers because they were forced to.

Don't forget the other half of that story though, Martino. Who is "they"?

[MartyG]

They who published today's figures I suppose, which haven't needed to be correct (yet!). They don't make for pretty reading.

12,564 new cases, 250,348 tested so that's 5% of tested now, this time last month we were at 1% of tested.

[Hirst]

Regardless of what the fault is, what's clear is that the manual audit checks to ensure the figures were correct either didn't exist or weren't being done (likely due to time constraints). I've been doing back end finance for yonks and half my job is making sure the figures presented in front of me by the computer are actually correct.

When 99% of the time they are right, it's easy to think "sod it, it's right" when it's near the end of the day and it'll take two hours to do it properly. It wouldn't surprise me at all if it was as simple as that.

[Neon Ignition]

Best part of todays figures is no backlog is in them meaning it's still spiralling out of the Tories control. 15,000 by weeks end?

[MartyG]

Regardless of what the fault is, what's clear is that the manual audit checks to ensure the figures were correct either didn't exist or weren't being done (likely due to time constraints). I've been doing back end finance for yonks and half my job is making sure the figures presented in front of me by the computer are actually correct

When 99% of the time they are right, it's easy to think "sod it, it's right" when it's near the end of the day and it'll take two hours to do it properly. It wouldn't surprise me at all if it was as simple as that.

The error was spotted, so someone was eventually checking them, but I wouldn't be surprised if that was closer to the truth, an export of data that errored and wasn't properly checked.

There's an article in the Evening Standard with a paragraph about the error being with the export of data the from Track & Trace system to PHE who do the stats, at least according to "an official government spokesman" - as I posted previously, these public bodies use Excel for exports all over the place.

No software developer** is going to design a system that uses an Excel spreadsheet as a datasource of this size for a multitude of reasons, the biggest one being it'd be incredibly slow to do any kind of CRUD work and it'd be completely memory bound. And let's not even get into concurrency issues.

**if a spreadsheet was used as the main datasource, no developer was involved.

[Hirst]

It turns out it is something more dumb - they used the old .xls format from Office 2003 and older which only supports 65536 rows. Great work losers!

https://www.bbc.co.uk/news/uk-54422505

[Golgo]

Where is the autodidact AI and systems genius Cummings in all this, the blogger's answer to Alan Turing? Isn't he the man who's ambition it is to deliver government-by-ARPA?

[Hirst]

The human version of Cybernet is probably waiting for the opportunity to use this failing as a reason to have it outsourced to some shell company owned by his family.

[QualityChimp]

Embedded Tweet

[Lebowski]

The error was spotted, so someone was eventually checking them, but I wouldn't be surprised if that was closer to the truth, an export of data that errored and wasn't properly checked.

There's an article in the Evening Standard with a paragraph about the error being with the export of data the from Track & Trace system to PHE who do the stats, at least according to "an official government spokesman" - as I posted previously, these public bodies use Excel for exports all over the place.

No software developer** is going to design a system that uses an Excel spreadsheet as a datasource of this size for a multitude of reasons, the biggest one being it'd be incredibly slow to do any kind of CRUD work and it'd be completely memory bound. And let's not even get into concurrency issues.

**if a spreadsheet was used as the main data source, no developer was involved.

The money thrown at this and their using excel badly is what worry's me, the cost of this data work was 36 million and its a system based on a ****ing excel spreadsheet. This just stinks of quango and jobs for mates again where the jobs gone out to the least qualified people for the maximum amount of money.

Is nobody else bothered by how corrupt our government has become on one hand you have the public throwing money at pensioners walking around their backyards and on the other we have billions transferred to private company's with zero consequence when that money goes missing or has been found to have been spent recklessly or fraudulently.

[MartyG]

It's not a system based on spreadsheets - it turned out to be exactly what I thought it would be, a data export that exceeds the Excel limits. The spreadsheets are an intermediary, something they were unlikely to have seen in testing because they probably didn't limit test it. If they'd have used the latest excel format it wouldn't have been a problem. The use of Excel templates would be to ensure that the data is in a consistant format so that it could be imported at PHE without having to have a dozen or more different workers to handle different files and formats (something that's far more likely to produce data errors).

Itermediary formats are used all the time in software systems - even calls to APIs use a JSON intermediary - it's just a way of passing data between gapped systems.

[Lebowski]

It's not a system based on spreadsheets - it turned out to be exactly what I thought it would be, a data export that exceeds the Excel limits. The spreadsheets are an intermediary, something they were unlikely to have seen in testing because they probably didn't limit test it. If they'd have used the latest excel format it wouldn't have been a problem. The use of Excel templates would be to ensure that the data is in a consistant format so that it could be imported at PHE without having to have a dozen or more different workers to handle different files and formats (something that's far more likely to produce data errors).

Itermediary formats are used all the time in software systems - even calls to APIs use a JSON intermediary - it's just a way of passing data between gapped systems.

Smarter people than me have pointed out that incompetence is to blame here and lots of data professionals have wadded in to that effect too. the data import didn't fail it was that the document reached the maximum number of columns. Everything was being held on one document, its not about importing data into one system they where using one excel document to hold "ALL" the records and then passing this document to public health England. If they had been using database software to compile the results then they wouldn't of needed to put everything into one excel document.

[Hirst]

I agree, there would have been no problem using Excel if they hadn't been using old .xls files. It's a solid enough program and is likely to already be installed on every PC that is likely to need to send/receive files.

What I'd like to know is why they were using .xls files in the first place - it would have stuck out like a sore thumb to me and I'm hardly a systems expert. There must have been at least several systems people who set it up in the first place and it's mad that someone didn't go "er, why are we setting them up in the old .xls format?".

I honestly wouldn't be surprised if it came out that they did it like that because some of the machines out in the hospitals or wherever are still on Office 97.

[MartyG]

Smarter people than me have pointed out that incompetence is to blame here and lots of data professionals have wadded in to that effect too. the data import didn't fail it was that the document reached the maximum number of columns. Everything was being held on one document, its not about importing data into one system they where using one excel document to hold "ALL" the records and then passing this document to public health England. If they had been using database software to compile the results then they wouldn't of needed to put everything into one excel document.

No, that's not correct. There are text files that come in from the tests daily, it's the daily files that are then exported into the single spreadsheet for import at PHE into their central system. That system holds all the data and it won't be using a speadsheet at its data source. that will be on a proper database for the reasons I outlined earlier (speed, memory limits, concurency).

Importing all the data every day from day zero is both time consuming and unnecessary. It's a daily import of the combined daily files. It is the process of the export to Excel format from these daily files that exceeded the Excel limits. The templates had an effective limit of about 1,400 cases - if it was the entirety of the data that was held in them, then the system would have failed long long ago.

The other thing that tells me this is a daily import, not a full dataset import every time, is the historical data points do not get corrected - when there are errors, they get added or subtracted from the current day totals, if it was a full data import they'd be able to correct the historical data points (and that would be preferable for the government as you wouldn't have a massive case spike on your charts when you have to do this).

The file is as I said, an intermediatry to allow ease of import into the central system, which is updated DAILY.

I AM an expert on these things, this is literally what I do, day in day out. Analyse, design and code systems like these.

[Dogg Thang]

Marty... did you do this?