Developers Getting Started With i2b2
Space shortcuts
Space Tools
Developers Getting Started With i2b2 getstarted

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 40 Next »

Overview

The Data Exporter functionality enables an i2b2 user to request patient data for the patients found by a desired query. In the configured workflow, data is requested and generated through new breakdown types that can be selected in the Run Query dialog. Users will see "data request" breakdowns, which send an e-mail to a predefined data manager requesting a type of data. The data manager (Manager user role) processes the request by rerunning the query with a special "data export" breakdown. The patient data is exported as a file and stored in a specified location for retrieval.

The patient data export types, request options, and file specifications are configured in the database using specific request type entries and configuration parameters in the database.


Data Export Workflow

User Process

User logs into webclient and...

  1. Creates a query

  2. Clicks Run query and ticks boxes for a data request

An email is automatically sent to specified manager user to run the user's export request, and the previous query name is tagged with the query master id.


Run a query with a data request

Manager Process

  1. The manager logs into the webclient and finds the query from their e-mail by locating the query master id, which is now part of the title of the previous query.

  2. Manager user then re-runs the previous query and clicks on export.  The export option is displayed selected in the Data export breakdown list.

  3. Data export runs in the background and file is generated in the folder specified in the HIVE_CELL_PARAM

  4. Manger user manually sends a confirmation email to the user on the file generation and retrieval info.


Data Export options in the Run Query Dialog



Sample Letter generated for Data Manager




Data Export Configuration

Design and Architecture

The existing i2b2 breakdown architecture is modified to display breakdowns for the request/Export Options display in the run query dialog corresponding to the entries in the QT_RESULT_TYPE table. When a manager user exports user request option, an export file is generated based on the entries in QT_BREAKDOWN_PATH table. Email notification sent to the manager is also based on the entries in the QT_BREAKDOWN_PATH table. The generated file is formatted as per the entries in HIVE_CELL_PARAMS. 

Configuring Database Parameters

Request/Export parameters set-up

User request and Manager Export parameters are configured in QT_REULT_TYPE table. VISUAL_ATTRIBUTE_TYPE_ID should be set to LR for Data request and LX for Data export

USER_ROLE_CD should be set to DATA_LDS for Data Request and USER_ROLE_CD should be MANAGER for Data Export display.


NAMEDESCRIPTIONDISPLAYTYPE_IDVISUAL_ATTRIBUTE_TYPE_IDUSER_ROLE_CDCLASSNAME
PATIENT_DEMOGRAPHIC_REQUESTRequest Demographics DataCATNUMLRDATA_LDSedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
PATIENT_DEMOGRAPHIC_CSVExport Demographics DataCATNUMLXMANAGERedu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload



Export file generation parameters set-up

QT_BREAKDOWN_PATH Name column hosts 2 type of parameters for each request type: The value column has XML file with email and file specifications

NameValue
  • <request name>_REQUEST has details on email notification generation
<?xml version="1.0"?>
<ValueExporter>
  <Version><version no></Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>< details sent to user on data request</RequesterEmailLetter>
  <RequestLetter><details sent to Manager on user data request>  </RequestLetter>
</ValueExporter>
  • <request name>_CSV has details on export file generation   
<?xml version="1.0"?>
<ValueExporter>
  <Version><version no></Version>
  <CreationDateTime><date value></CreationDateTime>
  <RequesterEmailLetter>details sent to user on data request</RequesterEmailLetter>
  <RequestLetter>details sent to Manager on user data request  </RequestLetter>
  <LetterFilename><name of the file></LetterFilename>
  <Letter> <letter content></Letter>
  <Table>
<Filename>filename</Filename>
<Query><SQL query to generate the data export content></Query>
      <SeparatorCharacter><file format seperator></SeparatorCharacter>
  </Table>

</ValueExporter>


Example Request types

NAMEVALUE
PATIENT_MEDICATION_CSV<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
  <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}.
  </RequestLetter>
  <LetterFilename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Readme.txt</LetterFilename>
  <Letter>
Results of the i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available.

Important notes about your data:
- Total number of patients returned in your data request: {{{PATIENT_COUNT}}}
- i2b2 reviewer:

Only persons specifically authorized and selected (as listed at the top of this letter) can download these files. If additional user access is needed, please ensure the person is listed on your project IRB protocol and contact the i2b2 team.

Specifically:
- Remove all PHI from computer, laptop, or mobile device after analysis is completed.
- Do NOT share PHI or PII with anyone who is not listed on the IRB protocol.

Your guideline for the storage of Protected Health Information can be found at: https://www.site.com/guidelines_for_protecting_and_storing_phi.pdf

*To download these files*
- You must be logged onto your site

These results are the data that was requested under the authority of the Institutional Review Board.  The query resulting in this identified patient data is included at the end of this letter.  A copy of this letter is kept on file and is available to the IRB in the event of an audit.

Thank you,

The i2b2 Team
</Letter>
  <Table>
<Filename>/{{{USER_NAME}}}/{{{QUERY_MASTER_ID}}}/Medication.csv</Filename>
<Query>SELECT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"
        ,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"
        ,m.name_char as "MODIFIER_NAME"
        ,a.instance_num as "INSTANCE_NUM"
        ,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
    LEFT OUTER JOIN modifier_dimension m on a.modifier_cd = m.modifier_cd</Query>
      <SeparatorCharacter>\t</SeparatorCharacter>
  </Table>

</ValueExporter>
PATIENT_MEDICATION_REQUEST<?xml version="1.0"?>
<ValueExporter>
  <Version>3.02</Version>
 <CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
  <RequesterEmailLetter>Your request on {{{PROJECT_MASTER_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>
  <RequestLetter>This user {{{USER_NAME}}} in project {{{PROJECT_ID}}} requested i2b2 request
 entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}.
  </RequestLetter>
</ValueExporter>



Export file format parameters set-up

Export File format, location and email server parameters are configurable in the HIVE_CELL_PARAMS


CRCedu.harvard.i2b2.crc.exportcsv.datamanageremailuserid@partners.org
CRCedu.harvard.i2b2.crc.smtp.hostsmtp.partners.org
CRCedu.harvard.i2b2.crc.smtp.port25
CRCedu.harvard.i2b2.crc.smtp.ssl.enableFALSE
CRCedu.harvard.i2b2.crc.smtp.authFALSE
CRCedu.harvard.i2b2.crc.smtp.usernamenone
CRCedu.harvard.i2b2.crc.smtp.passwordnone
CRCedu.harvard.i2b2.crc.exportcsv.defaultescapecharacter"
CRCedu.harvard.i2b2.crc.exportcsv.maxfetchrows-1
CRCedu.harvard.i2b2.crc.exportcsv.defaultlineend\n
CRCedu.harvard.i2b2.crc.exportcsv.defaultseperator\t
CRCedu.harvard.i2b2.crc.exportcsv.resultfetchsize50000
CRCedu.harvard.i2b2.crc.exportcsv.filename/tmp/{{{PROJECT_ID}}}/{{{DATE_yyyyMMdd}}}_{{{FULL_NAME}}}.tsv
CRCedu.harvard.i2b2.crc.exportcsv.defaultquotechar"
CRCedu.harvard.i2b2.crc.exportcsv.workfolder/tmp/i2b2
CRCedu.harvard.i2b2.crc.exportcsv.zipencryptmethodnone


Variables for File name entries 



Java Changes

Two new breakdown classes are designed to perform the file export process

  • edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest
  • edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload












Example Export files

Demographics


Diagnosis


Labs


Procedures


Medications


Patient_Mapping



















  • No labels

Developers Getting Started With i2b2 getstarted