Data Exporter (v 1.8.2)

Overview #

The Data Exporter functionality enables an i2b2 user to create a data table definition and request patient data for a desired cohort. First, a table of variables is designed using create data table for export tool. The defined 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 can 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 .  An e-mail is generated for the Data Manager when the request is submitted. The Data Manager processes the request by generating the data file under the Data Request Manager tool. The patient data is exported as a file and stored in a specified location for retrieval, and the user is notified by e-mail.

Data Export Workflow

Data Table Creation – User process #

The Create Data Table for export feature allows User to design a table of variables of interest using the Design table feature. The table design can then be saved as a template  that can be loaded and 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 (by default) Gender, Age, Race

3. From the Ontology Terms tab, drag and drop additional variables of interest onto the DESIGN TABLE grid. These variables will be added as a 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 file.

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

   

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
2. The table definition can be either saved under MY TABLES or PROJECT SHARED TABLES

Load Table #

Load table definition enables the User to Load a saved table definition 

1. User clicks on Load menu option to display Load Table Definition with  list of user saved definitions
2. Select desired table definition and clicks on LOAD button

Data Request – User process #

Request 1.8.2 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 data request checkboxes.
   1. 1.8.2 requests (custom tables) appear as  User Created:
      checkboxes
      1.8.1 data requests (long fact-oriented tables) appear as Request Data checkboxes.

      

      4. Click run query 

      5. An email is automatically sent to the data manager to inform them of a new data request.

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

      

      Data Request Manager  #

      User’s Data Requests List #

      User-submitted data requests (both 1.8.2 and 1.8.1) are logged under the Data Request Manager tool.

      1. Click on Tools=> Data Request Manager plugin

      

      The Data Request Manager displays  data requests along with their status and details

      

      For non-managers, only the user’s data requests are displayed under the Request Data Manager.

      View Data Request Details #

      2. Click on View Details

      View Details displays the Request details  and Request status as well as other options 

      • Query Name (Click on Query ID number to display the previous query in the Find Patients window)
      • Data Table Definition (Click on View under Data Request Type drop down Request item)
      • Option to Withdraw Request 
      • Option to enter Comments
      • Log info box displays the User’s actions. and log of the Request status

      

      Data Export – Manager process #

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

      Data Request Manager  #

      Data Requests List #

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

      List of User submitted data requests are displayed . 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 panel is similar in display  for non-Manager user, except 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 defined in the HIVE_CELL_PARAMS (see Configuration below). Data file generation status is displayed under the Status column under Data Request Manager

      Status is updated as the data file is processed, from Submitted to File in Progress to File Available. When the file is available, an e-mail notification may be sent to the site’s configured Data Request Email Address (for example, a data manager or project mailbox), depending on local configuration. The process for giving the user access to the file is site-specific.

      Example Export files (all data are fake)

      Two files are generated for each data table exported. 

      Definition file: has the variable names and data types for the data file

      Data file: has the values for the variables

      User-created Demographics #

      Definition file

      Example:1969_Definition_For_Demographics_8_5_25.csv

      TABLE_INSTANCE_ID

      TABLE_INSTANCE_NAME

      USER_ID

      GROUP_ID

      SET_INDEX

      C_FACTTABLECOLUMN

      C_TABLENAME

      COLUMN_NAME

      C_FULLPATH

      C_COLUMNNAME

      C_COLUMNDATATYPE

      C_OPERATOR

      C_DIMCODE

      AGG_TYPE

      CONSTRAIN_BY_DATE_TO

      CONSTRAIN_BY_DATE_FROM

      CONSTRAIN_BY_VALUE_OPERATOR

      CONSTRAIN_BY_VALUE_CONSTRAINT

      CONSTRAIN_BY_VALUE_UNIT_OF_MEASURE

      CONSTRAIN_BY_VALUE_TYPE

      CREATE_DATE

      7

      DEM_6_27

      demoManager

      SQLServerLarge

      0

      sex_cd

      patient_dimension

      Gender

      @

      @

      @

      @

      @

      Value

      37:14.3

      7

      DEM_6_27

      demoManager

      SQLServerLarge

      1

      age_in_years_num

      patient_dimension

      Age

      @

      @

      @

      @

      @

      Value

      37:14.3

      7

      DEM_6_27

      demoManager

      SQLServerLarge

      2

      race_cd

      patient_dimension

      Race

      @

      @

      @

      @

      @

      Value

      37:14.3

      7

      DEM_6_27

      demoManager

      SQLServerLarge

      3

      patient_num

      patient_dimension

      (children) < 18 years old

      \ACT_DEMOACTDemographicsAge< 18 years old

      birth_date

      N

      >

      dateadd(YY, -18, GETDATE())

      Exists

      37:14.3