Overview

The Data Exporter functionality enables an i2b2 user to request patient data and create the data file for the patients found by a desired query. In the configured workflow, a table of variables is designed using create data table for export tool. The predefined data tables are then generated under Data Request breakdown types that can be selected in the Run Query dialog, After the query run, the data requests are displayed under the Data Request Manager tool. Users will be able to view their data requests as well as the status of the data file generation.  When a data request is made, an e-mail is to a predefined data manager The data manager (Manager user role) 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.

The patient data file specifications are configured in the database using configuration parameters in the database.

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

1. User logs into the web client 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 process

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 and 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 - List User's Data Requests

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.
• Option to enter Comments

Data Export - Manager process

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

Data Request Manager - List Users Data Requests

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 

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

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