Using an AI tool to assist with my role in health libraries

Blog post by Chris Rowlands on how he is using an AI tool to assist in his role in health libraries

As the library systems admin for the Kent, Surrey and Sussex Koha consortium, part of my job is helping write mySQL reports for circulation and user statistics for the 20 libraries in our region.

While all staff have access to the module to write their own reports, learning mySQL is asking a lot of librarians for whom time is already a very precious resource. One project I am developing is using a data visualisation tool called Metabase to create a custom model of our data.

This will have two useful applications; firstly I can then create a curated dashboard of all the main statistics our libraries are interested in; secondly it will be much more intuitive for staff to build their own queries from this model, without having to learn about the system’s database structure mySQL.

For my first draft of the model’s code, I wrote without AI assistance. I wanted to present data for every issue and renewal, and the attributes that libraries measure them against; for example the ESR (employment) category for the borrower, and the book’s home library.

This was complicated by the need for this data to be grouped by library Trust rather than the individual site the issues were made – libraries tend to collect statistics by Trust regardless of how many discrete sites they have. This means Sussex Health have to run reports five times and then add each site’s data together. My model would save all multi-site Trusts from having to do this, reducing the chance for errors and saving lots of time.

I won’t go in depth on what I had to learn to achieve this, but it involved a lot of understanding how a library system’s database structure works, and learning bits of mySQL to join data to other parts of the data.

The working first draft was just over 130 lines of code, and reports written using it were taking 30 seconds or more to load. It was nice that the code itself worked but I didn’t think this would be used by library staff, as the frustration of waiting for software to load disengages us all! This was where my manager Richard Pemberton suggested I use claude.ai to review my code.

Claude is developed by a company called Anthropic as a “foundational AI model”, and lists code generation as one of its core capabilities. I have largely avoided interacting with AI since the boom period in late 2022, partially down to a lack of need but also partially due to cynicism and scepticism. I have been broadly keeping up with news around AI and am aware of concepts such as prompt engineering and hallucinations, so I wasn’t going to treat Claude as a silver bullet.

The first thing I did with Claude was copy my entire SQL query into it, and asked what methods it would suggest to optimise the efficiency of the code. It came back with a list of nine suggestions for me to try; most of these weren’t relevant as they involved modifying the database itself, however a couple of its suggestions seemed useful.

The problem then was, I didn’t know how to do what it was suggesting. I was able to ask Claude to explain in detail what each technique was, in what became a hybrid method of self-taught and AI-led training. Normally to learn SQL I read the textbooks our library stocks, or consult one or two trusted training websites for SQL.

The advantage of Claude in this situation was it being able to suggest things I didn’t have a concept of – I would not have thought to use derived tables in my query, nor did I understand how to aggregate case statements! The end result is that Claude took my code from 130 lines down to 45, and the reports generated using the model from this code loads almost instantly.

This has allowed me to progress with my statistics dashboard project, which could potentially roll out to any library or consortium using Koha in the NHS. The amount of time this could save library staff in harvesting monthly and annual statistics data is promising – with the added benefit of making statistics reporting more consistent.

I now use Claude in any situation where I am unsure of the answer, or have run out of ideas; though this tends to only really be useful in situations like the one described above. So for SQL help or Excel formulae it can be a really helpful tool. If I use it to supplement work I’ve already started then it can really enhance what I do.

I find it is less useful if I want it to generate something from scratch. How much this is down to Claude or my own inexperience with AI tools, I couldn’t say – perhaps it is a bit of both! I also haven’t really tried any other AI tools, mostly because I don’t know where to start with researching this, and for the amount I use it, I’m happy to trust my colleagues' recommendations.