Changes between Version 14 and Version 15 of i2b2 HowTo Create a Project


Ignore:
Timestamp:
08/15/17 15:09:50 (7 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • i2b2 HowTo Create a Project

    v14 v15  
    99=== 1. Create Databases
    1010
    11 Run the following SQL script found in the SVN repository [[https://svn.rcs.le.ac.uk/LCBRU/i2b2/i2b2/trunk/SqlScripts/]].
    12 
    13 Before running you will need to use Find and Replace to replace the strings
    14 1.  `{Study Name}` with the name of the study.
    15 2. `{Secure Password}` with a secure password
     11{{{
     12[i2b2ClinDataIntegration].[dbo].[CREATE_i2b2Project]
     13   '{study name}'
     14  ,'{password}'
     15GO
     16}}
    1617
    1718=== 2. Edit Project Creation Config and Scripts
    … …  
    5556  * JAVA_HOME
    5657
    57 iv. Edit the following SQL scripts in the following ways:
    58 
    59   * sql/sqlserver/meta/tables/create_sqlserver_metadata_tables.sql
    60    * Remove the creation of BIRN table.
    61    * Rename the ONYX table to something appropriate to the new project's ontology. [2]
    62 
    63   * sql/sqlserver/meta/inserts/schemes_insert_data.sql
    64    * Edit the values for C_KEY, C_NAME and C_DESCRIPTION [3]
    65 
    66   * sql/sqlserver/meta/inserts/table_access_insert_data.sql
    67    * Use the {ontology table name from [2]} for C_TABLE_CD and C_TABLE_NAME.
    68    * Give the ontology a root node name and use it for C_HLEVEL and C_DIMCODE. [4]
    69    * Use the {ontology description from [3] C_DESCRIPTION} for the C_FULLNAME and C_TOOLTIP.
    70 
    71   * sql/sqlserver/work/inserts/workplace_access_insert_data.sql
    72    * Change the C_TABLE_CD for both records to the project name.
    73 
    7458=== 3. Run the project creation scripts
    7559
    … …  
    8064  * bin/project-install/1-project-install.sh [Project Name]
    8165  * bin/project-install/2-update-datasources.sh [Project Name]
    82 
    83 === 4. Amend Tables
    84 
    85 {{{
    86 ALTER TABLE Observation_Fact
    87 ADD TEXT_SEARCH_INDEX INT NULL
    88 ;
    89 
    90 ALTER TABLE Patient_Dimension
    91 ADD INCOME_CD VARCHAR(50) NULL
    92 ;
    93 
    94 ALTER TABLE Visit_Dimension
    95 ADD LENGTH_OF_STAY INT NULL
    96 ;
    97 }}}
    98 
    99 === 5. Create Additional Indexes
    100 
    101 {{{
    102 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Encounter_Mapping]') AND name = N'ENCOUNTER_MAPPING_PK')
    103 ALTER TABLE [dbo].[Encounter_Mapping] DROP CONSTRAINT [ENCOUNTER_MAPPING_PK]
    104 GO
    105 
    106 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Encounter_Mapping]') AND name = N'IX_Encounter_Mapping')
    107 DROP INDEX [IX_Encounter_Mapping] ON [dbo].[Encounter_Mapping] WITH ( ONLINE = OFF )
    108 GO
    109 
    110 CREATE UNIQUE NONCLUSTERED INDEX [IX_Encounter_Mapping] ON [dbo].[Encounter_Mapping]
    111 (
    112         [ENCOUNTER_NUM] ASC,
    113         [ENCOUNTER_IDE_SOURCE] ASC
    114        
    115        
    116        
    117        
    118 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    119 GO
    120 
    121 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'IX_Patient_Mapping')
    122 DROP INDEX [IX_Patient_Mapping] ON [dbo].[Patient_Mapping] WITH ( ONLINE = OFF )
    123 GO
    124 
    125 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'PATIENT_MAPPING_PK')
    126 ALTER TABLE [dbo].[Patient_Mapping] DROP CONSTRAINT [PATIENT_MAPPING_PK]
    127 GO
    128 
    129 
    130 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'IX_Patient_Mapping_1')
    131 DROP INDEX [IX_Patient_Mapping_1] ON [dbo].[Patient_Mapping] WITH ( ONLINE = OFF )
    132 GO
    133 
    134 CREATE NONCLUSTERED INDEX [IX_Patient_Mapping_1] ON [dbo].[Patient_Mapping]
    135 (
    136         [PATIENT_IDE] ASC,
    137         [PATIENT_IDE_SOURCE] ASC
    138 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    139 GO
    140 
    141 
    142 IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Observation_Fact]') AND name = N'OBSERVATION_FACT_PK')
    143 ALTER TABLE [dbo].[Observation_Fact] DROP CONSTRAINT [OBSERVATION_FACT_PK]
    144 GO
    145 }}}
    14666
    14767=== 6. Update the ETL Reset i2b2 databases SQL Agent job