Page History

...

Design and Architecture

The existing i2b2 breakdown architecture is has been modified to display breakdowns for the request/Export Options support new breakdown types for data request and export. These 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

...

query types: QT_QUERY_RESULT_TYPE

The data request and data export query types are configured as breakdowns in the QT_QUERY_RESULTUser 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.

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 Name column hosts 2 type of parameters for each request type: The value column has XML file with email and file specifications defines e-mail content (for request and export) and SQL code (for export) in the following XML format:

Name

Value

<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>
<RequestLetter><details <RequestLetter>[...details sent to Manager on user data request> 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>
<LetterFilename><name of the file></LetterFilename>
<Letter> <letter content></Letter>
<Table>
<Filename>filename</Filename>
<Query><SQL <Query>[... SQL query to generate the data export content><content...]</Query>
<SeparatorCharacter><file format seperator><<SeparatorCharacter>[...file format seperator...]</SeparatorCharacter>
</Table>

</ValueExporter>

Info

Currently, The data Manager 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 is and export SQL are configurable in the XML entries for the request type/export definitions.

Example Request types

NAME	VALUE
PATIENT_MEDICATION_CSV	<?xml version="1.0"?> <ValueExporter>

Â

Â

<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

<CreationDateTime>08/09/2024 12:00:00</CreationDateTime>
<RequesterEmailLetter>Your request on {{{PROJECT_

MASTER_

ID}}} requested i2b2 request

entitled

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

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

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.enable	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.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	??????
CRC	edu.harvard.i2b2.crc.exportcsv.filename	/tmp/{{{PROJECT_ID}}}/{{{DATE_yyyyMMdd}}}_{{{FULL_NAME}}}.tsv	Parameterized template for export file names
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	?????????

Variables for File name entries

...

Valeb
Description
{{{USER_NAME}}}
Current user running query
{{{PROJECT_ID}}}
The project name
{{{RESULT_INSTANCE_ID}}}
The result instance ID
{{{RANDOM_xxx}}}
A random integer where xxx is the max integer size
{{{DATE_xxx}}}
Date/Time format, where xxx can be from the table to the right. Such as yyyy-MM-dd which could be 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

Value Descrption Value Descrption
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 is within the application Server hosting the i2b2 application in a secure folder.
...

Versions Compared

Old Version 46

New Version 47

Key