= 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'

{{{#!sql

  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'

{{{#!sql

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\'

{{{#!sql

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);

}}}

4. Create ontology hierarchy and Concept codes

This is beyond the scope of this page.  In order to stop the UI from giving an error when you click on the root note, you can create the root node in the ontology table.

{{{#!sql

INSERT INTO {Ontology Name from [1]}
           ([C_HLEVEL]
           ,[C_FULLNAME]
           ,[C_NAME]
           ,[C_SYNONYM_CD]
           ,[C_VISUALATTRIBUTES]
           ,[C_TOTALNUM]
           ,[C_BASECODE]
           ,[C_METADATAXML]
           ,[C_FACTTABLECOLUMN]
           ,[C_TABLENAME]
           ,[C_COLUMNNAME]
           ,[C_COLUMNDATATYPE]
           ,[C_OPERATOR]
           ,[C_DIMCODE]
           ,[C_COMMENT]
           ,[C_TOOLTIP]
           ,[UPDATE_DATE]
           ,[DOWNLOAD_DATE]
           ,[IMPORT_DATE]
           ,[SOURCESYSTEM_CD]
           ,[VALUETYPE_CD])
     VALUES
           (0
           ,'{Base path for ontology [4]}'
           ,'{Descriptive name for ontology [3]}'
           ,'N'
           ,'CA'
           ,NULL
           ,NULL
           ,NULL
           ,'concept_cd'
           ,'concept_dimension'
           ,'concept_path'
           ,'N'
           ,'LIKE'
           ,'{Base path for ontology [4]}'
           ,NULL
           ,'{Descriptive name for ontology [3]}'
           ,GETDATE()
           ,NULL
           ,NULL
           ,'{A suitable source perhaps Ontology description from [3]}'
           ,NULL)
GO

}}}