It was widely reported yesterday that students in Scotland who had signed up for SMS notification of their results had received them a day early, giving them a jump on their less technically minded compatriots and competitors and causing stress and distress to students, parents, educators, civil servants, and politicians.
The Scottish Education Authorities began a root cause investigation as the secrecy and security of the examination results system seemed to have been compromised.
Right now I suspect you are settling in for a tale of hackers and Jason Bourne like derring do. Well, here at IQtrainwrecks we never get that lucky. After all, this is a blog that looks at information and data quality problems.
According to The Register the root cause of this problem is good old data interchange and exchange across organisations.
- A template spreadsheet was used to perform the data interchange between the Scottish Education Authority and the company which provides the SMS gateway and related processing as a pro-bono to the Education Authority, AQL. A batch template is used rather than an on-line interface as the service is only used once a year.
- The template was populated and saved in a later version of Microsoft Excel
- The process of populating and saving the spreadsheet appended a white space to the end of each date stamp (the date that the sms was to be sent).
- The ETL process interpreted the “DATE” field as text (which it was thanks to that errant space) and rejected the field on the load. Luckily AQL had developed error handling for situations where a date field couldn’t be loaded and applied a default… the day of the file load (which was the day before the messages were to go out).
- As a result the SMS system read the file and sent the messages a full day early.