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

Data Table Creation- User-created Data Table (User process)

The Create Data Table feature allows User to design a table of variables of interest using the Design table feature. The table can then be saved as a template  that can be loaded and re-used to request data export.

Design Table

The DESIGN TABLE panel allows User to create a data table with list of variables of interest.

1. User logs into the webclient and accesses Data Export tool under the Analysis Tools plugin

2. The design table panel displays list of predefined variables that will be included by default in the data export file. These predefined variables are Gender, Age, Race, and Ethnicity

3. From the Ontology Terms tab, drag and drop additional variables of interest onto the DESIGN TABLE grid. These variables will be added to the list of variables included in the data export. The predefined variables are locked and cannot be unchecked since they are required for every data table.  Other variables can be deleted under Actions column to exclude them from the data set file.

4. Aggregation method for the variable can be applied by clicking on the Value in the Aggregation Method column to assign aggregate options to each variable 

5. 

Aggregate Options

The availability of aggregation options depends on the type of concept it aggregates on.

Example:

• Aggregation options for a non-numeric variable such as Diagnosis are Existence (Yes/No), Count: Number of Concepts, Count: Number of Dates, Count Number of Encounters, Count Number of Facts, Count Number of Providers, Date (First), Date (Most Recent), Most Frequent Concept (Codes)

• Aggregation options for concepts that support numeric values such as labs include Average, Min, Max, etc. in addition to all the functions available to normal variables

Preview Table

Table Design can be previewed to get a preview of the data export file that will be generated.

1. User clicks on Preview Table to verify the variables added are displayed  in the data columns in the format desired.

Save Table

1. User clicks on Save Table

Load Table

Saved table definition enables the User to Load  a saved table definition to be used for data export

1. User clicks on Load menu option to display the list of your saved Table definitions. Under MY TABLES,
2. Select desired table definition and clicks on LOAD button

Data Request - User-created Data Request (User process)

1. User logs into webclient and creates and runs a query.
2. In the Run Query dialog box, select one or more User created Data table definition Request checkboxes and click run query
   
   
3. 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, so it can be identified by the manager.

Data Export - User-created Data Requests (Manager process)

User-created data requests are managed by Data Request Manager tool where a manager user can view the request details and generate data files

Data Request Manager

View Data Request Details

Generate data file 

The data export runs in the background and the file is generated in a specified folder 

Example Export files (all data are fake)

User-created Demographics

User-created Diagnosis

Data Export Configuration

Design and Architecture

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

User Roles and Actions

Database changes

In order to support the  data Table  design, a new table RPDO_TABLE_REQUEST is added.  The metadata in Data Table design is stored in the table when the data Table is saved.

There are 4 patient_dimension default concepts that  are loaded into the RPDO_TABLE_REQUEST which are required. These are: race_cd (ethnicity), reace_cd(race), age_in_years_num, sex_cd and gender

Any other user created concepts in the data table design will be stored in the RPDO_TABLE_REQUEST.  Below is the list that correspond to values in the Data Table

Configuring Database Parameters

Data table definition details

QT_QUERY_RESULT_TYPE

The data table definitions are logged as breakdowns in the QT_QUERY_RESULT_TYPE table. The values are populated dynamically when the user saves the data table definition.

Following values are stored

RESULT_TYPE_ID: <TABLE_INSTANCE_ID>

NAME: Auto generated number: RPDO_<n>

VISUAL_ATTRIBUTE_TYPE_ID is set to LU 

USER_ROLE_CD is set to DATA_LDS /NULL?

DISPLAY_TYPE_ID: CATNUM

CLASSNAME: 

Example:

Data Request definition details

QT_BREAKDOWN_PATH

Qt_breakdown_path logs the table definition value and the result_instance_id. to be associated with the ptset. query.

The table definition is logged as the table_instance_id when the user saves the table_definition.  The result_instance_id is numeric value of the table_instance_id,

NAME:  <Table_Instance_id>

VALUE:  <Result_instance_id>

Example:

Data file format/location and Email parameters set-up: HIVE_CELL_PARAMS

The generated Data file format, location and email server parameters are configurable in the HIVE_CELL_PARAMS. 

Request and Export Results:

Metadata about data request and export queries are stored in the QT_XML_RESULT table, in the XML_VALUE field:

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.