Skip Headers
Oracle® Business Intelligence Applications Installation Guide for Informatica PowerCenter Users
Version 7.9.5.1

Part Number E13765-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

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

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 uses an example to explain a typical topology for an Oracle BI Applications deployment. Figure 4-1 shows a typical deployment topology.

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

This diagram is described in surrounding text.

In Figure 4-1, note the following:

High-Level Installation and Configuration Task List

This section contains a high-level task list of the tasks you must complete to install Oracle BI Applications. You must complete these tasks in the order listed below.

  1. Before you start to install Oracle BI Applications and Informatica PowerCenter Services, do the following:

    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 Oracle Business Intelligence Infrastructure Requirements.

      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.

    2. Make sure that you satisfy the Informatica PowerCenter requirements that are specified in Informatica PowerCenter Requirements.

      Also, read the Informatica PowerCenter overview section and plan your Informatica topology before you install Informatica PowerCenter.

    3. Make sure that you satisfy the Code Page requirements that are specified in Code Page and Data Movement Requirements.

    4. Make sure that you perform the mandatory preinstallation tasks that are specified in Preinstallation Tasks. The preinstallation tasks are the following:

      Create Databases for Oracle BI Applications and Informatica PowerCenter Components

      Create the required databases to store the Informatica Repository, DAC Repository, and Oracle Business Analytics Warehouse.

      Install Database Connectivity Software

      Native connectivity client software is required on machines that will host the Informatica PowerCenter Services, the DAC Client, and the DAC Server. The machines that host these components require connectivity to the Oracle Business Analytics Warehouse (target) database, the transactional (source) databases, and the databases for the DAC Repository and Informatica Repository.

      Create ODBC Database Connections

      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.

      Set Environment Variables

      Before you run the Informatica PowerCenter installer, you must set environment variables.

      Download and Install the Java JDK

      Java JDK is required on the machines that host the DAC Client and DAC Server.

      Download Hibernate Libraries

      The DAC Client and DAC Server require libraries from Hibernate, an open source software. Hibernate libraries are not installed as part of Oracle BI Applications 7.9.x. You must download the libraries from the Hibernate Web site.

  2. Install the Oracle BI Applications release 7.9.5.1 software. For more information, see Running the Oracle BI Applications Installer on Windows.

  3. Install Informatica PowerCenter Server and Client Tools version 8.1.1 SP5 software. For more information, see Installing Informatica PowerCenter Services Software.

  4. Restore the prebuilt Oracle BI Repository file. For more information, see Restoring the Prebuilt Informatica Repository.

  5. Configure the Informatica PowerCenter components. For more information, see Configuring Informatica PowerCenter Components.

  6. Install and Configure the DAC Client. For more information, see Configuring the DAC Client.

  7. Import BI Applications metadata into the DAC Repository. For more information, see Importing Metadata into the DAC Repository.

  8. Create the Oracle Business Analytics Warehouse tables. For more information, see Creating the Oracle Business Analytics Warehouse Tables.

  9. Install and configure the DAC Server. For more information, see Installing the DAC Server.

  10. Configure DAC Integration Settings. For more information, see Configuring DAC Integration Settings.

  11. Create Relational Connections in Informatica Workflow Manager, as specified in Configuring Relational Connections in Informatica Workflow Manager.

  12. Configure the SiebelUniCodeDB Custom Property, as specified in Configuring the SiebelUnicodeDB Custom Property.

  13. Perform required post-installation tasks (for more information, see Additional Configuration Tasks).

    For additional and supporting configuration tasks, see Additional Configuration Tasks.

Note:

After you have completed the above steps and completed any customizations that you need to make, you are ready to perform a full load of your OLTP data. For an example of performing a full load of OLTP data, see About Running A Full Load ETL. For detailed information about using DAC to perform ETL processes, see Oracle Business Intelligence Data Warehouse Administration Console Guide.

Mandatory Requirements and Preinstallation Tasks

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

This section contains the following topics:

Oracle Business Intelligence Infrastructure Requirements

Installation of Oracle BI Applications requires that you have already installed Oracle Business Intelligence Enterprise Edition on a Windows machine. Oracle Business Intelligence Enterprise Edition is the infrastructure required to run the Oracle BI Applications installer. 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, refer to the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

Informatica PowerCenter Requirements

This section contains information about the Oracle BI Applications requirements relating to Informatica PowerCenter.

Oracle BI Applications release 7.9.5.1 requires Informatica PowerCenter 8.1.1 SP5. If you license Informatica PowerCenter with Oracle BI Applications, you install Informatica PowerCenter 8.1.1 SP5 from the Oracle BI Applications installation disk. For more information, see Installing Informatica PowerCenter Services Software.

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

Informatica PowerCenter 8.1.1 has significant architecture changes from previous 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.1.1 SP5 documentation is provided in the Third_Party_Software\Informatica folder of the Oracle BI Applications release 7.9.5.1 installation disk.

  • Plan your topology carefully before installing Informatica PowerCenter Services.

You must install Informatica PowerCenter Client Tools on the same machine as the DAC Client and Informatica PowerCenter Services on the same machine as the DAC Server.

Informatica PowerCenter Services runs on 32-bit or 64-bit platforms. For more information see the topic titled 'Working with 32-bit and 64-bit Platforms' in the chapter on PowerCenter Prerequisites in the Informatica PowerCenter Installation and Configuration Guide.

About Changes in Informatica Terminology

This section lists changes in terminology in Informatica PowerCenter 8.1.1 SP5.

Table 4-1 Changes in Informatica Terminology

Term used in 7.1.x versions of Informatica PowerCenter Term used in 8.1.1 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 8.1.1.

The Informatica Repository is managed by the Repository Service.

Informatica Server

Integration Services

Not applicable

Repository Service


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.

    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.

    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.

  • 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 the Informatica documentation on 'Understanding Globalization' in the PowerCenter Administration Guide, particularly if your environment requires 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. 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. The installer uses the locale environment variable to generate the list of compatible code pages. For more information, see the topic titled 'Locale Environment Variables' in the Informatica PowerCenter Installation and Configuration Guide.

    If your environment uses Oracle or DB2 database, you need to set environment variables NLS_LANG or DB2CODEPAGE. For information on how to set these environment variables see How to Set the NLS_LANG Environment Variable for Oracle Databases and 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.1.1 SP5 documentation is provided in the Third_Party_Software\Informatica folder of the Oracle BI Applications release 7.9.5.1 installation disk.

Preinstallation Tasks

This section explains the mandatory tasks that you must perform for an Oracle BI Applications deployment. It contains the following topics:

Create Databases for Oracle BI Applications and Informatica PowerCenter Components

Before you install Oracle BI Applications and Informatica PowerCenter, use your target database tool to create database instances to hold the following:

  • DAC Repository

  • the PowerCenter domain configuration

  • Oracle Business Analytics Warehouse

    Note: Make sure that the Oracle Business Analytics Warehouse instance is granted the SSE_ROLE. For more information, see 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.

For information about supported database platforms, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications. The Informatica and DAC repositories are not supported on DB2/390 or Teradata databases. If your source or Oracle Business Analytics Warehouse database is DB2/390 or your Oracle Business Analytics Warehouse database is Teradata, you need to use a DB2-UDB, MS SQL Server, or Oracle database for both the DAC Repository and the Informatica Repository.

For details on creating the Informatica Repository and PowerCenter domain configuration databases, see the section on creating databases in the Informatica PowerCenter Installation and Configuration Guide. Informatica PowerCenter 8.1.1 SP5 documentation is provided in the Third_Party_Software\Informatica folder of the Oracle BI Applications release 7.9.5.1 installation disk.

How to Create the SSE Role

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

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. For instructions on creating roles, see 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:

    • READ

    • WRITE

    • UPDATE

    • DELETE

    • GRANT

  • If you are using a Teradata database, you do not need to create the SSE role.

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

Install Database Connectivity Software

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

The machines that will host these components require connectivity to the Oracle Business Analytics Warehouse (target) database, transactional (source) database(s), and DAC Repository and Informatica Repository databases.

Notes

  • For instructions on how to install and configure the native database client connectivity software, refer to the Informatica PowerCenter Installation and Configuration Guide:

  • To install and configure on the Windows machine that will host the DAC Client and PowerCenter Tools, see section 'Connecting to Databases from Windows' in the Informatica Installation and Configuration Guide. Also refer to this section if the DAC Server and PowerCenter Services will be installed on Windows.

  • To install and configure on a UNIX or Linux machine that will host the DAC Server and PowerCenter Services, see section 'Connecting to Databases from UNIX' in the Informatica PowerCenter Installation and Configuration Guide.

  • Informatica PowerCenter 8.1.1 SP5 documentation is provided in the Third_Party_Software\Informatica folder of the Oracle BI Applications release 7.9.5.1 installation disk.

  • For Oracle databases, set the NLS_LANG environment variable on the machines where the Oracle client was installed, as described in 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 was installed, as described in How to Set the DB2CODEPAGE Environment Variable for DB2 Databases.

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

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

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

    setenv DB2CODEPAGE <DB2CODEPAGE value>
    

    For example: setenv 1208.

Create ODBC Database Connections

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 Oracle BI Applications installer. For all other databases, you should use ODBC drivers supplied by your database vendor.

Refer to the appropriate instructions for your database type:

Note:

For information about creating database connections for Informatica PowerCenter, refer to the Informatica documentation.
How to Create ODBC Connections for DB2 Databases

Follow these instructions for creating ODBC connections for DB2 databases on Windows. For instructions on creating ODBC connections for DB2 databases on UNIX or Linux, see the documentation provided with your database.

To create ODBC connections for DB2 databases on Windows

  1. Using the DB2 Client Configuration Assistant, create a database connection to the Oracle Business Analytics Warehouse database on the machine that will host the DAC Client. If your source system is Siebel CRM, also create an ODBC connection to the transactional database on the machine where you will run the Oracle BI Applications installer.

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

  2. If necessary, on Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the Oracle Business Analytics Warehouse (and, if your source system is Siebel CRM, to the transactional database) using an ODBC driver.

  3. Test the connections to make sure they work.

How to Create ODBC Connections for Oracle Databases

Follow these instructions for creating ODBC connections for Oracle databases on Windows. For instructions on creating ODBC connections for Oracle databases on UNIX or Linux, see the documentation provided with your database.

Note: You must use the Oracle Merant ODBC driver to create the ODBC connections. The Oracle Merant ODBC driver is installed by the Oracle BI Applications installer. Therefore, you will need to create the ODBC connections after you have run the Oracle BI Applications installer and have installed the DAC Client.

To create ODBC connections for Oracle databases on Windows

  1. On the machine that will host the DAC Client, use the System DSN tab of the ODBC Data Source Administrator to create an ODBC connection to the Oracle Business Analytics Warehouse database using the Oracle Merant ODBC driver that is supplied with Oracle BI Applications.

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

  2. (If your source system is Siebel CRM) In Windows, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the Siebel transactional database using the Oracle Merant Closed driver.

  3. Test the connections to make sure they work.

How to Create ODBC Connections for SQL Server Databases

Follow these instructions for creating ODBC connections for SQL Server databases on Windows. For instructions on creating ODBC connections for SQL Server databases on UNIX or Linux, see the documentation provided with your database.

To create ODBC connections for SQL Server databases on Windows

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

    • An ODBC connection to the Oracle Business Analytics Warehouse database on the machine that will host the DAC Client.

    • (If your source system is Siebel CRM) An ODBC connection to the transactional database on the machine where you will run the Oracle BI Applications installer.

    Note: Select SQL Server as the ODBC driver.

  2. Test the connections to make sure they work.

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

How to Create ODBC Connections for Teradata Databases

Follow these instructions for creating ODBC connections for Teradata databases.

To create ODBC connections for Teradata databases on Windows

  1. On the Windows machine that will host the DAC Client, in the System DSN tab of the ODBC Data Source Administrator, create an ODBC connection to the Oracle Business Analytics Warehouse Teradata database.

  2. Set the following parameters:

    Field=Enter

    DateFormat=AAA

    SessionMode=ANSI

    NoScan=Yes

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

To create ODBC connections for Teradata databases on UNIX

  1. Using the Teradata ODBC driver, create an ODBC connection for the Teradata database.

  2. Set the following variables in the ODBC.INI file:

    • DateFormat=AAA

    • SessionMode=ANSI

    • NoScan=Yes

  3. For UNICODE environments, in the Teradata ODBC Driver Advanced Options dialog box, add the following:

    • CharacterSet=UTF8

Set Environment Variables

Before you run the Informatica PowerCenter installer, you must set environment variables as described in 'Step 5. Configure Environment Variables' in the chapter 'Before You Install' in the Informatica PowerCenter Installation and Configuration Guide. For UNIX machines, you must set environment variables for database client, Library Path and Locale as described. Informatica PowerCenter 8.1.1 SP5 documentation is provided in the Third_party_Software\Informatica folder of the Oracle BI Applications release 7.9.5.1 installation disk.

Download and Install the Java JDK

The DAC Client and DAC Server require Java JDK. Install Java JDK on the machines that will host the DAC Client and DAC Server. On Windows, you must install JDK with no spaces in the directory path.

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

Note:

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

To install Java SDK

  1. Download and install the Java JDK for your operating system(s) from a Web site that provides Java JDK downloads (for example, the Sun or IBM Web site). You must download and install the Java JDK for either 32 bit or 64 bit depending on your operating system.

    Note:

    On Windows, make sure that there are no spaces in the JDK directory path. If there are spaces in the directory path, DAC will not start (the \OracleBI\DAC\startclient.bat file will fail).

Download Hibernate Libraries

To run the DAC Client or DAC Server, you need to have libraries from an open source software product called Hibernate. Hibernate libraries are not installed as part of Oracle BI Applications 7.9.x. You must download the libraries from the Hibernate Web site.

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

To download the Hibernate libraries

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

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

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

The files will unzip into a directory named hibernate-3.2. As part of the installation and set up of DAC described later in this chapter, you will copy the Hibernate libraries that you downloaded to the DAC Client machine.

Running the Oracle BI Applications Installer on Windows

The Oracle BI Applications installer runs on Windows and requires the Oracle Business Intelligence infrastructure to be installed. For more information on Oracle Business Intelligence infrastructure requirements, see Oracle Business Intelligence Infrastructure Requirements.

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

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

Note:

If you have a previous release of Oracle BI Applications installed, you must uninstall this release before you run the installer for Oracle BI Applications release 7.9.5.1. If you do not uninstall the previous version, some release 7.9.5.1 directories will not be correctly installed. (Make a back-up of your Oracle BI Repository, Presentation Catalog, and DAC and dwrep directories before you uninstall the previous release.)

To install Oracle BI Applications on Windows

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

    Note:

    To run the installer in console (or text) mode, run the command setup.exe -console. You do not see the following screens in console installation mode. Instead, you enter input as plain text in the terminal window when prompted.
  2. In the Welcome page, click Next.

  3. Enter or browse for the location for the Oracle Business Intelligence infrastructure (for example, <DRIVE>:\OracleBI\) and for the Oracle Business Intelligence data (for example, <DRIVE>:\OracleBIData\). Click Next.

  4. Select the Oracle BI Applications that you want to install, and then click Next.

    To determine the applications you should install for the Oracle BI Applications licenses you have purchased, see the spreadsheet entitled '2-Installer Options' in the Oracle Business Intelligence Applications Licensing and Packaging Guide. This guide is part of the Oracle Business Intelligence Media Pack.

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

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

    The installer installs the Oracle BI Applications directories and files in the Oracle 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.
  6. Click Finish.

    Tip:

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

When the installation is complete, verify that the following directories or files are installed:

Notes

Installing Informatica PowerCenter Services Software

This section explains how to install Informatica PowerCenter Services and Client. It contains the following topics:

Notes

Installing Informatica PowerCenter Client Tools (Windows)

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

Note: Oracle recommends that you co-locate the PowerCenter Client Tools with the the DAC Client.

Follow this procedure to install the Informatica PowerCenter Client Tools for an Oracle BI Applications deployment.

To install Informatica PowerCenter Client Tools on Windows

  1. Run the program install.exe from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SE_for_Windows_<bit mode> folder on the Oracle BI Applications installation DVD.

    Alternatively, run install.bat from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SE_for_<OS>\Client\Disk1 folder.

  2. At the 'Please select an option to install' dialog box, click Client.

  3. Follow the instructions on the Informatica PowerCenter Client 8.1.1 installation wizard.

  4. Install Informatica PowerCenter SP5 patch from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SP5_for_Windows_<bit mode> folder, as follows:

    • Install Client - accept all default installation settings.

Installing Informatica PowerCenter Services

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

Note the following:

  • Oracle recommends that you co-locate the PowerCenter Services with the DAC Server.

  • During the Informatica PowerCenter Services installation, you must select the 'Create new PowerCenter Repository' option on the PowerCenter Repository Configuration screen.

To install Informatica PowerCenter Services 8.11 SP5

  1. Run the program install.exe from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SE_for_Windows_<bit mode> folder on the Oracle BI Applications installation DVD.

    Alternatively, run install.bat from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SE_for_<OS>\Client\Disk1 folder.

    To start the installer on UNIX or Linux, run install.sh at a shell command line from the DVD root directory or the root directory in the location where you downloaded the installer.

    On UNIX and Linux, the PowerCenter installer checks the operating system and verifies that the environment variables are correctly set. The environment variables must be specifically set to enable the PowerCenter installer to run processes and connect to databases and services. If the environment variables are not set, the PowerCenter installer gives you the option to stop the installation and modify the environment variables.

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

  2. At the 'Please select an option to install' dialog box, click Server.

  3. Follow the instructions on the Informatica PowerCenter Services installation wizard, as shown in the following table.

    Note:

    The installation wizard pages that you see depend on the licensing options that you have purchased. For example, the Application Server Configuration page is only displayed if you have purchased the PowerCenter Data Analyzer option.
    Page Your Action Notes
    Welcome Click Next.  
    License Key Click Browse, then locate and select the license key text file. If you have purchased the 'Informatica OEM PowerCenter ETL Server and PowerConnect Adapters' license from Oracle, use the Oracle_All_OS_Prod.key license key file. This file is located in the DVD root directory of the Informatica PowerCenter DVD that is provided with Oracle BI Applications.
    Installation Prerequisites Click Next.  
    Installation Set Select the Install PowerCenter 8.1.1 radio button. For information about upgrading to Informatica PowerCenter 8.1.1, refer to Informatica PowerCenter Installation and Configuration Guide.
    DA & MM License Key Not applicable.  
    Installation directory Specify the installation folder. On Windows, do not install Informatica in a directory that has a space in the directory name. For example, do not install in D:\Program Files\INFA. If you do install into a directory with a space in the name, you will cause errors in DAC.
    Application Server Configuration Not applicable.  
    Pre-Installation Summary Click Install.  
    Installing... Not applicable.  
    Domain Configuration At the Create or Join Domain page, select the Create New Domain radio button.  
    Domain Configuration At the Configure Domain Database page, specify the database details. Configure Domain Database page:
    Domain Configuration At the Configure Domain page, specify the domain details. Configure Domain page:
    • Informatica PowerCenter creates a default domain called Domain_<install machine name> and a default node called Node01_<install machine name>.

    • Specify a domain password (for example, admin), which you be use to log into PowerCenter Administration Console.

    • Make sure that you select the Create Repository Service check box.

    Node Configuration At the PowerCenter Repository Configuration page, select the Create new PowerCenter repository radio button, and specify the Services details. You must specify the appropriate repository Code Page setting. PowerCenter Repository Configuration page:
    • Specify the database details that you created for the Informatica Repository.

    • Choose a code page that is compatible with the code page of the machine where you install PowerCenter. For more information, refer to the Informatica PowerCenter documentation, and Code Page and Data Movement Requirements.

    Node Configuration At the Configure Integration Service page, select the Create Integration Service check box.

    Select the data character movement mode, either ASCII or Unicode.

    PowerCenter Services Configuration page:
    • The username here is the username for the Informatica Repository (for example, Administrator).

    • The password here is the password for the Informatica Repository (for example, Administrator).

    • Select Unicode if non-ASCII characters are to moved from source database to the Oracle Business Analytics Warehouse. Otherwise, select ASCII. For more information, see Code Page and Data Movement Requirements.

    Authentication Not applicable.  
    Repository Configuration Not applicable.  
    Post-Installation Summary Click Done.  

  4. Stop any Informatica services that are running.

  5. Install the Informatica PowerCenter SP5 patch from the \Third_Party_Software\Informatica\PowerCenter_8.1.1_SP5_<OS>_<bit mode> folder, as follows:

    • Install Server - select the Custom installation mode, and on the PowerCenter Components page, select PowerCenter Services 8.1.1 SP5, and deselect PowerCenter Data Analyzer 8.1.1 SP5 and PowerCenter Metadata Manager 8.1.1 SP5. Accept the other default installation settings.

Restoring the Prebuilt Informatica Repository

An Informatica Repository file called Oracle_BI_DW_Base.rep is installed into the OracleBI\dwrep\Informatica\Repository directory during the Oracle BI Applications installation. For use with deployments where the Oracle Business Analytics Warehouse is on Teradata, a repository file called Oracle_BI_DW_Teradata.rep is installed.

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.1.1\server\infa_shared\Backup directory.

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

  2. In Informatica PowerCenter Administration Console, select the Repository Service that was created during the installation process (for example, Oracle_BI_DW_Base or Oracle_BI_DW_Teradata).

    For information about how to connect to Informatica PowerCenter Administration Console, see How to Log Into Informatica PowerCenter Administration Console.

    This screenshot is described in surrounding text.
  3. In the Properties tab, click Edit in the General properties area.

  4. Make sure that the OperatingMode value is Exclusive.

  5. Click OK.

  6. Choose Actions, then Delete Contents.

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

  8. Choose Actions > Restore Contents.

  9. At the Restore Contents dialog box, select Oracle_BI_DW_Base (or Oracle_BI_DW_Teradata 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.

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

  13. In the Properties tab, click Edit in the General properties area.

  14. Change the OperatingMode value to Normal.

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

Note:

The prebuilt Oracle_BI_DW_Base and Oracle_BI_DW_Teradata repository files are versioned from Oracle BI Applications release 7.9.3 and higher. For more information about the versioned repositories, see Appendix J, "About the Versioned Informatica Repository."

Configuring Informatica PowerCenter Components

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

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 Informatica default directory locations for source and lookup files.

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 \Informatica PowerCenter8.1.1\server\infa_shared\SrcFiles on the Informatica PowerCenter Services machine.

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

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 $Informatica/PowerCenter8.1.1/server/infa_shared/SrcFiles on the Informatica PowerCenter Services machine.

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

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, then Configuration Properties, and then Edit.

  4. Change the value for the property 'ValidateDataCodePages' from 'Yes' to 'No'.

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.

    For example, on the machine where Informatica PowerCenter Services is installed, select Programs, then Informatica PowerCenter 8.1.1, then Services, then Launch Admin Console.

    This screenshot is described in surrounding text.
  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 DB2/390 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.

Installing an Additional Instance of the DAC Client on Windows

The DAC Client is installed by the Oracle BI Applications installer. The DAC Client can only be installed on Windows. So, if you performed the steps in Running the Oracle BI Applications Installer on Windows, you have already installed the DAC Client on the machine where you ran the Oracle BI Applications installer. Note: The DAC Server is also installed by the Oracle BI Applications installer at the same time as the DAC Client.

This section contains instructions for installing an additional instance of the DAC Client on a different machine from the one on which you ran the Oracle BI Applications installer.

Note: Oracle recommends that you install the DAC Client on the Windows machine that runs the Informatica PowerCenter Client tools.

You must satisfy the following requirements on the machine on which you want to install an additional DAC Client:

To install an additional instance of the DAC Client on Windows

  1. Copy the \OracleBI\DAC\ directory and its subfolders from the Oracle BI Applications installation (on Windows) to the target Windows machine (for example, to a directory called \DAC\).

  2. Copy the \OracleBI\dwrep\ directory and its subfolders from the Oracle BI Applications installation (on Windows) to the target Windows machine.

Note: Alternatively, if the target machine already has Oracle Business Intelligence Enterprise Edition installed, you can run the Oracle BI Applications installer on the target machine to install the DAC Client. For information about how to run the Oracle BI Applications installer, see Running the Oracle BI Applications Installer on Windows.

Configuring the DAC Client

This section contains instructions for configuring the DAC Client.

Note: You do not need Oracle Business Intelligence Enterprise Edition installed on a machine to run a DAC Client.

This section contains the following topics:

Copying Hibernate Files to the DAC Directories

Follow these instructions to copy Hibernate files to the appropriate DAC directories.

  • Copy the Hibernate files from the \hibernate-3.2 directory where you downloaded and unzipped the Hibernate files to the \DAC directory on the DAC Client machine, as described in the table below.

    Table 4-3 Hibernate Library Files You Need to Copy

    Files Copy from Copy to

    *.jar

    \hibernate-3.2\lib

    \DAC\lib

    hibernate3.jar

    \hibernate-3.2

    \DAC\lib

    hibernate-configuration-3.0.dtd

    \hibernate-3.2\src\org\hibernate

    \DAC

    hibernate-mapping-3.0.dtd

    \hibernate-3.2\src\org\hibernate

    \DAC


Installing JDBC Drivers for DAC Database Connectivity

You must install the appropriate JDBC driver in the DAC\lib directory on the machines where the DAC Client is installed to enable DAC database connectivity.

To install JDBC drivers in the \DAC\lib directory

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

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

      set ORACLELIB=%DAC_HOME%\lib\classes12.zip
      
  • DB2. If you are using a DB2 database, find the directory where DB2 is installed. In the Java 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 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.

Configuring the DAC Config.bat File

Follow these instructions to configure the DAC config.bat file.

To configure the DAC config.bat file

  1. Navigate to the \DAC folder.

  2. Open the config. bat file.

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

    Make sure there are no spaces in the path reference.

    For example:

    set JAVA_HOME=d:\jdk1.5
    
  4. Edit the DAC_HOME variable to point to the directory where you installed DAC.

    For example:

    set DAC_HOME=d:\OracleBI\DAC
    

Enabling DAC Client Communication with Informatica PowerCenter

The DAC Client uses the Informatica pmrep and pmcmd command line programs when communicating with Informatica PowerCenter. The DAC Client uses pmrep to synchronize DAC tasks with Informatica workflows and to keep the DAC task source and target tables information up to date.

This section includes the following topics:

How to Install Informatica pmcmd and pmrep Command Line Programs

The pmrep program is installed in the Informatica PowerCenter Client and Informatica PowerCenter Services bin directories. 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 the DAC to access pmcmd, it must also reside in the PowerCenter Client 8.1.1 SP5 bin directory on the same machine where the DAC Client is installed.

To install pmcmd in the PowerCenter Client 8.1.1 SP5 bin directory on the DAC Client machine

Do one of the following:

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

  • If Informatica PowerCenter Services 8.1.1 SP5 is installed on a Windows machine other than the one that hosts the DAC Client, copy the pmcmd.exe file from the \PowerCenter 8.1.1\server\bin directory on the machine where PowerCenter Services is installed to the \PowerCenter 8.1.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 Installing Informatica PowerCenter Services.

    2. In the Domain Configuration section, on the Create or Join Domain page, select the Create New Domain radio button

    3. At the Configure Domain Database page, specify the database details for the domain configuration database.

      You may use the same database that stores the domain configuration for your PowerCenter Services installation on UNIX. However, you will have to create a different database account.

    4. At the Configure Domain page, specify the domain details.

      Note: Do not check the Create Repository Service check box.

    5. Complete the installation.

    6. Apply Service Pack 4.

    7. Copy the pmcmd.exe file located in PowerCenter 8.1.1\server\bin to PowerCenter 8.1.1\client\bin.

How to Define the Informatica Domains File Path in the DAC Client Environment Variables

In order for the DAC Client to be able to use the pmrep and pmcmd programs, you need to define the path of the Informatica Domain file 'domains.infa' in the environment variables on the DAC Client machine.

To define the Informatica Domains File path in the DAC Client environment variables

  1. Locate the file domains.infa in the root Informatica PowerCenter installation directory and note down the directory path of this file.

    For example, D:\Informatica\PowerCenter8.1.1.

  2. Create an environment variable called INFA_DOMAINS_FILE with the value set to the directory path to the domans.infa file, as follows:

    • On Windows, display the Windows Environment Variables dialog box (that is, from the Windows Control Panel, select System, then Advanced, then Environment Variables), and create a System variable with the following values:

      • Variable name: INFA_DOMAINS_FILE

      • Variable value: <directory path of domains file>\domains.infa

        The path should include the name of the file. For example, 'D:\Informatica\PowerCenter8.1.1\domains.infa'.

  3. Add the directory path to Informatica PowerCenter binaries to the PATH environment variable as follows:

    In the Windows System Properties > Environment Variables dialog box, add the path of the Informatica \Client\bin directory to the PATH environment variable. For example: <drive>:\Informatica\PowerCenter8.1.1\client\bin.

How to Verify the DAC Client Is Able to Use pmrep and pmcmd

From a Windows command prompt, execute pmrep and then pmcmd. The test is successful if you see the programs are invoked and the pmrep and pmcmd prompts appear.

If pmrep and pmcmd are not recognized, then:

  • Ensure Service Pack 4 has been applied to Informatica PowerCenter 8.1.1.

  • Verify that the INFA_DOMAINS_FILE variable points to the domains.infa file located in the Informatica directory.

  • Verify that the PATH variable includes the path to the Informatica binaries (\Informatica\PowerCenter\client\bin), and that pmrep.exe and pmcmd.exe exist in the \bin folder.

Logging into DAC for the First Time and Creating the DAC Repository

When you log into DAC for the first time, you create a set of stored login information. Also, 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.

This section contains the following topics:

Logging into DAC

This section provides instructions for logging into DAC by creating a set of stored information.

Note: Before you start this procedure, you need to have already created a database for the DAC Repository. For more information, see Create Databases for Oracle BI Applications and Informatica PowerCenter Components.

To log into DAC

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

    This image is an example of the populated screen.

    Note:

    If you installed the DAC Client by running the Oracle BI Applications installer, you can also start the DAC Client from the Start menu > Oracle Business Intelligence > Oracle DAC > DAC Client.
  2. In the Login... dialog box, select Configure.

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

  4. Enter the appropriate connection details as specified in the table below.

    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 is stored.
    Connection String, or Database name, or TNS Name, or Instance Specify 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 machine name of the machine where the DAC Repository database resides.
    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.

  5. To test the connection, select Test Connection and enter the Table owner name and password for the DAC Repository.

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

  7. Select the new connection from the Connection drop-down list, specify a table owner name and password for the DAC Repository, and click Login.

The DAC Repository that you connect to does not contain the DAC Repository tables. In the following section ( Creating the DAC Repository Schema), you will create the DAC Repository schema.

Creating the DAC Repository Schema

Follow these instructions to create the DAC Repository schema.

When you log into DAC and connect to a DAC Repository for the first time, DAC detects that the DAC schema does not exist in the database, and you are asked whether you want to create a repository.

To create the DAC Repository schema

  • After logging into DAC for the first time, when you are asked whether you want to create a repository, click Yes.

This screenshot is described in surrounding text.

For a repository on Oracle or DB2 database, you have the option to specify a tablespace.

The Unicode check box is available for a repository on MS SQL Server or DB2 database. Check the Unicode check box if your deployment requires a Unicode schema to be created. To determine if your deployment requires a Unicode schema for the DAC Repository, see Code Page and Data Movement Requirements.

Importing Metadata into the DAC Repository

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

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

    This image is an example of the populated screen.

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

  2. Select the Logical check box, and the System check box.

  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.

    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 Creating Data Warehouse Tables).
  4. Select the Categories options as follows:

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

  5. Click OK to display the Importing tables dialog box.

    This image is an example of the populated screen.
  6. To confirm that you want to import the seed data selected, re-type the text in the text box and click Yes.

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

    When the process is complete, the DAC displays a status box containing a success or failure message. If the process fails, use the \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.
  8. Click OK.

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

Creating the Oracle Business Analytics Warehouse Tables

This section explains how to create tables in the Oracle Business Analytics Warehouse database.

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

The Oracle Business Analytics Warehouse tables are created by the DAC Client. The DAC Client uses ODBC connections to the Oracle Business Analytics Warehouse database for this procedure. Ensure that you have created an ODBC connection to the Oracle Business Analytics Warehouse database as described in Create ODBC Database Connections.

If your Oracle Business Analytics Warehouse database is Oracle, you must use the Oracle Merant ODBC driver to create the ODBC connection. This driver is installed by the Oracle BI Applications. The DAC Client must be installed by running the Oracle BI Applications installer so that the driver is installed on the machine. Create the ODBC DSN to the Oracle Business Analytics Warehouse as described in Create ODBC Database Connections.

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 How to Create the SSE Role).

If your Oracle Business Analytics Warehouse database is not Teradata, refer to Creating Data Warehouse Tables to create the data warehouse tables.

If you are using a Teradata database as the Oracle Business Analytics database, the DAC creates a SQL file to create the schema tables, not the tables themselves. Follow the steps in Creating Data Warehouse Tables on a Teradata Database.

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

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 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 How to Log into DAC.

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

    This image is an example of the populated screen.
  3. Click OK to display the Data Warehouse Configuration Wizard.

    This image is an example of the populated screen.
  4. Select the Create Data Warehouse Tables check box, and click Next.

    The Data Warehouse tab is active.

    This image is an example of the populated screen.
  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 Importing Metadata into the DAC Repository).

    • Any copies of those source system applications.

    • Any additions made those source system applications.

    • Any new source system applications that you have custom built.

    For example, you might do the following:

    • Import the seed data for Oracle 11.5.9 and 11.5.10

    • Make a copy of Oracle 11.5.10 and call it 'My Oracle 11.5.10'.

    • Add two data warehouse tables to 'My Oracle 11.5.10'

    • Create a new source system application called 'My new source' and add 10 data warehouse tables to 'my New Source'.

    If you leave the Container field blank, DAC will create the following containers:

    • Oracle 11.5.9

    • Oracle 11.5.10

    • My Oracle 11.5.10

    • My new Source

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

    Table Owner Valid database owner, username, or account that you set up to hold the data warehouse (for more information, see 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 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 Create ODBC Database Connections.

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

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 Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse).

Note:

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

To create data warehouse tables on a Teradata database

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

    For more information about logging into DAC, see 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. Click the 'Generate create statements for Data Warehouse Tables' check box, then click Next to display the Data Warehouse SQL tab.

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

  6. Click Start.

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

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

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

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

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

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

Installing the DAC Server

This section contains the following topics:

Installing an Additional Instance of the DAC Server on Windows

The DAC Server is installed by the Oracle BI Applications installer on Windows. So, if you performed the steps in Running the Oracle BI Applications Installer on Windows, you have already installed the DAC Server on the machine where you ran the Oracle BI Applications installer. This section provides instructions for installing an additional instance of the DAC Server on a machine different from the machine on which you ran the Oracle BI Applications installer.

Note: You do not need Oracle Business Intelligence Enterprise Edition installed on a machine to run the DAC Server. However, if you want to use the Oracle BI Applications installer to install the DAC Server on a Windows machine, you do need a full installation of Oracle Business Intelligence Enterprise Edition.

When installing a DAC Server, note the following prerequisites:

  • Make sure that JDK 1.5 or higher is installed. For more information, see Download and Install the Java JDK.

  • Make sure that Informatica PowerCenter Services is co-located on the machine that will host the DAC Server. For more information, see Installing Informatica PowerCenter Services Software.

    The domain should be set properly and the server port set as a custom property.

  • Make sure that database connectivity software has been installed for connection to source(s), to the Oracle Business Analytics Warehouse, and to the Informatica Repository/DAC Repository databases. For more information, see Install Database Connectivity Software.

To install an additional instance of the DAC Server

Do one of the following:

  • If the target machine already has an Oracle Business Intelligence Enterprise Edition installed, you can run the Oracle BI Applications installer on the target machine to install the DAC Server. For information about how to run the Oracle BI Applications installer, see Running the Oracle BI Applications Installer on Windows.

  • (Recommended) You can install a DAC Server by copying across the \DAC\ directory from the Windows machine on which you ran the Oracle BI Applications installer, as follows:

    1. Copy the \OracleBI\DAC\ directory from the DAC Client machine to the target machine.

      Note: The \DAC directory name on the target machine should not contain spaces.

      Note:

      If you have copied the DAC folder from the DAC Client machine and the DAC Client has been configured to use the Hibernate libraries, you do not have to install the Hibernate libraries again. Otherwise, install Hibernate files following instructions in described in Copying Hibernate Files to the DAC Directories.
    2. Copy the appropriate jdbc driver(s) to the \DAC\lib folder on the target machine. Follow instructions in described in Installing JDBC Drivers for DAC Database Connectivity.

    3. Configure DAC config.bat file, as follows:

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

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

        Make sure there are no spaces in the path reference.

        For example:

        set JAVA_HOME=d:\jdk1.5
        
    4. Edit the DAC_HOME variable to point to the directory where you installed the DAC.

      For example:

      set DAC_HOME=d:\OracleBI\DAC
      

Installing the DAC Server on UNIX

Oracle does not provide an installer for Oracle BI Applications on UNIX or Linux. Therefore, to install a DAC Server on UNIX or Linux, you copy the \DAC folder from the Windows machine on which the DAC Client is installed to a UNIX or Linux machine, as described below.

When installing a DAC Server, note the following prerequisites:

  • Make sure that JDK 1.5 or higher is installed. For more information, see Download and Install the Java JDK.

  • On UNIX or Linux, make sure that a zip and unzip utility is installed.

  • Make sure that Informatica PowerCenter Services is co-located on the machine that will host the DAC Server. For more information, see Installing Informatica PowerCenter Services Software.

    The domain should be set properly and the server port set as a custom property.

  • Make sure that database connectivity software has been installed for connection to source(s), to the Oracle Business Analytics Warehouse, and to the Informatica Repository/DAC Repository databases. For more information, see Install Database Connectivity Software.

To copy over the DAC Server to a UNIX or Linux 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. Copy the \DAC\ directory to the temporary directory (for example, \OracleBI_UNIX\).

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

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

  5. Copy the ZIP file to the target UNIX or Linux machine.

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

  6. On the target UNIX or Linux 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 or Linux 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 About the DAC Server Scripts for Bash and C Shells.

  8. On the UNIX or Linux machine copy the appropriate jdbc driver(s) to the /DAC/lib directory as described below. You must copy all jdbc drivers for the database platforms that host source(s), Oracle Business Analytics Warehouse, and DAC Repository databases:

    • Oracle. If you are using an Oracle database (other than 8.x), locate the directory where the Oracle client is installed on the machine. Copy the file named ojdbc14.jar in the jdbc/lib directory and paste it in the $DAC/lib folder, as described below.

      Note: Do not copy the ojdbc14.jar file from a different machine.

      If you are using Oracle 8.x, copy the file named classes12.zip and paste it in the $DAC/lib folder. Also, edit the ORACLELIB setting in the config.sh or config.csh file as follows:

      setenv ORACLELIB=$DAC_Home/lib/classes12.zip

      The config.sh and config.csh files are located in the /DAC/ directory.

    • DB2. If you are using a DB2 database, find the directory where the DB2 client is installed on the machine. In the Java sub-directory, copy the file named db2java.zip and paste it in the $DAC/lib folder.

      Note: Do not copy the db2java.zip file from a different machine.

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

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

      You can use the Microsoft SQL Server 2000 JDBC Driver files if you edit the /DAC/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 he 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.

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

  9. If required, edit the config.sh or config.csh file located in the /DAC directory to point to the correct version of the Java JDK by setting the JAVA_HOME environment variable.

Configuring the DAC Server

This section contains instruction for configuring the DAC Server. It contains the following topics:

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 or serverSetupPrompt.csh script to configure the connection between the DAC Server and the DAC Repository, as follows:

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

  2. Click Yes to display the Server Configuration dialog box.

    This image is an example of the populated screen.
  3. In the Repository Connection Information tab, enter the appropriate information, as described in the table below.

    Tip:

    If the DAC Server is running on the same machine as the DAC Client, click Populate from preconfigured client connection to populate the fields with connection details from the DAC Client.
    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 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, mymachinename@host.com).

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

    - When you created a DAC connection (for more information, see Logging into DAC).

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

  5. Click Save.

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

Use the serverSetupPrompt.sh or serverSetupPrompt.csh file 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 About the DAC Server Scripts for Bash and C Shells.

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

    • On UNIX or Linux, run serverSetupPrompt.sh or serverSetupPrompt.csh located in the /DAC directory.

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

Enabling DAC Server Communication with Informatica PowerCenter

The DAC Server uses the following command line programs to communicate with Informatica PowerCenter:

  • pmrep is used to communicate with PowerCenter Repository Services.

  • pmcmd is used to communicate with PowerCenter Integration Services.

The DAC Server uses the pmcmd program to run the Informatica workflows using PowerCenter Integration Services. The pmrep and pmcmd programs are installed during the PowerCenter Services installation in the bin folder of the server directory. Oracle recommends that you co-locate the PowerCenter Services with the DAC Server so that the pmrep and pmcmd programs are available on the machine for the DAC Server to use. To enable the DAC Server to communicate with Informatica PowerCenter Services using the pmrep and pmcmd programs, you need to define the path of the Informatica Domain file 'domains.infa', and set appropriate environment variables as follows:

How to Set Environment Variables on Windows

To set the environment variables on Windows:

  1. Locate the file domains.infa in the root Informatica PowerCenter installation directory and note down the directory path of this file.

    For example, D:\Informatica\PowerCenter8.1.1.

  2. Create an environment variable called INFA_DOMAINS_FILE with the value set to the directory path to the domans.infa file, as follows:

    • On Windows, display the Windows Environment Variables dialog box (that is, from the Windows Control Panel, select System, then Advanced, then Environment Variables), and create a System variable with the following values:

      • Variable name: INFA_DOMAINS_FILE

      • Variable value: <<directory path of domain file>\domains.infa>

        The Variable value should include the domains.infa file name. For example, D:\Informatica\PowerCenter8.1.1\domains.infa.

  3. Add the directory path to Informatica PowerCenter binaries to the PATH environment variable as follows:

    In the Windows System Properties > Environment Variables dialog box, add the path of the Informatica \server\bin directory to the PATH environment variable. For example: <drive>:\Informatica\PowerCenter8.1.1\server\bin.

How to Verify the DAC Server Is Able to Use pmrep and pmcmd

From a Windows command prompt, execute pmrep and then pmcmd. The test is successful if the pmrep and pmcmd prompts appear.

If pmrep and pmcmd are not recognized, then:

  • Ensure Service Pack 4 has been applied to Informatica PowerCenter 8.1.1.

  • Verify that the INFA_DOMAINS_FILE variable points to the domains.infa file located in the Informatica directory.

  • Verify that the PATH variable includes the path to the Informatica binaries (\Informatica\PowerCenter\server\bin).

How to Set Environment Variables on UNIX or Linux

Use the dac_env.sh or dac_env.csh file to set the appropriate environment variables on a UNIX or Linux machine. For more information on the DAC Server scripts, see About the DAC Server Scripts for Bash and C Shells.

To set environment variables on a UNIX or Linux machine:

  1. Open the dac_env.sh file or dac_env.csh file for editing.

    The files are located in the /DAC directory.

  2. Locate the OS section for the operating system on which your DAC Server is installed.

  3. Uncomment out the line to set the library path environment variable, and replace %DAC_PMCMD_PATH% with the path to the PowerCenter Services binaries.

    For example, using the dac_env.sh file, if the DAC Server is on Solaris or Linux, in the SOLARIS\LINUX section, change the following line from:

    #export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:%DAC_PMCMD_PATH%
    

    To:

    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/Informatica/PowerCenter8.1.1/server/bin
    
  4. In the Common section for Informatica Configuration, set the environment variables for PATH, PM_HOME, INFA_DOMAINS_FILE as follows:

    1. Replace %INFORMATICA_SERVER_LOCATION% with the path to Informatica PowerCenter Services installation.

    2. Replace %DAC_PMCMD_PATH% with the path to Informatica PowerCenter Services binaries.

    3. Replace %DOMAINS.INFA_FILE_LOCATION% with the path to the domains.infa file.

      Include the name of the file. For example, /home/Informatica/PowerCenter8.1.1/domains.infa.

    For example, using the dac_env.sh file, change the following lines in the common section from:

    export PATH=${PATH}:%DAC_PMCMD_PATH%:%INFORMATICA_SERVER_LOCATION%:.
    export PM_HOME=%INFORMATICA_SERVER_LOCATION%
    export INFA_DOMAINS_FILE=%DOMAINS.INFA_FILE_LOCATION%
    

    To:

    export PATH=${PATH}:/home/Informatica/PowerCenter8.1.1/server/bin
    export PM_HOME=/home/Informatica/PowerCenter8.1.1/server
    export PM_HOME=/home/Informatica/PowerCenter8.1.1/server
    export INFA_DOMAINS_FILE= /home/Informatica/PowerCenter8.1.1/domains.infa
    #export LANG=C (uncomment if required)
    #export LC_ALL=C (uncomment if required)
    
  5. If required, edit the dac_env.sh file and change the source command from:

    source ./dac_env.sh
    

    To:

    . ./dac_env.sh
    

How to Verify the DAC Server on UNIX or Linux Is Able to Use pmrep and pmcmd

Invoke config.sh or config.csh to set environment. For example, . ./config.sh. Then, invoke pmcmd.

Make sure that invoking pmcmd starts the pmcmd shell. If you get a 'command not found' error, then the location of the PowerCenter Services is not properly added to PATH in dac_env.sh or dac_env.csh. Review all environment variable settings to ensure they are correctly set.

Then, invoke pmrep, and make sure that invoking pmrep starts the pmrep shell. If you get a 'command not found' error, then the location of the PowerCenter Services is not properly added to PATH in dac_env.sh or dac_env.csh. Review all environment variable settings to ensure they are correctly set.

On some shells, export commands in dac_env.sh and config.sh might not work correctly. In this case, try breaking the commands in two. For example, from:

export JAVA_HOME=/opt/java1.5

To:

JAVA_HOME=/opt/java1.5
export JAVA_HOME

How to Verify Java JDK Availability and Version

To verify that the DAC Server uses the correct Java JDK:

  1. Invoke config.sh or config.csh to set environment.

    For example:

    . ./config.sh
    
  2. Verify Java availability and version by typing the following command:

    $JAVA –version
    

    The Java version is returned.

    If you receive a 'command not found' error message, or the Java version is lower than 1.5, then the JAVA_HOME parameter in config.sh or config.csh is pointing to a non-existent or incorrect Java JDK location.

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 either startserver.sh or startserver.csh.

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

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 are interested for the ETL.

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

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

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

  5. Redesign the execution plans.

Configuring DAC Integration Settings

This section explains how to configure DAC integration settings. It contains the following topics:

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 How to Start and Stop the DAC Server.

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 How to Log into DAC.

  2. From the Views menu, select Setup, then DAC System Properties tab.

    This image is an example of the populated screen.
  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.11\ server\infa_shared\SrcFiles.

Note: For a list of DAC System Properties and their descriptions, see How to Set DAC System Properties.

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

  • You must register one Repository Service in DAC.

Tip:

For details of the PowerCenter Services that you need to register, log into the Informatica PowerCenter Administration Console. For more information, see How to Log Into Informatica PowerCenter Administration Console.

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 How to Log into DAC.

    This screenshot is described in surrounding text.
  2. From the Views menu, select Setup, then Informatica Servers.

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

    1. Register the Informatica Integration Services service by modifying the record with Name = Oracle_BI_DW_Server. To modify the record, enter the following information in the Edit subtab:

      Field Enter
      Name Logical name for the Integration Service (for example, Integration_Service).
      Type Select Informatica.
      Server Hostname Integration Service host name or IP address. For example, myhost.company.com.
      Server Port The port that DAC uses to communicate with the Integration Services service (for example, 4006).

      Specify the port number that you configured for the Integration Services service to listen on when setting the ServerPort custom property in Setting PowerCenter Integration Services Custom Properties.

      Login Informatica Repository user name who has appropriate privileges to execute workflows (for example, Administrator).
      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 Enter the name of the Oracle BI Applications repository that you restored in Restoring the Prebuilt Informatica Repository. For example, Oracle_BI_DW_Base (or Oracle_BI_DW_Teradata for Teradata installations).
      Inactive Indicates whether the Informatica PowerCenter Integration Services service is active or inactive.

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

      Note: Integration Services must be running (for more information about starting Integration Services, see How to Start and Stop Informatica Services.

    3. Click Save to save the details.

  4. For the Informatica Repository Service you want to register, do the following:

    1. Register the Informatica Repository Service by modifying the record with Name = Informatica_REP_Server. To modify the record, enter the following information in the Edit subtab:

      Field Enter
      Name Logical name for the Repository Service (for example, Repository_Service).
      Type Select Repository.
      Server Hostname Repository Service host name or IP address.
      Server Port 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 Oracle BI Applications repository that you restored in Restoring the Prebuilt Informatica Repository.
      Inactive Indicates whether the Informatica PowerCenter Integration Services service is active or inactive.

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

      Note: Integration Services must be running. For more information about starting Integration Services, see How to Start and Stop Informatica Services.

    3. Click Save to save the details.

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

  6. Click Save to save the details.

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 How to Log into DAC.

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

  3. This screenshot is described in surrounding text.

    The Physical Data Sources tab displays a precreated record for the data warehouse with 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 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 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

    Select the Type based on your database.

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

Configuring Relational Connections in Informatica Workflow Manager

This section explains how to log into Informatica PowerCenter Workflow Manager and define relational connections. It contains the following topics:

Logging Into Informatica PowerCenter Workflow Manager

Note: Before you log into Workflow Manager, start the Informatica services. For more information, see How to Start and Stop 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.

Configuring Relational Connections in Informatica PowerCenter Workflow Manager

You need to use Informatica PowerCenter Workflow Manager to configure the relational connections, as follows:

Note: The Informatica services must be running to perform these tasks. To start the Informatica services, see How to Start and Stop Informatica Services.

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 that you need to create, do the following:

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

    2. Click New to display the Connection Object Definition dialog box, and enter the details.

      This image is an example of the populated screen.
    3. Click OK to save the details.

    Notes:

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

      • Click New and select the type as Oracle, DB2, 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 Setting Physical Data Sources.

      • User Name: Database user name.

      • Password: Database user password.

      • Connect string: Connect string for 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 Code Page and Data Movement Requirements.

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

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

      • Name: DataWarehouse, 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 Setting Physical Data Sources.

      • User Name: Database user name.

      • Password: Database password.

      • Connect string: Connect string for the connecting to 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.

  3. Click Close to close the Relational Connection Browser.

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

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

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

  4. Repeat these steps for the following objects:

    • Teradata_Tpump_Update

    • Teradata_Tpump_Insert

    • Teradata_Tpump_Delete

    • Teradata_FastLoad

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

    For example:

    • fastload -c UTF8

    • tpump -c UTF8

  6. Click Close to close the Loader Connection Browser.

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

  3. Display the Mapping tab.

    This image is an example of 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_Upset" 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 box.

    This image is an example of the populated screen.
  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.

Configuring the SiebelUnicodeDB Custom Property

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 Code Page and Data Movement Requirements.

To create and set the SiebelUnicodeDB custom property on Integration Services

  1. Log into Informatica PowerCenter Administration Console. For information on how to log into PowerCenter Administration Console, see How to Log Into Informatica PowerCenter Administration Console.

  2. Select the Integration Service.

  3. In the Properties tab, scroll down to the Custom Properties area, and click Edit.

  4. In the Name field, enter the following:

    SiebelUnicodeDB

  5. 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, oltp@db204007.host.com olap@db204008.host.com.

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 Configuring Relational Connections in Informatica PowerCenter Workflow Manager.

Setting Up DAC to Receive Email Notification

This section describes how to set up DAC to receive email notification. It includes the following topics:

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

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

Additional Configuration Tasks

This section provides configuration steps that may be required depending on your specific environment. All the sections may not be applicable for your environment.

For mandatory, Siebel-specific configuration tasks, see Siebel-Specific Configuration Tasks.

For mandatory, Teradata-specific configurations tasks, see Teradata-Specific Configuration Tasks.

This section contains the following topics:

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 How to Log into DAC.

  2. Make sure that you have selected the correct container from the containers drop down list. You cannot edit out-of-the box containers. Make a copy of an existing container in order to make edits.

  3. Display the Source System Parameters tab.

    This screenshot is described in surrounding text.

    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.

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 How to Create DB2 Stored Procedures).

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 How to Create DB2 Stored Procedures).

How to Create DB2 Stored Procedures

This section describes how to create DB2 stored procedures.

To create DB2 stored procedures

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

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

    Note:

    For more information about the location of DB2 stored procedures, see 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\ directory to \siebproc\.

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

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

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:

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

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

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

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

  2. Respond to the prompts as follows:

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

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

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

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

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

    For information about importing localization data into the W_LOCALIZEDS-STRING_G table, see Upgrading Oracle Business Intelligence Seed Data for Non-English Locales.

  5. Import the localization data into the W_LOCALIZED_STRING_G table.

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:

How to Run the DDLIMP Tool From The Command Line

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

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

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

NOTE: Where <ODBC_DSN> is the ODBC connection created as described in section Create ODBC Database Connections.

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

Additionally you can use the following commands:

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

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

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

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

/Y Storage File for DB2/390.

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:

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

    Note:

    If you are applying schema changes to a Siebel CRM 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 <DRIVE>:\OracleBI\dwrep\DDL_OLTP.CTL /L <DRIVE>:\OracleBI\dwrep\DDL_OLTP.log
    

    Notes:

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

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

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

    • In addition, you can use the following commands:

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

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

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

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

      /Y - Storage File for DB2/390.

  2. Restart all servers.

How to Apply CRM Schema Changes for DB2 on OS/390 and z/OS
  1. Edit the following parameters in the Storage control files located in \OracleBI\dwrep\Storeage_DDL_OLTP.CTL:

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

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

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

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

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

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

    Notes:

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

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

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

    • In addition, you can use the following commands:

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

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

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

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

      /Y - Storage File for DB2/390.

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

    \OracleBI\Dwrep\Create_OLTP_Db2390_index.sql

How to Verify the Siebel CRM Schema Changes

After applying Siebel CRM schema changes, you need to verify that appropriate tables were created in the transactional database.

To verify Siebel CRM schema changes

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

    • S_ETL_R_IMG_1 through S_ETL_R_IMG_166

    • S_ETL_I_IMG_1 through S_ETL_D_IMG_166

    • S_ETL_D_IMG_1 through S_ETL_D_IMG_166

    • S_ETL_PARAM

    • S_ETL_PRD_ATTR

    • S_ETL_PRD_REL

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

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:

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

  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 box, in the File Properties area, click Advanced.

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

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 directory and open the HOSTS file.

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

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

    For example:

    172.20.176.208 tdatsvr tdatsvrCOP1
    

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

  3. Save the HOSTS file.

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

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.

Teradata-Specific Installation Checklist

Table 4-4 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-4 Teradata-Specific Installation Checklist for Windows

Action Link to Topic

Review Teradata-specific database guidelines.

Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse.

Create or drop data warehouse tables.

Creating Data Warehouse Tables on a Teradata Database.

Create or restore the Informatica Repository using the Informatica Repository Manager. You cannot create or restore the Informatica Repository using DAC.

Restoring the Prebuilt 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.

Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse.

Set the Code Page File Property for Unicode Environments.

Setting Up the Code Page File Property for Unicode Environments on Teradata Databases

Set up the HOSTS file.

Setting Up the HOSTS File for Teradata Installations on Windows


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

For an example of using DAC to run a full load ETL, see An Example of Running a Full Load ETL.

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 How to Log into DAC. For more information about the source system container functionality in DAC, see Oracle Data Warehouse Administration Console Guide.

  2. Select the Create as a Copy of Existing Container radio button, and select the appropriate container (for example, Oracle 11.5.10) 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.