RAP

A cautionary tale

Tom Jemmett

thomas.jemmett@nhs.net

9 October 2024

I am an idiot

Many moons ago…

I received an FOI request
It was clearly sent to multiple trusts
It was the second year this request was sent through
They provided the figures we sent the year before
I hadn’t saved my work from the year before
I wrote the query to answer the request
Checked to see I got broadly similar results to the previous year

My figures were way off

What could I have done differently?

Saved my Sql query?
Saved the Excel file along with the query?
Saved a full set of instructions alongside?

It’s easy not to do these when it’s (seemingly) a super simple request.

If you haven’t got a good structure in your team for where to save these kinds of requests, then does it even help to save them?

What would I do differently now?

Ideally, use something like Quarto (.Qmd) [all one script]
- query the data from the database
- do any data transformations needed
- save the results to the required format
save it in version control with a sensible name (Year/FOI #?)
document what it is doing
Make this part of our teams strategy for dealing with FOI’s

Another cautionary example

A colleague was the point of contact for one specialty
They always produced their reports
One month they were off
☎️ “We need the report for our meeting, now”
What report?

The report…

After a bit of searching I found a couple of different Excel files
Which one was the most recent?
Guessed based on time stamps
Inside the file were three sheets: Sql, Data, Table
Could I just run the Sql, dump it into Data, and refresh the Table?
There was no notes or documentation

Did I provide the correct report for the user?

🤷‍♂️

🥉/🥈/🥇 RAP is cool and all… but

There are so many easy wins that get you most of the way
Consistently save files
Store any query and steps to reproduce alongside results
Use PowerQuery or equivalent to automate data->Excel
Use .Rmd/.Qmd documents to do everything in one file
Utilise git/version control
blah blah blah, docker, CI/CD…