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. 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
• CREATE_TEMP_TABLE

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

Error: Macro BackLinks(None) failed

'Environment' object has no attribute 'get_db_cnx'