Barnsley Council - using UPRNs to build a Vulnerability Index

Finding a solution for disparate data

It can be a struggle to dedicate resources to back-office systems, but Barnsley Council has a specific Business Intelligence (BI) Technical Team, which has enabled resources to be dedicated to the ‘engine room’ of the council – its data and underlying systems. This team has a wide range of skills including IT, Analysis, Performance, Digital and GIS. When the team was first formed, some basic training courses were completed, but mostly the skills have been developed in house and on the job, using existing technology to help develop bespoke solutions at relatively low cost. To ensure maximum efficiency, an overview of the team’s status quo and position within the council highlighted areas and opportunities for improvement.

Like many local authorities, data was not treated as a valued asset. There were many spreadsheets being used to maintain master data; inaccessible or unsupported Access Databases had appeared around the business, with a variety of naming conventions and structures. Data was being stored in tables within Microsoft Word documents, and some ‘old’ SharePoint sources (pre Microsoft 365) were proving difficult to use. Many legacy systems had not been designed with BI in mind – they had low levels of reporting capability – and while ‘cloud’ solutions were starting to appear, internal processes and procedures often did not align to business intelligence requirements. Those new systems had not been specified to include relevant reporting functions and accessible, interoperable data.

Acknowledging the need to manage data better, and to manage it centrally, it was agreed that any reporting should come from a managed source, instead of relying on direct connections to siloed repositories. In this way, prepared and managed data would be available for re-use by more than one end-user – increasing efficiency, and ensuring consistency. The team engaged with their peers for buy-in to a ‘Digital First’ programme. Within the wider programme, two relevant strands were developed – one for business intelligence itself, and one for data management. From this work, a council wide Data Management Policy was created and adopted – a crucial step for future conversations about getting ‘buy-in’ when it came to adoption of other systems and datasets.

The team’s Data Warehouse is an on-premise SQL Server: there was no additional cost to the BI Technical Team to set this up, as it was available through current licencing agreements with Microsoft and the IT department had hosting capacity. For ETL (Extract Transform Load), the teams used the ‘FME’ tool from Safe to collate data from a variety of sources, prepare it for use, then save in the data warehouse.

Existing skills were useful, stemming from GIS and spatial data management, but the team also referred to their IT colleagues for integration integrity. There was a small cost for additional licencing and some initial training. The team uses the popular Power BI as its data visualisation tool – developing outputs in the Desktop version, which attracts a small cost per user / per month. Minimum data modelling is done in Power BI – the premise being is that data is pre-prepared, minimising bespoke builds.

The council’s Local Land and Property Gazetteer (LLPG) is a lynchpin in these solutions, as this holds the ‘master’ list of UPRNs – the unique identifier for all property-based data. Alongside this, the team uses a web-based internal and external GIS platform – Earthlight. Power BI reports are accessed through an on-premise server version which ensures all reports are available to relevant internal audiences. Perhaps most importantly, through open communication, the Information Governance team was brought on board at an early juncture, actively working to help navigate through the legislation required. Awareness across the council of the importance of data is increasing as a result, of the BI Technical Team’s initiatives, but there is more work to do.

The results – a hyperlocal Vulnerability Index

Since 2018, the Business Intelligence (BI) Technical Team has created hundreds of fully managed data flows, taking in data from APIs, Databases, Cloud Platforms, e-forms, and Robotic Process Automation. The team applies business logic and promotes the fixing of data quality issues; stores the data in the Warehouse in a way that maximizes re-use; aggregates related data sets; and schedules automated updates, so all this information is as current as possible. With their proximity to so many of the councils' key datasets, they have become the valued experts on information use and access for Barnsley Council – responding quickly to new requests for data driven solutions, driving the council’s response to the Covid 19 pandemic. At the time, the team was receiving multiple requests for Business Intelligence. These included:

• Call Centre – who was being helped, how, and with which repetitive actions
• PPE tracker – how much PPE was in stock, where, and who was using it
• Test & Trace – local contact tracing, including the Children’s Tracker
• Outbreaks – tracking outbreaks in care homes and local businesses
• Shielded Patients data – outbound welfare and support calls
• Mass Testing – booking system, results, BI on impact and spread of the virus
• Identifying vulnerable people.

The requests led to the creation of a Barnsley Council’s Vulnerability Index – the VI – an assigned vulnerability index score against individual residents, as part of the council’s response to the pandemic. It used person level data from a few dozen key business systems as well as external data sources. It was fully data-managed and built with significant amounts of automation – ensuring the ability to call off information as required, with confidence about accuracy and currency of information.

The personally identifiable information was then standardised and written to a master file in the data warehouse. Fieldnames, formatting, syntax of the data was therefore consistent – correlating as many records as entries. An SQL View can then load the latest data for everyone, from the RAW People data, and follow a series of rules to ensure matching integrity. In theory, this results in only one row per person: data that can be aggregated and pivoted to include system refences and case numbers as appropriate. Rules are applied to ensure that the highest quality data for each person is retained, and, to track changes over time, each person is assigned a persistent, unique person identifier. That data is then matched primarily with a UPRN – ensuring a system-wide mechanism for checking data integrity and avoiding duplicate entries. A data matching process has been put into place to add the UPRN to households where systems don’t currently store it.

This data can then be scored to provide the Vulnerability Index, using criteria that can be changed according to desired influences, and – crucially – displayed on a map to aid understanding.

The person level data also contains details of those persons that have had contact with the council. This is useful for delivering efficient and effective services, such as Barnsley’s Supporting Families programme. The aspiration for this is to enable tailored support – the appropriate teams will be able to access known details about a person, with confidence, and collaborate internally before any intervention programmes are put in place. This feeds into the council’s desire for early intervention – using past trends to predict the future. It also supports Barnsley’s ambition to pursue the highly efficient ‘Single View of the Customer’ approach.

Barnsley is also currently planning how taking advantage of cloud technology could benefit their BI offering. Rather than a direct ‘lift and shift’ approach, Barnsley is keen to continue using their current tools and supplement it with elements of the cloud that will allow for sharing reports with partners, improving the performance of the people matching process and begin exploring the potential for data science.

A Barnsley Data Group is also being established that will include key partner organisations, with the aim of continuing development of the work already undertaken and drive it forward, for example creating a shared vulnerability index for multiple purposes.

Mark Wood, Barnsley’s Senior Business Intelligence Technical Officer said:

“UPRNs underpins Barnsley Council’s Vulnerability Index – identifying the locations with which individuals are associated as residents, reducing errors, increasing the efficiency of multiple, collaborative, targeted services.”