The Long View

Trying to create an integrated customer record? This healthcare data warehouse holds valuable lessons

By Karen Shockley

When you're faced with obtaining information from terabytes of data, your first thought may be "We need a data warehouse." But, unless you refocus this term to mean, "We need a data warehouse that can solve a particular business problem," you will waste much time and effort (and expensive resources). Ideally, solving this business problem supports the overall strategic objectives of your company. For example, your company may need to determine specific buyer demographics so that it can implement a plan to increase consumer purchasing in its stores. Here the supported business objective is to grow market share. Only by aligning IT objectives with business objectives can a company hope to have a successful data warehousing venture.

In this article, I'll discuss a data warehouse that my team at EDS created to support decisions related to healthcare objectives, but the same techniques can support many types of business decisions. The key is knowing which business objectives are crucial to the success of the enterprise.

In this healthcare example, the crux of the problem was that the design needed to handle a longitudinal view of the patient, as well as provide trends of services to these patients. The silver lining was that other industries could also use this solution to solve their business needs. In the same way that you view a patient record, users could view the overall buying trends of customers over the life of their relationships with a company based on time and demographics at the time of purchase. Additionally, the user could determine the purchasing habits of particular demographic components of the country or of the world. The user could also determine the amounts of money spent by location or by demographics, or the user could determine spending trends and whether that spending was a single purchase or comprised multi-item buying.

Data Access

The problem seemed straightforward: Create a data warehouse that contains healthcare data on several million beneficiaries and be available to both "novice" and "power" users. The goal of the warehouse was to assist the organization in providing patients quick, convenient access to quality healthcare information, with an efficient expenditure of resources. Then, as long as the warehouse was being created, the client wanted to include the following "challenging" requirements:

• Load disparate data for easy access by the user.
• Ensure that users can view the data in two ways:
  1. At a strategic level: a hierarchy starting from the top organization levels and ending with data captured for a specific clinic
  2. At an individual patient level.
• Load three terabytes of historical data.
• Ingest more than 50,000 files per month and load the data warehouse during down times.

The data warehouse model addresses the first two challenges, and the information architecture address the second two. A star schema model was selected because users could easily adapt it to form business questions, and it can provide answers to those questions relatively quickly. The real challenge was determining the best method to employ the star schema.

The business questions were divided into four relatively straightforward environments; a set of user-defined metrics separated themselves into the areas of resource utilization, the demographics of the population being served, the specific care being provided, and the cost of that care. In a retail instance, you could replace healthcare service with the product being supplied, purchased, tracked, and so forth. And, of course, all the metrics were a product of time.