Wednesday 10 August 2016

UK library data in PowerBI

Having had to put up with those god awful cipfa returns in FOI requests, sometimes as PDF's scanned in from horrible, blurry black and white print outs. I'm looking forward to whatever this data is that the libraries task force is going to put into the public domain. I'm a big believer that all data paid for by the public purse, should be available to the public as a matter of course, rather than having to put in FOI requests. And ideally the data should be as current as possible. Hopefully the data will have issues, visits, costs etc and also the number of libraries and if the library is statutory or not and some sort of time element on the datasets to trends can be shown. Having not being involved in this I've no idea what this data will contain.

Where I work, we've recently gone over to Office 365, and as part of this we got a new bit of BI (business intelligence) software from Microsoft called PowerBI. I don't work for Microsoft or have any friends or links to the company. I much prefer doing most of the BI work at the database level as the thing I'm most experience in is writing T-SQL statements for stored procedures and functions etc. If you know how to write t-sql well, you can throw data about much easier and in much more complex ways than you can in excel/access etc. The front end to all this of course has to be some sort of BI software to expose this data to the users. Down the years I've used: Excel, Access, Crystal Reports, Cognos, Business Objects and SQL Server Reporting Services. PowerBI as a tool, both for ease of use and the things you can do is so much stronger in my view than all the other stuff. You can configure more with some of the others, there's not a lot of tweaking you can do but the software is only a year old and they're adding new features every month.

Below is something I've thrown together from some very old libraries data that contains nothing in the way of measures other than the number of libraries per authority:



Nearly all of the visualisation in PowerBI can act as filters (or not) on the other visualizations within the report. This means on one page, you can click on a category on a bar chart and the data everywhere else on the page can be filtered. There's also slicers (the library authority names at the top of page four) which act as filter buttons, you can select one or many (ctrl + left click) and these will filter the data down as well. The example above isn't that great, because the data isn't great. There's a lot more you can do with this software, there's a library of user created custom visuals: https://app.powerbi.com/visuals/ and other examples online showing the sort of stuff you can do. It can take data from flat files, excel and most types of other database/datasource. There is a free version and a paid for version, if you use office 365 at work, you probably have access to the free version already. I've offered already to the task force to do some stuff with their data already, I'll probably want to do something with it myself even if they decide to use something else. Its important that we use this data to hold those to account who are failing to provide us with a proper library service.