The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Chapter 13. Health Care

Health care presents several interesting data warehouse design situations. In this chapter we will imagine first that we work for a large health care consortium, then that we work for a billing organization for care providers and hospitals, and finally that we work for a large clinic with millions of complex patient treatment records. Each of these situations will suggest important design techniques applicable to health care and other industries.

Note

Chapter 13 discusses the following concepts:

• Value circle within health care, centered on the patient treatment records

• Accumulating snapshot fact table to handle medical bill line items

• More dimension role-playing as applied to multiple dates and providers

• Multivalued dimensions, such as an open-ended number of diagnoses along with effective dates and weighting factors to support allocations

• Extended fact set to support profitability analysis

• Handling of complex medical events

• Fact dimension to organize extremely sparse, heterogeneous measurements

Health Care Value Circle

A typical large health care consortium is a network of providers, clinics, hospitals, pharmacies, pharmaceutical manufacturers, laboratories, employers, insurance companies, and government agencies. Unlike the value chain we described in Chapter 3, a health care consortium resembles more of a value circle, as illustrated in Figure 13.1. This figure is not a schema diagram! It is a picture of how all these diverse organizations need to share the same ...