Data Warehouse Case Study Essay

History of the CDR When the proj electroshock began in 199596, the CDR, signly referred to as the clinical research entropybase, was intended to support and enhance clinical research at the University of Virginia by providing clinicians, students, and researchers with direct, rapid access to retrospective clinical and administrative patient selective learning. Re? ecting this intent, the strategy was funded by the School of Medicine and ho exercised in the faculty member calculation Health Sciences group, which is distinct from the medical centers IT group. With considerable tending and cooperation from selective information owners and stewards, legacy entropy from several different sources were loaded into a single relational informationbase and periodically up durationd. Authorized users accessed the CDR through a idealised Web browser and viewed or downloaded data to their person-to-person computers for further analysis.Initially, wildness was placed on getting the CDR running as quickly as possible and with a minimum of resources consequently, extensive transformation of data to an opening data model was not performed. The CDR project team consists of 2. 53. 0 FTEs (full-time equivalents) one developer, one developer-database administrator, and portions of analyst, clinician, and administrative FTEs. To date, the costs of developing and operating(a) the CDR nurture been approximately $200,000 per year, underwritten by the School of Medicine.Over the course of the project, there make for been signi? cant enhancements to the user interface, incorporation of additional data sources, and the development of an co-ordinated data model. There has also been increasing interest in utilize the CDR to serve a broader audience than researchers and to support management and administrative functionsto meet the challenge of providing a way for anyone with a need to knowat every level of the organizationaccess to accurate and timely data necessary to support effective decision making, clinical research, and exercise improvement.In the argona of education, the CDR has become a core teaching resource for the part of Health Evaluation Sciences masters broadcast and for the School of Nursing. Students use the CDR to understand and master informatics issues such as data capture, vocabularies, and coding, as well as to perform Case written key A selective information Warehouse for an Academic Medical Center 167 searching analyses of healthcare questions. Starting in Spring 2001, the CDR bequeath also be introduced into the universitys undergraduate medical curriculum. System Description Following is a brief overview of the CDR application as it exists at the University of Virginia.System Architecture. The CDR is a relational data warehouse that resides on a dingle PowerEdge 1300 (Dual Intel 400MHz processors, 512MB RAM) running the Linux operating system of rules and Sybase 11. 9. 1 relational database management system. For s torage, the system uses a Dell Powervault 201S 236GB RAID Disk Array. As of October 2000, the database contained 23GB of information about 5. 4 meg patient visits (16GB visit data, 7GB laboratory results). information loading into Sybase is achieved using impost Practical Extraction and Report Language (Perl) programs. CDR Contents. The CDR currently draws data from 4 independent systems (see Table 1).In addition, a design of derived values (for example, telephone number of days to next inpatient visit, number of times a diagnostic code is used in various imbedtings) are computed to provide epitome information for selected data elements. Data from each of these source systems are co-ordinated into the CDRs data model. In addition to the current contents listed in Table 1, users and the CDR project team have identi? ed additional data elements that might be incorporated Table 1. Contents of the CDR Type of Data Inpatient, outpatient visits Source of Data Shared Medical Syste ms DescriptionPatient alteration and demographic data, diagnoses, procedures, unit and census information, billing transactions, including medications, costs, charges, reimbursement, insurance information mendelevium billing transactions from inpatient and outpatient visits, diagnoses, and procedures testing groundoratory test results Available Dates Jul 1993Jun 2000 captain billing Laboratory results Cardiac surgery IDX billing system HL-7 messages from SunQuest Lab System Cardiac surgery outcomes data (de? ned by Society of thoracic Surgeons Oct 1992Jun 2000 Jan 1996Jun 2000 Clinical details for thoracic surgery casesJul 1993Jun 2000 168 Einbinder, Scully, Pates, Schubart, Reynolds into the CDR, including microbio logy results, discharge summaries (and other news report data), outpatient prescribing information, order entry details, and tumor registry information. As of October 2000, we have vertical ? nished incorporating death registry data from the Virginia Department of Health into the CDR. These data will provide our users with direct access to more comprehensive mortality outcomes data than are contained in local information systems, which generally are dependant to an in-hospital death indicator.User Interface. The user interface runs in a standard Web browser and consists of a data dictionary, a collection of green gateway interface (CGI) programs implemented using the C programming language, and JavaScriptenabled hypertext markup language pages. Structured query language (SQL) statements are generated automatically in retort to point-and-click actions by the user, enabling submission of ad hoc queries without prior knowledge of SQL. The SQL queries are sent to the CGI programs that query the database and return results in dynamically created HTML pages.The stallion process is controlled by the contents of the data dictionary, which is used to format SQL results, set up HTML links for data drill-down, and provide on-line help. Data may be d ownloaded immediately into Microsoft Excel or another analysis tool on the users workstation. Query Formulation. Most CDR users use the Guided Query function to retrieve data. This process involves three go 1. De? ne a population of interest by setting conditions, for example, date ranges, diagnostic codes, physician identi? ers, service locations, and lab test codes or values. 2. bow down the query, specifying how much data the CDR should return (all matching data or a speci? ed number of rows). 3. After the CDR returns the population of interest, use the Report add-in to explore various attributes of the population on a case-by-case or group level. Custom reports can also be de? ned, and the results of any report can be downloaded into Microsoft Excel, Access, or other analysis tool. Generally, the query process requires several iterations to modify the population conditions or report options. In addition, browse the data may help the user generate ideas for additional queries.W e moot that it is helpful for end users to go through this query process themselvesto straightway engage the data. However, many users, especially those with a pressing need for data for a meeting, report, or grant, prefer to use CDR team members as intermediaries or analysts. To date, we have attempted to meet this preference, but as query book developments, our ability to provide data in a timely elan may fall off. Security. A steering committee of clinicians guided the initial development of the CDR and established policies for its utilization and access. Only authorized users may log onto the CDR.To protect con? dentiality, all patient and physician identifying information has been partitioned into a dependable Case Study A Data Warehouse for an Academic Medical Center 169 database. Translation from or to disguised identi? ers to or from veritable identi? ers is possible but requires a written request and appropriate grace (for example, from a supervisor or the human inve stigations committee). All data transmitted from the database server to the users browser are encrypted using the secure Netscape Web server, and all accesses to the database are logged.In addition, CDR access is restricted to personal computers that are part of the Virginia. edu domain or that are authenticated by the universitys proxy server. Evaluation Understanding user needs is the primer for improving the CDR to enable users to retrieve the data independently and to increase physical exercise of the CDR at our institution. Thus, assessing the value of the CDRhow well we meet our users needs and how we might increase our user basehas been an all important(predicate) activity that has helped guide planning for changes and enhancements and for apportionment of our limited resources.Efforts to evaluate the CDR have acceptd several approaches Monitoring user population and usage patterns Administering a CDR user survey Tracking queries submitted to the CDR and playacting follow-up telephone interviews Usage Statistics. Voluntary usage of an IS resource is an important measure of its value and of user satisfaction. 5 However, usage of a data warehouse is likely to be quite different than for other types of information resources, such as clinical information systems. A clinical system is likely to be used many times per day a data warehouse may be used sporadically.Thus, although we monitor system usage as a measure of the CDRs value, we believe that relative frequency of usage cannot be viewed in isolation in assessing the success of a data warehouse. Since the CDR went live, more than 300 individuals have requested and obtained logon IDs. As of family line 30, 2000, 213 individuals had logged on and submitted at least one query. This number does not include usage by CDR project team members and does not re? ect analyses performed by team members for end users. Figure 1 shows the cumulative number of active users (those who submitted a query) and d emonstrates a linear growth pattern.