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.
...
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.
The manager user then re-runs the previous query. In the Run Query dialog, the manager selects the requested export option in the Data export breakdown list.
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 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 Export files (all data are fake)
...
The i2b2 breakdown architecture has been modified to support new breakdown types for data request and export. HIVE_CELL_PARAMS has new parameters for global configuration, he the QT_RESULT_TYPE table has new entries for query export types, and the QT_BREAKDOWN_PATH 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. When a user requests a breakdown, an e-mail is sent to a data manager. When the data manager user exports the requested data, an export file is generated and email notification sent to the manager is also based on the entries in the QT_BREAKDOWN_PATH tableUser 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. The data requester email in the export definition is not directly sent to the user - it is generated as 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 .
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 |
| Info | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
We recommended 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.
|
...
| Name | Value |
|---|---|
| <?xml version="1.0"?> |
| <?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> |
...
| 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.enabled | 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.smtp.enabled | FALSE | TRUE will enable e-mails |
| CRC | edu.harvard.i2b2.crc.smtp.from.fullname | Data Manager | Name that e-mails will be sent from. |
| CRC | edu.harvard.i2b2.crc.smtp.from.email | datamanager@site.org | E-mail address that e-mails will be sent from. |
| CC | edu.harvard.i2b2.crc.smtp.subject | i2b2 Data Request | Subject line for e-mails. |
| CRC | edu.harvard.i2b2.crc.exportcsv.defaultescapecharacter | " | Escape character for export files |
| CRC | edu.harvard.i2b2.crc.exportcsv.maxfetchrows | -1 | Maximum number of rows to export, or -1 for no limit |
| CRC | edu.harvard.i2b2.crc.exportcsv.defaultlineend | \n | Line ending for export files |
| CRC | edu.harvard.i2b2.crc.exportcsv.defaultseperator | \t | Field separator for export files |
| CRC | edu.harvard.i2b2.crc.exportcsv.resultfetchsize | 50000 | ??????Number of records retrieved during each database fetch. |
| CRC | edu.harvard.i2b2.crc.exportcsv.filename | {{{PROJECT_ID}}}/{{{DATE_yyyyMMdd}}}_{{{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
...
