Secondary Logo

A Systematic Approach to Creation of a Perioperative Data Warehouse

Hofer, Ira S. MD; Gabel, Eilon MD, MS; Pfeffer, Michael MD; Mahbouba, Mohammed MD, MS; Mahajan, Aman MD, PhD

doi: 10.1213/ANE.0000000000001201
Technology, Computing, and Simulation: Technical Communication

Extraction of data from the electronic medical record is becoming increasingly important for quality improvement initiatives such as the American Society of Anesthesiologists Perioperative Surgical Home. To meet this need, the authors have built a robust and scalable data mart based on their implementation of EPIC containing data from across the perioperative period. The data mart is structured in such a way so as to first simplify the overall EPIC reporting structure into a series of Base Tables and then create several Reporting Schemas each around a specific concept (operating room cases, obstetrics, hospital admission, etc.), which contain all of the data required for reporting on various metrics. This structure allows centralized definitions with simplified reporting by a large number of individuals who access only the Reporting Schemas. In creating the database, the authors were able to significantly reduce the number of required table identifiers from >10 to 3, as well as to correct errors in linkages affecting up to 18.4% of cases. In addition, the data mart greatly simplified the code required to extract data, making the data accessible to individuals who lacked a strong coding background. Overall, this infrastructure represents a scalable way to successfully report on perioperative EPIC data while standardizing the definitions and improving access for end users.

Supplemental Digital Content is available in the text.

From the Departments of *Anesthesiology and Perioperative Medicine and Medicine, David Geffen School of Medicine at UCLA, Los Angeles, California; and Office of Health Informatics and Analytics, David Geffen School of Medicine at UCLA, Los Angeles, California.

Accepted for publication December 23, 2015.

Funding: None.

The authors declare no conflicts of interest.

Supplemental digital content is available for this article. Direct URL citations appear in the printed text and are provided in the HTML and PDF versions of this article on the journal’s website.

Reprints will not be available from the authors.

Address correspondence to Ira S. Hofer, MD, Department of Anesthesiology and Perioperative Medicine, David Geffen School of Medicine at UCLA, 757 Westwood Blvd., Los Angeles, CA 90095. Address e-mail to

The 2009 American Recovery and Reinvestment Act created “meaningful use criteria” for the adoption and implementation of electronic medical records (EMRs)1 assuming that increased adoption of EMRs would improve the quality of care and reduce costs.2,3 Unfortunately, these cost savings have yet to be realized,4 and some have found that EMRs have paradoxically increased the cost of care by allowing improved billing capture.5,6 One barrier to improved care quality and associated cost savings is the difficulties associated with turning EMR data into actionable information that can be used to improve health care delivery and outcomes.7

The transition from volume-based payments to value-based payments encouraged by the Affordable Care Act as a way to realize these savings requires consistent and reliable extraction of data from the EMR for both measurement and use in quality improvement programs.8 The Perioperative Surgical Home,9 the American Society of Anesthesiologists’ implementation of an accountable care organization, specifically targets getting these data10 through the early use of a data registry as part of its rollout. Despite the urgency of this need, as well as significant effort, these data remain difficult to obtain.

Currently, EPIC Systems’ EMR is the largest EMR platform, with more than half of the US population now having a patient record in an EPIC system.11 Although there has been some success in extracting data into uniform data models from other systems,12 EPIC EMRs have been particularly challenging, given their expansiveness and large number of tables (>15,000).

A well-established method to combine disparate, and often unstructured, data in such a way as to make it more easily accessible to the end users is to create a data warehouse (Appendix 1). We present our experience and describe the methodology for successfully extracting clinical data around the entire perioperative period from our EPIC EMR (Epic Systems, Verona, WI) into an independently designed data warehouse designed for business intelligence that simplifies access to the data and standardizes definitions so as to allow multiple groups to report off of the same data.

Back to Top | Article Outline


After obtaining exemption from informed consent from the University of California, Los Angeles IRB, a review of clinical and operational metrics desired for reporting was undertaken. The necessary raw clinical data were located in Clarity, the relational database created by EPIC for data analytics and reporting. Given the array of metrics needed and the complexity of the data structure in Clarity, a 2-stage data warehouse was constructed to reduce the need to join and optimize multiple tables.

The first stage, termed “Base Tables,” was designed to serve as a middle layer decreasing the number of tables and simplifying the joins between them. Conceptually, the tables coalesced into 3 groups: (1) patient-centered information (laboratories, allergies, medial history, etc.); (2) encounter-centered information (admission, discharge, and transfer [ADT], orders, notes, laboratories, etc.); and (3) operative procedure-centered information (staffing, scheduling, room times, etc.). Tables are joined by 1 of 3 of the following fields: (1) a patient identifier (pat_id); (2) a case identifier (case_id); or (3) an encounter identifier (enc_id). In creating these tables, effort was made to validate the completeness of the data by comparing data with independent sources, such as billing records, registry data, and clinical experience.

Figure 1

Figure 1

Amalgamation of the data into the Base Tables provided structure; however, the data remained cumbersome for reporting on specific metrics. To provide this simplicity for report writers, a series of Reporting Schemas centered around specific patient-centered episodes (operative cases, obstetric cases, hospital admissions, etc.) were created. The goal was to create a set of highly validated tables containing desired metrics in a way that can be directly incorporated in to a business intelligence tool or other reporting software. Each schema has a number of tables centered around a clear concept (i.e., list of operative cases, preoperative information, postanesthesia care unit information, etc.), and each column in each table was specifically based on 1 or more metrics targeted for reporting. The data were heavily validated against both external data sources and clinical experience. The code to populate the tables incorporated underlying metric definitions, so that subsequent queries can use the same definitions without requiring rewriting the code. The structure of the Base Tables and Reporting Schemas can be seen in Figure 1, and an example of the documentation for a table in a Reporting Schema can be seen in Supplemental Digital Content 1 (

Back to Top | Article Outline


Data required for the identified metrics were housed in 56 Clarity tables and then reduced to 22 Base Tables (10 case based, 7 admission based, and 5 patient based). A list of these tables, a description of data contained, and the number of Clarity source tables are shown in Table 1. Each of the Base Tables contains a specific type of information of use in metric development. Data for a specific case (or admission) can be obtained by joining tables. For example, events for case 12345 would be obtained by linking the “Case Base List” to “Case Events,” and similarly medications administered for that case would be obtained by linking the “Case Base List” to “Case Medications.” In each case, this involves a single join, doing the same in Clarity would require linking 19 tables for the events (15 for the case base list + 4 for the events) or 20 for the medications (15 for the base list +5 for the medications).

Table 1

Table 1

At the time of initial database creation, there were 99,657 anesthetics performed, 164,701 surgeries/procedures performed, and 182,975 surgeries/procedures scheduled since EPIC installation (March 2013). In Clarity, each case contained up to 7 distinct case identifiers and 5 distinct hospital admission identifiers. Comparing the data with those from our billing demonstrated that cases were missing from scheduling, case log, or anesthesia data in 18,094 (18.4%) of all anesthetic cases and 19,154 (11.7%) of all cases making linking inconsistent. These issues were resolved in all but 157 (0.1%) instances by using a single-case identifier and linking based on a hierarchy of criteria.

Table 2

Table 2

Figure 2

Figure 2

Transitioning from Clarity to the Base Tables resolved errors in linking and caused a marked reduction in the number of tables and fields required for joining; however, the code to generate metrics often remained quite lengthy and complex. Table 2 contains summary statistics on code for 4 metrics written against the 3 different levels, and Figure 2 is a visual depiction at the tables involved in calculating case cancellation data at each state of the data warehouse (Clarity, Base Tables, and Reporting Schema). The raw code for all metrics is shown in Supplemental Digital Content 2 ( Use of the Reporting Schemas often required only a single line of code and single table for obtaining a given metric. Given the vast reduction in complexity business analysts and others, generating reports called directly off the Reporting Schemas, making the data much more accessible to those who lacked a strong programming background.

Back to Top | Article Outline


Historically, anesthesia informatics has centered on the Anesthesia Information Management System, many of which are now used to contribute to the Multicenter Perioperative Outcomes Group or Anesthesia Quality Institute. This data mart expands on that foundation to incorporate data from across the perioperative period, including not only preoperative information (laboratory data, comorbidities, vital signs, etc.) that may be in some Anesthesia Information Management System but also extensive postoperative data such as complications, length of stay, and readmissions that are frequently missing.

The increasing use of data to drive organizational decisions has created the need to develop tools allowing those with less technical knowledge access to increasingly sophisticated data. Traditional methods or having a report writer create separate reports for each business case can be quite cumbersome, has long turnaround times, and requires a strong background in database query and design. The goal of this data warehouse was to make these data accessible to those who might lack these skills.

The metrics contained in the Reporting Schemas were dictated by organizational needs. Once a metric was defined, it was reported on for all operative cases, not just those in a specific cohort of cases (as would be the case with traditional independent queries for each report). Instead, the scope of the report is limited by the report writer using their analytics software. This results in an ever increasing library of metrics for reporting and a dramatic decrease in the technical skill needed to generate reports, as seen in Table 2 and Supplemental Digital Content 2 (

The limitations of creating this data mart reflect the underlying complexity and fluidity of the EPIC data structure. First, because each EPIC implementation is different, the overall structure and concepts described here can be replicated at another institution, but the detailed code and data validation would need to be developed based on the workflow at that institution. Second, although the final data extraction from the Reporting Schema may be straightforward, implementation of the data mart requires personnel who have the technical ability to build a database and enough clinical knowledge to help drive the metric creation and data validation. These resources may be beyond the scope of smaller institutions.

The rapid adoption of EMRs over the past 5 years, combined with the transition to a value-based model of care, has resulted in a rapidly growing need to improve extraction of data from the EMR. The data model presented here has provided the authors and their institution with a dramatically improved ability to rapidly find and report on all aspects of the perioperative encounter, and, thus, it is a valuable tool in improving perioperative patient care.

Back to Top | Article Outline


Definition of Key Terms

  • Data warehouse: A large store of data integrated from a wide range of sources and used for reporting and data analysis.
  • Data mart: The access layer of the data warehouse environment that is used to get data out to the users. The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts are small slices of the data warehouse.
  • Data-sprawl: The replication of data across multiple sources, making it difficult to keep data in sync and create security and compliance challenges.
  • Relational database: A collection of data items organized as a set of formally described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.
  • Episode of care: A discrete period of care often organized around a central theme. For example, all appointments, tests, and studies necessary for a given surgical procedure would comprise an episode of care.
  • Clarity: The relational database system created by EPIC to support the analysis and report functions.
  • Schema: A way to logically group objects such as tables, views, and stored procedures in a relational database.
  • Data integrity: Maintaining and assuring the accuracy and consistency of data over its entire life cycle.
  • Index: A data structure that improves the speed of data retrieval operations on a database table.
Back to Top | Article Outline


Name: Ira S. Hofer, MD.

Contribution: This author helped design the study, conduct the study, analyze the data, and prepare the manuscript and is the archival author.

Attestation: Ira S. Hofer attests to the integrity of the original data and the analysis reported in this manuscript and approves the final manuscript.

Name: Eilon Gabel, MD, MS.

Contribution: This author helped design the study, conduct the study, analyze the data, and prepare the manuscript.

Attestation: Eilon Gabel attests to the integrity of the original data and the analysis reported in this manuscript and approves the final manuscript.

Name: Michael Pfeffer, MD.

Contribution: This author helped prepare the manuscript.

Attestation: Michael Pfeffer approves the final manuscript.

Name: Mohammed Mahbouba, MD, MS.

Contribution: This author helped prepare the manuscript.

Attestation: Mohammed Mahbouba approves the final manuscript.

Name: Aman Mahajan, MD, PhD.

Contribution: This author helped design the study, conduct the study, analyze the data, and prepare the manuscript.

Attestation: Aman Mahajan approves the final manuscript.

This manuscript was handled by: Maxime Cannesson, MD.

Back to Top | Article Outline


1. Blumenthal D, Tavenner M. The “meaningful use” regulation for electronic health records. N Engl J Med 2010;363:501–4.
2. Jha AK. Meaningful use of electronic health records: the road ahead. JAMA 2010;304:1709–10.
3. Hebert CJ: Electronic health records and quality of diabetes care. N Engl J Med 2011;365:2338–9.
4. Teufel RJ II, Kazley AS, Ebeling MD, Basco WT Jr. Hospital electronic medical record use and cost of inpatient pediatric care. Acad Pediatr 2012;12:429–35.
5. Kianfar H, Leighton H, Avezbadalov A, Buch K, Kerwin T. Does the implementation of an electronic medical record reduce duplicate testing? A study of 25,000 transthoracic echocardiograms. J Am Coll Cardiol 2013;61:E1526.
6. Abelson R, Creswell J, Palmer G. Medicare bills rise as records turn electronic. September 21, 2012 New York Times.
7. DesRoches CM, Campbell EG, Vogeli C, Zheng J, Rao SR, Shields AE, Donelan K, Rosenbaum S, Bristol SJ, Jha AK. Electronic health records’ limited successes suggest more targeted uses. Health Aff (Millwood) 2010;29:639–46.
8. Fisher ES, Shortell SM, Kreindler SA, Van Citters AD, Larson BK. A framework for evaluating the formation, implementation, and performance of accountable care organizations. Health Aff (Millwood) 2012;31:2368–78.
9. Cannesson M, Kain Z. The Perioperative Surgical Home: an innovative clinical care delivery model. J Clin Anesth 2015:185–7.
10. Kain ZN, Hwang J, Warner MA. Disruptive innovation and the specialty of anesthesiology: the case for the Perioperative Surgical Home. Anesth Analg 2015;120:1155–7.
11. Koppel R, Lehmann CU. Implications of an emerging EHR monoculture for hospitals and healthcare systems. J Am Med Inform Assoc 2015;22:465–71.
12. Voss EA, Makadia R, Matcho A, Ma Q, Knoll C, Schuemie M, DeFalco FJ, Londhe A, Zhu V, Ryan PB. Feasibility and utility of applications of the common data model to multiple, disparate observational health databases. J Am Med Inform Assoc 2015;22:553–64.

Supplemental Digital Content

Back to Top | Article Outline
© 2016 International Anesthesia Research Society