Lester Nakamura tries to pride himself on always keeping an open mind. But when he went searching for a long-term solution to the state of Hawaiis bookkeeping needs, Nakamura said he quickly concluded that an open-source system was the only choice.
Up to that time—in 2002—the states Department of Commerce & Consumer Affairs had labored under a financial analysis program called FAMIS (Family Accounting and Management Information System)—a rickety COBOL application that could generate only a fixed set of budgetary reports, which were updated monthly and then printed out for state officials for review.
Reports routinely took weeks to assemble. If anyone requested a nonstandard report, a COBOL programmer had to write a new program to generate it.
In a state that prides itself for service and hospitality, “this was not a user-friendly system,” said Nakamura, administrator for Hawaiis Information and Communication Services Division, in Honolulu.
In 2002, exasperated state officials turned to the Linux operating system to change that. They wanted all budget and expenditure data in one data mart, with a front-end application that lets users download data to their PCs and crunch numbers as they see fit.
Moreover, they did not want to shackle themselves for another decade or so to a proprietary system such as FAMIS. An open-source platform would offer stability yet still evolve with the states needs.
“We wanted to use as much of the resources as we already had in place, and where we had to buy infrastructure we wanted it to be as economical as possible,” said Wayne Sasaki, a branch manager in the Department of Accounting and General Services who worked on the project with Nakamura. “So we started talking about Linux. The goal with Linux was cost.”
To manage the technical aspects of the project, Hawaii turned to eWorld Enterprise Solutions Inc., a systems integrator and consulting company in Honolulu that had helped the state with other IT implementations in the past.
Kris Hansen, eWorlds chief technology officer, readily admitted that he is “a fan” of Linux and endorsed its use early in the project, even though eWorld had never before installed Linux on a mainframe.
“One thing that really impressed me about it was that its just Linux,” Hansen said. “Once you do all the mainframe partition configuration and all that stuff, its just [Novell Inc.s] SuSE Linux.”
eWorld and the state began working on the project in July 2002, racing to complete implementation by that December, before the two cabinet-level officials overseeing it saw their terms expire.
Hansen and his team tackled the underlying database architecture; Nakamura, Sasaki and other state officials helped define the front-end user application they wanted, which eWorld then developed.
“It was a real challenge for them to work with the [old] application,” Hansen said. “They really just couldnt get to the kind of information they wanted.”
Faced with such a tight deadline, Hansen took a pragmatic approach to the systems architecture: a database layer, a warehouse manager layer to cleanse data into useful form and the application layer.
eWorld is an IBM business partner, so Hansen also proposed IBMs DB2 Enterprise Edition as its database and DB2 Warehouse Manager for the management layer.
Hansen framed the problem as understanding “where the data resides” and what the users wanted to see on their computer screens. His team and the states steering committee first considered what sort of queries users would ask. Then he consulted with the FAMIS development team to pinpoint where that data existed.
“That process was very collaborative,” Hansen said. Ultimately, eWorld developed a series of Warehouse Manager processes, extracted all the necessary data from the FAMIS system, and used those processes to cleanse the data and correlate it into tables stored in the DB2 database.
“The challenge is that its so unstructured,” Hansen said. “Theres a lot of cleansing and transformation that needs to be done to put the data into tables and to make sense.”
At the front end, eWorld used IBMs WebSphere Application Server to develop a custom-made J2EE (Java 2 Platform, Enterprise Edition) financial analysis program for the state. More important, Hansens team met with Nakamura, Sasaki and other state officials weekly to keep a tight grip on how the application would function.
Sasaki coordinated the state end, but his bosses gave him strong authority “to reverse scope creep,” he said. “Whenever anyone said, This would be nice, we said no. We just wanted to get something out the door.”
eWorld also encountered a few infrastructure obstacles for which open source saved the day. WebSphere, for example, ran on an Intel Corp. server because at that point it was not compatible with the zLinux operating system used by the mainframe.
But because the various flavors of Linux cooperate so easily, Hansen said he could seamlessly port configurations and files from one part of the system to another.
Hawaiis DB2 system went live in December 2002. Its first users were 150 accounting employees, working mostly in the Department of Accounting and General Services.
Today, Warehouse Manager on a daily basis captures state financial transactions (which average 7,500 per day but can hit 25,000 on busy days, Nakamura said) and stores them in the DB2 data mart for future use.
The DB2 system sits on 10 percent of the states mainframe, parceled out specifically for the Linux system. Each year of transactions occupies about 2.1GB of storage space. FAMIS could hold only three years worth of data, but Nakamura hopes to store 10 years worth of data on DB2 as more funding becomes available.
The new database has proved so popular that the state began an expansion and upgrade just last month.
eWorld upgraded all three system components to the latest versions available from IBM (Version 8 for DB2 Enterprise Edition and DB2 Warehouse Manager, Version 5 for the WebSphere server) and installed the latest version of SuSE Linux. The company also began tests to increase user capacity to about 500 people in more departments.
Nakamura, meanwhile, said he has already told state workers to generate a wish list of new features and functions they want to add to the system as funding becomes available.
“We didnt get all the funding we needed at one time to do all the things we can,” he said. “We see this as a living project.”
Matt Kelly is a free-lance writer in Somerville, Mass. He can be reached at [email protected].