Appian provides a low-code development platform that accelerates the creation of high-impact enterprise software applications – from idea to app in 8 weeks with a guarantee.
Using mapping technology to target public services
The Council worked with Microsoft Gold Certified Partner Ridgian to produce a business intelligence application that unites several technologies, including Bing Maps and the Microsoft Silverlight 3 browser plug-in. Called the Community Intelligence Hub, the application uses mapping technology to identify hot spots for problems such as antisocial behaviour and the illegal dumping of waste. The hub contains data from multiple sources, including intelligence from partners, such as the police and fire and rescue. Analysts can instantly generate a single report combining data onto easy-to-read maps, which previously would have taken a week or more if data were analysed manually.
Cherwell District Council delivers highquality public services to 139,000 people in the United Kingdom (U.K.), with its head office in Banbury in North Oxfordshire. As a largely rural local authority area, the council faces a challenge to ensure that it targets areas of greatest need of protection from problems such as environmental crimes and antisocial behaviour. Stuart Hemming, Technical Project Manager, Cherwell District Council, who manages all IT projects for the authority, says: “We have several business applications in use by individual departments. Although a few of them have dedicated reporting facilities that help us to extract management data, they work in different ways. Compiling reports from these systems was difficult, and, in some cases, impossible.”
In the past, the council’s analysts had to run a series of reports and then combine them manually to identify hot spots. Hemming says: “Traditionally, we would have known about illegal dumping through local intelligence but that way it is difficult to see how those hot spots change according to the seasons or school holidays.”
Cherwell decided to build a Community Intelligence Hub with the aim of bringing together information and statistics from its line-of-business applications into one data warehouse specifically designed for analysis. The specification for the hub included giving addresses in a fixed format. It breaks them down by the areas covered by the council such as wards, parishes, and super-output areas. The latter are a set of geographical areas used nationally since the 2001 census in the U.K. to help pinpoint areas of social deprivation. Hemming says: “Ease of use of the Community Intelligence Hub was critical. We wanted dashboards and reports that were highly graphical and easy to interpret.”
The authority compared two options for building the Community Intelligence Hub. It concluded that Bing Maps with the Microsoft Silverlight 3 browser plug-in was the most cost effective and simple to use for analysing and displaying geospatial information. The solution also included the use of Visual Fusion from IDV Solutions—also based entirely on Microsoft technologies—to enhance and accelerate the development. The other option failed to meet the authority’s strict security requirements for sensitive data. The council also chose Ridgian, a Microsoft Gold Certified Partner and expert in management information solutions, as its systems integrator.
The Microsoft solution included Microsoft Office SharePoint Server 2007 to present the analysis, and Microsoft SQL Server 2008 to store information in a data warehouse using online analytical processing cubes. Users can cross reference different sets of information within the same map and then merge the individual maps to create graphical representations of data.
Office SharePoint Server 2007 now includes Microsoft Office PerformancePoint Server 2007 business process management software, which the council is using as a powerful dashboard and monitor for its key management indicators. Another component of Office SharePoint Server 2007 is Excel Services, which makes it easy for users to share workbooks written in Microsoft Office Excel 2007.
Nigel Hamblin, Business Development Manager, Ridgian, says: “We’re working with the full spectrum of Microsoft technologies to extract information—not just from within the council but from its supporting statutory partners.”
After 18 months of development and implementation, the Cherwell Community Intelligence Hub is at the pilot stage and now contains 100,000 records. Hemming says: “The build of the solution took around six months, including the conversion of data, which was the biggest challenge. When the solution is fully implemented we expect to add around 10,000 records to the data warehouse every month.”
By bringing information together in the Community Intelligence Hub, the council can use one reporting tool to examine trends in issues such as antisocial behaviour. The hub presents analysts with user-friendly data compiled from all its internal systems, as well as those of its statutory partners—previously this could take several weeks. Hamblin says: “The Community Intelligence Hub using Bing Maps technology helps employees produce reports almost instantly. Analysts no longer need to spend weeks pulling this information together manually.”
Users Create Heat Maps Without Specialist IT Help
The new hub gives analysts the power to generate combination maps on a selfservice basis, without the need for assistance from IT experts. Hamblin says: “The use of Bing Maps to analyse and display geospatial information allows analysts at the council to make decisions based on objective information by spotting patterns and trends on maps. We’re using Bing Maps as a real business tool to assist with planning services. Previously, we would have had to trawl through tables of data to identify trends we can now see at the click of a mouse.”
Ward Maps Chart Crime Data
With the mapping technology, council staff can pinpoint areas affected by crime and antisocial behaviour in easily readable formats. Hemming says: “We can show a map of specific wards with crime data overlaid. We can colour the maps on a scale to see instantly where the hot spots for crime and antisocial behaviour are located. We can also see areas where intense activity occurs over a short period of time, where we need to react quickly and target resources to help solve problems.”
Mapping Toolset Improves Accuracy of Service Planning
In the current economic climate, public sector resources are under acute financial pressure. This means that councils have a responsibility to prioritise services towards the areas of greatest need. Hemming says: “The key business driver for this project was to help us produce evidence that would support our service and financial planning processes, as well as our corporate strategy work. We also hope it will help our statutory partners in their service planning. Data security is a big concern so it was essential to ensure that the information was properly secured in the design of the Community Intelligence Hub.”
Single Report Collates Information from Disparate Applications
The intuitive Microsoft reporting features in the Community Intelligence Hub help ensure that analysts can drill down into just a few streets or a couple of postcodes. Hemming says: “The main advantage is that we can create a single report, bringing together data from different sources. We can now deliver it at the click of a mouse. In the past, it could have taken our staff a week to convert data to make a specific report.”
User-Friendly Tools Need Minimal Training
Based on Office SharePoint Server 2007, with access through a Web browser, the Community Intelligence Hub is easy to use after a short training programme. No additional software or special logon is required for users. Hemming says: “All the dashboards and reports in the Office SharePoint Server 2007 solution are a single click away—they are graphical and easy to interpret. As long as a staff member can use Internet Explorer, he or she can use the reporting tools in the Community Intelligence Hub.”
Microsoft Bing Maps
The Microsoft Bing Maps platform is an integrated set of services providing quality geospatial data, rich imagery, cutting edge technology, and dependable performance that helps organizations visualize data and provide immersive end-user experiences.
For more information: