EPIC - This document provides a step-by-step documentation including SQL scripts and python procedures to extract ONC standard datasets from Epic and load an i2b2 data warehouse. https://datahandbook.epic.com/Reports/Details/9000400 (Need to be a EPIC customer to view)
AllScripts - Data Schema https://developer.allscripts.com/Account/Login?ReturnUrl=%2fDownloads#clientkits (Need to be a All Scripts customer to view)
| TABLES | PRIMARY KEYS |
|---|---|
| DEMOGRAPHICS | IMREDEM_CODE |
| SCHEDULE | IMRESCHED_CODE |
| ENCOUNTER | IMREENC_CODE |
| CONTACT | IMRECONTACT_CODE |
| IMMUNIZATION_RECORD | IMMREC_ID |
| LABORDERS | IMRELABORDER_CODE |
| MEDICATIONS | IMREMED_CODE |
| PROVIDER | IMREPROV_CODE |
| RESULT | RESULT_ID |
| VITALS_DATA | VITALS_CODE |
| DX | IMREDX_CODE |
| HX_DIAGNOSIS | HX_DIAGNOSIS_ID |
Common Patient Tables
• HPSITE.DEMOGRAPHICS – Name, DOB, SSN, Bloodtype, Language, Marital Status
• HPSITE.ADDRESSES – holdsaddressesfor5typesofentities
• Patient, Provider, Site, Insurance Carriers, Institutions
• HPSITE.INSURANCES – containsinsuranceinformationforthepatient
• HPSITE.DEMOGRAPHICPICTURE ― link to picture in the EHR that is associated with the patient
• HPSITE.PHARMACYFAVORITE ― Containsprimarypharmacyinformation
• HPSITE.DEMGUARANTOR ― contains patient guarantor information
Nextgen - Two Presentations on the data schema
Cerner - Download Cerner Millennium Data Model here. (Need to be a Cerner Customer)
person table contains patient data like name and date of birth.prsnl table holds data for personnel and there is an alias table prsnl_alias (to look up Provider NPI for example).person_prsnl_reltn personnel related to a patient. For example, this table can be used to look up patient's Primary care physician.encntr_prsnl_reltn can be used to look up Admitting or Attending physicians.address and phone tables are used to store various types of addresses and phone numbers for patients and physicians.organization table contains details about hospitals (facilities), outpatient practices, vendors, payers etc.location, nurse_unit, room and bed tables can help you determine exact location of your patient in hospital down to a room and a bed.orders,order_catalog, order_action, order_detail and order_ingredient are used to pull orders, alerts, drugs administration and procedures.diagnosis and nomenclature tables keep various classifications like CPT and ICD and can be tied to an encounter. For example, you can pull all the ICD-10 codes which are final coded or only ones present on admission.pathway and pathway_catalog store details about powerplans.clinical_event table (accumulated thousands of events per patient encounter for every day of stay such as vital signs, drugs administration time, lab results and so much more. It is huge as well!