Page History
...
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)
...
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 (ORACLE) and varchar(max) for (SQLServer).
Configuring Database Parameters
...
| 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"?> |
| <?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 {{{PROJECT_ID}}} requested i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_ID}}}</RequesterEmailMessage> <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_ID}}}. </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> <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> </File> </ValueExporter> |
| PATIENT_MEDICATION_REQUEST | <?xml version="1.0"?> <ValueExporter> <Version>3.02</Version> <CreationDateTime>08/09/2024 12:00:00</CreationDateTime> <RequesterEmailMessage>Your request on {{{PROJECT_ID}}} requested i2b2 request entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_MASTER_ID}}}</RequesterEmailMessage> <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}}}. </DataManagerEmailMessage> </ValueExporter> |
...
These variables can be used in HIVE_CELL_PARAMS entries and in query export definitions:
Value | Description |
{{{USER_NAME}}} | Current user running query |
{{{PROJECT_ID}}} | The project name |
{{{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 Name |
{{{QUERY_STARTDATE}}} | 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 |
| 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 |
Request and Export Results:
Metadata about data request and export queries are stored in the QT_XML_RESULT table, in the XML_VALUE field:
| Query Type | XML_VALUE example |
|---|---|
| Data Request | <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns10:i2b2_result_envelope xmlns:ns6="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns8="http://www.i2b2.org/xsd/cell/pm/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinition/1.1/" xmlns:ns9="http://www.i2b2.org/xsd/cell/ont/1.1/" xmlns:ns10="http://www.i2b2.org/xsd/hive/msg/result/1.1/" xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/"> <body> <ns10:result name="PATIENT_DEMOGRAPHIC_REQUEST"> <data column="patientCount" type="int">1</data> <data column="RequestEmail" type="string">This user act in project NODE9 requested i2b2 request entitled - "ACT_Vaccination_14_53_14", submitted on May 22, 2024 2:53:13 PM, with the query master of 1492. </data> <data column="DataManagerEmail" type="string">datamanager@site.org</data> </ns10:result> </body> </ns10:i2b2_result_envelope> |
| Data Export | <?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
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.
...
