Case Description In most respects, Mountain View Community Hospital (MVCH) has followe...

Case Description

In most respects, Mountain View Community Hospital (MVCH) has followed a carefully planned approach to designing, selecting, and installing its information systems. The organization developed an enterprise data model to guide its database development (Chapters 2–7). The hospital installed computer systems to support most of the routine operations in the organization. For example, there are systems for patient accounting, administrative services, and financial management. Many systems were acquired from outside vendors after a careful selection process.

Despite this careful planning, management is aware that there are some deficiencies and limitations in the present hospital information systems. In addition to the challenge of interfacing systems in MVCH’s heterogeneous environment of platforms and applications, two further problems have been noted:

1. Data are often duplicated in different files and databases, in different formats, and even in different media. For example, one set of patient data (used for billing purposes) resides in a patient accounting system based on a relational database. On the other hand, many patient medical records are maintained in a manual system (one folder per patient), or other applications, such as Dr. Z’s multiple sclerosis (MS) management system.

2. The systems are designed primarily to support operational (or transaction) processing but are not generally well suited to providing management information or to support analytical studies that are increasingly required for modern hospital management.

Management believes that these problems must be addressed, and that better and more centralized access to the hospital’s operational, financial, and clinical information is a strategic necessity for two reasons. First, like many other hospitals, MVCH is being driven by the trend to managed care and the resulting need to contain costs while maintaining or improving clinical outcomes. As a consequence, MVCH must closely track and analyze its clinical and financial data related to patient care services and provide those data to its administrative and clinical decision makers in a timely fashion. Second, Sarbanes-Oxley is beginning to have an impact. MVCH, as a not-for-profit organization, is not a covered entity under SOX. However, both Mr. Lopez, MVCH’s CFO, and Ms. Baker, the hospital’s CEO, have come under pressure from board members from the corporate world to certify the accuracy of financial statements, certify the accuracy of the hospital’s annual information return filed with the IRS, and provide timely reports. As a result, both Mr. Lopez and Ms. Baker have begun to demand more timely access to financial data for decision making, business intelligence, and financial reporting. The board of directors is also asking that reports include trend information and graphic presentations. In light of these issues, management wishes to investigate whether the techniques of data warehousing might be successfully applied in their organization.

A typical hospital data warehouse often contains four types of data: patient records; doctor, clinic, and hospital records; drug and pharmaceutical company records; and HMO and insurance company records. However, the small size of the hospital may not justify a large-scale data warehouse development project. Instead, several smaller data marts may be more feasible. After some investigation, MVCH plans to test the concept with two small prototype data marts:

1. A data mart that will record summary information regarding tests and procedures performed by physicians at the hospital.

2. A more detailed data mart that will record the details of tests and procedures performed by physicians for individual patients.

Table 9-1

After hearing about the data mart prototypes, Dr. Jefferson, chief of surgery, expresses an interest in a surgery data mart. Some of the reports he wishes to receive include the number of surgeries, by surgeon; the number of inpatient and outpatient surgeries per week and per month; the number of canceled surgeries, by reason, surgeon, and month; surgeries per week, operating room (MVCH has a suite of six operating rooms), and surgeon; mortality rates by surgeon; average surgery time, by type of surgery; average surgery time, by OR; and number of negative patient reactions to blood transfusions by surgeon and by patient gender and age. Dr. Jefferson also wishes to analyze surgeries in terms of the duration of anesthesia, total time in the operating room, and amount of time in the operating room before the start and after the end of the surgery. He wants to be able to slice and dice the surgery data by diagnosis, patient age, gender, insurance company, acuity code ( patient acuity at MVCH is rated on a five-point scale, with 1 reflecting the highest acuity and 5 the lowest acuity), operating room, and surgeon. Given this information, you have the following tasks:

a. Identify the dimensions and facts for this data mart.

b. Create the star schema.

c. Use SQL to create three queries that satisfy some of Dr. Jefferson’s information requirements.

d. Develop a business case for this data mart.