Minnesota Data Warehouse Furnishes Data on Demand

Imagine being in an industry in which you are a provider of services to 900,000 separate accounts. Each has different requirements for the amount of service desired. Some want a unique level of service each month. Others want the same service each month, but the level varies from one account to another. For most, you represent their only provider, so they are on the phone instantly if the service is in error or late.

You might be saying, "Why would I want to be in this business?" But if you are a state welfare administrator, you are likely to say, "Well, that's what we have to put up with every month." What's more, you also might be lamenting the monumental difficulty of keeping track of all this data to make sure you are spending tax dollars wisely.

But such pressures are common in state welfare departments now facing a growing list of performance demands dictated by new federal welfare legislation, swelling welfare fraud statistics and increasing human-services client rolls. On top of that, most welfare agencies are facing these requirements with older reporting systems that lack methods of responding dynamically to new accounting and legal complexities.

Minnesota's Department of Human Services (DHS) certainly fits that description. Two years ago it began to develop a variety of reporting and data analysis tools to replace its outdated systems. The result was the development of a data warehouse that has helped the state manage massive amounts of data and obtain useful executive- and policy-level data without the expense and time required to reprogram mainframes.

The Problem

DHS uses two primary production systems: the Medicaid Management Information System (MMIS), which processes health care data for Medical Assistance, General Assistance Medical Care, MinnesotaCare and a variety of waiver programs. The second system, MAXIS, automates county-level eligibility determination, benefit calculation, case management, program accounting for Aid to Families with Dependent Children, food stamps, general assistance and emergency-assistance programs. DHS uses MAXIS to pull reports from three databases that together hold more than 8 million records.

The department's immediate need was to produce timely reports for health care, analyze service patterns, allow forecasting and provide information to support reform decisions. Overall, the goal was to build basic reporting capabilities for health care claims data and then develop enhanced analytical capabilities in a short amount of time.

In the past, in order to analyze transactions related to managed health care data, DHS had relied on a number of systems that were found to be inadequate at examining complicated service patterns. The key problem was that most of the department's complex queries were relational in nature, and the tools available could not provide timely relational answers.

In fact, DHS had only three reporting options available before the data warehouse/Executive Information System (EIS) solution. The first was old-fashioned Cobol programming. "We might be able to use Cobol programming to produce some relational reports," said Bill Batcher, a DHS systems administrator. "But the problem with Cobol reporting is timely response." Also, the system was unable to answer natural follow-up questions occurring after the first report, he said.

DHS also deemed two other options inadequate: continuing to use the existing MAXIS decision support subsystem and doing extracts. "DSS worked off the department's production files, but that only gave us access to data in three files and 66 fields, which provided only a small amount of data to answer complex welfare questions," Batcher said. The extracts involve quickly removing data from the mainframe-based production systems and letting end users apply whatever tools they had against the extracts.

Both processes were considered slow. More importantly, they did not provide the level of detail the agency was seeking. DHS "possessed very complex data structures and complex questions that required a flexible relational database solution," Batcher said.

Fast-Track Procurement

Given the demand for a solution, managers at DHS wanted to acquire a data warehouse fast. "Many states award five-year contracts for the development of warehousing and decision support systems," Batcher said, "But we were looking to get a system in fast, get it working, get some experience with it and then move on from there."

Indeed, from start to finish, the procurement lasted only 12 months. DHS issued a request for information (RFI) in January 1996. In April, eight vendors responded with different approaches to building information-access tools. By June, a request for proposals went out. And by July, the proposals were in.

The RFI process, together with research gathered from a technology conference attended by staff, allowed DHS to write an RFP that defined its functional requirements without prescribing exact specifications, as other states had done in the past, Batcher said. The "combination RFI/technology conference is a model we will use on how to research and procure related IT systems in the future," he said.

In September 1996, DHS awarded a contract to Bull Worldwide Information Systems Inc. Although the company had no installed base in the state, its data warehousing solution for Michigan's Medical Services Administration included decision support for Medicaid data and had led to cost savings and productivity improvements. "Minnesota's exposure to the success of the Michigan project was a key factor in their decision-making," said Jim Parker, a Bull account executive.

Following the award, DHS moved quickly, signing a contract with Bull in early October. By early December the company had completed its database design, and by the end of the month it had installed a massively parallel NCR Corp. WorldMark 5100M relational database platform at the Department of Administration's central data processing site. The initial MMIS system data load occurred Dec. 11, and DHS answered the first query out of the warehouse Jan. 10, 1997.

Overall, it took fewer than 12 months for DHS to have the data warehouse hardware up and running -- just more than six months after releasing its RFP.


In going forward, DHS will take an incremental approach, adding functionality in phases during a two-year period. The strategy was to set up the underlying data warehouse with the lowest level of detail in the first phase. In the second phase, the data model was expanded to include more eligibility data. A report writer and mapping tools also were installed, and standard applications were built.

The third phase, which is now under way, will involve developing the EIS as well as enhancing the data model to include more eligibility data. Next, a specialized module for analyzing service in targeted program areas will be added, and EIS summary tables will be refined. Then, DHS will merge external data, including health department data (for example, birth and death records), and employment and training program data from the state's Department of Economic Security.

During the project's final phase, a contractor will continue to operate the system and train state staff to assume overall responsibility for the data warehouse/EIS system. The database would continue to evolve by training new users and adding supplementary data sets.


The new system includes a direct connection to the DHS mainframe so users can access the warehouse over a standard local-area network. "The architecture is very clean, especially [because] we have been building a fairly standard network structure in Minnesota for many years now," Batcher said. "Laying the data warehouse into that architecture was very easy."

DHS is using Teradata FastLoad, a tool from NCR that allows quick loading of database files and helps refresh the database monthly. The design keeps the database under 100M, with three years' worth of claims data already loaded. "We have as much history and eligibility in the warehouse as we've got in our production systems, so we could go back many years," Batcher said.

Because warehouse systems tend to become popular with users fast, the risk is that the demand for access and rising data volumes may overwhelm a system, especially after the 50G mark. But Batcher said he is confident the NCR platform is scalable enough to handle the demand from users well beyond that range.

"We've got a platform that we are very confident can grow to a terabyte range," he said.

User Acceptance

DHS went through a very rapid development process, rolling out the warehouse to users who were familiar with the business data but not necessarily at the level of detail provided by the new system.

"All of a sudden, users were provided with excruciating levels of detail, and that proved overwhelming for some people," Batcher said.

As a result, Batcher recommends other state agencies assess their organizations' readiness to deal with huge amounts of complex information. "At this point, we are placing a great deal of importance on educating our staff about the actual content of the data," Batcher said.

Of course, such features will let users send queries and interact with data in ways that were impossible with the old system. To that end, DHS looked for tools that will help workers pose questions dynamically, instead of developing applications that produce prescribed answers.

"I get an answer, but what does it mean?" Batcher asked. "What does it mean for this subpopulation? What does it mean for this geographic area?" Now such questions can be answered on the fly.

Costs vs. Benefits

The total budget for the two-year project was $4.8 million, which included 81.7 percent from federal participation and 18.3 percent from state support.

"We know what it will cost to keep the box up and running, and we know what basic administration will cost," Batcher said. "But how we actually structure user support around that is really changing the way we are using data for decision-making."

DHS' advanced-planning document estimates overall annual savings from the warehouse will be about $1.7 million. But Batcher expects that program savings alone should reach about $1 million per year. On software savings, "we figured we would have reduced programming costs of about $300,000," he said. "Previously, we were spending about that much and having Cobol programmers write reports." In the end, Batcher said, "most of the benefits will probably be immeasurable."

Some of the benefits will occur in areas that are not immediately obvious to those new to data warehousing -- in systems maintenance, for example. "Because we have a full level of detail, we've found [that] the system lends itself to supporting the basic system-maintenance function. People are using it for functional analysis," Batcher said. For example, while prioritizing changes was a serious problem before, now the department can measure the potential impact of a change and document how many transactions it would affect.

DHS also found the data warehouse could be a useful tool for operational program management. "When you design a system, you write production reports that you think people will need," Batcher said. "We had 800 MMIS production reports, so we are going to systematically assess how many of those we really want to keep and how many we can simply replace with the warehouse."

A new data warehousing solution is "a natural extension of the department's production systems," Batcher said. "The conventional wisdom in building an MMIS is that you build a decision support subsystem, but our project revealed that as a flawed approach. You really need to put your data in an analytically ready form, and warehousing is the way to do that."

-- John Kost is a senior vice president and Michael Coogan is a research analyst with the state and local services group of Federal Sources Inc., a McLean, Va.-based market research firm. FSI State&Local counts Bull Information Systems Inc. among its data warehouse clients.


  • Workforce
    White House rainbow light shutterstock ID : 1130423963 By zhephotography

    White House rolls out DEIA strategy

    On Tuesday, the Biden administration issued agencies a roadmap to guide their efforts to develop strategic plans for diversity, equity, inclusion and accessibility (DEIA), as required under a as required under a June executive order.

  • Defense
    software (whiteMocca/Shutterstock.com)

    Why DOD is so bad at buying software

    The Defense Department wants to acquire emerging technology faster and more efficiently. But will its latest attempts to streamline its processes be enough?

Stay Connected