Overview

Data Exporter functionality enables an i2b2 user to request patient data for a desired patient set query. The request is then processed by Manager role use. The patient data is exported as a file and stored in a specified location for retrieval.

The patient dataset, request options and file specifications are configured in the database using specific request type entries and XML files with embedded SQL select statements and other configuration parameters

Design and Architecture

The existing i2b2 breakdown architecture is modified to display additional breakdowns for the show up in the run query dialog corresponding to the entries in the QT_RESULT_TYPE  table

The QT_BREAKDOWN_PATH table has XML file entries with embedded SQL statements for each Request type. and file content generation 

Export File generation parameters are configurable in the HIVE_CELL_PARAMS

Java Changes include a new breakdown class for the file export process.

Flow chart

Request Scenario

• End user logs into webclient and creates a patientset query
• Selects a Data Request option and
• Click run Query
• Email is sent to manager user to run the user request
• The manager logs into the webclient and finds the run query by matching the query master id
• Manager user then re-runs the previous query and clicks on export.  The export option is displayed selected in the Data export breakdown.
• Data export runs in the background and file is generated in the folder specified in the HIVE_CELL_PARAM
• Manger user manually sends a confirmation email to the user on the file generation and retrieval info.

Display for End User

Display for Manager user

Updating Database Configuration

Entry into HIVE_CELL_PARAMS

File parameter specification entries

Variables for File name entries 

Entry into QT_BREAKDOWN_PATH

Entry into QT_RESULT_TTPE

Example SQL statement entries

Demographics

SELECT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,a.BIRTH_DATE as "BIRTH_DATE"

, a.DEATH_DATE as "DEATH_DATE"

,a.SEX_CD as "GENDER"

, a.AGE_IN_YEARS_NUM as "AGE_IN_YEARS"

,a.LANGUAGE_CD as "PRIMARY_SPOKEN_LANGUAGE"

,a.RACE_CD as "RACE"

,a.MARITAL_STATUS_CD as "MARTIAL_STATUS"

,a.RELIGION_CD as "RELIGION"

,a.ZIP_CD as "ZIP_CODE"

,a.STATECITYZIP_PATH as "STATE_CITY_ZIP"

,a.INCOME_CD as "INCOME"

,a.VITAL_STATUS_CD as "VITAL_STATUS"

FROM patient_dimension a, {{{DX}}} c

where a.patient_num = c.patient_num

Diagnosis

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "DIAGNOSIS_NAME"

,b.concept_cd as "DIAGNOSIS_CODE"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Diagnoses\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Labs

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "LAB_NAME"

,b.concept_cd as "LAB_CODE"

,case when a.valtype_cd = ''T'' then tval_char else to_char(nval_num) end as "LAB_RESULTS"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Labtests\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Procedures

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "PROCEDURE_NAME"

,b.concept_cd as "PROCEDURE_CODE"

,a.quantity_num as "QUANTITY"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Procedures\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Medications

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "MEDICATION_NAME"

,b.concept_cd as "NDC_CODE"

,a.units_cd as "UNIT"

,a.quantity_num as "DOSE_QUANTITY"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Medications\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Patient_Mapping

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,a.PATIENT_IDE_SOURCE as "PATIENT_SOURCE"

,a.PATIENT_IDE as "PATIENT_IDE"

FROM patient_mapping a, {{{DX}}} c

where a.patient_num = c.patient_num</Query>

Sample Letter generated for Data Manager