3 Installing the Oracle Health Sciences Cohort Explorer ETL

3.3.1.1 Downloading Zipped Files from the Installation Package

Procure the master repository (Cohort_Explorer_ODI_Master_Repository.zip) and work repository (Cohort_Explorer_ODI_Work_Repository.zip) files from the installation package.

3.3.1.2 Installing Cohort ETLs for ODI

create user TRC_31_REP identified by TRC_31_REP default tablespace TRC_31_REP_TS;
alter user TRC_31_REP quota unlimited on TRC_31_REP_TS;
grant connect,resource to TRC_31_REP;

3.3.1.3 Creating an Oracle Data Integrator Work Repository

Creating an ODI work repository involves two steps:

• Creating a New Oracle Data Integrator Work Repository

• Importing the Oracle Health Sciences Cohort Explorer Work Repository

3.3.1.4 Configuring the Physical Data Server

During the installation the following physical data servers are created automatically:

• ORACLE_APPS

• ORACLE_HDM

• ORACLE_TMP_DATAMART

Configure these data servers as described below:

1. Navigate to Topology > Physical Architecture tab, expand Oracle under Technologies. Select the physical data server created for HDM and double-click it. The Definition details for the HDM data server are displayed as shown in the following figure:

   Figure 3-8 Configuring the Physical Data Server

   
   Description of "Figure 3-8 Configuring the Physical Data Server"
   
   

2. Update Definition tab properties as follows:

   • Connection User name and Password: Provide the user name and password for connecting to the HDM schema.

   • Instance/ DBlink (data server): Enter NET_SERVICE_NAME. This is the TNS entry name of the HDM schema

3. Update the JDBC tab properties of the system containing HDM schema:

   • JDBC Driver: The field is automatically populated.

   • JDBC URL: Provide the host, port and SID of the HDWF schema.

     Figure 3-9 Configuring JDBC Properties of the Physical Data Server

     
     Description of "Figure 3-9 Configuring JDBC Properties of the Physical Data Server"
     
     

4. Click Test Connection to test whether the values are correct.

5. Click Save on the menu bar. The existing Physical Data Server ORACLE_HDM is updated.

6. Repeat steps 1 through 5 on the physical data servers ORACLE_TMP_DATAMART, ORACLE_APPS, and ORACLE_HDI.

3.3.1.5 Configuring the Physical Schema

To update the existing physical schema:

• Connect to the master repository and navigate to Topology > Technologies > Oracle.

  Figure 3-10 Configuring the Physical Schema

  
  Description of "Figure 3-10 Configuring the Physical Schema"
  
  

• Double-click ORACLE_HDM.HDWF, select Source schema name from the Schema and Work Schema drop-down lists.

• Repeat the above steps for ORACLE_TMP_DATAMART.CDM, ORACLE_APPS.APPS.

  
  

  Note: For further information on Topology configuration, see the following ODI guides: Oracle® Fusion Middle ware Getting Started with Oracle Data Integrator 11g Release 1 (11.1.1) Oracle® Fusion Middleware Developer's Guide for Oracle Data Integrator 11g Release 1 (11.1.1)

  
  

• Navigate to Topology > Oracle. Double-click Oracle. On the right side, verify that the Ordered option is deselected under Data Handling.

  Figure 3-11 Verifying Data Handling Setting

  
  Description of "Figure 3-11 Verifying Data Handling Setting"
  
  

3.3.1.6 Creating a Database Link

ODI requires a database link between the target CDM schema and the source (HDWF schema); Target APPS schema to source (HDWF Schema) to move data from the source to temporary tables created in the target.

For details, refer to ODI architecture in the Oracle® Data Integrator User's Guide.

To create a database link:

1. Copy the TNS string from the HDM tnsnames.ora in the HDWF schema to the tnsnames.ora of the OHSCE Data Mart Schema.

2. Repeat step 2 for APPS Schema.

3. Connect to the ODI Work Repository.

4. In ODI, navigate to Designer > Oracle Healthcare Analytics > Execution Plans > Initial Setup folder. The Initial Setup folder contains a Create DBLink with tasks Create HdmApps DBLink, Create HdmCdm DBLink packages.

5. Select each package and click Execute located in the tool bar menu.

The above steps require that both the HDM database user and the CDM database user reside on separate instances of the database.

In Exadata installations, all the schemas are located in the same database instance. The scripts for linking schemas does not work in this case. If you do not want the HDWF and Cohorts to reside on different databases, perform the following changes:

1. Check the global_names parameter in the database by executing the following command:

   show parameter global_names;

   If TRUE (in database or in SQL developer):

2. • Connect to the CDM schema and change the global_names parameter to FALSE (to session level) by executing the following command:

     Alter session set global_names = FALSE

   • Execute the following SQL statement manually:

     CREATE DATABASE LINK "HDM_DBLINK" CONNECT TO <HDWF schema name> IDENTIFIED BY VALUES <encrypted value of the HDWF schema password> USING <entire TNS string>

     
     

     Note: You can create the database link automatically from ODI to obtain the encrypted password and then copy it from its definition.

     
     

   • Test the database link in the SQL developer by executing the following command:

     "Select * from dual@<dblink_name>."

3.3.1.7 Configuring the Oracle Data Integrator Physical Agent

After successfully installing ODI, the ODI physical agent, LOCALHOST_20910, automatically appears. Modify the properties of the ODI agent as follows:

1. Select the Topology tab.

2. Navigate to Physical Architecture > Agents.

3. Double-click LOCALHOST_20910.

4. Edit the following:

   • Name: Enter a new name for the agent.

   • Host: Enter the host name where the ODI repository is installed.

   • Port: Enter a new port number or retain the default.

5. Save your changes. The new physical agent is created.

   
   Description of the illustration fig14.gif
   
   

3.3.1.8 Configuring a Standalone Agent

To configure a standalone agent, see the section Manually Configuring and Starting the Standalone Agent in the Oracle® Fusion Middleware Installation Guide for Oracle Data Integrator at the following location:

http://docs.oracle.com/cd/E28280_01/install.1111/e16453/configure.htm#ODING300

3.3.1.9 Security Guidelines for Oracle Health Sciences Cohort Explorer Oracle Data Integrator ETL Objects

The Cohort ETL objects consist of ODI Master Repository and the ODI Work Repository, which must be deployed in the ODI Server.

After deploying the ODI Master Repository, change all connection configurations as described in the Oracle® Health Sciences Cohort Explorer Installation Guide to point to the customer database connection parameters.

The ODI Work Repository contains only metadata for Cohort ODI ETLs. Since the metadata is used within the context of the ODI Server, follow the security guidelines applicable to the ODI Server while deploying these objects.

3.3.2.1 Installing Informatica ETLs

Perform the following steps to install Informatica ETLs:

Download the Informatica workflows wf_SIL_CDM.xml from the installation package and save it on your machine.

1. Log into the Power Center Administration Console (Server) and create a Repository Service with the name EHA_CDM.

   The default user name and password to login into Power Center Administration Console is Administrator.

2. After setting up the Informatica Server (such as, creating Repository Service and Integration Service), connect to the Repository Service (EHA_CDM) from your Informatica Client (Power Center Repository Manager).

   The default user name and password to connect to the Informatica Repository is Administrator.

3. In the Power Center Repository Manager, navigate to Folder > Create and create a new folder EHA_CDM_ETL (under the Informatica Repository created in step 2 and 3).

   Figure 3-12 Creating a Folder

   
   Description of "Figure 3-12 Creating a Folder"
   
   

4. Click OK.

5. In the Import Wizard dialog, navigate to the folder where the downloaded copy of the Informatica xml is placed. Select wf_SIL_CDM.XML and click Open.

   Figure 3-13 Importing Workflows

   
   Description of "Figure 3-13 Importing Workflows"
   
   

6. Click Add All to import all objects. Click Next.

7. For the Folder in XML file PowerCenter_slc04lx1:EHA_CDM_ETL_155, select EHA_CDM::EHA_CDM_ETL. Click Next.

8. Ensure that Check In is not selected. Click Next.

9. Click Next.

10. Reuse objects if any are unresolved while importing.

11. Once the import is successful in the above steps, navigate to the Informatica Power Center Workflow Manager and connect to the folder EHA_CDM_ETL.

12. Navigate to Connections > Relational and create the following Relational Connections:

    • ORACLE_HDWF to connect to schema containing HDWF tables.

      Figure 3-14 Creating Relational Connections

      
      Description of "Figure 3-14 Creating Relational Connections"
      
      

    • ORACLE_CDM to connect to schema containing CDM tables.

      Figure 3-15 Creating Relational Connections

      
      Description of "Figure 3-15 Creating Relational Connections"
      
      

    • ORACLE_APPS to connect to schema containing APPS tables.

      Figure 3-16 Creating Relational Connections

      
      Description of "Figure 3-16 Creating Relational Connections"
      
      

    Make a note of the connection objects.

13. Manually create a SIL_CDM_Global_Param_File.prm file with the following contents:

    [Global]
    $$CDM_INDEX_TS_NAME=<Table Space Name for CDM Indexes>
    $$DBSCHEMA_HDWF=<HDWF Schema Name>
    $$DBSCHEMA_CDM=<CDM Schema Name>
    $$DBSCHEMA_APPS=<APPS Schema Name>
    $DBConnection_HDWF=ORACLE_HDWF
    $DBConnection_CDM=ORACLE_CDM
    $DBConnection_APPS=ORACLE_APPS
    $$VPD_RAW=10
    

14. Copy the updated global parameter file to the following location:

    ${INFA_INSTALL_DIR}/server/infa_shared/SrcFiles

15. Navigate to the Informatica Power Center Workflow Manager.

    1. Connect to the repository and open the folder EHA_CDM_ETL.

    2. Expand the workflows section on the left-hand side and select or right-click on workflow wf_SIL_CDM_FULL to open.

    3. Navigate to Workflows > Edit.

    4. From the Integration Service browser, select and associate the Integration Service to the workflow (wf_SIL_CDM_FULL).

       Figure 3-17 Associating the Integration Service

       
       Description of "Figure 3-17 Associating the Integration Service"
       
       

    5. Repeat step 16 for incremental load workflow, that is, wf_SIL_CDM workflow. From the Integration Service browser, select and associate the Integration Service to the workflow wf_SIL_CDM.

    6. Save changes to the Repository.