Changes between Version 13 and Version 14 of i2b2 HowTo Create an Ontology


Ignore:
Timestamp:
12/14/15 10:11:05 (8 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • i2b2 HowTo Create an Ontology

    v13 v14  
    33Tags: [[i2b2]] [[HowTo]]
    44
    5 The procedure to create the tables for a new ontology.
     5== Procedure
    66
    7 1. Create the ontology table in the Meta Database
    8 
    9 [1] Name of the ontology, e.g. 'ONYX'
    10 
    11 {{{#!sql
    12 
    13   CREATE TABLE {Ontology Name from [1]}
    14    (    "C_HLEVEL" INT                  NOT NULL,
    15         "C_FULLNAME" VARCHAR(700)       NOT NULL,
    16         "C_NAME" VARCHAR(2000)          NOT NULL,
    17         "C_SYNONYM_CD" CHAR(1)          NOT NULL,
    18         "C_VISUALATTRIBUTES" CHAR(3)    NOT NULL,
    19         "C_TOTALNUM" INT                        NULL,
    20         "C_BASECODE" VARCHAR(50)        NULL,
    21         "C_METADATAXML" TEXT            NULL,
    22         "C_FACTTABLECOLUMN" VARCHAR(50) NOT NULL,
    23         "C_TABLENAME" VARCHAR(50)       NOT NULL,
    24         "C_COLUMNNAME" VARCHAR(50)      NOT NULL,
    25         "C_COLUMNDATATYPE" VARCHAR(50)  NOT NULL,
    26         "C_OPERATOR" VARCHAR(10)        NOT NULL,
    27         "C_DIMCODE" VARCHAR(700)        NOT NULL,
    28         "C_COMMENT" TEXT                        NULL,
    29         "C_TOOLTIP" VARCHAR(900)        NULL,
    30         "UPDATE_DATE" DATETIME          NOT NULL,
    31         "DOWNLOAD_DATE" DATETIME        NULL,
    32         "IMPORT_DATE" DATETIME  NULL,
    33         "SOURCESYSTEM_CD" VARCHAR(50)   NULL,
    34         "VALUETYPE_CD" VARCHAR(50)      NULL
    35    ) ;
    36 
    37 }}}
    38 
    39 2. Create a Scheme
    40 
    41 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:
    42 
    43 [2] Scheme name, e.g. 'CBO'
    44 [3] Descriptive name for ontology, e.g. 'Onyx Ontology'
    45 
    46 {{{#!sql
    47 
    48 INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
    49   VALUES('{Scheme name from [2]}:', '{Scheme name from [2]}', '{Descriptive name for ontology from [3]}');
    50 
    51 }}}
    52 
    53 3. Tell i2b2 about the new ontology
    54 
    55 [4] Base path for ontology, e.g. '\onyx\'
    56 
    57 {{{#!sql
    58 
    59 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)
    60   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);
    61 
    62 }}}
    63 
    64 4. Create ontology hierarchy and Concept codes
    65 
    66 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.
    67 
    68 {{{#!sql
    69 
    70 INSERT INTO {Ontology Name from [1]}
    71            ([C_HLEVEL]
    72            ,[C_FULLNAME]
    73            ,[C_NAME]
    74            ,[C_SYNONYM_CD]
    75            ,[C_VISUALATTRIBUTES]
    76            ,[C_TOTALNUM]
    77            ,[C_BASECODE]
    78            ,[C_METADATAXML]
    79            ,[C_FACTTABLECOLUMN]
    80            ,[C_TABLENAME]
    81            ,[C_COLUMNNAME]
    82            ,[C_COLUMNDATATYPE]
    83            ,[C_OPERATOR]
    84            ,[C_DIMCODE]
    85            ,[C_COMMENT]
    86            ,[C_TOOLTIP]
    87            ,[UPDATE_DATE]
    88            ,[DOWNLOAD_DATE]
    89            ,[IMPORT_DATE]
    90            ,[SOURCESYSTEM_CD]
    91            ,[VALUETYPE_CD])
    92      VALUES
    93            (0
    94            ,'{Base path for ontology [4]}'
    95            ,'{Descriptive name for ontology [3]}'
    96            ,'N'
    97            ,'CA'
    98            ,NULL
    99            ,NULL
    100            ,NULL
    101            ,'concept_cd'
    102            ,'concept_dimension'
    103            ,'concept_path'
    104            ,'N'
    105            ,'LIKE'
    106            ,'{Base path for ontology [4]}'
    107            ,NULL
    108            ,'{Descriptive name for ontology [3]}'
    109            ,GETDATE()
    110            ,NULL
    111            ,NULL
    112            ,'{A suitable source perhaps Ontology description from [3]}'
    113            ,NULL)
    114 GO
    115 
    116 }}}
    117 
    118 Or run this script - Edit the parameters first
    119 
    120 {{{#!sql
    121 
    122 DECLARE @OntologyName VARCHAR(50), -- The name of the ontology, e.g. 'Onyx'
    123                 @Prefix VARCHAR(10), -- The prefix for the concept code, e.g. 'ONX'
    124                 @OntologyDescription VARCHAR(100), -- A nice description, e.g. 'BRICCS Onyx Questionnaire'
    125                 @OntologyBaseNode VARCHAR(50), -- The name of the base node of the tree within i2b2, e.g. 'Onyx'
    126                 @SourceSystem VARCHAR(50), -- The system from where the data came, e.g. 'BRICCS Onyx'
    127                 @MetaDatabaseName VARCHAR(50), -- The name of the Meta Database in which to create the Ontology, e.g. 'i2b2_b1_metadata'
    128                 @DataDatabaseName VARCHAR(50) -- The name of the Data Database in which to create the Ontology, e.g. 'i2b2_b1_data'
    129                
    130 SET @OntologyName = 'EMISPrimaryCareData' -- Must not contain spaces
    131 SET @Prefix = 'EMISPCD'
    132 SET @OntologyDescription = 'EMIS Primary Care Data from GEM'
    133 SET @OntologyBaseNode = 'EMISPrimaryCareData'
    134 SET @SourceSystem = 'Gem Risk Stratification'
    135 SET @MetaDatabaseName = 'i2b2_app03_genvasc_MetaData'
    136 SET @DataDatabaseName = 'i2b2_app03_genvasc_Data'
    137 
    138 ----------------------------------------------------------------
    139 -------------- T H E   A C T U A L   S C R I P T ---------------
    140 ----------------------------------------------------------------
    141 
    142 --
    143 -- CREATE ONTOLOGY TABLE
    144 --
    145 
    146 DECLARE @TCreateOntologyTable VARCHAR(MAX)
    147 SET @TCreateOntologyTable = '
    148    USE ' + @MetaDatabaseName + ';
    149    
    150    CREATE TABLE ' + @OntologyName + '
    151    (    "C_HLEVEL" INT                  NOT NULL,
    152         "C_FULLNAME" VARCHAR(700)       NOT NULL,
    153         "C_NAME" VARCHAR(2000)          NOT NULL,
    154         "C_SYNONYM_CD" CHAR(1)          NOT NULL,
    155         "C_VISUALATTRIBUTES" CHAR(3)    NOT NULL,
    156         "C_TOTALNUM" INT                    NULL,
    157         "C_BASECODE" VARCHAR(50)        NULL,
    158         "C_METADATAXML" TEXT            NULL,
    159         "C_FACTTABLECOLUMN" VARCHAR(50) NOT NULL,
    160         "C_TABLENAME" VARCHAR(50)       NOT NULL,
    161         "C_COLUMNNAME" VARCHAR(50)      NOT NULL,
    162         "C_COLUMNDATATYPE" VARCHAR(50)  NOT NULL,
    163         "C_OPERATOR" VARCHAR(10)        NOT NULL,
    164         "C_DIMCODE" VARCHAR(700)        NOT NULL,
    165         "C_COMMENT" TEXT                    NULL,
    166         "C_TOOLTIP" VARCHAR(900)        NULL,
    167         "UPDATE_DATE" DATETIME          NOT NULL,
    168         "DOWNLOAD_DATE" DATETIME        NULL,
    169         "IMPORT_DATE" DATETIME  NULL,
    170         "SOURCESYSTEM_CD" VARCHAR(50)   NULL,
    171         "VALUETYPE_CD" VARCHAR(50)      NULL
    172    ) ;'
    173    
    174 EXEC (@TCreateOntologyTable);
    175 
    176 -- Upgrade ontology table to version 1.6
    177 
    178 DECLARE @UpgradeOntology1_6A VARCHAR(MAX)
    179 DECLARE @UpgradeOntology1_6B VARCHAR(MAX)
    180 
    181 SET @UpgradeOntology1_6A = '
    182    USE ' + @MetaDatabaseName + ';
    183 
    184     ALTER TABLE ' + @OntologyName + '
    185         ADD m_applied_path varchar(700) NULL
    186     ;
    187    
    188     ALTER TABLE ' + @OntologyName + '
    189         ADD m_exclusion_cd varchar(25) NULL
    190     ;
    191    
    192     ALTER TABLE ' + @OntologyName + '
    193         ADD     C_PATH  VARCHAR(700)   NULL
    194     ;
    195    
    196     ALTER TABLE ' + @OntologyName + '
    197         ADD     C_SYMBOL VARCHAR(50)    NULL
    198     ;
    199 '
    200 
    201 SET @UpgradeOntology1_6B = '
    202    USE ' + @MetaDatabaseName + ';
    203 
    204     UPDATE ' + @OntologyName + '
    205     SET m_applied_path = ''@''
    206     ;
    207 
    208     ALTER TABLE ' + @OntologyName + '
    209         ALTER COLUMN [m_applied_path] varchar(700) NOT NULL
    210     ;
    211        
    212     CREATE INDEX ' + @OntologyName + '_META_FULLNAME_IDX ON ' + @OntologyName + '(C_FULLNAME);
    213    
    214     CREATE INDEX ' + @OntologyName + '_META_APPLIED_PATH_IDX ON ' + @OntologyName + '(M_APPLIED_PATH); 
    215 '
    216 
    217 EXEC (@UpgradeOntology1_6A);
    218 EXEC (@UpgradeOntology1_6B);
    219 
    220 --
    221 -- CREATE SCHEME
    222 --
    223 
    224 DECLARE @TCreateScheme VARCHAR(MAX)
    225 SET @TCreateScheme = '
    226    USE ' + @MetaDatabaseName + ';
    227    
    228    INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
    229    VALUES(''' + @Prefix + ':'', ''' + @Prefix + ''', ''' + @OntologyDescription + ''');'
    230    
    231 EXEC (@TCreateScheme);
    232 
    233 --
    234 -- CREATE TABLE ACCESS
    235 --
    236 
    237 DECLARE @TCreateTableAccess VARCHAR(MAX)
    238 SET @TCreateTableAccess = '
    239    USE ' + @MetaDatabaseName + ';
    240    
    241    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)
    242    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);';
    243    
    244 EXEC (@TCreateTableAccess);
    245 
    246 --
    247 -- CREATE THE BASE FOR THE ONTOLOGY
    248 --
    249 
    250 DECLARE @OntologyID INT
    251 
    252 -- Create the Ontology
    253 
    254 INSERT INTO [i2b2ClinDataIntegration].[dbo].[Ontology] ([Name],[Prefix],[SourceSystem])
    255 VALUES (
    256                         @OntologyName
    257            ,@Prefix
    258            ,@SourceSystem);
    259 
    260 SET @OntologyID = SCOPE_IDENTITY();
    261 
    262 -- Create the ontology base code
    263 
    264 INSERT INTO [i2b2ClinDataIntegration].[dbo].[OntologyCode] ([ParentID],[OntologyID],[Code],[Name],[OntologyNodeTypeCode],[OntologyNodeStatusCode],[Editable],[MetaDataTypeCode],[MetaUnits])
    265 VALUES (
    266                         NULL
    267            ,@OntologyID
    268            ,1
    269            ,@OntologyBaseNode
    270            ,'C'
    271            ,'A'
    272            ,0
    273            ,NULL
    274            ,NULL
    275         );
    276 
    277 -- Insert the base code into the Ontology table
    278 DECLARE @TCreateBaseInOntologyTable VARCHAR(MAX)
    279 SET @TCreateBaseInOntologyTable = '
    280    USE ' + @MetaDatabaseName + ';
    281    
    282    INSERT INTO  ' + @OntologyName + '
    283    SELECT [C_HLEVEL]
    284       ,[C_FULLNAME]
    285       ,[C_NAME]
    286       ,[C_SYNONYM_CD]
    287       ,[C_VISUALATTRIBUTES]
    288       ,[C_TOTALNUM]
    289       ,[C_BASECODE]
    290       ,[C_METADATAXML]
    291       ,[C_FACTTABLECOLUMN]
    292       ,[C_TABLENAME]
    293       ,[C_COLUMNNAME]
    294       ,[C_COLUMNDATATYPE]
    295       ,[C_OPERATOR]
    296       ,[C_DIMCODE]
    297       ,[C_COMMENT]
    298       ,[C_TOOLTIP]
    299       ,[UPDATE_DATE]
    300       ,[DOWNLOAD_DATE]
    301       ,[IMPORT_DATE]
    302       ,[SOURCESYSTEM_CD]
    303       ,[VALUETYPE_CD]
    304       ,''@'' [m_applied_path]
    305       ,NULL [m_exclusion_cd]
    306       ,NULL [C_PATH]
    307       ,NULL [C_SYMBOL]
    308   FROM [i2b2ClinDataIntegration].[dbo].[i2b2Ontology]
    309   WHERE OntologyID = ' + CONVERT(VARCHAR(10), @OntologyID);
    310    
    311 EXEC (@TCreateBaseInOntologyTable);
    312 
    313 -- Insert the base code into the Concept Table
    314 DECLARE @TCreateBaseConceptTable VARCHAR(MAX)
    315 SET @TCreateBaseConceptTable = '
    316    USE ' + @DataDatabaseName + ';
    317    
    318 INSERT INTO concept_dimension
    319 SELECT
    320       [concept_path]
    321       ,[concept_cd]
    322       ,[name_char]
    323       ,[concept_blob]
    324       ,[update_date]
    325       ,[download_date]
    326       ,[import_date]
    327       ,[sourcesystem_cd]
    328       ,[UPLOAD_ID]
    329   FROM [i2b2ClinDataIntegration].[dbo].[i2b2OntologyConceptCodes]
    330   WHERE OntologyID = ' +  + CONVERT(VARCHAR(10), @OntologyID);
    331    
    332 EXEC (@TCreateBaseConceptTable);
    333 
    334 ----------------------------------------------------------------
    335 ------- E N D   O F   T H E   A C T U A L   S C R I P T --------
    336 ----------------------------------------------------------------
    337 
    338 }}}
     71. Log into the server `UHLSQLBRICCSDB\UHLBRICCSDB` and database `i2b2ClinDataIntegration`
     82. Execute the stored procedure `[dbo].[CreateEmptyOntology](@OntologyName, @Prefix, @SourceSystem, @OntologyBaseNode, @OntologyDescription)`
     9 - Where:
     10  - `@OntologyName` is the name of the ontology and must not contain spaces, e.g., 'EMISPrimaryCareData'
     11  - `@Prefix` is the prefix for the concept codes and must not contain spaces, e.g., 'EMISPCD'
     12  - `@SourceSystem` is the system from which the data is being copied, e.g., 'Gem Risk Stratification'
     13  - `@OntologyBaseNode` is the name of the root node for the ontology, e.g., 'EMISPrimaryCareData'
     14  - `@OntologyDescription` is a short description of the ontology, e.g., 'EMIS Primary Care Data from GEM'
     153. Execute the stored procedure `[dbo].[CreateOntologyInStudy](@OntologyID, @MetaDatabaseName, @DataDatabaseName)` for each project that requires the ontology
     16 - Where:
     17  - `@OntologyID` is the ID returned by the previous stored procedure.
     18  - `@MetaDatabaseName` is the name of the i2b2 meta database where the ontology should be created, e.g., 'project_MetaData'
     19  - `@DataDatabaseName` is the name of the i2b2 data database where the ontology should be created, e.g., 'project_Data'
    33920
    34021[[BackLinks]]