3 Upgrading Oracle Health Sciences Clinical Development Analytics

This chapter provides procedures that explain how to upgrade OHSCDA. It includes the following sections:

• Migrating from OHSCDA 2.1 (Plus Configuration) to OHSCDA 2.2 (Standard Configuration).

• Upgrading from OHSCDA 2.1 Standard to OHSCDA 2.2 Standard.

3.1 Migrating from OHSCDA 2.1 (Plus Configuration) to OHSCDA 2.2 (Standard Configuration)

If you are migrating from OCDA 2.1 (Plus Configuration) to OHSCDA 2.2 (Standard Configuration), perform the following steps:

1. Install OHSCDA 2.2 (Standard Configuration). Refer to Section 2.1.1, "Running the OHSCDA Installer," for more details.

2. If you made any modifications to database tables or ETL System Dependent Extract (SDE) or System Independent load (SIL) in your Plus Configuration, apply the same changes to their counterparts in the Standard Configuration (Informatica PowerCenter and DAC).

3. Perform an initial load of data from the transactional sources.

   1. Perform a full load of the warehouse using CDA- Warehouse Execution Plan.

3.2 Upgrading from OHSCDA 2.1 Standard to OHSCDA 2.2 Standard

The Installer automates the upgrade of the warehouse and the Informatica mappings. After running the Installer, carry out the remaining steps manually.

Note:

OHASCDA 2.2 Standard is certified on Oracle Database 11.2.0.3. For detailed certification information, refer to Finding Certification Information.

3.2.1 Upgrading Warehouse Database Version and Reconfiguring OBIEE and DAC Client

1. Upgrade the warehouse database version from 11.2.0.2 to 11.2.0.3.

2. Ensure that your OBIEE version is 11.1.1.6.4.

3. Reconfigure OBIEE and DAC client, pointing to the new upgraded warehouse database version 11.2.0.3.

3.2.2 Running the Installer in Upgrade Mode

1. Run the OHSCDA Installer as described in the section Section 2.1.1, "Running the OHSCDA Installer."

   After the installation is completed, you can inspect the installation log at:

   <ocda_home>\install\ocda_install.log.

3.2.3 Next Steps

After running the installer, perform the following steps:

1. Perform the steps listed in the section Section 3.2.5, "Migrating Data from OHSCDA 2.1 to OHSCDA 2.2 (Standard Configuration)."

2. Perform the steps listed in the following sections:

   If you plan to use deduplication:

   • Install OHMPI, following the instructions in 1Section 1.6, "Installing the Prerequisite Software"

   • Add the OHMPI master index database as an Informatica source, following the instructions in section Section 2.1.2, "Setting Up Relational Connections in the Informatica Workflow for OHSCDA"

   • Add the OHMPI master index database as a DAC source, following the instructions in steps 7, 8, and 9 of Section Section 2.1.3, "Preparing a DAC Repository for OHSCDA"

   • Section 3.2.4, "Emplacing the OHSCDA Help and Image Files,"

   • Section 2.1.5, "Preparing the OBIEE Web Catalog and Repository for OHSCDA,"

     Note:

     If you are upgrading from OHSCDA 2.1 and have modified the OBIEE repository or Web Catalog, merge these objects. For more information, refer to Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1.

   • Section 2.1.6, "Configuring Maps,"

   • Section 2.1.7, "Creating Security Groups and Application Roles for OHSCDA,"

   • Section 2.1.8, "Setting Up OHMPI Projects (Optional),"

   • Section 2.1.9, "Setting Up Informatica Server (Optional),"

   If you do not plan to use deduplication:

   • Section 2.1.4, "Emplacing the OHSCDA Help and Image Files,"

   • Section 2.1.5, "Preparing the OBIEE Web Catalog and Repository for OHSCDA,"

     Note:

     If you are upgrading from OHSCDA 2.1 and have modified the OBIEE repository or Web Catalog, merge these objects. For more information, refer to Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1.

   • Section 2.1.6, "Configuring Maps,"

   • Section 2.1.7, "Creating Security Groups and Application Roles for OHSCDA,"

3.2.4 Emplacing the OHSCDA Help and Image Files

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

3.2.4.1 File Placement for Oracle WebLogic Managed Server

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

1. Navigate to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\ in Oracle WebLogic Server.

2. If not already present, create the following folders at <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\

   • s_ocda

3. Unzip help.zip from OCDA_Home\oracle.pharma.ocda.standard\Reporting\Help to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda\.

4. Move customMessages and sk_ocda folders from <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda\ to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\.

5. If not already present, add the following tag in instanceconfig.xml, present in <MIDDLEWARE_HOME>\\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1.

   <UI><DefaultSkin>ocda</DefaultSkin></UI>

6. Unzip the Images.zip files from OCDA_Home\oracle.pharma.ocda.standard\Reporting\Images to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\s_ocda.

7. Launch Oracle WebLogic Administration Server Console.

   Example: https://hostname.domain:port/console

   This opens the Oracle WebLogic Server Administration Console.

8. Log in to Oracle WebLogic Server Administration Console as an Administrator.

9. In the left pane of the Administration Console, select Deployments.

10. On the left pane, click Lock & Edit.

11. In the right pane, click Update.

    This opens the Install Application Assistant.

12. In the Path field browse to <MIDDLEWARE_HOME>\instances\<instancename>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\.

13. Select analyticsRes and click Next.

14. Click Finish.

15. Click Activate Changes.

16. Select analyticsRes in Deployment section.

17. Click Start to view the list and select Servicing all requests. The Start Application Assistant page is displayed.

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

19. Log out from Oracle WebLogic Server Administration Console.

20. Log in to Oracle Enterprise Manager Fusion Middleware Control.

21. Restart the BI components.

22. Log in to OBIEE and verify the branding and help links on the dashboards.

See Also:

• Oracle WebLogic Server Documentation Library

3.2.5 Migrating Data from OHSCDA 2.1 to OHSCDA 2.2 (Standard Configuration)

Perform the following steps to migrate data from OCDA 2.1 to OHSCDA 2.2 (Standard Configuration):

Note:

Data migration scripts are available in OCDA_HOME\oracle.pharma.ocda.standard\DataModels\OCDA_2.1_to_2.2_Data_Migration_scripts.zip.

1. Log in to the OCDA Target database instance using system user.

2. Grant privileges to the following databases for creating the database link.

   For example:

   GRANT create database link TO <rxi_user>;
   GRANT create database link TO ohmpi_study_site;
   GRANT create database link TO ohmpi_study_region;
   

3. Log into your CDA data warehouse schema.

4. Create the following four database links:

   The dblink name should be oc_dblink for Oracle Clinical Source, sc_dblink for Siebel Clinical Source, ohmpi_study_site for OHMPI Study Site, and ohmpi_study_region for OHMPI Study_Region.

   Example (OC):

   create database link <oc_dblink>

   connect to <oc_username> identified by <oc password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

   Example (SC):

   create database link <sc_dblink>

   connect to <sc_username> identified by <sc password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

   Example (OHMPI Study Site):

   create database link < ohmpi_study_site>

   connect to <ohmpi_study_site_username> identified by <ohmpi_study_site_password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

   Example (OHMPI Study Region):

   create database link < ohmpi_study_region>

   connect to <ohmpi_study_region_username> identified by <ohmpi_ohmpi_study_region_password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

5. Execute Dimension Denormalization scripts:

   1. Execute update_dim_oc_denorm_id.sql.

   2. Execute update_dim_sc_denorm_id.sql.

6. If you had implemented MSI (Multi Source Integration) in CDA 2.1, perform step 7 through 14 else move to step 15 onwards.

7. Log in to OCDA OHMPI_STUDY_SITE schema.

8. Create the following two database links:

   The dblink name should be oc_dblink for Oracle Clinical Source and sc_dblink for Siebel Clinical Source.

   Example (OC):

   create database link <oc_dblink>

   connect to <oc_username> identified by <oc password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

   Example (SC):

   create database link <sc_dblink>

   connect to <sc_username> identified by <sc password>

   using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

9. Execute OHMPI Study Site scripts:

   1. Execute alter_ohmpi_study_site.sql.

   2. Execute update_ohmpi_study_site.sql.

10. Log in to OCDA OHMPI_STUDY schema.

11. Execute OHMPI Study scripts:

    1. Execute script alter_ohmpi_study.sql.

12. Log in to OCDA OHMPI_STUDY_REGION schema.

13. Create the following database link:

    The dblink name should be sc_dblink for Siebel Clinical Source.

    Example (SC):

    create database link <sc_dblink>

    connect to <sc_username> identified by <sc password>

    using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<portno>))(CONNECT_DATA=(SID=<sidname>)))';

14. Execute OHMPI Study Region scripts:

    1. Execute alter_ohmpi_study_region.sql.

    2. Execute update_ohmpi_study_region.sql.

15. Log into your CDA warehouse schema.

16. Execute the following migration scripts:

    update_mdm_table.sql

    upd_win_data_into_ref_dim.sql

    load_region_dim.sql

17. Execute the commit statement.

18. Execute the migration script update_fact_wid.sql.

19. After data validation, delete the temporary table and db links by executing the following script:

    drop_tables_and_dblinks.sql

3.2.6 Setting Up the Informatica Server for Upgrade

If you plan to use Dedup, ensure the Informatica Server is setup as defined in following sections:

• Section 2.1.9, "Setting Up Informatica Server (Optional),"

3.2.7 Redeploy following OHMPI projects into Oracle WebLogic

Redeploy the following OHMPI projects into Oracle WebLogic server:

Table 3-1 List of Projects

Project Name
OCDA_Study_Site
OCDA_Study_Region
OCDA_Study

3.2.8 Upgrading the Existing DAC Environment

Perform the following steps to upgrade the existing DAC environment:

3.2.8.1 Back Up the Existing DAC Repository

Perform the following steps to back up the existing DAC repository:

1. Export the current DAC repository by connecting to DAC repository as an Administrator:

   1. Start the Data Warehouse Administration Console (DAC) client.

   2. From the Tools menu select DAC Repository Management, and then select Export.

   3. Click Change import/export folder to navigate to an appropriate location.

   4. Click OK to display the Export dialog box.

   5. Select the following categories of metadata you want to import: Logical, Overwrite log file, and User Data.

   6. Deselect the System check box.

   7. Select CDA_Warehouse application in the ApplicationList.

   8. Click OK.

   Note:

   Before upgrading or backing up, note down all parameter and connection values set in DAC containers to reverify these values after DAC upgradation.

3.2.8.2 Upgrade the Current DAC Repository

1. Connect to the existing DAC repository, as Administrator.

2. Unzip %ocda_home%\ oracle.pharma.ocda.standard\DAC_Code\CDA_Warehouse.zip into a local folder.

3. Start the Data Warehouse Administration Console (DAC) client.

4. Log in to Data Warehouse Administration Console (DAC) client as an Administrator.

5. Make note of current logical names of the physical data sources. You will need them later during the upgrade.

6. Change logical names of the physical data sources to Oracle_Clinical, Siebel_Clinical, and Datawarehouse in DAC (depending on the source system connections used during OCDA 2.1).

7. Navigate to Tool, then DAC Repository Management, and then Upgrade/Merge Wizard.

8. From the Start Process list, select Replace Base and then click OK.

   The Import Source System Container dialog box appears.

9. Click Change import/export folder to navigate to the directory where you unzipped CDA_Warehouse.zip in Step 1 of this sub section.

10. Select the container you want to upgrade. It should be same as the one available for you in the Source System Container list. For example, CDA_Warehouse) and click OK.

11. In the Importing Tables dialog box, re-type the text in the text box to confirm you want to proceed, and click Yes.

    When the import process is complete, the Importing Tables dialog box tells you how long the process took.

12. Click OK.

    The Create Difference Report dialog box appears.

13. Create the Difference Report to view the differences between the new and existing DAC repositories.

    1. Enter a name for the Difference Report, or leave the default name.

    2. Select the appropriate existing container.

    3. (Optional) Enter a description for the Difference Report.

    4. Click OK.

       When the Difference Report is complete, the Creating Difference Report dialog box tells you how long the process took.

    5. Click OK.

       The View Difference Report dialog box displays the differences between the new and existing DAC repositories.

    6. Click Merge.

       The Merge dialog box appears and lists the details of the merge.

    7. Click Merge to begin the merge process.

    8. Click OK in the Merging Repositories dialog box when the merge process is complete.

    9. Verify Physical Data Source connection for the source and target connections.

3.2.9 Importing the OHSCDA Warehouse Application Metadata into the DAC Repository

1. Start the Data Warehouse Administration Console (DAC) client.

2. From the Tools menu, select DAC Repository Management, and then select Import.

3. Click Change import/export folder to navigate to the folder where you unzipped CDA_Warehouse.zip.

4. Click OK to display the Import dialog box.

5. Select the following categories of metadata you want to import: Logical, Overwrite log file, and User Data.

6. Deselect the System check box.

7. If you plan to implement deduplication, select all the applications in the ApplicationList else select only CDA_Warehouse application in the ApplicationList.

8. Click OK.

9. Enter the verification code and OK in the secondary window that is displayed after the import.

10. You can inspect the import log in ${DAC_ INSTALL_DIR}\log\import.log to verify if import is successful.

    You can ignore the following entry in the log:

    ORA-00001: unique constraint (<dac_schema>.W_ETL_<table_name>_U1) violated

11. Revert the logical names of the physical data sources to what you had noted in step 5 of Section 3.2.8.2, "Upgrade the Current DAC Repository,".

    Note:

    Ensure that the source and target connections names are same as the relational connection names you created in Informatica Workflow Manager.

12. Enable task level parameter in DAC.

    1. Select CDA_Warehouse application in the ApplicationList.

    2. Click Tasks tab in the top pane.

    3. Select a task.

    4. Click Parameters subtab in the bottom pane.

    5. Deselect Inactive for START_TS parameter.

    6. Click Save.

    7. Repeat steps 3, 4, and 5 for each of the following tasks:

       Table 3-2 List of Tasks

       Task Name
       SDE_SC_Study_Team_Dim
       SDE_SC_Study_Region_Team_Dhls

       
       

13. Add task level parameter in DAC.

    1. Select CDA_Warehouse application in the ApplicationList.

    2. Click Tasks tab in the top pane.

    3. Select a task.

    4. Click Parameters subtab in the bottom pane.

    5. Click New.

    6. Enter Name as START_TS, Select Data Type as TimeStamp, and Select Load Type as Incremental.

    7. Click the Value field icon to open a new window.

    8. Select Static. In the date field enter Jan,1 1900 12:00:00AM.

    9. In the Function drop-down list select Custom Format as Format type and enter MM/DD/YYYY HH24:MI:SS.

    10. Click OK and deselect Inactive. Click Save in the bottom half of the screen to save the parameter and then Save in the top half of the screen to save the task.

    11. Repeat steps 3 to 10 for following tasks:

    Table 3-3 List of Tasks

    Task Name
    SDE_SC_Study_Site_Fact
    SDE_OC_Study_Site_Fact
    SDE_SC_Subject_Visit_Fact

    
    

14. Execute one of the following execution plans. It must be same as you have executed in OCDA 2.1.

    • CDA - Complete Warehouse

    • CDA - Siebel Clinical Warehouse

    • CDA - Oracle Clinical Warehouse

15. Disable START_TS for each task:

    1. Select CDA_Warehouse application in the ApplicationList.

    2. Click Tasks tab in the top pane.

    3. For each task in Table 3-1:

       
       - Select the task.
       - Click Parameters subtab in the bottom pane.
       - Select Inactive for the START_TS parameter.

       Table 3-4 List of Tasks

       Task Name
       SDE_SC_Study_Site_Fact
       SDE_OC_Study_Site_Fact
       SDE_SC_Subject_Visit_Fact
       SDE_SC_Study_Team_Dim
       SDE_SC_Study_Region_Team_Dhls

       
       

3.2.10 Merging Changes into a New Oracle-supplied Repository

Each OHSCDA release, and some patches, includes a copy of the OHSCDA Repository. If you do modify your copy of the OHSCDA Repository, you must merge your changes into the Oracle-supplied Repository each time you receive a release or patch of OHSCDA that includes a repository. At upgrade time, use the OBIEE Merge Repository Wizard (accessed through File > Merge) in the Repository Administration Tool to merge your modified RPD with the Oracle-supplied RPD.

For information on comparing and merging Repositories, refer to Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1.