Small town gets big results with data warehouse, analytics

Several years ago, the town of Cary, N.C., the seventh largest municipality in the state, had a problem common to many city governments: Too much data spread across disconnected databases prevented city planners from making the kind of data-driven decisions needed to provide services to a growing population. The number of Cary residents grew 43.1 percent between 2000 and 2010, putting increased demands on the police, public works and social services departments. 

“We had so many disparate software packages and databases that we couldn’t look comprehensively across the entire organization to see how any one thing was impacting multiple departments or maybe all departments,” said Bill Stice, the town’s technology services director.

Moving toward a more data-driven approach to decision-making, Cary turned to the SAS Analytics suite of tools to pull information from all its various databases into a data warehouse so city planners could perform analytics against the data. Using SAS’ reporting and analytic tools the police are now on track to reduce crime, department heads have a clear understanding of their budgets, and other units are tracking efforts to meet customer service goals.

The data warehouse is just the beginning, according to Stice. “We’ve accomplished a lot of the data warehouse parts, but we are new — still crawling — with the analytics piece,” he said. “We are looking at ways to improve operations through advanced analytics.”

What Cary has been able to accomplish so far in its “infancy” stage is impressive. Currently, the focus is on providing information that was difficult to exploit in a timely manner to the various city departments, city officials and citizens.  

For instance, when revenue projections dropped during the recession a few years ago, Cary officials had to quickly decide what to cut from their $350 million capital budget. And they needed to know in more detail what unspent funds remained in active capital projects. By combining data from three different databases — capital projects budget, operations budget and ledger/financial systems — they were able to quickly spot unspent funds in active capital projects, which freed up more than $10 million.

Likewise, the finance department built a portal that lets water customers check their water usage and determine if they have a leak. The town's advanced meter infrastructure system pulls in hourly meter data over 13 months to generate 600 million rows of data, which is too much for a spreadsheet to handle, Stice said. Now, nightly meter reads are compiled in the SAS analytics database, which automatically summarizes the data and presents daily, weekly and monthly usage information to residents via a portal. 

In another application, the police department has been able to provide property managers in 41 multifamily apartment communities daily crime and incident reports to help them keep criminals out of the complexes and track down people who are violating their leases. Before deploying SAS, the four officers in the unit had to scroll through the department’s records management system to match addresses in police incident reports with those of the apartments. Then officers would have to do the same with service calls generated by the computer-aided dispatch system or ask the department’s crime analysts to generate a report, Lt. Ken Quinlan, an officer with the Cary Police Department, said.

Now, SAS extracts the information from the two systems and puts it into an Excel spreadsheet for the officers. Within a few seconds, incident reports and calls for service in the apartment communities are accessible to the police. The information is then emailed to the property managers. The speed of the data matching means that a person arrested at 3 a.m. for possession with intent to sell narcotics gets an eviction notice on his apartment door by 11 a.m. It also lets officers patrol communities and perform targeted community outreach instead of spending time compiling reports, Lt. Quinlan said.

Police officers also have faster access to crime statistics compiled from various databases via a SAS portal, said Elise Pierce, a crime analyst with the police department. The statistical information — including the number of calls for police assistance, the location of repeat calls and the specific nature of the call — can be used to better allocate personnel, putting officers in hotspot areas at specific times. Prior to using the SAS tools, the process was very tedious, taking two hours to download the information into a database. Pierce would like to expand the department’s capabilities, moving into predictive analytics to anticipate circumstances based on growth in the town.

“One of the goals is to use predictive and visual analytics,” Stice said. The town is already pulling its geospatial data into SAS Analytics, which is one way to incorporate the visualization. Predictive analytics could be used to determine how future growth is going to affect the level of services Cary offers it residents, he said.