Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/epic_alerts_transform.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 4.3 KB
Line 
1/* epic_alerts_load -- harvest alerts information from epic as observation
2   facts.
3 
4Copyright (c) 2015 University of Kansas Medical Center
5part of the HERON* open source codebase; see NOTICE file for license details.
6* http://informatics.kumc.edu/work/wiki/HERON
7
8Ref:
9
10  * Clarity Data Dictionary - Epic 2012
11    Catalog (Last updated: 07/14/12)
12    https://galaxy.epic.com/Search/GetFile?url=7900!44!100!1770229
13
14*/
15
16/* Check that we're connected to an Epic CLARITY database. */
17select pat_id from CLARITY.patient where 1 = 0;
18           
19create or replace view observation_fact_alerts as 
20    /* Clarity.alt_history.alt_action_inst signifies the instant when the alert is gone after some actions.
21     * For medication interactions, it is the instant after you click Override & Accept or Cancel button on the alert pop-up form.
22     * Use most recent instant when the alert is gone.
23     * To get patient information, link alt_history table to the ALERT table and then link the ALERT table to the PATIENT or PAT_ENC table.
24     * reference: ticket:3587#comment:2; Clarity Data Dictionary - Epic 2012
25     */
26with
27alert_history as
28(select alt_id, pat_csn_id, max(alt_action_inst) as alt_close_date from clarity.alt_history
29 group by alt_id, pat_csn_id order by alt_id, pat_csn_id),
30 
31alert_recs as
32
33     /* used parallel hint enabling a SQL statement to be simultaneously processed by multiple threads or processes to improve performance
34     /* Parallel execution enables a single session and SQL statement to harness the power of multiple CPU and disk devices.*/
35
36(select distinct /*+ parallel */
37    to_char(pe.pat_enc_csn_id) as encounter_ide,
38    al.pat_id as patient_ide,
39    case
40
41    when al.bpa_locator_id is not null
42    then 'KUH|BPA_LOCATOR_ID:'||al.BPA_LOCATOR_ID
43
44    when (al.med_alert_type_c is null and al.bpa_locator_id is null)
45    then 'KUH|GEN_ALERT:'||alr.alt_type_c
46   
47    /* For drug-drug interactions, we use alert_desc as a sort of concept code.
48     * But alert_desc is too long for i2b2's concept_cd, so we hash it.
49     * If we used the 32 bit ora_hash(), odds of collision with ~1700 items
50     * would be around 25%. So we use 160 bit SHA1, which
51     * gives us odds of a collision less than 10^-18, which is plenty.
52     *
53     * Note concept_cd is at most 50 characters in i2b2; 160 bits is 40 hex characters,
54     * which leaves us 10 characters for the scheme, so 'KUH|DI:' is short enough.
55     *
56     * ref http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm#i1002022
57     */
58   
59    when al.med_alert_type_c = alm.med_alert_type_c
60    then 'KUH|DI:' ||
61         DBMS_CRYPTO.Hash(UTL_I18N.STRING_TO_RAW(al.alert_desc),
62                          /* typ 3 is HASH_SH1 */
63                          3)
64
65    else 'KUH|MED_ALERT:'||alm.med_alert_type_c
66    end as concept_cd,   
67    coalesce(ah.alt_close_date, pe.enc_close_date, pe.contact_date) as start_date,
68    '@' as modifier_cd,
69    al.alt_id as instance_num                                   
70  from clarity.alert al
71  join alert_history ah on ah.alt_id=al.alt_id and ah.pat_csn_id=al.pat_csn 
72  join clarity.pat_enc pe on al.pat_csn = pe.pat_enc_csn_id
73  left join clarity.zc_alt_type alr on al.general_alt_type_c = alr.alt_type_c 
74  left join clarity.zc_med_alert_type alm on al.med_alert_type_c = alm.med_alert_type_c)
75
76select
77    encounter_ide                             ,
78    patient_ide                               ,
79    concept_cd                                ,
80    start_date                                ,
81    modifier_cd                               ,
82    instance_num                              ,                 
83    start_date as end_date                                  ,
84    start_date as update_date                 ,
85    mod(ora_hash(patient_ide), &&heron_etl_chunks)+1 as part ,
86    '@' provider_id                           ,
87    '@' valtype_cd                            ,
88    '@' tval_char                             ,
89    to_number(null) nval_num                  ,
90    null valueflag_cd                         ,
91    null units_cd                             ,
92    null location_cd                          ,
93    to_number(null) confidence_num
94  from alert_recs;
95 
96 
97 
98
99                                       
100                                       
101                                       
102                                       
103                                       
104                                       
105                                       
106                                       
107                                       
108                                       
109                                       
110                                       
111                                       
112                                       
113                                       
114                                       
115                                       
116
Note: See TracBrowser for help on using the repository browser.