Oracle® Business Intelligence Applications Installation Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19038-01 |
|
|
PDF · Mobi · ePub |
This section explains how to install and set up the Oracle BI Applications components. It contains the following main topics:
Section 4.6, "Installing and Setting Up Informatica PowerCenter"
Section 4.8, "Logging into DAC for the First Time and Importing Metadata into the DAC Repository"
Section 4.9, "Creating the Oracle Business Analytics Warehouse Tables"
Section 4.12, "Configuring Relational and Application Connections in Informatica Workflow Manager"
Section 4.13, "Configuring the SiebelUnicodeDB Custom Property"
Section 4.14, "Setting Up DAC to Receive Email Notification"
Section 4.15, "Configuring the Oracle BI Repository Connections"
Section 4.16, "Deploying the Metadata Repository and Presentation Catalog"
Section 4.17, "Applying the Oracle BI Applications Security Policy to the BI Domain"
For information about supported Windows versions, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
Oracle BI Applications and Informatica PowerCenter can be deployed flexibly across a wide range of topologies on different platforms and combinations of platforms.
This section describes a typical topology for an Oracle BI Applications deployment, as illustrated in Figure 4-1.
In Figure 4-1, note the following:
Installation
Machine A (Windows-only)
Machine A is a machine that has installed Oracle Business Intelligence Enterprise Edition, on which you run the Oracle BI Applications installer to install the Oracle BI Applications files.
Note: The instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version of Oracle Business Intelligence Enterprise Edition that you will use to deploy dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files onto a machine.
After the Oracle BI Applications files have been installed on Machine A, the DAC Client is installed on Machine B, and the DAC Server is installed on Machine C.
In addition, the following files are copied from the installation machine (Machine A) to the Business Intelligence Deployment Tier (Machine Group F) as follows:
- The OracleBIAnalyticsApps.rpd file is copied from Machine A to the machine that runs the BI Server in Machine Group F.
For more information about copying the RPD file to the BI Server, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog.".
- The Presentation Catalog files (in EnterpriseBusinessAnalytics.zip) are copied from Machine A to the machine that runs the Presentation Catalog in Machine Group F.
For more information about deploying the Presentation Catalog, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog.".
ETL Tier (Functional)
Machine B (Windows-only)
Runs the DAC Client and Informatica PowerCenter Client Tools.
Machine C (Windows, UNIX, Linux)
Runs the DAC Server and Informatica PowerCenter Services.
Machine D (Windows, UNIX, Linux)
Hosts the transactional (OLTP) database.
Machine E (Windows, UNIX, Linux)
Hosts the Oracle Business Analytics Warehouse database.
BI Deployment Tier (Functional)
The BI Deployment tier is used to deploy the business intelligence dashboards.
Machine Group F (Windows, UNIX, Linux)
Machine Group F is a group of machines that runs the Oracle Business Intelligence Enterprise Edition components. For example, one machine might run the BI Server and another machine might run the BI Presentation Services.
For more information about copying the RPD file and Presentation Catalog Services to appropriate Oracle BI EE machines, see Step 3 in Section 4.2, "High-Level Installation and Setup Task List".
The table below defines the Oracle Home locations for the components in an Oracle BI Applications deployment.
Table 4-1 Summary of Home Locations
Oracle Home Directory | Description |
---|---|
Location of the DAC Server installation (for example, \orahome\10gR3_1\bifoundation\). |
|
Location of the Oracle BI Domain (for example,\MW_HOME\user_projects\domains\bifoundation_domain. |
|
User-specified location of the Informatica installation directory on the machine that hosts Informatica PowerCenter Services (for example,\informatica\9.0.1\). |
|
User-specified location of the Oracle Fusion Middleware home (for example,\FM1_11g\). |
|
Location of the Oracle BI EE Infrastructure home (for example, MW_HOME\Oracle_BI1\). Note: The Oracle BI Applications installer installs into ORACLE_HOME\biapps. |
|
Location of the Oracle Instance home (for example, MW_HOME\instances\instance1). |
This section contains a high-level task list of the tasks you must complete to install and set up Oracle BI Applications.
Note: The steps in the following list form Step 2 in Section 2.4, "Roadmap to Installing and Configuring Oracle BI Applications".
You must complete these tasks in the order listed below.
Before you start, do the following:
Follow the appropriate preinstallation steps in Chapter 3, "Preinstallation and Deployment Requirements for Oracle BI Applications."
On the target installation machine, make sure that you have a valid Oracle Middleware Home (MW_HOME) that contains a valid Oracle BI EE Infrastructure Home (ORACLE_HOME). For more information about Home locations, see Section 4.1.1, "Summary of Oracle Home Locations Used in this Guide.".
You must make sure that:
- The BI EE system is installed and configured using the Simple or Enterprise installation type from the Oracle BI EE installation.
- The WebLogic Administration Server and NodeManager needs to be running. The Oracle BI Applications installer validations check that the WebLogic Administration Server and NodeManager are running. If these two processes are not running, then the Oracle BI Applications installation will not proceed.
Make sure that you satisfy the Informatica PowerCenter requirements that are specified in Section 4.3.2, "Informatica PowerCenter Requirements."
Make sure that you satisfy the Code Page requirements that are specified in Section 4.3.3, "Code Page and Data Movement Requirements."
Make sure that you perform the mandatory preinstallation tasks that are specified in Section 4.4, "Preinstallation Tasks." The preinstallation tasks are the following:
- Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"
- Section 4.4.2, "Install and Configure Database Connectivity Software"
- Section 4.4.3, "Perform Prerequisites for Informatica PowerCenter Installation"
Run the Oracle BI Applications 7.9.6.3 installer on a Windows machine that contains a valid Oracle BI EE Infrastructure Home (ORACLE_HOME). For more information, see Section 4.5, "Installing the Oracle BI Applications files."
Make sure that a complete installation of Oracle Business Intelligence Enterprise Edition is installed on the machine on which you want to install Oracle BI Applications. For more information, see Section 4.3.1, "Oracle Business Intelligence Infrastructure Requirements for the Oracle BI Applications Installer."
Note: This instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version that you will use to deploy dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files on a machine.
Install Informatica PowerCenter Services and Client Version 9.0.1 Hotfix 2 software and set up the Informatica components for use with Oracle BI Applications. For more information, see Section 4.6, "Installing and Setting Up Informatica PowerCenter."
Note:
Informatica PowerCenter Client must be co-located with DAC Client. Informatica PowerCenter Server must be co-located with DAC Server.Note:
See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hot fixes or emergency bug fixes that may be required to support the current version of Informatica.Install Informatica PowerCenter Client as described in Section 4.6.1, "Installing Informatica PowerCenter Client Tools (Windows)."
Install Informatica PowerCenter Services as described in Section 4.6.2, "Installing Informatica PowerCenter Services."
During the installation, you are prompted to create an Informatica Domain using a suitable database account. For information about creating a suitable database account, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".
Create the Informatica Repository Service as described in Section 4.6.3, "Creating the Informatica Repository Service."
Create the Informatica Integration Service as described in Section 4.6.4, "Creating the Informatica Integration Service."
Restore the Pre-built Informatica Repository as described in Section 4.6.5, "Restoring the Pre-built Informatica Repository."
Configure the Informatica PowerCenter components as described in Section 4.6.6, "Configuring Informatica PowerCenter Components."
Restore the pre-built Informatica Repository file provided with Oracle BI Applications. This file includes ETL repository objects, such as mappings, sessions, and workflows. For more information, see Section 4.6.5, "Restoring the Pre-built Informatica Repository."
Install the DAC Client and Server Platform. The DAC Platform is installed by the Oracle Data Warehouse Administration Console (DAC) installer. For more information, see Section 4.7, "Installing and Setting Up the DAC Platform."
Note:
Informatica PowerCenter Client must be co-located with DAC Client. Informatica PowerCenter Server must be co-located with DAC Server.(On Windows) Install DAC as described in Section 4.7.1, "Installing DAC Using the DAC Installer."
(On UNIX) Install DAC as described in Section 4.7.2, "Installing the DAC Server on UNIX."
Install JDBC Drivers for DAC as described in Section 4.7.3, "Installing JDBC Drivers for DAC Database Connectivity."
Create ODBC Connections for the DAC Client as described in Section 4.7.4, "Creating ODBC Database Connections for the DAC Client."
Install Informatica pmcmd and pmrep as described in Section 4.7.5, "Installing pmcmd and pmrep to Enable Communication Between Informatica PowerCenter and DAC."
Install DAC Metadata files as described in Section 4.7.6, "Installing DAC Metadata Files."
Log into DAC and Import Metadata into the DAC Repository. For more information, see Section 4.8, "Logging into DAC for the First Time and Importing Metadata into the DAC Repository."
Create the Oracle Business Analytics Warehouse tables. For more information, see Section 4.9, "Creating the Oracle Business Analytics Warehouse Tables."
Configure the DAC Server. For more information, see Section 4.10, "Configuring the DAC Server."
Configure DAC Integration Settings. For more information, see Section 4.11, "Configuring DAC Integration Settings."
Create Relational Connections in Informatica Workflow Manager, as specified in Section 4.12, "Configuring Relational and Application Connections in Informatica Workflow Manager."
Configure the Oracle BI Repository connections, as specified in Section 4.15, "Configuring the Oracle BI Repository Connections."
Copy the Oracle BI Applications RPD and Presentation Catalog to a suitable Oracle BI EE Machine and deploy them. For more information, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog."
Configure the SiebelUniCodeDB Custom Property, as specified in Section 4.13, "Configuring the SiebelUnicodeDB Custom Property."
Set up DAC to receive email notification. For more information, see Section 4.14, "Setting Up DAC to Receive Email Notification."
If your deployed BI EE system with Oracle BI Applications is different from the BI EE system used to install Oracle BI Applications, then you must apply the security policy to the BI Domain on the deployed BI EE system by following the steps in Section 4.17, "Applying the Oracle BI Applications Security Policy to the BI Domain.".
If your deployed BI EE system with Oracle BI Applications is the same as the BI EE system used to install Oracle BI Applications, then the Oracle BI Applications installer performs this configuration automatically.
Perform required post-installation tasks that are specific to your deployment, as follows.
For more information, see Section 4.18, "Additional Configuration Tasks."
Notes:
After you have completed the steps listed above, and completed all required configuration steps and customizations as described in Oracle Business Intelligence Applications Configuration Guide, you are ready to perform a full load of your OLTP data. For an example of performing a full load of OLTP data, see Section 4.19, "About Running A Full Load ETL." For detailed information about using DAC to perform ETL processes, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
After installing Oracle Business Intelligence Applications, you need to use Fusion Middleware Control to set the appropriate Presentation Services settings. For information about extending and scaling a deployment, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
This section includes mandatory requirements that you must satisfy before you can deploy Oracle BI Applications.
This section contains the following topics:
The Oracle BI Applications installer runs on Windows and requires an Oracle Business Intelligence infrastructure (or ORACLE_HOME) to be installed in an Oracle Fusion Middleware home directory (or MW_HOME). For more information about Oracle home locations, see Section 4.1.1, "Summary of Oracle Home Locations Used in this Guide".
Note: This instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version that you will use to deploy reports and dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files on a machine. The functional version of BI EE can be on any supported OS for BI EE (that is, it is not limited to Windows).
To determine the minimum version of Oracle Business Intelligence Enterprise Edition that is supported for this release of Oracle BI Applications, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
Oracle BI Applications release 7.9.6.3 requires Informatica PowerCenter V9.0.1 with Hotfix 2. If you license Informatica PowerCenter with Oracle BI Applications, you access Informatica PowerCenter software in the BI Media Pack on eDelivery.
If you license Informatica PowerCenter separately and you do not have Informatica PowerCenter V9.0.1 and Hotfix 2, you must upgrade your Informatica PowerCenter license to V9.0.1 and Hotfix 2 before deploying Oracle BI Applications.
Note: See System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hot fixes or emergency bug fixes that may be required to support the current version of Informatica.
Before installing Oracle BI Applications, Oracle recommends that you do the following:
Read the Informatica PowerCenter documentation to familiarize yourself with the new architecture, components, and features.
Plan your topology carefully before installing Informatica PowerCenter Services.
Also, note the following about Informatica and DAC requirements:
Informatica PowerCenter Client Tools and the DAC Client must be co-located on the same machine.
PowerCenter Services and the DAC Server must be co-located on the same machine.
Informatica PowerCenter Services runs on 32-bit or 64-bit platforms. For more information about these two platforms, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
About Changes in Informatica Terminology
Review this section if you are upgrading from an Informatica 7.x version to Informatica PowerCenter V9.0.1.
Table 4-2 Changes in Informatica Terminology
Term used in 7.1.x versions of Informatica PowerCenter | Term used in 9.x version of Informatica PowerCenter |
---|---|
Informatica Repository |
Informatica Repository The Informatica Repository is managed by the Repository Service. |
Informatica Repository Server |
Not applicable. This component does not exist in Informatica PowerCenter 9.0.1. The Informatica Repository is managed by the Repository Service. |
Informatica Server |
Integration Services |
Not applicable |
Repository Service |
The Oracle Business Analytics Warehouse can be deployed in various code page environments and supports global deployments. Data movement in the following source database and data warehouse configuration modes are supported:
Unicode to Unicode
Code page (multi- or single-byte) to Unicode
Code page to code page (where the code pages are the same)
Oracle BI Applications uses Informatica PowerCenter to perform extract, transform and load routines to move data from source database(s) to the Oracle Business Analytics Warehouse.
During the installation and configuration procedures described in this chapter, you will make various settings to enable accurate data movement. Use the guidelines and references noted below to determine values for these settings that are appropriate for your environment:
Determining the source to target configuration mode. Consult your database administrator to determine the code page your source OLTP database uses. Based on the type of data that will be moved from one or more source databases to the Oracle Business Analytics Warehouse, determine what code page you will need to use for the Oracle Business Analytics Warehouse database. Consider future requirements for storing data when determining what code page to use for the Oracle Business Analytics Warehouse.
For accurate data movement from source database to target, the code page of the Oracle Business Analytics Warehouse (target) must be a superset of the code page of the source database. Informatica considers a code page to be a superset of another code page when the code page contains all the characters encoded in the other code page and additional characters not encoded in the other code page.
Note: To enable data movement from source(s) to the Oracle Business Analytics Warehouse, you will set relaxed Code Page Validation for the Integration Services. You must ensure that the target code page is a superset of the source code page for accurate data movement.
Setting the SiebelUnicodeDB property. If your source to target configuration mode for data movement is Unicode to Unicode, you will set a custom property called SiebelUnicodeDB on the Integration Services. Configuration modes of code page to Unicode or code page to code page do not require this property to be set.
Determining the Data Movement Mode. Before you deploy Oracle BI Applications, you must determine what data movement mode to use (ASCII or Unicode) for the PowerCenter Integration Service. The Character Data Movement Mode is an Informatica PowerCenter Integration Service option that you choose based on whether you want to move single-byte or multi-byte data.
Choose Unicode if non-ASCII characters have to be moved. Otherwise, choose ASCII. The Data Movement Mode option is configurable and can be reset after installation.
To set the Data Movement Mode, log into Informatica Administrator, select the Integration Service, then click the Properties tab, then display the General Properties tab, and set the DataMovementMode value.
Determining code pages for Informatica PowerCenter components. In addition to source and target code pages, Informatica PowerCenter uses code pages for PowerCenter Client, the Integration Service, the Informatica Repository, and PowerCenter command line programs (pmcmd and pmrep, which are used by DAC to communicate with PowerCenter Services).
Carefully review the section "Understanding Globalization" (Chapter 22 in the book version) in the Informatica PowerCenter Administrator Guide, particularly if your environment requires the Data Mode Movement mode to be set to UNICODE. The section discusses code page compatibility and code page requirements for the Informatica components.
Setting environment variables. You must manually set the appropriate environment variables for UNIX environments. In addition, the Informatica installer requires the appropriate locale to be set on UNIX machines. Use LANG, LC_CTYPE or LC_ALL to set the UNIX code page. For more information, see the topic titled "Configuring Environment Variables," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.
If your environment uses Oracle or DB2 database, you need to set the environment variables NLS_LANG or DB2CODEPAGE. For information on how to set these environment variables see Section 4.4.2.1, "How to Set the NLS_LANG Environment Variable for Oracle Databases" and Section 4.4.2.2, "How to Set the DB2CODEPAGE Environment Variable for DB2 Databases."
Configuring Relational Connections. When you configure relational connections in the Workflow Manager, choose a code page that is compatible with the code page of the database client. If you set a database environment variable to specify the language for the database, ensure the code page for the connection is compatible with the language set for the variable. For example, if you set the NLS_LANG environment variable for an Oracle database, ensure that the code page of the Oracle connection is identical to the value set in the NLS_LANG variable.
For more information about data movement modes, refer to the Informatica PowerCenter documentation.
This section explains the mandatory preinstallation tasks that you must perform for an Oracle BI Applications deployment. It contains the following topics:
Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"
Section 4.4.2, "Install and Configure Database Connectivity Software"
Section 4.4.3, "Perform Prerequisites for Informatica PowerCenter Installation"
Before you install Oracle BI Applications, the Data Warehouse Administration Console (DAC), and Informatica PowerCenter, use your target database tool to create database instances to hold the following:
DAC Repository
Informatica Domain
Informatica Repository
Oracle Business Analytics Warehouse
Note the following points:
The transactional (OLTP) database user that is registered in DAC should be the database table owner. Alternatively, at a minimum, the user registered in DAC must have read privileges on the transactional database for all tables and aliases.
For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases.
Note:
Delete triggers are only used with Siebel CRM databases.For efficient ETL, DAC and Informatica components utilize multiple connections to the Oracle Business Analytics Warehouse. The Oracle Business Analytics Warehouse database must allow for a minimum of 100 connections to be made by DAC and Informatica. In addition, ensure that these connections are not allowed to time out. Consult with your network administrator and DBA for information on how to ensure these requirements.
Make sure that the Oracle Business Analytics Warehouse instance is granted the SSE_ROLE. For more information, see Section 4.4.1.1, "How to Create the SSE Role."
You can store the DAC Repository and the Oracle Business Analytics Warehouse in separate databases or in the same database.
The DAC and Informatica repositories are not supported on all database platforms. For information about which database platforms are supported, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
For database requirements for the Informatica PowerCenter components, see the topic titled, "Verifying Prerequisites," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.
You must create the Informatica PowerCenter domain configuration database before you run the PowerCenter installer. For more information see the topic titled, "Creating the Domain Configuration Database," in "Chapter 2: Before You Install," in the PowerCenter Installation and Configuration Guide.
Follow this procedure to create the SSE role for the Oracle Business Analytics Warehouse database.
Note: The transactional (OLTP) database user that is registered in DAC should be the database table owner. Alternatively, at a minimum, the user registered in DAC must have read privileges on the transactional database for all tables and aliases.
For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases.
Note:
Delete triggers are only used with Siebel CRM databases.To create the SSE role
Create a database role named SSE_ROLE (SSEROLE for DB2/390 databases). Assign this role to the database user that you are using for the target database. For example, in an Oracle database, you might use the following commands to create the role, grant DBA privileges to the new role, and assign the SSE_ROLE role to a database user named OLAP:
CREATE ROLE SSE_ROLE; GRANT DBA TO SSE_ROLE; GRANT SSE_ROLE TO olap;
For instructions on creating roles, refer to the documentation provided with your database.
Note the following:
For an Oracle database, when you create the SSE_ROLE role, you need to grant the following privileges:
CONNECT
RESOURCE
For an Oracle database, if the DAC Repository and the Informatica Repository are stored in a different database from the data warehouse database, the SSE_ROLE must have the following additional privileges:
SELECT
INSERT
UPDATE
DELETE
GRANT
If you are using a Teradata database, you do not need to create the SSE role.
If you are using a DB2/390 database, you can use the alternative SSE role name SSEROLE if required. To specify a different role name, modify the 'set GRANTEE="SSEROLE"' line in the ORACLE_HOME\biapps\dwrep\createwtables.bat file.
You must install and configure the appropriate database connectivity software on the machines that host the Informatica PowerCenter Services, DAC Server, and DAC Client.
The machine that hosts the PowerCenter Integration Service requires connectivity to the Oracle Business Analytics Warehouse (target) database and transactional (source) database(s). For information about installing and configuring native connectivity software for Integration Services, refer to Informatica PowerCenter Administrator Guide. Also see System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information on certified database connectivity software used by Integration Services to connect to the Oracle Business Analytics Warehouse.
The machine that hosts the PowerCenter Repository Service requires native connectivity to communicate with the Informatica Repository database. For information about installing and configuring the required software, refer to the section "PowerCenter Repository Service" in Informatica PowerCenter Administrator Guide.
For additional information about database connectivity for Integration Services and Repository Services, refer to PowerCenter Installation and Configuration Guide.
The machine or machines that host the DAC Client and DAC Server require connectivity to the Oracle Business Analytics Warehouse (target) database, transactional (source) database(s), and the DAC Repository database. For instructions on configuring connectivity for DAC, see Section 4.7.3, "Installing JDBC Drivers for DAC Database Connectivity" and Section 4.7.4, "Creating ODBC Database Connections for the DAC Client"
For Oracle databases, set the NLS_LANG environment variable on the machines where the Oracle client is installed, as described in Section 4.4.2.1, "How to Set the NLS_LANG Environment Variable for Oracle Databases".
For DB2 databases, set the DB2CODEPAGE environment variable on the machines where the DB2 client is installed, as described in Section 4.4.2.2, "How to Set the DB2CODEPAGE Environment Variable for DB2 Databases".
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
Determine the NLS_LANG value.
In the data warehouse database, run the following command:
SELECT * FROM V$NLS_PARAMETERS
Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, enter NLS_LANG
.
In the Variable Value field, enter the NLS_LANG value that was returned in Step 1.
The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8
.
Note:
The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.Setting NLS_LANG correctly allows proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion 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
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.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
Determine the DB2CODEPAGE value.
Connect to the Source database, using the following command:
SELECT CODEPAGE FROM SYSCAT.DATATYPES WHERE TYPENAME = 'VARCHAR'
Make a note of the result.
For example: 1208
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, DB2CODEPAGE
.
In the Variable Value field, enter the value that was returned in Step 1.
For UNIX and Linux, set the variable as shown below:
setenv DB2CODEPAGE <DB2CODEPAGE value>
For example: setenv 1208
.
Before you run the Informatica PowerCenter installer, you must perform the prerequisites that are described in the chapter, "Before You Install," in the PowerCenter Installation and Configuration Guide.
This section explains how to install the Oracle BI Applications files using the Oracle BI Applications Installer.
When you run Oracle BI Applications installer, the Oracle BI Applications files are installed into the ORACLE_HOME in a directory named biapps. For more information about Home locations, see Section 4.1.1, "Summary of Oracle Home Locations Used in this Guide."
Note:
To launch the Oracle BI Applications installer in non-English platforms, use this command:%JAVA_HOME%\bin\java.exe -cp <PATH_TO_7.9.6.3_BI_APPS_INSTALLER>\setup.jar -Dtemp.dir="%TEMP%" -Dis.jvm.home="%JAVA_HOME%" -Dis.jvm.temp="1" -Dis.external.home="<PATH_TO_7.9.6.3_BI_APPS_INSTALLER>" -Xms64m -Xmx128m run
To install Oracle BI Applications on Windows
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.Click Next to display the Specify Installation Location screen.
Use this screen to specify the details of the Oracle BI EE installation on the local drive. Note: Oracle BI EE must be installed on a local drive; it cannot be a Windows mapped drive that maps to a separate machine.
At the Enter the BI Oracle Home Location field, specify the Oracle BI EE Infrastructure home location (or ORACLE_HOME) on the local drive. For example, C:\ORACLEBIEE11G\Oracle_BI1\.
At the Enter the BI Instance Location field, specify the Oracle BI EE instance home location (or ORACLE_INSTANCE) on the local drive. For example, C:\ORACLEBIEE11G\instances\instance1.
At the Enter the Domain Home Location field, specify the BI domain home location on the local drive. For example, C:\ORACLEBIEE11G\user_projects\domains\bifoundation_domain.
For more information about Home locations, see Section 4.1.1, "Summary of Oracle Home Locations Used in this Guide."
Click Next to display the WebLogic Administration Server Details screen.
Use this screen to specify the details of the WebLogic Administration Server.
At the Enter the port number field, specify the WebLogic Server port number. For example, the default port number is 7001.
At the Enter the user name field, specify the WebLogic administrator account name. For example, the default account name is weblogic.
At the Enter the password field, specify the WebLogic administrator account password. This password is user-specified when you run the Oracle BI EE installer.
Click Next to display the Select the Oracle Business Intelligence Applications... screen.
Select the Oracle BI Applications that you want to install.
To determine the applications you should install for the Oracle BI Applications licenses you have purchased, see the Oracle Business Intelligence Applications Licensing and Packaging Guide. This guide is part of the Oracle Business Intelligence Media Pack.
Note: Once you have installed applications, you can deploy them for one or more source systems selectively when you create the DAC Repository in a later step. For more information, see Section 4.8.2, "Importing Metadata into the DAC Repository."
Click Next to display the summary screen.
At the summary screen, 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.Click Finish.
Tip:
Look in the ORACLE_HOME\biapps\version_apps.txt file in the to check that you have installed the correct version of Oracle BI Applications.When the installation is complete, you will see the following directories and files:
Table 4-3 Verifying installation files
File Location | File Name |
---|---|
ORACLE_HOME\biapps\repository |
OracleBIAnalyticsApps.rpd. You must copy this file to the \OracleBIServerComponent\ directory on a Oracle BI EE machine. For more information, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog.". Note: The EnterprisebusinessAnalytics.rpd file is the master file containing metadata for all applications. The OracleBIAnalyticsApps.rpd file contains only metadata for applications that you selecting during installation (on the 'Select the Oracle Business Intelligence Applications 7.9.6.3 you would like to install' screen). |
ORACLE_HOME\biapps\catalog |
The Presentation Catalog is provided as a zipped file named EnterpriseBusinessAnalytics.zip, which you must unzip before it can be used. For more information about unzipping the Presentation Catalog, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog.". |
ORACLE_HOME\biapps\upgrade |
RPD and MAP files. Repository files from previous versions of Oracle BI Application and Siebel Analytics Application releases. These repositories are used to upgrade from previous Oracle BI Applications versions. |
ORACLE_HOME\biapps\dwrep |
Sub-directories. Data Warehouse repository files and scripts. |
For information on configuring the Oracle BI Repository, see the Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.
Refer to the Oracle Business Intelligence Enterprise Edition documentation for more information on working with the Oracle BI Repository and Presentation Catalog.
This section explains how to install and set up Informatica PowerCenter, and contains the following topics:
Section 4.6.1, "Installing Informatica PowerCenter Client Tools (Windows)"
Section 4.6.2, "Installing Informatica PowerCenter Services"
Section 4.6.3, "Creating the Informatica Repository Service"
Section 4.6.4, "Creating the Informatica Integration Service"
Section 4.6.5, "Restoring the Pre-built Informatica Repository"
Section 4.6.6, "Configuring Informatica PowerCenter Components"
For information about additional hot fixes or emergency bug fixes that may be required to support Informatica PowerCenter V9.0.1 HF2, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
The Informatica PowerCenter installation files are available on eDelivery in the BI Media pack.
If you are deploying Oracle BI Applications with JD Edwards EnterpriseOne or JD Edwards World source systems, select the appropriate operating system on which to install Informatica PowerCenter as follows:
If you are a JDE World customer, or a JDE E1 customer on DB2 for IBMi that upgraded from JDE World and never changed the database structure, then you must run Informatica on a Windows OS platform and connect to the DB2 on IBMi source database with ODBC.
If you are an E1 customer on DB2 for IBMi that created the database with the E1 (constructed with SQL), then you may run Informatica on any LUW OS platform and use either ODBC (Windows) or DB2 Connect (Unix/Aix/Linux) to connect to the DB2 on IBMi source database.
In this guide, INFA_HOME refers to the user-specified Informatica installation directory on the machine that hosts Informatica PowerCenter Services. For example, if you have installed Informatica PowerCenter Services into a directory named \informatica\9.0.1\, then INFA_HOME is \informatica\9.0.1\. For more information about Home locations, see Section 4.1.1, "Summary of Oracle Home Locations Used in this Guide."
You need to determine the machines that will host Informatica PowerCenter Client Tools and Informatica PowerCenter Services. The PowerCenter Services can be installed on UNIX or on Windows. The PowerCenter Client Tools must be installed on Windows.
You must co-locate the DAC Client with the Informatica PowerCenter Client Tools.
You must co-locate the DAC Server with Informatica PowerCenter Services.
DAC produces parameter files that are used by Informatica. If an execution plan fails in DAC and you want to debug the workflow by running it directly from Informatica, then the parameter file produced by DAC should be visible to Informatica. This is one reason for the requirement to co-locate the DAC and Informatica components as stated above.
You must meet all requirements and complete preinstallation tasks as described in Section 4.3, "Mandatory Requirements" and in the chapter 'Before You Install' of the PowerCenter Installation and Configuration Guide.
When you install PowerCenter Services and Client Tools version 9.0.1 and Hotfix 2, use the Informatica PowerCenter installation documentation in conjunction with the steps provided in the following sections. This guide summarizes the steps and assumes a single-machine installation for the PowerCenter Services components.
Note:
If you have licensed Informatica PowerCenter separately from Oracle BI Applications, skip this section and go to section Section 4.6.5, "Restoring the Pre-built Informatica Repository."Make sure that you have installed the required version of Informatica PowerCenter (for more information, see Section 4.3.2, "Informatica PowerCenter Requirements").
If you are using a Windows Vista operating system, note the following:
The DAC Client uses the Informatica pmrep and pmcmd command line programs when communicating with Informatica PowerCenter. The installation of PowerCenter Client Tools provides the pmrep executable file. The installation of PowerCenter Services provides the pmcmd executable file. The DAC installer performs post-install configuration tasks related to the pmrep and pmcmd executable files. Therefore, you must run the run the PowerCenter Services installer on the Windows Vista machine where you will install the DAC Client. This installation of Informatica PowerCenter Services will be a non-functional installation because Informatica does not support PowerCenter Services on Windows Vista.
When installing PowerCenter Services on Windows Vista, you might receive an error message similar to the following. You can ignore any such error messages.
Use the error below and catalina.out and node.log in the server/tomcat/logs directory on the current machine to get more information. EXITCODE: S" Select Retry to continue the installation.
This section explains how to install Informatica PowerCenter Client Tools 9.0.1 Hotfix 2 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 9.0.1, refer to the PowerCenter Installation and Configuration Guide, and related documentation.
To install Informatica PowerCenter Client Tools on Windows
Extract the 901HF2_Client_Installer_win32-x86.zip file to a suitable location, then use the setup.exe program to start the installation.
The Informatica PowerCenter installation files are available on eDelivery in the BI Media pack.
Follow the instructions on the Informatica PowerCenter Client 9.0.1 HF2 installation wizard.
Note: When you are prompted to select either a new installation or an upgrade, select the option for a new installation. For information about upgrading Oracle BI Applications, see Oracle Business Intelligence Applications Upgrade Guide. If you are prompted to select either the Developer option or the Client option, then select the Client option.
This section explains how to install Informatica PowerCenter Services 9.0.1 and Hotfix 2 for an Oracle BI Applications deployment. For detailed generic information about installing Informatica 9.0.1, refer to the PowerCenter Installation and Configuration Guide and related documentation.
Before you start, you must create a database account for the Informatica Domain (for example, infadomain/infadomain). During the Informatica PowerCenter Services installation, you specify the connection details for this database account.
If you have purchased the 'Informatica OEM PowerCenter ETL Server and PowerConnect Adapters' license from Oracle, use the Oracle_All_OS_Prod.key license key file.
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 install into a directory with a space in the name, you will cause errors in DAC.
To install Informatica PowerCenter Services 9.0.1 and Hotfix 2
Extract the appropriate ZIP or JAR file for the platform that you want to use, then start the installer using an appropriate installer file.
For example, a setup.exe file on Windows, or an install.sh file at a shell command line on UNIX or Linux.
The Informatica PowerCenter installation files are available on eDelivery in the BI Media pack.
When you are prompted to select either a new installation or an upgrade, select the option for a new installation. For information about upgrading Oracle BI Applications, see Oracle Business Intelligence Applications Upgrade Guide.
Follow the instructions on the Informatica PowerCenter Services installation wizard.
When you get to the Domain Creation pages, do the following:
At the Domain creation page, select the Create a Domain option, then click Next.
At the next page, specify the connection details of the Informatica Domain database (for example, infadomain/infadomain). For more information about required database accounts, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components".
At the Configuration database page, specify the details of the Domain that you want to create.
Complete to installation wizard until you get to the Post Installation Summary screen, and click Done.
Follow this procedure to create the Informatica Repository Service.
For detailed information about creating a Repository Service, refer to the Informatica documentation.
To create the Informatica Repository Service
Note:
Before you log into Informatica Administrator, make sure that the Informatica service is running. To start the Informatica Service, use the Start Informatica Services program.On Windows, you can also use the Windows Services dialog to start the 'Informatica 9.0.1' service. On UNIX, use the infaservice [startup | shutdown] command.
Open Informatica Administrator.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
In the Domain Navigator, select the root Domain, then click Actions, then New, then PowerCenter Repository Service, to display the New PowerCenter Repository Service wizard.
Use the 'Specify the properties for this new PowerCenter Repository Service' dialog to specify a Name (for example, BIA_RS), Location, License, and Node, then click Next.
Use the 'Specify the database properties for this new PowerCenter Repository Service' dialog to specify a Database Type, Username, Password, Connection String, and Code Page.
Notes:
The Username and Password are for the database account that you created for the Informatica repository (see Section Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components.").
If you specify the wrong code page, you cannot correct this later. If the code page is incorrect, then you must create a new Repository Service.
In the 'Specify the creation options for the new PowerCenter Repository Service' area, select the No content exists under specified connection string. Create new content. radio button.
For detailed information about creating a Repository Service, refer to the Informatica documentation.
Click Finish.
In the example screen shot below, Domain_79456 contains a PowerCenter Repository Service named BIA_RS.
Follow this procedure to create the Informatica Integration Service.
For detailed information about creating an Integration Service, refer to the Informatica documentation.
Before you start: Change the Repository Properties\Operating Mode to 'Normal' for the Repository Service that you created in the previous step.
To create the Informatica Integration Service
Open Informatica Administrator.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
In the Domain Navigator, select the root Domain, then click Actions, the New, then PowerCenter Integration Service, to display the New PowerCenter Integration Service wizard.
Use the 'Specify the properties for this new PowerCenter Integration Service' dialog to specify a Name (for example, BIA_IS), Location, License, and Node, then click Next.
Use the 'Specify the PowerCenter Integration Service and its login credentials' dialog to select the Repository Service that you created in the previous step, and specify the repository username and password (for example, Administrator\Administrator).
In the 'Select the data movement mode' area, select the appropriate Code Page setting from the Data Movement Mode drop down list.
Click Finish.
Tip:
The Integration Service will only start if the Repository Service is operating with the Operating Mode set to 'Normal'.In the example screen shot below, Domain_79456 contains a PowerCenter Integration Service named BIA_IS.
An Informatica Repository file called Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep for Teradata deployments) is installed the ORACLE_HOME\biapps\dwrep\Informatica\Repository directory during the Oracle BI Applications installation. You must load the contents of this REP file into the Informatica repository.
If you are re-loading the Informatica repository, then you must first delete the contents of the repository. The Restore option is only available if the repository is empty.
You can only delete and restore contents when the Repository Service is in the 'Exclusive' Operating Mode. After the repository has been loaded, you must change the Operating Mode for the Repository Service from 'Exclusive' to 'Normal'.
If the Integration Service fails while the Operating Mode for the Repository Service is set to 'Exclusive', then you must restart the Integration Service with the Operating Mode for the Repository Service set to 'Normal'.
Follow the appropriate task from the following topics:
For English environments, follow the steps in Section 4.6.5.1, "Restoring the Pre-built Informatica Repository for Environments in English"
For non-English environments, follow the steps in Section 4.6.5.2, "Restoring the Pre-built Informatica Repository on a Non-English Operating System"
You use the Restore option in Informatica Administrator to load the pre-built Oracle_BI_DW_Base repository (or the Oracle_BI_DW_Teradata repository in a Teradata environment).
To load the pre-built Oracle_BI_DW_Base.rep or Oracle_BI_DW_Teradata.rep repository into Informatica
Copy the file Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep if your Oracle Business Analytics Warehouse is on Teradata) from:
ORACLE_HOME\biapps\dwrep\Informatica\Repository
To:
INFA_HOME\server\infa_shared\Backup
In Informatica Administrator, select the Repository Service that was created in the procedure in Section 4.6.3, "Creating the Informatica Repository Service."
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
In the General Properties area of the Properties tab, make sure the OperatingMode value is Exclusive.
To change the OperatingMode value, click Edit, and then select a new value from the drop-down list. Click OK to exit edit mode.
In the Actions menu at the top right-hand side, choose Actions, then Repository Contents, then Delete Contents.
At the Delete Contents for <repository name> dialog box, enter the repository username and password (for example, Administrator\Administrator), then click OK.
In the Actions menu at the top right-hand side, choose Actions, then Repository Contents, then Restore.
At the Restore Contents for <repository name> dialog, enter the repository username and password (for example, Administrator\Administrator), then click OK.
Choose Actions > Restore Contents.
At the Restore Contents dialog, select Oracle_BI_DW_Base.rep (or Oracle_BI_DW_Teradata.rep for Teradata installations) from the Select Backup File drop-down list.
Select the Restore as New check box.
Click OK to start the restore process.
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.
For instructions, see the topic titled, "Promoting a Local Repository to a Global Repository," in "Chapter 8: Managing the Repository," in Informatica PowerCenter Administrator Guide.
Change the OperatingMode value to Normal.
Go to the Properties tab.
In the General Properties area, click Edit.
Click the OperatingMode drop-down list, and select Normal.
Note: if you do not set the Operating Mode to Normal, the Integration Service will not restart.
If prompted, enter the repository username and password.
Note:
The pre-built Oracle_BI_DW_Base and Oracle_BI_DW_Teradata repository files are versioned from Oracle BI Applications release 7.9.3 and higher. For more information about the versioned repositories, see Appendix F, "About the Versioned Informatica Repository."If Informatica PowerCenter Services is installed on a non-English version of the operating system, you must use the command line to restore the pre-built Informatica Repository provided with Oracle BI Applications.
To restore the pre-built Informatica Repository on a non-English operating system
Open a Command window.
Enter the following command to connect to the repository:
Pmrep connect -r <RepositoryName> -d <Domain>
Enter the following command to restore the repository:
PmRep restore -u <domain_user_name> -p <domain_user_password> -i <input_file_name> -n
where the input_file_name
is the name of the pre-built repository file.
This section explains how to configure Informatica PowerCenter Services for use with Oracle BI Applications. It contains the following topics:
Section 4.6.6.3, "Setting PowerCenter Integration Services Relaxed Code Page Validation"
Section 4.6.6.4, "Setting PowerCenter Integration Services Custom Properties"
Section 4.6.6.5, "Creating the Repository Administrator User in the Native Security Domain"
When you first log into any of the Informatica PowerCenter Client tools (for example, Designer, Workflow Manager), you must specify the Informatica Domain that you created when you installed Informatica PowerCenter Server. For example, you might want to log into Informatica PowerCenter Designer to check that the Oracle BI Applications Repository has been successfully loaded.
The following tasks describe how to use Informatica PowerCenter Designer to specify an Informatica Domain and Informatica Repository, but you can use any of the Informatica PowerCenter Client tools.
To set up the Domain:
Start Informatica PowerCenter Designer.
Choose Repository, then Configure Domain, to display the Configure Domains dialog.
Click the Add a new Domain icon to display the Add Domain dialog.
Use the Add Domain dialog to specify the following:
Table 4-4 Add Domain dialog fields
Field Name | What to enter |
---|---|
Domain Name |
Specify the name of the domain that you created when you installed Informatica PowerCenter Server, as described in Section 4.6.2, "Installing Informatica PowerCenter Services" ). |
Gateway Host |
Specify the hostname or IP address of the Gateway Host as specified during the installation of PowerCenter Services (for example, machineA). |
Gateway Port |
Specify 6005 (unless you have changed the default Gateway Host port). |
Click OK.
When you specify a valid set of Domain details, the new domain is displayed in the Domains list, and any available Repositories are displayed in the Repositories list.
In the Repositories list, select the check box for the Repository that you want to use (for example, you might select the Informatica Repository that you created in Section 4.6.3, "Creating the Informatica Repository Service").
To set up the Repository:
Start Informatica PowerCenter Designer.
Choose Repository, then Add to display the Add Repository dialog.
In the Repository field, enter the name of the Informatica Repository that you created in Section 4.6.3, "Creating the Informatica Repository Service".
For example, you might have named the repository BIA_RS.
In the Username field, specify Administrator.
Click OK to save the details and return to the Start Page.
In the Repositories tree, double click on the Repository that you specified in the previous step (for example, BIA_RS).
In the Connection Settings part of the Connect to Repository dialog, make sure that the Domain field displays the domain that you specified in the previous task.
Specify a user name and password (for example, Administrator\Administrator), then click Connect.
You can now expand the repository in the Repositories list to display the contents.
You need to copy source files and lookup files from the Oracle BI Applications installation directory to the Informatica directory on the Informatica PowerCenter Services machine.
Note: The following instructions assume the default Informatica directory locations for source and lookup files.
The default Informatica directory for source files is INFA_HOME\server\infa_shared\SrcFiles. You can confirm the source file directory for your environment as follows: In PowerCenter Administrator, select the Integration Service, navigate to the General Properties area of the Processes tab, and verify the value of $PMSourceFileDir.
The default Informatica directory for lookup files is INFA_HOME\server\infa_shared\LkpFiles.You can confirm the source file directory for your environment as follows: In PowerCenter Administrator, select the Integration Service, navigate to the General Properties area of the Processes tab, and verify the value of $PMLookupFileDir.
To copy source files and lookup files to Informatica PowerCenter Services on Windows:
Copy the source files as follows:
From:
ORACLE_HOME\biapps\dwrep\Informatica\SrcFiles on the machine where the Oracle BI Applications installer was run.
To:
INFA_HOME\server\infa_shared\SrcFiles.
Copy the lookup files:
From:
ORACLE_HOME\biapps\dwrep\Informatica\LkpFiles on the machine where the Oracle BI Applications installer was run.
To:
INFA_HOME\server\infa_shared\LkpFiles.
Note:
If Informatica PowerCenter Services is installed on a UNIX or Linux machine, convert the target paths above from Windows to UNIX/Linux. For example, covert the Windows target path:From:
INFA_HOME\server\infa_shared\SrcFiles
To:
INFA_HOME/server/infa_shared/SrcFiles
PowerCenter Integration Services for Oracle BI Applications must be configured for relaxed code page validation.
To configure Informatica PowerCenter Integration Services for relaxed code page validation
Log into Informatica Administrator.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
Select the Integration Service.
Select the Properties tab.
In the Configuration Properties area, click Edit.
Deselect the 'ValidateDataCodePages' check box.
Follow this procedure to set PowerCenter Integration Services custom properties.
To set Informatica PowerCenter Integration Services Custom Properties
In Informatica Administrator, select the Integration Service.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
Click the Properties tab.
In the Custom Properties area, click Edit.
Use the New option to display the New Custom Property dialog, and add the following Custom Properties:
Table 4-5 Custom Properties for Informatica PowerCenter Integration Services
Custom Properties Name | Custom Properties Value | Notes |
---|---|---|
overrideMpltVarWithMapVar |
Yes |
Enables Informatica to evaluate parameters within mapplets. |
DisableDB2BulkMode |
Yes |
Add this custom property and set value to Yes if your Oracle Business Analytics Warehouse is on a DB2/390 or a DB2 UDB database. |
Click OK to save the details.
Make sure that the Integration Service and Repository Service that you created during the Informatica PowerCenter installation are running.
For DAC to be able to access Informatica and perform tasks in pmcmd and pmrep command line programs, DAC must log in to Informatica as an Informatica Repository Administrator user. This user must be configured in the native security domain.
You need to create such a Repository Administrator user, or , if your security policies allow, you can use the default Administrator user (whose privileges include Repository administration) for DAC connectivity to Informatica.
For more information on creating users and Informatica security domains, see section "Users and Groups" in Informatica PowerCenter Administrator Guide.
To use the default Administrator user in the native security domain
Log in to the PowerCenter Administration Console as Administrator.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
Display the Security tab by clicking the Configure Security icon in the top, right corner of the Informatica Administrator work area.
In the Users area, expand the Native directory and select Administrator.
Note: Do not select an existing user from an LDAP domain.
Display the Privileges tab, and use the make sure that the correct Domain and Repository Service are selected.
Click OK.
To create a new Repository Administrator defined in the native security domain
Log in to the PowerCenter Administration Console as Administrator.
For more information about logging into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
Display the Security tab by clicking the Configure Security icon in the top, right corner of the Informatica Administrator work area.
In the Users area, click Actions, then Create User to display the Create User dialog.
Note: Do not create this user in an LDAP domain.
Use the Create User dialog to specify the user details, then click OK.
In the Users area, select the new user, display the Privileges tab, then click Edit to display the Edit Roles and Privileges dialog.
Use the Roles tab and Privileges tab to select the appropriate Domain and Repository Service.
Click OK.
This section provides instructions for installing the DAC Client and Server as well as installing and creating the required database connections. For information about the version of the DAC platform supported with Oracle BI Applications, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
The DAC installer installs the DAC Client and DAC Server on Windows. DAC Client only runs on Windows. DAC Servers runs on Windows, UNIX, and Linux.
The DAC Server can run on Linux, but it must first be installed on a Windows machine, then copied over to a Linux machine. Oracle does not provides an installer for DAC on UNIX. For instructions on setting up DAC Server on UNIX, see Section 4.7.2, "Installing the DAC Server on UNIX".
After you install the DAC platform, you then need to install JDBC drivers for DAC database connectivity and create ODBC database connections for the DAC Client.
This section includes the following topics:
Section 4.7.3, "Installing JDBC Drivers for DAC Database Connectivity"
Section 4.7.4, "Creating ODBC Database Connections for the DAC Client"
This section explains how to use the DAC installer to install DAC Client and DAC Server for an Oracle Business Intelligence Applications environment.
Notes
The DAC Client can only be installed and run on Windows.
The DAC Client must be installed on the machine where Informatica PowerCenter Client Tools was installed.
The DAC Server must be installed on the machine where Informatica PowerCenter Services was installed.
You must install Informatica PowerCenter Services before you install DAC.
The correct version of the JDK is installed by the DAC installer.
The DAC installer installs DAC in the DAC_HOME\bifoundation\dac directory.
To install DAC using the DAC installer
Start the DAC installer by double-clicking the setup.exe file.
Follow the instructions on the installation wizard, as described in the following table:
Page | Your Action | Notes |
---|---|---|
Welcome | Click Next to proceed. | None. |
Specify Installation Location | Enter the absolute path for the location where you want to install DAC or use the Browse button to select an existing location.
Click Next. |
The installation directory path can contain alphanumeric, underscore (_), hyphen (-) or dot (.) characters and must begin with an alphanumeric character.
The directory in which you install DAC is referred to as the Oracle Home location. |
Install Component | Click Next. | This screen is read only.
If you are running the installer on Windows, the DAC Client and Server will be installed. If you are running the installer on Linux, the DAC Server will be installed. |
Specify Informatica Version | Select Informatica version 8.x, and then click Next. | Select the 8.x option to install DAC with Informatica PowerCenter V9.0.1 with Hotfix 2. |
Specify Informatica Location | Enter or browse for the location of the following:
|
DAC Server and Informatica PowerCenter Services must be co-located. Therefore, the Informatica PowerCenter Services must be on a local drive. |
Pre-requisite Checks | If the checks pass, click Next to proceed with the installation | The DAC installer automatically performs pre-requisite checks on your system. The Pre-requisite Checks screen indicates the progress of the checks.
If a check fails, you can click Retry to run the check again, or click Continue to proceed with the installation. |
Installation Summary | Click Install to begin the installation. | None. |
Installation Progress | Click Next. | None. |
Installation Completed | Click Finish. | None. |
DAC Configuration Handled by the DAC Installer
The DAC installer handles the following configuration:
In the config.bat file, the DAC installer configures the JAVA_HOME and DAC_HOME variables.
In the dac_env.bat file, the DAC installer creates an environment variable named INFA_DOMAINS_FILE and sets the value to the directory path of the domans.infa file.
The DAC installer adds the directory path to Informatica PowerCenter binaries to the PATH environment variable.
Note:
If you reinstall or upgrade Informatica PowerCenter without reinstalling DAC, you need to manually set these environment variables.The DAC Server can run on Linux, but it must first be installed on a Windows machine, then copied over to a Linux machine, as described in the steps below. Oracle does not provides an installer for DAC on UNIX.
Note:
You can also use this procedure to install the DAC Server on Linux.When installing a DAC Server, note the following prerequisites:
On the UNIX machine, make sure the following are installed:
JDK version 1.6.0 or higher
Zip and unzip utility
Make sure that Informatica PowerCenter Services is co-located on the machine that will host the DAC Server. For more information, see Section 4.6, "Installing and Setting Up Informatica PowerCenter."
Make sure that Informatica has been configured according to the instructions in Section 4.6.6, "Configuring Informatica PowerCenter Components."
To install the DAC Server on a UNIX machine
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.
On the machine where the DAC Client is installed, copy the \dac directory (that is, the directory installed by the DAC installer in \orahome\10g\bifoundation) to the temporary directory (for example, \OracleBI_UNIX\).
From the \dac directory in the temporary directory, remove the \export and \icons subdirectories.
These directories do not need to be copied to the machine hosting the DAC Server.
Zip up the temporary directory (for example, \OracleBI_UNIX\).
Copy the zip file to the target UNIX machine.
Note: If you use FTP to copy the zip file, use binary mode.
On the target UNIX machine, place the zip file in a directory where you want to install the DAC Server.
On the target machine, unzip the zip file.
Shell scripts are provided in the DAC_HOME/dac directory. After copying these files to a UNIX machine and before using them, you might need to use a MS-DOS to UNIX conversion tool to convert the script files to UNIX format (that is, remove the carriage return and line feed characters). Alternatively, you can manually remove the carriage return and line feed characters from the script files. For more information, see Section A.7, "About the DAC Server Shell Scripts."
Copy the contents of the DAC_HOME/dac/unix_script_bkp directory into the /dac directory.
Edit the config.sh file located in the DAC_HOME/dac directory to point to the correct version of the JDK by setting the JAVA_HOME environment variable.
DAC requires JDBC drivers for database connectivity. The JDBC drivers that are used should be for the databases supported. Since JDBC drivers show variations with different database versions, only drivers that are shipped with the database or downloaded from database vendor site and known to be certified for the given database version should be used. Currently, third-party JDBC drivers for the databases are not supported.
To enable DAC database connectivity, you must install the appropriate JDBC driver in the DAC_HOME\dac\lib directory on the machines where the DAC Client and Server are installed.
Note:
This section applies to Windows, UNIX and Linux. The instructions in this section use the Windows directory path format.To install JDBC drivers in the \dac\lib directory
Oracle
If you are using Oracle database 11g, find the directory where Oracle is installed. Copy the file named ojdbc6.jar in the jdbc\lib directory and paste it in the DAC_HOME\dac\lib directory (for example, c:\orahome\10gR3_2\bifoundation\dac\lib).
If you are using an Oracle database 9.x or 10.x, find the directory where Oracle is installed. Copy the file named ojdbc14.jar in the jdbc\lib directory and paste it in the DAC_HOME\dac\lib directory (for example, c:\orahome\10gR3_2\bifoundation\dac\lib).
If you are using Oracle database 8.x, copy the file named classes12.zip and paste it in the DAC_HOMEdac\lib directory (for example, c:\orahome\10gR3_2\bifoundation\dac\lib). Also, edit the ORACLELIB setting in the config.bat file as follows:
set ORACLELIB=%DAC_HOME%\lib\classes12.zip
DB2
If you are using a DB2 database, find the directory where DB2 is installed. In the Java subdirectory copy the file named db2java.zip and paste it in the \dac\lib directory
Note:
If your source or target database is DB2-UDB, you also need to create DB2-UDB stored procedures to be used during the ETL process. For instructions, see Section 4.18.3, "Creating Stored Procedures for DB2-UDB".MSSQL
DAC is configured for Microsoft SQL Server 2005 JDBC drivers. If you are using a Microsoft SQL Server database, then download the Microsoft SQL Server 2005 JDBC Driver file sqljdbc.jar and copy it to the DAC_HOME\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 appropriate .JAR files to the DAC_HOME\dac\lib directory.
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_HOME\dac\lib directory. Depending on the Teradata JDBC version, you might not have some of the above files.
The DAC Client uses an ODBC connection to create and alter tables in the Oracle Business Analytics Warehouse. Create an ODBC connection to the Oracle Business Analytics Warehouse database on the machine that hosts the DAC Client, as described below.
Additionally, if your source system is Siebel CRM, you must create an ODBC connection to the OLTP (source) database on the machine where you will install Oracle BI Applications software. This ODBC connection will be used by the DDLIMP utility.
For the ODBC connections to Oracle databases, you must use the Oracle Merant ODBC Driver that is installed with the DAC platform installation. For all other databases, you should use ODBC drivers supplied by your database vendor.
Note:
On Windows Vista, the Oracle Merant Driver is not successfully installed by the DAC installer. Use Microsoft ODBC Administrator to configure an ODBC connection with the native ODBC driver instead. Use this ODBC when creating tables in the Oracle Business Analytics Warehouse using the DAC Client.Refer to the appropriate instructions for your database type:
Section 4.7.4.2, "How to Create ODBC Connections for DB2 Databases"
Section 4.7.4.1, "How to Create ODBC Connections for Oracle Databases"
Section 4.7.4.3, "How to Create ODBC Connections for SQL Server Databases"
Section 4.7.4.4, "How to Create ODBC Connections for Teradata 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 the Windows machine that will host the DAC Client, navigate to the ODBC Data Source Administrator.
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.
Click the System DSN tab.
Click Add.
In the list of drivers, select the Oracle Merant ODBC driver that is installed with DAC, for example, Oracle Merant ODBC Driver in DAC 10g_Oracle - OH1.
In the ODBC Oracle Driver Setup dialog, enter or select the following:
Field | Description |
---|---|
Data Source Name | Enter any meaningful name. |
Server Name | Enter the tnsname for the database. |
Client Version | Select 10gR1 for 10g and 11g databases. |
Click Test Connect to make sure the connection works.
(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.
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
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).
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.
Test the connections to make sure they work.
Follow these instructions for creating ODBC connections for SQL Server databases on Windows.
To create ODBC connections for SQL Server databases
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.
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.
Follow these instructions for creating ODBC connections for Teradata databases.
To create ODBC connections for Teradata databases on Windows
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.
Set the following parameters:
Field=Enter
DateFormat=AAA
SessionMode=ANSI
NoScan=Yes
For Unicode environments, in the Teradata ODBC Driver Advanced Options dialog, set the Character Set parameter to UTF8.
To create ODBC connections for Teradata databases on UNIX
Using the Teradata ODBC driver, create an ODBC connection for the Teradata database.
Set the following variables in the ODBC.INI file:
DateFormat=AAA
SessionMode=ANSI
NoScan=Yes
For UNICODE environments, in the Teradata ODBC Driver Advanced Options dialog, add the following:
CharacterSet=UTF8
DAC uses the Informatica pmrep and pmcmd command line programs to communicate with Informatica PowerCenter in the following ways:
DAC Server uses
pmrep to communicate with PowerCenter Repository Services.
pmcmd to communicate with PowerCenter Integration Services to run the Informatica workflows.
DAC Client uses
pmrep to synchronize tasks with Informatica workflows and to keep the DAC task source and target tables information up to date.
The pmrep program is installed in the PowerCenter Client and PowerCenter Services bin directories (for example, INFA_HOME\client\bin and INFA_HOME\server\bin). Because of the requirement to co-locate the DAC Client with the PowerCenter Client, the pmrep program is available on the machine for the DAC Client to use.
The pmcmd program is installed in the PowerCenter Services bin directory. In order for DAC to access pmcmd, it must also reside in the PowerCenter Client bin directory on the same machine where the DAC Client is installed. Copying the pmcmd executable file from the PowerCenter Services bin directory to the PowerCenter Client bin directory is a manual process.
To install pmcmd in the PowerCenter Client 9.0.1 bin directory
Do one of the following:
If PowerCenter Services 9.0.1 has been installed on the same machine as the DAC Client and PowerCenter Client 9.0.1, copy the pmcmd.exe and pmrep.exe from the INFA_HOME\server\bin directory to INFA_HOME\clients\PowerCenterClient\client\bin directory.
If Informatica PowerCenter Services 9.0.1 is installed on a Windows machine other than the one that hosts the DAC Client, copy the pmcmd.exe file from the INFA_HOME\server\bin directory on the machine where PowerCenter Services is installed to the INFA_HOME\clients\PowerCenterClient\client\ 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:
Run the Informatica PowerCenter Services installer as described in Section 4.6.2, "Installing Informatica PowerCenter Services."
Copy the pmcmd.exe file located in INFA_HOME\server\bin to INFA_HOME\clients\PowerCenterClient\client\bin directory.
DAC metadata files are installed by the Oracle BI Applications installer (as described in Section 4.5, "Installing the Oracle BI Applications files"). You need to copy these files to the machines hosting the DAC Client and Server.
Table 4-6 describes the location of the file or directory names in the Oracle BI Applications installation that you need to copy to the machine hosting the DAC Client.
Table 4-7 describes the location of the file names in the Oracle BI Applications installation that you need to copy to the machine hosting the DAC Server.
Table 4-6 DAC Metadata Files That Need to Be Copied to DAC Client Machine
File/directory Name | Location of File/directory in Oracle BI Applications Installation | File/directory to Be Copied to Following Location |
---|---|---|
Export directory |
ORACLE_HOME\biapps\dwrep\dac_metadata\dac_client\export |
Copy contents of the export directory to the DAC_HOME\dac\export directory on the machine hosting the DAC Client. |
DeleteTriggers.list |
ORACLE_HOME\biapps\dwrep\dac_metadata\dac_client |
Copy this file to the DAC_HOME\dac\conf directory on the DAC Client machine. |
Table 4-7 DAC Metadata Files That Need to Be Copied to DAC Server Machine
File/directory Name | Location of File/directory in Oracle BI Applications Installation | File to Be Copied to Following Location |
---|---|---|
parameterfileOLTP.txt |
ORACLE_HOME\biapps\dwrep\dac_metadata\dac_server |
Copy this file to the DAC_HOME\dac\Informatica\parameters\input directory on the DAC Server machine. |
parameterfileDW.txt |
ORACLE_HOME\biapps\dwrep\dac_metadata\dac_server |
Copy this file to the DAC_HOME\dac\Informatica\parameters\input directory on the DAC Server machine. |
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:
When you log into DAC for the first time, you must first configure a connection to connect to the DAC Repository. DAC stores this connection information for subsequent logins.
After configuring a connection to the DAC Repository and logging in, the DAC will automatically prompt you to upgrade the repository schema.
DAC Repository Database Authentication File
When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.
A user with the Administrator role must distribute the authentication file to any user account that needs to access the specified DAC Repository. For information about managing user accounts, see Section A.2, "About DAC User Account Management.".
To log into DAC for the first time and upgrade the repository schema
Start the DAC Client by navigating to the DAC_HOME\dac directory and double-clicking the startclient.bat file.
The Login ... dialog appears.
Click Configure.
In the Configuring ... dialog, select Create Connection, and then click Next.
Enter the appropriate connection information:
Field | Required Value |
---|---|
Name | Enter a unique name for the connection to the DAC Repository. |
Connection type | Select the type of database in which the DAC Repository will be stored. |
Connection String, or Database name, or TNS Name, or Instance | Select the database name or database account name of the DAC Repository.
If you are using:
|
Database Host | Enter the name of the machine where the DAC Repository will reside. |
Database Port | Enter the port number on which the database listens. For example, for an Oracle database the default port is 1521, or for a SQL Server database the default port is 1433. |
Optional URL | Can be used to override the standard URL for this connection. |
Optional Driver | Can be used to override the standard driver for this connection. |
Authentication File | Click in this field to do one of the following:
Proceed to the next step for detailed instructions. |
To select an existing authentication file, do the following:
Click in the Authentication File field of the Configuring... dialog.
In the Authentication File dialog, select Choose existing authentication file.
Navigate to the appropriate directory, and select the authentication file. Click OK.
In the Configuring... dialog, click Test Connection to confirm the connection works.
Click Apply, and then click Finish.
Note:
You must distribute this authentication file to all user accounts that need to access this DAC Repository.To create a new authentication file, do the following:
Click in the Authentication File field of the Configuring... dialog.
In the Authentication File dialog, select Create authentication file.
Navigate to the directory where you want to save the new authentication file, and click OK.
In the Create Authentication File dialog, enter a unique name for the authentication file, and click OK.
Enter the Table Owner Name and Password for the database where the repository will reside.
In the Configuring... dialog, click Test Connection to confirm the connection works.
Click Apply, and then click Finish.
Note:
You must distribute this authentication file to all user accounts that need to access this DAC Repository.In the Login... dialog, do the following:
Select the appropriate Connection from the drop-down list.
Enter a the User Name (for example, Administrator).
Enter a Password (for example, Administrator).
Click Login.
When prompted to create a repository, click Yes.
This process creates DAC repository tables.
Depending on your database type, you may have the option specify a tablespace.
The Unicode check box is available for a repository on SQL Server or DB2 databases. Check the Unicode check box if your deployment requires a Unicode schema to be created.
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
In DAC, select Tools, then DAC Repository Management, then Import to display the Import dialog.
Make sure that the correct DAC_HOME\dac\export\ directory is displayed at the top of the dialog. 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
In the Categories area, select the Logical check box and the System check box.
The information imported by these options is as follows:
Categories Options | Description |
---|---|
Logical | Imports all information contained in the DAC Design view and the execution plan information for the DAC Execute view. |
System | Imports all information contained in the DAC Setup view, except passwords for servers and database connections. |
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:
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.
Select the check box for any other business applications that you want to deploy.
Note:
Do not select the Data Warehouse option unless Oracle specifically instructs you to do so. This container is reserved for special purposes.Tip:
Make a note of the Application names that you select. When you create data warehouse tables later in the configuration process, you might need to type in the names exactly as they are displayed here (for more information, see Section 4.9.1, "Creating Data Warehouse Tables").Select the Truncate Repository Tables check box.
Note:
If you are importing DAC metadata for the first time, you should select the Truncate Repository Tables check box. If you are importing DAC metadata into a repository that already has metadata, do the following:Back up the current repository by exporting it to an empty directory (use the 'Change import/export directory' button to select an empty directory.
Use the 'Change import/export directory' button to select the \dac\export\ directory.
Select the Truncate Repository Tables check box and the Enable Batch Mode check box, and import the new DAC metadata.
Use the 'Change import/export directory' button to select the directory where you backed up data in Step 1.
Deselect the Truncate Repository Tables check box and re-import the data you backed up.
Select the Enable Batch Mode check box.
Note:
If you are using an Oracle 9i Release 2 database, you cannot use batch mode because the Oracle 9i JDBC drivers do not handle array inserts or bulk inserts.Click OK to display the Importing tables dialog.
To confirm that you want to import the seed data selected, re-type the text in the text box and click Yes.
If prompted to verify that you want to continue with the Import, click Yes.
When the process is complete, the DAC displays a status box containing a success or failure message. If the process fails, use the DAC_HOME\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.Click OK.
If your source or target database is a DB2/390 database, run DAC_DB2390_Source.sql or DAC_DB2390_Target.sql immediately after importing the seed data by connecting to the database where the DAC Repository resides. These files are stored in the DAC_HOME\dac directory.
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 Section 4.4.1, "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 Section 4.7.4, "Creating ODBC Database Connections for the DAC Client."
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 Section 4.7.4, "Creating ODBC Database Connections for the DAC Client."
Before you perform the procedures described in this section, make sure that the SSE role has been created for the Oracle Business Analytics Warehouse, and that the database user has been associated with the role (for more information, see Section 4.4.1.1, "How to Create the SSE Role").
If your Oracle Business Analytics Warehouse database is not Teradata, refer to Section 4.9.1, "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 Section 4.9.2, "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.
Use this procedure to create the data warehouse tables.
If the Oracle Business Analytics Warehouse database is Teradata, follow the steps in Section 4.9.2, "Creating Data Warehouse Tables on a Teradata Database."
To create data warehouse tables
In DAC, select Tools, then ETL Management, then Configure.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
In the Sources dialog, select the database platform for the target data warehouse and source transactional database.
Click OK to display the Data Warehouse Configuration Wizard.
Select the Create Data Warehouse Tables check box, and click Next.
The Data Warehouse tab is active.
Enter the details of the database in which you want to store the data warehouse.
The information that you need to enter is dependent on the type of target database that you are using to store the data warehouse.
Field | Description |
---|---|
Database Type | (Read only) Type of database, as specified by the 'Target data warehouse database platform' field on the Sources dialog (see step 2).
If the database type is DB2/390, then check the 390 Database check box. |
Container | The name of the source business applications 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:
If there are tables that are common to these containers, then only one table will be created. For example, if there is a table called W_ORG_D in Oracle 11.5.9 and Oracle 11.5.10, then DAC will create only one table called W_ORG_D. If columns are different for the same table across containers, then DAC will create a table that has all the columns in the same table. If you only want to deploy a subset of the source business applications for which you imported seed data earlier, then use this field to specify a container name. When you specify a container name, you must enter the names of the applications exactly as they are displayed on the seed data Import dialog. For example:
For more information about containers, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide. |
Table Owner | Valid database owner, username, or account that you set up to hold the data warehouse (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"). |
Password | Valid database user password for the database owner, username, or account that you specified in the Table Owner field (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"). |
ODBC Data Source | Data Source Name (DSN) for the Oracle Business Analytics Warehouse.
You must specify the name of the ODBC connection (for example, Connect_to_OLAP) that you created for the data warehouse. For more information, see Section 4.7.4, "Creating ODBC Database Connections for the DAC Client". |
Data Area | (Optional) Tablespace where data warehouse tables are created. |
Index Area | (Optional) Indexspace where data warehouse indexes are created (applicable only to Oracle and DB2 databases). |
Is Unicode | Specifies whether the data warehouse database is Unicode. The database must be set to support the creation of a Unicode schema. For more information, see Section 4.3.3, "Code Page and Data Movement Requirements." |
Click Start.
The Run Status tab displays information about the process, as follows:
If a 'Success' message is displayed, the data warehouse tables have been created. If you want to see log information about the process, use the following log files.
DAC_HOME\dac\log\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.
DAC_HOME\dac\log\config\createwtables.log, createwtables.log.out, and createwtables.log.err - Logs of the DDLIMP process.
If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in generate_ctl.log to diagnose the error. The createtables.log is not generated.
This section explains how to create and delete data warehouse tables on a Teradata database. Before you start, make sure that the tables that you create are case specific by setting the session mode to ANSI in your Teradata ODBC configuration (for more information, see Section 3.5, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse").
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
In DAC, select Tools, then ETL Management, then Configure.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
In the Sources dialog, select 'Teradata' as the database platform for the source data warehouse, and select the appropriate database type for the target transactional database.
Click OK to display the Data Warehouse Configuration Wizard.
Select the Generate create statements for Data Warehouse Tables check box, then click Next to display the Data Warehouse SQL tab.
Use the Container field to specify individual containers, or leave blank to deploy all containers.
Click Start.
The Run Status tab displays information about the process, as follows:
If a 'Success' message is displayed, the data warehouse tables have been created. If you want to see log information about the process, use the following log files.
\DAC_HOME\dac\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.
DAC_HOME\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 DAC_HOME\dac\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.
Copy the SQL file created in step 6 from \conf\sqlgen\sql\Teradata into SQL Assistant and execute the SQL.
This section contains instruction for configuring the DAC Server. It contains the following topics:
Section 4.10.1, "Configuring the Connection Between the DAC Server and DAC Repository"
Section 4.10.4, "Activating Join Indexes for Teradata Databases"
You must configure the connection between the DAC Server and the DAC Repository.
On Windows, you can use the DAC Client to configure a DAC Server that runs in the same DAC_HOME\dac\ directory. Optionally, or to configure a DAC Server installed in another directory or on another Windows machine, use the serverSetupPrompt.bat file to configure the repository connection.
On UNIX or Linux, use the serverSetupPrompt.sh script to configure the connection between the DAC Server and the DAC Repository, as follows:
To configure the DAC Server repository connection using the DAC Client, see Section 4.10.1.1, "How to Configure the DAC Server Repository Connection Using the DAC Client (Windows)".
To configure the DAC Server repository connection using the serverSetupPrompt scripts, see Section 4.10.1.2, "How to Configure the DAC Server Repository Connection Using serverSetupPrompt Scripts (Windows, UNIX or Linux)".
If the DAC Server is co-located with a configured DAC Client in the same DAC_HOME\dac\ directory, 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
In DAC, select Tools, then DAC Server Management, then DAC Server Setup.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
Note: The DAC Repository that you connect to using the DAC Client is the one that will store the DAC Server repository connection information that you will specify in this procedure.
A confirmation dialog asks you to confirm that you want to configure the DAC Repository connection.
Click Yes to display the Server Configuration dialog.
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.
|
Table owner name | The database table name or instance (for example, DAC) that you created to store the DAC Repository (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components"). |
Password | The database or instance password (for example, DAC). |
Note:
The DAC Repository details that you specify here must match the DAC Repository details that you specified in the following tasks:- When you created a database to store the DAC Repository (for more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components").
- When you created a DAC connection (for more information, see Section 4.8.1, "Logging into DAC and Creating a Connection to the DAC Repository").
Click Test Connection to make sure the DAC Repository connection works.
Click Save.
Use the serverSetupPrompt.sh to configure the connection between the DAC Server and the DAC Repository when the DAC Server is installed on UNIX or Linux, as described below. For more information on the DAC Server scripts, see Section A.7, "About the DAC Server Shell Scripts."
Use the serverSetupPrompt.bat file to configure the connection between the DAC Server and the DAC Repository when the DAC Server is installed on Windows, as described below.
To configure the connection between the DAC Server and the DAC Repository using the serverSetupPrompt files:
Run the serverSetupPrompt script, as follows:
On Windows, double-click the serverSetupPrompt.bat located in the DAC_HOME\dac directory.
On UNIX or Linux, run serverSetupPrompt.sh located in the DAC_HOME/dac directory.
Enter 1 in the 'Please make your selection' prompt to enter repository connection information.
Enter the number for the type of database storing the DAC Repository from the list of connection type choices.
Enter the connection information as described in the Connection Information table in section Section 4.10.1.1, "How to Configure the DAC Server Repository Connection Using the DAC Client (Windows)".
Enter 2 to test the DAC Repository connection.
Enter 5 to save changes.
Enter 6 to exit.
When you install the DAC Server on UNIX, you must define the path for the Informatica Domain file and set environment variables manually. For instructions, see Section A.6.2, "How to Set Environment Variables for DAC Server Communication on UNIX."
On Windows, start the DAC Server by double-clicking the DAC_HOME\dac\startserver.bat script.
On UNIX or Linux, start the DAC Server by executing startserver.sh.
For more information on starting and stopping the DAC Server on Windows, UNIX or Linux, see Section A.4, "How to Start and Stop the DAC Server."
For Teradata databases, the preconfigured tasks for creating and dropping join indexes are inactive.
To activate join indexes for Teradata databases
In DAC, create a new Execution Plan with the list of Subject Areas that you want to extract.
Query for all Tasks whose name starts with 'Teradata Drop' and add them as preceding tasks.
Query for all Tasks whose name start with 'Teradata Create' and add them as following tasks.
Assemble the Execution Plan parameters in the Parameters tab and configure the parameters.
Redesign the Execution Plans.
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 Section A.4, "How to Start and Stop the DAC Server."
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
In DAC, navigate to the Views menu, then select Setup, then DAC System Properties.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
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 INFA_HOME\ server\infa_shared\SrcFiles.
You can confirm the source file directory for your environment by launching Informatica Administrator and going to the General Properties area of the Processes tab. |
Repository Name | Specify a name for the Informatica PowerCenter Repository. For example, BIA_RS. |
Note: For a description of all DAC System Properties, see Section A.3, "How to Set DAC System Properties."
This section explains how to register the Informatica PowerCenter Integration Services service and the Informatica PowerCenter Repository Service in DAC.
When you register Informatica Services in DAC, note the following:
You must register one or more Integration Services service.
You must register one Repository Service.
For information about Informatica Services details that you specify in this procedure, log into Informatica Administrator, select the appropriate Domain, and view the Repository Service and Integration Service.
In the example screen shot below, Domain_79456 contains a PowerCenter Repository Service named BIA_RS, and a PowerCenter Integration Service named BIA_IS.
In this example, you would perform Step 2 below to register the PowerCenter Integration Service BIA_IS, as in the following screen shot:
Then, you would perform Step 3 below to register the PowerCenter Repository Service BIA_RS, as in the following screen shot:
For more information, see Section A.8, "How to Log Into Informatica Administrator."
To register Informatica Services in DAC
In DAC, navigate to the Setup view, and then display the Informatica Servers tab.
For instructions on logging into DAC, see Section A.1, "How to Log into DAC."
For the Informatica Integration Service that you want to register, do the following:
Modify the record with Name = INFORMATICA_DW_SERVER (or create a new record) by entering the following information in the Edit subtab:
Field | Enter or select |
---|---|
Name | A name to identify the PowerCenter Integration Service in DAC. For example, you might use 'INFORMATICA_DW_SERVER', or change the name to 'BIA_IS'. |
Type | Informatica. |
Service | The name of the PowerCenter Integration Service that is being used. For example, BIA_IS.
The value that you specify here must match the name of the PowerCenter Repository Integration that was specified in Section 4.6.4, "Creating the Informatica Integration Service". |
Domain | The Informatica domain name. For example, Domain_machinename. |
Login | Informatica Repository user name with appropriate privileges to execute workflows (for example, Administrator).
Note: DAC must log in to Informatica as an Informatica Repository Administrator user that is configured in the native security domain. For instructions on how to create such a user, see Section 4.6.6.5, "Creating the Repository Administrator User in the Native Security Domain." For more information about Informatica security domains, see "Chapter4: Managing Users and Groups," in the PowerCenter Administrator Guide. |
Password | Informatica Repository user password (for example, Administrator). |
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 | The name of the PowerCenter Repository Service (for example, BIA_RS).
The value that you specify here must match the name of the PowerCenter Repository Service that was specified in Section 4.6.3, "Creating the Informatica Repository Service". |
Inactive | Indicates whether the PowerCenter Integration Services service will participate in the ETL process. |
Click Test Connection to make sure that the connection works.
Note: Integration Services must be running.
Click Save to save the details.
For the Informatica Repository Service you want to register, do the following:
Modify the record with Name = INFORMATICA_REP_SERVER (or create a new record) by entering the following information in the Edit subtab:
Field | Enter |
---|---|
Name | A name to identify the PowerCenter Repository Service in DAC. For example, you might use 'INFORMATICA_REP_SERVER', or change the name to 'BIA_RS'. |
Type | Repository. |
Hostname | The host name or IP address of the machine that is running the Informatica Server. |
Server Port | The Port number of the Gateway Node that is being used for the Informatica PowerCenter domain (for example, 6005).
If you need to obtain the name of the Repository Service, log into Informatica Administrator, expand the Domain, display the Node, display the Properties tab, and view the General Properties\Port value. Note: You must use the Node with the General Properties\Gateway Node value set to 'Yes'. |
Domain | (Read-only) The Domain name that you specified for the Domain field in step 2. |
Login | Informatica Repository user name with appropriate privileges to execute workflows (for example, Administrator). |
Password | Informatica Repository user password (for example, Administrator). |
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 | The name of the PowerCenter Repository Service (for example, BIA_RS).
The value that you specify here must match the name of the PowerCenter Repository Service that was specified in Section 4.6.3, "Creating the Informatica Repository Service". |
Inactive | Indicates whether the Repository Service will participate in the ETL process. |
Click Test Connection to make sure that the connection works.
Note: The Repository Service must be running.
Click Save to save the details.
Follow this procedure to specify the transactional and data warehouse data sources in DAC.
Note:
If you have a JD Edwards EnterpriseOne or a JD Edwards World source system hosted on an iSeries DB2 database, see Section 4.11.3.1, "Specifying a Data Source Connection for JD Edwards EnterpriseOne or JD Edwards World Hosted on a DB2 Database."To specify transactional and data warehouse data sources
Log into DAC.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
From the Views menu, select Setup, then Physical Data Sources.
The Physical Data Sources tab displays a precreated record for the data warehouse with the name DataWarehouse, and one or more records for the OLTP sources. The records that are created by DAC for the OLTP sources depend on the business application source systems you selected when importing the DAC metadata. For more information, see Section 4.8.2, "Importing Metadata into the DAC Repository."
For each record, enter the following information in the Edit subtab:
Field | Description |
---|---|
Name | Logical name for the OLAP or OLTP database connection. Do not change the default values. For example, you must specify DataWarehouse as the name of the OLAP data source.
Note: When you create an execution plan to perform an ETL, you need to use the OLAP and OLTP data source names that you specify here as the Parameter values for the execution plan parameters DBConnection_OLTP and DBConnection_OLAP. For more information about setting the Parameter values for an execution plan, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide. |
Type |
Do not change the default values. |
Connection Type | Type of database. Possible values are:
|
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.
Note: The transactional (OLTP) database user that you register should be the database table owner. Alternatively, at a minimum, the user must have read privileges on the transactional database for all tables and aliases. For Siebel CRM databases, the OLTP database user requires privileges to create triggers and views on all tables and aliases. For more information, see Section 4.4.1, "Create Databases for Oracle BI Applications and Informatica PowerCenter Components". |
Table Owner Password | Valid database user password. |
Max Num Connections | Maximum number of database connections this connection pool can contain. |
DB Host | Machine name or instance where the database resides. For example, databasename.us.company.com. |
Port | Port number where the database listens (for example 1521 is the default for an Oracle database). |
Dependency Priority | Number used to generate dependencies when designing execution plans. |
Data Source Number | Unique number assigned to the data source category so that the data can be identified in the data warehouse. For example, the value '1' is used for Siebel data sources.
If you are editing a data source template for a data source type, Oracle recommends that you do not change the default value. If you are specifying a data source without using a pre-defined template, you must use the correct value for that data source category. For example, if you specify an Oracle EBS R12 data source, you must specify the DATASOURCE_NUM_ID value '9'. For a complete list of supported data sources and DATASOURCE_NUM_ID values, see the section entitled, "How to Configure Data Source Num IDs," in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users. This value is passed as a parameter to the Informatica workflows. If you are using multiple sources, each data source has a unique number. Typically, all source dependent extracts will use this parameter to populate the DATASOURCE_NUM_ID column, and the source independent workflows will carry these values to the final dimension and fact tables. |
Default Index Space | (Oracle specific) Specifies the table space in which DAC drops and creates indexes against this database connection
To use the default table space, leave this field blank. |
Click Test Connection to make sure the connection works.
Click Save.
If you have a JD Edwards EnterpriseOne or JD Edwards World source system hosted on an iSeries DB2 database, follow the steps in this section to specify the OLTP connection in DAC.
From the IBM site, select and download the appropriate driver based on the version of your iSeries DB2 database.
Include the driver in the class path, for example, for IBM DB2 on iSeries V5R4M0, download jt400.jar copied from the iSeries server only.
On a machine that hosts the DAC Server and an appropriate drive client access for iSeries, create a data source name (DSN) to connect to the source DB2 database (using an ODBC Connection).
Log into DAC.
From the Views menu, select Setup, then Physical Data Sources.
Select the record for the JD Edwards EnterpriseOne or JD Edwards World source.
In the Edit tab, complete the following fields:
Field | Description |
---|---|
Name | This is the logical name for the database connection. It must match the name in the OLTP record that you selected. Do not change the default value. |
Type | Enter Source. |
Connection Type | Enter DB2. |
Instance | Or connection string. Enter the DSN that you created in step 3. You enter the DSN, or host name, because the JDBC driver and URL that you enter below are used to override the connection to the database. |
Table Owner | Valid database user. |
Table Owner Password | Valid database user password. |
Max Num Connections | Maximum number of database connections this connection pool can contain. |
JDBC Driver | The name of the driver. The driver that you enter will override the driver that connects to the iSeries database. The driver that you select must be applicable to the version of iSeries on which JD Edwards EnterpriseOne or JD Edwards World is hosted.
For example, for IBM DB2 on iSeries V5R4M0, you would enter this driver:
|
URL | The URL for the driver. The URL that you enter will override the driver URL that connects to the iSeries database. The driver URL that you select must be applicable to the version of iSeries on which JDE source is hosted. For example, for IBM DB2 on iSeries V5R4M0, you would enter this URL:
|
Click Test Connection to make sure the connection works.
Click Save.
This section explains how to log into Informatica PowerCenter Workflow Manager and define relational and applications connections. It contains the following topics:
Section 4.12.1, "Logging Into Informatica PowerCenter Workflow Manager"
Section 4.12.2, "Configuring Connections in Informatica PowerCenter Workflow Manager"
Note: Before you log into Workflow Manager, start the Informatica services.
To log into Informatica Workflow Manager
On the Informatica PowerCenter Client machine, start Informatica PowerCenter Workflow Manager.
Choose Repository, then Connect, to display the Connect to Repository dialog.
In the Connect to Repository dialog:
In the Password field, specify the Administrator password (for example, Administrator).
If the Connection Settings area is not displayed, click More.
Click Add (next to the Domain drop-down list) to display the Add Domain dialog.
In the Add Domain dialog:
Specify the name of the domain that was created when you installed Informatica PowerCenter Services (for example, Domain_<hostname>).
Specify the fully qualified hostname for the gateway host (for example, mymachine@us.company.com).
Specify the port for the gateway port (for example, 6005).
Click OK to save the details and close the Add Domain dialog.
Click Connect.
You use Informatica PowerCenter Workflow Manager to configure the relational and application connections that are required for your deployment, as follows:
For all data sources except PeopleSoft OLTP data sources, configure relational database connections for both OLTP and OLAP data sources by following the steps in Section 4.12.2.1, "How to Configure Relational Connections."
For PeopleSoft OLTP data sources, configure application connections by following the steps in Section 4.12.2.2, "How to Configure Application Connections for PeopleSoft OLTP Data Sources."
For Teradata databases, follow the additional steps in Section 4.12.2.3, "(Teradata specific) How to Configure the Informatica Repository for Teradata External Loader Connections."
Note: The Informatica services must be running to perform these tasks.
Follow this procedure to configure relational connections:
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.
For each database connection you need to create, do the following:
Click New to display the Select Subtype dialog, select the appropriate database type (for example, Oracle), then click OK to display the Connection Object Definition dialog.
Use the Connection Object Definition dialog to define the relational connection.
Click OK to save the details.
Notes:
If the target database is Oracle or DB2, use the following settings:
Click New, select the appropriate database type, and then click OK.
Name: DataWarehouse, for connection to the Oracle Business Analytics Warehouse.
You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View. For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup view. For example, if your source system is Siebel release 7.8.x, then you name this connection as SEBL_78. For more information, see Section 4.11.3, "Setting Physical Data Sources".
User Name: Database user name with the appropriate read and write database permissions to access the database.
Password: Password for the user name.
Connect string: Connect string used to communicate with the database (refer to the Informatica Help for information about specifying this value).
Code Page: Code page compatible with the code page of the database client. If NLS_LANG (for Oracle database) or DB2CODPAGE (for DB2 database) has been set, then the Code Page value should be a code page compatible with the language set by these variables. For more information, see Section 4.3.3, "Code Page and Data Movement Requirements".
If the target database is SQL Server, use the following settings:
Click New and select the type as ODBC, then click OK.
Name: DataWarehouse, for the connection to the Oracle Business Analytics Warehouse. You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View.For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup View. (For example, if your source system is Siebel version 7.8.x then you name this connection as SEBL_78). For more information, see Section 4.11.3, "Setting Physical Data Sources".
User Name: Database user name with the appropriate read and write database permissions to access the database.
Password: Password for the user name.
Code Page: Code page compatible with the code page of the database client.
Database name: Name of the database.
Server name: Database server name.
Domain name: Name of the domain.
If the target database is Teradata, use the following settings:
Click New and select the type as Teradata, then click OK.
Name: DataWarehouse, for the connection to the Oracle Business Analytics Warehouse. You must specify 'DataWarehouse' exactly as it appears in the Physical Data Sources tab in the DAC Setup View.For the connection to the OLTP, specify the name exactly as it appears in the Physical Data Sources tab in the DAC Setup View. (For example, if your source system is Siebel version 7.8.x then you name this connection as SEBL_78). For more information, see Section 4.11.3, "Setting Physical Data Sources".
User Name: Database user name with the appropriate read and write database permissions to access the database.
Use Parameter in Password: Indicates the password for the database user name is a session parameter, $ParamName. Define the password in the workflow or session parameter file, and encrypt it using the pmpasswd CRYPT_DATA option.
Password: Password for the database user name. For Teradata connections, this overrides the database password in the ODBC entry. Passwords must be in 7-bit ASCII.
Code Page: Code page compatible with the code page of the database client.
Database name: Name of the database. For Teradata connections, this overrides the default database name in the ODBC entry. If you do not enter a database name for a Teradata connection, the Integration Service uses the default database name in the ODBC entry.
Data Source name: Name of the Teradata ODBC data source.
Domain name: Name of the domain.
PeopleSoft OLTP data sources use Application Connections that implement Informatica's PowerConnect for PeopleSoft. PeopleSoft OLTP data sources do not use relational connections. Therefore, you need to use Informatica Workflow Manager to define Application Connections for the PeopleSoft OLTP data sources as described below.
Note: You need to configure relational connections for OLAP databases with PeopleSoft adapters (for more information, see Section 4.12.2.1, "How to Configure Relational Connections.").
To configure Application Connections for PeopleSoft OLTP data sources:
In Informatica PowerCenter Workflow Manager, select Connections, then Application, to display the Application Connection Browser dialog.
You need to create an Application Connection for each PeopleSoft transactional (OLTP) data source.
For each Application Connection that you need to create, do the following.
Click New to display the Select Subtype dialog, select the appropriate database type (for example, PeopleSoft Oracle), then click OK to display the Connection Object Definition dialog.
Use the Connection Object Definition dialog to define an Application Connection (for example, a connection named PSFT_9_0_HCM).
Click OK to save the details.
Notes:
You must specify the Name value exactly as it appears in the Physical Data Sources tab in the DAC Setup View. For example, if your source system is named PSFT_9_0_HCM in DAC, then you must name this connection as PSFT_9_0_HCM. For more information, see Section 4.11.3, "Setting Physical Data Sources".
Connect string: Connect string for the database (refer to the Informatica Help for information about specifying this value).
To configure the Informatica Repository for Teradata, you need to do the following:
Specify Loader Connections for Teradata (for more information, see Section 4.12.2.3.1, "How to Specify Loader Connections for Teradata").
For each workflow, specify Loader Connection details at the session level (for more information, see Section 4.12.2.3.2, "How to Specify Teradata Details at the Workflow Level").
Follow this procedure to configure the Informatica Repository in Informatica PowerCenter Workflow Manager for Teradata external loader connections.
In Informatica PowerCenter Workflow Manager, select Connections, then Loader to display the Loader Connection Browser.
In the Objects list, select Teradata_Tpump_Upsert, and then click Edit to display the Connection Object Definition dialog.
Edit the User Name, Password, TDPID, Database Name, Error Database, and Log Table Database, and other attributes as necessary. Please note that if you do not enter the Error Database and Log Table Database, Informatica will default it to the same as the Tables Database.
Repeat these steps for the following objects:
Teradata_Tpump_Update
Teradata_Tpump_Insert
Teradata_Tpump_Delete
Teradata_Tpump_Upsert
For Unicode environments, append -c UTF8 to the value for the External Loader Executable attribute for each external loader.
For example:
tpump -c UTF8
Click Close to close the Loader Connection Browser.
For each session, you need to specify Teradata details, as follows:
In Informatica PowerCenter Workflow Manager, go to the workflow and open the session in the Task Developer pane.
Double-click on the session to display the Edit Tasks dialog.
Display the Mapping tab.
On the Targets node on the left navigator panel, select the Teradata table name.
In the Writers area, select "File Writer" from the Writers drop-down list for the target table.
In the Connections area, select "Teradata_Tpump_Upsert" or other "Teradata_Tpump_XXX" as the loader.
Click on the edit icon (that is, the pencil icon) next to the Connections - Value field to display the Connection Object Definition dialog.
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.
In the Attributes list, specify values for Error Table and Log Table.
Note:
This procedure is not specific to the Siebel source system. It must be performed for all types of source systems.If your source to target data movement configuration is Unicode to Unicode, you need to create a custom property called SiebelUnicodeDB on Integration Services. If your source to target data movement configuration is either Code Page to Code Page or Code Page to Unicode, you do not need to create this property. For more information on supported source to target configuration modes, and how to determine the source to target configuration for your environment, see Section 4.3.3, "Code Page and Data Movement Requirements."
To create and set the SiebelUnicodeDB custom property on Integration Services
Log into Informatica Administrator. For information on how to log into Informatica Administrator, see Section A.8, "How to Log Into Informatica Administrator."
Select the Integration Service.
In the Properties tab, scroll down to the Custom Properties area, and click Edit.
In the Name field, enter the following:
SiebelUnicodeDB
In the Value field, enter the following:
[user_OLTP]@[connectString_OLTP] [user_OLAP]@[ConnectString_OLAP]
Where:
[user_OLTP] is the database user for the OLTP source database. It must match exactly the value you entered for the User Name field when creating the relational connection for the OLTP in Informatica Workflow Manager.
[ConnectString_OLTP] is the connect string for the OLTP. It must match exactly the value you entered for the Connect String field when creating the relational connection for the OLTP in Informatica Workflow Manager.
[user_OLAP] is the database user for the Oracle Business Analytics Warehouse database. It must match exactly the value you entered for the User Name field when creating the relational connection for the data warehouse in Informatica Workflow Manager.
[ConnectString_OLAP] is the connect string for the data warehouse. It must match exactly the value you entered for the Connect String field when creating the relational connection for the data warehouse in Informatica Workflow Manager.
For example, 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 Section 4.12.2, "Configuring Connections in Informatica PowerCenter Workflow Manager".
This section describes how to set up DAC to receive email notification. It includes the following topics:
Follow this procedure to configure email recipients.
To configure email recipients in DAC
In DAC, navigate to the Setup view.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
Click the Email Recipients tab.
Click New.
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:
|
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). |
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
In the DAC menu bar, select Tools, then DAC Server Management, then DAC Server Setup.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
A confirmation dialog asks you to confirm that you want to configure the DAC Repository connection.
Click Yes.
The Server Configuration dialog appears.
Display the Email Configuration tab, and enter the email details for the email address to which you want to send DAC Server information emails.
Click Save.
Once the configuration has been completed, you can start the DAC Server.
When you first install and setup Oracle Business Intelligence Applications, you must configure the predefined repository connections and variables in the RPD file. This section explains the predefined connection pools and variables, and how to configure them using Oracle BI Administration Tool, and contains the following sections:
Section 4.15.1, "About the Predefined Connection Pools in the Oracle Business Analytics Warehouse"
Section 4.15.2, "How to Configure the Oracle BI Repository Connections"
Section 4.15.3, "How to Configure Oracle BI Repository Variables"
Section 4.15.4, "How to Configure Dynamic Data Source Names"
The Oracle BI repository contains the following predefined databases:
Oracle Data Warehouse
Oracle EBS OLTP
PeopleSoft OLTP
Siebel OLTP
Loyalty Input Data Source
You configure the connection pools for databases in the Physical layer in Oracle BI Administration Tool.
The Oracle Data Warehouse physical database has two predefined connection pools:
Oracle Data Warehouse Connection Pool. The Oracle Business Analytics Warehouse Connection Pool is the main connection pool in the Oracle BI Repository. You need to configure this connection pool to connect to your physical data warehouse. The connection is used by the session initialization blocks. You can use this connection pool to set up a dynamic data source name.
Oracle Data Warehouse Repository Initblocks Connection Pool. You need to configure the Oracle Data Warehouse Repository Initblocks Connection Pool to connect to the your physical data warehouse. The connection is used by the repository level initialization blocks. Repository level initialization blocks cannot be configured to use the dynamic data source name.
For information about configuring configuration pools, see Section 4.15.2, "How to Configure the Oracle BI Repository Connections".
You can also set up dynamic data source names, which allow an Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For more information about how to set up dynamic data source names, see Section 4.15.4, "How to Configure Dynamic Data Source Names".
You also need to configure the following Static variables:
OLAP_DSN. The value of the OLAP_DSN static variable is set to the data source name for the warehouse database.
OLAP_USER. The value of the OLAP_USER static variable is set to the database user name for the warehouse database.
OLAPTBO. The value of the OLAPTBO static variable is set to the database table owner for the data warehouse database.
You configure Static variables in Oracle BI Administration Tool using the Variable Manager (choose Manage, then Variables, and expand the Variables\Static node). For more information about configuring Static variables, see Section 4.15.3, "How to Configure Oracle BI Repository Variables".
The PeopleSoft OLTP, Siebel OLTP, and Oracle EBS OLTP databases each have two predefined connection pools. The actual databases in the RPD depend on which modules are licensed. The connection pools and their functions within each database are listed below.
Oracle EBS OLTP:
Oracle EBS OLTP DBAuth Connection Pool. The Oracle EBS OLTP DBAuth Connection Pool is used if database authentication is required.
Oracle EBS OLTP Connection Pool. The Oracle EBS OLTP Connection Pool is used to connect to the Oracle EBS OLTP system.
You also need to configure the following Static variables:
ORA_EBS_OLTP_DSN. The value of the ORA_EBS_OLTP_DSN static variable is set to the data source name for the Oracle EBS OLTP database.
ORA_EBS_OLTP_USER. The value of the ORA_EBS_OLTP_USER static variable is set to the database user name for the Oracle EBS OLTP database.
Siebel OLTP
Siebel OLTP DBAuth Connection Pool. The Siebel OLTP DBAuth Connection Pool is used if database authentication is required.
Siebel OLTP Connection Pool. The Siebel OLTP Connection Pool is used to connect to the Siebel OLTP system.
You also need to configure the following Static variables:
OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the Siebel OLTP database.
OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the Siebel OLTP database.
PeopleSoft OLTP
PeopleSoft OLTP DBAuth Connection Pool. The PeopleSoft OLTP DBAuth Connection Pool is used if database authentication is required.
PeopleSoft OLTP Connection Pool. The PeopleSoft OLTP Connection Pool is used to connect to the PeopleSoft OLTP system.
You also need to configure the following Static variables:
PSFT_OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the PeopleSoft OLTP database.
PSFT_OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the PeopleSoft OLTP database.
Loyalty Input Data Source:
Loyalty Input Connection Pool. The Loyalty Input Connection Pool is used to connect to the Loyalty OLTP system.
The section explains how to configure the repository connections used by Oracle Business Intelligence Applications. At a minimum, you need to configure the following:
the connection pool for the 'Oracle Data Warehouse' database.
the connection pool for each OLTP data source that you want to deploy (for example, Oracle EBS OLTP, PeopleSoft OLTP, Siebel OLTP).
For each connection pool that you configure, you need to:
Specify the database type for each connection pool (for more information, see Section 4.15.2.1, "How to specify the database type for connection pools").
Specify the connection details for each connection pool (for more information, see Section 4.15.2.1, "How to specify the database type for connection pools").
Note:
No additional configuration is required for Oracle's JD Edwards EnterpriseOne and JD Edwards World, as both use the standard Oracle Data Warehouse physical connection.You need to specify the database type for the Oracle Data Warehouse connection pool, and the connection pool for each OLTP data source that you wish to deploy.
To specify the database type for connection pools
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.
For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog."
In the Physical pane, double-click the Oracle Data Warehouse object.
Display the General tab.
Use the Database field to specify your database type.
Save the repository.
Click Yes to Check Global Consistency.
Repeat steps 1-6 for each OLTP data source that you want to deploy (for example, Oracle EBS OLTP, PeopleSoft OLTP, and Siebel OLTP).
Click OK when the Warnings are displayed.
You need to specify the connection details for the Oracle Data Warehouse connection pool, and the connection pool for each OLTP data source that you want to deploy.
To configure the Oracle BI Repository connection pools
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.
For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog."
In the Physical pane:
Expand the Oracle Data Warehouse node, and double-click the Oracle Data Warehouse Connection Pool to display the Connection Pool dialog.
Use the Data source name field to specify 'VALUEOF(OLAP_DSN)'.
Use the User name field to specify 'VALUEOF(OLAP_USER)'.
Use the Password field to specify your database password.
Note: The variable values specified in the VALUEOF functions are specified in the Variable Manager. For more information about specifying variable values, see Section 4.15.3, "How to Configure Oracle BI Repository Variables".
Repeat Steps a. to d. above for the other connection pools that you want to deploy.
For example, to configure an Oracle EBS OLTP, expand the Oracle EBS OLTP object, then double-click on Oracle EBS OLTP Connection Pool to display the Connection Pool dialog.
Save the repository.
Click Yes to Check Global Consistency.
Click OK when the Warnings are displayed.
The section explains how to configure the Oracle BI Repository variables.
Note: If you want to deploy multi-calendar with Oracle Financial Analytics, you must have enabled a number of Initialization Blocks that are disabled out-of-the-box. For more information, see Chapter 3 in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.
To configure the Oracle BI Repository variables
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file.
For the location of the OracleBIAnalyticsApps.rpd file, see Section 4.16, "Deploying the Metadata Repository and Presentation Catalog."
On the Manage menu, click Variables to display the Variable Manager.
Expand the Variables\Static node.
Edit the following variables, which apply to all deployments:
GLOBAL_CURRENCY1
GLOBAL_CURRENCY2
GLOBAL_CURRENCY3
OLAP_DSN
OLAP_USER
OLAPTBO
Notes:
The global currency values must match the Source System Parameter values in DAC.
In a SQL Server database environment, if you deploy an application that uses Dim_W_ORDER_ITEM_F_Contact_First_and_Last_Order_Dates (for example, Siebel Marketing 8.1.1.1), then you must set the value of the OLAPTBO session variable to 'dbo'. If you do not set this value, then you get the an error message similar to the following:
[SQL Server]Invalid object name @ 'SIEBEL.W_ORDERITEM_F'.. [nQSError: 16002]
Edit the following variables that are specific to your deployment:
For Oracle EBS OLTP data sources, edit the following variables:
ORA_EBS_OLTP_DSN (Oracle EBS-specific)
ORA_EBS_OLTP_USER (Oracle EBS-specific)
For PeopleSoft OLTP data sources, edit the following variables:
PSFT_OLTP_DSN (PeopleSoft-specific)
PSFT_OLTP_USER (PeopleSoft-specific)
For Siebel OLTP data sources, edit the following variables:
OLTP_DSN (Siebel-specific)
OLTP_USER (Siebel-specific)
Close the Variables Manager dialog.
To reset the Oracle BI Repository password
On the Manage menu, click Security, to display the Security Manager.
In the Security Manager dialog, click Users, and then:
Double-click on Administrator user to display the User dialog.
Use the Password field to change the password.
Repeat steps a- b for the SADMIN user.
Save and close the Security Manager.
Save the repository.
Click Yes to Check Global Consistency.
Click OK when the Warnings are displayed.
This sections explains how to create and configure dynamic data source names.
Dynamic data source names allow the Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For this you need to have your user authentication based on an external system (like LDAP), and add the following to your repository:
Create new session variables: Session_OLAP_DSN and Session_OLAP_USER.
Create a Session Init Block which uses 'Oracle Data Warehouse Repository Initblocks Connection Pool' to populate these session variables based on the user login.
Add this Initialization Block to the Execution Precedence list of the Authorization Initialization block.
Modify the values of Data Source Name and User Name fields in 'Oracle Data Warehouse Connection Pool' to be VALUEOF(Session_OLAP_DSN) and VALUEOF(Session_OLAP_USER) respectively.
For information about configuring connection pools, see Section 4.15.2.2, "How to specify connection details for connection pools".
Update the field password with the same value as of User Name.
After an Oracle BI Applications installation, the Metadata Repository file (RPD) and Presentation Catalog are located on the installation machine. You must use Fusion Middleware Control to deploy and configure the Oracle BI Applications RPD file and Presentation Catalog files. The target Oracle BI EE machine can be the installation machine, or a separate machine.
For information about extending and scaling a deployment, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
To deploy the Metadata Repository and Presentation Catalog:
Copy the Metadata Repository and Presentation Catalog to the target Oracle BI EE machine, as follows:
Table 4-8 Copying Oracle BI EE Components to an Oracle BI EE Machine
Copy these files | From here: | To here: |
---|---|---|
OracleBIAnalyticsApps.rpd |
Installation machine: ORACLE_HOME |
Target Oracle BI EE machine:
|
Installation machine: ORACLE_HOME |
Target Oracle BI EE machine:
The EnterpriseBusinessAnalytics.zip file must be unzipped and the contents copied into this \catalog\ directory. |
Note: The target Oracle BI EE machine can be the installation machine, or a separate machine.
After copying the RPD file, you can verify the repository by using Oracle BI Administrator to open the OracleBIAnalyticsApps.rpd file. When you first open the RPD file, use Admin123 as the password to access the repository, then Oracle recommends that you use the File\Change Password option to change the password.
To open the OracleBIAnalyticsApps.rpd file using the Oracle Business Intelligence Administration Tool, use the password Admin123.
Note:
You should change the default password. Use the Oracle Business Intelligence Administration Tool to do so. For instructions, see the Oracle Business Intelligence Server Administration Guide.Use Oracle Fusion Middleware Control to deploy the Metadata Repository and Presentation Catalog, as follows:
In Fusion Middleware Control, navigate to the Business Intelligence Overview page.
For example, connect to http://<hostname>:7001/em, and select <Farm>, then Business Intelligence, then coreapplication, then Overview.
Display the Repository tab of the Deployment page.
On the Repository tab, you can view the name of the current published repository (called the Default RPD).
Click Lock and Edit Configuration to allow changes to be made.
To publish the repository in shared mode, select Share Repository and provide a Shared Location.
If you do not select this option, then the repository that you upload on this page is published in distributed mode by default.
Use the Upload BI Repository Server area to specify the location of the OracleBIAnalytics.rpd file and the repository password.
Click the Help button on the page to access the page-level help for the following options: Repository File option, Repository Password option.
Use the BI Presentation Catalog area to specify the location of the BI Presentation Catalog.
Click the Help button on the page to access the page-level help for the following options: Catalog Location option.
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
If your deployed BI EE system with Oracle BI Applications is different from the BI EE system used to install Oracle BI Applications, then you must apply the security policy to the BI Domain on the deployed BI EE system by following the steps below.
If your deployed BI EE system with Oracle BI Applications is the same as the BI EE system used to install Oracle BI Applications, then the Oracle BI Applications installer performs this configuration automatically.
To apply the Oracle BI Applications Security Policy to the BI Domain:
Note: Machine A is the installation machine. Machine B is the deployment machine.
Shutdown all processes in the BI EE system. Specifically:
the Administration Server
(if there is a cluster) all managed servers in the bi_cluster cluster
all opmn managed processes
On machine B, backup and rename the existing DOMAIN_HOME/config/fmwconfig/system-jazn-data.xml.
For example, if the BI EE root folder is named OracleBIEE11g, then the domain folder location (on Windows) might be C:\OracleBIEE11g\user_projects\domains\bifoundation_domain\config\fmwconfig
.
Copy the Oracle BI Applications jazn file from machine A at ORACLE_HOME/biapps/admin/provisioning/system-jazn-data.xml to machine B at DOMAIN_HOME/config/fmwconfig.
Start all the processes in the BI EE system for the Oracle BI Applications security policy to take effect. Specifically:
the Administration Server
(if there is a cluster) all managed servers in the bi_cluster cluster
all opmn managed processes
This section provides configuration steps that might be required, depending on your specific environment.
For mandatory Siebel-specific configuration tasks, see Section 4.18.4, "Siebel-Specific Configuration Tasks."
For mandatory Teradata-specific configurations tasks, see Section 4.18.5, "Teradata-Specific Configuration Tasks."
Note:
After you complete the tasks in this section and before you run the first ETL load process, you may need to perform additional configuration steps depending on your environment. For additional information about source system-specific and application-specific mandatory configuration tasks, see Section 2.1, "High-Level Overview of Configuring Oracle BI Applications," in Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.This section contains the following topics:
You may need to configure the security-related initialization blocks that are provided with Oracle BI Applications to work with your particular source system. For information about the Oracle BI Applications security model, see Oracle Business Intelligence Applications Security Guide. For information about configuring initialization blocks, see Oracle Business Intelligence Server Administration Guide.
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
In DAC, go to the Design view.
For more information about logging into DAC, see Section A.1, "How to Log into DAC."
Make sure that you have selected the correct container from the containers drop-down list. You cannot edit preconfigured containers. Make a copy of an existing container in order to make edits.
Display the Source System Parameters tab.
Note:
For information about recommended settings for specific databases, see Chapter 3, "Preinstallation and Deployment Requirements for Oracle BI Applications".Use the Edit tab below the list of Source System Parameters to change the value of parameters in the list.
Click Save.
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 ORACLE_HOME\biapps\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 ORACLE_HOME\biapps\dwrep\siebproc\db2udb\win32\.
Each platform-specific directory contains the following sub-directories:
\siebproc\ (containing stored procedures for a 32-bit DB2 environment)
\siebproc64\ (containing stored procedures for a 64-bit DB2 environment)
These directories also contain the files siebproc.sql and sqlproc.ksh, which are used to create the function. For more information, see Section 4.18.3.2, "How to Create DB2 Stored Procedures").
This section describes how to verify the existence of stored procedures.
To verify the existence of the stored procedures
From a DB2 command prompt or control center, issue the following SQL scripts:
db2 => create table test_siebproc(id int); DB20000I The SQL command completed successfully. db2 => insert into test_siebproc(id) values (1); DB20000I The SQL command completed successfully. db2 => call siebtrun('TEST_SIEBPROC'); SQL0805N Package "NULLID.SYSSH200" was not found. SQLSTATE=51002
Note:
If you get an error message, you do not have the required stored procedures.Creating stored procedures must be performed by the database administrator (for more information, see Section 4.18.3.2, "How to Create DB2 Stored Procedures").
This section describes how to create DB2 stored procedures.
To create DB2 stored procedures
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 ORACLE_HOME\biapps\dwrep\siebproc\db2udb\win32\siebproc\ to the directory \SQLLIB\function\ on the DB2 server side.
Note:
For more information about the location of DB2 stored procedures, see Section 4.18.3, "Creating Stored Procedures for DB2-UDB").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.
This section contains tasks you are required to complete if your source system is Siebel. This section contains the following topics:
Note:
The steps in this section are required if are using a Siebel source system.This section explains how to update Siebel transactional database schema definitions. It contains the following sections:
Section 4.18.4.1.1, "How to Run the DDLIMP Tool From The Command Line"
Section 4.18.4.1.2, "How to Apply Siebel CRM Schema Changes For Oracle, DB2/UDB, MSSQL"
Section 4.18.4.1.3, "How to Apply Siebel CRM Schema Changes for DB2 on OS/390 and z/OS"
Section 4.18.4.1.4, "How to Apply the Siebel CRM Image Table to a Siebel Transactional Database"
Section 4.18.4.1.6, "How to Verify Siebel (CRM) Schema Changes"
When you use the DDLIMP utility from a command line to update schema definitions, refer to the following notes:
To run DDLIMP from command line, run the following command:
ORACLE_HOME\biapps\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_DSN> /GSSE_ROLE [/W Y] [/Z Y] /F <ORACLE_HOME\biapps\dwrep\DDL_OLTP.CTL> /L <ORACLE_HOME\biapps\dwrep\DDL_OLTP.log>
Note: Where <ODBC_DSN> is the ODBC connection created as described in section Section 4.7.4, "Creating ODBC Database Connections for the DAC Client".
Additionally you can use the following commands:
/W Y (if the OLTP database is Unicode).
/Z Y (if the OTTP database is DB2 and Unicode).
/B <TABLE_SPACE_NAME> if you want to create these table in a separate table space.
/X <INDEX_TABLE_SPACE_NAME> if you want to create the indexes in a separate table space.
/Y Storage File for DB2/390.
You can obtain a complete list of DDLIMP parameters by running DDLIMP in a command line. DDLIMP is located in the ORACLE_HOME\biapps\dwrep\bin.
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
On the machine where Oracle BI Applications is installed, run the following command:
ORACLE_HOME\biapps\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC connect string> /G SSE_ROLE /F ORACLE_HOME\biapps\dwrep\DDL_OLTP.CTL /L ORACLE_HOME\biapps\dwrep\DDL_OLTP.log
Note:
If you are applying schema changes to a Siebel CRM release 6.3, specify DDL_OLTP_63.CTL as the /F parameter instead of DDL_OLTP.CTL.For example:
DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE /F ORACLE_HOME\biapps\dwrep\DDL_OLTP.CTL /L ORACLE_HOME\biapps\dwrep\DDL_OLTP.log
Notes:
/P <PASSWORD> - The password for Oracle's CRM OLTP.
/C <ODBC connect string> - The name of the ODBC connect string.
For Oracle databases, use the Oracle Merant ODBC Drivers (installed with Oracle BI Applications).
In addition, you can use the following commands:
/W Y - (if the OLTP database is Unicode).
/Z Y - (if the OLTP database is DB2 and Unicode or the OLTP database is MS SQL Server and Unicode).
/B <TABLE_SPACE_NAME> - If you want to create these table in a separate table space.
/X <INDEX_TABLE_SPACE_NAME> - If you want to create the indexes in a separate table. space.
/Y - Storage File for DB2/390.
Restart all servers.
Edit the following parameters in the Storage control files located in ORACLE_HOME\biapps\dwrep\STORAGE_DDL_OLTP.CTL:
%1 - Replace with a 2 character database name.
%indBufPool - Replace it with a index buffer pool name.
%4kBulfPool - Replace it with a 4k TBS buffer pool name.
%32kBufPool - Replace it with a 32K TBS Buffer Pool name.
On the machine where Oracle BI Applications is installed, run the following command:
ORACLE_HOME\biapps\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /G SSE_ROLE /F ORACLE_HOME\biapps\dwrep\DDL_OLTP_DB290.CTL /L ORACLE_HOME\biapps\dwrep\DDL_OLTP.log /5 Y /A <SCHEMA_OWNER> /Y ORACLE_HOME\biapps\dwrep\STORAGE_DDL_OLTP.CTL
Notes:
/P <PASSWORD> - The password for Oracle's CRM OLTP.
/C <ODBC connect string> - The name of the ODBC connect string.
For Oracle databases, use the Oracle Merant ODBC Drivers.
In addition, you can use the following commands:
/W Y - (if the OLTP database is Unicode).
/Z Y - (if the OLTP database is DB2 and Unicode or the OLTP database is MS SQL Server and Unicode).
/B <TABLE_SPACE_NAME> - If you want to create these table in a separate table space.
/X <INDEX_TABLE_SPACE_NAME> - If you want to create the indexes in a separate table. space.
/Y - Storage File for DB2/390.
To create indexes for the tables created, run the following SQL Script from the DB2 command line utility connected to your OLTP Schema:
ORACLE_HOME\biapps\dwrep\Create_OLTP_Db2390_index.sql
In DAC, select Design, then Tables, and query for tables where the Image Suffix value is not null.
Right click over the returned tables, then select 'Change Capture scripts', then 'Generate image and trigger scripts' to display the Triggers And Image Tables dialog.
At the Triggers And Image Tables dialog, do the following:
Select the All Tables In The List radio button.
Select the Generate Image Table Scripts check box.
Select the appropriate Database type.
Click OK to generate the database scripts.
DAC generates the scripts in a pop-up page.
Execute the scripts in your OLTP database.
Note:
Delete triggers are only used with Siebel CRM databases.Delete records in Siebel CRM sources are not propagated to the data warehouse tables. However the mechanism to identify the delete records is provided, as follows:
DAC can create delete triggers on source tables (refer to Oracle Business Intelligence Data Warehouse Administration Console User's Guide). These triggers write the primary keys of deleted records with the flag D in the corresponding S_ETL_I_IMG table. You need to write a custom SDE extract to pull these deleted primary keys from the image table and take corresponding action on the data warehouse table.
After applying Siebel (CRM) and Oracle E-Business Suite schema changes, you need to verify that appropriate tables were created in the transactional database.
Use a SQL tool to make sure that the following tables were created in the transactional database:
S_ETL_R_IMG_xxx
S_ETL_I_IMG_xxx
S_ETL_D_IMG_xxx
S_ETL_PARAM
S_ETL_PRD_ATTR
S_ETL_PRD_REL
This section contains tasks you are required to complete if you are using a Teradata database in your Oracle BI Applications environment.
This section contains the following topics:
Section 4.18.5.2, "Setting Up the HOSTS File for Teradata Installations on Windows"
Section 4.18.5.3, "Setting Up the HOSTS File for Teradata Installations on UNIX"
Section 4.18.5.4, "Teradata-Specific Installation Checklist"
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
In Informatica PowerCenter Workflow Manager, drag a Workflow into the Workflow Designer pane.
In the Workflow Designer pane, double click the Task (for example, SDE_PSFT_APTermsDimension) to display the Edit Tasks dialog.
Display the Mappings tab.
Select Target in the left pane. In the Properties section, click on the Set File link.
In the Flat Files - Targets dialog, in the File Properties area, click Advanced.
In the Fixed Width Properties - Targets dialog, in the Code Page area, select UTF-8 encoding of Unicode.
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
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.
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.
Save the HOSTS file.
For more information about setting the TDPID parameter, see the Teradata documentation.
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
How to Set Up the HOSTS File for Teradata Installations on UNIX.
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.
Save the HOSTS file.
For more information about setting the TDPID parameter, refer to the Teradata documentation.
Table 4-9 provides a list of Teradata-specific installation and configuration steps that are performed during the Oracle BI Applications installation and configuration process. You should review this list to make sure that you have performed all of the required Teradata-specific steps.
Table 4-9 Teradata-Specific Installation Checklist for Windows
Action | Link to Topic |
---|---|
Review Teradata-specific database guidelines. |
Section 3.5, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse". |
Create or drop data warehouse tables. |
Section 4.9.2, "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. You must use Informatica Administrator. |
Section 4.6.5, "Restoring the Pre-built Informatica Repository" |
Configure the source and data warehouse database relational connections in Informatica PowerCenter Workflow Manager. You cannot use the DAC's Data Warehouse Configurator to configure relational connections. |
Section 3.5, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse" |
Set the Code Page File Property for Unicode Environments. |
|
Set up the HOSTS file. |
Section 4.18.5.2, "Setting Up the HOSTS File for Teradata Installations on Windows" |
After you have installed and configured Oracle BI Applications, your Oracle Business Analytics Warehouse (OLAP) database is empty. You need to perform a full load ETL to populate your Oracle Business Analytics Warehouse.
The ETL processes for Oracle BI Applications are created and managed in DAC. For detailed information about running ETLs in DAC, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
For an example of using DAC to run a full load ETL, see Section 4.19.1, "An Example of Running a Full Load ETL."
This section uses an example to show you how to get started quickly with running a full load ETL. In this example, you have installed Oracle Financial Analytics with an Oracle EBS OLTP data source, and you want to load OLTP data for the subject area Receivables, as follows:
In the DAC menu bar, select File, then New Source System Container, to display the New Source System Container dialog, and specify details of the new container.
Note: You cannot make any changes to the preconfigured containers. You must make a copy of a container before you can make any changes to it.
For more information about logging into DAC, see Section A.1, "How to Log into DAC." For more information about the source system container functionality in DAC, see Oracle Data Warehouse Administration Console User's Guide.
Select the Create as a Copy of Existing Container radio button, and select the appropriate container from the Existing Containers drop-down list, then click OK.
In the Design view, display the Source System Parameters tab and set the parameters appropriately.
In the Setup view, display the DAC System Properties tab and set the properties appropriately.
Display the Execute view, and display the Execution Plan tab.
Click New, display the Edit tab, and use the Name field to specify a name for the ETL process.
Display the Subject Areas tab and click Add/Remove to display the Choose Subject Areas dialog.
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.
Select Financials - Receivables, click Add, then click OK.
Display the Parameters tab, and click Generate.
On the Parameters tab, edit the parameters as follows:
Edit the value of DBConnection_OLAP and set it to the same value as the name of the OLAP database that you specified in the Physical Data Source dialog (for example, DataWarehouse).
Edit the value of DBConnection_OLTP and set it to the same value as the name of the OLTP database that you specified in the Physical Data Source dialog (for example, ORA_11_5_8).
If there is a FlatFileConnection parameter, edit the value of FlatFileConnection and set it to the same value as the name of the flat file data source that is specified in the Physical Data Source dialog (for example, ORA_11_5_8_Flatfile).
On the Execution Plans tab, click Build.
On the Execution Plans tab, click Run Now.
DAC will perform a full load for Financials - Receivables.
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.