4 Installing and Setting Up Oracle BI Applications

4.3.1 Oracle Business Intelligence Infrastructure Requirements

The Oracle BI Applications installer requires that you have already installed Oracle Business Intelligence Enterprise Edition on a Windows machine. The instance of Oracle Business Intelligence Enterprise Edition must be a complete installation; that is, you must install it using the Complete setup type option.

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.

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.1 requires Informatica PowerCenter 8.6.1 with Hotfix 6. If you license Informatica PowerCenter with Oracle BI Applications, you install Informatica PowerCenter 8.6.1 and Hotfix 6 from the Informatica PowerCenter DVD provided with Oracle Business Intelligence Applications.

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

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

Informatica PowerCenter 8.6.1 has significant architecture changes from previous 7.x versions. 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 8.6.1 documentation is included on the Informatica PowerCenter DVD provided with Oracle Business Intelligence Applications.

• 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 the 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 PowerCenter 8.6.1.

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 PowerCenter Administration Console, 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 "Chapter 22: Understanding Globalization," 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 Informatica PowerCenter Installation 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.4.2.1, "How to Set the NLS_LANG Environment Variable for Oracle Databases" and Section 4.4.2.2, "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. Informatica PowerCenter 8.6.1 documentation is included on the Informatica PowerCenter DVD provided with Oracle BI Applications.

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

• Domain Configuration Database

• 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 as well as rights to create triggers and views on all tables and aliases.

• 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.4.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 Informatica PowerCenter Installation 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 Informatica PowerCenter Installation Guide.

  The Informatica documentation is included on the Informatica PowerCenter DVD provided with Oracle BI Applications.

4.4.2 Install and Configure Database Connectivity Software

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

The machine that hosts the PowerCenter Integration Services 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, see the topic titled, "Integratioin Service Connectivity," in "Chapter 10: Integration Service Architecture," in the 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, see the topic titled, "Database Connect Strings," in the section, "Creating the Repository Service," in "Chapter 7: Creating and Configuring the Repository Service," in the Informatica PowerCenter Administrator Guide.

For additional information about database connectivity for Integration Services and Repository Services, see the topic titled, "Install and Configure Database Client Software," in "Chapter 2: Before You Install," in the Informatica PowerCenter Installation Guide.

The machine or machines that host the DAC Client and DAC Server require connectivity to the Oracle Business Analytics Warehouse (target) database, transactional (source) database(s), and the DAC Repository database. For instructions on configuring connectivity for DAC, see Section 4.9.3, "Installing JDBC Drivers for DAC Database Connectivity" and Section 4.9.4, "Creating ODBC Database Connections for the DAC Client"

Notes

• For Oracle databases, set the NLS_LANG environment variable on the machines where the Oracle client is installed, as described in Section 4.4.2.1, "How to Set the NLS_LANG Environment Variable for Oracle Databases".

• For DB2 databases, set the DB2CODEPAGE environment variable on the machines where the DB2 client is installed, as described in Section 4.4.2.2, "How to Set the DB2CODEPAGE Environment Variable for DB2 Databases".

4.4.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 Informatica PowerCenter Installation Guide.

4.6.1 Installing Informatica PowerCenter Client Tools (Windows)

This section explains how to install Informatica PowerCenter 8.6.1 Client Tools and Hotfix 6 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 8.6.1, refer to the Informatica PowerCenter Installation Guide, and related documentation, which is included on the Informatica PowerCenter DVD provided with Oracle BI Applications. See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hotfixes or emergency bug fixes that may be required to support the current version of Informatica.

To install Informatica PowerCenter Client Tools on Windows

1. Run the program install.exe from the PowerCenter_8.6.1_SE_for_Windows_32bit on the Informatica PowerCenter DVD provided with Oracle BI Applications.

2. Follow the instructions on the Informatica PowerCenter Client 8.6.1 installation wizard.

3. Install Informatica PowerCenter Hotfix 6 from the PowerCenter_8.6.1_HotFix6_for_Windows_32bit folder on the Informatica PowerCenter DVD provided with Oracle BI Applications.

4. Follow the instructions on the Informatica PowerCenter Client 8.6.1 installation wizard.

4.6.2 Installing Informatica PowerCenter Services

This section explains how to install Informatica PowerCenter Services 8.6.1 and Hotfix 6 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 8.6.1, refer to the Informatica PowerCenter Installation Guide and related documentation, which is included on the Informatica PowerCenter DVD provided with Oracle BI Applications. See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hotfixes or emergency bug fixes that may be required to support the current version of Informatica.

To install Informatica PowerCenter Services 8.6.1 and Hotfix 6

1. Start the Informatica PowerCenter Services installer.

   To start the installer on Windows, run the install.bat file from the PowerCenter_8.6.1_SE_for_Windows_<bit mode> folder on the Informatica PowerCenter DVD that is provided with Oracle BI Applications.

   To start the installer on UNIX or Linux, run install.sh at a shell command line. The PowerCenter 8.6.1 installation files are located in the folder PowerCenter_8.6.1_SE_<OS>_<bit mode> on the Informatica PowerCenter DVD provided with Oracle Business Intelligence Applications.

   On UNIX or Linux, select the option to use the GUI mode installation.

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

3. Stop any Informatica services that are running.

4. Install the Informatica PowerCenter Hotfix 6 from the PowerCenter_8.6.1_HotFix6_for_<OS>_<bit mode> folder.

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

4.6.3 Creating the Informatica Repository Service

Follow this procedure to create the Informatica Repository Service.

For detailed information about creating the Repository Service, see the topic titled, "Creating the Repository Service," in "Chapter 7: Creating and Configuring the Repository Service," in the Informatica PowerCenter Administrator Guide, which is included on the Informatica DVD provided with Oracle BI Applications.

To create the Informatica Repository Service

1. Open the Informatica PowerCenter Administration Console.

2. On the left side of the Administration Console, click Create, and then select Create Repository Service.

3. In the Create New Repository Service dialog box, specify the appropriate values for the required properties. For a description of the required properties, see "Creating the Repository Service," in "Chapter 7: Creating and Configuring the Repository Service," in the Informatica PowerCenter Administrator Guide.

   In particular, note the following properties:

   1. For the Code Page, select a code page that is compatible with the code page of the machine on which you installed Informatica PowerCenter. For more information, refer to the Informatica PowerCenter documentation and to Section 4.3.3, "Code Page and Data Movement Requirements."

      Note: The code page of the Repository Service cannot be changed once the Repository Service has been created.

   2. For the Creation Mode, make sure you select Create New Repository Content.

4.6.4 Creating the Informatica Integration Service

Follow this procedure to create the Informatica Integration Service.

For detailed information about creating the Integration Service, see the topic titled, "Creating an Integration Service," in "Chapter 9: Creating and Configuring the Integration Service," in the Informatica PowerCenter Administrator Guide, which is included on the Informatica DVD provided with Oracle BI Applications.

To create the Informatica Integration Service

1. Open the Informatica PowerCenter Administration Console.

2. On the left side of the Administration Console, click Create, and then select Create Integration Service.

3. In the Create New Integration Service dialog box, specify the appropriate values for the required properties.

   For a description of the required properties, see the topic titled, "Creating an Integration Service," in "Chapter 9: Creating and Configuring the Integration Service," in the Informatica PowerCenter Administrator Guide.

4.7.1 Restoring the Prebuilt Informatica Repository for Environments in English

You use the Restore option in Informatica PowerCenter Administration Console to load the prebuilt Oracle_BI_DW_Base repository or Oracle_BI_DW_Teradata repository (in a Teradata environment).

To load the prebuilt 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 the OracleBI\dwrep\Informatica\Repository directory on the machine where the Oracle BI Applications installer was run to the following folder on the machine where Informatica PowerCenter Services has been installed:

   • On Windows copy the file to the \Informatica PowerCenter8.6.1\server\infa_shared\Backup directory.

   • On UNIX or Linux copy the file to $Informatica/PowerCenter8.6.1/server/infa_shared/Backup directory.

2. In Informatica PowerCenter Administration Console, select the Repository Service that was created in the procedure in Section 4.6.3, "Creating the Informatica Repository Service."

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. Choose Actions, 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. Choose Actions > Restore Contents.

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

8. Select the Restore as New check box.

9. Click OK to start the restore process.

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

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

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

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

4.7.2 Restoring the Prebuilt 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 prebuilt Informatica Repository provided with Oracle BI Applications.

To restore the prebuilt 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 prebuilt repository file.

4.8.1 Copying Source Files and Lookup Files

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

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

• The default Informatica directory for source files is <drive>:\Informatica\PowerCenter8.6.1\server\infa_shared\SrcFiles. You can confirm the source file directory for your environment by launching the PowerCenter Administration Console and going to the General Properties area of the Processes tab. The parameter name for the source file directory is $PMSourceFileDir.

  Note: The value of the $PMSourceFileDir parameter must match the DAC system property called InformaticaParameterFileLocation. You will set this property later in the set up process, using the instructions in Section 4.14.1, "Setting DAC System Properties."

• The default Informatica directory for lookup files is <drive>:\Informatica\PowerCenter8.6.1\server\infa_shared\LkpFiles. You can confirm the lookup file directory for your environment by launching the PowerCenter Administration Console and going to the General Properties area of the Processes tab. The parameter name for the source file directory is $PMLookupFileDir

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

1. Copy the source files in \OracleBI\dwrep\Informatica\SrcFiles on the machine where the Oracle BI Applications installer was run to the source files directory on the PowerCenter Services machine, for example \Informatica PowerCenter8.6.1\server\infa_shared\SrcFiles.

2. Copy the lookup files in \OracleBI\dwrep\Informatica\LkpFiles on the machine where the Oracle BI Applications installer was run to the lookup files directory on the PowerCenter Services machine, for example Informatica PowerCenter8.6.1\server\infa_shared\LkpFiles.

To copy source files and lookup files to Informatica PowerCenter Services on UNIX or Linux

1. Copy the source files in \OracleBI\dwrep\Informatica\SrcFiles on the Windows machine where the Oracle BI Applications installer was run to the source files directory on the PowerCenter Services machine, for example $Informatica/PowerCenter8.6.1/server/infa_shared/SrcFiles.

2. Copy the lookup files in \OracleBI\dwrep\Informatica\LkpFiles on the Windows machine where the Oracle BI Applications installer was run to the lookup files directory on the PowerCenter Services machine, for example $Informatica/PowerCenter8.6.1/server/infa_shared/LkpFiles.

4.8.2 Setting PowerCenter Integration Services Relaxed Code Page Validation

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

To configure Informatica PowerCenter Integration Services for relaxed code page validation

1. Log in to Informatica PowerCenter Administration Console.

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.8.3 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 PowerCenter Administration Console, select the Integration Service.

2. Click the Properties tab.

3. In the Custom Properties area, click Edit.

4. Create a custom property by clicking Add to display new Name and Value fields.

5. For each of the custom properties in the table below, click Add and add a new custom property with an appropriate value:

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

     Custom Properties Name	Custom Properties Value	Notes
     ServerPort	<Server Port Number>	For <Server port number>, enter the number of an available port. For example, 4006.This custom property configures Informatica PowerCenter Services to listen on <Server port number>. DAC communicates with the PowerCenter Integration Services service using this port.
     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.

     
     

6. Click OK to save the details.

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

4.8.4 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 "Chapter 4: Managing Users and Groups," in the Informatica PowerCenter Administrator Guide.

To create a Repository Administrator defined in the native security domain

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

2. Navigate to the Security page by clicking the Configure Security icon in the top, right corner of the page.

3. On the Security page, click Create User, or select an existing user defined in the native domain.

   Note: Do not create this user in an LDAP domain or select an existing user from an LDAP domain.

4. On the Privileges tab of the new or existing user, click the appropriate Repository Service.

5. In the Edit Roles and Privileges page, expand the Repository Service, and under System-Defined Roles, select the Administrator role check box.

6. Click OK.

4.9.1 Installing DAC Using the DAC Installer

You can run the DAC installer to install DAC in the following configurations:

• DAC Client and Server on Windows

• DAC Server on Linux

Note:

• The DAC Client can only be installed on Windows.

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

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

• You must install Informatica before you install DAC.

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

• The DAC installer installs DAC in the \orahome\10g\bifoundation\dac directory.

To install DAC using the DAC installer

1. Access the installation files on the Oracle Business Intelligence Data Warehouse Administration Console DVD, and start the DAC installer by double-clicking the setup.exe file.

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

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.bat file, the DAC installer creates an environment variable named INFA_DOMAINS_FILE and sets the value to the directory path of the domans.infa file.

• The DAC installer adds the directory path to Informatica PowerCenter binaries to the PATH environment variable.

4.9.2 Installing the DAC Server on UNIX

Oracle does not provide an installer for DAC on UNIX.

To install a DAC Server on UNIX, you need to copy the \dac folder from the Windows machine on which the DAC Client is installed to a UNIX machine, as described in the procedure below.

When installing a 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.6, "Installing Informatica PowerCenter."

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

To install the DAC Server on a UNIX machine

1. On the Windows machine on which the DAC Client is installed, create a temporary directory (for example, a directory named \OracleBI_UNIX\).

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

2. On the machine where the DAC Client is installed, copy the \dac directory (that is, the directory installed by the DAC installer in \orahome\10g\bifoundation) to the temporary directory (for example, \OracleBI_UNIX\).

3. From the \dac directory in the temporary directory, remove the \export and \icons subfolders.

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

4. Zip up the temporary directory (for example, \OracleBI_UNIX\).

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 directory where you want to install the DAC Server.

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

   Shell scripts are provided in the /dac directory. 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.7, "About the DAC Server Shell Scripts."

8. Copy the contents of the /dac/unix_script_bkp directory into the /dac directory.

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

4.9.3 Installing JDBC Drivers for DAC Database Connectivity

DAC requires JDBC drivers for database connectivity. The JDBC drivers that are used should be for the databases supported. Since JDBC drivers show variations with different database versions, only drivers that are shipped with the database or downloaded from database vendor site and known to be certified for the given database version should be used. Currently, third-party JDBC drivers for the databases are not supported.

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

To install JDBC drivers in the \dac\lib directory

• Oracle

  • If you are using Oracle database 11g, find the directory where Oracle is installed. Copy the file named ojdbc6.jar in the jdbc\lib directory and paste it in the \dac\lib directory.

  • If you are using an Oracle database 9.x or 10.x, find the directory where Oracle is installed. Copy the file named ojdbc14.jar in the jdbc\lib directory and paste it in the \dac\lib directory.

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

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

• DB2

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

  Note:

  If your source or target database is DB2-UDB, you also need to create DB2-UDB stored procedures to be used during the ETL process. For instructions, see Section 4.19.2, "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\lib directory.

  You can use the Microsoft SQL Server 2000 JDBC Driver files if you edit the \conf\connection_templates.xml file and un-comment the section that starts <!-- THIS SECTION IS FOR SQL SERVER 2000. Comment this section while using SQL Server 2005.-->.

  Download the SQL Server JDBC drivers for SQL Server 2000 from the Microsoft web site. Copy the Copy the files msbase.jar, mssqlserver.jar, and msutil.jar to the \dac\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 directory to the \DAC\lib directory. Depending on the Teradata JDBC version, you might not have some of the above files.

4.9.4 Creating ODBC Database Connections for the DAC Client

The DAC Client uses an ODBC connection to create and alter tables in the Oracle Business Analytics Warehouse. Create an ODBC connection to the Oracle Business Analytics Warehouse database on the machine that hosts the DAC Client, as described below.

Additionally, if your source system is Siebel CRM, you must create an ODBC connection to the OLTP (source) database on the machine where you will install Oracle BI Applications software. This ODBC connection will be used by the DDLIMP utility.

For the ODBC connections to Oracle databases, you must use the Oracle Merant ODBC Driver that is installed with the DAC platform installation. For all other databases, you should use ODBC drivers supplied by your database vendor.

Refer to the appropriate instructions for your database type:

• Section 4.9.4.1, "How to Create ODBC Connections for DB2 Databases"

• Section 4.9.4.2, "How to Create ODBC Connections for Oracle Databases"

• Section 4.9.4.3, "How to Create ODBC Connections for SQL Server Databases"

• Section 4.9.4.4, "How to Create ODBC Connections for Teradata Databases"

4.9.5 Installing pmcmd and pmrep 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.

The pmrep program is installed in the PowerCenter Client and PowerCenter Services bin directories (for example, \PowerCenter8.6.1\client\bin and \PowerCenter8.6.1\server\bin). Because of the requirement to co-locate the DAC Client with the PowerCenter Client, the pmrep program is available on the machine for the DAC Client to use.

The pmcmd program is installed in the PowerCenter Services bin directory. In order for DAC to access pmcmd, it must also reside in the PowerCenter Client bin folder on the same machine where the DAC Client is installed. Copying the pmcmd executable file from the PowerCenter Services bin folder to the PowerCenter Client bin folder is a manual process.

To install pmcmd in the PowerCenter Client 8.6.1 bin directory

Do one of the following:

• If PowerCenter Services 8.6.1 has been installed on the same machine as the DAC Client and PowerCenter Client 8.6.1, copy the pmcmd.exe and pmrep.exe from the PowerCenter 8.6.1\server\bin directory to PowerCenter 8.6.1\client\bin directory.

• If Informatica PowerCenter Services 8.6.1 is installed on a Windows machine other than the one that hosts the DAC Client, copy the pmcmd.exe file from the \PowerCenter 8.6.1\server\bin directory on the machine where PowerCenter Services is installed to the \PowerCenter 8.6.1\client\bin directory on the DAC Client machine.

• If Informatica PowerCenter Services is installed on a UNIX or Linux machine, and an installation on Windows is not available, run the Informatica PowerCenter Services installer on the DAC Client machine or (any other Windows machine) to install only Integration Services as follows:

  1. Run the Informatica PowerCenter Services installer as described in Section 4.6.2, "Installing Informatica PowerCenter Services."

  2. Copy the pmcmd.exe file located in PowerCenter 8.6.1\server\bin to PowerCenter 8.6.1\client\bin.

4.11.1 Logging into DAC and Creating a Connection to the DAC Repository

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.

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

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 log into DAC for the first time and upgrade the repository schema

1. Start the DAC Client by navigating to the $ORACLE_HOME\bifoundation\dac directory and double-clicking the startclient.bat file.

   The Login ... dialog box appears.

   
   

2. Click Configure.

3. In the Configuring ... dialog box, 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 select an existing authentication file, do the following:

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

   2. In the Authentication File dialog box, select Choose existing authentication file.

   3. Navigate to the appropriate folder, and select the authentication file. Click OK.

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

   5. 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. To create a new authentication file, do the following:

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

   2. In the Authentication File dialog box, 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 box, 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 box, click Test Connection to confirm the connection works.

   7. Click Apply, and then click Finish.

      Note:

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

7. In the Login... dialog box, do the following:

   1. Select the appropriate Connection from the drop-down list.

   2. Enter Administrator as the User Name.

   3. Enter Administrator as the Password.

   4. Click Login.

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

   This process creates DAC repository tables.

   Depending on your database type, you may have the option specify a tablespace.

   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.11.2 Importing Metadata into the DAC Repository

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

To import metadata into the DAC Repository

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

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

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 for which you will import the ETL metadata, 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.12.1, "Creating Data 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 directory' button to select an empty folder.

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

   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 directory' button to select the directory 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 Batch 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 box.

   
   

7. To confirm that you want to import the seed data selected, re-type the text in the text box 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\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 box.

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

4.12.1 Creating Data Warehouse Tables

Use this procedure to create the data warehouse tables.

If the Oracle Business Analytics Warehouse database is Teradata, follow the steps in Section 4.12.2, "Creating Data Warehouse Tables on a Teradata Database."

To create data warehouse tables

1. In DAC, select Tools, then ETL Management, then Configure.

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

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

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

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

   The Data Warehouse tab is active.

   
   

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

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

   Field	Description
   Database Type	(Read only) Type of database, as specified by the 'Target data warehouse database platform' field on the Sources dialog box (see step 2). If the database type is DB2/390, then check the 390 Database check box.
   Container	The name of the source business applications 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.11.2, "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 box. 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.
   Table Owner	Valid database owner, username, or account that you set up to hold the data warehouse (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components").
   Password	Valid database user password for the database owner, username, or account that you specified in the Table Owner field (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components").
   ODBC Data Source	Data Source Name (DSN) for the Oracle Business Analytics Warehouse. You must specify the name of the ODBC connection (for example, Connect_to_OLAP) that you created for the data warehouse. For more information, see Section 4.9.4, "Creating ODBC Database Connections for the DAC Client".
   Data Area	(Optional) Tablespace where data warehouse tables are created.
   Index Area	(Optional) Indexspace where data warehouse indexes are created (applicable only to Oracle and DB2 databases).
   Is Unicode	Specifies whether the data warehouse database 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."

   
   

6. Click Start.

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

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

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

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

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

4.12.2 Creating Data Warehouse Tables on a Teradata Database

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

To create data warehouse tables on a Teradata database

1. In DAC, select Tools, then ETL Management, then Configure.

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

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

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

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

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

6. Click Start.

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

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

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

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

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

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

4.13.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 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.13.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.13.1.2, "How to Configure the DAC Server Repository Connection Using serverSetupPrompt Scripts (Windows, UNIX or Linux)".

4.13.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.6.2, "How to Set Environment Variables for DAC Server Communication on UNIX."

4.13.3 Starting the DAC Server

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

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

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

4.13.4 Activating Join Indexes for Teradata Databases

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

To activate join indexes for Teradata databases

1. In DAC, create a new Execution Plan with the list of Subject Areas that you want to extract.

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

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

4. Assemble the Execution Plan parameters in the Parameters tab and configure the parameters.

5. Redesign the Execution Plans.

4.14.1 Setting DAC System Properties

This section describes the DAC System Properties to set to ensure proper integration between the DAC Client, the DAC Server and Informatica.

To set DAC System Properties

1. Log in to 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 DAC System Properties.

   
   

3. Set values for the following properties:

   Property	Value Required
   DAC Server Host	Enter the name or IP address of the machine that hosts the DAC Server.
   DAC Server OS	Enter the operating system of the machine that hosts the DAC Server. Possible values are Windows, AIX, Solaris, HP-UX, Linux (case sensitive).
   InformaticaParameterFileLocation	Enter the path to the Informatica source file directory, for example \Informatica\PowerCenter8.6.1\ server\infa_shared\SrcFiles. You can confirm the source file directory for your environment by launching the PowerCenter Administration Console and going to the General Properties area of the Processes tab. The parameter name for the source file directory is $PMSourceFileDir. The value of the $PMSourceFileDir parameter must match the value for the InformaticaParameterFileLocation property.

   
   

Note: For a description of all DAC System Properties, see Section A.3, "How to Set DAC System Properties."

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

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.

To register Informatica Services in DAC

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

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

2. For the Informatica Integration Service that you want to register, do the following:

   1. Modify the record with Name = INFORMATICA_DW_SERVER by entering the following information in the Edit subtab:

      Field	Enter or select
      Name	For Informatica version 7.x, logical name for the Informatica Server. For Informatica version 8.x, logical name for the Integration Service.
      Type	Informatica.
      Service	For Informatica version 7.x, Informatica server host name or IP address. For Informatica version 8.x, name of the Integration Service you created in Section 4.6.4, "Creating the Informatica Integration Service."
      Server Port	For Informatica version 7.x only. The port that DAC uses to communicate with the Informatica Server. The default port number is 4006.
      Domain	For Informatica version 8.x only. Informatica domain name.
      Login	Informatica Repository user name who has 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.8.4, "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, which is included on the Informatica PowerCenter DVD provided with Oracle BI Applications.
      Password	Informatica Repository user password.
      Maximum Sessions	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	Name of the Informatica Repository (for example, Oracle_BI_DW_Base.rep).
      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. For the Informatica Repository Service you want to register, do the following:

   1. Modify the record with Name = INFORMATICA_REP_SERVER by entering the following information in the Edit subtab:

      Field	Enter
      Name	For Informatica version 7.x, logical name for the Repository Server. For Informatica version 8.x, logical name for the Repository Service.
      Type	Repository.
      Hostname	For Informatica version 7.x, Repository Service host name or IP address. For Informatica version 8.x, Repository Serer host name or IP address.
      Server Port	For Informatica version 7.x only. Port where the Repository Service connects for requests. The default port number is 6001.
      Login	Informatica Repository user name that has appropriate privileges to execute workflows (for example, Administrator).
      Password	Informatica Repository user password (for example, Administrator).
      Maximum Sessions	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	Name of the Informatica Repository (for example, Oracle_BI_DW_Base.rep).
      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.14.3 Setting Physical Data Sources

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

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.

3. 
   

   The Physical Data Sources tab displays a precreated record for the data warehouse with the name DataWarehouse, and one or more records for the OLTP sources. 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.11.2, "Importing Metadata into the DAC Repository."

4. For each record, 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	'Source' for the database connection for a transactional (OLTP) database. 'Warehouse' for the database connection for a data warehouse (OLAP) database. Do not change the default values.
   Connection Type	Type of database. Possible values are: Oracle (OCI8) Oracle (Thin) DB2 DB2/390 MSSQL Teradata Flat File
   Instance or TNS Name or Connection String or Database Name (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.
   Table Owner Password	Valid database user password.
   Max Num Connections	Maximum number of database connections this connection pool can contain.
   DB Host	Machine name or instance where the database resides. For example, databasename.us.company.com.
   Port	Port number where the database listens (for example 1521 is the default for an Oracle database).
   Dependency 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.

   
   

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

6. Click Save.

4.15.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 Add, to display the Add Repository dialog box.

3. In the Add Repository dialog box:

   1. In the Repository field, specify Oracle_BI_DW_Base (or the appropriate Oracle BI Applications repository name).

   2. In the Username field, specify the Repository Administrator username (for example, Administrator).

4. Choose Repository, then Connect, to display the Connect to Repository dialog box.

5. In the Connect to Repository dialog box:

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

6. In the Add Domain dialog box:

   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, mymachine@us.company.com).

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

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

7. Click Connect.

4.15.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.15.2.1, "How to Configure Relational Connections."

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

   • For Teradata databases, follow the additional steps in Section 4.15.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.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 Oracle Business Analytics Warehouse from 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 will depend on the modules licensed by the customer. 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.

• 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 the 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 the connection pool (for more information, see Section 4.16.2.1, "How to specify the database type for connection pools").

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 in the \OracleBI\Server\Repository folder.

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

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

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.16.5 Modifying the Oracle BI Repository for Siebel Applications version 6.3 or version 7.5

Before you can enable Siebel Applications version 6.3 or version 7.5, you must modify the Oracle BI Repository.

To modify the Oracle BI Repository for Siebel applications version 6.3 and version 7.5

1. Open the file RepositoryDifferences75_63.xls in the \OracleBI\dwrep folder.

2. Using the Oracle BI Administration Tool, publish the information in the following tabs:

   • ITBlocks

   • LogicalCols

   • FINS Cols

4.18.1 Configuring Email Recipients in DAC

Follow this procedure to configure email recipients.

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

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 box asks you to confirm that you want to configure the DAC Repository connection.

2. Click Yes.

   The Server Configuration dialog box 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.19.1 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.

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 Predeployment 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.2 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 directory \OracleBI\dwrep\siebproc\db2udb\. There is a sub-directory for each platform. For example, stored procedures for the Windows platform are stored in the sub-directory \OracleBI\dwrep\siebproc\db2udb\win32\.

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

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

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

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

4.19.3 Configuring Security-Related Initialization Blocks

You may 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 Business Intelligence Server Administration Guide.

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

8. Select the new container that you created in step 4 from the container drop-down list at the top of the Choose Subject Areas dialog box.

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