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 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

Image RemovedImage Added

Data Request (User Process)

User logs into webclient and

...

Creates creates and runs a query.
Clicks Run query and ticks boxes for a data request

...

In the Run Query dialog box, select one or more Data Request checkboxes
Image Added
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

...

. The query name (in previous queries) is tagged with the query master id

...

Image Removed

Run a query with a data request

...

, 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 Image Added

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

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.

Image Added

The data export runs in the background and the file is generated in the folder specified in the HIVE_CELL_PARAM
Manger user manually sends a confirmation email to the user on the file generation and retrieval info.

Image Removed

Data Export options in the Run Query Dialog

Image Removed
Sample Letter generated for Data Manager

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 Manager

Results 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:
- Total number of patients returned in your data request: 133
- 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

Example Export files (all data are fake)

Demographics

Image Added

Diagnosis

Image Added

Data Export Configuration

Design and Architecture

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

Image Added

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_RESULT_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

...

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.

QT_QUERY_RESULT_TYPE	user role	data request	data export
DATA_LDS	DATA_LDS	Success	non-display
MANAGER	MANAGER	non-display	Success
MANAGER with DATA_LDS	MANAGER		Success
MANAGER with DATA_LDS	DATA_LDS	Success

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. Any parameters defined in an export XML override the default in HIVE_CELL_PARAMS (e.g., field separator character.)

Name	Value
<request name>_REQUEST

has details on

defines the request email

notification

generation parameters.
Request email can be configured in PM_USER_DATA parameters to define the email address of the User.
User email content configurable in <RequesterEmailMessage>/>xml value parameter
Data Manager gets the data manager email (email address configured in the HIVE_CELL_PARAMS)
Data manager email content configurable in the <DataManagerEmailMessage>/xml value parameter

<?xml version="1.0"?>
<ValueExporter>

<Version><version no><

<Version>[...version number...]</Version>
<CreationDateTime>08/09/2024 12:00:00</CreationDateTime>

<RequestLetter><details sent to

<RequesterEmailMessage>[...details sent to User on data request...] </RequesterEmailMessage>
<DataManagerEmailMessage>[...details sent to data Manager on user data

request>

request...]</

RequestLetter>

DataManagerEmailMessage>
</ValueExporter>

<request name>_CSV

has details on export file generation

defines the data export email parameters and the SQL to for the export file generation.
The data export email has detailed info on the file location and access info and can be customized according to your local institution's policies.
The exported file(s) are generated in a secure location on the server's file system along with the data export email file in a zipped folder
Email content and export SQL are configurable in the XML entries for the request/export in the Value parameters.
Multiple SQL statements can be configured in the Value <file></file> tags as one SQL statement per file tag

<?xml version="1.0"?>
<ValueExporter>

<Version><version no><

<Version>[...version number...]</Version>
<CreationDateTime><date value></CreationDateTime>

<RequesterEmailLetter>details

<DataManagerEmailMessage>details sent to

user

data Manager on data request</

RequesterEmailLetter>
<LetterFilename><name of the file></LetterFilename>
<Letter> <letter content></Letter>
<Table>
<Filename>filename</Filename>
<Query><SQL

DataManagerEmailMessage>
<File>
<Filename>[... filename specification..]</Filename>
<Query>[... SQL query to generate the data export

content><

content...]</Query>

<SeparatorCharacter><file format seperator><

<SeparatorCharacter>[...file format seperator...]</SeparatorCharacter>
</

Table>

File>
</ValueExporter>

Example

...

Requests

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

Â

Â

<RequesterEmailLetter>Your

<RequesterEmailMessage>Your request on {{{PROJECT_ID}}} requested i2b2 request
entitled - "{{{QUERY_NAME}}}", submitted on {{{QUERY_STARTDATE}}}, with the query master of {{{QUERY_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_ID}}}.
</

RequestLetter>

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

<RequesterEmailLetter>Your

<RequesterEmailMessage>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>

RequesterEmailMessage>

<RequestLetter>This

<DataManagerEmailMessage>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>

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.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	/tmp/{{{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

Variables for File name entries

Image RemovedImage Removed

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

Image Removed

Diagnosis

Image Removed

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_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

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.

Versions Compared

Old Version 42

New Version Current

Key

Overview

Data Export Workflow

Data Request (User Process)