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