Overview

The Data Exporter functionality enables an i2b2 user to create a data table definition and request patient data for the desired query. In the data table configured workflow, a table of variables is designed using create data table for export tool. The predefined data tables are then displayed under the Data Request(s) breakdown types that can be selected in the Run Query dialog, After the query run, the Manager user will be able to view and manage the data requests under the Data Request Manager tool. i2b2 Users will be able to view their data requests  status and details .  Email are generated both for the  data Manager and the i2b2 User when the request is submitted. The data Manager processes the request by generating the datafile under the Data Request Manager tool. The patient data is exported as a file and stored in a specified location for retrieval.

Data Export Workflow

Data Table Creation - 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 i2b2 User to create a data table definition with list of variables of interest.

1. User logs into the web client and accesses create data table for export tool under the 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. Click on Save Table.
2. The table definiton can be either saved under My Tables or Project Shared Tables

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 process

Data Request - Request User-created data exports

1. User logs into web client and creates a query.
2. In the Run Query dialog box, User created table definitions are displayed under Data Request(s) section
3. Select one or more table definition Request checkboxes along with Patient set breakdown checked

Data Request - Request 1.8.1 data exports

User can now request data for 1.8.1 data request breakdowns along with 1.8.2 requests.

4. Select 1.8.1 data request check boxes 

5. Click run query

4. Emails are automatically sent - one to the User's-email, to inform them the request has been made; and one to the specified manager user to inform them to generate the data file.

5. The table definition name with the status is displayed as one of the breakdown items in the previous query.

Data Request Manager - User's Data Requests List

After the Data request query finishes (in the previous query panel), the request details and the status of the request are logged under the Data Request Manager tool.

1. Click on Tools=> Data Request Manager plugin

The Data Request Manager displays all the data requests user made along with their status 

View Data Request Details

2. Click on View Details

View Details displays the Request details as well as other options to

• View the Query (Click on Query ID number to display the previous query in the Find Patients window)
• View the Data Table Definition (Click on View under Data Request Type drop down Request item
• Option to Withdraw Request (Click on Withdraw Request button under Actions section. The Data Request status changes to Withdrawn
• Option to enter Comments
• Log info box displays the User's actions. and logs the current status of the Request

Data Export - Manager process

The User created data requests are managed by the Manager user using the Data Request Manager tool. 

Data Request Manager - Users Data Requests List

1. Click on Tools=> Data Request Manager plugin.

All the data requests submitted by the Users are listed under the List of Export Data Requests. The initial status of the requests is Submitted. 

The Manager User can view the details of the Requests using VIEW DETAILS button and generate the Data files using Create File(s) button)

View Data Request Details

Click on View Details. 

The View Details page is similar to the display as a non-Manager user, In addition the Manager User can change the Data Request Status..

The Log info box displays the status of the Data Request as well as the Data File creation status.

Create Data File(s)

Click on Create File(s) on the Data Request Manager page

The data export runs in the background and the file is generated in a specified folder. Status of the data file creation is displayed under the Status column.

Status will change as the Data File is processed. from Submitted to File in Progress to File Available.

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

User visibility to Data Table Creation, Data Request and Data Export is based on User role configuration. Configuration is managed by the Admin user using Admin Dashboard plugin.

Below is the list of Actions available based on the User role.

Database Configuration 

RPDO_TABLE_REQUEST

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

QT_BREAKDOWN_PATH

QT_Breakdown_Path table has been modified to include a new column Group_id. It logs the Project_id value.

Database Updates

RPDO_TABLE_REQUEST 

User created concepts in the data table design will be dynamically logged in the RPDO_TABLE_REQUEST at the time of Table Definition Save.  Below is the list that correspond to values of the User created Data Table 

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.

QT_BREAKDOWN_PATH - Table Definition Creation

QT_breakdown_path logs the table_instance_id  and the stored procedure EXEC statement for creating the Data file at the time of Data Table Definition creation

• The Table_instance_id is logged in the Name column when the user saves the table_definition. 
• The Value column logs the EXEC statement of the stored procedure that generates the data file.
• The result_instance_id variable gets updated with the numeric value from the QUERY_RESULT_INSTANCE  where the  result_type_id  (Ptset)=1 at the time of creation of Data file.

Example:

QT_QUERY_RESULT_INSTANCE

When the User runs a query with User created Data Request option selected, a row is logged in the QT_Query_Result_Instance for the  result_type_id generated in the QT_Result_type

QT_BREAKDOWN_PATH - Data File Creation

The Value column is updated with result_instance_id from the Q_Result_Instance in the EXEC statement and the stored procedure to generate the Data flle is executed

Example:

QT_XML_RESULT

Metadata about data request queries are stored in the QT_XML_RESULT table, in the XML_VALUE field. The Value column gets updated with the change in Status of the Data Request. Following are the staus change in the <data column> tag

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. 

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.