View Creator

Installation Document - 1.1

Table of Contents

Table of Contents

Document Version History

Pre-requisites for View Creator

View Creator Install and Execution Instructions

Installing Procedures for pagination in Oracle

Installing Procedures for pagination in Sybase IQ

Document Version History

Pre-requisites for View Creator

• Sun JDK 1.5

http://java.sun.com/javase/downloads/index_jdk5.jsp

• Working i2b2 environment

https://www.i2b2.org/software/index.html

View Creator Install and Execution Instructions

• The View Creator allows for accessing data pertaining to i2b2 queries by creating views on the impacted i2b2 tables.  Download View Creator executable (ViewCreator-1.1.jar) from Released Builds section (available under Released Builds and Documentation).      

• Extract the executable onto a local folder using the following command:

jar xvf ViewCreator-1.1.jar

• Configure the database properties pertaining to i2b2demodata schema of your environment in view.properties (found under the extraction directory). 

• Configure the logger properties in log4j.properties (found under extraction folder).

• Configure the property ‘GENERATE_VISIT_ALL_FACTS’ in view.properties (found under the extraction folder) if you need to create an additional view apart from the basic views that View Creator generates.  This view returns all records from OBSERVATION_FACT pertaining to unique visits from i2b2 query.  The significance of this view is that it not only returns diagnostic (OBSERVATION_FACT) records pertaining to the i2b2 query but also other diagnostic records that have same visit numbers as returned by the query. 

• Execute the View Creator (from the extraction directory) by passing requisite parameters.  The View Creator takes a query name (enclosed within double quotes) and user name as parameters.  An account would be created in the database for the supplied user name automatically and select permission on the created views would be granted for this user.  By default the password for the created account is same as user name.  Set the class-path to current directory if you run into class not found issue.

java com.ucsf.mapper.view.ViewCreator “<i2b2_query_name>” <user_name>

• On successful execution the View Creator displays the names of the views created prefixed with its schema name.  By default the views get created in the i2b2demodata schema for Oracle and in the user schema for Sybase.  The View Creator creates five basic views and one additional view if GENERATE_VISIT_ALL_FACTS flag is enabled in view.properties.

• The five basic views include one each on CONCEPT_DIMENSION, PATIENT_DIMENSION, OBSERVATION_FACT and two on VISIT_DIMENSION.  Per convention the views are named after the i2b2 query name followed by the table name.  For instance the views pertaining to CONCEPT_DIMENSION, PATIENT_DIMENSION, OBSERVATION_FACT end-with “_Concept”, “_Patient”, “_ObsFact” respectively.  The two views on VISIT_DIMENSION end-with ‘_Visit’ and ‘_AllVisit’ with the former listing only unique visits satisfying all of the i2b2 query criteria and latter listing all visits satisfying any of the i2b2 query criteria.  The additional view enabled by GENERATE_VISIT_ALL_FACTS flag ends with ‘_VisitAllFacts’.

Installing Procedures for pagination in Oracle

• The data from the views created on Oracle can be paginated 10000 records at a time by installing a set database procedures.  These procedures can be created by running the script Procedures_Oracle.sql (found under the extraction folder).

• The above script creates the following database procedures that are named per the convention “<schema_name>.<package_name>.<procedure_name>”.

1. I2B2DEMODATA.PATIENT_PKG.OBS_FACT_DATA
2. I2B2DEMODATA.PATIENT_PKG.VISIT_DATA
3. I2B2DEMODATA.PATIENT_PKG.PATIENT_DATA
4. I2B2DEMODATA.PATIENT_PKG.CONCEPT_DATA
5. I2B2DEMODATA.PATIENT_PKG.VISIT_ALL_FACTS_DATA

• Each of these procedures takes in a view name and page number as input parameters and outputs a cursor of type as defined in the script (for instance the procedure VISIT_DATA outputs a cursor of type VISIT_TYPE).  The procedures accept only respective views as input meaning the procedure OBS_FACT_DATA takes in only views ending “_ObsFact “ as parameter.  The only exception to this rule is that the procedure VISIT_DATA takes in views ending with both “_Visit” and “_AllVisit” as parameter. 

• Here’s sample PL / SQL code that invokes OBS_FACT_DATA procedure and iterates over the cursor returned by the same.

create or replace PROCEDURE i2b2demodata.test (view_name IN VARCHAR2, page_num IN int ) IS

p_retcur i2b2demodata.OBS_FACT_PKG.OBS_FACT_TYPE;

at_rec   i2b2demodata.OBS_FACT_TEMP%ROWTYPE;

BEGIN

  i2b2demodata.OBS_FACT_PKG.OBS_FACT_DATA(view_name, page_num, p_retcur);

  FOR i IN 1 .. 10000

    LOOP

      FETCH p_retcur

      INTO at_rec;

      dbms_output.put_line(i || ' - ' || at_rec.encounter_num ||

      ' - ' || at_rec.patient_num ||

      ' - ' || at_rec.concept_cd ||

      ' - ' || at_rec.tval_char);

  END LOOP;

END ;

/

• The above test procedure could be executed by passing in relevant view name (as noted earlier) and page number as parameters.

call i2b2demodata.test('<view_name>',1);

Installing Procedures for pagination in Sybase IQ

• The data from the views created on IQ can be paginated 10000 records at a time by installing a set of database procedures.  These procedures can be created by running the script Procedures_IQ.sql (found under the extraction folder).

• The above script creates the following database procedures that are named per the convention “<schema_name>.<procedure_name>”.

1. i2b2demodata.OBS_FACT_DATA
2. i2b2demodata.VISIT_DATA
3. i2b2demodata.PATIENT_DATA
4. i2b2demodata.CONCEPT_DATA
5. i2b2demodata.VISIT_ALL_FACTS_DATA

• Each of these procedures takes in a view name and page number as input parameters and outputs an implicit cursor.   The procedures accept only respective views as input meaning the procedure OBS_FACT_DATA takes in only views ending “_ObsFact “ as parameter.  The only exception to this rule is that the procedure VISIT_DATA takes in views ending with both “_Visit” and “_AllVisit” as parameter.  For instance the CONCEPT_DATA procedure could be invoked by passing in a view ending with “_Concept” and page number as shown.

call i2b2demodata.CONCEPT_DATA('<view_prefix>_Concept',1);