4 Installing and Configuring Oracle BI Applications on Windows

This chapter provides instructions for installing and configuring the Oracle Business Intelligence Applications components in the Windows environment. It contains the following main topics:

• Section 4.1, "Installation and Configuration Process Task List for Windows"

• Section 4.2, "How to Install the Java SDK"

• Section 4.3, "How to Install Oracle Business Intelligence Applications (Windows)"

• Section 4.4, "How to Install Informatica PowerCenter and Required Patches (Windows)"

• Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server"

• Section 4.6, "How to Set Environment Variables on Windows"

• Section 4.7, "How to Copy Hibernate Libraries"

• Section 4.8, "How to Configure the DAC Client"

• Section 4.9, "How to Create the Required Databases"

• Section 4.10, "How to Create the DAC Metadata Repository"

• Section 4.11, "How to Configure the DAC Metadata Repository"

• Section 4.12, "How to Configure the Oracle Business Analytics Warehouse Database"

• Section 4.13, "How to Register Informatica Servers in the DAC Client"

• Section 4.14, "How to Configure and Manage the Informatica Repository"

• Section 4.15, "How to Set the Code Page File Property for Unicode Environments on Teradata Databases"

• Section 4.16, "How to Set Up the HOSTS File for Teradata Installations on Windows"

• Section 4.17, "How to Verify the Informatica Repository Configuration"

• Section 4.18, "How to Configure the DAC Server"

• Section 4.19, "How to Start the Informatica Server"

• Section 4.21, "How to Create Stored Procedures for DB2-UDB"

• Section 4.22, "Teradata-Specific Installation and Configuration Checklist (Windows)"

• Section 4.23, "Upgrade Issues for Oracle BI Applications"

4.1 Installation and Configuration Process Task List for Windows

The Oracle BI Applications installation and configuration process consists of the following tasks. Complete each of these tasks in the order listed below.

1. Install Java SDK (if not already installed as part of the Oracle BI infrastructure installation (for more information, see Section 4.2, "How to Install the Java SDK".

2. Install Oracle Business Intelligence Applications (for more information, see Section 4.3, "How to Install Oracle Business Intelligence Applications (Windows)").

3. Install Informatica PowerCenter (for more information, see Section 4.4, "How to Install Informatica PowerCenter and Required Patches (Windows)").

4. Set up the Informatica components (for more information, see Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server"):

   1. Section 4.5.2, "How to Set Up Informatica Data Code Pages Validation".

   2. Section 4.5.3, "How to Set Up The Informatica Server".

   3. Section 4.5.4, "How to Set Up The Informatica Repository Server".

   4. Section 4.5.6, "How to Start the Informatica Services".

5. Set the Windows environment variables (for more information, see Section 4.6, "How to Set Environment Variables on Windows").

6. Install the required Hibernate libraries (for more information, see Section 4.7, "How to Copy Hibernate Libraries").

7. Configure the DAC Client (for more information, see Section 4.8, "How to Configure the DAC Client"):

   1. Section 4.8.1, "How to Configure the DAC Config.bat File".

   2. Section 4.8.2, "How to Install JDBC Drivers for DAC Database Connectivity".

8. Creating the required OLTP and OLAP databases (for more information, see Section 4.9, "How to Create the Required Databases").

9. Create the DAC Metadata Repository (for more information, see Section 4.10, "How to Create the DAC Metadata Repository").

10. Configure the DAC Metadata Repository (for more information, see Section 4.11, "How to Configure the DAC Metadata Repository"):

    1. Section 4.11.1, "How to Set Up DAC System Properties".

    2. Section 4.11.2, "How to Specify Transactional and Data Warehouse Data Sources in the DAC".

    3. Section 4.11.3, "How to Configure Email Recipients in the DAC Client".

    4. Section 4.11.4, "How to Activate Join Indices for Teradata Databases".

11. Configure the Oracle Business Analytics Warehouse (for more information, see Section 4.12, "How to Configure the Oracle Business Analytics Warehouse Database"):

    1. Section 4.12.1, "How to Create the SSE Role".

    2. Section 4.12.2, "How to Create Data Warehouse Tables".

    3. Section 4.12.3, "How to Create Data Warehouse Tables on a Teradata Database".

12. Register the Informatica servers in the DAC (for more information, see Section 4.13, "How to Register Informatica Servers in the DAC Client").

13. Configure the Informatica Repository (for more information, see Section 4.14, "How to Configure and Manage the Informatica Repository"):

    1. Section 4.14.2, "How to Register the Informatica Repository Server".

    2. Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica".

    3. Section 4.14.4, "How to Configure the Informatica Repository in Workflow Manager".

    4. Section 4.14.5, "How to Stop and Start the Informatica Repository (Optional)".

    5. Section 4.14.6, "How to Back Up and Restore the Informatica Repository (Optional)".

14. Verify the Informatica Repository configuration (for more information, see Section 4.17, "How to Verify the Informatica Repository Configuration").

15. Start the Informatica Server (for more information, see Section 4.19, "How to Start the Informatica Server").

    
    

    Note: You must perform the following steps for each source database type.

    
    

16. Configure the DAC Server (for more information, see Section 4.18, "How to Configure the DAC Server"):

    1. Section 4.18.1, "How to Configure the Connection Between the DAC Server and DAC Repository".

    2. Section 4.18.2, "How to Configure Email in the DAC Server (Optional)".

    3. Section 4.18.3, "How to Set DAC Source System Parameters".

    4. Section 4.18.4, "How to Start the DAC Server".

17. Update the transactional database schema definitions (for more information, see Section 4.20, "How to Update Siebel Transactional Database Schema Definitions"):

    1. Section 4.20.2, "How to Apply Siebel (CRM) and Oracle E-Business Suite Schema Changes For Oracle, DB2/UDB, MSSQL".

    2. Section 4.20.3, "How to Apply CRM Schema Changes For DB2 on 390 or z/os".

    3. Section 4.20.4, "How to Verify the Siebel (CRM) and Oracle E-Business Suite Schema Changes".

    4. Section 4.20.5, "About Delete Triggers".

Other database specific tasks:

• If you are using Teradata as a target OLAP database, set the Code Page File Property (for more information, see Section 4.15, "How to Set the Code Page File Property for Unicode Environments on Teradata Databases").

• If you are using Teradata as a target OLAP database, set up the HOSTS file (for more information, see Section 4.16, "How to Set Up the HOSTS File for Teradata Installations on Windows").

• If you are using Teradata as a target OLAP database, perform the Teradata-specific configuration tasks (for more information, see Section 4.22, "Teradata-Specific Installation and Configuration Checklist (Windows)".

• If you are using DB2-UDB, create stored procedures (for more information, see Section 4.21, "How to Create Stored Procedures for DB2-UDB").

4.2 How to Install the Java SDK

This step can be optional or mandatory, depending on where you want to install the DAC server and the DAC client. Java SDK must be installed on the machines where both the DAC server and DAC client are installed. If a machine has the Oracle Business Intelligence infrastructure installed, the Java SDK will already be installed on that machine.

If required, you can have other versions of SDK installed on the same server to support other applications. For example, you might have Java SDK version 1.5 installed for the DAC, and Java SDK version 1.4.2 installed for a different application that is not supported on Java SDK version 1.5.

Note: For information about which version of the Java SDK is supported, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

To install Java SDK

1. Download the Java SDK from the Sun Web site, and install it on the machine where both the DAC server and DAC client are installed.

   For example (on Windows), C:\j2sdk1.5.

   
   

   Note: Make sure that there are no spaces in the directory path. If there are spaces in the directory path, the DAC will not start (the \OracleBI\DAC\startclient.bat file will fail).

   
   

4.3 How to Install Oracle Business Intelligence Applications (Windows)

This section explains how to install the Oracle BI Applications files using the Oracle BI Applications Installation Wizard, and contains the following sections:

• Section 4.3.1, "Prerequisites to Running The Oracle Business Intelligence Applications Installation Wizard"

• Section 4.3.2, "How to Run The Oracle Business Intelligence Applications Installation Wizard"

4.3.1 Prerequisites to Running The Oracle Business Intelligence Applications Installation Wizard

Before you install Oracle Business Intelligence Applications on a machine, you must first install the Oracle Business Intelligence infrastructure on that machine. For instructions on installing the Oracle Business Intelligence infrastructure, see Oracle Business Intelligence Platform Installation and Configuration Guide.

If you are using Oracle client software for connection to an Oracle database, you must make sure that the Oracle client software has been installed before the latest verified version of Java SDK is installed when you install Oracle Business Intelligence. If you install the Oracle client software after Oracle Business Intelligence, you might change the Java SDK version for Oracle Business Intelligence, which might result in runtime errors in Oracle Business Intelligence.

4.3.2 How to Run The Oracle Business Intelligence Applications Installation Wizard

When you run the Oracle BI Applications Installation Wizard, the Oracle BI Applications files are installed into the existing Oracle BI infrastructure directory (for example, <DRIVE>:\OracleBI\).

Note: During the installation, if you are prompted to create or upgrade the DAC repository, choose Yes.

To install Oracle BI Applications on Windows

1. Access the installation files on the installation CD-ROM, and then run the program setup.exe to display the Welcome page.

   
   
   
   

   Note: To run the installer in console (or text) mode, run the command setup.exe -console. You do not see following dialogue screens in console installation mode. Instead, you enter input as plain text in the terminal window when prompted.

   
   

2. Click Next to display the Oracle BI infrastructure and Java SDK directory location page.

   
   

3. Enter or browse for the location for the Oracle BI infrastructure (for example, <DRIVE>:\OracleBI\), the Oracle BI infrastructure data (for example, <DRIVE>:\OracleBIData\), and the Java SDK (for example, <DRIVE>:\jdk1.5).

4. Click Next to display the Select Oracle Business Intelligence Applications page.

5. At the Select Oracle Business Intelligence Applications page, select the applications that you want to install, then click Next.

   
   

   Once you have installed applications, you can deploy one or more of them selectively when you create the DAC metadata repository in a later step (for more information, see Section 4.10, "How to Create the DAC Metadata Repository").

6. If you selected more than one application on the Select Oracle Business Intelligence Applications page, click Next at the informational page.

   
   

7. At the summary page, review the summary information and click Next to start the installation.

   
   

   The installer installs the Oracle BI Applications directories and files in the Oracle BI infrastructure installation (for example, <DRIVE>:\OracleBI\), and installs the RPD Repository.

   
   

   Note: Even when the progress bar on the installer reports 100% complete, you must wait until the Finish button is displayed.

   
   

8. Click Finish.

   
   

   Tip: Look in the <DRIVE>:\OracleBI\Document\version_apps.txt file in the Oracle BI infrastructure directory to check that you have installed the correct version of Oracle BI Applications.

   
   

4.4 How to Install Informatica PowerCenter and Required Patches (Windows)

To install Informatica PowerCenter:

1. Install Informatica PowerCenter (for more information, see Section 4.4.1, "How to Install Informatica PowerCenter").

2. Install Informatica EBF Patch 2 (for more information, see Section 4.4.2, "How to Install the Informatica EBF Patch 2").

3. If you are using a Teradata database, install Informatica Patch 1 (for more information, see Section 4.4.3, "(Teradata specific) How to Install Informatica Patch1").

For a list of the main files that are installed, see Section 5.3, "How to Install Oracle BI Applications on UNIX".

If you first want to find out more about the versioned Informatica repository that is included in Oracle Business Intelligence Applications, see Section 4.14.1, "About the Versioned Informatica Repository").

4.4.1 How to Install Informatica PowerCenter

Follow this procedure to install Informatica PowerCenter. The

The table below lists the default administrator usernames and passwords for Informatica:

Table 4-1 Default Informatica Usernames and Passwords

Component	Username	Password
Informatica Repository Server	Administrator	Administrator
Informatica Server	Administrator	Admin

Note: Even if you decide to run the Informatica server and the DAC server on two separate machines, you must still install Informatica on the DAC server machine, because the DAC server requires the Informatica libraries.

To install Informatica PowerCenter on Windows

1. Access the installation files on the installation CD-ROM, and locate the appropriate operating system directory.

   For example, for Windows, use the directory Informatica_7.1.4_PowerCenter_CD_for_Windows_and_Linux.

   
   

   Note: The Informatica installation directory is located in the \ThirdPartySoftware directory on the Oracle BI Applications Installation CD.

   
   

2. Run the program launch.exe.

3. The installation wizard window appears and prompts you through each screen, as shown in the following table.

   To continue to the next screen, click Next. To return to a previous screen, click Back.

   Screen	Your Action	Notes
   Welcome	Click Next.
   Customer Information	Enter the user name, company name, and Product license key. Note: Do not enter the Connectivity license key or Options license key during the product installation.	The Informatica license provides three different license keys: Product. Allows you to install the Informatica Server and Repository Server. Enter during the product installation and the procedures Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server" and Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica". Connectivity. Allows you to access sources and targets. Enter during the product installation and the procedures Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server" and Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica". Options. This Team Based Development License key allows you to access specific options, such as Team-Based Development, Server Grid, and Partitioning. Enter during the product installation and the procedures Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server" and Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica".
   License Agreement	Select "I accept..." and click Next.
   Select Components and Destination directory	Select the following components: Informatica PowerCenter 7.1.4 OEM for Oracle - Client Informatica PowerCenter 7.1.4 OEM for Oracle - Server Informatica PowerCenter 7.1.4 OEM for Oracle - Repository Server Browse for and select the appropriate destination directory, and then click Next.
   Program Folder	Select a program folder or accept the default, and then click Next.	Note: If you are using a Teradata database, make sure there are no spaces in the Informatica Server directory name or the directory path. The default directory contains spaces that you must remove manually.
   Review Settings	Review the selected components and destination directory information, and then click Next.	The Informatica components are now installed on the machine.
   Setup	Click OK.	This screen informs you that you must specify Productivity and Options license keys in later steps.
   Setup Type	Remove the check marks from the Configure Informatica Server and Configure Informatica Repository Server check boxes. (Optional) Select the View Readme check box to view the Read Me file. Click Next.	You will configure the Informatica Server and Repository Server in later steps.
   Wizard Complete	Click Finish.

   
   
   
   

   Note: Depending on how your Windows machine is set up, you might need to start the Informatica services manually (for more information, see Section 4.5.6, "How to Start the Informatica Services").

   
   

4.4.2 How to Install the Informatica EBF Patch 2

Follow this procedure to install the required Informatica patches.

To install the Informatica patches on Windows

1. Locate the \Third_Party_Software\Informatica\ directory on the Oracle BI Applications CD-ROM.

2. Locate the directory named Patch2, and install the patch by following the instructions in the EBF108240.html readme file.

   
   

   Note: The instructions provided in the EBF108240.html file reference the regedit path for Informatica PowerCenter 7.1.4 on Windows machines as: \hkey_local_machine\system\currentcontrolset\services\powermart\configuration Depending on your machine and Informatica 7.1.4 installation, the path might be: \hkey_local_machine\system\currentcontrolset\services\powermart\parameters\configuration

   
   

4.4.3 (Teradata specific) How to Install Informatica Patch1

If you are using Teradata as the target database, locate the directory named Patch1 and install the patch by following the instructions in the Readme.doc file.

4.5 How to Set Up the Informatica Server and Informatica Repository Server

After the Informatica Server is installed, you must configure the Informatica components by following the steps in this section.

You must configure Informatica Server and Informatica Repository Server before you can start them, by doing the following:

• Set up the Informatica Data Code Pages. For more information, see Section 4.5.2, "How to Set Up Informatica Data Code Pages Validation".

• Set up the Informatica Server in Informatica PowerCenter. For more information, see Section 4.5.3, "How to Set Up The Informatica Server".

• Set up the Informatica Repository Server in Informatica PowerCenter. For more information, see Section 4.5.4, "How to Set Up The Informatica Repository Server".

• Copy source files and lookup files from the Oracle BI Applications installation directory to the Informatica directory. For more information, see Section 4.5.5, "How to Copy Source Files and Lookup Files".

• After you have created the Informatica repository (for more information, see Section 4.14, "How to Configure and Manage the Informatica Repository"), start the Informatica Services. For more information, see Section 4.5.6, "How to Start the Informatica Services".

• Create the required ODBC Database Connections. For more information, see Section 4.5.7, "How to Create ODBC Database Connections".

• If you need to migrate the Informatica repository, you must initialize the Informatica sequence generator for incremental runs (for more information, see Section 4.5.1, "About migrating the Informatica repository").

Make note of the settings you enter in the Server tab and Repository tab of the Configure Informatica Service window; you will need to enter the same settings in other configuration steps.

Tip: Keep a record of the Informatica Server details that you specify here, and use the same details when you register the Informatica servers in the DAC (for more information, see Section 4.13, "How to Register Informatica Servers in the DAC Client").

Note: Throughout this guide, the name Oracle_BI_DW_Base is used for the Informatica repository (the file \dwrep\Informatica\Repository\Oracle_BI_DW_Base.rep). However, you can use a different name if required, and have more than one version of this file if you have more than one development environment.

4.5.1 About migrating the Informatica repository

If you migrate the Informatica repository from one environment to another environment (for example, from test to production), you need to initialize the Informatica sequence generator for incremental runs, as follows:

1. Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.

   For example, for an Oracle database, navigate to \OracleBI\dwrep\Upgrade\DbScripts\Oracle.

2. Open the Reset_infa_seq_gen.bat file in a text editor.

3. Edit the parameter values in the section marked 'Login Settings for Informatica server and OLAP database' (for example, TARGET_USERID_PWD, INFA_USERID_PWD).

4. Save the file.

For more information about upgrading Oracle BI Applications, see Oracle Business Intelligence Applications Upgrade Guide.

4.5.2 How to Set Up Informatica Data Code Pages Validation

After installing the Informatica Server, Repository Server, and client tools, make sure that the ValidateDataCodePages parameter is set to 'No' in the powrmart.ini file located in the \Informatica PowerCenter 7.1.4\Client directory, as follows:

[Code Pages] 
ValidateDataCodePages=No

Note: If the powrmart.ini file does not contain a ValidateDataCodePages parameter, add this parameter as specified above.

4.5.3 How to Set Up The Informatica Server

To set up the Informatica Server in Informatica PowerCenter

1. Navigate to Programs, then Informatica PowerCenter 7.1.4, then Informatica PowerCenter 7.1.4 OEM for Oracle - Server, then Informatica Server Setup.

2. In the Informatica Server Setup Application - Options window, select Configure Informatica Service, and then click Continue.

   
   

3. In the Server tab, enter the following:

   Field	Description
   Server Name	A logical name for the Informatica Server. Oracle recommends that you enter ORACLE_BI_DW_SERVER.
   TCP/IP Host Address	The IP address or name of the machine on which you plan to run the Informatica Server.
   Max no. of concurrent sessions	From 1 to 15 sessions.
   Shared Memory	The default is 2,000,000. You should specify 200,000 for each session specified in the 'Max no. of concurrent sessions' field. For example, if you specify 5 in the 'Max no. of concurrent sessions' field, you should specify 1,000,000 in the Shared Memory field.

   
   

4. In the Repository tab, enter the following:

   Field	Description
   Repository Name	A logical name for the Informatica repository. Specify the name as follows: For Teradata databases, Oracle recommends that you enter Oracle_BI_DW_Teradata. For all other databases, Oracle recommends that you enter Oracle_BI_DW_Base.
   Repository User	The account used to access the repository. Oracle recommends that you use Administrator as the username.
   Repository Password	The password you use to connect to the Repository Server from the Repository Manager (the default password is Administrator).
   Repository Server Host Name	The host machine name where the Informatica Repository Server is installed.
   Repository Port Number	The port number the Repository Server uses to connect to repository client applications (the default is 5001). Oracle recommends that you use the default value.

   
   

5. In the Licenses tab:

   1. Leave the default License File Name as pm.lic.

   2. If you have a Connectivity license key, enter it in the with Key field, and click Update.

   3. If you have a Team Based Development license key, enter it in the with Key field, and click Update.

      If you do not specify a Connectivity license key or Options license key (the Team Based Development license key) at this point, you can specify them when you load the pre-built repository into Informatica in a later step (for more information, see Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica").

6. In the Compatibility and Database tab:

   1. Make sure the "Treat CHAR as CHAR on read" option is selected.

   2. Make sure the maximum number of database connections is 100.

7. In the Configuration tab, set the Data Movement Mode.

   
   

   Note: You must run the Informatica Server in Unicode mode if your source data contains multibyte or ISO 8859-1 (8-bit ASCII) data.

   
   

   1. Select the ASCII or UNICODE option.

   2. If you select the UNICODE option, remove the check from the Validate Data Codepages check box, and check the Output Session Log in UTF8 check box.

      
      

   3. Leave the default values in the remaining fields.

8. In the JVM Options tab, leave the default values in all fields.

9. Click OK in the Configure Informatica Service dialog box to complete the configuration of the Informatica Server service.

If you are using a Teradata database, you cannot start the Informatica Server until you have completed the procedures in the section Section 4.15, "How to Set the Code Page File Property for Unicode Environments on Teradata Databases".

4.5.4 How to Set Up The Informatica Repository Server

To set up the Informatica Repository Server in Informatica PowerCenter

1. Navigate to Programs, then Informatica PowerCenter 7.1.4, then Informatica PowerCenter 7.1.4 OEM for Oracle - RepServer, then Informatica Repository Server Setup.

   
   

2. In the Configure Repository Server dialog box, enter the following information:

   Field	Description
   Server Port Number	Note: The port number the Repository Server uses to connect to repository client applications. By default, this value is set to 5001. It is recommended that you use the default value.This value must match the value you entered in Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".
   Administrator Password	The password you use to connect to the Repository Server from the Repository Manager. (the default password is Admin). Note: This value must match the value you entered in Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".

   
   

3. Leave the default values in the remaining fields, and click OK.

4.5.5 How to Copy Source Files and Lookup Files

You need to copy source files and lookup files from the Oracle BI Applications installation directory to the Informatica directory.

To copy source files and lookup files:

1. Copy the source files in OracleBI\dwrep\Informatica\SrcFiles to Informatica PowerCenter 7.1.4\Server\SrcFiles.

2. Copy the lookup files in OracleBI\dwrep\Informatica\LkpFiles to Informatica PowerCenter 7.1.4\Server\LkpFiles.

4.5.6 How to Start the Informatica Services

After you have installed Informatica PowerCenter, you might need to start the Informatica services manually. The Informatica services must be running to enable you to access the Oracle Business Analytics Warehouse using the Informatica tools.

You need to configure the Informatica repository before you start the Informatica services (for more information, see Section 4.14, "How to Configure and Manage the Informatica Repository").

To start the Informatica Services

1. Display the Windows Services dialog.

   For example, on Windows XP, display the Control Panel, then double click the Services icon to display the Services dialog.

   
   

2. Start the Informatica Repository Server service.

3. Start the Informatica service.

If you start the Informatica service and the Informatica Repository Server service is not running, the Informatica service will fail.

4.5.7 How to Create ODBC Database Connections

The Informatica Server uses native drivers to connect to the source and target databases to move data. Depending on the source and target database type, you can optionally configure the Informatica Server to use ODBC drivers supplied by your database vendors to connect to the source and target databases. The Informatica Repository Server uses native drivers to connect to the repository database.

You also need to specify an ODBC connections when you create the data warehouse tables in the DAC in a later configuration task (for more information, see Section 4.12.2, "How to Create Data Warehouse Tables"). In the case of Oracle database, use the Oracle Merant ODBC Driver that comes with the Oracle BI Applications install. For all other databases, you should use ODBC drivers supplied by your database vendor.

To create database connections for DB2 installations

1. Using the DB2 Client Configuration Assistant, create a database connection to the Oracle Business Analytics Warehouse, the transactional database, and the Informatica repository.

   
   

   Note: If you use the DB2 Client Configuration Assistant to create database connections, you can omit step 2, because the DB2 Client Configuration Assistant automatically creates System DSNs (default behavior).

   
   

2. If necessary, in Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the Oracle Business Analytics Warehouse and transactional database using an ODBC driver.

3. Test the connections to make sure they work.

To create database connections for Oracle installations

1. Using Net8 Assistant or Oracle Net Service, create a native connect string (net service name) to the Oracle Business Analytics Warehouse, the transactional database, and the Informatica repository.

2. In Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the transactional database using the Oracle Merant ODBC driver that is supplied with Oracle BI Applications.

   For example, you might create a database connection called Connect_to_OLTP.

3. In Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the Oracle Business Analytics Warehouse and transactional database using the Informatica-supplied Merant Closed driver.

   For example, you might create a database connection called Connect_to_OLAP and specify 'Connect_to_OLAP' in the ODBC Data Source field in the Data Warehouse Configuration Wizard when you create the data warehouse tables in the DAC in a later configuration task (for more information, see Section 4.12.2, "How to Create Data Warehouse Tables").

4. Test the connections to make sure they work.

To create database connections for SQL Server installations

1. In Windows, in the System DSN tab of the ODBC Data Source Administrator, create the following:

   • an ODBC connection to the Oracle Business Analytics Warehouse

   • an ODBC connection to the transactional database

     
     

     Note: Select SQL Server as the ODBC driver.

     
     

2. Test the connections to make sure they work.

Note: When you use the ODBC Data Source Administrator to create a database connection, make sure that you select the SQL Server authentication option using a login ID and password entered by the user

To create database connections for Teradata installations

1. In Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection for the Teradata database.

2. Set the following parameters:

   Field	Enter
   DateFormat	AAA
   SessionMode	ANSI
   NoScan	Yes

   
   

For Unicode environments, in the Teradata ODBC Driver Advanced Options dialog box, set the Character Set parameter to UTF8.

4.6 How to Set Environment Variables on Windows

This section includes instructions for setting the following environment variables:

• Section 4.6.1, "How to Set the PATH Environment Variable for the Informatica Server and Informatica Repository Server"

• Section 4.6.2, "How to Set Siebel UnicodeDB Environment Variables on Windows"

• Section 4.6.3, "How to Set the Bulk Load Registry for DB2/390 Databases"

  
  

  Note: You need to reboot your machine after setting the environment variables for the change to take effect.

  
  

4.6.1 How to Set the PATH Environment Variable for the Informatica Server and Informatica Repository Server

The DAC server uses the pmcmd program to run the Informatica workflows on the Informatica servers. This requires the path to the \bin\ directory for the Informatica server and repository server to be added to the Windows system PATH environment variable.

To set the PATH environment variables for Informatica

• In the Windows System Properties window, add the path for the Informatica Server bin directory (for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\bin) to the PATH environment variable.

• In the Windows System Properties window, add the path for the Informatica Repository Server bin directory (for example, C:\Program Files\Informatica PowerCenter 7.1.4\RepositoryServer\bin) to the PATH environment variable.

4.6.2 How to Set Siebel UnicodeDB Environment Variables on Windows

If you are using the Unicode character data mode, you need to set the Siebel UnicodeDB environment variable.

To set Siebel UnicodeDB for Windows

1. In Windows, click Start, then Run.

2. In the Open field, enter regedit.

3. Navigate to HKEY_LOCAL_MACHINE, then SYSTEM, then CurrentControlSet, then Services, then PowerMart, then Parameters, then Configuration.

4. On the right window panel, right-click and select New, then String Value.

5. Rename the new string value SiebelUnicodeDB.

6. Double-click SiebelUnicodeDB.

7. In the Value data field, enter the connection string for your transactional database followed by the connect string for your data warehouse database, using the following format:

   [user_OLTP]@[connectString_OLTP] [user_OLAP]@[ConnectString_OLAP].
   

   For example, [e-mail] [e-mail].

   • Always leave a space between the connection strings.

   • For MSSQL, use the system DSN name for [connectString_OLTP] and [connectString_OLAP].

   
   

   Note: You must enter the user names and connect strings in the same case as you used in the Workflow Manager, then Connections, then Relational settings.

   
   

4.6.3 How to Set the Bulk Load Registry for DB2/390 Databases

DB2/390 databases do not support bulk loading; therefore, you need to disable bulk loading in the Windows registry: HKEY_LOCAL_MACHINE\..\System\CurrentControlSet\Services\Powermart\Configuration area.

To disable bulk loading for DB2/390 databases

• For DB2/390 databases, enter DisableDB2BulkMode = Yes in the Windows registry.

4.7 How to Copy Hibernate Libraries

To run the DAC Client or DAC Server, you need to have libraries from an open source software product called Hibernate. Hibernate libraries required to run the DAC Client or DAC server must be installed on each machine that runs the DAC client or DAC server. Hibernate libraries are not installed as part of Oracle BI Applications 7.9.x., but have to be downloaded from the Hibernate Web site.

Oracle recommends that you download Hibernate Core Package Version 3.2.x GA or later. Newer versions of Hibernate Core Package 3.2 are now generally available (for example, Hibernate Core Package Version 3.2.5 GA). DAC is supported on the libraries of these versions also. You can download Hibernate Core Package from http://www.hibernate.org.

To copy Hibernate libraries

1. Go to the Web site http://hibernate.org.

2. Navigate to the Download area and download Hibernate Core Package Version 3.2.x. GA to a local machine.

3. Unzip the files and directories in the hibernate-<3.2.x>.ga.zip file (for example, hibernate-3.2.2.ga.zip).

   The files will upzip into a directory named hibernate-3.2.

4. For each machine that needs to run the DAC client or DAC server, copy the Hibernate files from the \hibernate-3.2 directory to the \OracleBI\DAC directory, as described in the table below.

   Table 4-2 Hibernate library files that you need to copy

   Files	Copy from	Copy to
   *.jar	\hibernate-3.2\lib	\DAC\lib
   hibernate3.jar	\hibernate-3.2	\DAC\lib
   hibernate-configuration-3.0.dtd	\hibernate-3.2\src\org\hibernate	\DAC
   hibernate-mapping-3.0.dtd	\hibernate-3.2\src\org\hibernate	\DAC

   
   

Note: You do not need to copy any of the other files in the \hibernate-3.2 directory to the \OracleBI\DAC directory.

4.8 How to Configure the DAC Client

The DAC client is installed as part of an Oracle BI Analytics installation. You must run the DAC client from a Windows machine.

Note: You must have Java SDK installed on the machines where both the DAC server and DAC client are installed. Java SDK is also installed as part of the Oracle Business Intelligence infrastructure.

This section includes the following topics:

• Section 4.8.1, "How to Configure the DAC Config.bat File"

• Section 4.8.2, "How to Install JDBC Drivers for DAC Database Connectivity"

4.8.1 How to Configure the DAC Config.bat File

You configure the DAC config.bat file on the Windows machine that is used to run the DAC client. Follow this procedure to configure the DAC config.bat file.

To configure the DAC config.bat file

1. In the DAC directory, open the config.bat file.

   This directory is usually in the OracleBI root directory (for example, <DRIVE>:\OracleBI\DAC).

2. Edit the JAVA_HOME variable to point to the directory where you installed the Java SDK.

   Make sure there are no spaces in the path reference.

   For example:

   set JAVA_HOME=d:\jdk1.5
   

3. Edit the DAC_HOME variable to point to the directory where you installed the DAC.

   For example:

   set DAC_HOME=d:\OracleBI\DAC
   

4.8.2 How to Install JDBC Drivers for DAC Database Connectivity

This section provides instructions for installing the appropriate JDBC driver in the DAC\lib directory to enable DAC database connectivity. If the required JDBC drivers are not already installed, you need to install the JDBC driver on the machines where both the DAC server and DAC client are installed.

To install JDBC drivers in the OracleBI\DAC\lib directory

• Oracle. If you are using an Oracle database (other than 8.x), find the directory where Oracle is installed. Copy the file named ojdbc14.jar in the jdbc\lib directory and paste it in the OracleBI\DAC\lib directory.

  • If you are using Oracle 8.x, copy the file named classes12.zip and paste it in the OracleBI\DAC\lib directory. Also, edit the ORACLELIB setting in the config.bat file as follows:

    set ORACLELIB=%DAC_HOME%\lib\classes12.zip
    

• DB2. If you are using a DB2 database, find the directory where DB2 is installed. In the Java sub-directory copy the file named db2java.zip and paste it in the OracleBI\DAC\lib directory.

  
  

  Note: If your source or target database is DB2-UDB, you also need to create DB2-UDB stored procedures to be used during the ETL process. For instructions, see Section 4.21, "How to Create Stored Procedures for DB2-UDB".

  
  

• MSSQL. Data Warehouse Administration Console is configured for Microsoft SQL Server 2005 JDBC drivers. If you are using a Microsoft SQL Server database, then download the Microsoft SQL Server 2005 JDBC Driver files and copy them to the <DRIVE>:\OracleBI\DAC\lib\ directory. You can use the Microsoft SQL Server 2000 JDBC Driver files if you edit the \conf\connection_templates.xml file and un-comment the section that starts "<!-- THIS SECTION IS FOR SQL SERVER 2000. Comment this section while using SQL Server 2005.-->

  
  

  Note: You need the Microsoft SQL Server 2005 JDBC Driver 1.1 for SQL Server 2000 or SQL Server 2005.

  
  

• Teradata. If you are using a Teradata database, copy the files tdgssconfig.jar, TdgssUserConfigFile.xml, terajdbc4.jar, log4j.jar, and tdgssjava.jar from the Teradata installation directory to the \OracleBI\DAC\lib directory. Depending on the Teradata JDBC version, you might not have some of the above files.

4.9 How to Create the Required Databases

This procedure provides instructions for creating databases to hold the following:

• the DAC metadata repository.

• the Oracle Business Analytics Warehouse.

You can store both the DAC metadata repository and the Oracle Business Analytics Warehouse in separate databases, or in the same database. For example, if you are using an Oracle database, you might use Oracle SQL Plus to create one account for the DAC metadata repository and one account for the Oracle Business Analytics Warehouse.

Note: You also need to know the database connection details for the OLTP database that stores your transactional data that you want to upload to the Oracle Business Analytics Warehouse. For example, you might have an Oracle database account called OLTP that stores the transactional data.

To create the required databases

1. (Optional) In your target database tool (for example, Oracle SQL Plus), create a database or database account for the DAC metadata repository. For example, in Oracle SQL Plus you might create a database account named 'DAC'.

   If you perform step 1, you specify the name of the DAC metadata repository database (for example, DAC) when you log into the DAC (for more information about logging into the DAC, see Section 4.10, "How to Create the DAC Metadata Repository").

2. In your target database tool (for example, Oracle SQL Plus), create a database account for the Oracle Business Analytics Warehouse tables. For example, in Oracle SQL Plus you might create a database account named 'OLAP'.

   
   

   Note: Make sure that you specify this database account when you create the data warehouse tables in the DAC (for more information, see Section 4.12.2, "How to Create Data Warehouse Tables").

   
   

   If you omit step 1, you specify the name of the Oracle Business Analytics Warehouse database (for example, OLAP) when you log into the DAC (for more information about logging into the DAC, see Section 4.10, "How to Create the DAC Metadata Repository").

4.10 How to Create the DAC Metadata Repository

This procedure provides instructions for logging into the DAC, and importing the required metadata into the DAC repository.

• Section 4.10.1, "How to Create a DAC Connection"

• Section 4.10.2, "How to Log Into the DAC"

• Section 4.10.3, "How to Import Metadata into the DAC repository"

Note: The DAC client can connect to only one DAC repository at a time.

Note: Before you start this procedure, you need to create a database for the DAC metadata repository. This database can be the same as the data warehouse database. For more information, see Section 4.9, "How to Create the Required Databases".

4.10.1 How to Create a DAC Connection

This section explains how to create a DAC Connection.

To create a DAC connection

You create a DAC connection to enable you to log into the DAC using a stored set of login details.

1. Launch the DAC client by choosing Program Files\Oracle Business Intelligence\Oracle BI DAC\DAC Client.

   
   

   Note: You can also start the DAC client by navigating to the OracleBI\DAC directory and double-clicking the startclient.bat file.

   
   

2. In the Login... dialog box, select Configure.

3. In the Configuring... dialog box, select Create Connection, and then click Next.

4. Enter the appropriate connection details.

   
   

   Note: When you set up the DAC server later in the configuration (for more information, see Section 4.18.1, "How to Configure the Connection Between the DAC Server and DAC Repository"), you must use the same connection details that you specify here.

   
   

   Field	Description
   Name	A unique connection name that you specify here (for example, DAC).
   Connection type	Type of database.
   Database name	Database name or database account name in which you want to store the DAC metadata repository. If you are using... Oracle (OCI8) use the tnsnames entry. Oracle (Thin) use the instance name. SQL Server use the database name. DB2-UDB use the connect string as defined in the DB2 configuration.
   Database Host	Machine name where the database resides.
   Database Port	The port number on which the database listens. For example, for an Oracle database the default port is 1521, or for a SQL Server database the default port is 1433.

   
   

5. Select Test Connection to confirm that the login details are valid.

6. Select Apply to save the connection details and return to the login dialog.

The DAC repository is not supported on DB2-390 or Teradata databases. If your source or target database is DB2-390, you need to use a DB2-UDB, MSSQL, or Oracle database for both the DAC repository and the Informatica repository.

4.10.2 How to Log Into the DAC

This section explains how to log into the DAC.

To log into the DAC

You log into the DAC to enable you to edit the DAC metadata repository. Before you can log into the DAC, you need to have created a DAC connection, which is a set of stored login details. For information about creating a DAC connection, see Section 4.10.1, "How to Create a DAC Connection".

1. Launch the DAC client by double-clicking the DAC Client icon on your desktop or navigating to the OracleBI\DAC directory and double-clicking the startclient.bat file.

   The Login... dialog is displayed.

   
   

2. In the Login... dialog box, select a connection from the Connection drop down list.

3. In the Table owner name field, enter the database user name for the database in which you are creating the DAC metadata repository.

   
   

   Note: For more information about creating a database for the DAC metadata repository, see Section 4.9, "How to Create the Required Databases").

   
   

4. In the Password field, enter the database password for the database user name that you specified in the previous step.

5. Click Login to start the DAC.

   The DAC client starts and connects to the specified database.

6. Click Yes if you are prompted to create or upgrade the repository tables.

   If the DAC repository schema does not exist in the database to which you are connecting, the schema will be automatically created. When a repository is created on Oracle or DB2 databases, you have the option of specifying a tablespace. For MSSQL and DB2 databases, you can indicate whether the environment is Unicode.

4.10.3 How to Import Metadata into the DAC repository

This section explains how to import metadata into the DAC repository.

To import metadata into the DAC repository

1. If you are not logged into the DAC, log into the DAC (for more information, see Section 4.10.2, "How to Log Into the DAC").

2. From the DAC menu bar, choose Tools, then DAC Repository Management, then Import to display the Import dialog.

   
   
   
   

   Note: Make sure that the correct \DAC\export\ directory is displayed at the top of the dialog (for example, <drive letter>:\OracleBI\DAC\export). If necessary, use the 'Change import/export directory' button to select the \DAC\export\ directory. If the wrong directory is selected, the Applications list will be empty.

   
   

3. Using the check boxes in the Selected column, select the check box next to the business applications that you want to deploy.

   
   

   Note: Oracle recommends that you always import the 'Universal' container by selecting the Universal check box in the Selected column.

   
   
   
   

   Tip: Make a note of the Application names that you select. When you create data warehouse tables later in the configuration process, you might need to type in the names exactly as they are displayed here (for more information, see Section 4.12.2, "How to Create Data Warehouse Tables").

   
   

4. Select the Categories options as follows:

   Categories Options	Description
   Logical	Imports all information contained in the DAC Design view and the execution plan information for the DAC Execute view.
   Run Time	Imports ETL Run History and the last refresh date information.
   System	Imports all information contained in the DAC Setup view, except passwords for servers and database connections.

   
   

5. Click OK to display the Importing tables dialog.

   
   

6. To confirm that you want to import the seed data selected, re-type the text in the text box and click Yes.

7. If prompted to verify that you want to continue with the Import, click Yes.

   When the process is complete, the DAC displays a status box containing a success or failure message. If the process fails, use the \OracleBI\DAC\log\import.log file to diagnose errors.

8. Click OK.

9. If your source or target database is a DB2-390 database, run DAC_DB2390_Source.sql or DAC_DB2390_Target.sql immediately after importing the seed data by connecting to the database where the DAC repository resides. These files are stored in the \OracleBI\DAC\ directory.

4.11 How to Configure the DAC Metadata Repository

This section includes the following topics:

• Section 4.11.1, "How to Set Up DAC System Properties"

• Section 4.11.2, "How to Specify Transactional and Data Warehouse Data Sources in the DAC"

• Section 4.11.3, "How to Configure Email Recipients in the DAC Client"

• Section 4.11.4, "How to Activate Join Indices for Teradata Databases"

  
  

  Note: You will configure the DAC server to point to the DAC metadata repository later in the installation process, in the procedure Section 4.18, "How to Configure the DAC Server".

  
  

4.11.1 How to Set Up DAC System Properties

Follow this procedure to set up the DAC system properties, which determine the behavior of the DAC server. If you do not change a property value, Oracle BI Applications uses the property value that is automatically set during the Oracle BI Application installation.

To set up the DAC system properties

1. Launch the DAC client.

2. Click Setup on the DAC toolbar.

   The DAC System Properties tab is active.

   
   

3. If required, change the property values, as described below.

   
   

   Note: Possible values for all properties have to be keyed in exactly as described in the description tab (at the bottom) of each property. For example Auto Restart has possible values of false and true (case sensitive), and Server Log Level has the following possible values SEVERE, FINEST, FINER,FINE, INFO, WARNING (case sensitive).

   
   

4.11.2 How to Specify Transactional and Data Warehouse Data Sources in the DAC

Follow this procedure to specify the transactional and data warehouse data sources in the DAC.

Oracle BI Applications is installed with a set of template data sources that you can edit to specify the data source details. Alternatively, you can specify new data sources without use a template.

To specify transactional and data warehouse data sources

Note: You need to perform this procedure once for the transactional database and once for the data warehouse database. When you specify a database connection for the transactional database, select the 'Source' option from the Type drop down list on the Edit sub-tab. When you specify a database connection for the data warehouse database, select 'the Warehouse' option from the Type drop down list on the Edit sub-tab.

1. Launch the DAC client.

2. Click Setup on the toolbar, and then click the Physical Data Sources tab.

   
   

   The Physical Data Sources tab displays a list of data source templates for many common database types, which you can use to specify your data sources.

3. Do one of the following:

   • Select an appropriate datasource template from the list, and edit the details in the Edit tab.

   • Click New, and enter the details in the Edit tab.

4. In the Edit subtab, enter the following:

   Field	Description
   Name	Logical name for the database connection. If you are using a pre-created data source template, do not change the default value.
   Type	Select 'Source' when you create the database connection for a transactional (OLTP) database. Select 'Warehouse' when you create the database connection for a data warehouse (OLAP) database. If you are using a pre-created data source template, do not change the default value.
   Connection Type	Type of database. Possible values are: Oracle (OCI8) Oracle (Thin) DB2 DB2-390 MSSQL Teradata Flat File
   Instance or TNS Name	For an Oracle database instance, enter the database SID (that is the SERVICE_NAME = value in the tnsnames.ora file in \network\admin\. For an Oracle TNS Name, enter the TNS entry name that is specified in the tnsnames.ora file in \network\admin\. For more information about which data sources you should specify, see Section 4.9, "How to Create the Required Databases".
   Table Owner	Valid database user.
   Table Owner Password	Valid database user password.
   Max Num Connections	Maximum number of database connections this connection pool can contain.
   DB Host	Machine name or instance where the database resides. For example, databasename.us.company.com.
   Port	Port number where the database listens (for example 1521 is the default for an Oracle database).
   Priority	Number used to generate dependencies when designing execution plans.
   Data Source Number	Unique number assigned to the data source category so that the data can be identified in the data warehouse. For example, the value '9' is used for Siebel data sources. If you are editing a data source template for a data source type, Oracle recommends that you do not change the value that is set out-of-the-box. If you are specifying a data source without using a pre-defined template, you must use the correct value for that data source category. For example, if you specify an Oracle EBS data source, you must specify the DATASOURCE_NUM_ID value '9'. For a complete list of supported data sources and DATASOURCE_NUM_ID values, see Section 8.1.2.1, "How to Configure Data Source Num IDs" This value is passed as a parameter to the Informatica workflows. If you are using multiple sources, each data source has a unique number. Typically, all source dependent extracts will use this parameter to populate the DATASOURCE_NUM_ID column, and the source independent workflows will carry these values to the final dimension and fact tables.
   Default Index Space	(Oracle specific) Specifies the table space in which the DAC drops and creates indices against this database connection To use the default table space, leave this field blank.

   
   
   
   

   Note: Alternatively, you can edit the template data sources that are installed and fill in the values for the data sources that you are using. For example, to specify the data warehouse data source, you can edit the DataWarehouse data source, fill in the data sources details, and click Save. Do not change the value in the Logical field.

   
   

5. Click Test Connection to make sure the connection works.

6. Click Save.

4.11.3 How to Configure Email Recipients in the DAC Client

Follow this procedure to configure email recipients.

To configure email recipients in the DAC client

1. Launch the DAC client.

2. Click Setup on the DAC toolbar, and then click the Email Recipients tab.

3. Click New.

4. In the Edit tab below, enter the following information:

   Field	Description
   Name	Logical name of the user to be notified.
   Email Address	Email address where the notification is sent.
   Notification Level	The notification levels are as follows: 10 -- Notifies recipient of success or failure of each task. 5 -- Notifies recipient of success of failure of the entire ETL process. 1 -- Notifies recipient that ETL completed successfully.
   Inactive	Indicates whether the selected email activation is active or inactive.
   Needs Authentication	Read only value that specifies whether the corporate email server requires authentication (this value is set by choosing Tools, then DAC Server Setup).

   
   

4.11.4 How to Activate Join Indices for Teradata Databases

For Teradata databases, the preconfigured tasks for creating and dropping join indices are inactive.

To activate join indices for Teradata databases

1. Create a new execution plan with the list of subject areas that you are interested for the ETL.

2. Query for all tasks whose name starts with 'Teradata Drop' and add them as preceding tasks.

3. Query for all tasks whose name start with 'Teradata Create' and add them as following tasks.

4. Assemble the execution plan parameters in the Parameters tab and configure the parameters.

5. Redesign the execution plans.

4.12 How to Configure the Oracle Business Analytics Warehouse Database

This section includes the following procedures for configuring the Oracle Business Analytics Warehouse database:

• Section 4.12.1, "How to Create the SSE Role"

• Section 4.12.2, "How to Create Data Warehouse Tables"

• Section 4.12.3, "How to Create Data Warehouse Tables on a Teradata Database"

  
  

  Note: Before you start this procedure, you need to create a database for the data warehouse. This database can be the same as the DAC metadata repository database.

  
  

4.12.1 How to Create the SSE Role

Follow this procedure to create the SSE role.

To create the SSE Role

• Create a database role named SSE_ROLE (SSEROLE for DB2 390 databases). Assign this role to the database user. For instructions on creating roles, see the documentation provided with your database.

  For example, on an Oracle database you might use the 'CREATE ROLE' SQL command to create the role, and the GRANT command to assign privileges to the role, and to assign the role to the data warehouse database account.

Note the following:

• For an Oracle database, when you create the SSE_ROLE role, you need to grant the following privileges:

  • CONNECT

  • RESOURCE

• For an Oracle database, if the DAC repository and the Informatica repository are stored in a different database to the data warehouse database, the SSE_ROLE must have the following additional privileges:

  • READ

  • WRITE

  • UPDATE

  • DELETE

  • GRANT

• If you are using a Teradata database, you do not need to create the SSE role. If you are using a DB2 390 database, you can use an alternative SSE role name to SSEROLE if required. To specify a different role name, modify the 'set GRANTEE="SSEROLE"' line in the OracleBI\dwrep\createwtables.bat file.

4.12.2 How to Create Data Warehouse Tables

Use this procedure to create the data warehouse tables. Before you perform this procedure make sure that the SSE role has been created for the Oracle Business Analytics Warehouse, and that the database user has been associated with the role (for more information, see Section 4.12.1, "How to Create the SSE Role").

If you are using a Teradata database as a source transactional database, the DAC creates a SQL file to create the schema tables, not the tables themselves. Follow the steps in Section 4.12.3, "How to Create Data Warehouse Tables on a Teradata Database".

Note: Additional work by the database administrator is required if the data warehouse tables need to be moved to different tablespaces for performance or manageability reasons.

To create data warehouse tables

1. From the DAC menu bar, click Tools, then ETL Management, then Configure.

2. In the Sources dialog box, select the database platform for the target data warehouse and source transactional database.

   
   

3. Click OK to display the Data Warehouse Configuration Wizard.

   
   

4. Select the Create Data Warehouse Tables check box, and click Next.

   The Data Warehouse tab is active.

   
   

5. Enter the details of the database in which you want to store the data warehouse.

   The information that you need to enter is dependent on the type of target database that you are using to store the data warehouse.

   Field	Description
   Database Type	(Read only) Type of database, as specified by the 'Target data warehouse database platform' field on the Sources dialog (see step 2). If the database type is DB2 390, then check the 390 Database check box.
   Container	The name of the source business applications that you want to deploy. Note: Oracle recommends that you leave the Container field blank. If you leave the Container field blank, the DAC creates a container by default for all of the following: The source business applications that you selected when you imported the seed data into the DAC metadata repository earlier (for more information, see Section 4.10, "How to Create the DAC Metadata Repository"). Any copies of those source system applications. Any additions made those source system applications. Any new source system applications that you have custom built. For example, you might do the following: Import the seed data for Oracle 11.5.9 and 11.5.10 Make a copy of Oracle 11.5.10 and call it 'My Oracle 11.5.10'. Add two data warehouse tables to 'My Oracle 11.5.10' Create a new source system application called 'My new source' and add 10 data warehouse tables to 'my New Source'. If you leave the Container field blank, the The DAC will create the following containers: Oracle 11.5.9 Oracle 11.5.10 My Oracle 11.5.10 My new Source If there tables that are common to them, then only one table will be created. For example, if there is a table called W_ORG_D in Oracle 11.5.9 and Oracle 11.5.10, then the DAC will create only one table called W_ORG_D. If columns are different for the same table across containers, then DAC will create a table that has all the columns in the same table. If you only want to deploy a sub-set of the source business applications for which you imported seed data earlier, then use this field to specify a container name. When you specify a container name, you must enter the names of the applications exactly as they are displayed on the seed data Import dialog. For example: If you want to deploy Oracle 11.5.9, enter: 'Oracle 11.5.9'. If you want to deploy Oracle 11.5.9 and 11.5.10, enter: 'Oracle 11.5.9,Oracle 11.5.10'. For more information about containers, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
   Table Owner	Valid database owner, username, or account that you set up to hold the data warehouse (for more information, see Section 4.9, "How to Create the Required Databases").
   Password	Valid database user password for the database owner, username, or account that you specified in the Table Owner field (for more information, see Section 4.9, "How to Create the Required Databases").
   ODBC Data Source	Data Source Name (DSN) for the Oracle Business Analytics Warehouse. You need to specify the name of the ODBC connection (for example, Connect_to_OLAP) that you created for the data warehouse (for more information, see Section 4.5.7, "How to Create ODBC Database Connections").
   Data Area	(Optional) Tablespace where data warehouse tables are created.
   Index Area	(Optional) Indexspace where data warehouse indices are created (applicable only to Oracle and DB2 databases).
   Is Unicode	Specifies whether the data warehouse database is Unicode.

   
   

6. Click Start.

The Run Status tab displays information about the process, as follows:

• If a 'Success' message is displayed, the data warehouse tables have been created. If you want to see log information about the process, use the following log files.

  • \OracleBI\DAC\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.

  • \OracleBI\DAC\config\createtables.log - A log of the ddlimp process.

• If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in \OracleBI\DAC\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

4.12.3 How to Create Data Warehouse Tables on a Teradata Database

This section includes the procedures for creating and dropping data warehouse tables on a Teradata database. Before you start, make sure that the tables that you create are case specific by setting the session mode to ANSI in your Teradata ODBC configuration (for more information, see Section 3.9, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse").

Note: For Unicode environments, perform these procedures using a login that has the default character set UTF-8.

To create data warehouse tables on a Teradata database

1. From the DAC menu bar, click Tools, then ETL Management, then Configure.

2. In the Sources dialog box, select 'Teradata' as the database platform for the source data warehouse, and select the appropriate database type for the target transactional database.

3. Click OK to display the Data Warehouse Configuration Wizard.

4. Click the 'Generate create statements for Data Warehouse Tables' check box, then click Next to display the Data Warehouse SQL tab.

5. Use the Container field to specify individual containers, or leave blank to deploy all containers.

6. Click Start.

   The Run Status tab displays information about the process, as follows:

   • If a 'Success' message is displayed, the data warehouse tables have been created. If you want to see log information about the process, use the following log files.

     \OracleBI\DAC\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.

     \OracleBI\DAC\config\createtables.log - A log of the ddlimp process.

   • If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in \OracleBI\DAC\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

7. Copy the SQL file created in step 6 from \conf\sqlgen\sql\Teradata into SQL Assistant and execute the SQL.

4.13 How to Register Informatica Servers in the DAC Client

This section describes registering the Informatica Server and the Informatica Repository Server in the DAC and specifying the number of workflows that can be executed in parallel. The DAC server automatically load balances across the servers and does not run more than the value specified for each of them.

Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".

Tip: Specify Informatica Server and Repository Server details that are consistent with the Informatica details that you specified when you set up the Informatica Server (for more information, see

4.13.1 How to Register Informatica Servers in the DAC Client

When you register Informatica Servers in the DAC client, note the following:

• You must register at least one Informatica server in the DAC. You should register one Informatica server for each Informatica repository.

• You must not register more than one Informatica Repository server in the DAC.

• It is recommended that the Informatica Server be installed on the machine where the DAC server is installed. If a machine is running only the DAC client, it does not need an Informatica server installed.

To register Informatica servers in the DAC client

1. Launch the DAC client.

2. Click Setup on the DAC toolbar, and then click the Informatica Servers tab.

3. Click New to display the Edit tab below.

   
   

   If you are configuring a new installation, the Informatica Servers tab will be empty. If you are upgrading an existing installation, the Informatica Servers tab might contain existing Informatica servers.

4. Register the Informatica server, by entering the following information:

   Field	Enter
   Name	Logical name for the Informatica server (for example, INFO_SERVER).
   Type	Choose Informatica.
   Server Hostname	Informatica server host name or IP address.
   Server Port	Port where the Informatica server connects for requests (the default port number is 4001). Oracle recommends that you use the default port number.
   Login	Informatica user name who has appropriate privileges to execute workflows (for example, Administrator).
   Password	Informatica user password (the default password is Administrator).
   Maximum Sessions	Maximum number of workflows that can be executed in parallel on the Informatica server. If the number of sessions is zero or is not specified, the DAC server assigns the default value of 10.
   Repository Name	Enter Oracle_BI_DW_Base (or Oracle_BI_DW_Teradata for Teradata installations). Note: If you need to verify the repository name, log into the Informatica Administration Console and connect to the repository server.
   Inactive	Indicates whether the Informatica server is active or inactive.

   
   
   
   

   Note: You need to specify Informatica Server and Informatica Repository Server details that are consistent with the Informatica details that you specified when you set up the Informatica Server (for more information, see Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".

   
   

5. If you want to test the connection, click Test Connection.

   
   

   Note: Before you can test a connection, you need to start the Informatica Repository (for more information, see Section 4.19, "How to Start the Informatica Server").

   
   

6. Click Save to save the details.

7. Click New to display the Edit tab below

8. Register the Informatica Repository server, by entering the following information:

   Field	Enter
   Name	Logical name for the Informatica Repository server (for example, INFO_REP_SERVER).
   Type	Choose Repository.
   Server Hostname	Informatica Repository server host name or IP address.
   Server Port	Port where the Informatica Repository server connects for requests. (The default port number is 5001. It is recommended that you use the default port number.)
   Login	Informatica user name who has appropriate privileges to execute workflows (for example, Administrator).
   Password	Informatica user password (the default password is Admin).
   Maximum Sessions	Maximum number of workflows that can be executed in parallel on the Informatica server. If the number of sessions is zero or is not specified, the DAC server assigns the default value of 10.
   Repository Name	Type Oracle_BI_DW_Base (or Oracle_BI_DW_Teradata in a Teradata environment). Note: If you need to verify the repository name, log into the Informatica Administration Console and connect to the repository server.
   Inactive	Indicates whether the Informatica Repository server is active or inactive.

   
   
   
   

   Note: You need to specify Informatica Server and Informatica Repository Server details that are consistent with the Informatica details that you specified when you set up the Informatica Server (for more information, see Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".

   
   

9. Click Test Connection to make sure that the connection works.

   
   

   Note: Before you can test a connection, you need to start the Informatica Repository (for more information, see Section 4.14.5, "How to Stop and Start the Informatica Repository (Optional)").

   
   

10. Click Save to save the details.

If you have not already started the Informatica servers (for more information, see Section 4.5.6, "How to Start the Informatica Services"), start the Informatica servers now, then go back and test the connections using the Test Connection button.

4.14 How to Configure and Manage the Informatica Repository

This section contains instructions for configuring and managing the Informatica repository. For information about checking out and checking in objects in the Informatica repository, see Section 4.14.1, "About the Versioned Informatica Repository".

A pre-built Informatica repository called Oracle_BI_DW_Base is copied into the OracleBI\dwrep\Informatica\Repository directory during the Oracle BI Applications installation.

To configure the Informatica Repository for a new Oracle BI Applications installation:

1. Use the Informatica Repository Administration Tool to register the Informatica Server machine. For more information, see Section 4.14.2, "How to Register the Informatica Repository Server".

2. Use the Informatica Repository Administration Tool to load the pre-built Oracle_BI_DW_Base.rep repository. For more information, see Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica".

3. Use the Informatica Workflow Manager tool to configure the Informatica Repository. For more information, see Section 4.14.4, "How to Configure the Informatica Repository in Workflow Manager".

4. Start the Informatica services. For more information, see Section 4.5.6, "How to Start the Informatica Services").

You can also use the Informatica Repository Administration Tool to do the following optional tasks:

• stop and start the repository. For more information, see Section 4.14.5, "How to Stop and Start the Informatica Repository (Optional)".

• create and restore backup versions. For more information, see Section 4.14.6, "How to Back Up and Restore the Informatica Repository (Optional)".

4.14.1 About the Versioned Informatica Repository

This section explains the versioned Informatica Repository that is included in Oracle Business Intelligence Applications.

4.14.1.1 Summary of Versioning in the Informatica Repository

Oracle Business Intelligence Applications 7.9.4 ships the following two Informatica repository files:

• Oracle_BI_DW_Base.rep

• Oracle_BI_DW_Teradata.rep

In releases 7.9.0, 7.9.1 and 7.9.2, these were non-versioned files provided in the standard application. In Oracle Business Intelligence Applications version 7.9.3 and 7.9.4, these Informatica repository files are now versioned.

Because of this change, you need to enter the Team based Development license when loading the Informatica repository file supplied with Oracle BI Applications. Also, if you extend or modify the standard mappings, you need to check out and check changes to the Informatica repository. The Oracle Business Intelligence Data Warehouse Console (DAC) will now only pick up and execute valid and checked-in objects from the Informatica repository.

Oracle BI Applications now uses a standard way of labeling objects. For example, the standard Informatica objects have the label 'Oracle Business Intelligence Applications Release 7.9.x'. This label is useful when tracing back and comparing changes made from release to release. Note that Informatica does not allow a versioned repository file to be restored as a non-versioned repository somewhere else. Therefore, this change is uni-directional.

4.14.1.2 Applying the Team Based Development License to the Informatica Repository

When you load the pre-built repository into Informatica, you must use a Team Based Development license for the Options connectivity key. For more information, see Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica".

4.14.1.3 Customization of Repository Objects Using Check Out and Check In

When the Team based Development option has been enabled for an Informatica repository, you must "check out" a repository object to modify and then "check in" the change. To extend or modify the standard mappings, you must check out a repository object that is to be modified, make the desired changes, and then check in the changes. This process is described below, and is illustrated in the screenshot below where the red arrow represents the action flow.

The workflow for checking out and checking in changes in the Informatica repository is described below:

• Developers check out an object for modification.

  Note the green arrow on the object in the screenshot below.

• Developers make changes, validate and save the object, and are ready to check their changes in.

  The green arrow is still displayed to indicate this is still a checked out object.

• Developers provide mandatory check in comments and finally check in.

• The object is checked in now.

  Note that the green arrow disappears to indicate that the current version is now checked in and is usable/visible by other developers (for example, in the DAC). Developers can also compare versions, view history for this object now.

  The version prior to this change also gets stored in the repository for tracking purposes. Apart from tracking changes in repository objects, Informatica provides additional useful features like "Query" and "labeling" on versioned repositories. Refer to the Informatica product guides for the more information on the topic "team based development".

  
  

4.14.2 How to Register the Informatica Repository Server

Follow this procedure to register the Informatica Repository Server in the Informatica Repository Server Administration Console.

Note: Make sure the Informatica Repository Server is running before you begin this procedure.

To register the Informatica Repository Server

1. Launch the Repository Server Administration Console.

2. In the left pane, select the Informatica Repository Servers node, which appears under Console Root.

   
   

3. From the menu bar, select Action, then New Server Registration to display the 'Register a new Repository Server' dialog.

   1. Enter the host name (the machine where the repository server resides).

   2. Accept the default port number 5001 or enter the appropriate port number. Click OK.

      The Repository Server host name appears in the right pane under Hostname.

   3. Right-click the server name and choose Connect.

   4. In the Connecting to Repository Server dialog, enter the Administrator password (the default is Admin). Click OK.

   These values should match the values you entered in Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".

4.14.3 How to Load the Pre-Built Repository Into Informatica

You use the Restore option in the Informatica Repository Administration Tool to load the pre-built Oracle_BI_DW_Base.rep repository or Oracle_BI_DW_Teradata.rep (in a Teradata environment) into Informatica.

Note: The pre-built Oracle_BI_DW_Base and Oracle_BI_DW_Teradata repository files are versioned from BI Applications 7.9.3 and higher. You must use a The Team Based Development License key in step 13. For more information about the versioned repositories, see Section 4.14.1, "About the Versioned Informatica Repository"

Before you start this procedure, you need to create a database to contain the Informatica Repository. You can use the same database as the data warehouse database. For more information, see Section 4.9, "How to Create the Required Databases".

To load the pre-built Oracle_BI_DW_Base.rep repository into Informatica

1. Copy the Oracle_BI_DW_Base.rep from the OracleBI\dwrep\Informatica\Repository directory to the \Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup directory.

2. Launch the Repository Server Administration Console and connect to the Informatica Repository machine.

3. Click Backups to display available backup files in the right hand pane.

   
   

4. Right click on Oracle_BI_DW_Base.rep and click Restore to display the Restore Repository dialog.

   
   

5. Click the 'Create a new Repository and associate the restored information with it' check box.

6. Click OK to start the Restore procedure and display the New Repository dialog.

7. In the General tab, in the Repository Name field, enter the name for the Informatica repository (for example, Oracle_BI_DW_Base).

   
   

   Note: This value should match the value you entered in Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server" (for example, Oracle_BI_DW_Base).

   
   
   
   

8. In the Database Connection tab, enter the following information:

   Field	Description
   DatabaseType	The type of database storing the repository.
   CodePage	The code page based on the operating system platform.
   ConnectString	The native connect string of the database that stores the data warehouse. For MSSQL Server databases, use <SERVERNAME@DATABASENAME>.
   DBUser	The account used to access the DAC repository (for example, DAC).
   DBPassword	The password for the DAC repository user (for example, DAC).

   
   

9. Click the Licenses tab.

   You may see a message stating the license file is empty.

10. Click OK to close the message dialog box.

11. In the License Key field, enter the Product license key, and then click Update.

    The license key information appears in the lower window of the Licenses tab.

12. Repeat the step above for the Connectivity license keys.

13. Repeat the step above for the Option license keys.

    You must enter the Team Based Development Licence key.

14. Click OK to save the repository details and create the repository.

15. Select the repository, right-click and then click Start to start the repository.

    
    

    Note: The Informatica repository tables need to be analyzed for better performance. This procedure must be done manually by the database administrator.

    
    

4.14.4 How to Configure the Informatica Repository in Workflow Manager

Follow this procedure to configure the Informatica repository in Informatica Workflow Manager.

To configure the server properties for the Informatica repository in Workflow Manager

1. Launch Informatica Workflow Manager.

2. In the Repositories list, select the Informatica repository.

3. Choose Repository, then Connect.

   The Connect to Repository window opens.

4. Log in using the username and password you entered in Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server" (for example, with the user name Administrator).

5. Click Connect.

6. Once connected, double-click the icon of the server name (ORACLE_BI_DW_SERVER is the recommended server name) under the repository icon.

   The Server dialog box opens.

7. In the Host Name field, enter the computer name (as shown in the Network control panel) of the machine running the Informatica Server.

8. Click Resolve Server to confirm the computer name and update the IP address.

9. (Optional) Configure the Code Page setting.

   
   

   Note: If the Informatica Server uses ENU Windows, leave the setting unchanged. For localized environments, choose the appropriate setting.

   
   

10. Click Advanced to expose the Server Variables list.

11. Make sure that the $PMRootDir variable points to the directory where the Informatica Server is installed.

12. Make sure that the $PMSourceFileDir points to the location mentioned in DAC, then Setup, then DAC System Properties, then InformaticaParameterFileLocation. Also make sure that this location has no spaces in its path (the default path the \SrcFiles\ directory in the Informatica installation directory).

    
    

    Note: If you install the Informatica Server on a machine different from the recommended configuration, make sure to update the $PMRootDir variable with the new location.

    
    

13. Close the Server window.

To configure the database connections

1. In Informatica Workflow Manager, choose Connections, then Relational to display the Relational Connection Browser.

2. Create a connection for the target data warehouse, as follows:

   1. Click New, select the appropriate sub-type, then click OK to display the Relational Connection Editor.

      
      

   2. If the target database is Oracle, DB2, or Teradata, use the settings as follows:

      • Click New and select the type as Oracle, DB2, or Teradata, then click OK.

      • Name: DataWarehouse (exactly as specified by selecting DAC, then setup, then Physical Data sources for Data Warehouse).

      • User Name: Table Owner.

      • Password: TBO password.

      • Connect string: Connect string for the connecting to the database.

      • Code Page: UTF-8 encoding of Unicode if the database is unicode.

   3. If the target database is SQL Server, use the settings as follows:

      • Click New and select the type as ODBC, then click OK.

      • Name: DataWarehouse (type in the name exactly as specified by selecting DAC, then Setup, then Physical Data sources for your OLTP (for example, if your source system is Siebel ver 7.8.x then you name this connection as SEBL_78).

      • User Name: Table Owner.

      • Password: TBO password.

      • Connect string: Connect string for the connecting to the database.

      • Code Page: UTF-8 encoding of Unicode if the database is unicode.

3. Create connections for the source database, as follows:

   1. Click New, select the appropriate sub-type, then click OK to display the Relational Connection Editor.

      
      

   2. If the target database is Oracle, DB2, use the settings as follows:

      • Click New and select the type as Oracle or DB2, then click OK.

      • Name: Type in the name exactly as specified by selecting DAC, then Setup, then Physical Data sources for your OLTP (for example, if your source system is Siebel ver 7.8.x then you name this connection as SEBL_78).

      • User Name: Table Owner.

      • Password: TBO password.

      • Connect string: Connect string for the connecting to the database.

      • Code Page: UTF-8 encoding of Unicode if the database is unicode.

   3. If the target database is SQL Server, use the settings as follows:

      • Click New and select the type as ODBC, then click OK.

      • Name: Type in the name exactly as specified by selecting DAC, then Setup, then Physical Data sources for your OLTP (for example, if your source system is Siebel ver 7.8.x then you name this connection as SEBL_78).

      • User Name: Table Owner.

      • Password: TBO password.

      • Connect string: Connect string for the connecting to the database.

      • Code Page: UTF-8 encoding of Unicode if the database is unicode.

4. Click Close to close the Relational Connection Browser.

To configure the Informatica repository in the Workflow Manager for Teradata external loader connections

1. In Informatica Workflow Manager, choose Connections, then Loader to open the Loader Connection Browser.

2. In the Objects window, select Teradata_Tpump_Upsert, and then click Edit.

3. Edit the User Name, Password, TDPID, Database Name, and other attributes as necessary.

4. Repeat these steps for the following objects:

   • Teradata_Tpump_Update

   • Teradata_Tpump_Insert

   • Teradata_Tpump_Delete

   • Teradata_FastLoad

5. For Unicode environments, append -c UTF8 to the value for the External Loader Executable attribute for each external loader.

   For example:

   • fastload -c UTF8

   • tpump -c UTF8

6. Click Close to close the Loader Connection Browser.

4.14.5 How to Stop and Start the Informatica Repository (Optional)

This section includes instructions for stopping and starting the Informatica repository.

To stop and start the Informatica repository

1. Start the Repository Server Administration Console in Informatica PowerCenter.

2. Display the Informatica Repository Administration dialog, as follows:

   1. In the console navigator at the left hand side, double click the Informatica Repository Server to expand this node.

   2. Right click on the server machine name that holds the Informatica Repository Server

   3. Choose Action, then Connect to display the Connecting to Repository Server <name> dialog.

   4. Enter the Administrator password for this machine (do not change the default port number 5001), and click OK.

   5. Double click the Repositories node.

   6. Double click the Oracle_BI_DW_Base repository.

      
      

3. To stop the Informatica Repository, click the Stop option.

4. To start the Informatica Repository, click the Start option.

4.14.6 How to Back Up and Restore the Informatica Repository (Optional)

To backup and restore the Informatica repository

1. Start the Repository Server Administration Console in Informatica PowerCenter.

2. Display the Informatica Repository Administration dialog, as follows:

   1. In the console navigator at the left hand side, double click the Informatica Repository Server to expand this node.

   2. Right click on the server machine name that holds the Informatica Repository Server

   3. Choose Action, then Connect to display the Connecting to Repository Server <name> dialog.

   4. Enter the Administrator password for this machine (do not change the default port number 5001), and click OK.

   5. Double click the Repositories node.

   6. Double click the Oracle_BI_DW_Base repository.

      
      

3. To backup the Informatica Repository, do the following:

   1. Click the Backup option to display the Backup Repository dialog.

   2. Enter the Informatica Repository Administrator username and password.

   3. In the File Name field, enter a short name for the repository backup (for example, Inf_rep_backup).

   4. Click OK to start the backup.

   Informatica backs up the repository to the <DRIVE>:\Program Files\Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup directory.

4. To restore the Informatica Repository, do the following:

   1. Right click on the Oracle_BI_DW_Base node and click Delete to remove the Informatica Repository.

   2. Click the Restore option to display the Restore Repository dialog.

   3. Select a backup version of the Informatica Repository.

   4. Click OK to start the procedure.

5. Check the Activity Log dialog to make sure that the backup or restore executes without errors.

   
   

4.15 How to Set the Code Page File Property for Unicode Environments on Teradata Databases

If you have a Unicode environment on a Teradata database, you need to set the code page file property of sessions that use the Teradata external loaders to use the code page UTF8. You need to follow this procedure to set the code page file property for each session that uses a Teradata external loader.

To set the code page file property for a Unicode environment on a Teradata database

1. Launch the Informatica Workflow Manager.

2. Open the appropriate session.

3. In the Edit Tasks dialog box, select the Targets tab.

4. Click Set File Properties.

5. In the Flat Files - Targets dialog box, in the File Properties area, click Advanced.

6. In the Fixed Width Properties - Targets dialog box, in the Code Page area, select UTF-8 encoding of Unicode.

4.16 How to Set Up the HOSTS File for Teradata Installations on Windows

If you are using a Teradata database, you need to set the TDPID parameter on the machine where the Informatica Server is installed by adding an entry in the HOSTS file.

To set up the HOSTS file for Teradata installations

1. On the machine where the Informatica Server is installed, go to the \<SystemRoot>\system32\drivers\etc directory and open the HOSTS file.

2. In the HOSTS file, enter a line in the following format:

   <IP address of remote server> <remote server> <remote server>COP<n>
   

   For example:

   172.20.176.208 tdatsvr tdatsvrCOP1
   

   where tdatsvrCOP1 is the alias for the remote server. The alias must begin with an alphabetic string and end with the COP n suffix, where n is a number between 1 and the total number of applications processors that are associated with the Teradata communications processor.

3. Save the HOSTS file.

For more information about setting the TDPID parameter, see the Teradata documentation.

4.17 How to Verify the Informatica Repository Configuration

Follow this procedure to verify the Informatica repository configuration.

To verify the Informatica repository configuration

1. Launch the Informatica Workflow Manager.

2. Double-click the ORACLE_BI_DW_SERVER icon that appears under the Oracle_BI_DW_Base icon.

   The Server dialog box opens.

3. Click Advanced to expose the Server Variables list.

4. Verify the following settings:

   • Host Name

   • Port

   • Code Page

   • $PMRootDir

5. Close the Server dialog box.

6. In the menu bar, select Connections, then Relational.

   The Relational Connection Browser window opens.

7. Verify the DataWarehouse and OLTP connection settings.

4.18 How to Configure the DAC Server

This section provides the procedures for configuring the connection between the DAC server and the DAC metadata repository, and for setting up email recipients. You must perform this task on the machine hosting the DAC server. There should be one installation of the DAC server for each DAC repository.

This section includes the following procedures:

• Section 4.18.1, "How to Configure the Connection Between the DAC Server and DAC Repository"

• Section 4.18.2, "How to Configure Email in the DAC Server (Optional)"

• Section 4.18.3, "How to Set DAC Source System Parameters"

• Section 4.18.4, "How to Start the DAC Server"

4.18.1 How to Configure the Connection Between the DAC Server and DAC Repository

Follow this procedure to configure the connection between the DAC server and the DAC repository.

To configure the connection between the DAC server and the DAC repository

1. Launch the DAC client.

2. From the menu bar, click Tools, then DAC Server Management, then DAC Server Setup.

   A confirmation dialog box asks you to confirm that you want to configure the DAC repository connection.

3. Click Yes.

   The Server Configuration dialog box appears.

   
   

4. In the Repository Connection Information tab, enter the appropriate information, as described below.

   
   

   Note: The DAC repository details that you specify here must match the DAC repository details that you specified in the following tasks: When you created a database to store the DAC repository (for more information, see Section 4.9, "How to Create the Required Databases"). When you created a DAC connection (for more information, see Section 4.10.1, "How to Create a DAC Connection").

   
   

   Field	Description
   Connection type	Select the type of database in which you are storing the DAC metadata repository. Depending on what type you select, the connection details below change (see Connection fields below).
   Connection fields (for example, Instance, TNS Name, Connection string/Database name.	Specify connection details for the database that stores the DAC metadata repository. If you select Oracle (Thin), you are prompted below for the following information: Instance (for example, mymachinename). Database Host (fully qualified, for example, mymachine.us.company.com). Database Port (for example, 1521). Table owner name, using the account that you created a database to store the DAC repository (for example, DAC). For more information, see Section 4.9, "How to Create the Required Databases". Password (that is, for the above database account). If you select Oracle (OCI8), you are prompted below for a TNS name (for example, [e-mail]). If you select DB2, you are prompted below for an Connection string. If you select MYSQL, you are prompted below for a Database name (for example, mydacdatabase).
   Table owner name	The database table name or instance (for example, DAC) that you created to store the DAC repository (for more information, see Section 4.9, "How to Create the Required Databases").
   Password	The database or instance password (for example, DAC).

   
   

5. Click Test Connection to make sure the DAC repository connection works.

6. Click Save.

4.18.2 How to Configure Email in the DAC Server (Optional)

Follow this procedure to configure the email administrator account in the DAC, which enables the recipient to receive ETL status updates automatically. For example, the data warehousing administrator might want to be informed when an ETL routine has completed.

The DAC server has a built-in login-authentication based email (SMTP) client, which connects to any SMTP login-authenticating server to send emails.

Note: For the email functionality to work, you must be using an SMTP server in which the SMTP authentication mode LOGIN is enabled. For example, if you are using Microsoft Exchange Server, you must enable the SMTP module and enable authentication in the Basic Mode. The SMTP server outbound email authentication must be turned on for the DAC server to be able to send email notifications.

To configure the email administrator account in the DAC server

1. Launch the DAC client.

2. From the menu bar, click Tools, then DAC Server Management, then DAC Server Setup.

   A confirmation dialog box asks you to confirm that you want to configure the DAC repository connection.

3. Click Yes.

   The Server Configuration dialog box appears.

4. In the Email Configuration tab, enter the appropriate information, and click Save.

Once the configuration has been completed, you can start the DAC server.

4.18.3 How to Set DAC Source System Parameters

You set Source System Parameters (also known as DAC ETL Preferences) in the DAC to specify how the ETL routines process data for a container. For example, if operating in the United States, you might set the $$DLFT_COUNTRY to 'USA' to identify your data.

Note: The following preferences are applied to all tasks within a container. If extracting data from more than one source, these preferences will need to be re-applied to each associated container. They can be overridden at the task level by adding the parameter to the specific task and assigning a value there.

To set DAC source system parameters

1. Log into the DAC.

2. Display the Design view.

3. Make sure that you have selected the correct container from the containers drop down list.

4. Select the Source System Parameters tab.

   
   
   
   

   Note: For information about recommended settings for specific databases, see Chapter 3, "Pre-installation and Predeployment Requirements for Oracle BI Applications".

   
   

5. Use the Edit tab below the list of Source System Parameters to change the value of parameters in the list.

6. Click Save.

4.18.4 How to Start the DAC Server

You start the DAC server on the Windows machine that is used to run the DAC client. Follow this procedure to start the DAC Server.

To start the DAC Server

1. Select Oracle Business Intelligence, then Oracle DAC, and then Start DAC Server.

Tip: Use the DAC Server status icon in the DAC console to make sure that the DAC Server has started. The DAC Server status icon should either be orange (idle) or green (active). The screen shot below shows the DAC Server status icon highlighted.

4.19 How to Start the Informatica Server

At this point in the installation and configuration process, you can start the Informatica Server. It should now be able to communicate with the Informatica Repository Server and the Informatica repository.

To start the Informatica Server

1. Navigate to Start, then Settings, then Control Panel, then Administrative Tools, then Services.

2. Double-click Informatica.

   The Informatica Properties dialog box opens.

3. In the General tab, in the Startup Type drop-down list, select Automatic.

4. In the Log On tab, if you plan on using email notification, enter the appropriate username and password. Select System Account if you do not plan on using email notification.

5. In the General tab, in the Server Status area, click Start.

6. Close the Services window.

4.20 How to Update Siebel Transactional Database Schema Definitions

Note: You must perform the following steps for each Siebel source database type.

This section explains how to update Transactional Database Schema Definitions, and contains the following sections:

• Section 4.20.1, "How to Run the DDLIMP Tool From The Command Line"

• Section 4.20.2, "How to Apply Siebel (CRM) and Oracle E-Business Suite Schema Changes For Oracle, DB2/UDB, MSSQL"

• Section 4.20.3, "How to Apply CRM Schema Changes For DB2 on 390 or z/os"

• Section 4.20.4, "How to Verify the Siebel (CRM) and Oracle E-Business Suite Schema Changes"

• Section 4.20.5, "About Delete Triggers"

4.20.1 How to Run the DDLIMP Tool From The Command Line

When you use the DDLIMP utility from a command line to update schema definitions, refer to the following notes:

To run DDLIMP from command line, run the following command:

\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /GSSE_ROLE /F <..\OracleBI\dwrep\DDL_OLTP.CTL> /L <..\oracleBI\dwrep\DDL_OLTP.log>

NOTE: For Oracle, please use the Siebel Merant ODBC Drivers. For MSSQL you need to use both flags /w and /Z. The syntax for MSSQL Server is:

\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /GSSE_ROLE /W Y /Z Y /F <..\OracleBI\dwrep\DDL_OLTP.CTL> /L<..\oracleBI\dwrep\DDL_OLTP.log>

Additionally you can use the following commands:

/W Y (if the OLTP database is Unicode).

/Z Y (if the OTTP database is DB2 and Unicode).

/B <TABLE_SPACE_NAME> if you want to create these table in a separate table space.

/X <INDEX_TABLE_SPACE_NAME> if you want to create the indexes in a separate table space.

/Y Storage File for DB2/390.

4.20.2 How to Apply Siebel (CRM) and Oracle E-Business Suite Schema Changes For Oracle, DB2/UDB, MSSQL

To enable change capture for Oracle's Siebel adapters, you use the ddlimp control file to apply Siebel (CRM) and Oracle E-Business Suite schema changes, which updates the required image tables in the OLTP.

Note: Using the ddlimp control file to apply schema changes replaces the use of SIF files in Siebel Tools in previous product releases.

How to Apply Oracle's CRM Schema Changes For Oracle, DB2/UDB, MSSQL

1. On the machine where Oracle BI Applications is installed, run the following command:

   ..\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC connect string> /G SSE_ROLE /F <..\OracleBI\dwrep\DDL_OLTP.CTL> /L <..\oracleBI\dwrep\DDL_OLTP.log>
   

   
   

   Note: If you are applying schema changes to a Siebel CRM Version 6.3, specify DDL_OLTP_63.CTL as the /F parameter instead of DDL_OLTP.CTL.

   
   

   For example:

   DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE /F <DRIVE>:\OracleBI\dwrep\DDL_OLTP.CTL /L <DRIVE>:\OracleBI\dwrep\DDL_OLTP.log
   

   Notes:

   • /P <PASSWORD> - The password for Oracle's CRM OLTP.

   • /C <ODBC connect string> - The name of the ODBC connect string.

   • For Oracle databases, use the Oracle Merant ODBC Drivers (installed with Oracle BI Applications).

   • In addition, you can use the following commands:

     /W Y - (if the OLTP database is Unicode).

     /Z Y - (if the OLTP database is DB2 and Unicode or the OLTP database is MS SQL Server and Unicode).

     /B <TABLE_SPACE_NAME> - If you want to create these table in a separate table space.

     /X <INDEX_TABLE_SPACE_NAME> - If you want to create the indexes in a separate table. space.

     /Y - Storage File for DB2/390.

2. Restart all servers.

4.20.3 How to Apply CRM Schema Changes For DB2 on 390 or z/os

How to apply CRM schema changes for DB2 on 390 or z/os

1. Edit the following parameters in the Storage control files located in \OracleBI\dwrep\Storeage_DDL_OLTP.CTL:

   • %1 - Replace with a 2 character database name.

   • %indBufPool - Replace it with a index buffer pool name.

   • %4kBulfPool - Replace it with a 4k TBS buffer pool name.

   • %32kBufPool - Replace it with a 32K TBS Buffer Pool name.

2. On the machine where Oracle BI Applications is installed, run the following command:

   ..\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> >>> /G SSE_ROLE /F \OracleBI\dwrep\DDL_OLTP_DB2390.CTL> /L <..\oracleBI\dwrep\DDL_OLTP.log> /5 Y /A <SCHEMA_OWNER> /Y ..\OracleBI\dwrep\Storeage_DDL_OLTP.CTL
   

   Notes:

   • /P <PASSWORD> - The password for Oracle's CRM OLTP.

   • /C <ODBC connect string> - The name of the ODBC connect string.

   • For Oracle databases, use the Oracle Merant ODBC Drivers.

   • In addition, you can use the following commands:

     /W Y - (if the OLTP database is Unicode).

     /Z Y - (if the OLTP database is DB2 and Unicode or the OLTP database is MS SQL Server and Unicode).

     /B <TABLE_SPACE_NAME> - If you want to create these table in a separate table space.

     /X <INDEX_TABLE_SPACE_NAME> - If you want to create the indexes in a separate table. space.

     /Y - Storage File for DB2/390.

3. To create indices for the tables created, run the following SQL Script from the DB2 command line utility connected to your OLTP Schema:

   \OracleBI\Dwrep\Create_OLTP_Db2390_index.sql

4.20.4 How to Verify the Siebel (CRM) and Oracle E-Business Suite Schema Changes

After applying Siebel (CRM) and Oracle E-Business Suite schema changes, you need to verify that appropriate tables were created in the transactional database.

To verify Siebel (CRM) and Oracle E-Business Suite schema changes

1. For Oracle's Siebel Business Applications, make sure that the following tables were created in the transactional database:

   • S_ETL_R_IMG_1 through S_ETL_R_IMG_166

   • S_ETL_I_IMG_1 through S_ETL_D_IMG_166

   • S_ETL_D_IMG_1 through S_ETL_D_IMG_166

   • S_ETL_PARAM

   • S_ETL_PRD_ATTR

   • S_ETL_PRD_REL

4.20.5 About Delete Triggers

Note: Delete triggers are only used supported with Siebel CRM database.

Delete records in Siebel CRM sources are not propagated to the Data Warehouse tables. However the mechanism to identify the delete records is provided, as follows:

The DAC can create delete triggers on source tables (please refer to Oracle Business Intelligence Data Warehouse Administration Console User Guide. These triggers write the primary keys of deleted records with the flag "D" in the corresponding S_ETL_I_IMG table. You need to write a custom SDE extract to pull these deleted primary keys from the image table and take corresponding action on the Datawarehouse table.

4.21 How to Create Stored Procedures for DB2-UDB

The DAC uses siebstat and siebtrun stored procedures when running ETL processes. Typically, these stored procedures are available in your transactional database; they might not be available on the data warehouse database.

If you need to install the DB2 stored procedures manually, they are located in the installation directory \OracleBI\dwrep\siebproc\db2udb\. There is a sub-directory for each platform. For example, stored procedures for the Windows platform are stored in the sub-directory \OracleBI\dwrep\siebproc\db2udb\win32\.

Each platform-specific directory contains the following sub-directories:

• \siebproc\ (containing stored procedures for a 32-bit DB2 environment)

• \siebproc64\ (containing stored procedures for a 64-bit DB2 environment)

These directories also contain the files siebproc.sql and sqlproc.ksh, which are used to create the function. For more information, see Section 4.21.2, "How to Create DB2 stored procedures").

4.21.1 How to Verify the existence of stored procedures

This section describes how to verify the existence of stored procedures.

To verify the existence of these procedures, follow the steps below.

• From a DB2 command prompt or control center, issue the following SQL scripts:

  db2 => create table test_siebproc(id int);
  DB20000I  The SQL command completed successfully.
  
  db2 => insert into test_siebproc(id) values (1);
  DB20000I  The SQL command completed successfully.
  
  db2 => call siebtrun('TEST_SIEBPROC');
  SQL0805N  Package "NULLID.SYSSH200" was not found.  SQLSTATE=51002
  

  
  

  Note: If you get an error message, you do not have the required stored procedures.

  
  

Creating stored procedures must be performed by the database administrator (for more information, see Section 4.21.2, "How to Create DB2 stored procedures").

4.21.2 How to Create DB2 stored procedures

This section describes how to create DB2 stored procedures.

To create DB2 stored procedures

1. Copy the DB2 stored procedure directory (i.e. \siebproc\ or \siebproc64\) from the appropriate platform-specific directory to a directory on the DB2 server side.

   For example, for a 32-bit DB2 environment on a Windows platform, you might copy the directory \OracleBI\dwrep\siebproc\db2udb\win32\siebproc\ to the directory d:\Program Files\SQLLIB\function\ on the DB2 server side.

   
   

   Note: For more information about the location of DB2 stored procedures, see Section 4.21, "How to Create Stored Procedures for DB2-UDB").

   
   

2. If you copied stored procedures for a 64-bit DB2 environment, on the DB2 server side, rename the \siebproc64\ directory to \siebproc\.

   For example, if you copied stored procedures to d:\Program Files\SQLLIB\function\siebproc64\, rename this directory to d:\Program Files\SQLLIB\function\siebproc\.

Once these procedures are created, you can verify that they exist. After the test is complete, you can drop the table TEST_SIEBPROC.

4.22 Teradata-Specific Installation and Configuration Checklist (Windows)

The table below provides a list of Teradata-specific installation and configuration steps that are performed during the Oracle BI Applications installation and configuration process. You should review this list to make sure that you have performed all of the required Teradata-specific steps.

Table 4-3 Teradata-Specific Installation Checklist

Action	Link to Topic
Review Teradata-specific database guidelines.	Section 3.9, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse".
Activate join indices.	Section 4.11.4, "How to Activate Join Indices for Teradata Databases".
Create or drop data warehouse tables.	Section 4.12.3, "How to Create Data Warehouse Tables on a Teradata Database".
Use recommended name for Informatica repository.	Section 4.5, "How to Set Up the Informatica Server and Informatica Repository Server".
Create or restore the Informatica repository using the Informatica Repository Manager. You cannot create or restore the Informatica repository using the DAC.	Section 4.14.2, "How to Register the Informatica Repository Server".
Configure the source and data warehouse database relational connections in the Informatica Workflow Manager. You cannot use the DAC's Data Warehouse Configurator to configure relational connections.	Section 3.9, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse".
Set the Code Page File Property for Unicode Environments.	Section 4.15, "How to Set the Code Page File Property for Unicode Environments on Teradata Databases".
Set up the HOSTS file.	Section 4.16, "How to Set Up the HOSTS File for Teradata Installations on Windows".

4.23 Upgrade Issues for Oracle BI Applications

This section contains miscellaneous upgrade issues and workarounds, and contains the following topics:

• Section 4.23.1, "Moving From a Test Environment to a Production Environment"

• Section 4.23.2, "Migrating Seed Data from an Siebel Source System to the Oracle BI Repository"

4.23.1 Moving From a Test Environment to a Production Environment

Oracle Business Intelligence Applications 7.9.x uses sequence generator transformation in Informatica rather than a re-usable expression generated ROW_WID. The sequence generators in the 7.9.x mappings do not look up the target table to identify the Max(ROW_WID), and instead store the current ROW_WID value for each row inserted.To see how this works in 7.9.x, please review for example SIL_OrderFact mapping. This contains a mapplet mplt_SIL_OrderFact, which in turn contains a sequence generator transformation Seq_W_Order_F_WID.

Using sequence generator transformation information in Informatica can cause Key index violation database error (on Max(ROW_WID)) if an Informatica repository is moved from a Development/Test environment to a Production environment.

Since the data in the two different environments might not be in sync, the target tables in the OLAP database may contain different ROW_WIDs for the same table in each environment. As a result, when the Informatica repository is moved for instance from Test to Production, the sequence generator transformations in the mappings hold on to the ROW_WIDS generated in Test environment, when generating the next set of ROW_WIDS for the Production environment. These could very well be duplicate values already existing in the Production environment, thus resulting in primary key violations (on ROW_WID column) during data load.

Typical symptoms include a database error pointing to Primary Key violation on ROW_WID column during data load.

The workaround for this issue is to run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs, as follows:

1. Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.

2. Open the Reset_infa_seq_gen.bat script, and review/customize the parameters at the top of the file.

   In circumstances where this step was omitted after migrating the repository, and the ETL has been run, the index violation prevents data corruption.

3. Re-queue the failed DAC Tasks, to allow the ETL to continue.

4.23.2 Migrating Seed Data from an Siebel Source System to the Oracle BI Repository

This section describes how to move localized string content from the S_MSG table in a Siebel OLTP database to the W_LOCALIZED_STRING_G table in the Business Analytics Warehouse database. Use the following steps:

1. Export the customized localization data in the S_MSG table using the DATAEXP command from a command prompt, as shown in the following sample text:

   DATAEXP /U <Username for OLTP read access> /P <Password> /C <Siebel_OLTP odbc> /D <Table Owner> /F CUSTSTRINGS.DATA /W Y /L <EXPCUST.LOG>

2. Respond to the prompts as follows:

   • When you are prompted for the TABLE_NAME to export, enter S_MSG and press ENTER.

   • When you are prompted for the WHERE CLAUSE for filtering records from the table, enter WHERE LAST_UPD > '1/1/1980' and press ENTER twice.

3. A message is displayed that indicates the number of rows that were exported. Press ENTER to enter the Database Table Name and to end the process.

4. Import the data into the W_LOCALIZED_STRING_G table using the data file that you generated in the previous step.

   For information about importing localization data into the W_LOCALIZED_STRING_G table, see Section B.1.1, "Upgrading Oracle Business Intelligence Seed Data for Non-English Locales".

   Use the /R 1000000 argument in the DATAIMP command to insert the customized strings beyond Row Id 1000000.

5. Import the localization data into the W_LOCALIZED_STRING_G table.