= i2b2 HowTo Create an Ontology Tags: [[i2b2]] [[HowTo]] == Procedure 1. Log into the server `UHLSQLBRICCSDB\UHLBRICCSDB` and database `i2b2ClinDataIntegration` 2. Execute the stored procedure `[dbo].[CreateEmptyOntology](@OntologyName, @Prefix, @SourceSystem, @OntologyBaseNode, @OntologyDescription)` - Where: - `@OntologyName` is the name of the ontology and must not contain spaces, e.g., 'EMISPrimaryCareData' - `@Prefix` is the prefix for the concept codes and must not contain spaces, e.g., 'EMISPCD' - `@SourceSystem` is the system from which the data is being copied, e.g., 'Gem Risk Stratification' - `@OntologyBaseNode` is the name of the root node for the ontology, e.g., 'EMISPrimaryCareData' - `@OntologyDescription` is a short description of the ontology, e.g., 'EMIS Primary Care Data from GEM' 3. Execute the stored procedure `[dbo].[CreateOntologyInStudy](@OntologyID, @MetaDatabaseName, @DataDatabaseName)` for each project that requires the ontology - Where: - `@OntologyID` is the ID returned by the previous stored procedure. - `@MetaDatabaseName` is the name of the i2b2 meta database where the ontology should be created, e.g., 'project_MetaData' - `@DataDatabaseName` is the name of the i2b2 data database where the ontology should be created, e.g., 'project_Data' 4. Amend the stored procedure `[dbo].[LoadAllOntologiesIntoConceptDimension]` in the i2b2 data database to add the import of the codes into the `concept_dimension`. - The line should be of the form: `EXEC dbo.LoadOntologyIntoConceptDimension {Ontology ID};`, where `OntologyID` is the ID of the ontology created above. 5. Create a stored procedure to import the ontology codes into the i2b2 meta database. The procedure should follow the form: {{{ CREATE PROC [dbo].[Update{Ontology Name}] AS DELETE FROM dbo.{Ontology Table Name}; INSERT INTO dbo.{Ontology Table Name} 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] ,[m_exclusive_cd] ,[C_PATH] ,[C_SYMBOL] FROM [i2b2ClinDataIntegration].[dbo].[i2b2Ontology] WHERE OntologyID = {Ontology ID} }}} - Where: - `Ontology Name` is the name used for the ontology above - `Ontology Table Name` is the name of the ontology table created in the i2b2 meta database. - `OntologyID` is the ID of the ontology created above [[BackLinks]]