| Version 13 (modified by , 9 years ago) ( diff ) |
|---|
i2b2 HowTo Create an Ontology
The procedure to create the tables for a new ontology.
- 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
) ;
- 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]}');
- 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);
- 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.
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
Or run this script - Edit the parameters first
DECLARE @OntologyName VARCHAR(50), -- The name of the ontology, e.g. 'Onyx'
@Prefix VARCHAR(10), -- The prefix for the concept code, e.g. 'ONX'
@OntologyDescription VARCHAR(100), -- A nice description, e.g. 'BRICCS Onyx Questionnaire'
@OntologyBaseNode VARCHAR(50), -- The name of the base node of the tree within i2b2, e.g. 'Onyx'
@SourceSystem VARCHAR(50), -- The system from where the data came, e.g. 'BRICCS Onyx'
@MetaDatabaseName VARCHAR(50), -- The name of the Meta Database in which to create the Ontology, e.g. 'i2b2_b1_metadata'
@DataDatabaseName VARCHAR(50) -- The name of the Data Database in which to create the Ontology, e.g. 'i2b2_b1_data'
SET @OntologyName = 'EMISPrimaryCareData' -- Must not contain spaces
SET @Prefix = 'EMISPCD'
SET @OntologyDescription = 'EMIS Primary Care Data from GEM'
SET @OntologyBaseNode = 'EMISPrimaryCareData'
SET @SourceSystem = 'Gem Risk Stratification'
SET @MetaDatabaseName = 'i2b2_app03_genvasc_MetaData'
SET @DataDatabaseName = 'i2b2_app03_genvasc_Data'
----------------------------------------------------------------
-------------- T H E A C T U A L S C R I P T ---------------
----------------------------------------------------------------
--
-- CREATE ONTOLOGY TABLE
--
DECLARE @TCreateOntologyTable VARCHAR(MAX)
SET @TCreateOntologyTable = '
USE ' + @MetaDatabaseName + ';
CREATE TABLE ' + @OntologyName + '
( "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
) ;'
EXEC (@TCreateOntologyTable);
-- Upgrade ontology table to version 1.6
DECLARE @UpgradeOntology1_6A VARCHAR(MAX)
DECLARE @UpgradeOntology1_6B VARCHAR(MAX)
SET @UpgradeOntology1_6A = '
USE ' + @MetaDatabaseName + ';
ALTER TABLE ' + @OntologyName + '
ADD m_applied_path varchar(700) NULL
;
ALTER TABLE ' + @OntologyName + '
ADD m_exclusion_cd varchar(25) NULL
;
ALTER TABLE ' + @OntologyName + '
ADD C_PATH VARCHAR(700) NULL
;
ALTER TABLE ' + @OntologyName + '
ADD C_SYMBOL VARCHAR(50) NULL
;
'
SET @UpgradeOntology1_6B = '
USE ' + @MetaDatabaseName + ';
UPDATE ' + @OntologyName + '
SET m_applied_path = ''@''
;
ALTER TABLE ' + @OntologyName + '
ALTER COLUMN [m_applied_path] varchar(700) NOT NULL
;
CREATE INDEX ' + @OntologyName + '_META_FULLNAME_IDX ON ' + @OntologyName + '(C_FULLNAME);
CREATE INDEX ' + @OntologyName + '_META_APPLIED_PATH_IDX ON ' + @OntologyName + '(M_APPLIED_PATH);
'
EXEC (@UpgradeOntology1_6A);
EXEC (@UpgradeOntology1_6B);
--
-- CREATE SCHEME
--
DECLARE @TCreateScheme VARCHAR(MAX)
SET @TCreateScheme = '
USE ' + @MetaDatabaseName + ';
INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
VALUES(''' + @Prefix + ':'', ''' + @Prefix + ''', ''' + @OntologyDescription + ''');'
EXEC (@TCreateScheme);
--
-- CREATE TABLE ACCESS
--
DECLARE @TCreateTableAccess VARCHAR(MAX)
SET @TCreateTableAccess = '
USE ' + @MetaDatabaseName + ';
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(''' + @OntologyName + ''', ''' + @OntologyName + ''', 0, ''\' + @OntologyBaseNode + '\'', ''' + @OntologyDescription + ''', ''N'', ''CA'', NULL, NULL, NULL, ''concept_cd'', ''concept_dimension'', ''concept_path'', ''T'', ''LIKE'', ''\' + @OntologyBaseNode + '\'', NULL, ''' + @OntologyDescription + ''', NULL, NULL, NULL, ''N'', NULL);';
EXEC (@TCreateTableAccess);
--
-- CREATE THE BASE FOR THE ONTOLOGY
--
DECLARE @OntologyID INT
-- Create the Ontology
INSERT INTO [i2b2ClinDataIntegration].[dbo].[Ontology] ([Name],[Prefix],[SourceSystem])
VALUES (
@OntologyName
,@Prefix
,@SourceSystem);
SET @OntologyID = SCOPE_IDENTITY();
-- Create the ontology base code
INSERT INTO [i2b2ClinDataIntegration].[dbo].[OntologyCode] ([ParentID],[OntologyID],[Code],[Name],[OntologyNodeTypeCode],[OntologyNodeStatusCode],[Editable],[MetaDataTypeCode],[MetaUnits])
VALUES (
NULL
,@OntologyID
,1
,@OntologyBaseNode
,'C'
,'A'
,0
,NULL
,NULL
);
-- Insert the base code into the Ontology table
DECLARE @TCreateBaseInOntologyTable VARCHAR(MAX)
SET @TCreateBaseInOntologyTable = '
USE ' + @MetaDatabaseName + ';
INSERT INTO ' + @OntologyName + '
SELECT [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]
,''@'' [m_applied_path]
,NULL [m_exclusion_cd]
,NULL [C_PATH]
,NULL [C_SYMBOL]
FROM [i2b2ClinDataIntegration].[dbo].[i2b2Ontology]
WHERE OntologyID = ' + CONVERT(VARCHAR(10), @OntologyID);
EXEC (@TCreateBaseInOntologyTable);
-- Insert the base code into the Concept Table
DECLARE @TCreateBaseConceptTable VARCHAR(MAX)
SET @TCreateBaseConceptTable = '
USE ' + @DataDatabaseName + ';
INSERT INTO concept_dimension
SELECT
[concept_path]
,[concept_cd]
,[name_char]
,[concept_blob]
,[update_date]
,[download_date]
,[import_date]
,[sourcesystem_cd]
,[UPLOAD_ID]
FROM [i2b2ClinDataIntegration].[dbo].[i2b2OntologyConceptCodes]
WHERE OntologyID = ' + + CONVERT(VARCHAR(10), @OntologyID);
EXEC (@TCreateBaseConceptTable);
----------------------------------------------------------------
------- E N D O F T H E A C T U A L S C R I P T --------
----------------------------------------------------------------
Note:
See TracWiki
for help on using the wiki.
