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)
