Bullet-Proof Warehouses
Data warehouses promise efficiency. But millions of dollars and months of work still don't guarantee success. Here's how to avoid failure.
By Charlene Marietti
Warehouses for the New World Order
When the Military Health System, Arlington, Va., undertook its sprawling healthcare integration effort four years ago, it was prepared to overhaul its culture to make itself more efficient and to survive in an environment of budgetary blood letting. What it wasn't prepared for, however, was becoming a data warehousing pioneer. The plan was to begin building an enterprisewide data warehouse two years into the project. But when the time came to choose warehouse technologies, project managers could find few data warehouse products designed for the staggering workload and millions of transactions of the Military Health System. HBOC's product came closest, reports Michael Mauro, enterprise data warehouse (EDW) system architect for the Corporate Executive Information System (CEIS). But no healthcare vendor was geared for this project's magnitude.
Nuts and bolts
CEIS administrators enlisted the help of EDS, a Dallas systems integrator, to help design and select warehouse components. Flexibility and scalability were essential. "Whatever business problems you're facing today, they'll be different six months from now--and they'll change even more within 12 months," Mauro explains. "The minute you start to reengineer and change from fee-for-service to managed care, the business metrics you use today will be outdated tomorrow."
Mauro's group settled on a warehouse that includes IBM's RS/6000 hardware platform and Informix's parallel server database system. To populate the system, it chose DataStage, a lexicon-based extraction/transformation tool from Ardent Software. Flexibility is also important on the front end where, among other concerns, the project leaders needed query, reporting and online analytical processing (OLAP) tools to support both thin and fat clients. They selected WebIntelligence from Business Objects. Mauro says these select commercial tools helped his staff avoid a lot of code-writing.
To determine the data elements to support the business reengineering and organizational transformation process, CEIS sifted through tens of thousands of transactional elements to derive a short list that included diagnostic ID, procedure ID and an encounter case mix index. Only 300 data elements were selected for the first version of the warehouse.
Stars and falling prices
Organizing the data for access and retrieval was another major challenge. Project leaders decided that a star schema data model, as opposed to a more traditional relational data model, was to be the only practical solution for such a large warehouse, reports Mauro. The advantage: Users can examine the enterprise from the top down without knowledge of the questions. But this choice came with drawbacks. Namely, retrieving data can be a problem. A user may be able to determine everything there is to know about an individual or an event, but the system is not optimized for that type of retrieval--for which a properly indexed, normalized data model is better. Mauro credits lower mass storage technology costs for enabling the project to economically compensate for star schema's shortcomings. Multiple databases, normalized and designed to complement star schema's redundant data, now work with five star schemas built on information needs: clinical, population, claims, pharmacy and financial.
Disease managment wins big
Mauro says when users realized that they could use the data for disease management at this early stage, he was elated. He observes, "I have seen behavior modification that was not dictated; rather, it was self-engineered based on information and evidence."
Article appeared in Healthcare Informatics - September, 1998
