Oracle® Health Sciences Clinical Development Analytics Installation Guide Release 2.1 for Plus Configuration E25026-02 |
|
|
PDF · Mobi · ePub |
This chapter describes CDA upgrade tasks that you must complete before you begin to use the CDA. This chapter includes the following topic:
You must install the CDA Release 2.1 as a patch to CDA Release 2.0.0.3.
This section includes following steps:
Executing LSH Script to fix source independent RU issues
Deleting Informatica Pool Programs.
Backing up CDA Rpd and webcat to preserve customizations.
Write steps to backup existing CDA warehouse.Installing CDA 2.1.
To install the patch:
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.
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.
CDA pool programs are being modified from program type Informatica to Plsql. Hence older program can be deleted.
Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_POOL_WORK_AREA
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
Use combo list. Select Object and Remove.
Click Go.
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:
Log in to the OBIEE Administration Tool, using an Administrator account
Open the CDA 2003 repository.
Select Tools, then select Utilities.
Select Oracle BI Event Tables and click Execute
Select W_ETL_RUN_S table from the Event Tables list, and move it back to the Tables list.
Click OK.
Expand Dim - Code Data Capture Mode in Business Model and Mapping Layer.
Expand Sources under Dim - Code Data Capture Mode.
Double-click Dim_W_LOV_D_Data_Capture_Mode and remove the trailing space in the name.
Click OK.
Expand Fact - OCDA - Received CRF in Business Model and Mapping Layer.
Expand Sources under Dim - Code Data Capture Mode.
Double-click Avg # of Days pCRF Awaiting First Entry and remove the trailing space in the name.
Click OK.
On the Tools menu, select Consistency Checker. Resolve errors, if any.
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).If you would like to retain data in warehouse populated by CDA 2.0.0.3, perform the following steps:
Navigate to OCDA_domain > Application Area: OCDA_CODE_APP_AREA > Workarea: OCDA_WORK_AREA.
In a Work Area, select Data Mart from the Add drop-down list.
Click Go.
The system displays the Create Data Mart screen.
Select Create a new Data Mart definition and instance.
Enter values in the following fields::
Name: OCDA_2003_DM
Data Mart Type: Oracle Export
Click Apply to save your work and continue defining the Data Mart.
The system opens the Properties screen for the new Data Mart instance.
In the Program's Properties screen, select Table Descriptors from Existing Table Instances from the Actions drop-down list and click Go.
Navigate to OCDA_domain > Application Area: OCDA_CODE_APP_AREA > Workarea: OCDA_WORK_AREA.
Select all the table instances by clicking the Select checkboxes.
Click Create Table Descriptor.
Repeat step 7 through 10 for the workarea OCDA_CONTROL_TABLE_WA.
Install OCDA_2003_DM data mart.
Submit the current data mart program.
Navigate to MyHome.
Click the Job id submitted and download OCDA_2003_DM.dmp file.
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.
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
Create a database user with privileges needed to import dump file.
Import OCDA_2003_DM.dmp file using the user created in step 1.
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.
Verify the log file.
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;
Follow steps in Post Installation Steps for CDA 2.1.
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.
Unmap target table descriptors of all programs in OCDA_WORK_AREA workarea.
Click OCDA_INFA_Activity_Fact_SIL_PRG and check out the program.
Click the icon in the Mapping column for the record which has Is Target value as Yes.
Click Update.
Click Unmap.
Click Apply.
The system unmaps the Table Descriptor.
Navigate to OCDA_Work_Area and perform step 1 through 5 for all programs.
Create a new remote location, for example, ocda2003_upd with adaptor value as Oracle Tables and Views.
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.
Create new load set , for example, ocda21_LS.
Update load set attributes.
Click the Search icon and select the source remote location/connection combination created in step 2 and 3 from the list of values.
Click Apply.
Click Upload under Table Descriptors tab.
Select all warehouse tablesin the next screen.
Click Apply.
Select Automatic Mapping By Name from the Actions drop-down list and click Go.
Select all table descriptors and click Map.
Partially install the ocda21_LS.
Click Submit with following parameters:
Submission Type: Immediate
Force Execution: Yes
Set Remote Location parameter, to the same location you have created in step 2 (for example, ocda2003_upd), under Submission Parameters tab.
Submit the load set. Navigate to my home and monitor the job for successful execution.
Delete the load set from OCDA_WORK_AREA after successful execution.
Map target table descriptors of all programs in OCDA_WORK_AREA.
Click OCDA_INFA_Activity_Fact_SIL_PRG.
Select Automatic Mapping By Name from the Actions drop-down list and click Go.
Select a target table and click Map.
Navigate to OCDA_Work_Area and perform step 1 through 3 for all programs.
Partially install all program instances in OCDA_WORK_AREA with Replace object Action.
Navigate to OCDA_WORK_AREA and Click Install.
Select installation mode as partial
.
Under Omitted column, deselect all program instances.
Click Apply and Install.
After successful install, you can continue with Scheduling and Executing Extract Transform Load Jobs.
Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CONTROL_TABLE_WA.
Click OCDA_CONTROL_TABLE_POPULATE_PRG.
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.
Click Submit. Navigate to MyHome and monitor the Job_ID which was created for the current submission.
If you are disabling one of the source systems, perform steps listed in section Creating Source Configuration Schema and Tables
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).
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).Install OCDA_OC_DATA_WA work area manually in Full mode.
Navigate OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_SC_DATA_WA and ensure that the following connections are set in Oracle LSH:
Note:
If you have used either the view creation script or the synonym creation script, you must use the same user who is the owner of the schema for setting OCDA_SC_OLTP_RL.Note:
If Siebel clinical is one of your sources, follow the section Handling Deletions in Siebel ClinicalInstall OCDA_SC_DATA_WA work area manually in Full mode.
Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CUSTOM_TABLE_WA and ensure that the OCDA_CUSTOM_OLTP_RL/RXI remote connection is set for OCDA_RXI_LS load set.
Caution:
Ensure that the Informatica Distributed Processing (DP) Server is up and running. For more information on setting up DP Server in Oracle LSH, refer to Oracle Life Sciences Data Hub System Administrator's Guide (Setting Up the Distributed Processing Server section in Chapter 1, Setting Up Services.Install OCDA_CUSTOM_TABLE_WA work area manually in Full mode.
Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA and install the following work areas in upgrade mode, with this specified order:
OCDA_DELETE_LOG_TABLE_WA
OCDA_DWH_PASS_THROUGH_WA
Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA and upgrade install OCDA_CONTROL_TABLE_WA work area.
Navigate to OCDA_domain > OCDA_UTIL_APP_AREA and install the following work:
OCDA_SHARED_PROGRAM_WA
Navigate to OCDA_domain > OCDA_CODE_APP_AREA and install the following work areas in the specified order:
OCDA_SDE_SC_WORK_AREA
OCDA_SDE_OC_WORK_AREA
OCDA_POOL_WORK_AREA
OCDA_UTIL_WA
Navigate to OCDA_domain > OCDA_CODE_APP_AREA >OCDA_WORK_AREA .
Click Installation.
In the Work Area Install screen, select the following options:
Install Mode: Partial
Install Option: Force Script Re-generation
In Work Area Objects, select only the table instances with replace object option.
Click Apply and Install.
Perform following steps before running the work area install script:
Navigate to OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
Select table instance W_LOV_D.
Check out the table instance.
Select the Constraints/Indexes tab.
Select W_LOV_D_U2 constraint and remove it.
Navigate to OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
Select table instance OCDA_INFA_Party_Dim_SIL_PRG.
Check out the program instance.
Select the Table Descriptors tab.
Select W_HS_MAPPING_S table descriptor and remove it.
Partially install OCDA_INFA_Party_Dim_SIL_PRG program instance and the W_LOV_D table instance.
Ensure that all the work areas in OCDA_domain are in Status Installable.
The exception OCDA_DWH_WA can be ignored since it is used to fetch definition and is not used during ETL execution.
Navigate to OCDA_domain > OCDA_UTIL_APP_AREA and install the following work:
OCDA_ETL_WORKFLOW_WA
On the Oracle LSH database server, navigate to the directory where OCDA_domain.zip is placed in staging area, and run the following work area (WA) installation script:
sqlplus apps/<apps_password>@<DB_INSTANCE> @../cdrruwainstall.sql <LSH_APPL_USER> <DB_DIRECTORY> OCDA_domain.zip
where:
<DB_INSTANCE> is the service name for the database where Oracle LSH is installed.
<LSH_APPL_USER> is the LSH user account as created in the Creating an Oracle Life Sciences Data Hub User Account.
<DB_DIRECTORY> is the logical DB directory name mapped to the Operating System (OS) directory containing the OCDA_domain.zip to be imported.
Note:
Use the ocda_domain_import.log file to verify if the script has executed successfully.Note:
After you have successfully run the WA install script, run the Gather Schema Statistics concurrent program to gather table statistics. For more information refer to, Section 2.3, "Gathering Table Statistics"In Oracle LSH, execute the ETL Programs. Follow steps listed in Oracle Health Sciences Clinical Development Analytics Administrator's Guide Release 2. 1 (Executing the ETL Programs).
Perform the steps listed in the following sections:
If you plan to use deduplication:
If you do not plan to use deduplication:
Note:
If you plan to upgrade CDA 2.0.0.3 Standard Configuration Repository (RPD) and Web Catalog to CDA 2.1 Standard 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).You have now installed the CDA patch. To confirm that the patch has been successfully applied, start Dashboards and confirm that the data is displayed after initial ETL execution.
See Also:
Oracle Life Sciences Data Hub System Administrator's Guide
Oracle Life Sciences Data Hub Installation Guide
For information on scheduling and executing Extract, Transform, and Load (ETL) jobs, refer to Oracle Clinical Development Analytics Administrator Guide (Chapter 2, Extract Transform Load Programs).
Note Ensure that each ETL program has a default execution setup. |