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_demo_concepts.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: 9.5 KB
Line 
1/* epic_demographic_concepts.sql -- load i2b2 demographic concept hierarchy
2   from the epic source data
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*/
8
9/* epic_audit_info looks up the source_cd for this ETL job. */
10create or replace view epic_audit_info as
11select * from BlueHeronData.source_master
12where source_cd like 'Epic@%';
13
14-- TODO move to a more generic (i2b2) constanst file
15create or replace view i2b2_concept_constants as
16select 'LA' as leaf_active_va,
17       'LH' as leaf_hidden_va,
18       'FA' as folder_active_va,
19       'FH' as folder_hidden_va
20from dual;
21
22create or replace view demo_concept_constants as
23select '\i2b2\Demographics\' as base_concept_path
24from dual;
25
26/******************
27 * Demographics: religion/language/race/ethnicity/state
28          (also) Email exists
29 */
30
31truncate table BLUEHERONMETADATA.epic_demo_terms;
32
33insert into BLUEHERONMETADATA.epic_demo_terms (
34  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
35  c_visualattributes,
36  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
37  c_operator,  m_applied_path,
38  update_date, import_date, sourcesystem_cd
39)
40with schemes_and_constants as
41(select *
42 from demo_concept_constants
43 cross join i2b2_concept_constants
44 cross join scheme_labels),
45
46demo_religion as
47(select -- '\i2b2\Demographics\Religion\' exists as a part of stock i2b2
48        -- this is an implicit invariant
49        sac.base_concept_path || 'Religion\'||r.name||'\' as concept_path
50      , sac.demo_religion_scheme|| lower(r.name) as concept_cd
51      , r.name as name_char
52      , sac.leaf_active_va as c_visualattributes
53  from CLARITY.zc_religion@id r
54  cross join schemes_and_constants sac)
55  -- select * from demo_religion;
56
57, demo_email_base as
58 (select sac.base_concept_path || 'EmailExists\'  as concept_path
59       , null as concept_cd
60       , 'Email on file' as name_char
61       , sac.folder_active_va as c_visualattributes
62 from schemes_and_constants sac)
63 -- select * from demo_email_base;
64
65, demo_email_epic as
66(select deb.concept_path || 'EpicEmail\'  as concept_path
67       , sac.demo_epic_email_scheme as concept_cd
68       , 'Email address on file in Epic' as name_char
69       , sac.leaf_active_va as c_visualattributes
70  from demo_email_base deb
71  cross join schemes_and_constants sac)
72
73-- boo, IDX concept in an epic *only* concept load
74-- TODO think of a better place to put this that still makes sense.
75, demo_email_idx as
76(select deb.concept_path || 'IdxEmail\'  as concept_path
77       , sac.demo_idx_email_scheme as concept_cd
78       , 'Email address on file in IDX' as name_char
79       , sac.leaf_active_va as c_visualattributes
80  from demo_email_base deb
81  cross join schemes_and_constants sac)
82
83, demo_language as
84(select -- '\i2b2\Demographics\Language\' exists as a part of stock i2b2
85        -- this is an implicit invariant
86        sac.base_concept_path ||'Language\'||l.name||'\' as concept_path
87      , sac.demo_language_scheme||lower(l.name) as concept_cd
88      , l.name as name_char
89      , sac.leaf_active_va as c_visualattributes
90  from clarity.zc_language@id l
91  cross join schemes_and_constants sac)
92
93, demo_race as
94(select -- '\i2b2\Demographics\Race\' exists as a part of stock i2b2
95        -- this is an implicit invariant
96        sac.base_concept_path ||'Race\'||czpr.name||'\' as concept_path
97      , sac.demo_race_scheme ||
98        case when czpr.patient_race_c is NULL
99             then '@'
100             else lower(czpr.abbr)
101         end as concept_cd
102       , czpr.name as name_char
103       , sac.leaf_active_va as c_visualattributes
104  from CLARITY.zc_patient_race@id czpr
105  cross join schemes_and_constants sac)
106
107, demo_ethnicity_base as
108(select sac.base_concept_path|| 'Ethnicity\' as concept_path
109      , null as concept_cd
110      , 'Ethnicity' as name_char
111      , sac.folder_active_va as c_visualattributes
112 from schemes_and_constants sac)
113
114, demo_ethnicity_no_rec as
115(select deb.concept_path || 'Not Recorded\' as concept_path
116      , sac.demo_ethnicity_scheme || '@' as concept_cd
117      , 'Not Recorded' as name_char
118      , 'MA' as c_visualattributes -- Huh? what is the M for?
119 from demo_ethnicity_base deb
120 cross join schemes_and_constants sac)
121
122, demo_ethnicity_full as
123(select deb.concept_path || eg.name || '\' as concept_path
124      , sac.demo_ethnicity_scheme || lower(eg.abbr) as concept_cd
125      , eg.name as name_char
126      , sac.leaf_active_va as c_visualattributes
127 -- TODO remove DEID->ID link
128 from clarity.zc_ethnic_group@id eg
129 cross join demo_ethnicity_base deb
130 cross join schemes_and_constants sac)
131
132, demo_ethnicity_other as
133(select denr.concept_path || 'Other-o\' as concept_path
134      , sac.demo_ethnicity_scheme || 'o' as concept_cd
135      , 'Other' as name_char
136      , sac.leaf_hidden_va as c_visualattributes
137 from demo_ethnicity_no_rec denr
138 cross join schemes_and_constants sac)
139
140, demo_state_base as
141(select sac.base_concept_path|| 'State\' as concept_path
142      , null as concept_cd
143      , 'Place: State' as name_char
144      , sac.folder_active_va as c_visualattributes
145 from schemes_and_constants sac)
146
147, demo_state_full as
148(select sac.base_concept_path|| 'State\' || s.abbr  || '\' as concept_path
149     , sac.demo_state_scheme || s.abbr as concept_cd
150     , s.name || ' (' || s.abbr || ')' as name_char
151     , sac.leaf_active_va as c_visualattributes
152 -- TODO remove DEID->ID link
153 from clarity.zc_state@id s
154 cross join schemes_and_constants sac)
155 
156, demo_mychart_base as
157(select sac.base_concept_path || 'MyChart\' as concept_path
158      , null as concept_cd
159      , 'My Chart' as name_char
160      , sac.folder_active_va as c_visualattributes
161 from schemes_and_constants sac)
162
163, demo_mychart_status_base as
164(select mcb.concept_path || 'MyChart Status\' as concept_path
165      , null as concept_cd
166      , 'My Chart Status' as name_char
167      , sac.folder_active_va as c_visualattributes
168 from demo_mychart_base mcb
169 cross join schemes_and_constants sac)
170
171/*
172, demo_mychart_active_account as
173(select dmb.concept_path || mcs.abbr || '\' as concept_path
174      , sac.myc_status_scheme || mcs.mychart_status_c as concept_cd
175      , 'Active MyChart Account' as name_char
176      , sac.leaf_active_va as c_visualattributes
177 from clarity.zc_mychart_status mcs
178 cross join demo_mychart_base dmb
179 cross join schemes_and_constants sac
180 where mcs.name = 'Activated')
181*/
182
183, demo_mychart_status as
184(select dmsb.concept_path || mcs.abbr || '\' as concept_path
185      , sac.myc_status_scheme || mcs.mychart_status_c as concept_cd
186      , mcs.name as name_char
187      , sac.leaf_active_va as c_visualattributes
188 from clarity.zc_mychart_status@id mcs
189 cross join demo_mychart_status_base dmsb
190 cross join schemes_and_constants sac)
191 
192, demo_mychart_email_base as
193(select mcb.concept_path || 'MyChart Recieve Email\' as concept_path
194      , null as concept_cd
195      , 'Chose to recieve E-mail' as name_char
196      , sac.folder_active_va as c_visualattributes
197 from demo_mychart_base mcb
198 cross join schemes_and_constants sac)
199
200, email_categories as
201  /*
202   * There is, unfortunately no correlated ZC table for `PATIENT_MYC.RECV_EMAIL_YN`
203   * So, from the Clarity Data Dictionary - Epic 2014 Documentation
204   * Epic Released Entries:
205   *   1 - Yes
206   *   2 - No
207   */
208( select 'Y' as code, 'Yes' as name from dual union all
209  select 'N' as code, 'No' as name from dual)
210
211, demo_mychart_email as
212(select dmeb.concept_path || ec.name || '\' as concept_path
213      , sac.myc_recv_email_scheme || ec.code as concept_cd
214      , ec.name as name_char
215      , sac.leaf_active_va as c_visualattributes
216 from email_categories ec
217 cross join demo_mychart_email_base dmeb
218 cross join schemes_and_constants sac)
219
220, demo_mychart_access_base as
221(select mcb.concept_path || 'Accessed MyChart\' as concept_path
222      , null as concept_cd
223      , 'Accessed MyChart' as name_char
224      , sac.folder_active_va as c_visualattributes
225 from demo_mychart_base mcb
226 cross join schemes_and_constants sac)
227 
228, demo_mychart_access as
229(select dmab.concept_path || pas.abbr || '\' as concept_path
230      , sac.myc_accessed_scheme || pas.pat_access_stat_c as concept_cd
231      , pas.name as name_char
232      , sac.leaf_active_va as c_visualattributes
233 from clarity.zc_pat_access_stat@id pas
234 cross join demo_mychart_access_base dmab
235 cross join schemes_and_constants sac)
236
237select
238  length(con.concept_path) - length(replace(con.concept_path, '\', '')) - 2 as c_hlevel,
239  con.concept_path as c_fullname,
240  con.name_char as c_name,
241  con.concept_cd as c_basecode,
242  con.concept_path as c_dimcode,
243  c_visualattributes as c_visualattributes,
244  norm.*,
245  sysdate as update_date,
246  sysdate as import_date,
247  aud.source_cd as sourcesystem_cd
248from (
249  select * from demo_religion union all
250  select * from demo_email_base union all
251  select * from demo_email_epic union all
252  select * from demo_email_idx union all
253  select * from demo_language union all
254  select * from demo_race union all
255  select * from demo_ethnicity_base union all
256  select * from demo_ethnicity_no_rec union all
257  select * from demo_ethnicity_full union all
258  select * from demo_ethnicity_other union all
259  select * from demo_state_base union all
260  select * from demo_state_full union all
261  select * from demo_mychart_base union all
262  select * from demo_mychart_status_base union all
263  select * from demo_mychart_status union all
264  select * from demo_mychart_email_base union all
265  select * from demo_mychart_email union all
266  select * from demo_mychart_access_base union all
267  select * from demo_mychart_access
268  ) con
269, BlueHeronMetadata.normal_concept norm, epic_audit_info aud
270order by con.concept_path;
Note: See TracBrowser for help on using the repository browser.