i2b2 Create an Ontology

The procedure to create the tables for a new ontology.

1. Create the ontology table in the Meta Database

[1] Name of the ontology, e.g. 'ONYX'

  CREATE TABLE {Ontology Name from [1]}
   (    "C_HLEVEL" INT                  NOT NULL, 
        "C_FULLNAME" VARCHAR(700)       NOT NULL, 
        "C_NAME" VARCHAR(2000)          NOT NULL, 
        "C_SYNONYM_CD" CHAR(1)          NOT NULL, 
        "C_VISUALATTRIBUTES" CHAR(3)    NOT NULL, 
        "C_TOTALNUM" INT                        NULL, 
        "C_BASECODE" VARCHAR(50)        NULL, 
        "C_METADATAXML" TEXT            NULL, 
        "C_FACTTABLECOLUMN" VARCHAR(50) NOT NULL, 
        "C_TABLENAME" VARCHAR(50)       NOT NULL, 
        "C_COLUMNNAME" VARCHAR(50)      NOT NULL, 
        "C_COLUMNDATATYPE" VARCHAR(50)  NOT NULL, 
        "C_OPERATOR" VARCHAR(10)        NOT NULL, 
        "C_DIMCODE" VARCHAR(700)        NOT NULL, 
        "C_COMMENT" TEXT                        NULL, 
        "C_TOOLTIP" VARCHAR(900)        NULL, 
        "UPDATE_DATE" DATETIME          NOT NULL, 
        "DOWNLOAD_DATE" DATETIME        NULL, 
        "IMPORT_DATE" DATETIME  NULL, 
        "SOURCESYSTEM_CD" VARCHAR(50)   NULL, 
        "VALUETYPE_CD" VARCHAR(50)      NULL
   ) ;

2. Create a Scheme

A scheme is the prefix for a concept code. Not exactly sure what it is used for, but hey ho. You create one like this:

[2] Scheme name, e.g. 'CBO' [3] Descriptive name for ontology, e.g. 'Onyx Ontology'

INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
  VALUES('{Scheme name from [2]}:', '{Scheme name from [2]}', '{Descriptive name for ontology from [3]}');

3. Tell i2b2 about the new ontology

[4] Base path for ontology, e.g. '\onyx\'

INSERT INTO TABLE_ACCESS(C_TABLE_CD, C_TABLE_NAME, C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_DIMTABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, C_COMMENT, C_TOOLTIP, C_ENTRY_DATE, C_CHANGE_DATE, C_STATUS_CD, C_PROTECTED_ACCESS, VALUETYPE_CD)
  VALUES('{Ontology Name from [1]}', '{Ontology Name from [1]}', 0, '{Base path for ontology [4]}', '{Descriptive name for ontology [3]}', 'N', 'CA', NULL, NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '{Base path for ontology [4]}', NULL, '{Descriptive name for ontology [3]}', NULL, NULL, NULL, 'N', NULL);