4 Installing and Setting Up Oracle BI Applications

This section explains how to install and set up the Oracle BI Applications components.

It contains the following main topics:

• Section 4.1, "About the Oracle Business Intelligence Applications Installer"

• Section 4.2, "About Oracle BI Applications Topologies"

• Section 4.3, "Mandatory Requirements"

• Section 4.4, "High-Level Installation and Setup Task List"

• Section 4.5, "Setup Task: Preinstallation Tasks"

• Section 4.6, "Setup Task: Installing the Oracle BI Applications files"

• Section 4.7, "Setup Task: Installing and Setting Up Informatica PowerCenter"

• Section 4.8, "Setup Task: Installing and Setting Up the DAC Platform"

• Section 4.9, "Setup Task: Log into DAC for the First Time, Configure DAC Server, and Import Metadata into the DAC Repository"

• Section 4.10, "Setup Task: Creating the Oracle Business Analytics Warehouse Tables"

• Section 4.11, "Setup Task: Configuring the DAC Server"

• Section 4.12, "Setup Task: Configuring DAC Integration Settings"

• Section 4.13, "Setup Task: Configuring Relational and Application Connections in Informatica Workflow Manager"

• Section 4.14, "Setup Task: Configuring the SiebelUnicodeDB Custom Property"

• Section 4.15, "Setup Task: Setting Up DAC to Receive Email Notification"

• Section 4.16, "Setup Task: Configuring the Oracle BI Repository Connections"

• Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog"

• Section 4.18, "Setup Task: Applying the Oracle BI Applications Security Policy to the BI Domain"

• Section 4.19, "Setup Task: Additional Configuration Tasks"

• Section 4.20, "About Running A Full Load ETL"

For information about supported Windows versions, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

4.1 About the Oracle Business Intelligence Applications Installer

The Oracle Business Intelligence Applications Installer only runs on Windows. However, many Oracle BI Applications components can also be deployed on Linux and UNIX platforms by manually copying folders and files from the Windows install machine to a Linux or UNIX machine. Instructions for installing and setting up Oracle BI Applications components on Linux and UNIX are also provided in this chapter.

4.2 About Oracle BI Applications Topologies

Oracle BI Applications and Informatica PowerCenter can be deployed flexibly across a wide range of topologies on different platforms and combinations of platforms.

This section describes a typical topology for an Oracle BI Applications deployment, as illustrated in Figure 4-1.

Figure 4-1 Typical Topology for an Oracle BI Applications Deployment

In Figure 4-1, note the following:

• Installation

  • Machine A (Windows-only)

    Machine A is a machine that has installed Oracle Business Intelligence Enterprise Edition, on which you run the Oracle BI Applications installer to install the Oracle BI Applications files.

    Note: The instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version of Oracle Business Intelligence Enterprise Edition that you will use to deploy dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files onto a machine.

    After the Oracle BI Applications files have been installed on Machine A, the DAC Client is installed on Machine B along with Informatica PowerCenter Client Tools, and the DAC Server is installed on Machine C along with Informatica PowerCenter Services.

    In addition, the following files are copied from the installation machine (Machine A) to the Business Intelligence Deployment Tier (Machine Group F) as follows:

    - The OracleBIAnalyticsApps.rpd file is copied from Machine A to the machine that runs the BI Server in Machine Group F.

    - The Presentation Catalog files (in EnterpriseBusinessAnalytics.zip) are copied from Machine A to the machine that runs the Presentation Catalog in Machine Group F.

    Copying BI components to appropriate deployment machines is covered by Step 12 in Section 4.4, "High-Level Installation and Setup Task List." For more information about copying the RPD file to the BI Server, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog."

• ETL Tier (Functional)

  • Machine B (Windows-only)

    Runs the DAC Client and Informatica PowerCenter Client Tools.

  • Machine C (Windows, UNIX, Linux)

    Runs the DAC Server and Informatica PowerCenter Services.

  • Machine D (Windows, UNIX, Linux)

    Hosts the transactional (OLTP) database.

  • Machine E (Windows, UNIX, Linux)

    Hosts the Oracle Business Analytics Warehouse database.

• BI Deployment Tier (Functional)

  The BI Deployment tier is used to deploy the business intelligence dashboards.

  • Machine Group F (Windows, UNIX, Linux)

    Machine Group F is a group of machines that runs the Oracle Business Intelligence Enterprise Edition components. For example, one machine might run the BI Server and another machine might run the BI Presentation Services.

    For more information about copying the RPD file and Presentation Catalog Services to appropriate Oracle BI EE machines, see Step 3 in Section 4.4, "High-Level Installation and Setup Task List".

4.2.1 Summary of Oracle Home Locations Used in this Guide

The table below defines the Oracle Home locations for the components in an Oracle BI Applications deployment.

Table 4-1 Summary of Home Locations

Oracle Home folder	Description
DAC_HOME	The full path to the \dac folder on the DAC installation machine, (for example, C:\DAC11g\dac).
DOMAIN_HOME	Location of the Oracle BI Domain (for example, MW_HOME\user_projects\domains\bifoundation_domain. When you run the Oracle BI Applications installer, you specify this location using the 'Enter the Domain Home Location' field.
INFA_HOME	User-specified location of the Informatica installation folder on the machine that hosts Informatica PowerCenter Services (for example,C:\Informatica\9.0.1\).
MW_HOME	User-specified location of the Oracle Fusion Middleware folder where Oracle Business Intelligence Enterprise Edition is installed (for example, C:\BIEE_MW).
ORACLE_HOME	Location of the Oracle BI EE Infrastructure home (for example, MW_HOME\Oracle_BI1\). Note: The Oracle BI Applications installer installs into ORACLE_HOME\biapps.
ORACLE_INSTANCE	Location of the Oracle Instance home (for example, MW_HOME\instances\instance1).

4.3 Mandatory Requirements

This section includes mandatory requirements that you must satisfy before you can deploy Oracle BI Applications.

This section contains the following topics:

• Section 4.3.1, "Oracle Business Intelligence Infrastructure Requirements for the Oracle BI Applications Installer"

• Section 4.3.2, "Informatica PowerCenter Requirements"

• Section 4.3.3, "Code Page and Data Movement Requirements"

4.3.1 Oracle Business Intelligence Infrastructure Requirements for the Oracle BI Applications Installer

The Oracle BI Applications installer runs on Windows and requires an Oracle Business Intelligence infrastructure (or ORACLE_HOME) to be installed in an Oracle Fusion Middleware home folder (or MW_HOME). For more information about Oracle home locations, see Section 4.2.1, "Summary of Oracle Home Locations Used in this Guide".

Note: This instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version that you will use to deploy reports and dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files on a machine. The functional version of BI EE can be on any supported OS for BI EE (that is, it is not limited to Windows). However, a Windows machine is required to edit BI metadata using Oracle BI Administration Tool.

To determine the minimum version of Oracle Business Intelligence Enterprise Edition that is supported for this release of Oracle BI Applications, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

4.3.2 Informatica PowerCenter Requirements

Oracle BI Applications release 7.9.6.4 requires Informatica PowerCenter V9.0.1 with Hotfix 2. If you license Informatica PowerCenter with Oracle BI Applications, you obtain Informatica PowerCenter software and documentation from the BI Media Pack on Oracle Software Delivery Cloud.

If you license Informatica PowerCenter separately and you do not have Informatica PowerCenter V9.0.1 and Hotfix 2, you must upgrade your Informatica PowerCenter license to V9.0.1 and Hotfix 2 before deploying Oracle BI Applications.

Note: See System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hot fixes or emergency bug fixes that might be required to support the current version of Informatica.

Before installing Oracle BI Applications, Oracle recommends that you do the following:

• Read the Informatica PowerCenter documentation to familiarize yourself with the new architecture, components, and features. Informatica PowerCenter documentation is available in the BI Media Pack.

• Plan your topology carefully before installing Informatica PowerCenter Services.

Also, note the following about Informatica and DAC requirements:

• Informatica PowerCenter Client Tools and the DAC Client must be co-located on the same machine.

• PowerCenter Services and the DAC Server must be co-located on the same machine.

• Informatica PowerCenter Services runs on 32-bit or 64-bit platforms. For more information about these two platforms, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

About Changes in Informatica Terminology

Review this section if you are upgrading from an Informatica 7.x version to Informatica PowerCenter V9.0.1.

Table 4-2 Changes in Informatica Terminology

Term used in 7.1.x versions of Informatica PowerCenter	Term used in 9.x version of Informatica PowerCenter
Informatica Repository	Informatica Repository The Informatica Repository is managed by the Repository Service.
Informatica Repository Server	Not applicable. This component does not exist in Informatica PowerCenter 9.0.1. The Informatica Repository is managed by the Repository Service.
Informatica Server	Integration Services
Not applicable	Repository Service

4.3.3 Code Page and Data Movement Requirements

The Oracle Business Analytics Warehouse can be deployed in various code page environments and supports global deployments. Data movement in the following source database and data warehouse configuration modes are supported:

• Unicode to Unicode

• Code page (multi- or single-byte) to Unicode

• Code page to code page (where the code pages are the same)

Oracle BI Applications uses Informatica PowerCenter to perform extract, transform and load routines to move data from source database(s) to the Oracle Business Analytics Warehouse.

During the installation and configuration procedures described in this chapter, you will make various settings to enable accurate data movement. Use the guidelines and references noted below to determine values for these settings that are appropriate for your environment:

• Determining the source to target configuration mode. Consult your database administrator to determine the code page your source OLTP database uses. Based on the type of data that will be moved from one or more source databases to the Oracle Business Analytics Warehouse, determine what code page you will need to use for the Oracle Business Analytics Warehouse database. Consider future requirements for storing data when determining what code page to use for the Oracle Business Analytics Warehouse.

  For accurate data movement from source database to target, the code page of the Oracle Business Analytics Warehouse (target) must be a superset of the code page of the source database. Informatica considers a code page to be a superset of another code page when the code page contains all the characters encoded in the other code page and additional characters not encoded in the other code page.

  Note: To enable data movement from source(s) to the Oracle Business Analytics Warehouse, you will set relaxed Code Page Validation for the Integration Services. You must ensure that the target code page is a superset of the source code page for accurate data movement.

• Setting the SiebelUnicodeDB property. If your source to target configuration mode for data movement is Unicode to Unicode, you will set a custom property called SiebelUnicodeDB on the Integration Services. Configuration modes of code page to Unicode or code page to code page do not require this property to be set.

• Determining the Data Movement Mode. Before you deploy Oracle BI Applications, you must determine what data movement mode to use (ASCII or Unicode) for the PowerCenter Integration Service. The Character Data Movement Mode is an Informatica PowerCenter Integration Service option that you choose based on whether you want to move single-byte or multi-byte data.

  Choose Unicode if non-ASCII characters have to be moved. Otherwise, choose ASCII. The Data Movement Mode option is configurable and can be reset after installation.

  To set the Data Movement Mode, log into Informatica Administrator, select the Integration Service, then click the Properties tab, then display the General Properties tab, and set the DataMovementMode value.

• Determining code pages for Informatica PowerCenter components. In addition to source and target code pages, Informatica PowerCenter uses code pages for PowerCenter Client, the Integration Service, the Informatica Repository, and PowerCenter command line programs (pmcmd and pmrep, which are used by DAC to communicate with PowerCenter Services).

  Carefully review the section "Understanding Globalization" (Chapter 22 in the book version) in the Informatica PowerCenter Administrator Guide, particularly if your environment requires the Data Mode Movement mode to be set to UNICODE. The section discusses code page compatibility and code page requirements for the Informatica components.

• Setting environment variables. You must manually set the appropriate environment variables for UNIX environments. In addition, the Informatica installer requires the appropriate locale to be set on UNIX machines. Use LANG, LC_CTYPE or LC_ALL to set the UNIX code page. For more information, see the topic titled "Configuring Environment Variables," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.

  If your environment uses Oracle or DB2 database, you need to set the environment variables NLS_LANG or DB2CODEPAGE. For information on how to set these environment variables see Section 4.5.2.2, "How to Set the NLS_LANG Environment Variable for Oracle Databases" and Section 4.5.2.3, "How to Set the DB2CODEPAGE Environment Variable for DB2 Databases."

• Configuring Relational Connections. When you configure relational connections in the Workflow Manager, choose a code page that is compatible with the code page of the database client. If you set a database environment variable to specify the language for the database, ensure the code page for the connection is compatible with the language set for the variable. For example, if you set the NLS_LANG environment variable for an Oracle database, ensure that the code page of the Oracle connection is identical to the value set in the NLS_LANG variable.

For more information about data movement modes, refer to the Informatica PowerCenter documentation.

4.4 High-Level Installation and Setup Task List

This section describes the high-level tasks that you must complete in the specified order to install and set up Oracle BI Applications.

Note: The tasks in the following list form Step 2 in Section 2.4, "Roadmap to Installing, Setting Up, and Configuring Oracle BI Applications".

You must complete these tasks in the order listed below.

1. Before you start, do the following:

   1. Follow the appropriate preinstallation steps in Chapter 3, "Preinstallation and Deployment Requirements for Oracle BI Applications."

   2. On the target installation machine, make sure that you have a valid Oracle Middleware Home (MW_HOME) that contains a valid Oracle BI EE Infrastructure Home (ORACLE_HOME). For more information about Home locations, see Section 4.2.1, "Summary of Oracle Home Locations Used in this Guide.".

      You must make sure that:

      - The BI EE system is installed and configured using the Simple or Enterprise installation type from the Oracle BI EE installation.

      - The WebLogic Administration Server and NodeManager needs to be running. The Oracle BI Applications installer validations check that the WebLogic Administration Server and NodeManager are running. If these two processes are not running, then the Oracle BI Applications installation will not proceed.

   3. Make sure that you satisfy the Informatica PowerCenter requirements that are specified in Section 4.3.2, "Informatica PowerCenter Requirements."

   4. Make sure that you satisfy the Code Page requirements that are specified in Section 4.3.3, "Code Page and Data Movement Requirements."

   5. Make sure that you perform the mandatory preinstallation tasks that are specified in Section 4.5, "Setup Task: Preinstallation Tasks." The preinstallation tasks are the following:

      - Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"

      - Section 4.5.2, "Install and Configure Database Connectivity Software"

      - Section 4.5.3, "Perform Prerequisites for Informatica PowerCenter Installation"

2. Run the Oracle BI Applications 7.9.6.4 installer on a Windows machine that contains a valid Oracle BI EE Infrastructure Home (ORACLE_HOME). For more information, see Section 4.6, "Setup Task: Installing the Oracle BI Applications files."

   1. Make sure that a complete installation of Oracle Business Intelligence Enterprise Edition is installed on the machine on which you want to install Oracle BI Applications. For more information, see Section 4.3.1, "Oracle Business Intelligence Infrastructure Requirements for the Oracle BI Applications Installer."

      Note: This instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version that you will use to deploy dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files on a machine.

3. Install Informatica PowerCenter Services and Client Version 9.0.1 Hotfix 2 software and set up the Informatica components for use with Oracle BI Applications. For more information, see Section 4.7, "Setup Task: Installing and Setting Up Informatica PowerCenter."

   Note:

   Informatica PowerCenter Client must be co-located with DAC Client. Informatica PowerCenter Server must be co-located with DAC Server.

   Note:

   See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hot fixes or emergency bug fixes that might be required to support the current version of Informatica.

   1. Install Informatica PowerCenter Client as described in Section 4.7.1, "Installing Informatica PowerCenter Client Tools (Windows)."

   2. Install Informatica PowerCenter Services as described in Section 4.7.2, "Installing Informatica PowerCenter Services."

      During the installation, you are prompted to create an Informatica Domain using a suitable database account. For information about creating a suitable database account, see Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".

   3. Create the Informatica Repository Service as described in Section 4.7.3, "Creating the Informatica Repository Service."

   4. Create the Informatica Integration Service as described in Section 4.7.4, "Creating the Informatica Integration Service."

   5. Restore the Pre-built Informatica Repository as described in Section 4.7.5, "Restoring the Pre-built Informatica Repository."

   6. Configure the Informatica PowerCenter components as described in Section 4.7.6, "Configuring Informatica PowerCenter Components."

4. Restore the pre-built Informatica Repository file provided with Oracle BI Applications. This file includes ETL repository objects, such as mappings, sessions, and workflows. For more information, see Section 4.7.5, "Restoring the Pre-built Informatica Repository."

5. Install the DAC Client and Server Platform. The DAC Platform is installed by the Oracle Data Warehouse Administration Console (DAC) installer. For more information, see Section 4.8, "Setup Task: Installing and Setting Up the DAC Platform."

   Note:

   Informatica PowerCenter Client must be co-located with DAC Client. Informatica PowerCenter Server must be co-located with DAC Server.

   1. (On Windows) Install DAC as described in Section 4.8.1, "Installing DAC on Windows Using the DAC Installer."

   2. (On UNIX) Install DAC as described in Section 4.8.3, "Deploying the DAC Server on Linux or UNIX."

   3. Install JDBC Drivers for DAC as described in Section 4.8.4, "Installing a JDBC Driver for DAC Database Connectivity."

   4. (Siebel-specific) Create ODBC Connections for the DAC Client as described in Section 4.8.5, "(Siebel specific) Creating an ODBC Database Connection for a Siebel Source System."

   5. Install Informatica pmcmd and pmrep as described in Section 4.8.6, "Setting Up pmcmd to Enable Communication Between Informatica PowerCenter and DAC."

   6. Install DAC Metadata files as described in Section 4.8.7, "Installing DAC Metadata Files."

6. Log into DAC and Import Metadata into the DAC Repository. For more information, see Section 4.9, "Setup Task: Log into DAC for the First Time, Configure DAC Server, and Import Metadata into the DAC Repository."

7. Create the Oracle Business Analytics Warehouse tables. For more information, see Section 4.10, "Setup Task: Creating the Oracle Business Analytics Warehouse Tables."

8. Configure the DAC Server. For more information, see Section 4.11, "Setup Task: Configuring the DAC Server."

9. Configure DAC Integration Settings. For more information, see Section 4.12, "Setup Task: Configuring DAC Integration Settings."

10. Create Relational Connections in Informatica Workflow Manager, as specified in Section 4.13, "Setup Task: Configuring Relational and Application Connections in Informatica Workflow Manager."

11. Configure the Oracle BI Repository connections, as specified in Section 4.16, "Setup Task: Configuring the Oracle BI Repository Connections."

12. Copy the Oracle BI Applications RPD and BI Presentation Catalog to a suitable Oracle BI EE Machine and deploy them. For more information, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog."

13. Configure the SiebelUniCodeDB Custom Property, as specified in Section 4.14, "Setup Task: Configuring the SiebelUnicodeDB Custom Property."

14. Set up DAC to receive email notification. For more information, see Section 4.15, "Setup Task: Setting Up DAC to Receive Email Notification."

15. If your deployed BI EE system with Oracle BI Applications is different from the BI EE system used to install Oracle BI Applications, then you must apply the security policy to the BI Domain on the deployed BI EE system by following the steps in Section 4.18, "Setup Task: Applying the Oracle BI Applications Security Policy to the BI Domain.".

    If your deployed BI EE system with Oracle BI Applications is the same as the BI EE system used to install Oracle BI Applications, then the Oracle BI Applications installer performs this configuration automatically.

16. Perform required post-installation tasks that are specific to your deployment, as follows.

    • Section 4.19.1, "Configuring Security-Related Initialization Blocks."

    • Section 4.19.2, "Setting DAC Source System Parameters."

    • Section 4.19.3, "Creating Stored Procedures for DB2-UDB."

    • Section 4.19.4, "Siebel-Specific Configuration Tasks."

    • Section 4.19.5, "Teradata-Specific Configuration Tasks."

    For more information, see Section 4.19, "Setup Task: Additional Configuration Tasks."

Notes:

After you have completed the steps listed above, and completed all required configuration steps and customizations as described in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users, you are ready to perform a full load of your OLTP data. For an example of performing a full load of OLTP data, see Section 4.20, "About Running A Full Load ETL." For detailed information about using DAC to perform ETL processes, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.

After installing Oracle Business Intelligence Applications, you need to use Fusion Middleware Control to set the appropriate Presentation Services settings. For information about extending and scaling a deployment, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

4.5 Setup Task: Preinstallation Tasks

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

Do the following:

1. Create databases for Oracle BI Applications components, as described in Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".

2. Install and configure database connectivity software, as described in Section 4.5.2, "Install and Configure Database Connectivity Software".

3. Perform prerequisite tasks required for Informatica PowerCenter, as described in Section 4.5.3, "Perform Prerequisites for Informatica PowerCenter Installation".

4.5.1 Create Databases for Oracle BI Applications and Informatica PowerCenter Components

Before you install Oracle BI Applications, the Data Warehouse Administration Console (DAC), and Informatica PowerCenter, use your target database tool to create database instances to hold the following:

• DAC Repository

• Informatica Domain

• Informatica Repository

• Oracle Business Analytics Warehouse

Note the following points:

• The transactional (OLTP) database user that is registered in DAC should be the database table owner. Alternatively, at a minimum, the user registered in DAC must have read privileges on the transactional database for all tables and aliases.

  For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases.

  Note:

  Delete triggers are only used with Siebel CRM databases.

• For efficient ETL, DAC and Informatica components utilize multiple connections to the Oracle Business Analytics Warehouse. The Oracle Business Analytics Warehouse database must allow for a minimum of 100 connections to be made by DAC and Informatica. In addition, ensure that these connections are not allowed to time out. Consult with your network administrator and DBA for information on how to ensure these requirements.

• Make sure that the Oracle Business Analytics Warehouse instance is granted the SSE_ROLE. For more information, see Section 4.5.1.1, "How to Create the SSE Role."

• You can store the DAC Repository and the Oracle Business Analytics Warehouse in separate databases or in the same database.

• The DAC and Informatica repositories are not supported on all database platforms. For information about which database platforms are supported, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

• For database requirements for the Informatica PowerCenter components, see the topic titled, "Verifying Prerequisites," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.

• You must create the Informatica PowerCenter domain configuration database before you run the PowerCenter installer. For more information see the topic titled, "Creating the Domain Configuration Database," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.

4.5.1.1 How to Create the SSE Role

Follow this procedure to create the SSE role for the Oracle Business Analytics Warehouse database.

Note: The transactional (OLTP) database user that is registered in DAC should be the database table owner. Alternatively, at a minimum, the user registered in DAC must have read privileges on the transactional database for all tables and aliases.

For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases.

Note:

Delete triggers are only used with Siebel CRM databases.

To create the SSE role

1. Create a database role named SSE_ROLE (SSEROLE for DB2/390 databases). Assign this role to the database user that you are using for the target database. For example, in an Oracle database, you might use the following commands to create the role, grant DBA privileges to the new role, and assign the SSE_ROLE role to a database user named OLAP:

   CREATE ROLE SSE_ROLE;
   GRANT DBA TO SSE_ROLE;
   GRANT SSE_ROLE TO olap;
   

   For instructions on creating roles, refer to the documentation provided with your database.

   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 from the data warehouse database, the SSE_ROLE must have the following additional privileges:

  • SELECT

  • INSERT

  • 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 the alternative SSE role name SSEROLE if required. To specify a different role name, modify the 'set GRANTEE="SSEROLE"' line in the ORACLE_HOME\biapps\dwrep\createwtables.bat file.

4.5.2 Install and Configure Database Connectivity Software

This section describes the database connectivity requirements for Oracle BI Applications. To configure database connectivity, do the following:

1. Review the summary of requirements in Section 4.5.2.1, "Summary of database connectivity requirements".

2. If you are deploying JDE World on DB2 iSeries, you must have Informatica PowerCenter Services installed on Windows, with an ODBC connection configured for this machine.

3. If you are using Oracle databases, make sure that the NLS_LANG variable is configured on the machines where the Oracle client is installed, as described in Section 4.5.2.2, "How to Set the NLS_LANG Environment Variable for Oracle Databases".

4. If you are using DB2 databases, make sure that the DB2CODEPAGE environment variable is configure on the machines where the DB2 client is installed, as described in Section 4.5.2.3, "How to Set the DB2CODEPAGE Environment Variable for DB2 Databases".

4.5.2.1 Summary of database connectivity requirements

Summary of database connectivity requirements:

• You must install and configure the appropriate database connectivity software on the machines that host Informatica PowerCenter Services, DAC Server, and DAC Client.

• The machine that hosts the PowerCenter Integration Service requires connectivity to the Oracle Business Analytics Warehouse (target) database and transactional (source) database(s). For information about installing and configuring native connectivity software for Integration Services, refer to Informatica PowerCenter Administrator Guide. Also see System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information on certified database connectivity software used by Integration Services to connect to the Oracle Business Analytics Warehouse.

• The machine that hosts the PowerCenter Repository Service requires native connectivity to communicate with the Informatica Repository database. For information about installing and configuring the required software, refer to the section "PowerCenter Repository Service" in Informatica PowerCenter Administrator Guide.

  For additional information about database connectivity for Integration Services and Repository Services, refer to PowerCenter Installation and Configuration Guide.

4.5.2.2 How to Set the NLS_LANG Environment Variable for Oracle Databases

Follow this procedure to set the NLS_LANG environment variable for Oracle databases.

Note:

You need to set the NLS_LANG environment variable on each machine that has the Oracle client installed.

To set the NLS_LANG environment variable for Oracle databases

1. Determine the NLS_LANG value.

   1. In the data warehouse database, run the following command:

      SELECT * FROM V$NLS_PARAMETERS

   2. Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].

      For example: American_America.UTF8

2. For Windows:

   1. Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.

   2. In System variables section, click New.

   3. In the Variable Name field, enter NLS_LANG.

   4. In the Variable Value field, enter the NLS_LANG value that was returned in Step 1.

      The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].

      For example: American_America.UTF8.

      Note:

      The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

      Setting NLS_LANG correctly allows proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion might not be performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary

3. For UNIX and Linux, set the variable as shown below:

   setenv NLS_LANG <NLS_LANG>
   

   For example: setenv NLS_LANG American_America.UTF8.

   If your data is 7-bit or 8-bit ASCII and the Integration Service is running on UNIX or Linux, then set NLS_LANG <NLS_LANGUAGE>_<NLS_TERRITORY>.WE8ISO8859P1

   Caution:

   Make sure you set the NLS_LANG variable correctly, as stated in this procedure, or your data will not display correctly.

4.5.2.3 How to Set the DB2CODEPAGE Environment Variable for DB2 Databases

Follow this procedure to set the DB2CODEPAGE environment variable for DB2 databases on machines that have the DB2 client installed.

To set the DB2CODEPAGE environment variable for DB2 databases

1. Determine the DB2CODEPAGE value.

   1. Connect to the Source database, using the following command:

      SELECT CODEPAGE FROM SYSCAT.DATATYPES WHERE TYPENAME = 'VARCHAR'
      

   2. Make a note of the result.

      For example: 1208

2. For Windows:

   1. Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.

   2. In System variables section, click New.

   3. In the Variable Name field, DB2CODEPAGE.

   4. In the Variable Value field, enter the value that was returned in Step 1.

3. For UNIX and Linux, set the variable as shown below:

   setenv DB2CODEPAGE <DB2CODEPAGE value>
   

   For example: setenv 1208.

4.5.3 Perform Prerequisites for Informatica PowerCenter Installation

Before you run the Informatica PowerCenter installer, you must perform the prerequisites that are described in the chapter, "Before You Install," in the PowerCenter Installation and Configuration Guide.

4.6 Setup Task: Installing the Oracle BI Applications files

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

This section explains how to install the Oracle BI Applications files using the Oracle BI Applications Installer.

When you run Oracle BI Applications installer, the Oracle BI Applications files are installed into the ORACLE_HOME in a folder named biapps. For more information about Home locations, see Section 4.2.1, "Summary of Oracle Home Locations Used in this Guide."

Note: For information about uninstalling Oracle BI Applications, see Section A.8, "How to Uninstall Oracle BI Applications".

Note:

To launch the Oracle BI Applications installer in non-English platforms, use this command:

%JAVA_HOME%\bin\java.exe -cp <PATH_TO_7.9.6.4_BI_APPS_INSTALLER>\setup.jar -Dtemp.dir="%TEMP%" -Dis.jvm.home="%JAVA_HOME%" -Dis.jvm.temp="1" -Dis.external.home="<PATH_TO_7.9.6.4_BI_APPS_INSTALLER>" -Xms64m -Xmx128m run 

To install Oracle BI Applications on Windows

1. 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 the following screens in console installation mode. Instead, you enter input as plain text in the terminal window when prompted.

2. Click Next to display the 'Specify Installation Location' screen.

3. Use the 'Specify Installation Location' screen to specify the details of the Oracle BI EE installation on the local drive. Note: Oracle BI EE must be installed on a local drive; it cannot be a Windows mapped drive that maps to a separate machine.

   
   

   At the 'Enter the BI Oracle Home Location' field, specify the Oracle BI EE Infrastructure home location (or ORACLE_HOME) on the local drive. For example, C:\ORACLEBIEE11G\Oracle_BI1.

   At the 'Enter the BI Instance Location' field, specify the Oracle BI EE instance home location (or ORACLE_INSTANCE) on the local drive. For example, C:\ORACLEBIEE11G\instances\instance1.

   At the 'Enter the Domain Home Location' field, specify the BI domain home location (or DOMAIN_HOME) on the local drive. For example, C:\ORACLEBIEE11G\user_projects\domains\bifoundation_domain.

   For more information about Home locations, see Section 4.2.1, "Summary of Oracle Home Locations Used in this Guide."

4. Click Next to display the 'WebLogic Administration Server Details' screen.

5. Use the 'WebLogic Administration Server Details' screen to specify the details of the WebLogic Administration Server.

   
   

   At the Enter the port number field, specify the WebLogic Server port number. For example, the default port number is 7001.

   At the Enter the user name field, specify the WebLogic administrator account name. For example, the default account name is weblogic.

   At the Enter the password field, specify the WebLogic administrator account password. This password is user-specified when you run the Oracle BI EE installer.

6. Click Next to display the Select the 'Oracle Business Intelligence Applications...' screen.

7. Use the 'Oracle Business Intelligence Applications...' screen to select the Oracle BI Applications that you want to install.

   
   

   To determine the applications you should install for the Oracle BI Applications licenses you have purchased, see the Oracle Business Intelligence Applications Licensing and Packaging Guide. This guide is part of the Oracle Business Intelligence Media Pack.

   Note: Once you have installed applications, you can deploy them for one or more source systems selectively when you create the DAC Repository in a later step. For more information, see Section 4.9.3, "Importing Metadata into the DAC Repository."

8. Click Next to display the summary screen.

9. At the summary screen, review the summary information, and click Next to start the installation.

   
   

   The installer installs the Oracle BI Applications folders and files in the Oracle Business Intelligence infrastructure installation.

   Note:

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

10. Click Finish.

    Tip:

    Look in the ORACLE_HOME\biapps\version_apps.txt file to verify that you have installed the correct version of Oracle BI Applications.

When the installation is complete, you will see the following folders and files:

Table 4-3 Verifying installation files

File Location	File Name
ORACLE_HOME\biapps\repository	OracleBIAnalyticsApps.rpd. You must copy the OracleBIAnalyticsApps.rpd file to the \OracleBIServerComponent\ folder on an Oracle BI EE machine. For more information, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog.". Note: The EnterprisebusinessAnalytics.rpd file is the master file containing metadata for all applications. The OracleBIAnalyticsApps.rpd file contains only metadata for applications that you selecting during installation (on the 'Select the Oracle Business Intelligence Applications 7.9.6.4 you would like to install' screen).
ORACLE_HOME\biapps\catalog	The Presentation Catalog is provided as a zipped file named EnterpriseBusinessAnalytics.zip, which you must unzip before it can be used. For more information about unzipping the Presentation Catalog, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog.".
ORACLE_HOME\biapps\upgrade	RPD and MAP files. Repository files from previous versions of Oracle BI Application and Siebel Analytics Application releases. These repositories are used to upgrade from previous Oracle BI Applications versions.
ORACLE_HOME\biapps\dwrep	Sub-folders. Data Warehouse repository files and scripts.

Notes

• For information on configuring the Oracle BI Repository, see the Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.

• Refer to the Oracle Business Intelligence Enterprise Edition documentation for more information on working with the Oracle BI Repository and Presentation Catalog.

4.7 Setup Task: Installing and Setting Up Informatica PowerCenter

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

To install and setup Informatica PowerCenter, do the following:

1. Install Informatica PowerCenter Client, as described in Section 4.7.1, "Installing Informatica PowerCenter Client Tools (Windows)".

2. Install Informatica PowerCenter Services, as described in Section 4.7.2, "Installing Informatica PowerCenter Services".

3. Create an Informatica Repository Service, as described in Section 4.7.3, "Creating the Informatica Repository Service".

4. Create an Informatica Integration Service, as described in Section 4.7.4, "Creating the Informatica Integration Service".

5. Install the pre-configured Informatica Repository, as described in Section 4.7.5, "Restoring the Pre-built Informatica Repository".

6. Configure the BI Applications Repository in Informatica Client Tools, as described in Section 4.7.6, "Configuring Informatica PowerCenter Components".

Notes

• For information about additional hot fixes or emergency bug fixes that might be required to support Informatica PowerCenter V9.0.1 HF2, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

• The Informatica PowerCenter installation files are available on Oracle Software Delivery Cloud in the BI Media pack.

• If you are deploying Oracle BI Applications with JD Edwards EnterpriseOne or JD Edwards World source systems, select the appropriate operating system on which to install Informatica PowerCenter as follows:

  • If you are a JDE World customer, or a JDE E1 customer on DB2 for IBMi that upgraded from JDE World and never changed the database structure, then you must run Informatica on a Windows OS platform and connect to the DB2 on IBMi source database with ODBC.

  • If you are an E1 customer on DB2 for IBMi that created the database with the E1 (constructed with SQL), then you can deploy Informatica on any LUW OS platform and use either ODBC (Windows) or DB2 Connect (Unix/Aix/Linux) to connect to the DB2 on IBMi source database.

• In this guide, INFA_HOME refers to the user-specified Informatica installation folder on the machine that hosts Informatica PowerCenter Services. For example, if you have installed Informatica PowerCenter Services into a folder named \informatica\9.0.1\, then INFA_HOME is \informatica\9.0.1\. For more information about Home locations, see Section 4.2.1, "Summary of Oracle Home Locations Used in this Guide."

• You need to determine the machines that will host Informatica PowerCenter Client Tools and Informatica PowerCenter Services. The PowerCenter Services can be installed on UNIX or on Windows. The PowerCenter Client Tools must be installed on Windows.

• You must co-locate the DAC Client with the Informatica PowerCenter Client Tools.

• You must co-locate the DAC Server with Informatica PowerCenter Services.

• DAC produces parameter files that are used by Informatica. If an execution plan fails in DAC and you want to debug the workflow by running it directly from Informatica, then the parameter file produced by DAC should be visible to Informatica. This is one reason for the requirement to co-locate the DAC and Informatica components as stated above.

• You must meet all requirements and complete preinstallation tasks as described in Section 4.3, "Mandatory Requirements" and in the chapter 'Before You Install' of the PowerCenter Installation and Configuration Guide.

• When you install PowerCenter Services and Client Tools version 9.0.1 and Hotfix 2, use the Informatica PowerCenter installation documentation in conjunction with the steps provided in the following sections. This guide summarizes the steps and assumes a single-machine installation for the PowerCenter Services components.

  Note:

  If you have licensed Informatica PowerCenter separately from Oracle BI Applications, skip this section and go to section Section 4.7.5, "Restoring the Pre-built Informatica Repository."

  Make sure that you have installed the required version of Informatica PowerCenter (for more information, see Section 4.3.2, "Informatica PowerCenter Requirements").

• If you are using a Windows Vista operating system, note the following:

  The DAC Client uses the Informatica pmrep and pmcmd command line programs when communicating with Informatica PowerCenter. The installation of PowerCenter Client Tools provides the pmrep executable file. The installation of PowerCenter Services provides the pmcmd executable file. The DAC installer performs post-install configuration tasks related to the pmrep and pmcmd executable files. Therefore, you must run the run the PowerCenter Services installer on the Windows Vista machine where you will install the DAC Client. This installation of Informatica PowerCenter Services will be a non-functional installation because Informatica does not support PowerCenter Services on Windows Vista.

  When installing PowerCenter Services on Windows Vista, you might receive an error message similar to the following. You can ignore any such error messages.

  Use the error below and catalina.out and node.log in the server/tomcat/logs folder on the current machine to get more information. EXITCODE: S" Select Retry to continue the installation.

4.7.1 Installing Informatica PowerCenter Client Tools (Windows)

This section explains how to install Informatica PowerCenter Client Tools 9.0.1 Hotfix 2 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 9.0.1, refer to the PowerCenter Installation and Configuration Guide, and related documentation.

To install Informatica PowerCenter Client Tools on Windows

1. Extract the 901HF2_Client_Installer_win32-x86.zip file to a suitable location, then use the install.bat or setup.exe program to start the installation.

   The Informatica PowerCenter installation files are available on Oracle Software Delivery Cloud in the BI Media pack.

2. Follow the instructions on the Informatica PowerCenter Client 9.0.1 HF2 installation wizard.

   Note: When you are prompted to select either a new installation or an upgrade, select the option for a new installation. For information about upgrading Oracle BI Applications, see Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users. If you are prompted to select either the Developer option or the Client option, then select the Client option.

   When the Informatica installation is complete, the installation details are stored in the file <INFA_HOME>\Informatica_9.0.1_Client_HotFix2.log.

4.7.2 Installing Informatica PowerCenter Services

This section explains how to install Informatica PowerCenter Services 9.0.1 and Hotfix 2 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 9.0.1, refer to the PowerCenter Installation and Configuration Guide and related documentation.

Notes

• Before you start, you must create a database account for the Informatica Domain (for example, infadomain/infadomain). During the Informatica PowerCenter Services installation, you specify the connection details for this database account.

• If you have purchased the 'Informatica OEM PowerCenter ETL Server and PowerConnect Adapters' license from Oracle, then use the Oracle_All_OS_Prod.key license key file.

• On Windows, do not install Informatica in a folder that has a space in the folder name. For example, do not install in D:\Program Files\INFA. If you install into a folder with a space in the name, then you will cause errors in DAC.

• When the Informatica installation is complete, the installation details are stored in the file <INFA_HOME>\Informatica_9.0.1_Server_HotFix2.log.

To install Informatica PowerCenter Services 9.0.1 and Hotfix 2

1. Extract the appropriate ZIP or JAR file for the platform that you want to use, then start the installer using an appropriate installer file.

   For example, a setup.exe file on Windows, or an install.sh file at a shell command line on UNIX or Linux.

   The Informatica PowerCenter installation files are available on Oracle Software Delivery Cloud in the BI Media pack.

   When you are prompted to select either a new installation or an upgrade, select the option for a new installation. For information about upgrading Oracle BI Applications, see Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users.

2. Follow the instructions on the Informatica PowerCenter Services installation wizard.

   When you get to the Domain Creation pages, do the following:

   1. At the Domain creation page, select the Create a domain radio button, then click Next.

      
      

   2. At the next page, specify the connection details of the Informatica Domain database (for example, Oracle, INFA91/<password>, dbhost:1521, BI11GR2). For more information about required database accounts, see Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".

      
      

   3. At the Configuration database page, specify the details of the Domain that you want to create. Informatica creates default values for you based on the host name.

      
      

3. Complete to installation wizard until you get to the Post Installation Summary screen. Note down the link for the Informatica Administrator Home Page, then click Done.

   
   

4.7.3 Creating the Informatica Repository Service

Follow this procedure to create the Informatica Repository Service.

For detailed information about creating a Repository Service, refer to the Informatica documentation.

To create the Informatica Repository Service

Note:

Before you log into Informatica Administrator, make sure that the Informatica service is running. To start the Informatica Service, use the Start Informatica Services program.

On Windows, you can also use the Windows Services dialog to start the 'Informatica 9.0.1' service. On UNIX, use the infaservice [startup | shutdown] command.

1. Open Informatica Administrator.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. In the Domain Navigator, select the root Domain, then click Actions, then New, then PowerCenter Repository Service, to display the New PowerCenter Repository Service wizard.

3. Use the 'Specify the properties for this new PowerCenter Repository Service' dialog to specify a Name (for example, BIA_RS), Location, License, and Node, then click Next.

4. Use the 'Specify the database properties for this new PowerCenter Repository Service' dialog to specify a Database Type, Username, Password, Connection String, and Code Page.

   Notes:

   • The Username and Password are for the database account that you created for the Informatica repository (see Section Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components.").

   • If you specify the wrong code page, you cannot correct this later. If the code page is incorrect, then you must create a new Repository Service.

5. In the 'Specify the creation options for the new PowerCenter Repository Service' area, select the No content exists under specified connection string. Create new content. radio button.

   For detailed information about creating a Repository Service, refer to the Informatica documentation.

6. Click Finish.

In the example screen shot below, Domain_myhost contains a PowerCenter Repository Service named BIA_RS.

4.7.4 Creating the Informatica Integration Service

Follow this procedure to create the Informatica Integration Service.

For detailed information about creating an Integration Service, refer to the Informatica documentation.

Before you start: Change the Repository Properties\Operating Mode to 'Normal' for the Repository Service that you created in the previous step.

To create the Informatica Integration Service

1. Open Informatica Administrator.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. In the Domain Navigator, select the root Domain, then click Actions, the New, then PowerCenter Integration Service, to display the New PowerCenter Integration Service wizard.

3. Use the 'Specify the properties for this new PowerCenter Integration Service' dialog to specify a Name (for example, BIA_IS), Location, License, and Node, then click Next.

4. Use the 'Specify the PowerCenter Integration Service and its login credentials' dialog to select the Repository Service that you created in the previous step, and specify the repository username and password (for example, Administrator\Administrator).

5. In the 'Select the data movement mode' area, select the appropriate Code Page setting from the Data Movement Mode drop down list.

6. Click Finish.

   Tip:

   The Integration Service will only start if the Repository Service is operating with the Operating Mode set to 'Normal'.

In the example screen shot below, Domain_myhost contains a PowerCenter Integration Service named BIA_IS.

4.7.5 Restoring the Pre-built Informatica Repository

An Informatica Repository file called Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep for Teradata deployments) is installed the ORACLE_HOME\biapps\dwrep\Informatica\Repository folder during the Oracle BI Applications installation. You must load the contents of this REP file into the Informatica repository.

Notes

• If you are re-loading the Informatica repository, then you must first delete the contents of the repository. The Restore option is only available if the repository is empty.

• You can only delete and restore contents when the Repository Service is in the 'Exclusive' Operating Mode. After the repository has been loaded, you must change the Operating Mode for the Repository Service from 'Exclusive' to 'Normal'.

• If the Integration Service fails while the Operating Mode for the Repository Service is set to 'Exclusive', then you must restart the Integration Service with the Operating Mode for the Repository Service set to 'Normal'.

Follow the appropriate task from the following topics:

• For English environments, follow the steps in Section 4.7.5.1, "Restoring the Pre-built Informatica Repository for Environments in English"

• For non-English environments, follow the steps in Section 4.7.5.2, "Restoring the Pre-built Informatica Repository on a Non-English Operating System"

4.7.5.1 Restoring the Pre-built Informatica Repository for Environments in English

You use the Restore option in Informatica Administrator to load the pre-built Oracle_BI_DW_Base repository (or the Oracle_BI_DW_Teradata repository in a Teradata environment).

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

1. Copy the file Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep if your Oracle Business Analytics Warehouse is on Teradata) from:

   ORACLE_HOME\biapps\dwrep\Informatica\Repository

   To:

   INFA_HOME\server\infa_shared\Backup

2. In Informatica Administrator, select the Repository Service that was created in the procedure in Section 4.7.3, "Creating the Informatica Repository Service."

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

3. In the General Properties area of the Properties tab, make sure the OperatingMode value is Exclusive.

   To change the OperatingMode value, click Edit, and then select a new value from the drop-down list. Click OK to exit edit mode.

4. In the Actions menu at the top right-hand side, choose Actions, then Repository Contents, then Delete Contents.

5. At the Delete Contents for <repository name> dialog box, enter the repository username and password (for example, Administrator\Administrator), then click OK.

6. In the Actions menu at the top right-hand side, choose Actions, then Repository Contents, then Restore.

7. At the Restore Contents for <repository name> dialog, enter the repository username and password (for example, Administrator\Administrator), then click OK.

8. Choose Actions > Restore Contents.

9. At the Restore Contents dialog, select Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep for Teradata installations) from the Select Backup File drop-down list.

10. Select the Restore as New check box.

11. Click OK to start the restore process.

12. When the restore process is complete, click Close (or click Save first to save the logging information).

    When the restore is complete (the process typically takes approximately ten to twenty minutes), you will see a 'Success' message.

13. When a repository is restored, the repository becomes a standalone repository. After restoring the repository, you need to promote it to a global repository.

    For instructions, see the topic titled, "Promoting a Local Repository to a Global Repository," in "Chapter 8: Managing the Repository," in Informatica PowerCenter Administrator Guide.

14. Change the OperatingMode value to Normal.

    1. Go to the Properties tab.

    2. In the General Properties area, click Edit.

    3. Click the OperatingMode drop-down list, and select Normal.

    Note: if you do not set the Operating Mode to Normal, the Integration Service will not restart.

15. If prompted, enter the repository username and password.

Note:

The pre-built Oracle_BI_DW_Base and Oracle_BI_DW_Teradata repository files are versioned. For more information about the versioned repositories, see Appendix F, "About the Versioned Informatica Repository."

4.7.5.2 Restoring the Pre-built Informatica Repository on a Non-English Operating System

If Informatica PowerCenter Services is installed on a non-English version of the operating system, you must use the command line to restore the pre-built Informatica Repository provided with Oracle BI Applications.

To restore the pre-built Informatica Repository on a non-English operating system

1. Open a Command window.

2. Enter the following command to connect to the repository:

   Pmrep connect -r <RepositoryName> -d <Domain> 
   

3. Enter the following command to restore the repository:

   PmRep restore -u <domain_user_name> -p <domain_user_password> -i <input_file_name> -n
   

   where the input_file_name is the name of the pre-built repository file.

4.7.6 Configuring Informatica PowerCenter Components

This section explains how to configure Informatica PowerCenter Services for use with Oracle BI Applications. It contains the following topics:

• Section 4.7.6.1, "Setting up the Informatica Domain and Repository in Informatica PowerCenter Client Tools"

• Section 4.7.6.2, "Copying Source Files and Lookup Files"

• Section 4.7.6.3, "Setting PowerCenter Integration Services Relaxed Code Page Validation"

• Section 4.7.6.4, "Setting PowerCenter Integration Services Custom Properties"

• Section 4.7.6.5, "Creating the Repository Administrator User in the Native Security Domain"

4.7.6.1 Setting up the Informatica Domain and Repository in Informatica PowerCenter Client Tools

When you first log into any of the Informatica PowerCenter Client tools (for example, Designer, Workflow Manager), you must specify the Informatica Domain that you created when you installed Informatica PowerCenter Server. For example, you might want to log into Informatica PowerCenter Designer to check that the Oracle BI Applications Repository has been successfully loaded.

The following tasks describe how to use Informatica PowerCenter Designer to specify an Informatica Domain and Informatica Repository, but you can use any of the Informatica PowerCenter Client tools.

To set up the Domain:

1. Start Informatica PowerCenter Designer.

2. Choose Repository, then Configure Domain, to display the Configure Domains dialog.

   
   

3. Click the Add a new Domain icon to display the Add Domain dialog.

   
   

4. Use the Add Domain dialog to specify the following:

   Table 4-4 Add Domain dialog fields

   Field Name	What to enter
   Domain Name	Specify the name of the domain that you created when you installed Informatica PowerCenter Server, as described in Section 4.7.2, "Installing Informatica PowerCenter Services" ).
   Gateway Host	Specify the hostname or IP address of the Gateway Host as specified during the installation of PowerCenter Services (for example, machineA).
   Gateway Port	Specify 6005 (unless you have changed the default Gateway Host port).

   
   

5. Click OK.

   When you specify a valid set of Domain details, the new domain is displayed in the Domains list, and any available Repositories are displayed in the Repositories list.

6. In the Repositories list, select the check box for the Repository that you want to use (for example, you might select the Informatica Repository that you created in Section 4.7.3, "Creating the Informatica Repository Service").

   
   

To set up the Repository:

1. Start Informatica PowerCenter Designer.

2. Choose Repository, then Add to display the Add Repository dialog.

   
   

3. In the Repository field, enter the name of the Informatica Repository that you created in Section 4.7.3, "Creating the Informatica Repository Service".

   For example, you might have named the repository BIA_RS.

   In the Username field, specify Administrator.

4. Click OK to save the details and return to the Start Page.

5. In the Repositories tree, double click on the Repository that you specified in the previous step (for example, BIA_RS).

   
   

6. In the Connection Settings part of the Connect to Repository dialog, make sure that the Domain field displays the domain that you specified in the previous task.

7. Specify a user name and password (for example, Administrator\Administrator), then click Connect.

   
   

8. You can now expand the repository in the Repositories list to display the contents.

   
   

4.7.6.2 Copying Source Files and Lookup Files

You need to copy source files and lookup files from the Oracle BI Applications installation folder to the Informatica folder on the Informatica PowerCenter Services machine.

Note: The following instructions assume the default Informatica folder locations for source and lookup files.

• The default Informatica folder for source files is INFA_HOME\server\infa_shared\SrcFiles. You can confirm the source file folder for your environment as follows: In PowerCenter Administrator, select the Integration Service, navigate to the General Properties area of the Processes tab, and verify the value of $PMSourceFileDir.

• The default Informatica folder for lookup files is INFA_HOME\server\infa_shared\LkpFiles.You can confirm the source file folder for your environment as follows: In PowerCenter Administrator, select the Integration Service, navigate to the General Properties area of the Processes tab, and verify the value of $PMLookupFileDir.

To copy source files and lookup files to Informatica PowerCenter Services on Windows:

1. Copy the source files as follows:

   From:

   ORACLE_HOME\biapps\dwrep\Informatica\SrcFiles on the machine where the Oracle BI Applications installer was run.

   To:

   INFA_HOME\server\infa_shared\SrcFiles.

2. Copy the lookup files:

   From:

   ORACLE_HOME\biapps\dwrep\Informatica\LkpFiles on the machine where the Oracle BI Applications installer was run.

   To:

   INFA_HOME\server\infa_shared\LkpFiles.

Note:

If Informatica PowerCenter Services is installed on a UNIX or Linux machine, convert the target paths above from Windows to UNIX/Linux. For example, covert the Windows target path:

From:

INFA_HOME\server\infa_shared\SrcFiles

To:

INFA_HOME/server/infa_shared/SrcFiles

4.7.6.3 Setting PowerCenter Integration Services Relaxed Code Page Validation

PowerCenter Integration Services for Oracle BI Applications must be configured for relaxed code page validation.

To configure Informatica PowerCenter Integration Services for relaxed code page validation

1. Log into Informatica Administrator.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. Select the Integration Service.

3. Select the Properties tab.

4. In the Configuration Properties area, click Edit.

5. Deselect the 'ValidateDataCodePages' check box.

4.7.6.4 Setting PowerCenter Integration Services Custom Properties

Follow this procedure to set PowerCenter Integration Services custom properties.

To set Informatica PowerCenter Integration Services Custom Properties

1. In Informatica Administrator, select the Integration Service.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. Click the Properties tab.

3. In the Custom Properties area, click Edit.

4. Use the New option to display the New Custom Property dialog, and add the following Custom Properties:

   • Table 4-5 Custom Properties for Informatica PowerCenter Integration Services

     Custom Properties Name	Custom Properties Value	Notes
     overrideMpltVarWithMapVar	Yes	Enables Informatica to evaluate parameters within mapplets.
     DisableDB2BulkMode	Yes	Add this custom property and set value to Yes if your Oracle Business Analytics Warehouse is on a DB2/390 or a DB2 UDB database.
     OraDateToTimestamp	Yes	You must create this custom property if you are using a non-Oracle database for Oracle Business Analytics Warehouse. If you are using an Oracle database for Oracle Business Analytics Warehouse, then this custom property is not required.

     
     

5. Click OK to save the details.

6. Make sure that the Integration Service and Repository Service that you created during the Informatica PowerCenter installation are running.

4.7.6.5 Creating the Repository Administrator User in the Native Security Domain

For DAC to be able to access Informatica and perform tasks in pmcmd and pmrep command line programs, DAC must log in to Informatica as an Informatica Repository Administrator user. This user must be configured in the native security domain.

You need to create such a Repository Administrator user, or , if your security policies allow, you can use the default Administrator user (whose privileges include Repository administration) for DAC connectivity to Informatica.

For more information on creating users and Informatica security domains, see section "Users and Groups" in Informatica PowerCenter Administrator Guide.

To use the default Administrator user in the native security domain

1. Log in to the PowerCenter Administration Console as Administrator.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. Display the Security tab by clicking the Configure Security icon in the top, right corner of the Informatica Administrator work area.

3. In the Users area, expand the Native folder and select Administrator.

   Note: Do not select an existing user from an LDAP domain.

4. Display the Privileges tab, and use the make sure that the correct Domain and Repository Service are selected.

   
   

5. Click OK.

To create a new Repository Administrator defined in the native security domain

1. Log in to the PowerCenter Administration Console as Administrator.

   For more information about logging into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. Display the Security tab by clicking the Configure Security icon in the top, right corner of the Informatica Administrator work area.

3. In the Users area, click Actions, then Create User to display the Create User dialog.

   Note: Do not create this user in an LDAP domain.

4. Use the Create User dialog to specify the user details, then click OK.

5. In the Users area, select the new user, display the Privileges tab, then click Edit to display the Edit Roles and Privileges dialog.

6. Use the Roles tab and Privileges tab to select the appropriate Domain and Repository Service.

   
   

7. Click OK.

4.8 Setup Task: Installing and Setting Up the DAC Platform

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

This section explains how to install Oracle Business Intelligence Data Warehouse Administration Console Platform (DAC) , and how to create the required database connections. For information about the version of the DAC platform supported with Oracle BI Applications, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

Important Information about Deploying DAC

When you deploy DAC, note the following:

• The DAC Client only runs on Windows. The DAC Server runs on Windows, Linux, and UNIX.

• The DAC installer installs DAC Client and DAC Server on Windows only.

• Oracle does not provides an installer for DAC on Linux or UNIX. However, you can deploy DAC Server on Linux or UNIX by manually copying files from a Windows machine to a Linux or UNIX machine. For instructions on setting up DAC Server on UNIX, see Section 4.8.3, "Deploying the DAC Server on Linux or UNIX".

To install and setup the DAC Platform, do the following:

1. Install DAC, as described in Section 4.8.1, "Installing DAC on Windows Using the DAC Installer".

2. If you need to deploy DAC Server on a separate Windows machine, then follow the setup instructions in Section 4.8.2, "Deploying DAC Server on a separate Windows machine".

3. If you need to deploy DAC Server on Linux or UNIX, then follow the setup instructions in Section 4.8.3, "Deploying the DAC Server on Linux or UNIX".

4. Install JDBC drivers, as described in Section 4.8.4, "Installing a JDBC Driver for DAC Database Connectivity".

5. (Siebel specific) Create ODBC connections for Siebel source system, as described in Section 4.8.5, "(Siebel specific) Creating an ODBC Database Connection for a Siebel Source System".

6. Set up pmcmd, as described in Section 4.8.6, "Setting Up pmcmd to Enable Communication Between Informatica PowerCenter and DAC"

7. Install DAC Metadata files, as described in Section 4.8.7, "Installing DAC Metadata Files".

4.8.1 Installing DAC on Windows Using the DAC Installer

This section explains how to use the DAC installer to install DAC Client and DAC Server on Windows for an Oracle Business Intelligence Applications environment.

Notes

• You must install Informatica PowerCenter Client before you install DAC.

• The DAC Client must be installed on the machine where Informatica PowerCenter Client is installed.

• The DAC Server must be installed on the machine where Informatica PowerCenter Services is installed.

• The required version of the JDK is installed by the DAC installer.

• Before you start the DAC installer, make sure that the Windows compatibility option is set for the Windows version that you are using. For example, to install DAC on Windows 2008, you might set the compatibility to Windows XP (Service Pack 3). To set the Windows compatibility option, right click on the setup.exe file to display the Properties dialog, and use the Compatibility tab.

• After installation, the \dac folder will contain files for both DAC Client and DAC Server.

To install DAC using the DAC installer

1. Start the DAC installer by double-clicking the dac11g_install.exe file.

2. Follow the instructions on the installation wizard, as described in the following table:

Page	Your Action	Notes
Introduction	Click Next to proceed.	None.
Informatica PowerCenter	Click Choose to specify the location of the PowerCenterClient folder on the local machine. For example, C:\Infa\9.0.1\clients\PowerCenterClient.	None.
Informatica Domains File	Click Choose to specify the location of the domains.infa file on the local machine. For example, C:\Infa\9.0.1\clients\PowerCenterClient\domains.infa.	None.
Choose Install Folder	To accept the default location, do nothing. To specify a non-default location on the local machine in which to install DAC, do one of the following: - Enter the absolute path for the location. - Use the Choose button to select an existing location. For example, C:\DAC11G. The \dac folder that is created in this location is known as the DAC_HOME. For example, C:\DAC11G\dac.	The installation folder path can contain alphanumeric, underscore (_), hyphen (-) or dot (.) characters and must begin with an alphanumeric character.
Choose Shortcut Folder	Use the options to specify where you would like to create product icons for DAC Client and related programs.	None.
Pre-installation Summary	Click Install to begin the installation.	None.
Installing...	None.	None.
Install Completed	Click Done.	In the folder that you specified in Choose Install Folder dialog, the following sub-folders are created: - _Oracle Business Intelligence Data Warehouse Administration Console 11g_installation - dac - jre

DAC Configuration Handled by the DAC Installer

The DAC installer handles the following configuration:

• In the config.bat file, the DAC installer configures the JAVA_HOME and DAC_HOME variables.

• In the dac_env.cmd file, the DAC installer creates an environment variable named INFA_DOMAINS_FILE and sets the value to the folder path of the domans.infa file. The dac_env.sh is not configured automatically.

• In the dac_env.cmd file, the DAC installer adds the folder path to Informatica PowerCenter binaries to the PATH environment variable. The dac_env.sh is not configured automatically.

Note:

If you reinstall or upgrade Informatica PowerCenter without reinstalling DAC, you need to manually set these environment variables.

4.8.2 Deploying DAC Server on a separate Windows machine

Oracle recommends that you deploy DAC Server on the machine that hosts Informatica PowerCenter Services. The DAC installer installs DAC Client and DAC Server onto a Windows machine. This section explains how to deploy DAC Server onto a different machine to the DAC installation machine.

To deploy DAC Server on a separate Windows machine:

1. Run the DAC installer on the target Windows machine.

   Note: You specify the target machine details in step Section 4.9.2, "Configure DAC Server".

4.8.3 Deploying the DAC Server on Linux or UNIX

Oracle does not provide an installer for DAC on Linux or UNIX. To deploy DAC Server on Linux or UNIX, you install DAC on a Windows machine, then copy the DAC files from the Windows machine to a Linux or UNIX machine, as described in this section.

When deploying DAC Server, note the following prerequisites:

• On the UNIX machine, make sure the following are installed:

  • JDK version 1.6.0 or higher

  • Zip and unzip utility

• Make sure that Informatica PowerCenter Services is co-located on the machine that will host the DAC Server. For more information, see Section 4.7, "Setup Task: Installing and Setting Up Informatica PowerCenter."

• Make sure that Informatica has been configured according to the instructions in Section 4.7.6, "Configuring Informatica PowerCenter Components."

To deploy the DAC Server on a UNIX or Linux machine

1. On the Windows machine where DAC is installed, create a temporary folder (for example, a folder named \DAC_UNIX\).

   You will use this temporary folder to create a zip file for the UNIX or Linux deployment.

2. On the machine where the DAC is installed, copy the \dac folder (that is, the folder created by the DAC installer, for example C:\DAC11G\dac) to the temporary folder (for example, \DAC_UNIX\).

3. From the \dac folder in the temporary folder, remove the \export and \icons sub-folders.

   These folders do not need to be copied to the machine hosting the DAC Server.

4. Zip up the temporary folder (for example, zip up the \DAC_UNIX\ folder into DAC_UNIX.zip).

5. Copy the zip file to the target UNIX machine.

   Note: If you use FTP to copy the zip file, use binary mode.

6. On the target UNIX machine, place the zip file in a folder where you want to install the DAC Server.

7. On the target machine, extract the zip file.

   Shell scripts are provided in the DAC_HOME folder (for example, C:\DAC11G\dac). After copying these files to a UNIX machine and before using them, you might need to use a MS-DOS to UNIX conversion tool to convert the script files to UNIX format (that is, remove the carriage return and line feed characters). Alternatively, you can manually remove the carriage return and line feed characters from the script files. For more information, see Section A.6, "About the DAC Server Shell Scripts."

8. Copy the contents of the DAC_HOME/unix_script_bkp folder into the /dac folder.

9. Edit the config.sh file located in the DAC_HOME folder to point to the correct version of the JDK by setting the JAVA_HOME environment variable.

10. Make sure that environment variables are set for DAC Server, as described in Section A.5.2, "How to Set Environment Variables for DAC Server Communication on UNIX."

4.8.4 Installing a JDBC Driver for DAC Database Connectivity

DAC requires JDBC drivers for connectivity with supported databases. You should only use drivers that are shipped with the database or downloaded from database vendor site and known to be certified for the given database version. Third-party JDBC drivers for databases are not supported.

To enable DAC database connectivity, you must install the appropriate JDBC driver in the DAC_HOME\lib folder on the machines where the DAC Client and Server are installed.

Note:

This section applies to Windows, UNIX and Linux. The instructions in this section use the Windows folder path format.

To install JDBC drivers for DAC:

Make sure that the required JDBC driver is available in the DAC_HOME\lib folder (for example, C:\DAC11G\dac\lib), as described below.

• For Oracle Database Version 11G:

  If you are using Oracle Database 11g and you plan to use the Oracle (Thin) connection type in DAC Client, then no additional JDBC driver is required. The DAC installer installs the required ojdbc6.jar file in the DAC_HOME\lib folder.

  If you are using Oracle Database 11g and you plan to use the Oracle (OCI8) connection type in DAC Client, then do the following:

  1. Delete the ojdbc6.jar file from the DAC_HOME\lib folder (for example, C:\DAC11G\dac\lib).

  2. Copy the file ojdbc6.jar from the <ORACLE DB HOME>\ jdbc\lib folder into the DAC_HOME\lib folder.

• For Oracle Database Version 8.x, 9.x and 10.x:

  1. Delete the ojdbc6.jar file from the DAC_HOME\lib folder.

  2. Copy in the required JDBC driver file as follows:

     • If you are using an Oracle database 9.x or 10.x, then copy the file <ORACLE DB HOME>jdbc\lib\ojdbc14.jar into the DAC_HOME\lib folder.

     • If you are using Oracle database 8.x, copy the file <ORACLE DB HOME>jdbc\lib\classes12.zip into the DAC_HOME\lib folder. In addition, you must edit the ORACLELIB setting in the config.bat file (or config.sh file on UNIX and Linux), as follows:

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

• DB2

  If you are using a DB2 database, find the folder where DB2 is installed. In the Java sub-folder, copy the file named db2java.zip and paste it into the DAC_HOME\lib folder.

  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.19.3, "Creating Stored Procedures for DB2-UDB".

• MSSQL

  DAC 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 file sqljdbc.jar and copy it to the DAC_HOME\lib folder.

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

  Download the SQL Server JDBC drivers for SQL Server 2000 from the Microsoft web site. Copy the appropriate .JAR files to the DAC_HOME\lib folder.

  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 folder to the DAC_HOME\lib folder. Depending on the Teradata JDBC version, you might not have some of the above files.

4.8.5 (Siebel specific) Creating an ODBC Database Connection for a Siebel Source System

If you are using a Siebel source system with a DB2 390 transactional database, then you must create an ODBC connection on the DAC Client machine to the transactional database, as described below:

1. On Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the transactional database using an ODBC driver.

4.8.6 Setting Up pmcmd to Enable Communication Between Informatica PowerCenter and DAC

DAC uses the Informatica pmrep and pmcmd command line programs to communicate with Informatica PowerCenter in the following ways:

• DAC Server uses:

  • pmrep to communicate with PowerCenter Repository Services.

  • pmcmd to communicate with PowerCenter Integration Services to run the Informatica workflows.

• DAC Client uses:

  • pmrep to synchronize tasks with Informatica workflows and to keep the DAC task source and target tables information up to date.

Informatica installs the pmrep program file in both the PowerCenter Client and PowerCenter Services bin folders (for example, INFA_HOME\clients\PowerCenterClient\client\bin, and INFA_HOME\server\bin).

Informatica installs the pmcmd program on the PowerCenter Services machine. For example, on a Windows installation of PowerCenter Services, the pmcmd.exe program file is installed in INFA_HOME\server\bin.

To communicate with Informatica, DAC requires that the Windows version of pmcmd (that is, pmcmd.exe) is available. To make pmcmd available, you must manually copy the pmcmd.exe program file to the PowerCenter Client bin folder, as described below.

To copy pmcmd into the PowerCenter Client bin folder:

1. Obtain the pmcmd.exe file by doing one of the following:

   On the PowerCenter client machine, obtain the pmcmd.exe file from the following folder:

   INFA_HOME\clients\PowerCenterClient\CommandLineUtilities\PC\server\bin
   

   Alternatively, if you have access to a Windows version of PowerCenter Services, then the pmcmd.exe file is located in the following folder on the machine where PowerCenter Services is installed:

   INFA_HOME\server\bin
   

2. Copy the pmcmd.exe file to the following folder on the PowerCenter Client/DAC Client machine:

   INFA_HOME\clients\PowerCenterClient\client\bin
   

   Note: PowerCenter Client and DAC Client should be installed on the same Windows machine.

4.8.7 Installing DAC Metadata Files

DAC metadata files are installed by the Oracle BI Applications installer. You need to manually copy these files to the machines hosting the DAC Client and Server.

Table 4-6 describes the location of the file or folder names in the Oracle BI Applications installation that you need to copy to the machine hosting the DAC Client.

Table 4-7 describes the location of the file names in the Oracle BI Applications installation that you need to copy to the machine hosting the DAC Server.

Table 4-6 DAC Metadata Files That Need to Be Copied to DAC Client Machine

File/folder Name	Location of File/folder in Oracle BI Applications Installation	File/folder to Be Copied to Following Location
Export folder	ORACLE_HOME\biapps\dwrep\dac_metadata\dac_client\export	Copy the contents of the export folder to the DAC_HOME\export folder on the machine hosting the DAC Client.
DeleteTriggers.list	ORACLE_HOME\biapps\dwrep\dac_metadata\dac_client	Copy this file to the DAC_HOME\conf folder on the DAC Client machine.

Table 4-7 DAC Metadata Files That Need to Be Copied to DAC Server Machine

File/folder Name	Location of File/folder in Oracle BI Applications Installation	File to Be Copied to Following Location
parameterfileOLTP.txt	ORACLE_HOME\biapps\dwrep\dac_metadata\dac_server	Copy this file to the DAC_HOME\Informatica\parameters\input folder on the DAC Server machine.
parameterfileDW.txt	ORACLE_HOME\biapps\dwrep\dac_metadata\dac_server	Copy this file to the DAC_HOME\Informatica\parameters\input folder on the DAC Server machine.

4.9 Setup Task: Log into DAC for the First Time, Configure DAC Server, and Import Metadata into the DAC Repository

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

When you log into DAC for the first time, you create a set of stored login information known as a Connection. As part of the first-time login process, DAC prompts you to create the DAC Repository schema. Once the schema is created, you can then import metadata into the DAC Repository.

Do the following:

1. Create a DAC Connection and log into DAC, as described in Section 4.9.1, "Creating a DAC Connection and Logging Into DAC".

2. Configure DAC Server, as described in Section 4.9.2, "Configure DAC Server".

3. Import the BI Applications metadata into DAC, as described in Section 4.9.3, "Importing Metadata into the DAC Repository".

4.9.1 Creating a DAC Connection and Logging Into DAC

When you log into DAC for the first time, you must first configure a connection to connect to the DAC Repository. DAC stores this connection information for subsequent logins.

For additional information about logging into DAC, refer to the topic named 'Logging into DAC for the First Time as a DAC User' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

After configuring a connection to the DAC Repository and logging in, the DAC will automatically prompt you to upgrade the repository schema.

About the DAC Repository Database Authentication File

When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.

A user with the Administrator role must distribute the authentication file to any user account that needs to access the specified DAC Repository. For information about managing user accounts, see Section A.2, "About DAC User Account Management."

To create a DAC Connection and login to DAC for the first time:

1. Start the DAC Client by navigating to the DAC_HOME\ folder and double-clicking the startclient.bat file.

   The Login ... dialog appears.

   
   

2. Click Configure.

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

4. Enter the appropriate connection information:

   Field	Required Value
   Name	Enter a unique name for the connection to the DAC Repository.
   Connection type	Select the type of database in which the DAC Repository will be stored.
   Connection String, or Database name, or TNS Name, or Instance	Select the database name or database account name of the DAC 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	Enter the name of the machine where the DAC Repository will reside.
   Database Port	Enter 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.
   Optional URL	Can be used to override the standard URL for this connection.
   Optional Driver	Can be used to override the standard driver for this connection.
   Authentication File	Click in this field to do one of the following: Select an existing authentication file. Create a new authentication file. Proceed to the next step for detailed instructions.

   
   

5. To create a new authentication file, do the following:

   1. Click in the Authentication File field of the Configuring... dialog.

   2. In the Authentication File dialog, select Create authentication file.

   3. Navigate to the folder where you want to save the new authentication file, and click OK.

   4. In the Create Authentication File dialog, enter a unique name for the authentication file, and click OK.

   5. Enter the Table Owner Name and Password for the database where the repository will reside.

   6. In the Configuring... dialog, click Test Connection to confirm the connection works.

   7. Click Generate Random Encryption Key.

   8. Click Apply, and then click Finish.

      Note:

      You must distribute this authentication file to all user accounts that need to access this DAC Repository.

6. In the Login... dialog, do the following:

   
   

   1. Use the Connection drop down list to select Connection that you created in steps 1 to 6.

   2. Use the User name field to specify the database user.

   3. Use the Password field to specify the database password for the specified database user.

   4. In the Authentication Type drop down list, select DAC.

      Note: You must instruct all DAC Client users to select DAC in the Authentication Type drop down list. The FMW authentication type is not supported in Oracle BI Applications V7.9.6.4.

   5. Click Login.

7. When prompted to create a repository, click Yes.

   This process creates DAC repository tables.

   The Unicode check box is available for a repository on SQL Server or DB2 databases. Check the Unicode check box if your deployment requires a Unicode schema to be created.

4.9.2 Configure DAC Server

In DAC Client, you must specify the host details for DAC Server.

To configure DAC Server:

1. In DAC, select Tools, then DAC Server Management, then Repository Configuration to display the Repository Configuration dialog.

   
   

2. Select Standalone from the Mode drop down list.

   Note: Web mode is not supported in Oracle BI Applications V7.9.6.4.

3. Use the Host field to specify the machine that hosts DAC Server.

   Do not change the default number in the Port field.

4. Save the details.

   If DAC Server is running, the DAC Server status icon in the top right hand corner of the DAC main screen changes to Green.

4.9.3 Importing Metadata into the DAC Repository

This section explains how to import Oracle BI Applications metadata into the DAC Repository.

For additional information about importing metadata into DAC, refer to the topic named 'Importing DAC Metadata' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

Note:

The DAC Client can connect to only one DAC Repository at a time.

To import metadata into the DAC Repository

1. In DAC, select Tools, then DAC Repository Management, then Import to display the Import dialog.

   Make sure that the correct DAC_HOME\export\ folder is displayed at the top of the dialog. If necessary, use the Change import/export folder button to select the correct DAC_HOME\export\ folder. If the wrong folder is selected, the Applications list will be empty

2. In the Categories area, select the Logical check box and the System check box.

   The information imported by these options is 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.
   System	Imports all information contained in the DAC Setup view, except passwords for servers and database connections.

   
   

3. In the Applications List table, use the check boxes in the Selected column to specify the source system applications that you need to deploy, as follows:

   1. Select the check box next to Universal.

      To avoid possible ETL errors in other applications (for example, missing tasks), you must import the Universal application.

   2. Select the check box for any other business applications that you want to deploy.

      Note:

      Do not select the Data Warehouse option unless Oracle specifically instructs you to do so. This container is reserved for special purposes.

   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.10, "Setup Task: Creating the Oracle Business Analytics Warehouse Tables").

4. Select the Truncate Repository Tables check box.

   Note:

   If you are importing DAC metadata for the first time, you should select the Truncate Repository Tables check box.
   
   If you are importing DAC metadata into a repository that already has metadata, do the following:

   1. Back up the current repository by exporting it to an empty folder (use the 'Change import/export folder' button to select an empty folder.

   2. Use the 'Change import/export folder' button to select the \dac\export\ folder.

   3. Select the Truncate Repository Tables check box and the Enable Batch Mode check box, and import the new DAC metadata.

   4. Use the 'Change import/export folder' button to select the folder where you backed up data in Step 1.

   5. Deselect the Truncate Repository Tables check box and re-import the data you backed up.

5. Select the Enable Bulk Mode check box.

   Note:

   If you are using an Oracle 9i Release 2 database, you cannot use batch mode because the Oracle 9i JDBC drivers do not handle array inserts or bulk inserts.

6. Click OK to display the Importing tables dialog.

   
   

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

8. 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 DAC_HOME\log\import.log file to diagnose errors.

   Note:

   The import process typically takes between one and five hours, depending on the location of the database that stores the DAC Repository, the speed of the network, and the number of applications selected in the Applications list on the Import dialog.

9. Click OK.

10. 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 DAC_HOME\ folder.

4.10 Setup Task: Creating the Oracle Business Analytics Warehouse Tables

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

You must create tables in the Oracle Business Analytics Warehouse database, as described below.

For additional information about creating metadata tables, refer to the topic named 'Creating, Upgrading or Dropping an Entire Schema' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

Pre-requisites

• Before you start this procedure, you need to create a database for the Oracle Business Analytics Warehouse. For more information, see Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components."

• Before you perform the procedures described in this section, 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.5.1.1, "How to Create the SSE Role").

To create Oracle Business Analytics Warehouse tables:

1. In DAC, select Tools, then ETL Management, then Configure to display the Sources dialog.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

2. In the Sources dialog, select the following:

   - Use the Target data warehouse database platform drop down list to specify the database platform of the Business Analytics Warehouse.

   - Use the Source transactional database platform drop down list to specify the database platform of the transactional system database.

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

   
   

4. On the Data Warehouse Configuration Wizard > Configuration page, select the Generate create statements for Data Warehouse Tables check box.

5. Click Next to display the Data Warehouse/Data Warehouse SQL page, which displays fields that are appropriate to the data warehouse type specified.

   
   

6. Use the Data Warehouse/Data Warehouse SQL page to specify the details for the Oracle Business Analytics Warehouse database, using the fields as described below:

   Note: The information that you need to enter is dependent on the type of target database that you are using.

   Field	Description
   Container	The name of the source business applications for which you want to create the data warehouse tables. Note: You must leave this field blank, unless told to specify a value by Oracle Support. If you leave the Container field blank, 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.9.3, "Importing Metadata into the DAC Repository"). Any copies of those source system applications. Any additions made to those source system applications. Any new source system applications that you have custom built. If there are tables that are common to these containers, 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 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 subset 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 User's Guide.
   Is Unicode	Specifies whether the database platform of the Business Analytics Warehouse is Unicode. The database must be set to support the creation of a Unicode schema. For more information, see Section 4.3.3, "Code Page and Data Movement Requirements."
   Execute	Use this option if you want DAC to execute the SQL script automatically after it is generated. If you leave this check box empty, you must manually execute the SQL scripts that DAC generates to create the Oracle Business Analytics Warehouse tables.
   Physical Data Source	Select the required target data source.
   Change default parameter file	Use this button to display the Choose default parameter file dialog, which enables you to specify a different schema template.

   
   

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

  • DAC_HOME\log\config\generate_ctl.log - This is a log of the schema definition process, including details of any conflicts between containers.

  • DAC_HOME\log\config\createwtables.log, createwtables.log.out, and createwtables.log.err - These are logs of the DDLIMP process.

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

4.11 Setup Task: Configuring the DAC Server

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

For additional information about configuring the DAC Server, refer to the topic named 'Managing the DAC Server' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

To configure the DAC Server, do the following:

1. Configure the Connection between DAC Server and DAC Repository, as described in Section 4.11.1, "Configuring the Connection Between the DAC Server and DAC Repository".

2. (UNIX Only) Set Environment Variables, as described in Section 4.11.2, "Setting Environment Variables to Enable Communication between the DAC Server on UNIX and Informatica".

3. Start the DAC Server, as described in Section 4.11.3, "Starting the DAC Server".

4.11.1 Configuring the Connection Between the DAC Server and DAC Repository

You must configure the connection between the DAC Server and the DAC Repository.

On Windows, you can use the DAC Client to configure a DAC Server that runs in the same DAC_HOME\ folder. Optionally, or to configure a DAC Server installed in another folder or on another Windows machine, use the serverSetupPrompt.bat file to configure the repository connection.

On UNIX or Linux, use the serverSetupPrompt.sh script to configure the connection between the DAC Server and the DAC Repository, as follows:

• To configure the DAC Server repository connection using the DAC Client, see Section 4.11.1.1, "How to Configure the DAC Server Repository Connection Using the DAC Client (Windows)".

• To configure the DAC Server repository connection using the serverSetupPrompt scripts, see Section 4.11.1.2, "How to Configure the DAC Server Repository Connection Using serverSetupPrompt Scripts (Windows, UNIX or Linux)".

4.11.1.1 How to Configure the DAC Server Repository Connection Using the DAC Client (Windows)

If the DAC Server is co-located with a configured DAC Client in the same DAC_HOME\ folder, you can set the connection between the DAC Server and DAC Repository using the DAC Client, as described below.

To configure the connection between the DAC Server and the DAC Repository using the DAC Client

1. In DAC, select Tools, then DAC Server Management, then DAC Server Setup.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

   Note: The DAC Repository that you connect to using the DAC Client is the one that will store the DAC Server repository connection information that you will specify in this procedure.

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

2. Click Yes to display the Server Configuration dialog.

3. In the Repository Connection Information tab, enter the appropriate information, as described in the table below.

   If the DAC Server is running on the same machine as the DAC Client, click Populate from preconfigured client connection to populate the fields automatically, using connection details from the DAC Client. When prompted for a DAC Connection, select the DAC Connection that you want to use, then click OK. The remaining fields on this dialog are populated automatically. You can skip the rest of this Step and go to Step 4.

   If the DAC Server is running on a different machine to the DAC Client, then use the remaining fields on this dialog to specify the details, as described in the table below:

   Field	Description
   Connection type	Select the type of database that you are using to store 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.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components". 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 a Connection string. If you select MS SQL Server, 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.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components").
   Password	The database or instance password (for example, DAC).

   
   

   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.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components").

   - When you created a DAC connection (for more information, see Section 4.9.1, "Creating a DAC Connection and Logging Into DAC").

4. Click Test Connection to make sure the DAC Repository connection works.

5. Click Save.

4.11.1.2 How to Configure the DAC Server Repository Connection Using serverSetupPrompt Scripts (Windows, UNIX or Linux)

Use the serverSetupPrompt.sh to configure the connection between the DAC Server and the DAC Repository when the DAC Server is installed on UNIX or Linux, as described below. For more information on the DAC Server scripts, see Section A.6, "About the DAC Server Shell Scripts."

Use the serverSetupPrompt.bat file to configure the connection between the DAC Server and the DAC Repository when the DAC Server is installed on Windows, as described below.

To configure the connection between the DAC Server and the DAC Repository using the serverSetupPrompt files:

1. Run the serverSetupPrompt script, as follows:

   • On Windows, double-click the serverSetupPrompt.bat located in the DAC_HOME folder.

   • On UNIX or Linux, run serverSetupPrompt.sh located in the DAC_HOME folder.

2. Enter 1 in the 'Please make your selection' prompt to enter repository connection information.

3. Enter the number for the type of database storing the DAC Repository from the list of connection type choices.

4. Enter the connection information as described in the Connection Information table in section Section 4.11.1.1, "How to Configure the DAC Server Repository Connection Using the DAC Client (Windows)".

5. Enter 2 to test the DAC Repository connection.

6. Enter 5 to save changes.

7. Enter 6 to exit.

4.11.2 Setting Environment Variables to Enable Communication between the DAC Server on UNIX and Informatica

When you install the DAC Server on UNIX, you must define the path for the Informatica Domain file and set environment variables manually. For instructions, see Section A.5.2, "How to Set Environment Variables for DAC Server Communication on UNIX."

4.11.3 Starting the DAC Server

On Windows, start the DAC Server by double-clicking the DAC_HOME\startserver.bat script.

On UNIX or Linux, start the DAC Server by executing startserver.sh.

When you start the DAC Server, look at 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).

For more information on starting and stopping the DAC Server on Windows, UNIX or Linux, see Section A.3, "How to Start and Stop the DAC Server."

4.12 Setup Task: Configuring DAC Integration Settings

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

To configure DAC Integration Settings, do the following:

1. Set DAC System Properties, as described in Section 4.12.1, "Setting DAC System Properties".

2. Register Informatica Services in DAC, as described in Section 4.12.2, "Registering Informatica Services in DAC".

3. Register the Physical Data Sources in DAC, as described in Section 4.12.3, "Setting Physical Data Sources".

You must start the DAC Server to complete certain steps in the procedures described below. For information on how to start the DAC Server, see Section A.3, "How to Start and Stop the DAC Server."

4.12.1 Setting DAC System Properties

You set DAC System Properties to specify how DAC operates. For example, you might use the 'Server Log Level' parameter to change the amount of information recorded in the DAC server log file.

For additional information about configuring DAC, refer to the topic named 'Setting Up DAC System Properties' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

To set DAC System Properties

1. In DAC, navigate to the Views menu, then select Setup, then DAC System Properties.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

2. 
   

3. Specify a value for the following property:

   Property	Value Required
   InformaticaParameterFileLocation	Enter the path to the Informatica source file folder, for example INFA_HOME\server\infa_shared\SrcFiles. You can confirm the source file folder for your environment by launching Informatica Administrator and going to the General Properties area of the Processes tab.

   
   

Note: For a description of all DAC System Properties, refer to the DAC Help System.

4.12.2 Registering Informatica Services in DAC

This section explains how to register the Informatica PowerCenter Integration Services service and the Informatica PowerCenter Repository Service in DAC.

For additional information about configuring Informatica Services, refer to the topic named 'Registering Informatica Services in DAC' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

When you register Informatica Services in DAC, note the following:

• You must register one or more Integration Services service.

• You must register one Repository Service.

Tip

For information about Informatica Services details that you specify in this procedure, log into Informatica Administrator, select the appropriate Domain, and view the Repository Service and Integration Service.

In the example screen shot below, Domain_79456 contains a PowerCenter Repository Service named BIA_RS, and a PowerCenter Integration Service named BIA_IS.

In this example, you would perform Step 2 below to use DAC Client to register the PowerCenter Integration Service BIA_IS (note that the Type is 'Informatica'), as in the following screen shot:

Then, you would perform Step 3 below to use DAC Client to register the PowerCenter Repository Service BIA_RS (note that the Type is 'Repository'), as in the following screen shot:

For more information, see Section A.7, "How to Log Into Informatica Administrator."

To register Informatica Services in DAC

1. In DAC, navigate to the Setup view, and then display the Informatica Servers tab.

   For instructions on logging into DAC, see Section A.1, "How to Log into DAC."

2. Specify the details of the Informatica Integration Service, as follows:

   1. Modify the record with Name = INFORMATICA_DW_SERVER (or create a new record) by entering the following information in the Edit subtab:

      Field	Enter or select
      Name	A name to identify the PowerCenter Integration Service in DAC. For example, you might use 'INFORMATICA_DW_SERVER', or change the name to 'BIA_IS'.
      Type	Informatica.
      Service	The name of the PowerCenter Integration Service that is being used. For example, BIA_IS. The value that you specify here must match the name of the PowerCenter Repository Integration that was specified in Section 4.7.4, "Creating the Informatica Integration Service".
      Server Port	The Informatica Server port. To change this value, click on the value in the Server Port column in the list above.
      Domain	The Informatica domain name. For example, Domain_machinename.
      Login	Informatica Repository user name with appropriate privileges to execute workflows (for example, Administrator). Note: DAC must log in to Informatica as an Informatica Repository Administrator user that is configured in the native security domain. For instructions on how to create such a user, see Section 4.7.6.5, "Creating the Repository Administrator User in the Native Security Domain." For more information about Informatica security domains, see "Chapter4: Managing Users and Groups," in the PowerCenter Administrator Guide.
      Password	Informatica Repository user password (for example, Administrator).
      Num Parallel Workflows per EP	Maximum number of workflows that can be executed in parallel on the Informatica PowerCenter Integration Services service. If the number of sessions is zero or is not specified, the DAC Server assigns the default value of 10.
      Repository Name	The name of the PowerCenter Repository Service (for example, BIA_RS). The value that you specify here must match the name of the PowerCenter Repository Service that was specified in Section 4.7.3, "Creating the Informatica Repository Service".
      Inactive	Indicates whether the PowerCenter Integration Services service will participate in the ETL process.

      
      

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

      Note: Integration Services must be running.

   3. Click Save to save the details.

3. Specify the details of the Informatica Repository Service, as follows:

   1. Modify the record with Name = INFORMATICA_REP_SERVER (or create a new record) by entering the following information in the Edit subtab:

      Field	Enter or Select
      Name	A name to identify the PowerCenter Repository Service in DAC. For example, you might use 'INFORMATICA_REP_SERVER', or change the name to 'BIA_RS'.
      Type	Repository.
      Hostname	The host name or IP address of the machine that is running the Informatica Server.
      Server Port	The Port number of the Gateway Node that is being used for the Informatica PowerCenter domain (for example, 6005). If you need to obtain the name of the Repository Service, log into Informatica Administrator, expand the Domain, display the Node, display the Properties tab, and view the General Properties\Port value. Note: You must use the Node with the General Properties\Gateway Node value set to 'Yes'.
      Domain	(Read-only) The Domain name that you specified for the Domain field in step 2.
      Login	Informatica Repository user name with appropriate privileges to execute workflows (for example, Administrator).
      Password	Informatica Repository user password (for example, Administrator).
      Num Parallel Workflows per EP	Maximum number of workflows that can be executed in parallel on the Informatica PowerCenter Integration Services service. If the number of sessions is zero or is not specified, the DAC Server assigns the default value of 10.
      Repository Name	The name of the PowerCenter Repository Service (for example, BIA_RS). The value that you specify here must match the name of the PowerCenter Repository Service that was specified in Section 4.7.3, "Creating the Informatica Repository Service".
      Inactive	Indicates whether the Repository Service will participate in the ETL process.

      
      

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

      Note: The Repository Service must be running.

   3. Click Save to save the details.

4.12.3 Setting Physical Data Sources

Follow this procedure to specify the transactional database and Oracle Business Analytics Warehouse database in DAC.

For additional information about configuring physical data sources, refer to the topic named 'Setting Up Physical Sources' in the DAC Help System or Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.

Note:

If you have a JD Edwards EnterpriseOne or a JD Edwards World source system hosted on a DB2 for iSeries database, see Section 4.12.3.1, "Specifying a Data Source Connection for JD Edwards EnterpriseOne or JD Edwards World Hosted on a DB2 for iSeries Database."

To specify transactional and data warehouse data sources

1. Log into DAC.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

2. From the Views menu, select Setup, then Physical Data Sources.

   
   

   The Physical Data Sources tab displays a list of pre-created records that you can use as templates to specify the details of your source and target databases, as follows:

   - Use the record with the Name 'DataWarehouse' to specify the details of the Oracle Business Analytics Warehouse database.

   - Use the other records to specify the details of the transactional source systems being used. For example, if you are using an Oracle Database V12 source system, then you might use the pre-created ORA_R12 record as a template.

   The records that are created by DAC for the OLTP sources depend on the business application source systems you selected when importing the DAC metadata. For more information, see Section 4.9.3, "Importing Metadata into the DAC Repository."

3. For each record that you edit or create, enter the following information in the Edit subtab:

   Field	Description
   Name	Logical name for the OLAP or OLTP database connection. Do not change the default values. For example, you must specify DataWarehouse as the name of the OLAP data source. Note: When you create an execution plan to perform an ETL, you need to use the OLAP and OLTP data source names that you specify here as the Parameter values for the execution plan parameters DBConnection_OLTP and DBConnection_OLAP. For more information about setting the Parameter values for an execution plan, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
   Type	Select 'Source' for the database connection for a transactional (OLTP) database. Note: The transactional (OLTP) database user that you register should be the database table owner. Alternatively, at a minimum, the user must have read privileges on the transactional database for all tables and aliases. For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases. For more information, see Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components". Select 'Warehouse' for the database connection for a data warehouse (OLAP) database. Do not change the default values.
   Connection Type	Use to select the database and type of connection that you want to use. The Connection Type that you select determines the name of the next field in the dialog, which is set appropriately as Instance or TNS Name or Connection String or Database Name or ODBC Data Source.
   Instance or TNS Name or Connection String or Database Name or ODBC Data Source Note: The field name changes based on the Connection Type selection.	Enter the value appropriate for your database. 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\.
   Table Owner	Valid database user. Note: The transactional (OLTP) database user that you register should be the database table owner. Alternatively, at a minimum, the user must have read privileges on the transactional database for all tables and aliases. For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases. For more information, see Section 4.5.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".
   Table Owner Password	Valid database user password.
   Num Connections Per EP	Maximum number of database connections this connection pool can contain.
   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).
   Source 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 '1' 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 default value. 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 R12 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 the section entitled, "How to Configure Data Source Num IDs," in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users. 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 DAC drops and creates indexes against this database connection To use the default table space, leave this field blank.

   
   

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

5. Click Save.

4.12.3.1 Specifying a Data Source Connection for JD Edwards EnterpriseOne or JD Edwards World Hosted on a DB2 for iSeries Database

If you have a JD Edwards EnterpriseOne or JD Edwards World source system hosted on a DB2 for iSeries database, follow the steps in this section to specify the OLTP connection in DAC.

1. From the IBM site, select and download the appropriate driver based on the version of your DB2 for iSeries database.

2. Include the driver in the class path, for example, for IBM DB2 on iSeries V5R4M0, download jt400.jar copied from the iSeries server only.

3. On a machine that hosts the DAC Server and an appropriate drive client access for iSeries, create a data source name (DSN) to connect to the source DB2 database (using an ODBC Connection).

4. Log into DAC.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

5. From the Views menu, select Setup, then Physical Data Sources.

6. Select the record for the JD Edwards EnterpriseOne or JD Edwards World source.

7. In the Edit tab, complete the following fields:

   Field	Description
   Name	This is the logical name for the database connection. It must match the name in the OLTP record that you selected. Do not change the default value.
   Type	Enter Source.
   Connection Type	Enter DB2.
   Instance	Or connection string. Enter the DSN that you created in step 3. You enter the DSN, or host name, because the JDBC driver and URL that you enter below are used to override the connection to the database.
   Table Owner	Valid database user.
   Table Owner Password	Valid database user password.
   Num Connections Per EP	Maximum number of database connections this connection pool can contain.
   JDBC Driver	The name of the driver. The driver that you enter will override the driver that connects to the iSeries database. The driver that you select must be applicable to the version of iSeries on which JD Edwards EnterpriseOne or JD Edwards World is hosted. For example, for IBM DB2 on iSeries V5R4M0, you would enter this driver: com.ibm.as400.access.AS400JDBCDriver
   URL	The URL for the driver. The URL that you enter will override the driver URL that connects to the iSeries database. The driver URL that you select must be applicable to the version of iSeries on which JDE source is hosted. For example, for IBM DB2 on iSeries V5R4M0, you would enter this URL: jdbc:as400://<hostname>/LIBRARY

   
   

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

9. Click Save.

4.13 Setup Task: Configuring Relational and Application Connections in Informatica Workflow Manager

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

To configure relational and applications connections, do the following:

1. Log into Informatica PowerCenter Workflow Manager, as described in Section 4.13.1, "Logging Into Informatica PowerCenter Workflow Manager".

2. Configure connections in Informatica PowerCenter Workflow Manager, as described in Section 4.13.2, "Configuring Connections in Informatica PowerCenter Workflow Manager".

4.13.1 Logging Into Informatica PowerCenter Workflow Manager

Note: Before you log into Workflow Manager, start the Informatica services.

To log into Informatica Workflow Manager

1. On the Informatica PowerCenter Client machine, start Informatica PowerCenter Workflow Manager.

2. Choose Repository, then Connect, to display the Connect to Repository dialog.

3. In the Connect to Repository dialog:

   1. In the Password field, specify the Administrator password (for example, Administrator).

   2. If the Connection Settings area is not displayed, click More.

   3. Click Add (next to the Domain drop-down list) to display the Add Domain dialog.

4. In the Add Domain dialog:

   1. Specify the name of the domain that was created when you installed Informatica PowerCenter Services (for example, Domain_<hostname>).

   2. Specify the fully qualified hostname for the gateway host (for example, [e-mail]).

   3. Specify the port for the gateway port (for example, 6005).

   4. Click OK to save the details and close the Add Domain dialog.

5. Click Connect.

4.13.2 Configuring Connections in Informatica PowerCenter Workflow Manager

You use Informatica PowerCenter Workflow Manager to configure the relational and application connections that are required for your deployment, as follows:

1. • For all data sources except PeopleSoft OLTP data sources, configure relational database connections for both OLTP and OLAP data sources by following the steps in Section 4.13.2.1, "How to Configure Relational Connections."

   • For PeopleSoft OLTP data sources, configure application connections by following the steps in Section 4.13.2.2, "How to Configure Application Connections for PeopleSoft OLTP Data Sources."

   • For Teradata databases, follow the additional steps in Section 4.13.2.3, "(Teradata specific) How to Configure the Informatica Repository for Teradata External Loader Connections."

Note: The Informatica services must be running to perform these tasks.

4.13.2.1 How to Configure Relational Connections

Follow this procedure to configure relational connections:

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

   You need to create a connection for each transactional (OLTP) database, and a connection for the Oracle Business Analytics Warehouse (OLAP) database.

2. For each database connection you need to create, do the following:

   1. Click New to display the Select Subtype dialog, select the appropriate database type (for example, Oracle), then click OK to display the Connection Object Definition dialog.

   2. Use the Connection Object Definition dialog to define the relational connection.

      
      

   3. Click OK to save the details.

   Notes:

   • If the target database is Oracle or DB2, use the following settings:

     • Click New, select the appropriate database type, and then click OK.

     • Name: DataWarehouse, for connection to the Oracle Business Analytics Warehouse.

       You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View. For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup view. For example, if your source system is Siebel release 7.8.x, then you name this connection as SEBL_78. For more information, see Section 4.12.3, "Setting Physical Data Sources".

     • User Name: Database user name with the appropriate read and write database permissions to access the database.

     • Password: Password for the user name.

     • Connect string: Connect string used to communicate with the database (refer to the Informatica Help for information about specifying this value).

     • Code Page: Code page compatible with the code page of the database client. If NLS_LANG (for Oracle database) or DB2CODPAGE (for DB2 database) has been set, then the Code Page value should be a code page compatible with the language set by these variables. For more information, see Section 4.3.3, "Code Page and Data Movement Requirements".

   • If the target database is SQL Server, use the following settings:

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

     • Name: DataWarehouse, for the connection to the Oracle Business Analytics Warehouse. You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View.For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup View. (For example, if your source system is Siebel version 7.8.x then you name this connection as SEBL_78). For more information, see Section 4.12.3, "Setting Physical Data Sources".

     • User Name: Database user name with the appropriate read and write database permissions to access the database.

     • Password: Password for the user name.

     • Code Page: Code page compatible with the code page of the database client.

     • Database name: Name of the database.

     • Server name: Database server name.

     • Domain name: Name of the domain.

   • If the target database is Teradata, use the following settings:

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

     • Name: DataWarehouse, for the connection to the Oracle Business Analytics Warehouse. You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View.For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup View. (For example, if your source system is Siebel version 7.8.x then you name this connection as SEBL_78). For more information, see Section 4.12.3, "Setting Physical Data Sources".

     • User Name: Database user name with the appropriate read and write database permissions to access the database.

     • Use Parameter in Password: Indicates the password for the database user name is a session parameter, $ParamName. Define the password in the workflow or session parameter file, and encrypt it using the pmpasswd CRYPT_DATA option.

     • Password: Password for the database user name. For Teradata connections, this overrides the database password in the ODBC entry. Passwords must be in 7-bit ASCII.

     • Code Page: Code page compatible with the code page of the database client.

     • Database name: Name of the database. For Teradata connections, this overrides the default database name in the ODBC entry. If you do not enter a database name for a Teradata connection, the Integration Service uses the default database name in the ODBC entry.

     • Data Source name: Name of the Teradata ODBC data source.

     • Domain name: Name of the domain.

4.13.2.2 How to Configure Application Connections for PeopleSoft OLTP Data Sources

PeopleSoft OLTP data sources use Application Connections that implement Informatica's PowerConnect for PeopleSoft. PeopleSoft OLTP data sources do not use relational connections. Therefore, you need to use Informatica Workflow Manager to define Application Connections for the PeopleSoft OLTP data sources as described below.

Note: You need to configure relational connections for OLAP databases with PeopleSoft adapters (for more information, see Section 4.13.2.1, "How to Configure Relational Connections.").

To configure Application Connections for PeopleSoft OLTP data sources:

1. In Informatica PowerCenter Workflow Manager, select Connections, then Application, to display the Application Connection Browser dialog.

   You need to create an Application Connection for each PeopleSoft transactional (OLTP) data source.

2. For each Application Connection that you need to create, do the following.

   1. Click New to display the Select Subtype dialog, select the appropriate database type (for example, PeopleSoft Oracle), then click OK to display the Connection Object Definition dialog.

   2. Use the Connection Object Definition dialog to define an Application Connection (for example, a connection named PSFT_9_0_HCM).

      
      

   3. Click OK to save the details.

Notes:

• You must specify the Name value exactly as it appears in the Physical Data Sources tab in the DAC Setup View. For example, if your source system is named PSFT_9_0_HCM in DAC, then you must name this connection as PSFT_9_0_HCM. For more information, see Section 4.12.3, "Setting Physical Data Sources".

• Connect string: Connect string for the database (refer to the Informatica Help for information about specifying this value).

4.13.2.3 (Teradata specific) How to Configure the Informatica Repository for Teradata External Loader Connections

To configure the Informatica Repository for Teradata, you need to do the following:

• Specify Loader Connections for Teradata (for more information, see Section 4.13.2.3.1, "How to Specify Loader Connections for Teradata").

• For each workflow, specify Loader Connection details at the session level (for more information, see Section 4.13.2.3.2, "How to Specify Teradata Details at the Workflow Level").

4.13.2.3.1 How to Specify Loader Connections for Teradata

Follow this procedure to configure the Informatica Repository in Informatica PowerCenter Workflow Manager for Teradata external loader connections.

1. In Informatica PowerCenter Workflow Manager, select Connections, then Loader to display the Loader Connection Browser.

2. In the Objects list, select Teradata_Tpump_Upsert, and then click Edit to display the Connection Object Definition dialog.

3. Edit the User Name, Password, TDPID, Database Name, Error Database, and Log Table Database, and other attributes as necessary. Please note that if you do not enter the Error Database and Log Table Database, Informatica will default it to the same as the Tables Database.

4. Repeat these steps for the following objects:

   • Teradata_Tpump_Update

   • Teradata_Tpump_Insert

   • Teradata_Tpump_Delete

   • Teradata_Tpump_Upsert

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

   For example:

   • tpump -c UTF8

6. Click Close to close the Loader Connection Browser.

4.13.2.3.2 How to Specify Teradata Details at the Workflow Level

For each session, you need to specify Teradata details, as follows:

1. In Informatica PowerCenter Workflow Manager, go to the workflow and open the session in the Task Developer pane.

2. Double-click on the session to display the Edit Tasks dialog.

3. Display the Mapping tab.

   
   

4. On the Targets node on the left navigator panel, select the Teradata table name.

5. In the Writers area, select "File Writer" from the Writers drop-down list for the target table.

6. In the Connections area, select "Teradata_Tpump_Upsert" or other "Teradata_Tpump_XXX" as the loader.

7. Click on the edit icon (that is, the pencil icon) next to the Connections - Value field to display the Connection Object Definition dialog.

   
   

8. Enter the User Name and Password to log in to the Teradata database.

   Note: In the Attributes list, make sure that the following attributes are specified:

   • TDPID

   • Database name

   • Error Database

   • Log Table Database

   If the above attribute values are not inherited from the attributes defined at the connection level, you must specify them.

9. In the Attributes list, specify values for Error Table and Log Table.

4.14 Setup Task: Configuring the SiebelUnicodeDB Custom Property

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

Note:

This procedure is not specific to the Siebel source system. It must be performed for all types of source systems.

If your source to target data movement configuration is Unicode to Unicode, you need to create a custom property called SiebelUnicodeDB on Integration Services. If your source to target data movement configuration is either Code Page to Code Page or Code Page to Unicode, you do not need to create this property. For more information on supported source to target configuration modes, and how to determine the source to target configuration for your environment, see Section 4.3.3, "Code Page and Data Movement Requirements."

To create and set the SiebelUnicodeDB custom property on Integration Services

1. Log into Informatica Administrator. For information on how to log into Informatica Administrator, see Section A.7, "How to Log Into Informatica Administrator."

2. Select the Integration Service.

3. In the Properties tab, scroll down to the Custom Properties area, click Edit, then click New to display the New Custom Property dialog.

4. Use the New Custom Property dialog to specify the SiebelUnicodeDB property, as follows:

   • In the Name field, enter the following:

     SiebelUnicodeDB

   • In the Value field, enter the following:

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

     Where:

     [user_OLTP] is the database user for the OLTP source database. It must match exactly the value you entered for the User Name field when creating the relational connection for the OLTP in Informatica Workflow Manager.

     [ConnectString_OLTP] is the connect string for the OLTP. It must match exactly the value you entered for the Connect String field when creating the relational connection for the OLTP in Informatica Workflow Manager.

     [user_OLAP] is the database user for the Oracle Business Analytics Warehouse database. It must match exactly the value you entered for the User Name field when creating the relational connection for the data warehouse in Informatica Workflow Manager.

     [ConnectString_OLAP] is the connect string for the data warehouse. It must match exactly the value you entered for the Connect String field when creating the relational connection for the data warehouse in Informatica Workflow Manager.

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

     Note: Always leave a space between the strings for OLTP and OLAP. You must enter the user names and connection strings in the same case as you used for the relational connections in Informatica Workflow Manager.

     For more information about Relational Connections, see Section 4.13.2, "Configuring Connections in Informatica PowerCenter Workflow Manager".

4.15 Setup Task: Setting Up DAC to Receive Email Notification

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

To set up DAC to receive email notification, do the following:

1. Configure email recipients in DAC, as described in Section 4.15.1, "Configuring Email Recipients in DAC".

2. Configure the email server details in DAC, as described in Section 4.15.2, "Configuring Email in the DAC Server".

4.15.1 Configuring Email Recipients in DAC

Follow this procedure to configure email recipients, who you want to be notified about the status of ETL processes.

To configure email recipients in DAC

1. In DAC, navigate to the Setup view.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

2. 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.15.2 Configuring Email in the DAC Server

Follow this procedure to configure the email administrator account in 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.

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. In the DAC menu bar, select Tools, then DAC Server Management, then DAC Server Setup.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

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

2. Click Yes.

   The Server Configuration dialog appears.

3. Display the Email Configuration tab, and enter the email details for the email address to which you want to send DAC Server information emails.

4. Click Save.

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

4.16 Setup Task: Configuring the Oracle BI Repository Connections

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

When you first install and setup Oracle Business Intelligence Applications, you must configure the predefined repository connections and variables in the RPD file. This section explains the predefined connection pools and variables, and how to configure them using Oracle BI Administration Tool.

To configure Oracle BI Repository Connections, do the following:

1. Read about the required connections in Section 4.16.1, "About the Predefined Connection Pools in the Oracle Business Analytics Warehouse".

2. Configure Oracle BI Repository Connections, as described in Section 4.16.2, "How to Configure the Oracle BI Repository Connections".

3. Configure Oracle BI Repository Variables, as described in Section 4.16.3, "How to Configure Oracle BI Repository Variables".

4. Configure Dynamic Source Names, as described in Section 4.16.4, "How to Configure Dynamic Data Source Names".

4.16.1 About the Predefined Connection Pools in the Oracle Business Analytics Warehouse

The Oracle BI repository contains the following predefined databases:

• Oracle Data Warehouse

• Oracle EBS OLTP

• PeopleSoft OLTP

• Siebel OLTP

• Loyalty Input Data Source

You configure the connection pools for databases in the Physical layer in Oracle BI Administration Tool.

Figure 4-2 BI Repository Physical Layer in Oracle BI Administration Tool

The Oracle Data Warehouse physical database has two predefined connection pools:

• Oracle Data Warehouse Connection Pool. The Oracle Business Analytics Warehouse Connection Pool is the main connection pool in the Oracle BI Repository. You need to configure this connection pool to connect to your physical data warehouse. The connection is used by the session initialization blocks. You can use this connection pool to set up a dynamic data source name.

• Oracle Data Warehouse Repository Initblocks Connection Pool. You need to configure the Oracle Data Warehouse Repository Initblocks Connection Pool to connect to the your physical data warehouse. The connection is used by the repository level initialization blocks. Repository level initialization blocks cannot be configured to use the dynamic data source name.

For information about configuring configuration pools, see Section 4.16.2, "How to Configure the Oracle BI Repository Connections".

You can also set up dynamic data source names, which allow an Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For more information about how to set up dynamic data source names, see Section 4.16.4, "How to Configure Dynamic Data Source Names".

You also need to configure the following Static variables:

• OLAP_DSN. The value of the OLAP_DSN static variable is set to the data source name for the warehouse database.

• OLAP_USER. The value of the OLAP_USER static variable is set to the database user name for the warehouse database.

• OLAPTBO. The value of the OLAPTBO static variable is set to the database table owner for the data warehouse database.

You configure Static variables in Oracle BI Administration Tool using the Variable Manager (choose Manage, then Variables, and expand the Variables\Static node). For more information about configuring Static variables, see Section 4.16.3, "How to Configure Oracle BI Repository Variables".

Figure 4-3 Variable Manager in Oracle BI Administration Tool

The PeopleSoft OLTP, Siebel OLTP, and Oracle EBS OLTP databases each have two predefined connection pools. The actual databases in the RPD depend on which modules are licensed. The connection pools and their functions within each database are listed below.

• Oracle EBS OLTP:

  • Oracle EBS OLTP DBAuth Connection Pool. The Oracle EBS OLTP DBAuth Connection Pool is used if database authentication is required.

  • Oracle EBS OLTP Connection Pool. The Oracle EBS OLTP Connection Pool is used to connect to the Oracle EBS OLTP system.

  You also need to configure the following Static variables:

  • ORA_EBS_OLTP_DSN. The value of the ORA_EBS_OLTP_DSN static variable is set to the data source name for the Oracle EBS OLTP database.

  • ORA_EBS_OLTP_USER. The value of the ORA_EBS_OLTP_USER static variable is set to the database user name for the Oracle EBS OLTP database.

• Siebel OLTP

  • Siebel OLTP DBAuth Connection Pool. The Siebel OLTP DBAuth Connection Pool is used if database authentication is required.

  • Siebel OLTP Connection Pool. The Siebel OLTP Connection Pool is used to connect to the Siebel OLTP system.

  You also need to configure the following Static variables:

  • OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the Siebel OLTP database.

  • OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the Siebel OLTP database.

• PeopleSoft OLTP

  • PeopleSoft OLTP DBAuth Connection Pool. The PeopleSoft OLTP DBAuth Connection Pool is used if database authentication is required.

  • PeopleSoft OLTP Connection Pool. The PeopleSoft OLTP Connection Pool is used to connect to the PeopleSoft OLTP system.

  You also need to configure the following Static variables:

  • PSFT_OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the PeopleSoft OLTP database.

  • PSFT_OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the PeopleSoft OLTP database.

• Loyalty Input Data Source:

  • Loyalty Input Connection Pool. The Loyalty Input Connection Pool is used to connect to the Loyalty OLTP system.

4.16.2 How to Configure the Oracle BI Repository Connections

The section explains how to configure the repository connections used by Oracle Business Intelligence Applications. At a minimum, you need to configure the following:

• the connection pool for the 'Oracle Data Warehouse' database.

• the connection pool for each OLTP data source that you want to deploy (for example, Oracle EBS OLTP, PeopleSoft OLTP, Siebel OLTP).

For each connection pool that you configure, you need to:

• Specify the database type for each connection pool (for more information, see Section 4.16.2.1, "How to specify the database type for connection pools").

• Specify the connection details for each connection pool (for more information, see Section 4.16.2.1, "How to specify the database type for connection pools").

Note:

No additional configuration is required for Oracle's JD Edwards EnterpriseOne and JD Edwards World, as both use the standard Oracle Data Warehouse physical connection.

4.16.2.1 How to specify the database type for connection pools

You need to specify the database type for the Oracle Data Warehouse connection pool, and the connection pool for each OLTP data source that you wish to deploy.

To specify the database type for connection pools

1. Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.

   For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog."

2. In the Physical pane, double-click the Oracle Data Warehouse object.

3. Display the General tab.

   
   

4. Use the Database field to specify your database type.

5. Save the repository.

6. Click Yes to Check Global Consistency.

7. Repeat steps 1-6 for each OLTP data source that you want to deploy (for example, Oracle EBS OLTP, PeopleSoft OLTP, and Siebel OLTP).

8. Click OK when the Warnings are displayed.

4.16.2.2 How to specify connection details for connection pools

You need to specify the connection details for the Oracle Data Warehouse connection pool, and the connection pool for each OLTP data source that you want to deploy.

To configure the Oracle BI Repository connection pools

1. Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.

   For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog."

2. In the Physical pane:

   1. Expand the Oracle Data Warehouse node, and double-click the Oracle Data Warehouse Connection Pool to display the Connection Pool dialog.

      
      

   2. Use the Data source name field to specify 'VALUEOF(OLAP_DSN)'.

   3. Use the User name field to specify 'VALUEOF(OLAP_USER)'.

   4. Use the Password field to specify your database password.

   Note: The variable values specified in the VALUEOF functions are specified in the Variable Manager. For more information about specifying variable values, see Section 4.16.3, "How to Configure Oracle BI Repository Variables".

3. Repeat Steps a. to d. above for the other connection pools that you want to deploy.

   For example, to configure an Oracle EBS OLTP, expand the Oracle EBS OLTP object, then double-click on Oracle EBS OLTP Connection Pool to display the Connection Pool dialog.

   
   

4. Save the repository.

5. Click Yes to Check Global Consistency.

6. Click OK when the Warnings are displayed.

4.16.3 How to Configure Oracle BI Repository Variables

The section explains how to configure the Oracle BI Repository variables.

Note: If you want to deploy multi-calendar with Oracle Financial Analytics, you must have enabled a number of Initialization Blocks that are disabled out-of-the-box. For more information, see Chapter 3 in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.

To configure the Oracle BI Repository variables

1. Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.

   For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.17, "Setup Task: Deploying the Metadata Repository and Presentation Catalog."

2. On the Manage menu, click Variables to display the Variable Manager.

3. Expand the Variables\Static node.

   
   

4. Edit the following variables, which apply to all deployments:

   • GLOBAL_CURRENCY1

   • GLOBAL_CURRENCY2

   • GLOBAL_CURRENCY3

   • OLAP_DSN

   • OLAP_USER

   • OLAPTBO

   Notes:

   • The global currency values must match the Source System Parameter values in DAC.

   • In a SQL Server database environment, if you deploy an application that uses Dim_W_ORDER_ITEM_F_Contact_First_and_Last_Order_Dates (for example, Siebel Marketing 8.1.1.1), then you must set the value of the OLAPTBO session variable to 'dbo'. If you do not set this value, then you get the an error message similar to the following:

     [SQL Server]Invalid object name @ 'SIEBEL.W_ORDERITEM_F'.. [nQSError: 16002]
     

5. Edit the following variables that are specific to your deployment:

   For Oracle EBS OLTP data sources, edit the following variables:

   • ORA_EBS_OLTP_DSN (Oracle EBS-specific)

   • ORA_EBS_OLTP_USER (Oracle EBS-specific)

   For PeopleSoft OLTP data sources, edit the following variables:

   • PSFT_OLTP_DSN (PeopleSoft-specific)

   • PSFT_OLTP_USER (PeopleSoft-specific)

   For Siebel OLTP data sources, edit the following variables:

   • OLTP_DSN (Siebel-specific)

   • OLTP_USER (Siebel-specific)

6. Close the Variables Manager dialog.

To reset the Oracle BI Repository password

1. On the Manage menu, click Security, to display the Security Manager.

   
   

2. In the Security Manager dialog, click Users, and then:

   1. Double-click on Administrator user to display the User dialog.

      
      

   2. Use the Password field to change the password.

   3. Repeat steps a- b for the SADMIN user.

   4. Save and close the Security Manager.

3. Save the repository.

4. Click Yes to Check Global Consistency.

5. Click OK when the Warnings are displayed.

4.16.4 How to Configure Dynamic Data Source Names

This sections explains how to create and configure dynamic data source names.

Dynamic data source names allow the Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For this you need to have your user authentication based on an external system (like LDAP), and add the following to your repository:

1. Create new session variables: Session_OLAP_DSN and Session_OLAP_USER.

2. Create a Session Init Block which uses 'Oracle Data Warehouse Repository Initblocks Connection Pool' to populate these session variables based on the user login.

3. Add this Initialization Block to the Execution Precedence list of the Authorization Initialization block.

4. Modify the values of Data Source Name and User Name fields in 'Oracle Data Warehouse Connection Pool' to be VALUEOF(Session_OLAP_DSN) and VALUEOF(Session_OLAP_USER) respectively.

   For information about configuring connection pools, see Section 4.16.2.2, "How to specify connection details for connection pools".

5. Update the field password with the same value as of User Name.

4.17 Setup Task: Deploying the Metadata Repository and Presentation Catalog

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

After an Oracle BI Applications installation, the Metadata Repository file (RPD) and Presentation Catalog are located on the installation machine. You must use Fusion Middleware Control to deploy and configure the Oracle BI Applications RPD file and Presentation Catalog files. The target Oracle BI EE machine can be the Windows installation machine, or any supported OS for BI EE (that is, it is not limited to Windows).

Note: A Windows machine is required to edit BI metadata using Oracle BI Administration Tool.

For information about extending and scaling a deployment, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To deploy the Metadata Repository and Presentation Catalog:

1. Copy the Metadata Repository and Presentation Catalog to the target Oracle BI EE machine, as follows:

   Table 4-8 Copying Oracle BI EE Components to an Oracle BI EE Machine

   Copy these files	From here:	To here:
   OracleBIAnalyticsApps.rpd	Installation machine: ORACLE_HOME\biapps\repository\	Target Oracle BI EE machine: ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis<n>\repository
   EnterpriseBusinessAnalytics.zip	Installation machine: ORACLE_HOME\biapps\catalog\	Target Oracle BI EE machine: ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplicationobips<n>\catalog The EnterpriseBusinessAnalytics.zip file must be unzipped and the contents copied into this \catalog\ folder.

   
   

   Note: The target Oracle BI EE machine can be the installation machine, or a separate machine.

   After copying the RPD file, you can verify the repository by using Oracle BI Administrator to open the OracleBIAnalyticsApps.rpd file. When you first open the RPD file, use Admin123 as the password to access the repository, then Oracle recommends that you use the File\Change Password option to change the password.

   To open the OracleBIAnalyticsApps.rpd file using the Oracle Business Intelligence Administration Tool, use the password Admin123.

   Note:

   You should change the default password. Use the Oracle Business Intelligence Administration Tool to do so. For instructions, see the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

2. Use Oracle Fusion Middleware Control to deploy the Metadata Repository and Presentation Catalog, as follows:

   1. In Fusion Middleware Control, navigate to the Business Intelligence Overview page.

      For example, connect to http://<hostname>:7001/em, and select <Farm>, then Business Intelligence, then coreapplication, then Overview.

   2. Display the Repository tab of the Deployment page.

      
      

      On the Repository tab, you can view the name of the current published repository (called the Default RPD).

   3. Click Lock and Edit Configuration to allow changes to be made.

   4. To publish the repository in shared mode, select Share Repository and provide a Shared Location.

      If you do not select this option, then the repository that you upload on this page is published in distributed mode by default.

   5. Use the Upload BI Repository Server area to specify the location of the OracleBIAnalytics.rpd file and the repository password.

      Click the Help button on the page to access the page-level help for the following options: Repository File option, Repository Password option.

   6. Use the BI Presentation Catalog area to specify the location of the BI Presentation Catalog.

      Click the Help button on the page to access the page-level help for the following options: Catalog Location option.

   7. Click Apply, then click Activate Changes.

   8. Return to the Business Intelligence Overview page and click Restart.

4.18 Setup Task: Applying the Oracle BI Applications Security Policy to the BI Domain

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

If your deployed BI EE system with Oracle BI Applications is different from the BI EE system used to install Oracle BI Applications, then you must apply the security policy to the BI Domain on the deployed BI EE system by following the steps below.

If your deployed BI EE system with Oracle BI Applications is the same as the BI EE system used to install Oracle BI Applications, then the Oracle BI Applications installer performs this configuration automatically.

To apply the Oracle BI Applications Security Policy to the BI Domain:

Note: Machine A is the installation machine. Machine B is the deployment machine.

1. Shutdown all processes in the BI EE system. Specifically:

   • the Administration Server

   • (if there is a cluster) all managed servers in the bi_cluster cluster

   • all opmn managed processes

2. On machine B, backup and rename the existing DOMAIN_HOME/config/fmwconfig/system-jazn-data.xml.

   For example, if the BI EE root folder is named OracleBIEE11g, then the domain folder location (on Windows) might be C:\OracleBIEE11g\user_projects\domains\bifoundation_domain\config\fmwconfig.

3. Copy the Oracle BI Applications jazn file from machine A at ORACLE_HOME/biapps/admin/provisioning/system-jazn-data.xml to machine B at DOMAIN_HOME/config/fmwconfig.

4. Start all the processes in the BI EE system for the Oracle BI Applications security policy to take effect. Specifically:

   • the Administration Server

   • (if there is a cluster) all managed servers in the bi_cluster cluster

   • all opmn managed processes

4.19 Setup Task: Additional Configuration Tasks

This task is performed as part of Section 4.4, "High-Level Installation and Setup Task List".

This section provides configuration steps that might be required, depending on your specific environment, as follows:

• For tasks relating to Security Initialization Blocks, see Section 4.19.1, "Configuring Security-Related Initialization Blocks".

• For tasks relating to DAC Source System Parameters, see Section 4.19.2, "Setting DAC Source System Parameters"

• For tasks relating to DB2-UDB, see Section 4.19.3, "Creating Stored Procedures for DB2-UDB".

• For tasks relating to Siebel source systems, see Section 4.19.4, "Siebel-Specific Configuration Tasks".

• For tasks relating to Teradata databases, see Section 4.19.5, "Teradata-Specific Configuration Tasks".

Note:

After you complete the tasks in this section and before you run the first ETL load process, you might need to perform additional configuration steps depending on your environment. For additional information about source system-specific and application-specific mandatory configuration tasks, see Section 2.1, "High-Level Overview of Configuring Oracle BI Applications," in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.

4.19.1 Configuring Security-Related Initialization Blocks

You might need to configure the security-related initialization blocks that are provided with Oracle BI Applications to work with your particular source system. For information about the Oracle BI Applications security model, see Oracle Business Intelligence Applications Security Guide. For information about configuring initialization blocks, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

4.19.2 Setting DAC Source System Parameters

You set source system parameters (also known as DAC ETL Preferences) in 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. In DAC, go to the Design view.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC."

2. Make sure that you have selected the correct container from the containers drop-down list. You cannot edit preconfigured containers. Make a copy of an existing container in order to make edits.

3. Display the Source System Parameters tab.

   
   

   Note:

   For information about recommended settings for specific databases, see Chapter 3, "Preinstallation and Deployment Requirements for Oracle BI Applications".

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

5. Click Save.

4.19.3 Creating Stored Procedures for DB2-UDB

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 folder ORACLE_HOME\biapps\dwrep\siebproc\db2udb\. There is a sub-folder for each platform. For example, stored procedures for the Windows platform are stored in the sub-folder ORACLE_HOME\biapps\dwrep\siebproc\db2udb\win32\.

Each platform-specific folder contains the following sub-folders:

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

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

These folders also contain the files siebproc.sql and sqlproc.ksh, which are used to create the function. For more information, see Section 4.19.3.2, "How to Create DB2 Stored Procedures").

4.19.3.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 the stored procedures

• 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.19.3.2, "How to Create DB2 Stored Procedures").

4.19.3.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 folder (i.e. \siebproc\ or \siebproc64\) from the appropriate platform-specific folder to a folder on the DB2 server side.

   For example, for a 32-bit DB2 environment on a Windows platform, you might copy the folder ORACLE_HOME\biapps\dwrep\siebproc\db2udb\win32\siebproc\ to the folder \SQLLIB\function\ on the DB2 server side.

   Note:

   For more information about the location of DB2 stored procedures, see Section 4.19.3, "Creating 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\ folder to \siebproc\.

   For example, if you copied stored procedures to d:\Program Files\SQLLIB\function\siebproc64\, rename this folder 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.19.4 Siebel-Specific Configuration Tasks

This section contains tasks you are required to complete if your source system is Siebel. This section contains the following topics:

• Section 4.19.4.1, "Updating Siebel Transactional Database Schema Definitions"

4.19.4.1 Updating Siebel Transactional Database Schema Definitions

Note:

The steps in this section are required if are using a Siebel source system.

This section explains how to update Siebel transactional database schema definitions. It contains the following sections:

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

• Section 4.19.4.1.2, "How to Apply Siebel CRM Schema Changes For Oracle, DB2/UDB, MSSQL"

• Section 4.19.4.1.3, "How to Apply Siebel CRM Schema Changes for DB2 on OS/390 and z/OS"

• Section 4.19.4.1.4, "How to Apply the Siebel CRM Image Table to a Siebel Transactional Database"

• Section 4.19.4.1.5, "About Delete Triggers"

• Section 4.19.4.1.6, "How to Verify Siebel (CRM) Schema Changes"

4.19.4.1.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:

ORACLE_HOME\biapps\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_DSN> /GSSE_ROLE [/W Y] [/Z Y] /F <ORACLE_HOME\biapps\dwrep\DDL_OLTP.CTL> /L <ORACLE_HOME\biapps\dwrep\DDL_OLTP.log>

Note: Where <ODBC_DSN> is the ODBC connection created as described in section Section 4.8.5, "(Siebel specific) Creating an ODBC Database Connection for a Siebel Source System".

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.

You can obtain a complete list of DDLIMP parameters by running DDLIMP in a command line. DDLIMP is located in the ORACLE_HOME\biapps\dwrep\bin.

4.19.4.1.2 How to Apply Siebel CRM 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 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.

To apply Siebel CRM schema changes for Oracle, DB2/UDB and MSSQL databases

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

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

   Note:

   If you are applying schema changes to a Siebel CRM release 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 ORACLE_HOME\biapps\dwrep\DDL_OLTP.CTL /L ORACLE_HOME\biapps\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.19.4.1.3 How to Apply Siebel CRM Schema Changes for DB2 on OS/390 and z/OS

1. Edit the following parameters in the Storage control files located in ORACLE_HOME\biapps\dwrep\STORAGE_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:

   ORACLE_HOME\biapps\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /G SSE_ROLE /F ORACLE_HOME\biapps\dwrep\DDL_OLTP_DB290.CTL /L ORACLE_HOME\biapps\dwrep\DDL_OLTP.log /5 Y /A <SCHEMA_OWNER> /Y ORACLE_HOME\biapps\dwrep\STORAGE_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 indexes for the tables created, run the following SQL Script from the DB2 command line utility connected to your OLTP Schema:

   ORACLE_HOME\biapps\dwrep\Create_OLTP_Db2390_index.sql

4.19.4.1.4 How to Apply the Siebel CRM Image Table to a Siebel Transactional Database

1. In DAC, select Design, then Tables, and query for tables where the Image Suffix value is not null.

   
   

2. Right click over the returned tables, then select 'Change Capture scripts', then 'Generate image and trigger scripts' to display the Triggers And Image Tables dialog.

   
   

3. At the Triggers And Image Tables dialog, do the following:

   • Select the All Tables In The List radio button.

   • Select the Generate Image Table Scripts check box.

   • Select the appropriate Database type.

4. Click OK to generate the database scripts.

   DAC generates the scripts in a pop-up page.

5. Execute the scripts in your OLTP database.

4.19.4.1.5 About Delete Triggers

Note:

Delete triggers are only used with Siebel CRM databases.

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:

DAC can create delete triggers on source tables (refer to Oracle Business Intelligence Data Warehouse Administration Console User's 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 data warehouse table.

4.19.4.1.6 How to Verify Siebel (CRM) 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.

1. Use a SQL tool to make sure that the following tables were created in the transactional database:

   • S_ETL_R_IMG_xxx

   • S_ETL_I_IMG_xxx

   • S_ETL_D_IMG_xxx

   • S_ETL_PARAM

   • S_ETL_PRD_ATTR

   • S_ETL_PRD_REL

4.19.5 Teradata-Specific Configuration Tasks

This section contains tasks you are required to complete if you are using a Teradata database in your Oracle BI Applications environment.

This section contains the following topics:

• Section 4.19.5.1, "Setting Up the Code Page File Property for Unicode Environments on Teradata Databases"

• Section 4.19.5.2, "Setting Up the HOSTS File for Teradata Installations on Windows"

• Section 4.19.5.3, "Setting Up the HOSTS File for Teradata Installations on UNIX"

• Section 4.19.5.4, "Teradata-Specific Installation Checklist"

4.19.5.1 Setting Up 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. In Informatica PowerCenter Workflow Manager, drag a Workflow into the Workflow Designer pane.

2. In the Workflow Designer pane, double click the Task (for example, SDE_PSFT_APTermsDimension) to display the Edit Tasks dialog.

3. Display the Mappings tab.

4. Select Target in the left pane. In the Properties section, click on the Set File link.

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

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

4.19.5.2 Setting 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 PowerCenter Integration Services service is installed by adding an entry in the HOSTS file.

To set up the HOSTS file for Teradata installations on Windows

1. On the machine where the Informatica PowerCenter Integration Services service is installed, go to the \<SystemRoot>\system32\drivers\etc folder 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.19.5.3 Setting Up the HOSTS File for Teradata Installations on UNIX

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 on UNIX

1. How to Set Up the HOSTS File for Teradata Installations on UNIX.

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, refer to the Teradata documentation.

4.19.5.4 Teradata-Specific Installation Checklist

Table 4-9 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-9 Teradata-Specific Installation Checklist for Windows

Action	Link to Topic
Review Teradata-specific database guidelines.	Section 3.8, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse".
Create or restore the Informatica Repository using the Informatica Repository Manager. You cannot create or restore the Informatica Repository using DAC. You must use Informatica Administrator.	Section 4.7.5, "Restoring the Pre-built Informatica Repository"
Configure the source and data warehouse database relational connections in Informatica PowerCenter Workflow Manager. You cannot use the DAC's Data Warehouse Configurator to configure relational connections.	Section 3.8, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse"
Set the Code Page File Property for Unicode Environments.	Section 4.19.5.1, "Setting Up the Code Page File Property for Unicode Environments on Teradata Databases"
Set up the HOSTS file.	Section 4.19.5.2, "Setting Up the HOSTS File for Teradata Installations on Windows"

4.20 About Running A Full Load ETL

After you have installed and configured Oracle BI Applications, your Oracle Business Analytics Warehouse (OLAP) database is empty. You need to perform a full load ETL to populate your Oracle Business Analytics Warehouse.

The ETL processes for Oracle BI Applications are created and managed in DAC. For detailed information about running ETLs in DAC, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.

For an example of using DAC to run a full load ETL, see Section 4.20.1, "An Example of Running a Full Load ETL."

4.20.1 An Example of Running a Full Load ETL

This section uses an example to show you how to get started quickly with running a full load ETL. In this example, you have installed Oracle Financial Analytics with an Oracle EBS OLTP data source, and you want to load OLTP data for the subject area Receivables, as follows:

1. In the DAC menu bar, select File, then New Source System Container, to display the New Source System Container dialog, and specify details of the new container.

   Note: You cannot make any changes to the preconfigured containers. You must make a copy of a container before you can make any changes to it.

   For more information about logging into DAC, see Section A.1, "How to Log into DAC." For more information about the source system container functionality in DAC, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.

2. Select the Create as a Copy of Existing Container radio button, and select the appropriate container from the Existing Containers drop-down list, then click OK.

3. In the Design view, display the Source System Parameters tab and set the parameters appropriately.

4. In the Setup view, display the DAC System Properties tab and set the properties appropriately.

5. Display the Execute view, and display the Execution Plan tab.

6. Click New, display the Edit tab, and use the Name field to specify a name for the ETL process.

7. Display the Subject Areas tab and click Add/Remove to display the Choose Subject Areas dialog.

8. Select the new container that you created in Step 1 from the container drop-down list at the top of the Choose Subject Areas dialog.

9. Select Financials - Receivables, click Add, then click OK.

10. Display the Parameters tab, and click Generate.

11. On the Parameters tab, edit the parameters as follows:

    • Edit the value of DBConnection_OLAP and set it to the same value as the name of the OLAP database that you specified in the Physical Data Source dialog (for example, DataWarehouse).

    • Edit the value of DBConnection_OLTP and set it to the same value as the name of the OLTP database that you specified in the Physical Data Source dialog (for example, ORA_11_5_8).

    • If there is a FlatFileConnection parameter, edit the value of FlatFileConnection and set it to the same value as the name of the flat file data source that is specified in the Physical Data Source dialog (for example, ORA_11_5_8_Flatfile).

12. On the Execution Plans tab, click Build.

13. On the Execution Plans tab, click Run Now.

    DAC will perform a full load for Financials - Receivables.

14. Use the Current Run tab to check the status of the ETL run.

    If the ETL run was successful, you will see 'Success' in the Run Status field, and the End Timestamp value will be set to the time and date when the ETL was completed.

    If the ETL run fails, use the information in the Description tab and the Audit Trail tab to diagnose the error that caused the failure.

    Tip:

    If an ETL run fails, you cannot re-run the ETL until the failed ETL has been cleared from the Current Run tab. To clear an ETL from the Current Run tab, right click on the ETL and select Mark As Completed.