The eID clinical data warehouse is a relational database built with:

The Data Transformation Services (DTS)⁸ function of the SQL server enabled open database connectivity (ODBC) linkages to different database platforms (e.g., application databases from Pharmacy and Laboratory) via the Active X scripting engine. This function was programmed to automate data extraction from each primary server to our eID clinical data warehouse. To minimize any potential impact on the primary transactional server, the extraction step was scheduled once every twenty-four hours, between 1:00 am and 5:00 pm. Data extraction of one day's data takes about two hours. The selected data elements are stored in Microsoft SQL tables. Although extraction could occur more frequently than once a day, currently, no CARP studies require this.

We imported data from 13 different computing and operating systems—pharmacy (3), laboratory (2), radiology (1), medical records (6), and emergency department (1)—in the three CARP hospitals. The clinical data warehouse also stores data generated by CARP studies or surveillance programs (e.g., infection control surveillance). After three years of operation during 1999 to 2002, the database contained 50 GB of information encompassing approximately 130,000 admission/discharge, 22,000,000 laboratory, 6,000,000 pharmacy, 2,000,000 radiology, 1,000,000 emergency department, and 500,000 procedure/diagnosis records ( and ).

Electronic data sources do not encompass all the data necessary for analysis. Much of the primary clinical data, such as vital signs (e.g., blood pressure or temperature) or clinical assessments (e.g., coma scores, urinary output) are collected manually for specific studies. Additional data, such as outcome evaluations (e.g., infection control surveillance) and investigator determination about appropriateness of antimicrobial therapy, also must be transformed into an electronic format for analysis. We have used an optical character recognition (OCR) program for this transfer. In our studies, there has been ≥99% agreement between data collection forms and scanned data. In general, we have used scanning methods rather than keystroke data entries when greater than 3,000 data fields (e.g., 30 data fields × 100 surveys) require input. In our experience, the amount of time for keystroke data entry for more than 3,000 data fields exceeds the effort needed to develop a scan form and to input and verify data.

Validating electronic data for completeness, continuity, and accuracy requires a substantial investment of time and is an ongoing process. Missing or incorrect data from the primary sources, the hospitals' transactional databases, can result from errors in the data entry by care providers, hospital clerks, pharmacy technicians, or laboratory workers.^5,14 Also, corruption of data can occur during data transmission, storage, or retrieval. By abstracting a sample of our data and manually comparing the data set with the source data, we were able to find missing data and revise the abstraction programming code for each data set. We repeated this process until there was 100% agreement between data sets from our sample. A final check on validating our abstraction and data management for any new report was to compare patient-specific data from three systems, our data warehouse, the hospital information system vendor's application used to view patient results, and the patient medical record. Data from the hospital information system and from our clinical data warehouse matched, but the paper record missed one or more laboratory results 80% of the time. As an ongoing validation, we periodically compare reports programmed from our data warehouse with existing hospital reports (e.g., admission data, pharmacy expenditures, or shoe-leather surveillance programs). This process is performed by the personnel who use the information and by the programmers who developed the applications.

The administration of the hospitals' information system data repository is a contracted service through an extramural vendor. This operational practice limited the development of technical expertise in database management within the hospitals' information services staff and restricted their ability to assist us in establishing a connection to the application servers. We learned that staff from each clinical department (e.g., pharmacy, laboratory, medical records, radiology) assisted with the design and management of data in its own server. Understanding the design of the information system software architecture and determining data flow of the two-tiered server configuration were critical to successfully build our clinical data warehouse.

To create links to local servers, we found it essential to use established, or to develop new, relationships with the clinical department directors and staff who managed each local server. In exchange for the time provided by these staffers, we helped them access data. For example, we abstracted pharmacy data about unit-level drug dispensing practices, information the pharmacy needed to plan implementation of automated drug dispensing machines.

Selecting data elements from each database required knowledge of the database model and the data dictionary and clinical content expertise. Ideally, we would have had the documentation supporting each application, but often this was not available. Once we could view the database contents, clinically trained staff helped interpret the data variables and relational table design. The pharmacy database was relatively simple in design and required abstracting drug data from only one table. In addition, one of the pharmacy directors was able to provide important assistance by directing us to tables containing census information that we needed to report the antimicrobial use per patient days. In contrast, the laboratory database was complicated, and we required assistance from the software vendor's application specialist. The microbiology laboratory database had over 600 tables, only a fraction of which contained data elements relevant to the CARP study. We created one microbiology table by linking ten different tables; some of these tables contained only two variables (e.g., tables that contained encoded data descriptors). After data aggregation and validation, we learned that some laboratory results were archived to other data tables. Much effort was required to review the large number of tables, to determine the meaning of encoded variables, to locate stored variables, and to learn how to link tables and to abstract complete data sets. In addition to the complicated table structure, the microbiology data were partitioned into time-dependent preliminary and final results, and each of the final pathogens had a panel of 1 to ≥10 antimicrobial susceptibility results.

Few published studies report on the accuracy or usability of clinical warehouse data. Because data accuracy is highly variable,¹⁴ methods should be applied for continuous monitoring. In addition, data may be accurate but not in a usable format for computation. For example, in our system's pharmacy tables, the field containing the dose of a medication could be expressed in three different ways: the dose field could be a number representing the actual dosage (e.g., 250 mg of medication); the dose field could represent the volume of fluid to be infused to administer one dose (e.g., 10 mL of medication); or the dose field could indicate the number of units to be administered (e.g., two pills). To address this problem, we had to create a new dose field by having clinical staff manually transform the free text into uniformly comparable data. Once the initial mapping of the 3,247 unique antibiotic dosing combinations in our pharmacies' formularies was completed (approximately 80 person-hours of labor), the ongoing update of new lines of data was minimal but repeated every three months. Another usability problem occurred when the clinical data warehouse encountered nonstandardized data formats. For example, in our pharmacy tables, at times, the drug name also included the preparation strength, which made simple queries by medication name nearly impossible. A third set of usability issues resulted from storage of data as text. The microbiology table contained the results of infrequently performed tests in a free-text comment field that was not abstracted easily. Also, the radiology text-based report files were compressed, and access to them required proprietary extraction software and natural language processing software to interpret these reports.¹⁷

Changes in application software and vendors are inevitable, and this requires clinical data warehouse developers to be continually in “development and refinement” mode. provides an overview of our hospitals' information systems, the data sets that we have been able to obtain, and the time frame for new systems. In 2002, the Cook County Bureau of Health Services contracted with a new clinical information system vendor. This evolution in the information systems will require that CARP repeat the data acquisition process ( and ) or access these data from a data repository function planned by the Cook County Bureau of Health Services Information Systems Department.

Because antibiotic combinations with redundant antimicrobial spectra are a potentially remediable source of excessive antimicrobial use, we wrote a computer program to detect combinations of antibiotics with overlapping spectrum of activity (i.e., two antibiotics that target the same or very similar bacteria).²¹ Using Web-based access to the computer program, a clinical pharmacist was able to query the database using drop-down menus, select a specific day, and obtain a list of patients who were receiving two or more antibiotics with overlapping activity. The patient list, sorted by unit location, noted all antimicrobials prescribed and highlighted the redundant combinations. The patient information could be printed with an attached data collection instrument to record follow-up observations. During a five-week test period, 71% of eligible cases flagged by the computer were judged to be truly redundant based on a clinical review, physicians readily accepted this unsolicited advice in 98% of these cases, and drug cost savings were $4,500.²¹ Before the clinical data warehouse, surveillance for this type of medication error was not performed routinely. During the development for this intervention, a manual review of one day's antimicrobial use in our hospital required 10 person hours (PH) just for case identification. Automated real-time identification of this potential medication error allowed the clinical pharmacist to shift his focus from reviewing patient charts for errant medication orders to evaluating the appropriateness of therapy and conferring with the prescribing physician.

The measurement of antimicrobial utilization was programmed to trend specific drugs by predefined daily dose, duration of therapy in days, and number of courses of antimicrobial therapy.²⁰ Utilization was stratified by administration route (intravenous or oral), patient location, and time (monthly aggregation). Using the inpatient census data, rates of use were normalized (e.g., rates per 1,000 patient-days) for comparison. Automated trend reports of antimicrobial utilization are available at the investigators' desktop computers, through the study's intranet Web site. Without the clinical data warehouse, 0.37 full-time equivalent (FTE) or 770 PH per year would be required to quantify antimicrobial use; with the clinical data warehouse, after the initial development of programming code (160 PH), clinical staff have “as needed” access to reports offering additional levels of unit-based analysis. Previous improvement activities undertaken by study investigators required manual tabulation of antimicrobial utilization.²⁴