2 Data Management Assembly for Oracle Data Integrator Installation

This chapter describes how to install the OHF Data Management Assembly for Oracle Data Integration (ODI). There are two ways to install the Data Management Assembly for ODI, depending on how you create the user schemas. The installer can create the user schemas during the installation or you can create them manually, prior to the installation. The two methods are described below:

2.1 Installing the Data Management Assembly without Pre-Created User Schemas

  1. Check Prerequisites

  2. Prepare the Installer

  3. Run the Installer

  4. Check the Installation

  5. Create a New ODI Repository Login

2.1.1 Check Prerequisites

The user is familiar with Oracle Database (DB), ODI, and Linux OS.
The OHF Data Model is installed.

Follow the instructions in Chapter 1, "Data Model Installation" or Chapter 8, "Data Model Upgrade".
The Data Management Assembly installer is run on the system where the ODI server is installed.
Make sure that the database compatible parameter is set to 12.2.0.1.0 by connecting to the DBA user and running the query below: 
select * from v$parameter where name = 'compatible';

If the parameter is not set to 12.2.0.1.0, ask your database administrator to set it.
ODI services can connect to the Data Model 7.2.1 database mentioned in the Oracle TNS file (TNS entries of the required database must be available in the installation server tnsnames.ora file).
The password expiry notification message does not display for the system user.
The terminology loaders source file location exists. The installer creates an archive directory. For example,

/scratch/home/oemora/TL_Source is the source file location specified during installation, which already exists.

/scratch/home/oemora/TL_Archive_Files is created by the installer as the archive directory.
The path of the data file (Configuration schema/Repository schema tablespace) mentioned when creating the tablespace is correct. Make sure that the database user has write privileges.
Enough space is available in the installation directory and the Oracle Home directory.
The Sqlplus utility is available on the installation server.
The impdp utility is available on the repository database server.
The installation user has read, write, and execute privileges to the $ODI_HOME/odi/agent/bin folder. The installer creates product specific files under this location.
For remote installations, where the installation server and the ODI Repository Database server are different machines, make sure that:

  • A directory from the remote database server is mounted to the installation server with appropriate read and write privileges.

  • The remote directory is accessible from the installation server on mounting.

  • The user that owns Oracle db services on the remote server has privilege 755 for the directory that was mounted on the installation server.

  • If the database is on an Exadata machine, provide the database single node (the node which is mounted) as the host name when prompted during installation.
GLOBAL_NAMES database initialization parameter is set to false.
In case of an OHF upgrade, back up the following csv files under $ODI_HOME/odi/agent/bin if it exists:

  • bus_susp_day.csv

  • daylght_svngs_day.csv

  • hosp_hol_day.csv

  • time_odi.csv
In case of an OHF upgrade, if the Terminology Loaders source folder is shared, make a backup of the following files:

  • Code Axes.txt

  • Code Descriptions.txt

  • Code Hierarchy.txt

  • Code.txt

  • Related Entity.txt

  • Relations Type.txt

  • Relations.txt

  • Relationship Type.txt

  • time.txt

The installer will overwrite any existing files from the list above.

Note:

The installer auto-populates some of the user parameters and lets you edit them.

2.1.2 Prepare the Installer

Extract the contents of the OHF media pack to your system.
Navigate to the <media_pack_location>/ folder.
Unzip the OHF_721_Linux-x64.zip file where you want to launch the installer using the following command:

unzip -a OHF_721_Linux-x64.zip
Navigate to the Disk1/install folder.
Change the protection on files as follows:

chmod 755 *

2.1.3 Run the Installer

Start the Oracle Universal Installer by running the following command:

  • If the ODI repository schemas (master and work) to be created are on the database instance of the installation server, execute:

    sh runInstaller.sh -local

  • If the ODI repository database or OHF data model database is on the database instance of another server, execute:

    sh runInstaller.sh -local remote_installation=true

where, the -local option is to install on the local node irrespective of the cluster nodes specified on the installer machine.


 Screen Action
Welcome Click Next.
Select a Product to Install Select the Oracle Healthcare Foundation Data Management Assembly for ODI 7.2.1.0.0 option.
Specify Home Details Enter the installation home path.
Verify Installation Prerequisites Verify if all the prerequisites are met before proceeding.
Select the Oracle Home Configuration Specify the Oracle client home path.
Select the ODI Home Location Specify the ODI home location.

The ODI home should be one level above the /oracle_common directory. For example: /u01/app/oracle/Oracle_ODI1.
Select Database Server for ODI Repository Schemas Select one of the following options for the ODI repository schemas:

  • If the ODI repository database server is on the installation server, select the Installation database server option.

  • Else, select the Remote database server option.
Specify Mount Path Details (applicable only for remote installations) Enter the configuration details for the mounted remote server directory:

  • Mounted directory path on the remote server

  • Mount path on the installation server

To obtain the available storage drives, on the Linux machine, execute the df -h command. If the remote server directory is mounted on the installation server, the mounting is displayed as follows:

<Remote Server name>:<Remote server path>
        <total size>  <used up space>  <Available space>  <use%> <Path in installation server where mounting was done>

For example,

abc:/scratch/dump
         191G 138G 44G 76% /installation server

Note:

  • The directory of the remote database server should be mounted to the installation server with appropriate read and write privileges for the IMPDB utility.

  • If the remote server mounted path is displayed as /, provide the absolute mounted path of the remote server.
Specify Healthcare Data Model Database Instance Details Enter values for the following fields:

  • Host name - By default, the system host name appears. For remote installations, set this value to the host name of the remote machine.

  • Port number

  • Service name

  • System user password

  • Select this database instance for repository schema creation

    If you select Yes, the installer uses the same Data Model database instance for ODI repository schema creation.
Specify ODI Repository Database Instance Details (applicable only if you selected No in the previous screen for Select this database instance for repository schema creation) Enter values for the following fields:

  • Host name - By default, the system host name appears. For remote installations, set this value to the host name of the remote machine.

  • Port

  • Service name

  • System user password
Specify ODI Supervisor Password Specify the supervisor password used for the ODI console login.
Select Terminology Loaders Source Location Specify the Terminology loaders source file location.

This location is used to read the terminology loaders source data files. You can change this location when required.

The installer creates an archive directory at the same level as the source directory. Make sure that the create directory privileges exist for the installation user.
Specify Healthcare Data Model Schema Details Enter values for the following fields:

  • Interface table schema name

  • Interface table schema password

  • Data warehouse schema name

  • Data warehouse schema password

  • Common data mart (hcd) schema name

  • Common data mart (hcd) schema password

  • Cohort data mart (cdm) schema name

  • Cohort data mart (cdm) schema password
Specify Healthcare Data Model Schema Details Enter values for the following fields:

  • Enterprise schema name

  • Enterprise schema password.
Specify Terminology Loader Details Enter values for the following fields:

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

Specify the new schema names for the Master and Work repository. The installer creates the Master and Work repository schemas, and imports loaders into them.
Specify Warehouse Integration Loader Details Enter values for the following fields:

  • Configuration schema name

  • Configuration schema password

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer creates the configuration schema if it does not exist.

Specify the new schema names for the Master and Work repository. The installer creates the Master and Work repository schemas, and imports loaders into them.
Specify Healthcare Common Data Mart Loader Details Enter values for the following fields:

  • Configuration schema name

  • Configuration schema password

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer creates the configuration schema if it does not exist.

Specify the new schema names for the Master and Work repository. The installer creates the Master and Work repository schemas, and imports loaders into them.
Specify Healthcare Cohort Data Mart Loader Details Enter values for the following fields:

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

Specify the new schema names for the Master and Work repository. The installer creates the Master and Work repository schemas, and imports loaders into them.

The configuration schema is not required for CDM.
Specify RCU Prefix and Password Specify an RCU prefix to be appended to the schemas created by the ODI Repository Creation Utility (RCU). The RCU creates 3 schemas:

  • <RCU prefix>_STB

  • <RCU prefix>_WLS

  • <RCU prefix>_WLS_RUNTIME

Note: The RCU Prefix must be unique. The prefix should be alphabetic only. It cannot have special characters and cannot start with a number. The length of the prefix must not exceed 8 characters.

Specify a password for schemas created using the ODI Repository Creation Utility (RCU).

Note: The password must be between 8 and 12 alphanumeric characters long and must include at least one number. The password cannot start with a number.
Specify the Tablespace Details Specify the tablespace names for the configuration schemas. The installer creates these tablespaces if they do not exist.

  • Configuration schema default tablespace name

  • Configuration schema temporary tablespace name
Specify the Tablespace Details Specify the tablespace names for repository schemas:

  • wil_odi_ts - Default tablespace for ODI temporary objects for Warehouse Integration Loaders

  • hcd_odi_ts - Default tablespace for ODI temporary objects for Healthcare Common Data mart Loaders

  • cdm_odi_ts - Default tablespace for ODI temporary objects for Cohort Data mart Loaders

  • tl_odi_ts - Default tablespace for ODI temporary objects for Terminology Loaders
Specify Tablespace for Creation of ODI Temporary Objects Specify tablespace names for the creation of temporary objects used by the ODI Loaders. The installer creates these tablespaces if they do not exist.

  • Warehouse Integration Loaders

  • Healthcare Common Data mart Loaders

  • Cohort Data mart Loaders

  • Terminology Loaders

It is recommended to provide different tablespace names for each component.
Specify Tablespace Location for Configuration Schemas Specify the tablespace location for configuration schemas.

The location should be present in the data model database server with write privileges.

If the OHF data model database is not on the installation server, you must enter the location manually.
Specify Tablespace Location for Repository Schemas Specify the tablespace location for the ODI repository schema.

The location should be present in the repository database server with write privileges.

If the repository database is not on the installation server, you must enter the location manually.
Verify Configuration Parameters Click Next.
Summary Click Install.
End of Installation Click Exit after reviewing the installation information. At the confirmation prompt, click Yes to exit the installer.

2.1.4 Check the Installation

Review the generated installation log files for errors. For details, see Installation Log Files.
Contact Oracle support, if necessary, to resolve any errors.

2.1.5 Create a New ODI Repository Login

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

Navigate to ODI > File > New > Create a New ODI Repository Login.
Click OK. The Repository Connection Information screen is displayed.
Enter the following values:

  • Login Name - For example, WIL_REPOSITORY_LOGIN

  • User - SUPERVISOR

  • Password - Provide the ODI Login password entered during installation (see Run the Installer)

  • User - <database schema created for the master repository>

  • Password - <database schema password created for the master repository>

  • Driver List - Select OracleJDBC Driver from the drop-down list

  • Driver Name - oracle.jdbc.oracledriver

  • Url - Set appropriate values based on your database details

  • Work Repository - Select the Work Repository option, browse to select the work repository shown (for example, for Warehouse Integration loader, select WIL_WORK_REPOSITORY), and click OK.
Click OK. The login name is created with the name specified at the previous step.
Navigate to ODI > Connect > ODI Studio.
Enter the following details:

  • Login Name - Select WIL_REPOSITORY_LOGIN

  • User - Supervisor

  • Password - Provide the ODI login password that was entered during installation (see "Run the Installer").

Similarly, follow the above steps to create the login for the Terminology loaders, Common Data Mart loader, and Cohort Data Mart loader.

2.2 Installing the Data Management Assembly with Pre-Created User Schemas

  1. Check Prerequisites

  2. Create Default and Temporary Tablespaces

  3. Create User Schemas

  4. Prepare the Installer

  5. Run the Installer

  6. Check the Installation

  7. Revoke Privileges

2.2.1 Check Prerequisites

The user is familiar with Oracle Database (DB), ODI, and Linux OS.
The OHF Data Model is installed.

Follow the instructions in Chapter 1, "Data Model Installation" or Chapter 8, "Data Model Upgrade".
The Data Management Assembly installer is run on the system where the ODI server is installed.
Make sure that the database compatible parameter is set to 12.2.0.1.0 by connecting to the DBA user and running the query below: 
select * from v$parameter where name = 'compatible';

If the parameter is not set to 12.2.0.1.0, ask your database administrator to set it.
ODI services can connect to the Data Model 7.2.1 database mentioned in the Oracle TNS file (TNS entries of the required database must be available in the installation server tnsnames.ora file).
The password expiry notification message does not display for the pre-created schemas or system user.
The terminology loaders source file location exists. The installer creates an archive directory. For example,

/scratch/home/oemora/TL_Source is the source file location specified during installation, which already exists.

/scratch/home/oemora/TL_Archive_Files is created by the installer as the archive directory.
The path of the data file (Configuration schema/Repository schema tablespace) mentioned when creating the tablespace is correct. Make sure that the database user has write privileges.
Enough space is available in the installation directory and the Oracle Home directory.
The Sqlplus utility is available on the installation server.
The impdp utility is available on the repository database server.
The installation user has read, write, and execute privileges to the $ODI_HOME/odi/agent/bin folder. The installer creates product specific files under this location.
For remote installations, where the installation server and the ODI Repository Database server are different machines, make sure that:

  • A directory from the remote database server is mounted to the installation server with appropriate read and write privileges.

  • The remote directory is accessible from the installation server upon mounting.

  • The user that owns Oracle DB services on the remote server has privilege 755 for the directory that has been mounted on the installation server.

  • If the database is on an Exadata machine, provide the database single node (the node which is mounted) as the host name when prompted during installation.
GLOBAL_NAMES database initialization parameter is set to false.
In case of an OHF upgrade, back up the following csv files under $ODI_HOME/odi/agent/bin if it exists:

  • bus_susp_day.csv

  • daylght_svngs_day.csv

  • hosp_hol_day.csv

  • time_odi.csv
In case of an OHF upgrade, if the Terminology Loaders source folder is shared, make a backup of the following files:

  • Code Axes.txt

  • Code Descriptions.txt

  • Code Hierarchy.txt

  • Code.txt

  • Related Entity.txt

  • Relations Type.txt

  • Relations.txt

  • Relationship Type.txt

  • time.txt

The installer will overwrite any existing files from the list above.

Note:

The installer auto-populates some of the user parameters and lets you edit them.

2.2.2 Create Default and Temporary Tablespaces

Create the following default and temporary tablespaces with appropriate quotas. You can use different tablespace names than the ones listed below.

Note:

The hmc_ts and hmc_temp tablespaces must be created in the Data Model database instance.

 Tablespace Name Big File Tablespace Description
odirep_ts Yes Default tablespace for the Repository schema.
odirep_temp Yes Temporary tablespace for the Repository schema.
hmc_ts Yes Default tablespace for the Configuration schema.
hmc_temp Yes Temporary tablespace for the Configuration schema.
wil_odi_ts Yes Default tablespace for ODI temporary objects for Warehouse Integration loaders.
hcd_odi_ts Yes Default tablespace for ODI temporary objects for Healthcare Common Data mart loaders.
cdm_odi_ts Yes Default tablespace for ODI temporary objects for Cohort Data mart loaders.
tl_odi_ts Yes Default tablespace for ODI temporary objects for terminology loaders.

2.2.3 Create User Schemas

Create the following user schemas and assign the appropriate default and temporary tablespaces. Refer to Create Default and Temporary Tablespaces for details. If you created tablespaces with different names, use those instead.


 Schema Name Schema Description Default Tablespace Temporary Tablespace
wil_hmc Warehouse Integration Loader Configuration schema hmc_ts hmc_temp
hcd_hmc Healthcare Common Data Mart Configuration schema hmc_ts hmc_temp
tl_master_rep_7 21 Terminology Loader Master Repository schema odirep_ts odirep_temp
tl_work_rep_7 21 Terminology Loader Work Repository schema odirep_ts odirep_temp
wil_master_rep_721 Warehouse Integration Loader Master Repository schema odirep_ts odirep_temp
wil_work_rep_721 Warehouse Integration Loader Work Repository schema odirep_ts odirep_temp
hcd_master_rep_721 Healthcare Common Data Mart Master Repository schema odirep_ts odirep_temp
hcd_work_rep_721 Healthcare Common Data Mart Work Repository schema odirep_ts odirep_temp
cdm_master_rep_721 Healthcare Cohort Data Mart Master Repository schema odirep_ts odirep_temp
cdm_work_rep_721 Healthcare Cohort Data Mart Work Repository schema odirep_ts odirep_temp

2.2.4 Prepare the Installer

Extract the contents of the OHF media pack to your system.
Navigate to the <media_pack_location>/ folder.
Unzip the OHF_721_Linux-x64.zip file where you want to launch the installer using the following command:

unzip -a OHF_721_Linux-x64.zip
Navigate to the <media_pack_location>/Disk1/stage/Components/oracle.hsgbu.hc.dma.odi/7.2.1.0.0/1/DataFiles/Expanded/filegroup1 directory.
Unzip the dma_odi_master_install.zip file.
Navigate to the dma_odi_master_install/hmc_odi_install/ directory.
Connect to the system user and execute the following scripts:

  • @odi_hdi_user_privilege.sql <HDI_SCHEMA_NAME>

  • @odi_hmc_user_privilege.sql <WIL_HMC_SCHEMA_NAME> <HCD_HMC_SCHEMA_NAME>

  • @odi_rep_user_privilege.sql <HLI_MASTER_REP_USR> <WIL_MASTER_REP_USR> <AT_MASTER_REP_USR> <HLI_WORK_REP_USR> <WIL_WORK_REP_USR> <AT_WORK_REP_USR> <CDM_MASTER_REP_USR> <CDM_WORK_REP_USR>
Navigate to the <media_pack_location>/Disk1/install folder.
Change the protection on files as follows:

chmod 755 *

2.2.5 Run the Installer

Start the Oracle Universal Installer by running the following command:

  • If the ODI repository schemas (master and work) to be created are on the database instance of the installation server, execute:

    sh runInstaller.sh -local dba_tasks=false

  • If the ODI repository database or OHF data model database is on the database instance of another server, execute:

    sh runInstaller.sh -local remote_installation=true dba_tasks=false

where, the -local option is to install on the local node irrespective of the cluster nodes specified on the installer machine.


 Screen Action
Welcome Click Next.
Select a Product to Install Select the Oracle Healthcare Foundation Data Management Assembly for ODI 7.2.1.0.0 option.
Specify Home Details Enter the installation home path.
Verify Installation Prerequisites Verify if all the prerequisites are met before proceeding.
Select the Oracle Home Configuration Specify the Oracle client home path.
Select the ODI Home Location Specify the ODI home location.

The ODI home should be one level above the /oracle_common directory. For example, /u01/app/oracle/Oracle_ODI1.
Select Database Server for ODI Repository Schemas Select one of the following options for the ODI repository schemas:

  • If the ODI repository database server is on the installation server, select the Installation database server option.

  • Else, select the Remote database server option.
Specify Mount Path Details (applicable only for remote installations) Enter the configuration details for the mounted remote server directory:

  • Mounted directory path on the remote server.

  • Mount path on the installation server.

To obtain the available storage drives, on the Linux machine, execute the df -h command. If the remote server directory is mounted on the installation server, the mounting is displayed as follows:

<Remote Server name>:<Remote server path>
        <total size> <used up space> <Available space> <use%> <Path in installation server where mounting was done>

For example:

abc:/scratch/dump
        191G 138G 44G 76% /installation server

Note:

  • The directory of the remote database server should be mounted to the installation server with appropriate read and write privileges for the IMPDB utility (folder with dba group).

  • If the remote server mounted path is displayed as /, provide the absolute mounted path of the remote server.
Specify Healthcare Data Model Database Instance Details Enter values for the following fields:

  • Host name - By default, the system host name appears. For remote installations, set this value to the host name of the remote machine.

  • Port number

  • Service name

  • Select this database instance for repository schema creation

    If you select Yes, the installer uses the same Data Model database instance for ODI repository schema creation.
Specify ODI Repository Database Instance Details (applicable only if you selected No in the previous screen for Select this database instance for repository schema creation) Enter values for the following fields:

  • Host name - By default, the system host name appears. For remote installations, set this value to the host name of the remote machine.

  • Port

  • Service name
Specify ODI Supervisor Password Specify the supervisor password used for the ODI console login.
Select Terminology Loaders Source Location Specify the Terminology loaders source file location.

This location is used to read the terminology loaders source data files. You can change this location when required.

The installer creates an archive directory at the same level as the source directory. Make sure that the create directory privileges exist for the installation user.
Specify Healthcare Data Model Schema Details Enter values for the pre-created schemas:

  • Interface table schema name

  • Interface table schema password

  • Data warehouse schema name

  • Data warehouse schema password

  • Common data mart (hcd) schema name

  • Common data mart (hcd) schema password

  • Cohort data mart (cdm) schema name

  • Cohort data mart (cdm) schema password
Specify Healthcare Data Model Schema Details Enter values for the pre-created schemas:

  • Enterprise schema name

  • Enterprise schema password.
Specify Terminology Loader Details Enter values for the pre-created schemas:

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer imports loaders into the pre-created terminology loaders repository schemas.
Specify Warehouse Integration Loader Details Enter values for the pre-created schemas:

  • Configuration schema name

  • Configuration schema password

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer imports loaders into the pre-created warehouse integration loaders repository schemas.
Specify Healthcare Common Data Mart Loader Details Enter values for the pre-created schemas:

  • Configuration schema name

  • Configuration schema password

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer imports loaders into the pre-created common data mart loaders repository schemas.
Specify Healthcare Cohort Data Mart Loader Details Enter values for the pre-created schemas:

  • Master repository schema name

  • Master repository schema password

  • Work repository schema name

  • Work repository schema password

The installer imports loaders into the pre-created cohort data mart loaders repository schemas.

The configuration schema is not required for CDM.
Specify RCU Prefix and Password Specify an RCU prefix to be prepended to the schemas created by the ODI Repository Creation Utility (RCU). The RCU creates 3 schemas:

  • <RCU Prefix>_STB

  • <RCU Prefix>_WLS

  • <RCU Prefix>_WLS_RUNTIME

Note: The RCU Prefix must be unique. The prefix should be alphabetic only. It cannot have special characters and cannot start with a number. The length of the prefix must not exceed 8 characters.

Specify a password for schemas created using the ODI Repository Creation Utility (RCU).

Note: The password must be between 8 and 12 alphanumeric characters long and must include at least one number. The password cannot start with a number.
Specify Tablespace for Creation of ODI Temporary Objects Specify tablespace names for the creation of temporary objects used by the ODI loaders. The installer creates the following tablespaces if they do not exist:

  • Warehouse Integration Loaders

  • Healthcare Common Datamart Loaders

  • Cohort Datamart Loaders

  • Terminology Loaders

It is recommended to provide different tablespace names for each component.
Verify Configuration Parameters Click Next.
Summary Click Install.
End of Installation Click Exit after reviewing the installation information. At the confirmation prompt, click Yes to exit the installer.

2.2.6 Check the Installation

Review the generated installation log files for errors. For details, see Installation Log Files.
Contact Oracle support, if necessary, to resolve any errors.

2.2.7 Revoke Privileges

After the Data Management Assembly for ODI installation, run the following scripts from a user with DBA privileges:

@<OHF_Install_Home>/dma_odi_master_install/hmc_odi_install/dma_directory_drop.sql

@@<OHF_Install_Home>/dma_odi_master_install/hmc_odi_install/dma_revoke_privilege.sql <WIL_MASTER_REP_USR> <HCD_MASTER_REP_USR> <CDM_MASTER_REP_USR> <HLI_MASTER_REP_USR>

where,

<OHF_Install_Home> - OHF installation home directory

<WIL_MASTER_REP_USR> - Warehouse integration loader master repository schema name

<HCD_MASTER_REP_USR> - Healthcare common data mart loader master repository schema name

<CDM_MASTER_REP_USR> - Cohort data mart loader master repository schema name

<HLI_MASTER_REP_USR> - Terminology loader master repository schema name