2 Installing Oracle Health Sciences Clinical Development Analytics

This chapter describes the OHSCDA installation tasks that you must complete.

OHSCDA application installation consists of the following components:

Adding InForm Studies to the OHSCDA Warehouse
Running the OHSCDA Installer
Source-specific Procedures
Setting Up Oracle Data Integrator
Configuring Data Sources for OHSCDA
Executing the ETL Load Plans
Emplacing the OHSCDA Help and Image Files
Preparing the OBIEE Web Catalog and Repository for OHSCDA
Configuring Maps
Creating Security Groups and Application Roles for OHSCDA

2.1 Adding InForm Studies to the OHSCDA Warehouse

Note:

You must perform the tasks in this section only if:

You have InForm as a source
It is a fresh installation of OHSCDA

Figure 2-1 displays the OHSCDA InForm consolidation installation scenarios.

Figure 2-1 OHSCDA InForm Consolidation Installation Scenarios

Description of ''Figure 2-1 OHSCDA InForm Consolidation Installation Scenarios''

Note:

You can download the OHSCDA patch from My Oracle Support (https://support.oracle.com).

2.1.1 OHSCDA InForm Consolidation Schema

This section describes how OHSCDA extracts InForm's operational data and loads it into the OHSCDA warehouse. All of the data for each InForm study is maintained in a transactional database (actually a database schema). InForm maintains one or more InForm databases per Oracle database instance. Each study also has a Reporting and Analysis database (again, a set of objects in a schema, not necessarily an exclusive database instance) which contains data in views designed for query, as opposed to transactional activity.

InForm maintains the Reporting and Analysis database for each study in a study-specific schema. For efficiency and maintainability, OHSCDA has created a program that extracts data from a number of InForm Reporting and Analysis databases and places that data into a single consolidated database. Then, OHSCDA's source-dependent extract code reads from the consolidated database, and writes to the OHSCDA warehouse.

For the remainder of this section, refer to any given InForm Reporting and Analysis database as a source schema and the schema in which OHSCDA consolidates InForm data as the consolidation schema.

OHSCDA consolidates data from selected InForm source schema views, the selected ones being the ones that have data corresponding to columns in the OHSCDA data model. The following are the InForm source views that are consolidated for OHSCDA:

IRV_AF_SUBJECT_FORMS
IRV_CUR_QUERY
IRV_CUR_RULE
IRV_CUR_SITE
IRV_CUR_SPONSOR
IRV_CUR_SUBJECT
IRV_CUR_USER
IRV_FORM_REVS
IRV_ITEMSET_REVS
IRV_ITEM_REVS
IRV_QUERY_STATE_CHANGES
IRV_SECTION_REVS
IRV_STUDYVERSIONS
IRV_STUDYVERSION_FORMS
IRV_STUDYVERSION_VISITS
IRV_SUBJECT_STATE_CHANGES
IRV_SV_SUBJECTVISITS
IRV_USERS_SITES

For each of these source views, the consolidation schema contains a consolidation table. Each consolidation table has the same columns as its source view, except that the consolidation table has three additional columns:

STUDYGUID
CONSOLIDATION_DATETIME
MODIFIEDDATETIME

STUDYGUID is needed to retain the association of each record with its InForm study. CONSOLIDATION_DATETIME and MODIFIEDDATETIME allows OHSCDA ETL to detect which consolidated records have been added or updated since the prior ETL execution.

The names of tables in the consolidation schema match the names of their source views, with the following change:

IRV in the source view name is replaced with RXI in the consolidation table name.

Consolidation is the process of getting new or modified records from a source schema and updating the consolidation schema with those records. Consolidation is carried out by periodic execution of a scheduled job in each InForm schema that contributes to the consolidation schema. Execution is done through the DBMS_JOB facility. Each source schema is consolidated on its own schedule.

There is no master job that oversees or coordinates the consolidation of all source InForm schemas. You can choose to consolidate different InForm schemas at different times, and with different intervals between consolidations. However, the more spread out consolidations are, the wider the time period represented by the records in the consolidation schema. So it is recommended that you schedule consolidation jobs to fall within a small time window. For details on scheduling, see Section 2.1.5, "Adjusting Scheduling of Consolidation Jobs".

When the Consolidation job executes for a particular source schema, it does the following:

For each source table from which OHSCDA extracts data,

Identify what records have been created or modified in this table since the previous consolidation
For each modified source record, drop the corresponding record in the consolidation table
Insert all new and modified records in the Consolidation schema table, setting their StudyGUID, and modification timestamps

The procedures that does this work reside in each InForm Reporting and Analysis Schema, they are placed there as part of the process of preparing that schema for consolidation.

Preparation for consolidation also creates an access account in the database holding the source schema. This account is the one that reads the Reporting views, and writes to the consolidation tables. This access account is named CDAxxx, where xxx is the Study name.

All movement of data is done by a pull from the Reporting and Analysis Database to the Consolidation database. This ensures that there is no path by which someone who can connect to the consolidation schema is able to modify the InForm database.

The instructions that follow tell how to do the initial setup of the consolidation schema (this also enrolls the first Study to be consolidated as part of the process) and then how to set up each additional InForm study so that it becomes a contributor to the Consolidation schema.

2.1.2 Prerequisites

Download the OHSCDA patch (as required) from My Oracle Support (https://support.oracle.com). For information on the required patch, see Figure 2-1.
Unzip the downloaded patch.
Copy and unzip the Inform_Consolidate.zip file to a server from which it is possible to access all InForm source database that contain studies that are to be consolidated and also to access the database in which the consolidation schema is located.

2.1.3 Setting Up Consolidation Schema

This section describes the steps to create the Consolidation Schema. As part of the process, it enrolls an initial InForm study as a source to be consolidated.

Note:

If you are using the OHSCDA 3.0.0.2 patch, you must set up both the source trial and destination consolidation databases.
If you are using the OHSCDA 3.0.0.4 patch, you must only set up the destination consolidation database.

2.1.3.1 Preparing TNSNAMES Files on Source Trial and Destination Consolidation Database Servers

If one does not already exist, create a TNSNAMES.ORA entry for the Consolidation database instance on each InForm database server that contains a study that is to be consolidated.

Note:

This is not essential. If you prefer not to create the TNSNAMES entries, you can provide the complete connection string instead of the TNS name when specifying remote databases below.

On the destination consolidation schema database server, add a TNSNAMES.ORA entry for each source schema database.

2.1.3.2 Creating a Consolidation Management Root Directory

On the destination consolidation schema database server, create a root directory for managing the files needed to configure and log consolidation of the source schemas.

We will refer to this directory as <$ConsolidationMgmtRoot>.

If source trial and destination consolidation databases are in the same network, then a single <$ConsolidationMgmtRoot> is sufficient. Else, replicate the directory in both the servers.

During the setup of the source trial database, use TrialCDA and Logs folders. And, during the setup of the destination consolidation database, use DestCDA and Logs folder.

2.1.4 Initiating Consolidation of InForm Studies

This section describes the steps you must follow to prepare a source schema for consolidation and perform the initial execution of consolidation of that schema. Subsequent consolidations occurs automatically at an interval you define as part of the preparation described here.

The setup and configuration is performed in the following two phases for each study trial:

Section 2.1.4.1, "Source Trial Database Configuration"
Section 2.1.4.2, "Destination Consolidation Database Configuration"

2.1.4.1 Source Trial Database Configuration

Follow the instructions in this section for each InForm study that is to be consolidated for consumption by OHSCDA.

On either source trial or destination consolidation schema database server, based on the network architecture as described in Section 2.1.3.2, "Creating a Consolidation Management Root Directory", create the <Study-name> folder within <$ConsolidationMgmtRoot>/ to hold the files for consolidation of this study.
Copy the following folders from Inform_Consolidate.zip into <$ConsolidationMgmtRoot>/<Study-name>/:
- TrialCDA
- Logs
Set your current directory to the <$ConsolidationMgmtRoot>/<Study-name>/TrialCDA/ directory.

Edit CONFIGTRIALCDA.sql to set specific values of configuration parameters for this study. Make the following changes:

Table 2-1 Source Trial Configuration Parameters

Parameter	Set value to
Slash	Use \ for Windows or / for Linux
trial_name	The name of the study.
inform_release	For InForm 4.6, enter R46. For later releases, enter R55.
trial_tnsname	Name in the local TNSNAMES file of the source trial database for the study. Can also supply entire TNS entry for the source trial database as a string.
trial_schema_user	Username of the InForm source schema.
trial_schema_password	Password of the InForm source schema.
trial_ro_user	This is OHSCDA reporting object schema user name and is defaulted to trail_name prefixed with 'cda'.
trial_ro_password	This is OHSCDA reporting object schema password and is defaulted to trail_name prefixed with 'cda'. You can change this password.
DDS_SCHEMA_OWNER	This is OHSCDA reporting object schema user name. This value must be same as the value you have entered for trial_ro_user.
trial_dba_user	Database username who has the dba privileges.
trial_dba_password	Password for database username.
trial_ts	Name of a tablespace in the source schema database to which the access account for this study will be given access.
trial_ts_temp	Name of a temporary tablespace in the source schema database to which the access account for this study will be given access.

Save the file.
Connect to SQLPlus. Execute the following command:

sqlplus /nolog
From the command prompt, execute the following command:

@Setup_Trial_CDA.sql
Navigate to the <$ConsolidationMgmtRoot>/<Study-name>/Logs directory to review the log.

If any errors are found, make necessary corrections and re-execute Setup_Trial_CDA.sql.

2.1.4.2 Destination Consolidation Database Configuration

Follow the instructions in this section for each InForm study that is to be consolidated for consumption by OHSCDA.

On the consolidation schema database server, based on the network architecture as described in Section 2.1.3.2, "Creating a Consolidation Management Root Directory", create the <Study-name> folder within <$ConsolidationMgmtRoot>/ if the destination consolidation database is not in the same network as the source trial database.

The <Study-name> folder holds the files for consolidation of this study.
Copy the following folders from Inform_Consolidate.zip into <$ConsolidationMgmtRoot>/<Study-name>/:

Note:
Do not copy the Logs folder if the destination consolidation database is in the same network as the source trial database.
- DestCDA
- Logs
Set your current directory to the <$ConsolidationMgmtRoot>/<Study-name>/DestCDA directory.

Edit CONFIGDESTCDA.sql to set specific values of configuration parameters for this study. Make the following changes:

Note:

If you prefer not to create the TNSNAMES entries, you can provide the complete connection string instead of the TNS name when specifying the remote databases below.

The trial parameters mentioned in Table 2-2 must match with the ones defined in Table 2-1.

Table 2-2 Destination Configuration Parameters

Parameter	Set value to
Slash	Use \ for Windows or / for Linux
Parameters for configuring the source schema and database
trial_name	The name of the study.
inform_release	For InForm 4.6, enter R46. For later releases, enter R55.
trial_tnsname	Name in the local TNSNAMES file of the source trial database for the study. Can also supply entire TNS entry for the source trial database as a string.
trial_schema_user	Username of the InForm source schema.
trial_global_name	Global name of the source schema database. You can get this by connecting to the source schema and running the following query: SELECT * FROM GLOBAL_NAME
trial_ro_user	This is OHSCDA reporting object schema user name and is defaulted to trail_name prefixed with 'cda'.
trial_ro_password	This is OHSCDA reporting object schema password and is defaulted to trail_name prefixed with 'cda'. You can change this password.
Parameters for configuring the destination schema and database
dest_tnsname	Name of the destination database in the TNSNAMES file on the source database server for the study. Can also supply entire TNS entry for the destination database as a string.
dest_schema_owner	This defaults to and is same as trial_ro_user parameter.
dest_schema_password	This defaults to and is same trial_ro_password parameter.
dest_consolidated_user	This is the consolidation schema username. Default is RXI_INFORM.
dest_consolidated_password	This is the consolidation schema password. Defaults is RXI_INFORM. You can change this password.
dest_dba_user	Database username who has dba privileges on the consolidation database.
dest_dba_password	Password for the database username on the consolidation database.
dest_ts	Name of a tablespace in the destination schema database to which the consolidation and access account will be given access.
dest_ts_temp	Name of a temporary tablespace in the destination schema database to which the consolidation and access account will be given access.
consolidate_job_interval	The interval between executions of the program to consolidate new and modified data from this study. A value of 1 results in an interval of one day. A value of 1/24 results in an interval of one hour.

Save the file.
Connect to SQLPlus. Execute the following command:

sqlplus /nolog
From the command prompt, execute the following command:

@Setup_Dest_CDA.sql
Navigate to the <$ConsolidationMgmtRoot>/<Study-name>/Logs directory to review the log.

If any errors are found, make necessary corrections and re-execute Setup_Dest_CDA.sql.

2.1.5 Adjusting Scheduling of Consolidation Jobs

As mentioned earlier, initial consolidations, especially of large source schemas, should be done in isolation to avoid contention for resources. But once the initial consolidation of a schema is done, subsequent consolidations of that schema are incremental and involve smaller numbers of records (incremental consolidations are limited to new or modified Subjects, CRFs, and Queries accumulated since the previous consolidation).

Once initial consolidation of a InForm trial source schema is complete, it is useful to adjust the scheduling of its incremental consolidations so that they start near to the time of other incremental consolidation of other source schemas. The closer in time consolidations start across studies, the smaller the time period represented in the consolidation schema.

To adjust the time when a study's consolidation job executes, perform the following steps:

Connect as the InForm trial source schema owner.

Determine the consolidation job ID using the following query for InForm 4.6:

select job
from user_jobs
where what like 'REFRESH_PROCS.REFRESH_WITH_STREAMS_DELAY%';

or the following query for InForm 5.5, 6.0, and 6.1:

select job
from user_jobs
where what = 'CDA_PROCS.CONSOLIDATE';

Change the time for the next execution of the job by using the dbms_job.next_date procedure.

Note:
The next_date parameter is an Oracle date-time in which you can specify both the date and time for the next execution.

Remember to follow the call with a commit. For example,
```
begin
dbms_job.next_date (
  job => 12345,
  next_date => to_date('21-AUG-2013@01:01:00','DD-MON-YYYY@HH24:MI:SS')
);
end;
commit;
/
```
This causes job 12345 to next be executed at 1:01 AM on 21-AUG-2013.

You can also change the interval between subsequent executions of the job using the dbms_job.interval procedure. Set the interval parameter to the string 'SYSDATE + <desired-interval>', replacing <desired-interval> with the number of days, or the fraction of a day to elapse before the next execution.

begin
dbms_job.interval(
  job => 12345,
  interval => 'SYSDATE + 1/2'
);
end;
commit;
/

This sets the interval between executions of job 12345 to half a day or every 12 hours.

2.2 Running the OHSCDA Installer

The basic OHSCDA components are installed using the Oracle Universal Installer. The installer gathers all the information about the database connectivity and data mart by presenting a sequence of prompt screens, and then installs the components accordingly.

Important:

Make sure that the Oracle Database client is available on the same system where you plan to execute the installer.

Note:

This note is applicable if you have Oracle Clinical 5.1 as a source.

Oracle Clinical (OC) installer includes a script that can configure OC such that when you log in as <schema_username>, you are able to access tables from other schema.

If you are not able to access tables from other schema when logged in as <schema_username>, then you must search the OC installer for the script and execute it with <schema_username> login.

For OC 5.1, the script is named as CreateDropPublicSynonym and you can execute it using the following command:

exec opa_ddl.CreateDropPublicSynonym('PROGRAM_SUBSTANCES','PROGRAM_SUBSTANCES');

For any other OC version or if you do not find the script in the OC installer, contact the support team.

Perform the following steps to install the OHSCDA application on Windows:

Extract the contents of the media pack into a temporary directory.
Navigate to the \install directory under the extracted media pack folder.
Double-click the setup.exe file.

The Oracle Universal Installer: Welcome screen appears.

The Welcome screen provides information about the Oracle Universal Installer. The following function buttons appear on the installation screens:
- Deinstall Products: To view and deinstall the components already installed on the system. This button appears only on the Welcome screen.
- About Oracle Universal Installer: To view the version number of the installer you are using.
- Help: To access detailed information about the functionality of each screen.
- Installed Products: To view components already installed on the system. You can deinstall a component, if necessary.
- Next: To proceed to the next screen.
- Cancel: To cancel the installation process and exit the installer.
Click Next.

The Select a Product to Install screen appears.
Perform the following steps:
1. Set up the OHSCDA data warehouse. For information on how to do so, see Section 2.2.1, "Setting Up the Data Warehouse".
2. Set up the source system for OHSCDA. For information on how to do so, see Section 2.2.2, "Setting Up the Source System".

2.2.1 Setting Up the Data Warehouse

Select the Oracle Health Sciences Clinical Development Analytics Standard 3.2.0.0.0 option to setup the OHSCDA data warehouse.
Click Next.

The Specify Home Details screen appears.
Enter the name of the installation and path where all the staged files from the Installer get copied to the local system. This is also the location from where installer executes the database script.

Example Name: OCDA_HOME

Path: <Drive>:\OCDA_HOME
Click Next.

The Install or Upgrade OHSCDA screen appears.
Select one of the following options:
- Install: If you want to make a fresh installation of OHSCDA.
- Upgrade: If you want to upgrade an existing OHSCDA 3.1.1 instance.
Click Next.

The OHSCDA ODI Data Warehouse Details screen appears.

This screen collects all the information regarding the OHSCDA ODI data warehouse.
Enter values in the following fields:
- Data Warehouse Connect String: Connection of the database where OHSCDA Data Warehouse will be deployed.
  
  Note:
  Make sure the connect string matches the value in the tnsnames.ora file.
- Database Host Name
- Database Port No
- Database Service Name
- Default Tablespace Name
- Temporary Tablespace Name
- Data Warehouse Schema Name
Click Next.

The OHSCDA ODI Data Warehouse screen appears.
Enter and confirm the system password of the database where OHSCDA ODI Data Warehouse will be deployed.
Click Next.

The OHSCDA ODI Data Warehouse Schema Details screen appears.
Enter and confirm the OHSCDA ODI Data Warehouse schema password.
Click Next.

The OHSCDA ODI Data Warehouse RPD Details screen appears.
Enter values in the following fields:
- DW RPD Schema Name
- DW RPD Default Tablespace Name
- DW RPD Temporary Tablespace Name
Click Next.

The OHSCDA ODI Data Warehouse RPD Schema Details screen appears.
Enter and confirm the OHSCDA ODI Data Warehouse RPD schema password.
Click Next.

The OHSCDA ODI Data Warehouse Work Details screen appears.
Enter values in the following fields:
- Data Warehouse Work Schema Name
- Default Tablespace Name
- Temporary Tablespace Name
Click Next.

The OHSCDA ODI Data Warehouse Work screen appears.
Enter and confirm the OHSCDA ODI Data Warehouse work password for OHSCDA database.
Click Next.

The National Language Support screen appears.
Select one the following options:
- Yes: If the source data contains data in English and any other language.
- No: If the source data contains data only in English.
Click Next.

The Do you wish to install OCDA Source System? screen appears.
Perform one of the following:
- If you want to set up source systems, select the Yes option and click Next.
  
  The ODI Sources screen appears. Perform steps through step 7 in Section 2.2.2, "Setting Up the Source System".
- Else, select the No option and click Next.
  
  The Summary screen appears.
Click Install.

Note:
You must record the log file path that appears on the bottom of the screen for future reference.

The End of Installation screen appears.
Click Exit.

2.2.2 Setting Up the Source System

Select the ODI Source System 3.2.0.0.0 option to setup the source system for OHSCDA.
Click Next.

The Specify Home Details screen appears.
Enter the name of the installation and path where all the staged files from the Installer get copied to the local system. This is also the location from where Installer executes the database script.

Example Name: OCDA_HOME

Path: <Drive>:\OCDA_HOME
Click Next.

The ODI Sources screen appears.
Select the Yes option.

Note:
The No option is for internal purpose.
Click Next.

The Install or Upgrade screen appears.
Select one of the following options:
- Install: If you want to make a fresh installation of source system for OHSCDA.
- Upgrade: If you want to upgrade existing source systems for OHSCDA from 3.1.1 instance.
The Select the Source System to install screen appears.
Click Next.
Select one or multiple source system from the following list:
- Siebel Clinical
- Oracle Clinical
- InForm
Note:
Hold CTRL to select more than one source system.
Click Next.

The selected source systems appear.
Click OK.

Note:
Depending on the selected source system, the installer displays the required screens. For example, if you select Oracle Clinical as the source system, the Oracle Clinical Source System Details screen appears.
Enter values in the following fields:
- Connect String for OC Source Server
- OC Source Server Host Name
- OC Source Server Port No
- OC Source Server Service Name
- Oracle Clinical Source Schema Name
Click Next.
Enter and confirm the system password of the database where Oracle Clinical source system is deployed.
Click Next.
Enter and confirm the Oracle Clinical source schema password.
Click Next.

The OCDA Oracle Clinical Source System Details screen appears.
Enter values in the following fields:
- OCDA OC Source Schema Name: This schema has the read-only grant on the required source tables.
- OCDA OC Default Tablespace Name
- OCDA OC Temporary Tablespace Name
Click Next.

The Oracle Clinical Source System Details screen appears.
Enter and confirm the OCDA Oracle Clinical source schema password.
Click Next.

The OHSCDA Oracle Clinical Work Schema Details screen appears.
Enter values in the following fields:
- OCDA OC Work Schema Name
- OCDA OC Work Default Tablespace Name
- OCDA OC Work Temporary Tablespace Name
Click Next.
Enter and confirm the OHSCDA Oracle Clinical work schema password.
Click Next.
Click Install.

Note:
Similar set of screens are displayed for other selected source systems. Perform steps through 12 for Siebel Clinical and/or InForm source systems.

2.3 Source-specific Procedures

This section describes procedures you may, or must, perform for certain OHSCDA sources. Please review each section and determine if it applies to the source(s) you are using for OHSCDA. If the section does apply, carry out the procedure. If the section is optional, decide whether it is appropriate for your requirements and act accordingly.

2.3.1 Handling Deletions of Source Records

Different source applications handle the deletion of records in different ways. Oracle Clinical allows deletions of records in a way that avoids orphans, and has built-in auditing of these deletions. Siebel Clinical also allows deletions, properly cascading to maintain referential integrity, but does not have built-in auditing of deletions. InForm does not provide any means to hard-delete records in its transactional database. So, it does not need to be concerned neither with maintaining integrity in the face of deletions nor with auditing deletions.

2.3.1.1 Handling Deletions in Oracle Clinical

OHSCDA's ETL consults the audit trails to determine if source records have been deleted, and then soft-deletes those records in the warehouse.

2.3.1.2 Handling Deletions in Siebel Clinical

OHSCDA provides a feature to manage hard deletion of records in Siebel Clinical. Create triggers in the source system to handle deletion of records.

Navigate to the following temporary staging location where the OHSCDA installer copies the installation files:

<OCDA_HOME>\OCDA_Common\OCDA_Common_Scripts\Delete_Script
Connect to the Siebel Clinical schema and run the ocda_sc_del_trigger.sql script delivered with OHSCDA. This script creates the triggers on tables provided as input. The following are the tables in Siebel Clinical for which OHSCDA supports creating triggers:
- S_CL_PTCL_LS
- S_PROD_INT
- S_CL_SUBJ_LS
- S_CONTACT
- S_CL_PGM_LS
- S_PTCL_SITE_LS
- S_EVT_ACT
- S_ORG_EXT
- S_PTCL_VER_LS
- S_ASSESS
Provide a list of comma separated values of table names for which the triggers need to be created as the script's input. For example, S_CL_PTCL_LS, S_PROD_INT, and S_CL_SUBJ_LS. The tables names that you provide can only be a subset of the tables listed above.

Note:
When you delete a record in the table, the primary key of the deleted record is inserted in the RXI_DELETE_LOG_S table on the Siebel source system.
Set the values of the OHSCDA.DELETE_FLOW and DELETE_FLOW variables to Y while executing the load plan in ODI.
Execute the ETLs. For information, see Oracle Clinical Development Analytics Administrator's Guide.

The Siebel Clinical related SDE mappings read the above instance of the RXI_DELETE_LOG_S table.

Note:

Records that are deleted in the source system are soft deleted in the data warehouse.

2.3.1.3 Handling Deletions in InForm

Since no deletions are supported through the InForm UI, OHSCDA makes no provision for detecting them. If you need to delete records from an InForm transactional table at the backend and want the OHSCDA warehouse to be consistent with that change, you must identify the warehouse records that arose from the InForm records that are to be deleted. Also, you must remove them from the consolidation schema and the warehouse.

2.3.2 (Optional) Handling Deletion or Deactivation of a Study

Depending on how you use OHSCDA, you may prefer to have reports that give information only on currently active studies. If so, and if source studies are completed or possibly deleted, the recommended approach for removing such a study from OHSCDA is to use OHSCDA's access control capability. That is, enable Study and Study-site access controls, and then exclude the study and its sites from the list of studies (and sites) available to OHSCDA users. Aside from being simpler than identifying and deleting records from the warehouse, this approach has the advantage of being reversible if the decision to remove the study from the warehouse is reconsidered.

If you do not wish to use access control to avoid display of deactivated or deleted studies, there is an alternative solution. In the OHSCDA warehouse, every dimension table, and every base fact table, has a column named DELETE_FLG. OHSCDA's OBIEE repository is designed to treat records where DELETE_FLG equals 'Y' as if they were not present in the warehouse. OHSCDA's SIL also disregards records with DELETE_FLG = 'Y' when calculating values for aggregate tables (those with names ending with _A). So you can cause records to be effectively removed from the warehouse by setting their DELETE_FLG to 'Y'. Thus, if a study has been removed from a source database, you could identify all dimension and fact records for that study, and set their DELETE_FLG to 'Y'. This causes them to be forever invisible to OHSCDA's analytics.

This solution could be used, but only with great caution, for a study that is considered to be deactivated, but still remains in the source. Once marked as deleted, warehouse records for a study will not get their DELETE_FLG set back to 'N', but OHSCDA creates new warehouse records if there are changes or additions in the source for that study. So, if the study is truly inactive in the source, soft-deleting warehouse records for the study by setting DELETE_FLG to 'Y' will work. But any changes or additions to the study would cause new records to be loaded into the warehouse for the study, and it would re-appear in analyses.

2.3.3 (Optional) Fine-tuning of Oracle Clinical Settings

To optimize source system integration with OHSCDA, perform the following steps:

Create the following indexes:
- Function based index on DISCREPANCY_ENTRIES - NVL ("MODIFICATION_TS", "CREATION_TS")
- Function based index on DISCREPANCY_ENTRY_REVIEW_HIST - NVL ("NEXT_STATUS_TS", "CREATION_TS")
- Function based index on RECEIVED_DCIS - NVL ("MODIFICATION_TS", "RECEIVED_DCI_ENTRY_TS")
- Function based index on RECEIVED_DCMS - NVL ("MODIFICATION_TS", "RECEIVED_DCM_ENTRY_TS")

2.4 Setting Up Oracle Data Integrator

Create Master and Work repository in ODI.
Set up Java EE Agent

Note:
For information, see Oracle Data Integrator Installation Guide.
Launch ODI Studio from the system where OHSCDA is installed and connect to work repository.
Note:
Before importing the topology, make sure the Global context is not defined.
If the Global context is defined, perform the following steps to delete the Global context:
1. Click the Topology tab.
2. Under the Context section, right-click Global and select Delete.
  
  The Confirmation dialog box appears.
3. Click Yes.
Click the Designer tab.
Select Import from the Designer Menu drop-down list.

The Import Selection dialog box appears.
Select Import the Topology.
Click OK.

The Import Topology dialog box appears.
Select Synonym mode INSERT_UPDATE from the Import Mode drop-down list.
Select the Import From a Zip File option.
Select the OCDA_Topology.zip file in the OCDA home directory. For example, <Drive>:\<OCDA_HOME>\OCDA_ODI\ODI_Code\OCDA_Topology.zip.
Click Open.
Click OK.

Note:
After the import process is complete, you can save the log file for future reference.
Click Close.
Select Import from the Designer Menu drop-down list.

The Import Selection dialog box appears.
Select Import the Work Repository.
Click OK.

The Import Work Repository dialog box appears.
Select Synonym mode INSERT_UPDATE from the Import Mode drop-down list.
Select the Import From a Zip File option.
Select the OCDA_Work_Repository.zip file in the OCDA home directory. For example, <Drive>:\<OCDA_HOME>\OCDA_ODI\ODI_Code\OCDA_Work_Repository.zip.
Click Open.
Click OK.

Note:
After the import process is complete, you can save the log file for future reference.
Click Close.
Click the Topology tab.
Under the Physical Architecture section, expand Agents.
Double-click the OracleDIAgent physical agent.

The OracleDIAgent tab opens.
Enter the host name and port number to point to an agent of current environment and save the changes.
Click Test to test the changes.

The OracleDIAgent Test Successful dialog box must appear.
Click OK.
Make changes to the physical data server connections to point to the required source and target schemas. Perform the following steps to do so:
1. Click the Topology tab.
2. Expand Physical Architecture.
3. Navigate to Technologies > Oracle.
4. Right-click and open the data server DS_OCDA_DWH.
5. In Definition, change Instance/dblink (Data Server) to point to the OHSCDA DWH work schema.
6. In Connection, change User and Password to the user and password which were specified for the DWH work schema while installation.
7. In JDBC, select the Oracle JDBC Driver as the JDBC Driver and change JDBC URL to point to the DWH work schema.
8. Expand the data server DS_OCDA_DWH.
9. Right-click the DS_OCDA_DWH.<CDA_DWH_SCHEMA> physical schema and select Open.
10. In Definition, update Schema (Schema) to the OHSCDA DWH schema and Schema (Work Schema) to the work schema name.
11. Save the configuration.
12. Steps m through t are to update Oracle Clinical source connection. Similarly, update source connections for other required sources (Siebel Clinical and InForm).
13. Right-click and open the data server DS_OCDA_OracleClinical.
14. In Definition, change Instance/dblink (Data Server) to point to the Oracle Clinical work schema.
15. In Connection, change User and Password to the user and password which were specified for the Oracle Clinical work schema while installation.
16. In JDBC, select the Oracle JDBC Driver as the JDBC Driver and change JDBC URL to point to the Oracle Clinical work schema.
17. Expand the data server DS_OCDA_OracleClinical.
18. Right-click the DS_OCDA_OracleClinical.<OCDA_OC_SRC_SCHEMA> physical schema and select Open.
19. In Definition, update Schema (Schema) to the OHSCDA Oracle Clinical source schema and Schema (Work Schema) to the work schema name.
20. Save the configuration.
  Note:
  If InForm is your source and Auto merge is enabled:
  1. Expand the data server DS_OCDA_InForm.
  2. Right-click the DS_OCDA_InForm.<INFORM_CONSOLICATION_SCHEMA> physical schema and select Open.
  3. In Definition, update Schema (Schema) to the InForm consolidation schema and Schema (Work Schema) to the work schema name.
Log on to the WebLogic console where ODI Agent is configured.
Expand Services and click Data Sources.
Under the Configuration tab, click odiMasterRepository to open.
Click the Connection Pool tab
Set value for the following properties:
- Initial Capacity =100
- Maximum Capacity=3600
- Minimum Capacity =100
Click Save.

Note:
Perform the same changes to the odiWorkRepository datasource.

2.5 Configuring Data Sources for OHSCDA

Note:

By default, Oracle supports data source number 1, 2, and 3 for Oracle Clinical, Siebel Clinical, and InForm respectively.

Table 2-3 Sample of Data Source Table W_RXI_DATASOURCE_S

ROW_WID	DATASOURCE_NUM_ID	DATASOURCE_NAME	DELETE_FLG	INTEGRATION_ID	INFORM_STUDY_GUID
1	1	ORACLE_CLINICAL	N	ORACLE_CLINICAL	-
2	2	SIEBEL_CLINICAL	N	SIEBEL_CLINICAL	-
3	3	INFORM	N	INFORM	{FA5BCD87-B858-4F08-8BF8-1914B61C9DDF}
4	101	INFORM_1	N	INFORM_1	{CC0DBD73-FA40-4943-BDB3-60752C1A2732}

Note:

If InForm is one of your sources and auto merge feature is not used, then the column INFORM_STUDY_GUID must be populated with studyguid for the corresponding InForm study.

You can find the distinct studyguid from table RXI_STUDYVERSIONS in the consolidation database.

If auto merge is set to Y, then a single entry for data source 3 for INFORM is sufficient and INFORM_STUDY_GUID need not be populated. In this case, the consolidation database is considered as single source and duplicates are handled by auto merge.

For more information, see Oracle Health Sciences Clinical Development Analytics Administrator's Guide.

2.6 Executing the ETL Load Plans

Execute the following ETL load plans (as required) which are shipped with OHSCDA:

CDA - Complete Warehouse with Dedup: This is used to load warehouse with one or all of the three source systems (Oracle Clinical, Siebel Clinical, and InForm).
CDA- Inform Automerge: This is used to load warehouse only from Inform data source and only when automerge feature is needed.

For information on how to execute the load plan, see Oracle Health Sciences Clinical Development Analytics Administrator's Guide.

2.7 Emplacing the OHSCDA Help and Image Files

You will be using help.zip and images.zip files in this section.

2.7.1 Placement of Files for Oracle WebLogic Managed Server

You need to manually deploy OHSCDA's help and images files on Oracle WebLogic Managed Server. Perform the following steps:

Navigate to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\ in Oracle WebLogic Server and create the s_ocda folder.
Copy the help.zip file from <OCDA_HOME>\OCDA_Common\Reporting\Help to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda\.
Unzip the help.zip file.
Move customMessages and sk_ocda folders from <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda\help\ to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\.
Replace the tags within the UI tag with the following tags in instanceconfig.xml, present in <MIDDLEWARE_HOME>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1:
```
<DefaultSkin>ocda</DefaultSkin>
<DefaultStyle>ocda</DefaultStyle>
```
Copy the Images.zip file from <OCDA_HOME>\OCDA_Common\Reporting\Images to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda.
Unzip the Images.zip file.
Launch Oracle WebLogic Administration Server Console. The following shows the format of the URL:

https://<hostname>.<domain>:<port>/console

The Oracle WebLogic Server Administration Console screen appears.
Log on to Oracle WebLogic Server Administration Console as an Administrator.
In the left pane of the Administration Console, select Deployments.
On the left pane, click Lock & Edit.
In the right pane, click Install.

This opens the Install Application Assistant.
In the Path field browse to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1.
Select analyticsRes and click Next.
Select Install this deployment as an application and click Next.
In Available targets for analyticsRes, under Clusters, select bi servers on which you want to deploy OHSCDA.
Click Next.
Select I will make the deployment accessible from the following location option for <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\ in the Source accessibility section.
Click Finish.

analyticsRes appears in the Deployment section.
Click Activate Changes.
Select analyticsRes in the Deployment section.
Click Start to view the list and select Servicing all requests. The Start Application Assistant page is displayed.
In the content pane of the new page, click Yes to start the selected deployment.

State of analyticsRes should be Active after this deployment. You may confirm the same on Deployments page.
Log out from Oracle WebLogic Server Administration Console.
Log on to Oracle Enterprise Manager Fusion Middleware Control. The following shows the format of the URL:

https://<hostname>.<domain>:<port>/em
Restart the BI components.
Log on to OBIEE and verify the branding and help links on the dashboards.

2.8 Preparing the OBIEE Web Catalog and Repository for OHSCDA

You will be using OCDA.zip and OCDA.rpd files in this section.

To deploy InForm web catalog, copy OCDA.zip from <OCDA_HOME>\OCDA_Common\Reporting\Webcat\Inform_Webcat to OBIEE server.

Else, copy OCDA.zip from <OCDA_HOME>\OCDA_Common\Reporting\Webcat to OBIEE server.
Copy OCDA.rpd files from <OCDA_HOME>\OCDA_Common\Reporting\RPD to OBIEE server.
Unzip OCDA.zip in the <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog directory.
In the Oracle BI Administration Tool, select File, then Open, and then Offline.
Navigate to the OCDA.rpd, and then click Open.

Password: Admin123
Click OK.
In the Oracle BI Administration Tool, select File, then Change Password.
Enter the current (old) password.
Enter the new password and confirm it.
Confirm the new password.
Click OK.
Modify the connection pools in the RPD as following:
1. In the physical layer, expand the OCDA Data Warehouse node and double-click the Connection Pool object.
  
  The Connection Pool dialog box appears.
2. In the following string under the Data source name field, replace <hostname>, <port_number>, and <SID_NAME> with the host name, port number, and SID name of the database:
```
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<port_number>))(CONNECT_DATA=(SID=<SID_NAME>)))
```
3. Change the username to the username of the OHSCDA read-only DW RPD schema name.
4. Change the password to the password of the OHSCDA read-only DW RPD schema password.
5. Click OK.
6. Reconfirm the password.
7. Click OK.
8. In the physical layer, expand the OCDA Data Warehouse node and double-click the OCDA_CP_InitBlocks object.
9. Repeat steps b through h.
From the File menu, select Save to save the rpd.
Click Yes for Do you wish to check global consistency?
Click Close in the Consistency Check Manager.
Click Save.
Click File and then click Close.
Click File and then click Exit.
Open NQSConfig.INI found at <MIDDLEWARE_HOME>instances\<instancename>\config\OracleBIServerComponent\coreapplication_obis1.
Set EVALUATE_SUPPORT_LEVEL =2.
Restart the Oracle WebLogic Server and BI components.
Open the Fusion Middleware Control URL from the system where you saved the OCDA.rpd in step 13. The URL includes the name of the host and the port number assigned during the installation. The following shows the format of the URL:

https://<hostname>.<domain>:<port>/em

The login page appears.

Note:
Oracle recommends that you enable HTTPS on middle-tier computers that are hosting the Web services, since otherwise the trusted user name and password that are passed can be intercepted.
Enter the Oracle Fusion Middleware administrator user name and password and click Login.
Expand the Business Intelligence folder and select the coreapplication node.

The Overview page displays the current status of the system, by providing information about current availability, performance, and issues identified within the BI domain. The Overview page also enables you to start and stop Oracle Business Intelligence.
Navigate the Repository tab of the Deployment page.
Click Lock and Edit Configuration.
Click Close.
In the Upload BI Server Repository section, click Browse and navigate to select the RPD.
Enter the RPD password in Repository Password and Confirm Password fields.
In the BI Presentation Catalog section, enter the following (as applicable) in the Catalog Location field:

<MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\OCDA
Click Apply, and then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
Log on to OBIEE Analytics. The following shows the format of the URL:

https://<hostname>.<domain>:<port>/analytics

If you get an authentication error, perform the following steps:
1. Stop all opmn processes. Execute the following command from <MIDDLEWARE_HOME>\instances\<instancename>\bin:
```
opmnctl stopall
```
2. Update the NQSConfig.INI file found at <MIDDLEWARE_HOME>instances\<instancename>\config\OracleBIServerComponent\coreapplication_obis1.
```
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;
```
  By default, the value of FMW_UPDATE_ROLE_AND_USER_REF_GUIDS is No.
3. Add the following tag within the catalog tag in the instanceconfig.xml file found at <MIDDLEWARE_HOME>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1:
```
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
```
4. Start all opmn processes. Execute the following command from <MIDDLEWARE_HOME>\instances\<instancename>\bin:
```
opmnctl startall
```
5. Stop all opmn processes. Execute the following command from <MIDDLEWARE_HOME>\instances\<instancename>\bin:
```
opmnctl stopall
```
6. Update the NQSConfig.INI file found at <MIDDLEWARE_HOME>instances\<instancename>\config\OracleBIServerComponent\coreapplication_obis1.
```
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO;
```
7. Delete the following tag within the catalog tag in the instanceconfig.xml file found at <MIDDLEWARE_HOME>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1:
```
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
```
8. Start all opmn processes. Execute the following command from <MIDDLEWARE_HOME>\instances\<instancename>\bin:
```
opmnctl startall
```

2.9 Configuring Maps

To configure maps:

Go to http://www.oracle.com/technetwork/middleware/bi-foundation/obieesamplesarchive-2026956.html.
Navigate to OBIEE 11.1.1.3 - Sample Application.
Click Documentation Downloads.
Click Save.
Unzip the folder.
Open SampleApp_Deploy_Instructions_825.pdf.
For instructions on how to configure maps, see section 6 Deploying SampleApp Mapviewer Content.

2.10 Creating Security Groups and Application Roles for OHSCDA

2.10.1 Creating Groups

Perform the following steps to create groups:

Launch Oracle WebLogic Server Administration Console.
In Oracle WebLogic Server Administration Console, select Security Realms from the left pane and click the realm you are configuring. For example, myrealm.
Select Users and Groups tab, then Groups.
Click New.
In the Create a New Group page, provide the following information:
- Name: Enter the name of the group. Group names are case insensitive but must be unique. See online help for a list of invalid characters.
- (Optional) Description: Enter a description.
- Provider: Select the authentication provider from the list that corresponds to the identity store where the group information is contained. DefaultAuthenticator is the name for the default authentication provider.
Click OK.

The group name is added to the Groups table.

Repeat step 4 through 6 with the following values:

Table 2-4 Security Group Parameters

Name	Description	Provider
OCDA-CRA	(Optional)	DefaultAuthenticator
OCDA-DataManager	(Optional)	DefaultAuthenticator
OCDA-ExecutiveManager	(Optional)	DefaultAuthenticator
OCDA-RegionManager	(Optional)	DefaultAuthenticator
OCDA-StudyManager	(Optional)	DefaultAuthenticator
OCDA-WebcatAdim	(Optional)	DefaultAuthenticator

2.10.2 Creating Application Roles

Perform the following steps to create roles:

Log on to Oracle Enterprise Manager Fusion Middleware Control. The following shows the format of the URL:

https://<hostname>.<domain>:<port>/em
From the target navigation pane, open Business Intelligence.
Right-click coreapplication, then select Security > Application Roles.

The Application Roles page is displayed.
Click Create to display the Create Application Role page. Complete the fields as follows:

In the General section:
- Role Name - Enter the name of the Application Role.
- (Optional) Display Name - Enter the display name for the Application Role.
- (Optional) Description - Enter a description for the Application Role.
In the Members section, click Add.
1. In the dialog box that appears, click the blue button to search.
  
  The list containing all groups is displayed.
2. Select a group from the list.
3. Click OK.

Repeat steps 4 and 5 for all the Roles listed in the following table:

Table 2-5 Application Roles and Groups

Role Name	Description	Groups
OCDA-CRA	(Optional)	OCDA-CRA
OCDA-DataManager	(Optional)	OCDA-DataManager
OCDA-ExecutiveManager	(Optional)	OCDA-ExecutiveManager
OCDA-RegionManager	(Optional)	OCDA-RegionManager
OCDA-StudyManager	(Optional)	OCDA-StudyManager
OCDA-WebcatAdim	(Optional)	OCDA-WebcatAdim

Click OK.