[DATA-2] Collisions in concept_dimension load between demo project and act

[jklann]

After loading the crc cell with the 'demo' project data, I attempted to load the 'act' project data to update the concept dimension mappings. However, there are collisions between the two concept dimension insert files which prevent the inserts from the act load from completing.

Alternatively, I attempted to load just the act data to the crc cell, but found that the insert scripts for act do not contain any data for the observation_fact cell.

---

Buildfile: /docker-entrypoint.d/workspace/NewInstall/Crcdata/data_build.xml

init:

db_demodata_load_data:
[echo] Loading demodata -- may take a while; over 10 min
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/visit_dimension_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_demographics_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_birn_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_diagnoses_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_medications_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_affy_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/provider_dimension_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/concept_dimension_demo_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/patient_mapping_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/dim_code_lookup_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/patient_dimension_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_labtests_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/concept_dimension_ICD10_ICD9_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/observation_fact_demo_reports_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/query_demo_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/encounter_mapping_insert_data.sql
[sql] Executing resource: /docker-entrypoint.d/workspace/NewInstall/Crcdata/scripts/postgresql/modifier_dimension_insert_data.sql
[sql] 8 of 8 SQL statements executed successfully
[sql] Failed to execute: INSERT INTO CONCEPT_DIMENSION(CONCEPT_PATH, CONCEPT_CD, NAME_CHAR, CONCEPT_BLOB, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID) VALUES('\Diagnoses(A00-B99) Certugmm', 'ICD10:A00-B99', 'Certain infectious and parasitic diseases (a00-b99)', NULL, NULL, NULL, NULL, NULL, NULL)
[sql] org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "concept_dimension_pk"
[sql] Detail: Key (concept_path)=(\Diagnoses(A00-B99) Certugmm) already exists.
[sql] Failed to execute: INSERT INTO CONCEPT_DIMENSION(CONCEPT_PATH, CONCEPT_CD, NAME_CHAR, CONCEPT_BLOB, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID) VALUES('\Diagnoses(A00-B99) Certugmm(A00-A09) Inte3luo', 'ICD10:A00-A09', 'Intestinal infectious diseases (a00-a09)', NULL, NULL, NULL, NULL, NULL, NULL)
[sql] org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
.
.
.
[sql] 0 of 116979 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 34 seconds

---

db.type=postgresql
db.username=i2b2data
db.password=demodata
db.server=postgres::i2b2
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://postgres/i2b2?searchpath=i2b2data
db.project=act

---

Imported from Jira

• Key: DATA-2
• ID: 10781
• Type: Bug
• Priority: Minor
• Status: To Do
• Reporter: Former user
• Created: 16/Dec/19 2:40 PM
• Updated: 16/Dec/19 2:40 PM
• Project: DATA—i2b2 Data
• Environment: c718238

[markabajian]

It would be helpful if the demo-example "asthma patient" data were available for loading into a CRC database with the ACT ontology, and likewise with the ACT-OMOP ontology. Just a suggestion.