IE 11 Not Supported

For optimal browsing, we recommend Chrome, Firefox or Safari browsers.

Planting Data Gardens

Government data warehouses in some states and localities are turning out to be huge, and so are the returns on investment.

Many government agencies avoid trying to find out their return on investment for technology projects. But that's not the case with Iowa's statewide data warehouse. Started in 2000 and involving three agencies, the warehouse will eventually provide analytical data support for dozens of agencies.

More importantly, the warehouse is expected to generate a return on investment of 673 percent, or nearly $16.8 million, annually. Some of the money is in the form of cost savings generated by the Department of Human Services, which uses the warehouse to tackle fraud and abuse in its Medicaid program. But millions more in savings and new revenue is coming from a wide range of applications involving child welfare, veterans affairs, criminal justice and tax revenue.

Instead of five or six warehouses performing separate tasks for individual agencies, Iowa has elected to build one central warehouse that will eventually serve the entire state. "We are engineering our data warehouse around the way government really works," said Richard Varn, CIO of Iowa. "From that idea you have opportunities to relate data from different programs to give the state a better ability to deliver services."

Iowa's data warehouse activity reflects a trend in state and local government: a greater emphasis on using large amounts of data to analyze the performance of public programs and to make better policy and management decisions. What's unique about Iowa is it's ushering in a new generation of warehousing where the goal is decision-making and electronic government services on an enterprise scale. The results could help alter the way state and local governments provide services.

Looking ahead, Varn sees a day in the not-so-distant future when Iowa will use its warehouse to track the government and educational services provided to a child as he or she grows up to try to find out why one child ends up in jail and the other does not. Through better analysis of data, the state can accurately decide what policy adjustments can be made so that fewer children end up in trouble. "We have all that information on paper form being collected today," he said, "but it isn't integrated into an analytical environment where it can be easily accessed."

State of Warehousing
State and local governments may not be ready to use data warehousing on such a scale, but the rapid increase in the use of the technology bodes well for the future. Warehouses are central repositories of an organization's most significant data, typically from transactional processing applications, but from other sources as well. The data can then be extracted and organized in a database for use by analytical, query and decision-support applications.

Data warehouses have been used in the private sector for years, primarily to analyze customer buying trends. The federal government has also been a big customer of warehouses. But for the most part, warehouse use in state and local government has been relegated to Medicaid programs to help administrators assess quality of care and to track down fraud and abuse.

However, in the past few years, that picture has begun to change. States and local governments are using warehouses to analyze data for a variety of programs, ranging from criminal justice and finance to education and personnel. As government increases its use of systems that rely on data in a consolidated format, such as enterprise resource planning, customer relationship management, knowledge management and business intelligence, the role of data warehousing will continue to grow, say the experts.

"The value of data warehousing in both government and industry comes from capturing a variety of information across the enterprise," said Chuck Kelley, president of Excellence in Data Inc., and a data warehouse expert. "You can't just rely on transactional data if you plan to use a warehouse for decision support."

Imagine a state policymaker sifting through financial data several years down the road. He may notice a sudden rise in sales-tax revenue during the latter part of 2001, but if the warehouse lacks additional information, such as news reports mentioning President Bush's tax relief package with its billions of dollars in rebates that were pumped into the economy, the analyst may falsely assume the rise in receipts were due to some other factors. The result could be bad fiscal policy based on incomplete information.

Entering the Enterprise Era
An enterprise data warehouse starts with a strategy for integrating data from various agency systems, consolidating descriptive information into a system that is accessible to each state department, according to Dan Paolini, New Jersey's director of Data Management Services. The result is less wasteful duplication of information.

"We found there are certain types of data that have more value than others in an enterprise data warehouse," said Paolini. "So we identified the data that had the most immediate interest for all agencies."

That turned out to be a finance, payroll and personnel data warehouse that each agency can access and analyze. But before the warehouse can operate on such a scale, the state has had to come up with a logical data model and framework so that agencies provide information that is consistent and accurate, whether it includes people, places or organizations; is relevant to a broad group of agencies, such as human services or criminal justice; or is specific to an individual agency and is not shared or integrated.

Cleaning data so that it can be integrated and accessed in a logical way is a major part of any government warehouse project, according Paolini. "But that's minimal compared to the organizational problem of getting agencies to see the value of sharing data," he said. "Fortunately, the drive for electronic government within the state has helped push down the barriers in terms of data sharing."

So far, New Jersey has spent about $700,000 on its warehouse project for consulting, data warehouse tools and other needs. Plans are already under way to broaden the use of the warehouse into other areas, including criminal justice.

In Michigan, where the state is building an enterprise data warehouse with its business partner, Bull, the issue of merging disparate data from several agencies into a single mainframe stands out as a particular challenge, according to state CIO George Boersma. With the state spending nearly $4 million on warehouse projects, Boersma wants to be sure the effort generates the right kind of payback. One concern he has is making sure the agencies are relying on the warehouse for analytical, not just transactional, purposes.

So far, the strategy is working. The warehouse supports the state's Medicaid program for fraud and abuse, child support and the state Treasury for financial auditing. All these projects are generating a return on investment through cost savings and new revenue.

Join the Fray
A number of cities have also begun using data warehouses, but with a twist. In New York City, the development of a massive spatial data warehouse is under way. Address-based information plays a crucial role in municipal government, according to Alan Leidner, the city's GIS coordinator. The problem in New York, as New Jersey has discovered, is that different departments use different formats for the same kind of information. The result is inconsistency.

"The use of GIS in local government hinges on orienting all geo data to a common set of standards," explained Leidner. "We are working to get all this data to meet base map standards, identify the data most commonly used by as many departments as possible and centrally locate it for distribution back to the agencies."

The city is working with Oracle to develop a common platform for its spatial warehouse. While the warehouse component is new, Leidner estimates the city has already spent at least $50 million on GIS and will spend another $50 million when all is said and done. But the payoff has been tremendous, when you look at how the police are using GIS to analyze crime patterns. The results have been one of the most significant drops in crime ever seen by any city in decades, and the application has been internationally recognized for its effectiveness. Other applications include everything from emergency management to restaurant inspections.

Modest Investment, Major Payback
Compared to New York City, Iowa's data warehouse project is definitely small change. Costing less than $3 million, CIO Varn characterized it as a mid-sized IT project. Built by Bull with NCR as its partner, the warehouse project has succeeded in sharing components -- hardware, storage, software query tools, application development -- among the first generation of agencies using it.

So far, the results have been impressive. For example, the Department of Corrections was able to analyze the impact of policy changes on the penalty phase to certain misdemeanors and other violations. The Department of Criminal and Juvenile Planning was able to reduce its analytical query costs from $215,000 to just $2,690. The Department of Revenue and Finance used the warehouse to identify noncompliant taxpayers for audits and ended up increasing tax revenue by $3.5 million in less than one year.

Not surprisingly, a fair number of other agencies are lining up to become part of the centralized warehouse. "It's very expandable the way it's structured," said Varn. In fact, Varn is looking to offer the state's warehouse infrastructure as a platform for not just agency use, but other states as well. "There's an opportunity for Iowa to play the role of service provider. Our price point is less than what private-sector data warehouse providers can offer," he added.

Yet Iowa, like other states, is still in the first phase of enterprise warehouse building. The components are in place, but the data standards aren't up to snuff yet. "We're trying to get to the point where we can structure the data environment and have common files in all agencies for such things as customer and place. That's going to take a little longer to accomplish," said Varn.

Until that happens, Iowa will have to settle for the smaller, but still noticeably rewarding benefits from its warehouse. But once the state develops a single data structure for the most common data elements, then it can begin to do what other states are still talking about. Namely, focus policy and resources on the citizen as customer, rather than on government programs. "Once you can do that you can begin to look at the citizen in several different ways: spatially from a GIS environment, transactionally from a service delivery perspective or from the multi-layer, city, county, school district, state and federal perspective," said Varn. "When you can relate data from different programs, then you give yourself a better ability to deliver services."
With more than 20 years of experience covering state and local government, Tod previously was the editor of Public CIO, e.Republic’s award-winning publication for information technology executives in the public sector. He is now a senior editor for Government Technology and a columnist at Governing magazine.