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)
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)
persontable contains patient data like name and date of birth.
prsnltable holds data for personnel and there is an alias table
prsnl_alias(to look up Provider NPI for example).
person_prsnl_reltnpersonnel related to a patient. For example, this table can be used to look up patient's Primary care physician.
encntr_prsnl_reltncan be used to look up Admitting or Attending physicians.
phonetables are used to store various types of addresses and phone numbers for patients and physicians.
organizationtable contains details about hospitals (facilities), outpatient practices, vendors, payers etc.
bedtables can help you determine exact location of your patient in hospital down to a room and a bed.
order_ingredientare used to pull orders, alerts, drugs administration and procedures.
nomenclaturetables 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_catalogstore details about powerplans.
clinical_eventtable (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!