Changes between Initial Version and Version 14 of UHCSource

Remember: No patient names, identifiers, or other PHI


Ignore:
Timestamp:
Apr 22, 2016 9:10:42 AM (5 years ago)
Author:
dconnolly
Comment:

cite CDB/RM file spec docs

Legend:

Unmodified
Added
Removed
Modified
  • UHCSource

    v1 v14  
     1Data submitted by KU Hospital to the University !HealthSystem Consortium ([https://www.uhc.edu/ UHC]) is used as the source for hospital
     2billing diagnoses and also provides quality, visit, and additional
     3procedure details. Key observations include:
     4 * Length of Stay
     5 * Comorbidity, MS-DRG
     6 * Service Line
     7 * Agency for Healthcare Research and Quality (AHRQ) measures such as Central Venous Catheter Blood Stream Infections
     8
     9Integration of UHC data from KUH has been released to the [[HERON]] user community:
     10
     11 * [blog:2012/03/heron-el-dorado-release HERON El Dorado Release incorporates searching by MSDRG and LOS]  -- Posted on 2012-03-08
     12 * [blog:heron-bighill-release HERON: Big Hill release adds home medications, other medication orders, and expands UHC data]  -- Posted on 2012-04-12
     13
     14We presented this work at AMIA CRI in March 2013:
     15
     16 1. Nair M, Connolly DW, Whittkopp C, Waitman LR. Combining Quality and Accountability Study Metrics from University Health System Consortium in an i2b2 based Clinical Data Repository with EMR data for Patient Centered Outcome Research (PCOR). AMIA Clinical Research Informatics Joint Summit. San Franciso, CA, USA; 2013.
     17
     18== Source code and development notes for UHC ETL == #uhc-etl-dev
     19
     20While the code is not (yet) packaged for use independent of the KUMC HeronLoad environment, you are welcome to study it as-is:
     21
     22 - source:heron_load/uhc_i2b2_transform.sql
     23 - source:heron_load/uhc_clinical_facts_load.sql
     24 - source:heron_load/test_uhc.sql
     25 - source:heron_load/uhc_concepts_load.sql
     26
     27
     28''not yet published:''
     29  - source:heron_staging/UHC
     30
     31Development notes include:
     32
     33[[TicketQuery(id=280|834|917|1243|1244|1719|1720,group=milestone)]]
     34
     35=== CDB/RM File Specification #cdb-rm-file-spec
     36
     37A copy of the specification was provided by KUH staff to the HERON team:
     38 - `CDBRM_Dwnloads_File_Specification (4).xlsx`, Pilarz, Thu 4/16/2015 3:42 PM
     39
     40== Requirements gathering and design notes == #design
     41
     42from [ticket:834#comment:8 Feb 23, 2012 meeting]
     43
     44Discussion of where to start
     45
     46 * load things in a draft separate ontology first ala the NAACCR TumorRegistry
     47
     48 * focus on on query first that would involve length of stay, service line and msdrg
     49
     50 * could tackle visit linking with HospitalEpicSource later.  Note #333 re visit linking between IDX and Epic.
     51
     52Dan: what's the story?
     53
     54 1. How many patients do I have length of stay and service line is kidney/pancreas transplant? How about "hip fracture"? ''figure out where "hip fracture is in UHC"''
     55 * of those, how many have a LOS < 3 days?  LOS > 7 days?
     56 * then explore comorbidities and adverse events that may explain variance (long run, in R) but early exploration with i2b2 like:
     57   * look at Epic/IDX diagnoses and see if there are more LOS > 7 days if the patient has diabetes and/or congestive heart failure.  Or weight's impact/BMI.  And likely labs related.
     58   * can also explore mortality ala SSDI and Hospital.
     59 * do this with data post Nov 2007 of course we just have a year of uhc data. 
     60
     61Technical notes with Dan:
     62
     63- technically, LOS should be done in the visit dimension.  But, we haven't done that yet. 
     64  - this is unprecedented for our team but let's see if we can make it work.
     65- First pass, we don't care if the comorbidity was documented during the visit.
     66
     67Sketching some SQL:
     68
     69{{{
     70-- Check that we're in the KUMC sid.
     71select  recordid from UHC.encounter where 1=0;
     72
     73-- Service Line facts
     74select 'UHC|ServiceLine:' || e.uhcserviceline as concept_cd
     75  , e.patientid
     76  , e.admissiondate as startdate
     77  , e.dischargedate as enddate
     78  , e.encounterid
     79from UHC.encounter e;
     80
     81-- Note: the concept_cd should perhaps be 'UHC|SERVICELINE:' for consistency
     82
     83}}}