2 Installing the Database Tier

This chapter includes the following sections:

• Section 2.1, "Creating Default Schema Users"

• Section 2.2, "Deploying Mobile Clinical Research Associate Server Schemas (With Clinical Development Analytics Install)"

• Section 2.3, "Deploying Mobile CRA Server Schemas (Without Clinical Development Analytics Install)"

• Section 2.4, "Installing Seed Data"

• Section 2.5, "Deploying Adaptive Site-at-a-Glance Server Schemas"

2.1 Creating Default Schema Users

This section details the default schema users created by the script. For more details, see Section 2.2, Section 2.3, and Section 2.5.

Table 2-1 Database Schema

Schema	Recommended User Name
Mobile Clinical Research Associate (CRA) Server Rules Engine	MCRE_ADMIN
Mobile CRA Server Trip Report	MCTR_ADMIN
Mobile CRA Server	MCSV_ADMIN
Mobile CRA Server AQ	MCAQ_ADMIN
ASAAG Server	KPI_ADMIN

The script:

• Prompts to enter a password for each user

• Prompts for OCDA user name and password

• Assumes that schema user do not exist

Note:

The schema user names mentioned in the script are only for recommendation. However, you can modify the script and update schema users name as necessary.If you modify the user name, ensure to update the same in the MobileCRA_Server_Database\HSM_SchemaCreation.sql (for install without CDA installation) and MobileCRA_Server_Database\HSM_SchemaCreation_CDA.sql (for install with CDA installation) files.

2.2 Deploying Mobile Clinical Research Associate Server Schemas (With Clinical Development Analytics Install)

To deploy Mobile CRA Server schemas for customers with OCDA installation, perform the following steps:

1. Extract Mobile CRA_Server_Database.zip to a local folder.

2. Modify the value of the repeat_interval variable in the MobileCRA_Server_Database\adapter\cda\database\HSMRE_1.0_refresh_mvw.sql file to match the schedule of the Mobile CRA Server Rules Engine configured in Section 4.1.5, "Customizing the Configuration File".

   You need to schedule this interval before the Mobile CRA Server Rules Engine runs. By default, the FREQ=MINUTELY;INTERVAL value is set to 30.

3. Using SQL*Plus, log in to the Oracle 11gR2 database as SYS user.

   Note:

   Ensure that the SQL*Plus is started from the folder where files are downloaded.

4. Execute the following SQL script using SQL*:

   MobileCRA_Server_Database\HSM_SchemaCreation_CDA.sql

5. Enter the OCDA user name and password when prompted.

6. Enter the passwords for MCRE_ADMIN, MCSV_ADMIN, MCAQ_ADMIN, and MCTR_ADMIN schemas user when prompted.

The script creates database objects for MCRE_ADMIN, MCSV_ADMIN, MCAQ_ADMIN, and MCTR_ADMIN schemas.

Note:

Passwords will not echo on the screen.

2.3 Deploying Mobile CRA Server Schemas (Without Clinical Development Analytics Install)

To deploy Mobile CRA Server schemas for customers without OCDA installation, perform the following:

1. Extract Mobile CRA_Server_Database.zip to a local folder.

2. Using SQL*Plus, log in to the Oracle 11gR2 database as SYS user.

   Note:

   Ensure that the SQL*Plus is started from the folder where files are downloaded.

3. Execute the following SQL script using SQL*:

   MobileCRA_Server_Database\HSM_SchemaCreation.sql

4. Enter the passwords for MCSV_ADMIN, MCAQ_ADMIN, and MCTR_ADMIN schemas when prompted.

The script creates database objects for MCSV_ADMIN, MCAQ_ADMIN, and MCTR_ADMIN schemas.

Note:

Passwords will not echo on the screen.

2.4 Installing Seed Data

2.4.1 Trip Report Seed Data

To install trip report seed data, perform the following:

1. From the MobileCRA_Server_Database/mobile-admin/database folder, execute the TripReport_SeedData.sql by connecting through a SQL session to the Mobile CRA Server trip report schema.

   For example,

   CMD>sqlplus

   Enter user name and password when prompted, where user name is trip report schema user.

   SQLPlus>@TripReport_SeedData.sql

2. Review to ensure that there are no errors.

   If you want to spool the results, execute the spool command. For example,

   SQL> spool abc.log

2.4.2 XSL Seed Data

To install XSL seed data, perform the following:

1. Set up the necessary environment so that you can run the Oracle utilities such as SQLPlus, imp, and so on.

2. Grant IMP_FULL_DATABASE role to the MCTR_ADMIN user.

3. Import the XSL seed data for the out-of-the-box trip reports by using the Oracle imp utility provided in step 5.

4. While importing the XSL seed data, you need to connect to the Mobile CRA Server trip report schema by executing the following command:

   $>imp file=mobile-admin/database/mobilecra_xsl.dmp full=Y ignore=Y

   Enter user name and password when prompted, where user name is trip report schema user.

5. After importing, revoke the IMP_FULL_DATABASE role.

6. After importing the XSL seed data in Mobile CRA Server CTMS trip report schema, perform the following:

   1. Log in to the database using SQLPlus as MCTR_ADMIN schema user.

   2. Execute HSCTMS_1.0_ResetSeq_ddl.sql (that is, MobileCRA_Server_Database\mobile-admin\database\HSCTMS_1.0_ResetSeq_ddl.sql).

2.5 Deploying Adaptive Site-at-a-Glance Server Schemas

To deploy ASAAG Server schemas for customers, perform the following:

1. Extract ASAAG_Server_Database.zip to a local folder.

2. Using SQL*Plus, log in to the Oracle 11gR2 database as system user.

   Note:

   Ensure that the SQL*Plus is started from the folder where files are downloaded.

3. Execute the following SQL script using SQL*:

   HSOSS_Create_Schema_User.sql

4. Enter the passwords for KPI_ADMIN schemas when prompted.

   The script creates database objects for KPI_ADMIN schemas.

5. Edit the following value in the ASAAG_CONFIG.sql file before executing in the KPI_ADMIN schema:

   • NOTIFICATION_URL: HSPNS URL

6. Execute ASAAG_CONFIG.sql.

Note:

Passwords will not echo on the screen.