About the FAIR DB Administrator's Toolset

Introduction

Part of the FAIR Initiative is to make it as easy as possible for a DB administrator (DBA) to insert or update the FAIR data for each patient into the i2b2 DB, after all the tedious upfront works has been done in compiling them.

The FAIR DBA Toolset consists of the following utilities:

PL/SQL scripts containing useful procedures to be stored and used in the i2b2 DB
1. FAIRupdate.sql – contains the FAIRupdate( ) procedure.
2. FAIRshow.sql – contains the FAIRshow( ) procedure.
VBS scripts for generating PL/SQL scripts to call the aforementioned stored procedures to update and show the FAIR XML for specified patients in the i2b2 DB
1. FAIRxls2sqls.vbs – takes an XLS input file that contains the patient FAIR data.
2. FAIRcsv2sqls.vbs - takes a CSV input file that contains the patient FAIR data.

Instructions

With these tools, a DBA simply need to do the following when it is necessary to input / update patient FAIR data into the i2b2 DB:

1. Run FAIRupdate.sql and FAIRshow.sql scripts on the i2b2 DB (e.g. via the Oracle APEX GUI) to load / store the two procedures (note that this only needs to be done once).

2. Compile the patient FAIR data into either an Excel spreadsheet (XLS) file or a comma-separated-values (CSV) file.

3. Run either FAIRxls2sqls.vbs or FAIRcsv2sqls.vbs against the XLS or CSV FAIR data file, respectively, to generate the resulting "FAIRxml-updates.sql" and "FAIRxml-shows.sql" (note: name of each generated file to be specified by the user as part of the invocation of either VBS utility) script files.

4. Run the "FAIRxml-updates.sql" script on the i2b2 DB (e.g. via the Oracle APEX GUI) to input / update the patient FAIR data accordingly.

5. Optionally run the "FAIRxml- shows.sql" script on the i2b2 DB (e.g. via the Oracle APEX GUI) to display the latest patient FAIR data accordingly.

Caveats

N/A.

Additional Information

For additional information regarding this toolset, please refer to the our document available at the UMMS website..

i2b2 Version Compatibilities

This version (v.1.0) of this plugin is developed on i2b2 v.1.6.0.2.

Version History

1.0	Inital release, for i2b2 v1.6.0.2.	(Q4 2012)

Availability

This toolset is free to the rest of the i2b2 community. It can be downloaded from the University of Massachusetts Medical School website for i2b2 related downloads, as well as the Google Code project download page set up for it.

Terms of Use

This toolset is published under the GNU GPL v3 license.

This software is distributed as a plugin to the i2b2 v1.6 webclient framework, and may be used free of charge. It is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

About Us

This toolset was created by S. Wayne Chan, Tom English, Rajani Sadasivam, Thomas Houston, & the Bio-Medical Informatics (BMI) Core Team, University of Massachusetts Medical School, Worcester, MA.