Skip Headers
Oracle® Health Sciences Clinical Development Analytics Installation Guide
Release 2.1 for Plus Configuration
E25026-02
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
PDF · Mobi · ePub

4 Upgrade Tasks

This chapter describes CDA upgrade tasks that you must complete before you begin to use the CDA. This chapter includes the following topic:

4.1 Upgrading CDA 2.0.0.3 to CDA 2.1

You must install the CDA Release 2.1 as a patch to CDA Release 2.0.0.3.

4.1.1 Installing CDA 2.1

This section includes following steps:

  1. Executing LSH Script to fix source independent RU issues

  2. Deleting Informatica Pool Programs.

  3. Backing up CDA Rpd and webcat to preserve customizations.

  4. Write steps to backup existing CDA warehouse.Installing CDA 2.1.

4.1.1.1 Executing LSH Script to Fix Source Independent Release Utility Issues

To install the patch:

  1. Log in to the database as apps user where Oracle LSH is installed.

    sqlplus apps/<apps_password>@<DB_INSTANCE> where: <DB_INSTANCE> is the service name for the database where Oracle LSH is installed.

  2. Execute the following commands:

    create table cdr_ru_imports_bkp as (select * from cdr_ru_imports);

    update cdr_ru_imports set original_company_id=src_company_id, original_obj_id=src_obj_id;

    commit;

These commands fix a bug in the LSH Source-Independent Release Utility.

4.1.1.2 Deleting Informatica Pool Programs

CDA pool programs are being modified from program type Informatica to Plsql. Hence older program can be deleted.

  1. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_POOL_WORK_AREA

  2. Select check box for the programs listed below.

    • OCDA_INFA_Activity_Fact_SDE_Pool_PRG

    • OCDA_INFA_Application_User_D_SDE_Pool_PRG

    • OCDA_INFA_CRF_Book_Dim_SDE_Pool_PRG

    • OCDA_INFA_CRF_Dim_SDE_Pool_PRG

    • OCDA_INFA_Discrepancy_Fact_SDE_Pool_PRG

    • OCDA_INFA_Discrepancy_Status_SDE_Pool_PRG

    • OCDA_INFA_Employee_Dim_SDE_Pool_PRG

    • OCDA_INFA_Geo_Dim_SDE_Pool_PRG

    • OCDA_INFA_LOV_Dim_SDE_Pool_PRG

    • OCDA_INFA_Party_Per_Dim_SDE_Pool_PRG

    • OCDA_INFA_Product_Dim_SDE_Pool_PRG

    • OCDA_INFA_Program_Dim_SDE_POOL_PRG

    • OCDA_INFA_Received_CRF_Fact_SDE_Pool_PRG

    • OCDA_INFA_Rgn_Enrlmnt_Pln_Fact_SDE_Pool_PRG

    • OCDA_INFA_SS_Con_Dim_SDE_Pool_PRG

    • OCDA_INFA_SS_Team_History_Dim_SDE_Pool_PRG

    • OCDA_INFA_Site_Dim_SDE_Pool_PRG

    • OCDA_INFA_Site_Enrl_Pln_Fact_SDE_Pool_PRG

    • OCDA_INFA_Stdy_Enrlmnt_Pln_Fact_SDE_Pool_PRG

    • OCDA_INFA_Study_Access_Sec_SDE_Pool_PRG

    • OCDA_INFA_Study_Dim_SDE_Pool_PRG

    • OCDA_INFA_Study_Region_Dim_SDE_Pool_PRG

    • OCDA_INFA_Study_Site_Access_Sec_SDE_Pool_PRG

    • OCDA_INFA_Study_Site_Dim_SDE_Pool_PRG

    • OCDA_INFA_Study_Subject_Dim_SDE_Pool_PRG

    • OCDA_INFA_Subject_Prtcptn_Fact_SDE_Pool_PRG

    • OCDA_INFA_Subject_Status_Fact_SDE_Pool_PRG

    • OCDA_INFA_User_Dim_SDE_Pool_PRG

    • OCDA_INFA_Validation_Procedure_SDE_Pool_PRG

  3. Use combo list. Select Object and Remove.

  4. Click Go.

4.1.1.3 Backing Up CDA RPD and Web Catalog to Preserve Customizations

If you have done any customizations on CDA 2.0.0.3 RPD and Web Catalog, perform the following steps before you start migration and upgrade activities:

  1. Log in to the OBIEE Administration Tool, using an Administrator account

  2. Open the CDA 2003 repository.

  3. Select Tools, then select Utilities.

  4. Select Oracle BI Event Tables and click Execute

  5. Select W_ETL_RUN_S table from the Event Tables list, and move it back to the Tables list.

  6. Click OK.

  7. Expand Dim - Code Data Capture Mode in Business Model and Mapping Layer.

  8. Expand Sources under Dim - Code Data Capture Mode.

  9. Double-click Dim_W_LOV_D_Data_Capture_Mode and remove the trailing space in the name.

  10. Click OK.

  11. Expand Fact - OCDA - Received CRF in Business Model and Mapping Layer.

  12. Expand Sources under Dim - Code Data Capture Mode.

  13. Double-click Avg # of Days pCRF Awaiting First Entry and remove the trailing space in the name.

  14. Click OK.

  15. On the Tools menu, select Consistency Checker. Resolve errors, if any.

  16. Save the repository file.

    Note:

    If you plan to upgrade CDA 2.0.0.3 Plus Configuration Repository (RPD) and Web Catalog to CDA 2.1 Plus Configuration, refer to Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence 11g Release 1 (11.1.1) and Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).

4.1.1.4 Migrating CDA 2.0.0.3 Data Warehouse to CDA 2.1

If you would like to retain data in warehouse populated by CDA 2.0.0.3, perform the following steps:

4.1.1.4.1 Take Backup of 2.0.0.3 Warehouse Data using Oracle LSH Data Mart

  1. Navigate to OCDA_domain > Application Area: OCDA_CODE_APP_AREA > Workarea: OCDA_WORK_AREA.

  2. In a Work Area, select Data Mart from the Add drop-down list.

  3. Click Go.

    The system displays the Create Data Mart screen.

  4. Select Create a new Data Mart definition and instance.

  5. Enter values in the following fields::

    • Name: OCDA_2003_DM

    • Data Mart Type: Oracle Export

  6. Click Apply to save your work and continue defining the Data Mart.

    The system opens the Properties screen for the new Data Mart instance.

  7. In the Program's Properties screen, select Table Descriptors from Existing Table Instances from the Actions drop-down list and click Go.

  8. Navigate to OCDA_domain > Application Area: OCDA_CODE_APP_AREA > Workarea: OCDA_WORK_AREA.

  9. Select all the table instances by clicking the Select checkboxes.

  10. Click Create Table Descriptor.

  11. Repeat step 7 through 10 for the workarea OCDA_CONTROL_TABLE_WA.

  12. Install OCDA_2003_DM data mart.

  13. Submit the current data mart program.

  14. Navigate to MyHome.

  15. Click the Job id submitted and download OCDA_2003_DM.dmp file.

  16. Partially install all table instances in OCDA_WORK_AREA with replace object.

    This cleans old data of all table instances such that new data can be migrated into warehouse. Also, this is required to avoid errors during upgrade of table instanced in further steps.

4.1.1.4.2 Import Data Mart dmp File and Make Exported Data Compatible with Oracle Health Sciences Clinical Development Analytics 2.1

  1. Run the installer in upgrade mode. This will extract upgd_ocda_plus_2.0.0.3_to_2.1_ddl.sql to the staging area.

    You must install CDA Release 2.1 as a patch to CDA Release 2.0.0.3.

    Run the CDA Installer as described in the sections:

    At the completion of the installation, you can inspect the installation log at:

    <ocda_home>\install\ocda_install.log

  2. Create a database user with privileges needed to import dump file.

  3. Import OCDA_2003_DM.dmp file using the user created in step 1.

  4. Log in to the schema and execute upgd_ocda_plus_2.0.0.3_to_2.1_ddl.sql script from the temporary staging location.

    The script upgrades CDA 2.0.0.3 to CDA 2.1.

  5. Verify the log file.

  6. Execute the following SQL statements:

    UPDATE W_EMPLOYEE_D SET DATASOURCE_NUM_ID=-1 WHERE ROW_WID=-1;
update W_PRODUCT_D set DELETE_FLG='N',MERGE_FLAG='N' where ROW_WID=-1;
update W_GEO_D t1 set integration_id= (select country||':'||state_prov||':'|| city||':'|| zipcode from W_GEO_D t2 where t1.row_wid=t2.row_wid) where t1.row_wid<>-1;
UPDATE W_GEO_D SET delete_flg = 'N' where row_wid=-1;
UPDATE W_GEO_D SET merge_flag = 'N';
UPDATE W_LOV_D SET delete_flg = 'N' where row_wid=-1;
UPDATE W_LOV_D SET merge_flag = 'N';
UPDATE W_GEO_D SET delete_flg = 'N';
UPDATE W_LOV_D SET delete_flg = 'N';
COMMIT;

  7. Follow steps in Post Installation Steps for CDA 2.1.

4.1.1.4.3 Re-import Migrated Data Back into the Warehouse

Before you re-import data, perform steps listed in Creating Source Configuration Schema and Tables .

After applying CDA 2.1, you can re-import exported data back into LSH CDA warehouse tables.

  1. Unmap target table descriptors of all programs in OCDA_WORK_AREA workarea.

    1. Click OCDA_INFA_Activity_Fact_SIL_PRG and check out the program.

    2. Click the icon in the Mapping column for the record which has Is Target value as Yes.

    3. Click Update.

    4. Click Unmap.

    5. Click Apply.

      The system unmaps the Table Descriptor.

    6. Navigate to OCDA_Work_Area and perform step 1 through 5 for all programs.

  2. Create a new remote location, for example, ocda2003_upd with adaptor value as Oracle Tables and Views.

  3. Create a connection for newly created remote location in step 2. Specify user name of the database which holds the migrated data while specifying connection details.

  4. Create new load set , for example, ocda21_LS.

  5. Update load set attributes.

    1. Click the Search icon and select the source remote location/connection combination created in step 2 and 3 from the list of values.

    2. Click Apply.

    3. Click Upload under Table Descriptors tab.

    4. Select all warehouse tablesin the next screen.

    5. Click Apply.

    6. Select Automatic Mapping By Name from the Actions drop-down list and click Go.

    7. Select all table descriptors and click Map.

    8. Partially install the ocda21_LS.

    9. Click Submit with following parameters:

      Submission Type: Immediate

      Force Execution: Yes

    10. Set Remote Location parameter, to the same location you have created in step 2 (for example, ocda2003_upd), under Submission Parameters tab.

    11. Submit the load set. Navigate to my home and monitor the job for successful execution.

    12. Delete the load set from OCDA_WORK_AREA after successful execution.

  6. Map target table descriptors of all programs in OCDA_WORK_AREA.

    1. Click OCDA_INFA_Activity_Fact_SIL_PRG.

    2. Select Automatic Mapping By Name from the Actions drop-down list and click Go.

    3. Select a target table and click Map.

    4. Navigate to OCDA_Work_Area and perform step 1 through 3 for all programs.

  7. Partially install all program instances in OCDA_WORK_AREA with Replace object Action.

    1. Navigate to OCDA_WORK_AREA and Click Install.

    2. Select installation mode as partial.

    3. Under Omitted column, deselect all program instances.

    4. Click Apply and Install.

    5. After successful install, you can continue with Scheduling and Executing Extract Transform Load Jobs.

4.1.1.4.4 Delete Program Entries from Control Table to Facilitate Loading Newly Added Columns

  1. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CONTROL_TABLE_WA.

  2. Click OCDA_CONTROL_TABLE_POPULATE_PRG.

  3. Submit the program with following parameters:

    Submission Details

    Submission Type: Immediate

    Submission Mode: Incremental

    Force Execution: Yes

    Submission Parameters

    Delete_mode: Program_Name

    Input_values: <Enter a comma-separated list of following program names>

    OCDA_INFA_Study_Dim_SIL_PRG,OCDA_INFA_Study_Site_Dim_SIL_PRG,OCDA_INFA_Geo_Dim_SIL_PRG,OCDA_INFA_Site_Dim_SIL_PRG,OCDA_INFA_Study_Region_Dim_SIL_PRG,OCDA_INFA_Study_Subject_Dim_SIL_PRG,OCDA_INFA_LOV_Dim_SIL_PRG,OCDA_INFA_User_Dim_SIL_PRG,OCDA_INFA_Product_Dim_SIL_PRG,OCDA_INFA_Program_Dim_SIL_PRG,OCDA_INFA_SS_Team_History_Dim_SIL_PRG

    These programs have had changes to their target tables, and therefore need to be reloaded for retrieval of data for newly added columns.

  4. Click Submit. Navigate to MyHome and monitor the Job_ID which was created for the current submission.

4.1.1.5 Post Installation Steps for CDA 2.1

If you are disabling one of the source systems, perform steps listed in section Creating Source Configuration Schema and Tables

  1. Log in to Oracle LSH as a user who can execute ETL Programs.

    For more information, refer to Oracle Health Sciences Clinical Development Analytics Administrator Guide Release 2.1 (Security).

  2. Navigate OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_OC_DATA_WA and ensure that the following connections are set in Oracle LSH:

    Table 4-1 Connections in Oracle LSH

    Remote Connection Load Set

    OCDA_OC_OLTP_RL/OPA

    OCDA_OC_OPA_LS

    OCDA_OC_OLTP_RL/RXA_DES

    OCDA_OC_RXA_DES_LS

    OCDA_OC_OLTP_RL/RXC

    OCDA_OC_RXC_LS

    Note:

    For more information about setting up remote locations in Oracle LSH, refer to Oracle Clinical Installation Guide Release 2. 1 (Post Installation Tasks).