SQL Server database provides police performance data to Home Office

For a number of years, the Home Office's Police & Crimes Standards Directorate (PCSD) has been compiling and publishing a range of data on the performance of the nation's police forces.

For a number of years, the Home Office's Police & Crimes Standards Directorate (PCSD) has been compiling and publishing a range of data on the performance of the nation's police forces.

In June, 2007, it implemented an SQL Server 2005 database and other new technologies to give its end users quicker and more dynamic access to performance data, and new tools to analyse it more effectively.

The IT system, iQuanta, gives its users browser-based access to the data which is collected by the PCSD at regular intervals from diverse sources. The users of the system include police authorities and members of Crime and Disorder Reduction Partnerships (CDRPs).

"The main driver from the 43 territorial police forces in England and Wales was the timely provision of performance data both for their forces and other forces within their peer group against which they are assessed by the Home Office," explains Nick Manton, iQuanta project manager, at the PCSD.

"In a nutshell, the solution is an SQL Server repository of police performance data - such as crime levels and so on, used both for internal analysis and reporting, and for reporting on a web portal," he says.

The iQuanta system, which is separate from the corporate Home office IT system, was launched in 2003. But it underwent a major technology refresh in 2007, because the original technology had become out of date, and the system had grown in scope, says Manton.

At the end of 2006, the PCSD re-tendered for iQuanta. It was originally based on an SQL Server 2000 database, running under Windows 2000 and using SQL Server Analysis Server, Microsoft Excel, and IntelligentApps Excel Edition and Web Server Edition.

The former system ran for four years, and produced static graphical pages and downloadable PDF files, but the PCSD wanted to move to a dynamic system. Previously, the PCSD published half a million monthly reports to a web portal, containing the latest data, and this took 10 days.

"This was okay for our customers, but in terms of development on my side of the fence, if we wanted to add new data it took too long, wasn't very flexible, and the technology was getting on a bit. We wanted to make the website more dynamic and create charts on the fly," says Manton.

The new system was implemented in 2007, and is based on the SQL Server 2005 database, running on Windows 2003. It also uses DotnetNuke, a free and open source web application framework. Charting is still carried out by Dundas, but the firm now also uses Sage BI for business intelligence activities, linked into SQL Server Reporting Services (SSRS). Reports are also turned into PDF files when necessary.

The department has around 30 to 40 Extraction, Translation & Loading (ETL) packages for the data which comes from many different tech sources. Around half of the sources are from the central Home Office data warehouse, and the rest of the performance data comes from spreadsheets, and Microsoft Access databases. This data is held in many different formats, so porting it into the SQL Server 2005 database is a manual process for each data set.

"In terms of data quality, performance data is not necessarily the most accurate data, but timeliness is more important. Data quality is down to the providers of the information, and we just reflect what we're given, and offer a window onto that data for our users. Where there are inconsistencies, we offer a feedback loop," comments Manton.

The PCSD uses a new component of SQL Server 2005, the SQL Server Management Studio, to configure and manage the database, and also browse the data cube.

Manton has a core team of five civil servants, who are all operational research specialists with technical abilities. They are trained in statistical analysis rather than IT per-se, but are highly technically literate and can use the ETL and cube design software, as well as designing reports and having SQL Server skills. They are supported by Altius Consulting.

Manton says that the new system offers dynamic charts and reports, rather than static ones, and therefore presents users with a small wait for information. But one of the key measures of success for the new system was that it delivered continuity of service to the users with "no significant performance deterioration", and the PCSD feels that the new system has achieved this.

Its purpose was also to have the same functionality as the old system, and Manton believes this has also been achieved. "The initial remit for the project was to build a new web portal that looked like the old one from the customer's point of view. But from our point of view, it is vastly different. Its performance is much quicker, and we now turn around data in two days instead of 10, and it has allowed us to develop new features much more quickly, and add in new data items such as charts and new analysis," says Manton.

Read more on Privacy and data protection