Once you have created an Ontology you can create the codes (which go in the concept_cd column in the facts table). To do this you run the following code (I'll detail the input in a mo).

execute dbo.CreateOntologyCode @OntologyID = 99, @ParentID = 7777777, @Name = 'ethnicity',@SourceSystem = 'DREAM_RedCap',@SourceCode = 'ethnicity';

• @OntologyID, this is 1 for GenvascCiviCRM, 27 for Gem BRICCS REDCap, the full list can be found by using:

SELECT * FROM [i2b2ClinDataIntegration].[dbo].Ontology;

• @ParentID, this needs must be a container node (top level is possible however if there is a hierarchy then don't add all the codes to the top level, instead create the child container nodes as needed and then add the codes to them).
  Note that all the codes will be Leaf nodes, this indicates that is will be containing data and that no other codes will hang off them.

the ParentID can be found by using the following code:

SELECT * FROM [i2b2ClinDataIntegration].[dbo].OntologyCode where [OntologyID] = 99;

replace 99 with your ontology (49 for example is Dream).

• @Name, this is what you are going to be calling the code by from now on

• @SourceSystem (will be whatever you put into the SourceSystem column in the Ontology table

• @SourceCode, this is what the incomming field name is in the source system database (and may not be a helpfully named field, which is why we have the @Name field which gives us a chance to change it to something better.

Below is a abstract for the dream ontology as way of example:
Notice how the Top level (DREAM_RedCap) has a Null ParentID. Notice how the two containers ParentID is the Id of the top level (DREAM_RedCap) Also worth noting that only one of the left nodes conects directly to the top level container (Enrollment Date), most of the codes you'll add will have a sub container. In this simple example there are only three levels however you could have a 5,6 or 7 level hierarchy.

Example of the Dream ontology with the Top level, two containers of the Top, and the Leafs of those.
ID	ParentID	OntologyID	Code	Name	OntologyNodeTypeCode
1969358	NULL	49	1	DREAM_RedCap	C
1969362	1969358	49	5	demographics_and_social_history	C
1969363	1969358	49	6	medical_history_and_exam	C
1969381	1969358	49	24	Enrollment Date	L
1969383	1969362	49	26	ethnicity	L
1969385	1969363	49	28	medical_history	L
1969391	1969363	49	34	medical_history_other	L
1969399	1969362	49	42	bmi	L
1969400	1969363	49	43	diabetes	L
1969402	1969363	49	45	diastolic_ip	L
1969409	1969363	49	52	heart_rate_ip	L
1969411	1969362	49	54	height	L

To work through one please see the ethnicity code with ID 1969383. This has a Parent ID of 1969362 which is the ID of demographics_and_social_history which in turn has a Parent ID of 1969358 which is the ID of DREAM_RedCap (the top level container).
this shows that the ethnicity field is in the demographics_and_social_history page / form of the DREAM_RedCap ontology.

Snipet to change Left nodes to Container nodes:

update [i2b2ClinDataIntegration].[dbo].OntologyCode set OntologyNodeTypeCode = 'C' where Ontologyid = 99 and Name in (' Your' ,'list' ,'of' ,'Names')

Where 99 would be the Ontologyid you are using (49 for example is Dream) and you would list the Names you added (which default to 'L' (leaf node)).

Once this is done you will need to run the following to update the Concept code in the database the example is SCAD database below which has an ID of 50:

USE [i2b2ClinDataIntegration]
GO
exec [dbo].[UpdateOntologyConceptCodesInStudy] @StudyName = 'SCAD', @OntologyID = 50 }}}

then, for completeness ensure all conceptcodes are upto date using the proc:

exec dbo.UpdateOntologyConceptCodes (Which, btw, is set to run daily in the TEL UHLDWH SQL Agent Job, so if you don't run it the job will in the wee hours, but it takes 10 mins to run)

Error: Macro BackLinks(None) failed

'Environment' object has no attribute 'get_db_cnx'