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_facts_load.sql

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

Merge with demo_concepts_3800

File size: 2.7 KB
Line 
1/* epic_facts_load.sql: Load observations from Epic Clarity.
2
3Copyright (c) 2012-2014 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
7see also epic_dimensions_load.sql,
8  http://informatics.kumc.edu/work/wiki/HeronLoad
9*/
10
11/* Check for id repository, uploader service tables */
12select * from NightHeronData.observation_fact where 1 = 0;
13select * from NightHeronData.upload_status where 1 = 0;
14
15/* We're wasting our time if an upload_status record isn't in place. */
16select case count(*) when 1 then 1 else 1/0 end as upload_status_exists from (
17select * from NightHeronData.upload_status up
18where up.upload_id = :upload_id
19);
20
21
22truncate table observation_fact_upload;
23whenever sqlerror continue;
24alter table observation_fact_upload
25  disable constraint observation_fact_pk;
26whenever sqlerror exit;
27
28insert into observation_fact_upload(
29  patient_num, encounter_num, sub_encounter,
30  concept_cd,
31  provider_id,
32  start_date,
33  modifier_cd,
34  instance_num,
35  valtype_cd,
36  tval_char,
37  nval_num,
38  valueflag_cd,
39  units_cd,
40  end_date,
41  location_cd,
42  update_date,
43  import_date, upload_id, download_date, sourcesystem_cd)
44select pmap.patient_num, emap.encounter_num, f.encounter_ide,
45  f.concept_cd,
46  f.provider_id,
47  f.start_date,
48  f.modifier_cd,
49  f.instance_num,
50  f.valtype_cd,
51  f.tval_char,
52  f.nval_num,
53  f.valueflag_cd,
54  f.units_cd,
55  f.end_date,
56  f.location_cd,
57  f.update_date,
58  sysdate, up.upload_id, :download_date, up.source_cd
59from &&epic_fact_view f
60   , NightHeronData.patient_mapping pmap
61   , NightHeronData.encounter_mapping emap
62   , NightHeronData.upload_status up
63where pmap.patient_ide = f.patient_ide
64  and emap.encounter_ide = f.encounter_ide
65  and pmap.patient_ide_source = :pat_source_cd
66  and emap.encounter_ide_source = :enc_source_cd
67  and pmap.patient_ide_status = 'A'
68  and emap.encounter_ide_status = 'A'
69  and up.upload_id = :upload_id
70  and f.part = :part
71  &&log_fact_exceptions
72;
73
74
75/* For this upload of data, check primary key constraints.
76This could perhaps be factored out of all the load scripts into i2b2_facts_deid.sql,
77at the cost of slowing down the select count(*) for summary stats, below.
78 */
79alter table observation_fact_upload
80  enable constraint observation_fact_pk
81  /* TODO: log errors ... ? #2117 */
82  ;
83
84
85/** Summary stats.
86
87Report how many rows are dropped when joining on patient_ide and encounter_id.
88Subsumes check for null :part (#789).
89*/
90update NightHeronData.upload_status
91  set loaded_record = (select count(*) from observation_fact_upload)
92    , no_of_record = (select count(*) from &&epic_fact_view)
93  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.