Page History
| Table of Contents |
|---|
| Children Display |
|---|
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.
...
- User logs into webclient and creates and runs a query.
- In the Run Query dialog box, select one or more Data Request checkboxes
- An email is automatically sent to Emails are automatically sent - one to the requester's e-mail, to inform them the request has been made; and one to the specified manager user to inform them to run the user's export request, and the previous . The query name (in previous queries) is tagged with the query master id, so it can be identified by the manager.
Data Export (Manager Process)
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.
Manager The manager user then re-runs the previous query and clicks on export. The In the Run Query dialog, the manager selects the requested export option is displayed selected in the Data export breakdown list.
Data export runs in the background and file is generated in a specified folder
- Manger user manually sends a confirmation email to the user on the file generation and retrieval info.
Info It is very important to put the original query master id (or some other identifier that links the export to the request) into the query name. The export letter will contain the query name, and this is the only way to trace the export back to the original request. (Currently there is no way to tie the export directory to the data requester query_master_id.) If the data manager runs the query as a query-in-query, the query name will automatically be populated with this identifier.
The data export runs in the background and the file is generated in a specified folder
- An e-mail is sent to the manager that can be forwarded to the requesting user, to inform them that their export is available and to provide retrieval info.
Sample Letter generated for Data ManagerResults of the i2b2 request entitled - "Demographics_12_48_52", submitted on Apr 18, 2024 1:47:58 PM, are available. Important notes about your data: 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: 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* These results are the data that was requested under the authority of the Institutional Review Board. The query resulting Sample LetterResults of the i2b2 request entitled - "Demographics_12_48_52", submitted on Apr 18, 2024 1:47:58 PM, are available. Important notes about your data: 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: 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* 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 |
|---|
...
Example Example Export files (all data are fake)
...
The i2b2 breakdown architecture has been modified to support new breakdown types for data request and export. These display in the run query dialog corresponding to the entries in HIVE_CELL_PARAMS has new parameters for global configuration, the QT_RESULT_TYPE table . When a manager user exports user request option, an export file is generated based on the entries in has new entries for query export types, and the 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 defines the request and export. These are XML documents containing e-mails to send and SQL to execute. These breakdown types display in the run query dialog. User e-mails are sent to the e-mail address of the currently logged-in use. Data Manager e-mails are sent to an address configured in HIVE_CELL_PARAMS, and the data manager e-mail in an export is also saved as a file in the export location. The export file generation location, also configured in HIVE_CELL_PARAMS, is on the local drive hosting the i2b2 application.
Database changes
Currently QT_BREAKDOWN_PATH .value column has datatype of varchar(2000). In order to support the XML structure needed for the Data Exporter, the value column has been modified to CLOB datatype.
...
| NAME | DESCRIPTION | DISPLAYTYPE_ID | VISUAL_ATTRIBUTE_TYPE_ID | USER_ROLE_CD | CLASSNAME |
| PATIENT_DEMOGRAPHIC_REQUEST | Request Demographics Data | CATNUM | LR | DATA_LDS | edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientRequest |
| PATIENT_DEMOGRAPHIC_CSV | Export Demographics Data | CATNUM | LX | MANAGER | edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientDownload |
Request/Export file definition details: QT_BREAKDOWN_PATH
QT_BREAKDOWN_PATH defines e-mail content (for request and export) and SQL code (for export) in the following XML format:
...
- <request name>_REQUEST has details on email notification generation
...
| Info | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
We recommend data requests be limited to DATA_LDS users (irrespective of Admin path role - admin, user, manager) and data exports be limited to MANAGER users (irrespective of data path role - data_lds, data_agg,data_obf, data_deid). The user role determines if exports/requests are visible and runnable.
|
Request/Export file definition details: QT_BREAKDOWN_PATH
QT_BREAKDOWN_PATH defines e-mail content (for request and export) and SQL code (for export) in the following XML format. Any parameters defined in an export XML override the default in HIVE_CELL_PARAMS (e.g., field separator character.)
| Name | Value |
|---|---|
| <?xml version="1.0"?> |
|
...
- <request name>_CSV has details on export file generation
...
| Info |
|---|
The data manager receives both the Requester Email and the Request Emails. Data Manager will then forward the Requester email to the data user that initiated the request. Email content and export SQL are configurable in the XML entries for the request/export definitions. |
Example Request types
| <?xml version="1.0"?> <ValueExporter> |
| <Version>[...version number...]</Version> <CreationDateTime><date value></CreationDateTime> <DataManagerEmailMessage>details sent to data Manager on data request</DataManagerEmailMessage> <File> <Filename>[... filename specification..]</Filename> <Query>[... SQL query to generate the data export content...]</Query> <SeparatorCharacter>[...file format seperator...]</SeparatorCharacter> </File> </ValueExporter> |
Example Requests
| NAME | VALUE |
| PATIENT_MEDICATION_CSV | <?xml version="1.0"?> <ValueExporter> <Version>3.02</Version> <CreationDateTime>08/09/2024 12:00:00</CreationDateTime> <RequesterEmailMessage>Your request on <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></RequesterEmailMessage> <LetterFilename>/<DataManagerEmailMessage>This user {{{USER_NAME}}} /in project {{{QUERY_MASTERPROJECT_ID}}} /Readme.txt</LetterFilename> <Letter> Results of the requested i2b2 request entitled entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, are available. Important notes about your data: - Total number of patients returned in your data request: with the query master of {{{PATIENTQUERY_COUNTID}}} - 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 . </DataManagerEmailMessage> <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><File> <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>File> </ValueExporter> |
| PATIENT_MEDICATION_REQUEST | <?xml version="1.0"?> <ValueExporter> <Version>3.02</Version> <CreationDateTime>08/09/2024 12:00:00</CreationDateTime> <RequesterEmailLetter>Your <RequesterEmailMessage>Your request on {{{PROJECT_ID}}} requested i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailLetter>RequesterEmailMessage> <RequestLetter>This <DataManagerEmailMessage>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>DataManagerEmailMessage> </ValueExporter> |
Export file format/location and Email parameters set-up: HIVE_CELL_PARAMS
Export File format, location and email server parameters are configurable in the HIVE_CELL_PARAMS.
| Info |
|---|
The <Filename> parameter in the QT_BREAKDOWN_PATH definition will override the default value specified in the HIVE_CELL_PARAMS. Wildfly must be restarted for changes to the HIVE_CELL_PARAMs to take effect. |
| Cell ID | Parameter Name | Example Value | Notes | ||||
| CRC | edu.harvard.i2b2.crc.exportcsv.datamanageremail | userid@partners.org | Email address used for sending request/export e-mails | ||||
| CRC | edu.harvard.i2b2.crc.smtp.host | smtp.partners.org | SMTP host | ||||
| CRC | edu.harvard.i2b2.crc.smtp.port | 25 | SMTP port | ||||
| CRC | edu.harvard.i2b2.crc.smtp.ssl.enableenabled | FALSE | TRUE will enable SSL | ||||
| CRC | edu.harvard.i2b2.crc.smtp.auth | FALSE | TRUE will enable SMTP authentication | ||||
| CRC | edu.harvard.i2b2.crc.smtp.username | none | SMTP username (required for SMTP authentication) | ||||
| CRC | edu.harvard.i2b2.crc.smtp.password | none | SMTP password (required for SMTP authentication) | ||||
| CRC | edu.harvard.i2b2.crc. | exportcsvsmtp. | defaultescapecharacterenabled | " | FALSE | TRUE will enable e-mailsEscape character for export files | |
| CRC | edu.harvard.i2b2.crc.smtp. | exportcsvfrom. | maxfetchrowsfullname | -1 | Data Manager | Name that e-mails will be sent from.Maximum number of rows to export, or -1 for no limit | |
| CRC | edu.harvard.i2b2.crc.smtp. | exportcsvfrom. | defaultlineend\n | Line ending for export files | CRCdatamanager@site.org | E-mail address that e-mails will be sent from. | |
| CC | edu.harvard.i2b2.crc. | exportcsvsmtp. | defaultseperatorsubject | i2b2 Data Request | Subject line for e-mails. | \t | Field separator for export files |
| CRC | edu.harvard.i2b2.crc.exportcsv.resultfetchsizedefaultescapecharacter | 50000 | " | Escape character for export files?????? | |||
| CRC | edu.harvard.i2b2.crc.exportcsv.filename | {{{PROJECT_ID}}}/{{{DATE_yyyyMMdd}}}_{{{FULL_NAME}}}.tsv | maxfetchrows | -1 | Maximum number of rows to export, or -1 for no limitParameterized template for export file names | ||
| CRC | edu.harvard.i2b2.crc.exportcsv.defaultquotechardefaultlineend | " | \n | Line ending Quote character for export files | |||
| CRC | edu.harvard.i2b2.crc.exportcsv.workfolder | /tmp/i2b2 | defaultseperator | \t | Field separator for export filesFolder on the i2b2 server for data exports | ||
| CRC | edu.harvard.i2b2.crc.exportcsv.zipencryptmethodresultfetchsize | none | ????????? |
These variables can be used in HIVE_CELL_PARAMS entries and in export query letter definitions:
| 50000 | Number of records retrieved during each database fetch. |
| CRC | edu.harvard.i2b2.crc.exportcsv.filename |
Value
| {{{ |
| PROJECT_ |
| ID}}} |
Current user running query
| /{{{ |
| DATE_ |
| yyyyMMdd}}} |
The project name
| _{{{ |
| FULL_ |
| NAME}}} |
| .tsv | Parameterized template for export file names. If the extension is .zip, the file is zipped. |
| CRC |
| edu.harvard.i2b2.crc.exportcsv.defaultquotechar | " | Quote character for export files | |
| CRC | edu.harvard.i2b2.crc.exportcsv.workfolder | /tmp/i2b2 | Folder on the i2b2 server for data exports |
| CRC | edu.harvard.i2b2.crc.exportcsv.zipencryptmethod | none | Encryption method for the exported ZIP file. One of STANDARD, NONE, or AES. |
Export query definition variables
These variables can be used in HIVE_CELL_PARAMS entries and in query export definitions:
Value | Description | ||
{{{USER | |||
{{{RANDOM_xxx}}} | A random integer where xxx is the max integer size | ||
{{{DATE_xxx}}} | Date/Time format, where xxx can be from the table below. The string yyyy-MM-dd would output dates that look like 2022-07-26 | ||
{{{QUERY_NAME}}} | Query NameCurrent user running query | ||
{{{QUERYPROJECT_STARTDATEID}}} | Query Start date/timeThe project name | ||
{{{QUERYRANDOM_ENDDATExxx}}} | Query End date/timeA random integer where xxx is the max integer size | ||
{{{PATIENTDATE_COUNTxxx}}} | Number of patients | {{{FULL_NAME}}} | Fullname of user who ran queryDate/Time format, where xxx can be from the table below. The string yyyy-MM-dd would output dates that look like 2022-07-26 |
{{{QUERY_MASTER_IDNAME}}}The query | Master IDQuery Name | ||
{{{QUERY_RUNTIMESTARTDATE}}} | Time to run the query |
...
Query Start date/time |
{{{ |
...
QUERY_ |
...
ENDDATE}}} |
...
Query End date/time | |
{{{PATIENT_COUNT}}} | Number of patients. |
{{{FULL_NAME}}} | Fullname of user who ran query |
{{{QUERY_MASTER_ID}}} | The query Master ID |
{{{QUERY_RUNTIME}}} | Time to run the query |
{{{FULL_SCHEMA }}} | Used in SQL export definitions. Inserts the full schema name (e.g., 'i2b2.dbo.'). Needed to be sure i2b2 accesses the correct data tables! |
Table of allowable characters in {{{DATE_xxx}}} strings:
| Value | Description | Value | Description |
| G | era | K | hour-of-am-pm (0-11) |
| u | year | k | clock-hour-of-am-pm (1-24) |
| yyyy | 4-digit year | H | hour-of-day (0-23) |
| D | day-of-year | m | minute-of-hour |
| Value | Description | Value | Description |
| G | era | K | hour-of-am-pm (0-11) |
| u | year | k | clock-hour-of-am-pm (1-24) |
| y | year-of-era | H | hour-of-day (0-23) |
| D | day-of-year | m | minute-of-hour |
| M/L | month-of-year | s | second-of-minute |
| d | day-of-month | S | fraction-of-second |
| Q/q | quarter-of-year | A | milli-of-day |
| Y | week-based-year | n | nano-of-second |
| w | week-of-week-based-year | N | nano-of-day |
| W | week-of-month | V | time-zone ID |
| E | day-of-week | z | time-zone name |
| e/c | localized day-of-week | O | localized zone-offset |
| F | week-of-month | X | zone-offset 'Z' for zero |
| a | am-pm-of-day | x | zone-offset |
| h | clock-hour-of-am-pm (1-12) | Z | zone-offset |
| p | pad next |
| Info |
|---|
Currently, the export file generation location must be accessible to the application Server hosting the i2b2 application |
Software Changes:
- Data: New entries in HIVE_CELL_PARAMS, QT_QUERY_RESULT_TYPE, and QT_BREAKDOWN_PATH define the exporter configuration.
- Java code: New breakdown classes and updates to existing java classes to support the data exporter functionality.
Limitations in current functionality
The current data exporter functionality has the following design limitations
Database Configuration
- QT_BREAKDOWN_PATH table configuration for Request/Export file XML definition support is limited to SQL statements only.
- HIVE_CELL_PARAMS /QT_BREAKDOWN_PATH defaultseperator value parameter overrides the file type parameter set in exportcsv.filename value column parameter
Example default separator value : '|'
file type value: demograhics.csv
export file generated: demographics.csv with '|' separator
Export File generation
- Currently, the export file generation location is on the local drive hosting the i2b2 application
- Data export folder is generated with a new Query_master_id generated when the data Manager reruns the data request query. Therefore, currently there is no way to tie the export directory to the data requester query_master_id. Two Options that can be used as a workaround to identify the data requester query
- Use the data requester query_master_id in the query name when rerunning the data requester query. This will generate the original data requester id as part of the data export queryname and also will be displayed in the generated data requester letter
- Run the data requester query as a query- in-query to obtain the same result as option 1.
Software Changes:
- Data: New entries in HIVE_CELL_PARAMS, QT_QUERY_RESULT_TYPE, and QT_BREAKDOWN_PATH define the exporter configuration.
- Java code: New breakdown classes and updates to existing java classes to support the data exporter functionality
- Cata requester email is configured to be generated a README file in the data export directory. The data Manager will be required to manually copy the file content into an email and send it to the data request User
- The default export file location and format should be set in the HIVE_CELL_PARAMS without the QT_BREAKDOWN_PATH updates , but requires a restart of the wildfly for the changes to take effect.
