Skip Headers
Oracle® Health Sciences Translational Research Center Installation Guide
Release 3.1
E66212-06
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
 
Next
Next
 

3 Installing the Oracle Health Sciences Cohort Explorer ETL

This section describes how to configure ETLs and related components on ODI and Informatica. This chapter contains the following topics:

3.1 Prerequisites

3.2 Configuring or Updating HDWF and CDM Schemas

A database link (ORCL_HDM_DBLINK) has been used for some ETL-related database procedures. Consequently, add a TNS entry for ORCL_HDM_ DBLINK in the tnsnames.ora file.

3.2.1 Installing the ODI or Informatica Script in HDWF Schema

The install_hdwf_script.sh script is located in the cdm_hdm_install folder. This script updates the HDWF 6.1 schema (ETL related objects) to successfully run the ETLs. Perform the following steps to install the ODI or Informatica script HDWF schema:


Note:

Make sure that HDWF 6.1 is installed before proceeding further.

  1. Execute install_hdwf_script.sh from the cdm_hdm_install folder with the following parameters.

    • cdm_schema - This is the CDM user name.

    • hdwf_schema - This is the HDWF user name.

    • cdm_db_conn - This is the database connection of the CDM schema.

    • hdwf_db_conn - This is the database connection of the HDWF schema.

    • ORCL_HDM_DBLINK - This is the service name created for the database link.

    • etl_mode - Enter the ETL mode, either ODI or INFA.

    For example,

    install_hdwf_script.sh -cdm_schema <<cdm schema user>> -hdwf_schema <<hdm schema user>> -cdm_db_conn <<sid>> - hdwf_db_conn <<hdwf sid>> - ORCL_HDM_DBLINK <<Service name created for DB link>> -etl_mode<<ODI/INFA>>
    

    Note:

    The above script prompts for the HDM and CDM schema password.

3.3 Installing Oracle Data Integrator ETLs for Oracle Health Sciences Translational Research Center 3.1

This section describes the steps to be performed to install ODI ETLs for TRC 3.1.

3.3.1 Installing an Oracle Data Integrator Repository

Perform the following steps for a new installation:

  1. Downloading Zipped Files from the Installation Package

  2. Installing Cohort ETLs for ODI

  3. Creating a Database Schema

  4. Creating an Oracle Data Integrator Master Repository

  5. Creating an Oracle Data Integrator Work Repository

  6. Configuring the Physical Data Server

  7. Configuring the Physical Schema

  8. Creating a Database Link

  9. Configuring the Oracle Data Integrator Physical Agent

  10. Configuring a Standalone Agent

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

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

3.3.1.2.1 Creating a Database Schema

Create one database schema for both the ODI master and Work repository.

For example,

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.2.2 Creating an Oracle Data Integrator Master Repository

Creating an ODI master repository involves the following three steps:

3.3.1.2.3 Master Repository Creation Wizard

Perform this step before you import the master repository.

  1. Click Master Repository Creation Wizard.

    Description of fig4.gif follows
    Description of the illustration fig4.gif

  2. Enter the following information:

    • Technology: Oracle

    • JDBC Driver: oracle.jdbc.OracleDriver

    • JDBC Url: jdbc:oracle:thin:@localhost:1521:orcl

    • User: Enter username

    • Password: Enter password.

    • DBA User: sys as sysdba

    • DBA Password: Enter sys user password.

    • Id: Enter the ID for the new repository.


      Note:

      Each repository ID must be unique. ODI does not permit importing a repository if its ID already exists.

      The repository zip files already contain the following IDs:

      0,10,33,95,100,123,150,305,308,999

      While creating a repository, provide IDs that are different from these.


    Description of fig5.gif follows
    Description of the illustration fig5.gif

  3. Click Next.

  4. Enter the authentication mode for your master repository.

    Description of fig6.gif follows
    Description of the illustration fig6.gif

  5. Click Next.

  6. Select Internal Password Storage.

    Description of fig7.gif follows
    Description of the illustration fig7.gif

  7. Click Finish. A confirmation message is displayed.

    Description of fig8.gif follows
    Description of the illustration fig8.gif

  8. Click OK.

3.3.1.2.4 Creating a New Oracle Data Integrator Master Repository Login

To create a new ODI repository login, perform the following steps:

  1. Open Oracle Data Integrator.

  2. Select File >New >Create a new ODI repository Login in the New Gallery window.

  3. Click OK.

    The Repository Connection Information window opens.

  4. Enter values for all the properties as described below.

    Oracle Data Integrator Connection details:

    Login name: Provide any value.

    User: The default value is Supervisor.

    Password: Provide any value.

    Database Connection (Master Repository) details:

    User: Provide the Master repository schema name in the database.

    Password: Provide the Master repository schema password.

    Driver List: Select Oracle JDBC Driver from the drop-down list.

    Driver Name: Auto-populated when you select the Driver list.

    URL: Use the search icon to get the URL format and provide the host, port and SID values of the master repository.

    The following figure displays the Repository Connection Information window:

    Figure 3-1 Repository Connection Information Window

    Description of Figure 3-1 follows
    Description of "Figure 3-1 Repository Connection Information Window"

  5. Select Master Repository Only.

  6. Test the connection.

  7. Click OK.

    A login name is created with the name provided.

3.3.1.2.5 Importing the Oracle Data Integrator Master Repository

Perform the following steps to import the ODI master repository:

  1. Open Oracle Data Integrator.

  2. Connect to the ODI master repository as SUPERVISOR using the password SUNOPSIS.

  3. Click OK. You are now connected to the master repository.

  4. Click the icon located on the right, as shown in the following screen.

    Description of fig9.gif follows
    Description of the illustration fig9.gif

  5. Click Import.

  6. Select Import the Master Repository.

    Description of fig10.gif follows
    Description of the illustration fig10.gif

  7. Select Import from Zip File and provide the path to import the master repository zip file.

    Description of fig11.gif follows
    Description of the illustration fig11.gif

  8. Click OK.

  9. .Click Close. The import report is displayed.

    Description of fig12.gif follows
    Description of the illustration fig12.gif

  10. Perform the instructions detailed from Section 3.3.1.3 onwards.

3.3.1.3 Creating an Oracle Data Integrator Work Repository

Creating an ODI work repository involves two steps:

3.3.1.3.1 Creating a New Oracle Data Integrator Work Repository

Perform the following steps to create a new ODI work repository:

  1. Disconnect and re-connect to the master repository as SUPERVISOR using the password SUNOPSIS.


    Note:

    Ensure that you use the above credentials to log in. The defined SUPERVISOR password is overwritten with the imported repository password. For more information, see Oracle Healthcare Analytics Data Integration Secure Installation and Configuration Guide Release 2.0.2 for Oracle Data Integrator.

  2. Select Topology > Repositories. Right-click Work repositories, and select New Work Repository.

    The Create Work Repository window is displayed.

    Figure 3-2 Create Work Repository Window

    Description of Figure 3-2 follows
    Description of "Figure 3-2 Create Work Repository Window"

  3. Select the following values for these fields:

    Technology: Oracle

    JDBC Driver: Use the search icon to select Oracle JDBC Driver. The syntax is populated automatically.

    JDBC URL: Use the search icon to get the URL format and provide the host, port and SID values of the work repository.

    User: Work repository schema user name

    Password: Work repository schema password

  4. Click Test Connection to verify whether the connection is successful. Click Next.

    Figure 3-3 Create Work Repository Window: Specifying ODI Work Repository Properties

    Description of Figure 3-3 follows
    Description of "Figure 3-3 Create Work Repository Window: Specifying ODI Work Repository Properties"

  5. Provide the necessary values for the fields in step 2 of the Create Work Repository window.

    ID: The Work Repository ID.


    Note:

    Each repository ID must be unique. ODI does not let you import a repository if its ID already exists.

    The repository zip files already contain the following IDs: 1, 2, 3, 4, 5, 6, 7, 8, 11, 18, 19, 66, 77, 99, 101, 111, 117, 122, 133, 137, 141, 167, 189, 222, 234, 235, 245, 249, 271, 287, 295, 307, 309, 343, 347, 348, 369, 371, 417, 472, 479, 500, 501, 502, 505, 506, 516, 532, 593, 600, 615, 631, 634, 636, 638, 642, 666, 668, 676, 738, 777, 786, 813, 817, 835, 858, 888, 892, 900, 912, 913, 915, 927, 987, 991, 992, 993, 994, 995, 996, 997, 998, 999. While creating a repository, provide IDs different from these.


    Name: Provide the name of the repository

    Password: Provide the password for the repository

    Work Repository Type: Select the type of the work repository

  6. Click Finish. The work repository is created successfully. You are prompted to create a login name for the work repository.

  7. Click Yes.

  8. Provide a login name for the work repository and click OK.

    Description of fig13.gif follows
    Description of the illustration fig13.gif

3.3.1.3.2 Importing the Oracle Health Sciences Cohort Explorer Work Repository

Perform the following steps to import the OHSCE Work Repository into ODI:

  1. Disconnect from the master repository.

  2. Re-connect to the work repository with the login name created in the previous step.

  3. Within the Designer tab, select the Connect Navigator icon.

    Select Import > Import the Work Repository as shown below.

    Figure 3-4 Import Selection

    Description of Figure 3-4 follows
    Description of "Figure 3-4 Import Selection"

    The Import Work Repository window opens.

  4. Select Import Mode as Synonym Mode Insert.

  5. Select Import from a zip File.

    Figure 3-5 Import Work Repository

    Description of Figure 3-5 follows
    Description of "Figure 3-5 Import Work Repository"

  6. Select the work repository zip file and click OK.

    While importing the work repository, you are prompted to declare different work repository numbers in the master repository as shown in Figure 3-6. Click OK for each one of them.

    Figure 3-6 Confirmation Window

    Description of Figure 3-6 follows
    Description of "Figure 3-6 Confirmation Window"

    The Import report is displayed.

  7. Click Close. The application is now installed successfully.

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 follows
    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:

  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 follows
    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 follows
    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):

    • 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 fig14.gif follows
    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 Installing Cohort ETLs for Informatica

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 follows
    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 follows
    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:

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

3.4 Installing ETLs for an Upgrade

3.4.1 Upgrading the Oracle Data Integrator Repository

Perform the steps in Section 3.3.1, "Installing an Oracle Data Integrator Repository" to upgrade your ODI repository.

3.4.2 Upgrading Informatica ETLs to 3.1

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

  1. Log into the Power Center Repository Manager.

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

  2. In the Power Center Repository Manager, navigate to Folder > Create and create a new folder EHA_CDM_ETL3.1 (or any valid name).

    Figure 3-18 Creating a Folder

    Description of Figure 3-18 follows
    Description of "Figure 3-18 Creating a Folder"

  3. Click OK.

  4. 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-19 Importing Workflows

    Description of Figure 3-19 follows
    Description of "Figure 3-19 Importing Workflows"

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

  6. For the Folder in XML file PowerCenter_slc04lx1::EHA_CDM_ETL_155, select EHA_CDM::EHA_CDM_ETL302. Click Next.

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

  8. Click Next.

  9. Reuse objects if any are unresolved while importing.

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

  11. Check the existing parameter file SIL_CDM_Global_Param_File.prm has following parameters or value defined:

    [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
    
  12. Global parameter file is placed in the following location:

    ${INFA_INSTALL_DIR}/server/infa_shared/SrcFiles

  13. Navigate to the Informatica Power Center Workflow Manager.

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

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

    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-20 Associating the Integration Service

      Description of Figure 3-20 follows
      Description of "Figure 3-20 Associating the Integration Service"

    5. Repeat step 14 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.