i2b2 - Create Project

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. Change [ProjectName] to be the actual project name:

CREATE DATABASE [ProjectName]_MetaData
GO
CREATE DATABASE [ProjectName]_Data
GO
CREATE DATABASE [ProjectName]_WorkData
GO

CREATE LOGIN [ProjectName]_MetaData WITH PASSWORD = '[ProjectName]_MetaData';
GO
CREATE LOGIN [ProjectName]_Data WITH PASSWORD = '[ProjectName]_Data';
GO
CREATE LOGIN [ProjectName]_WorkData WITH PASSWORD = '[ProjectName]_WorkData';
GO

USE [ProjectName]_MetaData
CREATE USER [ProjectName]_MetaData FOR LOGIN [ProjectName]_MetaData
GO
EXEC sp_addrolemember N'db_owner', N'[ProjectName]_MetaData'
GO

USE [ProjectName]_Data
CREATE USER [ProjectName]_Data FOR LOGIN [ProjectName]_Data
GO
EXEC sp_addrolemember N'db_owner', N'[ProjectName]_Data'
GO

USE [ProjectName]_WorkData
CREATE USER [ProjectName]_WorkData FOR LOGIN [ProjectName]_WorkData
GO
EXEC sp_addrolemember N'db_owner', N'[ProjectName]_WorkData'
GO

1a. Create Bespoke Data Import Stored Procedures

Create the bespoke data import stored procedures by copying them from another project:

• INSERT_EID_MAP_FROMTEMP_RB
• INSERT_PATIENT_FROMTEMP_RB
• INSERT_PID_MAP_FROMTEMP_RB

2. Edit Project Creation Config and Scripts

1. 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.
2. 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

1. 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

1. 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. Create the ontology

Creating the ontology is beyond the scope of this page. However, if you want to create the minimum required to stop an error when clicking the top-level node of the ontology, run the following SQL script:

  INSERT INTO {MetaData database name from [1]}.dbo.{Ontology table name from [2]}
           ([C_HLEVEL]
           ,[C_FULLNAME]
           ,[C_NAME]
           ,[C_SYNONYM_CD]
           ,[C_VISUALATTRIBUTES]
           ,[C_TOTALNUM]
           ,[C_BASECODE]
           ,[C_METADATAXML]
           ,[C_FACTTABLECOLUMN]
           ,[C_TABLENAME]
           ,[C_COLUMNNAME]
           ,[C_COLUMNDATATYPE]
           ,[C_OPERATOR]
           ,[C_DIMCODE]
           ,[C_COMMENT]
           ,[C_TOOLTIP]
           ,[UPDATE_DATE]
           ,[DOWNLOAD_DATE]
           ,[IMPORT_DATE]
           ,[SOURCESYSTEM_CD]
           ,[VALUETYPE_CD])
     VALUES
           (0
           ,'{Ontology root node name from [4]}'
           ,'{Ontology description from [3]}'
           ,'N'
           ,'CA'
           ,NULL
           ,NULL
           ,NULL
           ,'concept_cd'
           ,'concept_dimension'
           ,'concept_path'
           ,'N'
           ,'LIKE'
           ,'{Ontology root node name from [4]}'
           ,NULL
           ,'{Ontology description from [3]}'
           ,GETDATE()
           ,NULL
           ,NULL
           ,'{A suitable source perhaps Ontology description from [3]}'
           ,NULL)
GO