Curtis Boyd had a miniscule budget. Yet, he needed to quickly replace a nearly 20 year-old mainframe system to reduce report generation times from two weeks to one day.

Sounds like Mission: Impossible, right? Well, with a little patience and a lot of testing, Boyd's mission turned into a successful data migration for the State of Louisiana's Department of Health and Hospitals (DHH).

Since 1983, DHH's OS/390 mainframe had supplied users with ad hoc reports on Medicaid patient information called Management Administrative Reporting System (MARS). These reports contain information pertaining to the number of eligibles, recipients, and payments. "This information can be provided by parish (county), age, gender, race, type of service, and provider type," said Boyd, assistant section chief at DHH.

The MARS reports, however, averaged about $25,000 in mainframe utilization expenses and 40 CPU hours each month, said Boyd. That time and expense, on top of the two-week report turnaround each request usually took, was unsatisfactory. Reports need to be delivered one day to one week after they are requested, he said.

Baton Rouge-based DHH has over 250 million Medicaid records in its Medicaid Management Information System (MMIS), which was running on the OS/390. MMIS processes all the welfare claims for the state. The federal government funds each state's MMIS and each state must have a fiscal intermediary. Baton Rouge-based Unisys, Corp., a hardware server vendor, is DHH's intermediary.

Each year, 350-400 requests are submitted to DHH's system for MARS reports. Information requests are submitted for a variety of reasons. Doctors may want to know how long a patient on Medicaid was in the hospital and what services he received. Or, legislators may need to find eligible Medicaid recipient information, Boyd said. Until a year ago, Unisys compiled and sent out the reports for the hospital.

Give me simple reporting, please!

Knowing a PC-based system would be able to return the information more quickly than the mainframe, Unisys' and Boyd's seven-person committee began looking for a solution.

The team first looked into building a data center with Ann Arbor, MI-based Medstat Group's DataScan technology. Unfortunately, DataScan "was too cumbersome to use and it was hard to understand its logic, technology and design." Further, Boyd said, two people would ask the same question and the technology would return two different answers.

Next, the committee looked into using Unix and running an Oracle database. They soon found that "Unix is still a quasi-mainframe," said Susan Wagner, DHH's Section Chief. The database would not let individual users query reports, Boyd said. Further, because Unix's technology is very technical, more training would have been necessary for DHH's staff. More training means a more expensive solution, said Boyd.

Finally, the team tested Windows running SQL Server 2000 Enterprise Edition. After running a six-month pilot project, the team found that the SQL Server easy to set up, configure, and establish a complex data structure, said Boyd. DHH and Unisys were also able to easily migrate three years worth of data to the SQL Server from the old mainframe.

Now, two SQL Servers are integrated with Cary, NC-based SAS Institute's Enterprise Software to form a data warehouse. The SQL server software runs on Unisys ES2000 servers with four G bytes of RAM and 180 G bytes of RAID. The data warehouse has been running for about eight months, Boyd said.

DHH now saves $13,000 a month running the data warehouse, Boyd said. Since the data is only pulled from the server, "it's not transmitting through hard-coded logic," like it was from the mainframe, she said. For example, one large report cost DHH $300,000 to produce from the old mainframe system. It costs merely $200 with the SQL data warehouse, Wagner said.

Report generation times have also been drastically reduced. The hospital now averages an eight-hour turnaround for reports. In fact, reports needed right away are provided in two hours, said Boyd. Additionally, both Unisys and DHH employees work to create the reports because they are much easier to compile now, he said.

Because the data warehouse is so efficient, information requests have increased, Boyd said. Already 480 requests have been submitted and generated since the data warehouse was implemented. However, "now there's an expectation by the users that they can submit requests and they will get it by the next day." Yes, data can be retrieved quickly, he said, but it must also be validated before a report is finished. Therefore, not every request is completed within the user's expected timeframe. Because the request number is growing, Boyd plans to expand DHH's SQL Servers from two to four this year.

Lastly, DHH has been able to streamline which reports are even necessary to run. DHH has either phased out or is in the process of migrating all of the reports the mainframe system ran. It has also phased out paper reports completely.