i2b2 Working Groups
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

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!
  • No labels