8 ms·

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the pro

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now separate to the "backend". This nutter thinks we have time to write some unit tests and also wants to add an authentication module so we know who uploaded what. And somehow he thinks we need to add logging so we can ensure errors can be tracked and debugged. Amazingly he seems to be suggesting that there's going to be errors in our process.

matt123456789 6y ago
Add to this the understanding that getting this on contract will take a few months, when the boss was asking for these figures yesterday...
TeMPOraL 6y ago
That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentication module built in (a file sent from a government mail address). There's a risk of user errors, but the system will be working immediately[0]. Deployment can be done by anyone, as it's just clicking on File->New and starting to type data in. Meanwhile, a "pigsqueal" solution will take half a year to design, produce and deploy (that's in an emergency, a year otherwise). And that's with competent IT people, and not someone who wants to profiteer off the crisis. Then you'll have to train the users, and hope the reality won't necessitate any changes, because they will take a while. I think plenty of decent coders appreciate the fact that Excel is suitable for a surprisingly wide range of tasks, and that if you spot it widely used somewhere, it most likely means there isn't any comparable alternative available. -- [0] - Note that the user error that finally happened did not happen in the place where you'd expect it to.
- strbean 6y ago
  Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?
  - TeMPOraL 6y ago
    Extremely low performance. Obscure "sharing" workflow instead of obvious "mail a file". Cloud dependency that needs to be vetted for security (EDIT: and data privacy, as 'sjansen pointed out), and policies regarding cloud accounts on government e-mails. And in case of Airtable, also for budget. I'd be surprised if either of the above could handle 65k rows in a single table without becoming near-unusable.
  - sjansen 6y ago
    Probably not while complying with privacy laws. I would be much more upset to find out a governemnt employ on a deadline used Google Sheets or Airtable to share my medical data instead of an Excel doc on a secure government file server.
    - retbull 6y ago
      I don't really agree with much of google any more but I was at one point fairly involved in a project there. They do have some more secure systems for government use that isn't the crazy leaky ad cesspool gmail and docs is.
      - srtjstjsj 6y ago
        Where are the ads in Docs?
        monocasa 6y ago
        The docs are used for data into the advertisement systems. I've heard on the grapevine that several fortune 500 companies are on O365 because Google wouldn't disassociate docs from data gathering, even at the level of high touch high paying customers and they rightfully consider their internal documents to be part of their IP. Microsoft says "sure" and flips a secret "don't even gather stack traces on the server" flag for these customers.
jonplackett 6y ago
Come on, this is a government department of a first world country. They have databases for all sorts of things. They're not using excel for any other NHS databases, or the terrorism watchlist. There are plenty of people capable of making a good decision here. I think you let them off too easily by just assuming they're dumb. This a bad decision by people who definitely should have known better.
- ImaCake 6y ago
  Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.
  - Smoosh 6y ago
    And yet, I have first-hand witnessed a scenario like the one described in an AU government department. A small system developed by business (one person who had subsequently moved on) using Excel and Word and macros to keep track of information and generate letters to "customers" had become an essential tool, but unsupported and undocumented. When we (IT) recommended redeveloping it in Java with an SQL database (and documentation and testing and backups), they balked at the cost. Partly because the original person did the work "in their spare time", so the business never saw the actual costs. And partly because there are zero infrastructure costs with Excel and Word. There was very much a feeling that IT massively inflate the costs and will build a Rolls Royce when a Ford will do. It was a house of straw vs house of bricks situation, but they were happy with their straw house, they thought their only problem was that the little pig who built it was no longer there.
    - jonplackett 6y ago
      Was that system working on the single most important problem facing the country at that time though?
    - 6y ago
happytoexplain 6y ago
Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.
- Reason077 6y ago
  Excel can handle the volume of data just fine. The (very outdated) .xls file format with its 65,536 row limit was the problem here.
  - deleted 6y ago
    [deleted]
  - srtjstjsj 6y ago
    Excel will also happily screw it up and not tell you, as happened here.
  - jackjeff 6y ago
    This is the test and trace system for the worst pandemic this country has ever faced, six months in. I’m sorry Excel spreadsheets won’t cut it. This is a management failure. PHE has capable coders. And if not, they could hire some. The NHSX team has managed to write the dang COVID-19 app twice. Once not using the Apple/Google contact API (because management) and a second time properly. It’s all open source and it looks pretty decent.
    - tomatocracy 6y ago
      As an aside on the Covid-19 app - it is open sourced in the "occasionally throw some code over the wall" kind of way - eg it looks like the github was last updated on 24 September vs the app which has more recent updates. It's not clear they open sourced everything needed to actually build the app either looking through the issues. This is very very far from the only project doing that but disappointing nonetheless given the amount of public money which was spent on it.
  - 6y ago
untog 6y ago
This isn't really true. The people making the decisions will have more or less zero insight into the data format being used to store data, they'll just know that data is stored. IMO, a much more likely scenario: a brittle, haphazard system was set up with extremely short notice at the start of everything. One administrative snafu led to another, ran into "well, it's working so let's not touch it", and we got to where we are today.
tqi 6y ago
Your scenario makes it seem like these things are the result of incompetence or stupidity by people who aren't "decent coders". The reality is that for most use cases excel+manual processes are perfectly adequate/have little startup costs and what you're suggesting is grossly over engineered/require immense initial investment. The real problem is if/when scope begins to increase these processes are sufficient until they aren't, and the tools aren't flexible enough to gracefully manage the transition.
- nobody9999 6y ago
  >The real problem is if/when scope begins to increase these processes are sufficient until they aren't, and the tools aren't flexible enough to gracefully manage the transition. That's a good point. I'd also add that, at least in the US, we don't have any sort of national standard for such things. Each public health department (and there are >1,000 of them in the US) has their own set of processes and procedures. I'd expect that the CDC has a proper database system and that data from those 1000+ entities is collated into that system. However, state and local laws/rules require specific handling of such data and can differ significantly from state to state, county to county or even town to town. Attempting to stand up an integrated nationwide system isn't a bad idea. But doing so while trying to manage a pandemic isn't reasonable. That many places used Excel spreadsheets for storing testing data is neither surprising or necessarily a bad thing. Until March, no one needed to have huge databases of test results -- now we do. Complaining that places which had , at most, a few dozen cases of reportable infectious diseases should have implemented a database that can support thousands (tens of thousands?) of test results seems rather silly to me.
  - nobody9999 6y ago
    My apologies. I didn't read the article posted until now, and I see it's about issues in the UK and not the US (I was confused, as we had issues with data reporting in the US as well). My comment was focused on the decentralized US public health model and not on the UK's. My apologies for injecting analysis of a different problem into this discussion. As such, my prior comment should probably be down-voted.
leokennis 6y ago
Look, you’re right that Excel shouldn’t be used for this. But even then, Excel definitely could be used for this. We’re not talking big data here. We’re talking about a finite number of possible test result from a finite set of testing locations grouped on a daily basis. Let’s say 1000 test locations, where each location has a daily number of tests performed divided into “positive”, “negative”, “inconclusive” and maybe a few other options. That’s ~5000 data points per day. Should not be any problem. However, you need to properly plan ahead about how you design your spreadsheet, how to format the columns, how to keep it performant and how to not save it in some godawful 25 year old memory-dump dumpster fire of a file format...
hanoz 6y ago
The dubious wisdom of choosing Excel for this aside, how many people working in (1.) computing and (2.) statistics saw a file with exactly 65536 rows, with nary an eyebrow raised? And I mean the first time they saw it, not the second, third or fourth day in a row.
commandlinefan 6y ago
> another lunatic who says something called "pigsqueal" And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".
- higeorge13 6y ago
  rofl
javagram 6y ago
No need for a “front end” or “backend” or “pigsqueal”. MS provides a perfectly workable all in one tool, Access, for this kind of work. You can even use Excel as a front-end to access data if you want.
- taxcoder 6y ago
  I have spent plenty of time running Access programs for employers. You better have a Frontend and Backend if you want data integrity.
II2II 6y ago
A good part of the problem could be the timelines involved. With something like Excel, you have a large number of people who can put something together in a short amount of time and perform the needed analysis. Contrast that to a database. The first problem you run into is the limited number of people who actually have meaningful experience with databases. Databases ceased being consumer products about 20 years ago, so most people have only interacted with very limited front ends. They certainly haven't created a database, nor performed anything more than trivial queries. (Even then, they probably aren't thinking in terms of databases and queries.) Okay then, we are reliant on a much smaller number of skilled database administrators and developers. Do their tools facilitate rapid development? Even if they do have access to suitable tools, the process is going to be slowed by communications between the people who need the analysis done and the people who can actually perform the analysis. Needless to say that Excel, with all of its limitations is starting to look like a good option.
maerF0x0 6y ago
Exactly... > "But you wouldn't use XLS. Nobody would start with that." Actually almost every non-technical person I know that wants something "database like" ends up in google sheets or excel first.
golemiprague 6y ago
You didn't read the article, it seems that they tried to import csv files into a proper system but rather than importing them directly they converted it to an old format of excel which couldn't support the amount of record. I guess it was some microsoft based system and the developers were used to extract data from excel rather than just importing a csv text file as it would be in a more unixy environment.
dahart 6y ago
Your point is incredibly good, and made me laugh too. It illustrates precisely why people, both technical and non-technical, frequently avoid choosing the massive scalable hammer factory system architecture that takes five people three weeks just to get rev 1, over the little tapper that gets the job done today with one junior developer. From the technical standpoint of a decent (and probably only decent) coder, I think Excel is an amazingly sensible choice for technical projects for a bunch of reasons. I think decent coders reach for the database solution far too often, and waste ungodly amounts of time setting it up. Excel coming bundled with an amazing front end UI you don't have to write code for shouldn't be ignored. Sure it's error prone, sure it's a nightmare for data validation, sure it doesn't scale to Amazon sizes, but this little project will never get that big. (Yes, until, of course, it does.) I used Excel as the frontend for artists to author and enter small databases for a game engine, for example, and it was leaps and bounds better, and cheaper, and easier to use, than the unmaintainable SQL nightmare, with a bunch of frontend and backend dev on top of it, that the decent code who proceeded me left behind. Excel even scaled better, up to the size of our studio (hundreds of people), compared to the other system that needed technical artists to know how to use it. No question whether it would crumble if we were talking thousands of people or more. Which is why it happens so often. :P

matt123456789 6y ago

Add to this the understanding that getting this on contract will take a few months, when the boss was asking for these figures yesterday...

TeMPOraL 6y ago

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentication module built in (a file sent from a government mail address). There's a risk of user errors, but the system will be working immediately[0]. Deployment can be done by anyone, as it's just clicking on File->New and starting to type data in. Meanwhile, a "pigsqueal" solution will take half a year to design, produce and deploy (that's in an emergency, a year otherwise). And that's with competent IT people, and not someone who wants to profiteer off the crisis. Then you'll have to train the users, and hope the reality won't necessitate any changes, because they will take a while. I think plenty of decent coders appreciate the fact that Excel is suitable for a surprisingly wide range of tasks, and that if you spot it widely used somewhere, it most likely means there isn't any comparable alternative available. -- [0] - Note that the user error that finally happened did not happen in the place where you'd expect it to.

strbean 6y ago
Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?
- TeMPOraL 6y ago
  Extremely low performance. Obscure "sharing" workflow instead of obvious "mail a file". Cloud dependency that needs to be vetted for security (EDIT: and data privacy, as 'sjansen pointed out), and policies regarding cloud accounts on government e-mails. And in case of Airtable, also for budget. I'd be surprised if either of the above could handle 65k rows in a single table without becoming near-unusable.
- sjansen 6y ago
  Probably not while complying with privacy laws. I would be much more upset to find out a governemnt employ on a deadline used Google Sheets or Airtable to share my medical data instead of an Excel doc on a secure government file server.
  - retbull 6y ago
    I don't really agree with much of google any more but I was at one point fairly involved in a project there. They do have some more secure systems for government use that isn't the crazy leaky ad cesspool gmail and docs is.
    - srtjstjsj 6y ago
      Where are the ads in Docs?
      - monocasa 6y ago
        The docs are used for data into the advertisement systems. I've heard on the grapevine that several fortune 500 companies are on O365 because Google wouldn't disassociate docs from data gathering, even at the level of high touch high paying customers and they rightfully consider their internal documents to be part of their IP. Microsoft says "sure" and flips a secret "don't even gather stack traces on the server" flag for these customers.

jonplackett 6y ago

Come on, this is a government department of a first world country. They have databases for all sorts of things. They're not using excel for any other NHS databases, or the terrorism watchlist. There are plenty of people capable of making a good decision here. I think you let them off too easily by just assuming they're dumb. This a bad decision by people who definitely should have known better.

ImaCake 6y ago
Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.
- Smoosh 6y ago
  And yet, I have first-hand witnessed a scenario like the one described in an AU government department. A small system developed by business (one person who had subsequently moved on) using Excel and Word and macros to keep track of information and generate letters to "customers" had become an essential tool, but unsupported and undocumented. When we (IT) recommended redeveloping it in Java with an SQL database (and documentation and testing and backups), they balked at the cost. Partly because the original person did the work "in their spare time", so the business never saw the actual costs. And partly because there are zero infrastructure costs with Excel and Word. There was very much a feeling that IT massively inflate the costs and will build a Rolls Royce when a Ford will do. It was a house of straw vs house of bricks situation, but they were happy with their straw house, they thought their only problem was that the little pig who built it was no longer there.
  - jonplackett 6y ago
    Was that system working on the single most important problem facing the country at that time though?
  - 6y ago

happytoexplain 6y ago

Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.

Reason077 6y ago
Excel can handle the volume of data just fine. The (very outdated) .xls file format with its 65,536 row limit was the problem here.
- deleted 6y ago
  [deleted]
- srtjstjsj 6y ago
  Excel will also happily screw it up and not tell you, as happened here.
- jackjeff 6y ago
  This is the test and trace system for the worst pandemic this country has ever faced, six months in. I’m sorry Excel spreadsheets won’t cut it. This is a management failure. PHE has capable coders. And if not, they could hire some. The NHSX team has managed to write the dang COVID-19 app twice. Once not using the Apple/Google contact API (because management) and a second time properly. It’s all open source and it looks pretty decent.
  - tomatocracy 6y ago
    As an aside on the Covid-19 app - it is open sourced in the "occasionally throw some code over the wall" kind of way - eg it looks like the github was last updated on 24 September vs the app which has more recent updates. It's not clear they open sourced everything needed to actually build the app either looking through the issues. This is very very far from the only project doing that but disappointing nonetheless given the amount of public money which was spent on it.
- 6y ago

untog 6y ago

This isn't really true. The people making the decisions will have more or less zero insight into the data format being used to store data, they'll just know that data is stored. IMO, a much more likely scenario: a brittle, haphazard system was set up with extremely short notice at the start of everything. One administrative snafu led to another, ran into "well, it's working so let's not touch it", and we got to where we are today.

tqi 6y ago

Your scenario makes it seem like these things are the result of incompetence or stupidity by people who aren't "decent coders". The reality is that for most use cases excel+manual processes are perfectly adequate/have little startup costs and what you're suggesting is grossly over engineered/require immense initial investment. The real problem is if/when scope begins to increase these processes are sufficient until they aren't, and the tools aren't flexible enough to gracefully manage the transition.

nobody9999 6y ago
>The real problem is if/when scope begins to increase these processes are sufficient until they aren't, and the tools aren't flexible enough to gracefully manage the transition. That's a good point. I'd also add that, at least in the US, we don't have any sort of national standard for such things. Each public health department (and there are >1,000 of them in the US) has their own set of processes and procedures. I'd expect that the CDC has a proper database system and that data from those 1000+ entities is collated into that system. However, state and local laws/rules require specific handling of such data and can differ significantly from state to state, county to county or even town to town. Attempting to stand up an integrated nationwide system isn't a bad idea. But doing so while trying to manage a pandemic isn't reasonable. That many places used Excel spreadsheets for storing testing data is neither surprising or necessarily a bad thing. Until March, no one needed to have huge databases of test results -- now we do. Complaining that places which had , at most, a few dozen cases of reportable infectious diseases should have implemented a database that can support thousands (tens of thousands?) of test results seems rather silly to me.
- nobody9999 6y ago
  My apologies. I didn't read the article posted until now, and I see it's about issues in the UK and not the US (I was confused, as we had issues with data reporting in the US as well). My comment was focused on the decentralized US public health model and not on the UK's. My apologies for injecting analysis of a different problem into this discussion. As such, my prior comment should probably be down-voted.

leokennis 6y ago

Look, you’re right that Excel shouldn’t be used for this. But even then, Excel definitely could be used for this. We’re not talking big data here. We’re talking about a finite number of possible test result from a finite set of testing locations grouped on a daily basis. Let’s say 1000 test locations, where each location has a daily number of tests performed divided into “positive”, “negative”, “inconclusive” and maybe a few other options. That’s ~5000 data points per day. Should not be any problem. However, you need to properly plan ahead about how you design your spreadsheet, how to format the columns, how to keep it performant and how to not save it in some godawful 25 year old memory-dump dumpster fire of a file format...

hanoz 6y ago

The dubious wisdom of choosing Excel for this aside, how many people working in (1.) computing and (2.) statistics saw a file with exactly 65536 rows, with nary an eyebrow raised? And I mean the first time they saw it, not the second, third or fourth day in a row.

commandlinefan 6y ago

> another lunatic who says something called "pigsqueal" And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".

higeorge13 6y ago
rofl

javagram 6y ago

No need for a “front end” or “backend” or “pigsqueal”. MS provides a perfectly workable all in one tool, Access, for this kind of work. You can even use Excel as a front-end to access data if you want.

taxcoder 6y ago
I have spent plenty of time running Access programs for employers. You better have a Frontend and Backend if you want data integrity.

II2II 6y ago

A good part of the problem could be the timelines involved. With something like Excel, you have a large number of people who can put something together in a short amount of time and perform the needed analysis. Contrast that to a database. The first problem you run into is the limited number of people who actually have meaningful experience with databases. Databases ceased being consumer products about 20 years ago, so most people have only interacted with very limited front ends. They certainly haven't created a database, nor performed anything more than trivial queries. (Even then, they probably aren't thinking in terms of databases and queries.) Okay then, we are reliant on a much smaller number of skilled database administrators and developers. Do their tools facilitate rapid development? Even if they do have access to suitable tools, the process is going to be slowed by communications between the people who need the analysis done and the people who can actually perform the analysis. Needless to say that Excel, with all of its limitations is starting to look like a good option.

maerF0x0 6y ago

Exactly... > "But you wouldn't use XLS. Nobody would start with that." Actually almost every non-technical person I know that wants something "database like" ends up in google sheets or excel first.

golemiprague 6y ago

You didn't read the article, it seems that they tried to import csv files into a proper system but rather than importing them directly they converted it to an old format of excel which couldn't support the amount of record. I guess it was some microsoft based system and the developers were used to extract data from excel rather than just importing a csv text file as it would be in a more unixy environment.

dahart 6y ago

Your point is incredibly good, and made me laugh too. It illustrates precisely why people, both technical and non-technical, frequently avoid choosing the massive scalable hammer factory system architecture that takes five people three weeks just to get rev 1, over the little tapper that gets the job done today with one junior developer. From the technical standpoint of a decent (and probably only decent) coder, I think Excel is an amazingly sensible choice for technical projects for a bunch of reasons. I think decent coders reach for the database solution far too often, and waste ungodly amounts of time setting it up. Excel coming bundled with an amazing front end UI you don't have to write code for shouldn't be ignored. Sure it's error prone, sure it's a nightmare for data validation, sure it doesn't scale to Amazon sizes, but this little project will never get that big. (Yes, until, of course, it does.) I used Excel as the frontend for artists to author and enter small databases for a game engine, for example, and it was leaps and bounds better, and cheaper, and easier to use, than the unmaintainable SQL nightmare, with a bunch of frontend and backend dev on top of it, that the decent code who proceeded me left behind. Excel even scaled better, up to the size of our studio (hundreds of people), compared to the other system that needed technical artists to know how to use it. No question whether it would crumble if we were talking thousands of people or more. Which is why it happens so often. :P