= i2b2 HowTo Create a Project

Tags: [[i2b2]] [[HowTo]]

This is the procedure to create a new project in i2b2.  Most of the process is carried out by Jeff's create project scripts, but there is some preparation work that could perhaps be incorporated into the scripts at some point.

== Procesdure

=== 1. Create Databases

Run the following SQL script found in the SVN repository [[https://svn.rcs.le.ac.uk/LCBRU/i2b2/i2b2/trunk/SqlScripts/]].

Before running you will need to use Find and Replace to replace the strings
1.  `{Study Name}` with the name of the study.
2. `{Secure Password}` with a secure password

=== 2. Edit Project Creation Config and Scripts

i. As the scripts have some hard coded elements it is necessary to take a copy of the procedures directory and change the hard coded values.  Therefore, copy the /usr/local/i2b2-procedures-1.1-production directory.
i. In the new procedures directory, amend the file config/config.properties.

The following values need amending:

  * project.name
  * crc.ds.jndi.name
  * ont.ds.jndi.name
  * work.ds.jndi.name
  * db.project.data.databasename
  * db.project.data.username
  * db.project.data.password
  * db.project.meta.databasename [1]
  * db.project.meta.username
  * db.project.meta.password
  * db.project.work.databasename
  * db.project.work.username
  * db.project.work.password
  * import.user
  * import.password
  * import.pmURL
  * import.domainID
  * import.projectID

iii. Edit the file config/defaults.sh.

The following values need amending:

  * CRC_DS
  * ONT_DS
  * WORK_DS

Also, ensure that the following variables contain the correct directories:

  * JBOSS_HOME
  * ANT_HOME
  * JAVA_HOME

iv. Edit the following SQL scripts in the following ways:

  * sql/sqlserver/meta/tables/create_sqlserver_metadata_tables.sql
   * Remove the creation of BIRN table.
   * Rename the ONYX table to something appropriate to the new project's ontology. [2]

  * sql/sqlserver/meta/inserts/schemes_insert_data.sql
   * Edit the values for C_KEY, C_NAME and C_DESCRIPTION [3]

  * sql/sqlserver/meta/inserts/table_access_insert_data.sql
   * Use the {ontology table name from [2]} for C_TABLE_CD and C_TABLE_NAME.
   * Give the ontology a root node name and use it for C_HLEVEL and C_DIMCODE. [4]
   * Use the {ontology description from [3] C_DESCRIPTION} for the C_FULLNAME and C_TOOLTIP.

  * sql/sqlserver/work/inserts/workplace_access_insert_data.sql
   * Change the C_TABLE_CD for both records to the project name.

=== 3. Run the project creation scripts

Whilst in the procedures directory do the following commands:

  * sudo su root (And enter your password)
  * export I2B2_PROCEDURES_HOME=$PWD
  * bin/project-install/1-project-install.sh [Project Name]
  * bin/project-install/2-update-datasources.sh [Project Name]

=== 4. Amend Tables

{{{
ALTER TABLE Observation_Fact
ADD TEXT_SEARCH_INDEX INT NULL
;

ALTER TABLE Patient_Dimension
ADD INCOME_CD VARCHAR(50) NULL
;

ALTER TABLE Visit_Dimension
ADD LENGTH_OF_STAY INT NULL
;
}}}

=== 5. Create Additional Indexes

{{{
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Encounter_Mapping]') AND name = N'ENCOUNTER_MAPPING_PK')
ALTER TABLE [dbo].[Encounter_Mapping] DROP CONSTRAINT [ENCOUNTER_MAPPING_PK]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Encounter_Mapping]') AND name = N'IX_Encounter_Mapping')
DROP INDEX [IX_Encounter_Mapping] ON [dbo].[Encounter_Mapping] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Encounter_Mapping] ON [dbo].[Encounter_Mapping] 
(
	[ENCOUNTER_NUM] ASC,
	[ENCOUNTER_IDE_SOURCE] ASC
	
	
	
	
)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]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'IX_Patient_Mapping')
DROP INDEX [IX_Patient_Mapping] ON [dbo].[Patient_Mapping] WITH ( ONLINE = OFF )
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'PATIENT_MAPPING_PK')
ALTER TABLE [dbo].[Patient_Mapping] DROP CONSTRAINT [PATIENT_MAPPING_PK]
GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Patient_Mapping]') AND name = N'IX_Patient_Mapping_1')
DROP INDEX [IX_Patient_Mapping_1] ON [dbo].[Patient_Mapping] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_Patient_Mapping_1] ON [dbo].[Patient_Mapping] 
(
	[PATIENT_IDE] ASC,
	[PATIENT_IDE_SOURCE] ASC
)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]
GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Observation_Fact]') AND name = N'OBSERVATION_FACT_PK')
ALTER TABLE [dbo].[Observation_Fact] DROP CONSTRAINT [OBSERVATION_FACT_PK]
GO
}}}

=== 6. Create the ontology

See: [[i2b2 HowTo Create an Ontology]]

[[BackLinks]]