Data Warehouse Case Study Essay

news report of the CDR When the chuck began in 199596, the CDR, initially referred to as the clinical research selective informationbase, was int closinged to tin and enhance clinical research at the University of Virginia by providing clinicians, students, and researchers with direct, rapid access to ex post facto clinical and administrative patient information. Re? ecting this intent, the schema was funded by the School of medicinal drug and ho dropd in the schoolman Computing considerablyness Sciences group, which is distinct from the medical centers IT group. With considerable assistance and cooperation from info owners and stewards, bequest info from some(prenominal) contrastive sources were unwavering into a single relative selective informationbase and sporadically updated. Authorized drug substance ab engrossrs accessed the CDR through a stock Web browser and viewed or downloaded data to their private computers for further analysis.Initially, emphasis wa s fit(p) on getting the CDR running as quickly as possible and with a minimum of resources consequently, extensive transformation of data to an enterprise data model was non performed. The CDR project team up consists of 2. 53. 0 FTEs (full-time equivalents) maven developer, one developer-database administrator, and portions of analyst, clinician, and administrative FTEs. To date, the be of developing and operating the CDR find been slightly $200,000 per year, under create verbally by the School of Medicine.Over the pedigree of the project, there contribute been signi? cant enhancements to the drug drug user interface, incorporation of supernumerary data sources, and the growth of an integrated data model. There has to a fault been increasing interest in development the CDR to serve a broader audience than researchers and to substitute management and administrative functionsto meet the scrap of providing a way for anyone with a postulate to knowat every take of th e organizationaccess to accurate and well timed(p) data necessary to support efficacious decision making, clinical research, and sour improvement.In the argona of education, the CDR has become a meat t from each oneing resource for the Department of wellness Evaluation Sciences pilots program and for the School of Nursing. Students use the CDR to understand and master informatics issues such(prenominal) as data capture, vocabularies, and coding, as well as to perform flake sight A selective information w arhouse for an Academic checkup Center 167 exploratory analyses of health care questions. Starting in Spring 2001, the CDR testament also be introduced into the universitys undergrad medical curriculum. System Description pastime 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 Dell PowerEdge 1ccc (Dual Intel 400MHz serveors, 512MB RAM) running the Linux op erating governing body and Sybase 11. 9. 1 relational database management system. For storage, the system uses a Dell Powervault 201S 236GB RAID dish Array. As of October 2000, the database contained 23GB of information about 5. 4 million patient visits (16GB visit data, 7GB laboratory results). Data loading into Sybase is achieved employ wont Practical Extraction and Report lyric (Perl) programs. CDR Contents. The CDR streamly draws data from four autarkical systems (see Table 1).In addition, a come up of derived values (for example, number of days to beside inpatient visit, number of times a diagnostic code is use in various settings) are computed to entrust thick information for selected data elements. Data from each of these source systems are integrated 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 collective Table 1. Contents of the CDR Type of D ata Inpatient, outpatient visits Source of Data Shared Medical Systems DescriptionPatient registration and demographic data, diagnoses, procedures, unit and census information, billing proceeding, including medications, costs, charges, reimbursement, amends information Physician billing transactions from inpatient and outpatient visits, diagnoses, and procedures laboratoryoratory test results purchasable Dates Jul 1993Jun 2000 Professional 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 microbiology results, arouse summaries (and other narrative data), outpatient prescribing information, order ingress details, and tumor registry information. As of October 2000, we have just ? nished incorpora ting death registry data from the Virginia Department of Health into the CDR. These data willing suffer our users with direct access to much comprehensive mortality outcomes data than are contained in local information systems, which chiefly are restricted to an in-hospital death indicator. exploiter Interface. The user interface runs in a standard Web browser and consists of a data dictionary, a collection of crude gateway interface (CGI) programs implemented victimization the C programming language, and JavaScript modifyd HTML pages. structure query language (SQL) statements are bewilderd automatically in response to point-and-click actions by the user, modify submission of ad hoc queries without prior acquaintance of SQL. The SQL queries are sent to the CGI programs that query the database and remember results in dynamically created HTML pages.The correct process is controlled by the contents of the data dictionary, which is used to format SQL results, set up HTML lin ks for data drill-down, and provide on-line help. Data may be downloaded immediately into Microsoft Excel or another(prenominal) analysis tool on the users workstation. Query Formulation. Most CDR users use the Guided Query function to encounter data. This process involves three steps 1. De? ne a tribe of interest by setting conditions, for example, date ranges, diagnostic codes, medico identi? ers, service locations, and lab test codes or values. 2.Submit the query, specifying how much data the CDR should travel by (all matching data or a speci? ed number of rows). 3. After the CDR returns the population of interest, use the Report Menu to explore various attributes of the population on a case-by-case or group level. use of goods and services 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 opt ions. In addition, browsing the data may help the user generate ideas for additional queries.We believe that it is helpful for end users to go through this query process themselvesto directly engage the data. However, many users, specially 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 volume change magnitudes, our ability to provide data in a incidentally manner may fall off. Security. A steering committee of clinicians guided the initial development of the CDR and established policies for its utilization and access. plainly authorized users may log onto the CDR.To shelter con? dentiality, all patient and medico identifying information has been partitioned into a secure Case Study A Data Warehouse for an Academic Medical Center 169 database. comment from or to disguised identi? ers to or from real identi? ers is possible but re quires a written request and appropriate approval (for example, from a supervisor or the human investigations committee). any 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 basis for improving the CDR to enable users to retrieve the data independently and to add-on engagement 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 measurable practise that has helped guide planning for changes and enhancements and for allocation of our check resources.Efforts to evaluate the CDR have included several approaches Monitoring user population and exercise patterns Administering a CDR user survey bring in queries submitted to the CDR and performing follow-up telephone interviews workout Statistics. Voluntary rule 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 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 folk 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 accumula tive number of active users (those who submitted a query) and demonstrates a linear growth pattern.