For a number of years, theHome
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.