Changing the Rules in Enterprise Data Warehousing.

New paradigm brings new opportunities for data utilization.

A decision-support data warehouse plays a critical role in healthcare organizations by integrating data from across the enterprise and providing information management capabilities to the organization. A typical warehouse manages financial and clinical data, as well as data brought in from diverse systems. It serves as a strategic complement to operational systems used by caregivers and clerical staff by supplying a separate data store that answers management analysis and reporting needs without impacting information access at the point of care.

The classic definition of data warehousing held that the data must be relatively static and support the concept of "read only" and "write once." Two philosophies emerged regarding whether the construction of a data warehouse should follow a "top-down" design, implementing a single monolithic warehouse for the entire enterprise, or follow a "bottom-up" design, consisting of an incremental string of related data marts.

Today, healthcare professionals typically require data warehouses to be updated on a monthly basis with encounter-level data. The warehouse must accommodate updates through multiple paths, incorporating sophisticated interfaces, such as round-trip exports to costing and revenue modeling applications. This article details some of the principle drivers of healthcare data management and focuses on the new design principles that must be deployed in creating effective healthcare data warehouses.

Keying in on Flexibility

Whether you are considering building or purchasing a data warehouse, there are several aspects to consider regarding the underlying architecture. A crucial concern is the construction of the primary keys and the flexibility required to accommodate the diversity found in healthcare data.

A major limitation of earlier warehouses was the use of "natural" keys for the primary key value (e.g., using patient account number as the primary key for encounter records). Although a seemingly logical approach, healthcare data is unique from data in other industries because there is a large number of "child" records that must be related to each encounter record (e.g., service items and payments). Maintaining this key structure results in considerable performance overhead when joining the records in a report query. The generally accepted practice today is to assign "surrogate" keys to the records as the data is imported using an incremented binary value (usually a 4-byte integer). Since the keys are represented by integer values, the performance during table joins is vastly improved.

Equally important in the definition of primary keys is the flexibility to change them as disparate data sets are introduced. A health system that originally loads acute care data may decide to add physician-level data from a recently acquired ambulatory surgery center, with the goal of relating records at an encounter level. The initial data sets created off the acute care data may derive the surrogate key for an encounter record from a combination of patient account number and discharge date. The introduction of the physician data may require the creation of a new data set that links physician-level encounter records based on a surrogate key derived from Patient Account Number and check-in date.

The warehouse should be able to commingle primary key structures without needing to re-engineer the model. A flexible key structure is vital in securing the longevity of a data warehouse and should be seriously evaluated in the design phase.

Incremental ROI 

Philosophical arguments continue regarding a "top-down" versus a "bottom-up" design approach. In reality; there is a steady trend in the industry toward a less risky, middle-of-the-road approach. In this paradigm, the data model is designed using a top-down healthcare enterprise model, with the actual population of data implemented in a focused, iterative manner, typically starting with financial data, then moving toward encounter data, and finally to anciliary and clinical system data. As scrubbed, audited data become available in the warehouse, independent data marts are spawned that serve as the distributed reporting base for the end users.

The advantage of this approach is that it offers the quickest path to return on investment while providing flexibility to expand the data captured in the warehouse without redesigning the model. Solid investment returns should be recognized in six- to 12-month increments.

Time/Value Continuum

In traditional views of data warehousing, data remained static and updates typically occurred on a semi-annual or quarterly basis. Today, organizations need to accommodate incremental updates from diverse information systems. Update schedules generally range from weekly to monthly, providing a good balance between timely information and capturing lagging data coded in the HIS system, such as late charges.

The ability to update existing records in the warehouse is also a change to the old-school mentality. Integrating cost accounting algorithms with expected reimbursement modeling adds cost and payment components to the data and facilitates analysis from a profitability viewpoint beyond the raw data. Most cost accounting applications work only on billing data. However, future needs will focus on costing from a clinical perspective, with integration and costing of orders from a care management system. This vision merges the financial and clinical perspectives of healthcare management, associating the cost of care within a clinical framework.

To achieve these long-term benefits, the data warehouse must be constructed with a focus on the data import engine, which must be flexible to allow different types of incremental update methods. Examples of update methods that minimally should be supported are: update, insert, replace, accumulate (add values within a field) and delete. The import process should also be streamlined for performance and take advantage of the native bulk copy process supported by the back-end database. Finally, the import engine should support batch processes, so large imports can be scheduled to run during off hours.

From Warehouse to "Web" House

The Internet is revolutionizing modern business and healthcare data warehousing is no exception. The original users of healthcare data warehouse information were usually limited to highly specialized data analysts. As more end users demand access to the data warehouse, browser-enabled applications provide an ideal vehicle for report distribution and analysis. The thin-client nature of most browser-based applications consumes minimal bandwidth and is the perfect choice for organizations that struggle to provide information to remote users.

The development tools surrounding these applications are maturing rapidly, with recently released products closely mirroring their client-server counterparts. When evaluating browserbased reporting tools, ease-of-use is an important consideration given the volume of users likely to be included in the rollout. The application should also be robust enough to support dynamic analysis of the data from a traditional online analytical processing perspective and offer sophisticated graphing capabilities.

Finally, the application should draw on a principle concept of the Web--"pushing" information into the hands of users in a meaningful way. The Web has blazed the trail for applications to support triggers within the data that are based on user-defined thresholds. When these thresholds are exceeded the application should alert the user via e-mail or other messaging avenue and enable users to evaluate and share the results with colleagues.

Overall, the future is bright for healthcare data warehousing as design evolution has given way to vast improvements in data collection and analysis. The integration of the Internet and browser-enabled report applications brings information directly to the fingertips of the individuals who need it most and positions the data warehouse as the cornerstone of information dissemination within the enterprise.

Summary of Paradigm Shift 

Warehouse             Traditional Beliefs     New Paradigm
                      Characteristics

Key structure         Fixed "natural" keys    Flexible "surrogate"
                                              keys

Overall design        Top-down v.s            Top-down design with
                      bottom-up design

Frequency of          Semi-annual or          Weekly to monthly
updates               quarterly

Data enhancement      Read-only "static"     Data enhanced
                      data                   thourgh value-added
                                             applications

Number of end users   Limited users          Numerous users via
                                             Web-enabled report
                                             distribution

Report distribution   Static reports         Dynamic analysis
                                             tools using "push"
                                             technology  and
                                             threshold alerts

Michael T. Wood is vice president of research and development for McKessonHBOC in Amherst, MA.