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/i2b2_dimensions_deid.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: 3.4 KB
Line 
1/* i2b2_dimensions_deid.sql -- de-identify data in i2b2 dimension tables
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7*/
8
9alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI';
10
11/* Check for id repository, uploader service tables, clarity link, views */
12select * from NightHeronData.observation_fact where 1 = 0;
13select * from NightHeronData.upload_status where 1 = 0;
14
15select patient_num from BlueHeronData.patient_dimension@deid where 1 = 0;
16
17/** patient_dimension
18 */
19
20/* Check that this upload id is in use. */
21select case
22  when exists (
23    select upload_id
24    from NightHeronData.upload_status
25    where upload_id = :upload_id
26  ) then 1
27  else 1/0
28  end test_result
29from dual;
30 
31insert into BlueHeronData.patient_dimension@deid (
32  patient_num, vital_status_cd
33, birth_date
34, death_date
35, update_date
36, sex_cd
37, age_in_years_num
38, language_cd
39, race_cd
40, marital_status_cd
41, religion_cd
42, import_date
43, download_date, sourcesystem_cd, upload_id )
44select
45  dim.patient_num, dim.vital_status_cd
46  , birth_date_hipaa + date_shift as birth_date
47  , death_date + date_shift as death_date
48  , update_date + date_shift as update_date
49  , sex_cd
50  , age_in_years_num_hipaa
51  , language_cd
52  , race_cd
53  , marital_status_cd
54  , religion_cd
55  , SYSDATE as import_date
56  , dim.download_date, dim.sourcesystem_cd, dim.upload_id
57from NightHeronData.patient_dimension dim
58where upload_id = :upload_id
59  and mod(dim.patient_num, &&heron_etl_chunks)+1 = :part;
60
61
62/* #records in = #records out? */
63select case mismatches when 0 then 1 else 1/0 end test_result from (
64select (
65select count(*)
66from NightHeronData.patient_dimension
67where upload_id = :upload_id
68) - (
69select count(*)
70from BlueHeronData.patient_dimension@deid
71where upload_id = :upload_id)
72as mismatches from dual
73);
74
75/** visit_dimension
76 */
77insert into BlueHeronData.visit_dimension@deid (
78    start_date, end_date
79  , encounter_num, patient_num, active_status_cd, inout_cd
80  , import_date
81  , download_date, sourcesystem_cd, upload_id )
82select
83    -- shift start, end dates
84    dim.start_date + pdim.date_shift as start_date
85  , dim.end_date + pdim.date_shift as end_date
86  , dim.encounter_num, dim.patient_num, dim.active_status_cd, dim.inout_cd
87  , SYSDATE as import_date
88  , dim.download_date, dim.sourcesystem_cd, dim.upload_id
89from NightHeronData.visit_dimension dim
90  join NightHeronData.patient_dimension pdim
91    on pdim.patient_num = dim.patient_num
92where dim.upload_id = :upload_id
93  and mod(dim.encounter_num, &&heron_etl_chunks)+1 = :part
94;
95
96
97/* #records in = #records out? */
98select case mismatches when 0 then 1 else 1/0 end test_result from (
99select (
100select count(*)
101from NightHeronData.visit_dimension
102where upload_id = :upload_id
103) - (
104select count(*)
105from BlueHeronData.visit_dimension@deid
106where upload_id = :upload_id)
107as mismatches from dual
108);
109
110
111/* todo: see #87 re providers
112        insert into blue_hictrdata.provider_dimension@idxdi_link
113          (provider_path ,
114           provider_id ,
115           import_date, download_date, sourcesystem_cd, upload_id )
116          (select
117           substr(provider_path, 1,
118                                           instr(provider_path, '\')) ,
119           provider_id ,
120           SYSDATE, dim.download_date, dim.sourcesystem_cd, dim.upload_id
121           from BrownHerondata.provider_dimension dim
122          )
123*/
Note: See TracBrowser for help on using the repository browser.