Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console
11g Release 1 (11.1.1)

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

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Setup Tasks and Concepts for DAC Administrators

This chapter provides information about the administrative tasks that must be performed in order for a DAC environment to be operable. It also includes various concepts related to DAC operations that a DAC administrator should be aware of.

Note: If you installed and configured DAC using the Oracle BI Applications installation process and by following the post-installation instructions in Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications, you do not need to repeat the same tasks that appear in this chapter.

This chapter contains the following topics:

About DAC Authentication Modes

When the DAC Client, automation utilities, or command line utilities connect to the DAC Server or DAC repository, they use either Fusion Middleware mode (the option appears in the DAC Client as "FMW") or DAC standalone authentication mode (the option appears in the DAC Client as "DAC"). See "DAC Authentication in Oracle Fusion Middleware (WebLogic Server) Mode" and "DAC Authentication in Standalone Mode" for topology diagrams that illustrate these two authentication modes.

Fusion Middleware Mode

The Fusion Middleware mode uses the Fusion Middleware security model. This model requires the DAC repository to be configured for Web mode and a valid DAC Server URL to be present in the DAC repository. Authentication occurs through Web service calls to the DAC enterprise application deployed in WebLogic Server. The DAC enterprise application that the URL points to must be running for the authentication to occur.

DAC Standalone Authentication Mode

DAC standalone authentication mode uses the DAC user account security model. DAC user account information is stored in the DAC repository. A DAC user with the Administrator role creates user accounts with the appropriate user names, passwords, and roles. The DAC administrator needs to distribute the user account information (name and password) to the users to enable them to log into the DAC Client and repository. In standalone mode, the DAC Server does not participate in user authentication.

Using the DAC standalone authentication mode, a user can connect to a DAC repository that is configured for either Fusion Middleware or standalone mode. However, when the DAC repository is in Fusion Middleware (Web) mode and the user is authenticated through standalone mode, the user cannot run ETLs, because the user cannot connect to the DAC Server. When standalone mode is used for both the user and DAC repository, connecting to the DAC Server is possible, and, therefore, ETLs can be run.

About the DAC Authentication File

The DAC authentication file is a cwallet.sso file and is used by the DAC Client and automation utilities to connect to the DAC repository. It contains the repository database schema owner and password and a DAC encryption key. An authentication file and a DAC repository have a one-to-one relationship.

The authentication file is automatically generated when the Oracle BI Applications Installer is run or when the BI domain is extended by running the install_dwtools.py installation script. When automatically generated, the authentication file is saved by default in <DAC_Config_Location>\conf-shared\security\repository.

The authentication file can also be created by a DAC user at the time of the first connection to the DAC repository. The first time users connect to a DAC repository, they must either specify an authentication file that was previously created or create a new authentication file. When creating a new authentication file, the user must specify the database schema owner and password.

A DAC administrator needs to distribute the authentication file to users who need access to the repository.

Logging Into DAC for the First Time as an Administrator

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

The initial login process for an administrator varies depending on the authentication mode being used and whether an authentication file was already created.

If you are using Fusion Middleware authentication, the authentication file was created during the Oracle BI Applications set up and configuration process. You will need to know the location of this file when you log into DAC for the first time.

If you are using DAC standalone authentication, you will create an authentication file while logging into DAC for the first time. To do so, you will need to know the database schema owner and password.

Regardless of the authentication mode, an administrator must distribute the authentication file to all user accounts (those authenticated through DAC as well as through WebLogic Server) that need to access the specified DAC repository. For more information about the authentication file, see "About the DAC Authentication File".

To log in to DAC for the first time:

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

    The Login... dialog is displayed.

    This dialog box is described in the surrounding text.
  2. Click Configure.

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

  4. Enter the appropriate connection information:

    Field Required Value

    Name

    Enter a unique name for the connection to the DAC repository.

    Connection type

    Select the type of database in which the DAC repository will be stored.

    Connection String, or Database name, or TNS Name, or Service Name

    Select the database name or database account name of the DAC repository.

    If you are using:

    • Oracle (OCI8), use the tnsnames entry.

    • Oracle (Thin), use the service name.

    • SQL Server, use the database name.

    • DB2-UDB, use the connect string as defined in the DB2 configuration.

    Database Host

    Enter the name of the machine where the DAC repository will reside.

    Database Port

    Enter the port number on which the database listens. For example, for an Oracle database the default port is 1521, or for a SQL Server database the default port is 1433.

    DB URL (Optional)

    Can be used to override the standard URL for this connection.

    DB Driver (Optional)

    Can be used to override the standard driver for this connection.

    Authentication File

    Click in this field to do one of the following:

    • Select an existing authentication file. Proceed to step 5 for detailed instructions.

    • Create a new authentication file. Proceed to step 6 for detailed instructions.

    SSL Trust Store File

    (Optional) For deployments in Web mode, location of the SSL Trust Store file.

    SSL JKS Password File

    (Optional) For deployments in Web mode, location of the SSL JKS password file.

    Log Level

    Specifies a client log level for the client session. The logs are saved in <Domain_Home>\dac\log\client <client's logical connection name>.


  5. To select an existing authentication file, do the following:

    1. Click in the Authentication File field.

    2. In the Authentication File dialog, select Choose existing authentication file, and click OK.

    3. Navigate to the appropriate directory, and select the appropriate cwallet.sso file. Click OK.

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

    5. Click Apply, and then click Finish.

      Note:

      A DAC administrator must distribute this authentication file to all user accounts that need to access this DAC repository. Do not rename this file.

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

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

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

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

    4. In the Create Authentication File dialog, enter the Table Owner Name and Password for the database where the repository will reside.

    5. Click Generate Random Encryption Key to generate an encryption key for the authentication file. The key is displayed in the Key field.

      Alternatively, you can enter a key in the Key field. The key must be at least 24 characters long.

    6. Click OK to close the Create Authentication File dialog.

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

      Note:

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

  7. Click Apply, and then click Finish

  8. To log in using Web mode, do the following:

    1. In the Login... dialog, select the appropriate Connection from the drop-down list.

    2. Enter your User Name and Password.

      This must match the user name and password stored in the WebLogic Server identity store.

    3. Select FMW as the Authentication Type.

    4. If you want DAC to remember the password for this connection, select Remember Password.

    5. Click Login.

      The DAC Client is displayed.

  9. To log in using DAC standalone mode, do the following:

    1. In the Login... dialog, select the appropriate Connection from the drop-down list.

    2. For a first-time login, you can enter any values in the User Name and Password fields, because the DAC repository that you just created does not contain any user names or passwords.

      A user account will be created with the user name and password you enter in this step. This user account is automatically assigned the Administrator role.

    3. Select DAC as the Authentication Type.

    4. If you want DAC to remember the password for this connection, select Remember Password.

    5. Click Login.

  10. (Optional) To specify the DAC view and tab that you want to appear each time you log in, click Options.

  11. If a DAC repository has not already been created, you will be prompted to create one. Click Yes.

    This process creates DAC repository tables.

    Depending on your database type, you may have the option to 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.

Managing the DAC Server

This section contains the following topics:

Accessing the DAC Server

You use the DAC Server to manage data warehouse ETL processes. The DAC Server can run in standalone mode or Web mode.

  • In standalone DAC mode, the DAC Server connects to other components in the DAC environment using TCP/IP. When the DAC Server runs in standalone mode, the DAC repository must also be configured as standalone (by selecting Tools, DAC Server Management, Repository Configuration). For a description and topology of standalone mode, see "DAC Authentication in Standalone Mode".

  • In Web mode, the DAC Server runs as an enterprise application on WebLogic Server. The DAC repository must also be configured as Web mode. For a description and topology of Web mode, see "DAC Authentication in Oracle Fusion Middleware (WebLogic Server) Mode".

You can access the DAC Server using the following methods:

  • DAC Client. The DAC Client is a command and control interface that enables you to perform schema management tasks, and configure, administer and monitor execution plans. The DAC Client accesses the DAC Server through TCP/IP. You can access the DAC Client through the Start menu or by double-clicking the startclient.bat file in the <Domain_Home>\dac directory.

  • Oracle Enterprise Manager Fusion Middleware Control MBean Browser. You can view information about running, failed and queued execution plans and the status of the DAC Server. See "Viewing DAC Metrics Using Fusion Middleware Control MBean Browser" for information about accessing DAC through Fusion Middleware Control MBean Browser.

  • Oracle WebLogic Server. The DAC Server runs as an enterprise application on the WebLogic Server. You can monitor the status of the DAC Server application through the WebLogic Server Administration Console. Only one DAC Server application can be run against the DAC repository at one time. Therefore, the DAC Server application should be deployed to only one managed server. If more than one DAC Server application is run against the DAC repository, the repository will be corrupted.

    See "Monitoring the DAC Server Using WebLogic Server" for information about accessing the DAC Server through the WebLogic Server Administration Console.

  • Command Line. You can access the DAC Server using the command line in order to start and stop execution plans and to get status information for servers, databases, and execution plans. See "Accessing the DAC Server Using the Command Line" for more information.

  • WSDL File. You can use a WSDL (Web service definition language) file in order to write service calls to the DAC Server. To access the WSDL file, invoke the following URL:

    http//<host of the managed WebLogic Server>:<port of the managed WebLogic Server>/DACServer/DACServerManagerService?wsdl
    

    where the name of the DAC Server enterprise application is DACServer.

About DAC Server High-Availability

The DAC Server can be configured for high-availability. This process involves scaling out the Oracle BI Applications installation. For more information about scaling out Oracle BI Applications, see Oracle Fusion Applications Enterprise Deployment Guide.

The DAC Server uses an active-passive high-availability configuration. There can be only one active DAC Server and one or more passive servers. Once you have scaled out Oracle BI Applications, the DAC Server failover process occurs automatically. The scale out process uses the server migration approach, which is explained in "DAC Server Migration Approach to Failover". For more information about configuring DAC for high-availability, see the section titled "Configuring Data Warehouse Administration Console for High-Availability," in Oracle Fusion Applications Enterprise Deployment Guide.

Note:

If DAC was installed using the Simple Install option in the Oracle BI Applications Installer, you will not be able to scale out DAC.

DAC Server Migration Approach to Failover

Figure 4-1 shows the architecture of how DAC can be deployed to achieve high availability and failover capability.

Figure 4-1 DAC Server Migration Approach to Failover

This graphic is described in the surrounding text.

In the BI cluster, when one of the managed servers fails unexpectedly, the second managed server will automatically continue an ETL execution from the point at which it stopped. To enable this operation, the DAC system property "Auto Restart ETL" must be set to True (in the DAC System Properties tab in the Setup view). When the second managed server is automatically started, the execution graph status is reconstructed from the run history tables.

The following scenarios are possible for individual workflows that were started but not completed by the first managed server:

  • If the workflow is still running on the Informatica Integration Service grid, DAC will wait for it to complete.

  • If the workflow completed, DAC will not restart this workflow.

  • If the workflow failed, DAC will restart the workflow.

The second managed server is also capable of authenticating any DAC user defined in a common credential store.

How DAC High Availability Relates to Informatica

The Informatica binaries and domains file need to be on a shared network location that is highly available. DAC will be configured to use them at the time the first managed server is created by the installer.

DAC uses the Informatica pmrep and pmcmd command line programs to communicate with the Informatica Integration Service and the Repository Service.

  • pmcmd.exe - Used to start and stop workflows and to get workflow status.

  • pmrep.exe - Used to get the list of sessions used in a workflow.

In addition, Oracle recommends that the DAC Server and Informatica Integration Service are on a shared network drive, because DAC produces parameters files that are consumed by Informatica.

For information about setting up Informatica for high availability, see the Informatica documentation.

Configuring the DAC Repository to Allow DAC Server Connections

In this procedure you specify which DAC Server will be allowed to connect to the DAC repository. Only one DAC Server at a time should connect to a DAC repository. However, you can specify alternative DAC Server hosts that will be allowed to connect to the DAC repository if the main DAC Server host fails. In standalone mode, you must manually start an alternative DAC Server. In Web mode, if high availability is configured, the alternative DAC Server will start automatically.

To configure the DAC repository to allow DAC Server connections

  1. In the DAC Client, on the Tools menu, select DAC Server Management, and then select Repository Configuration.

  2. In the Repository Configuration dialog, enter the following information:

    Field Description

    Mode

    Select the DAC Server mode.

    Possible values are Web and Standalone.

    Web Mode Configuration: URL

    If you are using Web mode, enter the URL for the DAC enterprise application that is deployed on WebLogic Server.

    Standalone Mode Configuration:

    If you are using standalone mode, enter the following information:

    • Host. DAC Server host machine

    • Alternative Hosts. Additional machines hosting DAC Servers that can be used to connect to the DAC repository if the main DAC Server host machine fails. Enter commas between multiple host names.

    • Port. DAC Server port.


  3. Click Save.

Configuring the Connection Between the DAC Server and DAC Repository

On Windows, you can use the DAC Client to configure the connection between the DAC Server and the DAC repository if the DAC Client and DAC Server are running in the same \dac folder.

Optionally, or to configure a DAC Server installed in another folder or on another Windows machine, use the serverSetupPrompt.bat file to configure the repository connection from the additional machine or folder

On UNIX or Linux, use the serverSetupPrompt.sh script to configure the connection between the DAC Server and the DAC repository.

Note the following points:

  • In Web mode, you must first complete the procedure "Configuring the DAC Repository to Allow DAC Server Connections" before you perform the procedure in this section.

  • In standalone mode, you must configure the connection between the DAC Server and DAC repository from the location where the DAC Server will be running.

  • Only one DAC Server at a time should connect to a DAC repository. It is possible to configure connections between multiple DAC Servers and a single DAC repository, but only one DAC Server at a time should be allowed to connect to the DAC repository.

To use the DAC Client to configure the connection between the DAC Server and the DAC repository:

  1. Make sure an authentication file (cwallet.sso) has been created and placed in the <DAC_Config_Location>\conf-shared\security\repository directory.

  2. In the DAC Client, on the Tools menu, select DAC Server Management, and then select DAC Server Setup.

  3. Click Yes to display the Server Configuration dialog.

  4. In the Repository Connection Information tab, click Populate from preconfigured client connection. This will populate the fields with connection details from the DAC Client.

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

  6. Click Save.

To use the serverSetupPrompt files to configure the connection between the DAC Server and the DAC repository:

  1. Run the serverSetupPrompt script, as follows:

    • On Windows, double-click the serverSetupPrompt.bat located in the <Domain_Home>\dac directory.

    • On UNIX or Linux, run serverSetupPrompt.sh located in the <Domain_Home>/dac directory.

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

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

  4. Enter the appropriate connection information for the database.

  5. Enter 2 to test the DAC repository connection.

  6. Enter 5 to save changes.

  7. Enter 6 to exit.

Starting and Stopping the DAC Server (Standalone Mode)

This section explains how to start and stop the DAC Server when the DAC Server is running in standalone mode.

To start or stop the DAC Server in standalone mode:

  • On Windows, do one of the following:

    • Select Start, Programs, Oracle Business Intelligence, Oracle DAC, and then Start Server or Stop Server.

    • Go to the <Domain_Home>\dac folder and double-click the startserver.bat or stopserver.bat file, depending on which action you want to perform.

  • On UNIX or Linux, issue one of the following commands:

    ./startserver.sh
    
    ./stopserver.sh
    
  • On AIX, issue one of the following commands:

    ./startserver_aix.sh
    
    ./stopserver_aix.sh
    

    Note: When you execute ./stopserver.csh or ./stopserver.sh, the server will issue a warning about the shutdown request. When the server receives the request, it will shut down even if there is an ETL in progress. The statuses for the ETL run will not be set correctly. The next time the DAC Server starts, it will set the status to Failed for any uncompleted run.

    Tip:

    When you start the DAC Server, look at the DAC Server status icon in the DAC console to make sure that the DAC Server has started. The DAC Server status icon should either be orange (idle) or green (active). If Accessibility mode is enabled, the icon for an idle DAC Server is replaced with the text "Connected to idle DAC Server," and the icon for an active DAC Server is replaced with the text "Connected to active DAC Server."

    Note: In Web mode, the process for the DAC Server to move from idle status to active can take a number of minutes.

To run the DAC Server in the background:

  • In bash-related shells, issue the following command:

    nohup startserver.sh 2>&1 &
    

    The nohup command allows the DAC Server to run in the background on UNIX.

    Note: To stop the DAC Server running as a background process, use stopserver.sh or stopserver.csh.

Note: When you execute ./stopserver.csh or ./stopserver.sh, the server will issue a warning about the shutdown request. When the server receives the request, it will shut down even if there is an ETL in progress. The statuses for the ETL run will not be set correctly. The next time the DAC Server starts, it will set the status to Failed for any uncompleted run.

Tip:

When you start the DAC Server, look at the DAC Server status icon in the DAC console to make sure that the DAC Server has started. The DAC Server status icon should either be orange (idle) or green (active). If Accessibility mode is enabled, the icon for an idle DAC Server is replaced with the text "Connected to idle DAC Server," and the icon for an active DAC Server is replaced with the text "Connected to active DAC Server."

Note: In Web mode, the process for the DAC Server to move from idle status to active can take a number of minutes.

Starting and Stopping the DAC Server (Web Mode)

When the DAC Server is running in Web mode, you can start and stop it using the following methods:

Setting Up Email Notifications in the DAC Client and Server

This section provides instructions for setting up users to receive email notifications about ETL statuses, task statuses, and data source usage. To set up email notifications, you need to complete the steps in both of the following procedures:

For data source usage notifications, you need to complete additional configuration steps, as described in "Setting Up Extract Delays, Event Delays and Data Source Notifications".

Configuring Email Recipients in the DAC Client

Follow this procedure to configure email recipients in the DAC Client.

To configure email recipients in the DAC Client:

  1. In the Setup view, click the Email Recipients tab.

  2. Click New in the toolbar.

  3. In the Edit subtab, enter the following information:

    Field Description

    Name

    Logical name of the user to be notified.

    Email Address

    Email address where the notification will be sent.

    Notification Level

    The notification levels are as follows:

    • 1 -- Notifies recipient of ETL statuses Failed and Aborted.

    • 5 -- Notifies recipient of ETL statuses Failed, Aborted, and Completed; and data source usage.

    • 10 -- Notifies recipient of the same notifications as level 5 and the tasks status Failed.

    Inactive

    Indicates whether the email notification for this recipient is active or inactive.


Configuring Email in the DAC Server

Follow this procedure to configure the email administrator account in the DAC Server to enable recipients to receive email notifications.

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

Note the following points:

  • In Web mode, you must first complete the procedure "Configuring the DAC Repository to Allow DAC Server Connections" before you perform the procedure in this section.

  • In standalone mode, you must configure email administrator account from the location where the DAC Server will be running.

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

To configure the email administrator account in the DAC Server:

  1. In the DAC Client, on the Tools menu, select DAC Server Management, and then select DAC Server Setup.

  2. Click Yes.

    The Server Configuration dialog appears.

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

    Field Description

    User Name

    User name for the email account.

    Password

    User password on the email server. (Only required if you select Needs Authentication.)

    Email Server

    Host name of the email server.

    Email Server Port

    Port where the email server listens.

    Email Address

    Email address of the user.

    Needs Authentication

    Specifies whether the corporate email server requires authentication.

    Needs SSL

    Specifies whether an SSL connection is required.


  4. Click Send Test Email to test the connection.

  5. Click Save.

Setting Up Physical Data Sources

This section provides instructions for specifying the connection pool information for the transactional and data warehouse databases.

To specify transactional and data warehouse data source connections:

  1. In the Setup view, select the Physical Data Sources tab.

    The Physical Data Sources tab displays a precreated record for the data warehouse with the name DataWarehouse, and one or more records for the transactional sources.

  2. For each record, enter the following information in the Edit subtab:

    Field Description

    Name

    Logical name for the data warehouse or transactional database connection pool. You should not change the names of the precreated records.

    Note: When you create an execution plan to perform an ETL, you need to use the data warehouse and transactional data source names that you specify here as the Parameter values for the execution plan parameters DBConnection_OLTP and DBConnection_OLAP.

    Type

    • Source. Select this option for a transactional (OLTP) database.

    • Warehouse. Select this option for a data warehouse (OLAP) database.

    • Informatica Repository. Select this option for the Informatica repository database.

    • DAC Repository. Select this option for the DAC repository database.

    • Other

    Connection Type

    Database type.

    Service Name, TNS Name, Connection String, or Database Name (Note: Field name changes based on the Connection Type selection.)

    Enter the value appropriate for your database.

    For an Oracle TNS Name, enter the TNS name that is specified in the tnsnames.ora file in \network\admin\.

    Table Owner

    Valid database user.

    Password

    Password for the database user.

    Num Connections per EP

    Number of connections the DAC Server will open to this physical data source during the ETL process.

    Note: Typically, DAC needs more connections for data warehouse databases on which it truncates and analyzes tables, and drops and creates indexes.

    DAC also requires more connections for source systems, such as Siebel, for which it performs change capture operations. In such cases, the value of the Num Connections per EP property determines how many change capture processes can run concurrently. If you have a powerful transactional database server and are going to run ETL processes during off-peak times, you can increase the Num Connections per EP value to 15 or 20 (10 is the default). If you have a less powerful transactional database server, you should not overload the system with ETL processes. Therefore, you should set the value below 10.

    Source systems for which DAC does not perform transactions usually do not require as many connections.

    For data warehouse databases, when the DAC Server is analyzing and truncating tables and dropping and creating indexes, the Num Connections per EP value can be higher than the Num Parallel Workflows per EP parameter value (set in the Informatica Servers tab) because when DAC creates indexes for a table in parallel, it needs additional relational connections.

    For example, if you are running 10 workflows, and you want to create two indexes per table in parallel, you will need 20 connections. This is true even though not all 10 tasks will be doing database-related activities (such as dropping and creating indexes, analyzing tables, and so on) at the same time.

    Host

    Machine name or instance where the database resides.

    Port

    Port number where the database listens (for example, 1521 is the default for an Oracle database).

    Source Priority

    Priority of the physical data source connection. Used for multi-source execution plans to resolve conflicts when data is extracted into common data warehouse tables. The DAC dependency algorithm ensures that no more than one task writes to a target table at a time (unless overridden in task groups), and that the reads and writes are staggered. For example, if you have multi-source scenario in which you want to extract data from Oracle EBS and Siebel transactional sources, and you want to extract data for the common data warehouse entities from Oracle EBS first and then from Siebel, the source priority for Oracle EBS should be 1 and Siebel should be 2.

    Data Source Number

    Unique number assigned to the data source category so that the data can be identified in the data warehouse.

    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.

    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 creates indexes on the database.

    To use the default table space, leave this field blank.

    Num Parallel Indexes per Table

    Specifies how many indexes are to be created in parallel for each table associated with the physical data source connection. For example, if you give this property a value of 2, then two indexes will be created in parallel per table during the ETL process.

    Note: The number of indexes that can be created in parallel is limited by the value you set in the Num Connections per EP property and the Max Num Workflows per EP property on the Informatica Servers tab. Each task that is creating an index on a table requires a connection. Therefore, if you have two tables and each table has three indexes, and you have set the Num Parallel Indexes per Table property to 3, the Num Connections per EP property should have a value of at least 6 in order for all the indexes to be created in parallel.

    Note: Use caution when setting this property. Setting this number too high as a default for every table that participates in the ETL process can put stress on the database. You may want to consider setting the Number of Parallel Indexes property (in the Parallel Indexes subtab) for individual tables after you identify tasks that are creating bottlenecks or blockages.

    JDBC Driver (Optional)

    A JDBC driver for the data source connection. The value in this field must conform to the database specifications. Use this option to specify a unique driver for a particular physical data source.

    URL (Optional)

    A JDBC URL for the data source connection. The value in this field must conform to the database specifications. Use this option to specify a unique URL for a particular data source. For example, this option can be useful if this physical data source uses Oracle RAC and other data sources use a different database type.


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

  4. Click Save.

Database Connectivity

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

Setting Up Communication Between DAC and Informatica

This section includes information about the requirements needed for DAC and Informatica PowerCenter to communicate.

Note: If you installed and configured DAC using the Oracle BI Applications installation process and by following the post-installation instructions in Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications, you do not need to repeat the same tasks that appear in this section.

This section contains the following topics:

Introduction to DAC and Informatica Interaction

DAC orchestrates ETL routines written in Informatica and uses the Informatica pmcmd and pmrep command line interface tools to run workflows. At runtime, DAC consolidates all parameters—including runtime, database specific, SQL, and application level—into a parameter file while invoking the workflows. DAC also sets the session log file name, and, at the end of the routine execution, collects valid statistics, such as workflow status, number of row processed, and read/write throughputs.

DAC Session Log File Naming Conventions

DAC provides a log file name for each Informatica session it executes. If a workflow does not run concurrently, and, therefore, does not have multiple instances, DAC uses the following naming convention for the log file:

[session_name].[source_database_name].log

If a workflow runs concurrently because it is configured to loop (see "Looping of Workflows"), DAC uses the following naming convention for the log file

[dac_task_name].S[session_number].[source_database_name].log

If a workflow runs concurrently because a DAC_EP_PREFIX execution plan level parameter has been defined (see "Execution Plans That Are Eligible to Run Concurrently"), DAC uses the following naming convention for the log file:

[prefix].[session_name].[source_database_name].log

The source database name is appended to allow for multi-source ETL processes in which the same task may be run as many times as there are sources, and the log files must be distinguishable.

DAC will shorten the name of the session log file if the naming convention produces files names greater than 100 characters.

If the length of the absolute file name (fully qualified with directory names) is more than 250 characters, Informatica may fail and return a "File name too long" error when it tries to create the workflow/session log files. To help avoid this error, Oracle recommends that the variables $PMSessLogDir and $PMWorkflowDir have values that are short, such as C:\infalogs, rather than the typical value C:\Informatica\PowerCenter<version number>\server\infa_shared\SessLogs.

However, even if you use short values for the variables $PMSessLogDir and $PMWorkflowDir, DAC may still shorten the length of the log file names. To enable DAC to use longer file names (over 100 characters, which is the default), edit the variable informatica.max.sesslogfile.length in the infa-config.properties file, which is located in the <DAC_Config_Location>\conf-shared directory.

Informatica Log File Naming Conventions

The naming conventions for Informatica log files is different depending on whether the DAC_EP_PREFIX execution plan level parameter has been defined. Table 4-1 lists the naming conventions for the Informatica log files.

Table 4-1 Informatica Log File Naming Conventions

Command That Generates Log Log File Name Without DAC_EP_PREFIX Defined Log File Name With DAC_EP_PREFIX Defined

Pmcmd: startworkflow
getworkflowstatus

WF.log

<PREFIX>_WF.log

Pmcmd: getSessionDetails

WF_DETAIL.log

<PREFIX>_WF_DETAIL.log

Pmrep: getSessionSet

WF_SESSIONS.log

<PREFIX>_WF_SESSIONS.log


Connectivity Requirements

You should be familiar with the following connectivity requirements for DAC and Informatica.

Informatica and DAC Server Connectivity

The DAC Server requires access to the following Informatica command line programs:

  • pmrep. For communicating with the Informatica Repository Service to query the sessions associated with a workflow.

  • pmcmd. For communicating with the Informatica Integration Service in order to start and stop the Informatica workflows as well as collect statistics for the workflow.

To ensure DAC has access to pmrep and pmcmd, do one of the following:

  • Co-locate the DAC Server and Informatica on the same machine.

  • Make the Informatica binary files accessible on a shared network drive.

  • Copy the Informatica\PowerCenter<version number>\CMD_Utilities directory to the machine where the DAC Server is installed

Informatica and DAC Client Connectivity

The DAC Client requires access to pmrep in order to synchronize tasks with Informatica workflows and to keep the DAC task source and target tables information up to date. You can either co-locate the Informatica client tools and the DAC Client on the same machine, or you can install the Informatica client tools as a shared network resource.

Procedure DAC Follows When Interacting With Informatica

The procedure DAC follows when interacting with Informatica is as follows.

  1. At the beginning of the ETL process, DAC executes the pmrep command "connect" (CONNECT_REP_SERVER section) and the pmcmd command "pingserver" (PING_SERVER section) to the Repository Service entry and also to all the Informatica Integration Services.

    Any non-zero return codes indicate an error occurred when connecting to the Informatica Integration Services, resulting in failure of the ETL process. The outputs are captured in pmrepCommand.log and ping.log in the appropriate log directory for the run.

  2. The following steps are followed when a workflow is executed:

    1. The list of sessions associated with the workflow are identified. This is done by invoking the pmrep "listobjectdependencies" command (GET_SESSIONS section in the infa_command.xml). The output of the command is parsed to get the list of sessions. This list is then used for composing a parameter file where the parameter name-value pairs are repeated for each session under the workflow. The output is captured in <WF_NAME>_SESSIONS.log.

    2. The workflow execution is triggered in a polling mode for ENU environments, and with -WAIT mode for the non-ENU environments, using the pmcmd "startworkflow" command (START_WORKFLOW section).

      For ENU environments, the exit code for the pmcmd command to start the workflow indicates whether the request to start the workflow succeeded. The output of this command is captured in <WFNAME>.log. Once the workflow starts to execute, DAC tries to poll the status of the workflow every 30 seconds using pmcmd "getworkflowdetails" command (GET_WORKFLOW_DETAILS section). As long as the status is "Running," DAC waits. If the workflow return code is 0 and the status is "Succeeded," then the workflow is considered to have completed without errors. DAC looks for both workflow return code and also the status to make a decision. The output of the command is also written to WF_NAME.log file.

      For non-ENU environments, the exit code of the pmcmd command to start the workflow indicates whether the workflow completed successfully. Also, some of the strings that DAC looks for in the output files are localized. In such cases, you need to find the equivalent strings from the respective outputs and populate them in the infa_commands.xml file under "Informatica Internationalization" section. This step is essential for DAC to interpret the output provided by the pmcmd and pmrep utilities. For example, DAC interprets a workflow to be successful if the workflow status is listed by the pmcmd command "getworkflowdetails," with the tag "Workflow Run Status," and a value of "Succeeded." These strings in a non-ENU environment could appear in localized strings. For DAC to be able to understand and interpret this, you need to fill in the equivalent localized strings in the body.

    3. For each of the sessions under the workflow, DAC also tries to get the session statistics by executing the pmcmd "getSessionStatistics" command (GET_SESSION_STATISTICS section). The output of the command is written to the file <SESSION_NAME>_DETAIL.log.

Defining the Informatica Domains File Path for the DAC Client and DAC Server

In order for the DAC Client and Server to be able to use the pmrep and pmcmd programs, the path of the Informatica Domains file 'domains.infa' must be defined in the dac_env.cmd or dac_env.sh file on both the DAC Client machine and the DAC Server machine.

Configuring Relational Connections in Informatica Workflow Manager

The Informatica Integration Service requires database connection information for the source and target databases. You use Informatica Workflow Manager to configure the database connections. The connections you configure in Informatica map to physical data source definitions in DAC, as specified in the Physical Data Sources tab of the Setup view. DAC also specifies the name of the Informatica database connection in the parameter file that is generated for the workflow when an ETL process is executed.

Note: The name for the connection specified in Informatica must match the name of the physical data source in DAC to which it maps.

To configure relational connections in Informatica Workflow Manager:

  1. In Informatica PowerCenter Workflow Manager, connect to the repository.

  2. Select Connections, and then select Relational.

    The Relational Connection Browser is displayed.

  3. Create a connection for each transactional (OLTP) database, and a connection for the Oracle Business Analytics Warehouse (OLAP) database. For each database connection, do the following:

    1. Click New to display the Select Subtype dialog.

    2. Select the appropriate database type, and click OK.

      The Connection Object Definition dialog displays options appropriate to the specified database type.

    3. Define the relational connection.

      Name: Enter a name for the database connection exactly as it appears in the Physical Data Sources tab in the DAC Setup View.

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

    4. Click OK to save the details.

Registering Informatica Services in DAC

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

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

  • You can register multiple Integration Service services, but you must register at least one service.

  • You must register one Repository Service. You should only register one Repository Service.

Tip:

To locate Informatica services properties that you may need to complete this procedure, log into the Informatica Administrator and select the appropriate service.

To register Informatica Services in DAC:

  1. In the DAC Setup view, click the Informatica Servers tab.

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

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

      Field Description

      Name

      Enter a logical name for the Integration Service.

      Type

      Select Informatica.

      Service

      Enter the name of the Integration Service as specified in the Informatica domain (as seen in Informatica Administrator).

      Domain

      Enter the Informatica domain name (as seen in Informatica Administrator).

      Login

      Informatica repository user name who has Administrator privileges for the Informatica repository.

      Note: DAC must log in to Informatica as an Informatica repository Administrator user that is configured in the native security domain.

      Password

      Password for the user specified in the Login field.

      Num Parallel Workflows per EP

      Maximum number of workflows that can be executed in parallel on the Informatica Integration Service. If the number of sessions is zero or is not specified, the DAC Server assigns the default value of 10.

      For more information about setting this property, see "Determining the Num Parallel Workflows per EP Parameter Setting".

      Repository Name

      Name of the Informatica Repository Service that manages the Informatica repository for Oracle BI Applications.

      Inactive

      Indicates whether the Integration Service will participate in the ETL process.


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

      Note: The Integration Service must be running.

    3. Click Save to save the details.

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

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

      Field Description

      Name

      Enter a logical name for the Repository Service.

      Type

      Select Repository.

      Hostname

      Enter the name of the Informatica Gateway Host machine.

      Server Port

      Enter the Gateway port (as specified during the installation of Informatica PowerCenter Services).

      Login

      Informatica repository user name who has Administrator privileges for the Informatica repository.

      Password

      Password for the user specified in the Login field.

      Num Parallel Workflows per EP

      Maximum number of workflows that can be executed in parallel on the Informatica Integration Service. If the number of sessions is zero or is not specified, the DAC Server assigns the default value of 10.

      For more information about setting this property, see "Determining the Num Parallel Workflows per EP Parameter Setting".

      Repository Name

      Name of the Informatica Repository Service that manages the Informatica repository for Oracle BI Applications.

      Inactive

      Indicates whether the Repository Service will participate in the ETL process.


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

      Note: The Repository Service must be running.

    3. Click Save to save the details.

Determining the Num Parallel Workflows per EP Parameter Setting

You set the Num Parallel Workflows per EP parameter value when you register the Informatica Integration Service in the Informatica Servers tab of the DAC Client. This parameter specifies the maximum number of workflows that can be executed in parallel on the Integration Service. If the number of sessions is zero or is not specified, the DAC Server assigns the default value of 10.

You should consider the following factors when determining the Num Parallel Workflows per EP parameter value:

  • How powerful the machine is that hosts the Integration Service.

  • How many instances of Integration Services are available.

  • The number of Runnable tasks in the queue. A Runnable task is a task for which the Depends On tasks have completed and is ready to be run but is waiting for an Informatica slot to be available. For information about the different task run statuses, see "Current Runs Tab".

    For an optimal run, the runnable queue should be at zero or should reach zero in a short time. For example, Figure 4-2 shows an ideal performance run when 15 sessions were run in parallel. There were many runnable tasks before the process began, but the queue soon reached zero.

    You can generate a run analysis such as Figure 4-2 from the right-click menu (select Get Run Information, then select Get Graph) on the DAC Current Runs and Run History tabs. If you find that the runnable curve does not reach zero in a short time, you should increase the Num Parallel Workflows per EP parameter value to make more Informatica slots available.

Figure 4-2 Sample Performance Run

This figure is a graph showing various ETL runs.

Setting Informatica Integration Service Relaxed Code Page Validation

The Informatica Integration Service must be configured for relaxed code page validation. For more information, refer to the Informatica documentation.

To configure the Informatica Integration Service for relaxed code page validation:

  1. Log into Informatica Administrator.

  2. Select the appropriate Integration Service.

  3. Select the Properties tab.

  4. In the Configuration Properties area, click Edit.

  5. Deselect the ValidateDataCodePages check box.

Setting Informatica Integration Service Custom Properties

Follow this procedure to set custom properties for the Informatica Integration Service.

To set custom properties for the Informatica Integration Service:

  1. In Informatica Administrator, select the appropriate Integration Service.

  2. Click the Properties tab.

  3. In the Custom Properties area, click Edit.

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

    • Table 4-2 Custom Properties for Informatica Integration Service

      Custom Properties Name Custom Properties Value Notes

      ServerPort

      <Server Port Number>

      For <Server port number>, enter the number of an available port. For example, 4006. This custom property configures the Integration Service to listen on <Server port number>. DAC communicates with the Integration Service using this port.

      overrideMpltVarWithMapVar

      Yes

      Enables Informatica to evaluate parameters within mapplets.

      DisableDB2BulkMode

      Yes

      Add this custom property and set value to Yes if your Oracle Business Analytics Warehouse is on a DB2/390 or a DB2 UDB database.


  5. Click OK to save the details.

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

Creating the Repository Administrator User in the Native Security Domain

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

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

For more information on creating users and Informatica security domains, see the Informatica documentation.

To use the default Administrator user in the native security domain:

  1. Log in to Informatica Administrator as Administrator.

  2. Click the Configure Security icon in the top, right corner of the Informatica Administrator work area to display the Security tab.

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

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

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

  5. Click OK.

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

  1. Log in to Informatica Administrator as Administrator.

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

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

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

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

  6. Click OK.

Setting the Data Movement Mode

You must set the data movement mode appropriately in the Informatica Integration Service.

To configure the data movement mode:

  1. Log in to Informatica Administrator.

  2. Select the appropriate Integration Service.

  3. Select the Properties tab.

  4. In the General Properties area, click Edit.

  5. Use the DataMovementMode drop down list to specify either ASCII or Unicode, then click OK to save the change.

Encrypting Passwords When DAC Interacts With Informatica

DAC sends the Informatica Integration Service and Repository Service passwords un-encrypted when communicating with Informatica through pmcmd and pmrep commands. This section includes instructions for forcing password encryption.

To force password encryption:

In the examples included in the following procedure, the Informatica Integration Service and Informatica Repository Service use the password Administrator.

  1. Open a command window and type the following command to create an encrypted Informatica password for pmcmd and pmrep

    pmpasswd Administrator -e CRYPT_SYSTEM

    This step will produce something similar to the following text:

    Informatica PowerMart Encryption Utility, 32-bit
    Copyright (c) Informatica Corporation 1996-2008
    All Rights Reserved
    
    Encrypted string 
    –>dMGpMvpsuQwXD5UvRmq0OZxhppTWK0Y7fzBtxHL04Gg=<–
    Will decrypt to –>Administrator<–
    
  2. Create a new environment variable with the following properties.

    NameINFA_PASSWORD (Or any name that you choose.)

    ValuedMGpMvpsuQwXD5UvRmq0OZxhppTWK0Y7fzBtxHL04Gg=

    Note: The value should be exactly as shown in the encrypted message in the Command window (the value between --> and <--).

  3. Modify the file <DAC_Config_Location>\conf-shared\infa_command.xml by replacing all occurrences of <-p> with <-pv> and <-x> with <-X>.

  4. Stop the DAC Server.

  5. In DAC, go to the Setup view, and select the Informatica Servers tab.

  6. Select the appropriate Informatica Service record and enter the name of the environment variable that you created in step 2 of this procedure as the value in the Password field. Save the record.

  7. Repeat step 6 for the Informatica Repository Service.

  8. Close and re-open the DAC Client to test the connections.

  9. If the DAC Server is located on the same machine, start the DAC Server and run an execution plan.

  10. Verify that DAC issues the following pmcmd command.

    pmcmd startworkflow -sv BI_DW_Server -d <Domain> -u Administrator -pv **** -f
    <folder> -lpf <filename><workflow>
    INFORMATICS TASK:<workflow> has finished execution with Completed status.
    
  11. If the DAC Server is on a different Windows machine, do the following:

    1. Set the environmental variable on the server machine and modify the <DAC_Config_Location>\conf-shared\infa_command.xml.

    2. Shut down the DAC Server when testing Informatica connectivity or unit testing a DAC task using the DAC Client.

  12. If the DAC Server is on a non-Windows machine, do the following:

    1. Set the environmental variable on the server machine and modify the <DAC_Config_Location>\conf-shared\infa_command.xml.

    2. Shut down the DAC Server when testing Informatica connectivity or unit testing a DAC task using the DAC Client.

Integrating DAC and Oracle BI Server

If you are using an Oracle Fusion Applications source system, you need to configure the Oracle BI Server as a physical data source. DAC communicates with the BI Server for the following reasons:

To configure the BI Server as a physical data source:

  1. In the Setup view, select the Physical Data Sources tab.

  2. Click New in the toolbar.

  3. In the Edit subtab, enter the appropriate information for the BI Server. For a description of the fields, see "Setting Up Physical Data Sources".

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

  5. Click Save.

  6. Specify the BI Server Connection Pool name:

    1. Click the Extended Properties subtab.

    2. Click New in the bottom pane toolbar.

    3. Select BIPool from the Name drop-down list.

    4. Double-click in the Value field to open the Property Value dialog.

    5. Enter the connection pool name.

    6. Enter the database type of the transactional data source.

    7. Click Save in the bottom pane toolbar.

      Note:

      For Oracle Fusion Applications source systems, after performing this step, you might receive an error in the log files for the run stating the connection pool does not have privileges to run a script for obtaining the timestamp of the data source database. You can ignore this error. However, if physical data source and DAC Server are in different time zones, make sure you define the Time Difference Override property as described in the following step.

  7. If the DAC Server machine and the transactional system's database are in different time zones, set the Time Difference Override extended property to specify the time difference between the DAC Server machine and the transactional system's database:

    1. In the Extended Properties subtab, click New in the bottom pane toolbar.

    2. Select TimeDifferenceOverride from the Name drop-down list.

    3. Click in the Value field to open the Property Value dialog.

    4. Enter the time difference, in minutes, between the DAC Server machine and the transactional system's database.

      This value can be a positive or negative number. It needs to specify the minutes that must be added to or subtracted from the DAC Server time to match the physical data source database time. For example, if the DAC Server is in Pacific Standard Time and the physical data source database is in Eastern Standard Time, the value should be 180. And, conversely, if the DAC Server is in Eastern Standard Time and the physical data source database is in Pacific Standard Time, the value should be -180.

      This value is static and is not automatically updated for Daylight Savings Time differences throughout the year. You will need to update this value for any such time changes.

      For more information about the Time Difference Override extended property, see "Physical Data Sources Tab: Extended Properties Subtab".

Setting Up DAC System Properties

You set DAC system properties in the DAC System Properties tab in the Setup view. The Description subtab provides a description of each property. Also see "DAC System Properties Tab" for more information about system properties.

Creating or Upgrading the Data Warehouse Schema

DAC provides functionality for creating and upgrading data warehouse schemas. For information about this functionality, see Chapter 10, "Managing Data Warehouse Schemas."

Distributing DAC Metadata

Typically, you may have multiple environments, such as development, test, QA, production, and so on. The DAC Import/Export feature enables you to make changes in one environment, then export the whole environment as XML files, and then import the XML files into another environment.

For instructions on exporting DAC metadata, see "Exporting DAC Metadata".

For instructions on importing DAC metadata, see "Importing DAC Metadata".

Exporting DAC Metadata

The DAC's Import/Export feature enables you to export source system-specific DAC metadata out of the DAC repository. You can use this feature to migrate DAC metadata from one environment to another, such as from a development environment to test or production environments.

Caution:

When you export metadata, all files in the target folder are deleted.

DAC behavior relating to the target folder is as follows:

  • If the target folder is empty, DAC exports the metadata without a warning.

  • If the target folder contains DAC metadata, DAC issues a warning and you must click OK to proceed with the export. The export process replaces all content in the target folder.

  • If the target folder has non-DAC metadata as well as DAC metadata, DAC issues a warning, and you must click OK to proceed with the export. The export process replaces all content in the folder. All non-DAC metadata is deleted.

  • If the target folder has only non-DAC metadata, DAC cannot export into the specified target folder.

To export DAC metadata:

  1. In the DAC Client, on the Tools menu, select DAC Repository Management, and then select Export.

  2. Select the directory to which you want to export DAC metadata, or accept the default directory.

  3. Select the source system containers that hold the metadata you want to export.

  4. Select the appropriate categories of metadata you want to export:

    • Logical. Exports all information contained in the Design view and metadata defined in the Seed Data menu.

    • System. Exports all information contained in the Setup view, except passwords for servers and database connections.

    • Run Time. Exports information about ETL runs and schedules (contained in the Execute view).

    • User Data. (Applicable to DAC standalone authentication only) Exports the users, roles, and passwords. Note: When importing roles and passwords, if the encryption key is different in the repository to which you are importing from that in which the metadata was exported, the roles and passwords will be unreadable.

  5. Click OK.

  6. Verify the export process by reviewing the log file <Domain_Home>\dac\log\export.log.

Importing DAC Metadata

The DAC's Import/Export feature enables you to import source system-specific DAC metadata into the DAC repository. You can also use this feature to migrate DAC metadata from one environment to another, such as from a development environment to test or production environments.

To import DAC metadata:

  1. In the DAC Client, on the Tools menu, select DAC Repository Management, and then select Import.

  2. Select the directory from which you want to import DAC metadata, or accept the default directory.

  3. Select the appropriate applications for which you want to import metadata.

  4. Select the appropriate categories of metadata you want to import:

    • Logical. Exports all information contained in the Design view and metadata defined in the Seed Data menu.

    • System. Exports all information contained in the Setup view, except passwords for servers and database connections.

    • Run Time. Exports information about ETL runs and schedules (contained in the Execute view).

    • User Data. (Applicable to DAC standalone authentication only) Exports the users, roles, and passwords. Note: When importing roles and passwords, if the encryption key is different in the repository to which you are importing from that in which the metadata was exported, the roles and passwords will be unreadable.

  5. If you are importing metadata into a blank repository or to completely replace selected categories of the current metadata in the repository, select Truncate Repository Tables. This option overwrites the content in the current repository. It also greatly increases the speed of the import process.

  6. If you want DAC to import new records and update existing records, select Update Existing Records. If you do not select this check box, DAC will only insert new records. This option is only available if you do not select the Truncate Repository Tables option.

    Note: When importing DAC metadata into a container, if you select the Update Existing Records option (and not the Truncate Repository Tables option), you also need to import the metadata into any child containers. Otherwise, the child containers will be inconsistent with the parent container.

  7. (Optional) Select Enable bulk mode to insert the imported metadata into the repository as an array insert. You should elect this option only if you also selected the Truncate Repository Tables option.

    This action increases the speed of the import process.

  8. Click OK.

  9. Verify the import process by reviewing the log file <Domain_Home>\dac\log\import.log.

Applying Patches

The DAC metadata patch feature enables you to do the following:

For more information about the DAC metadata patch feature, see Chapter 12, "Working With DAC Metadata Patches."

Integrating DAC With ETL Tools Other Than Informatica

The DAC external executor framework provides interfaces that enable you to integrate it with ETL tools other than Informatica. The interfaces are described in Javadocs, which are located in the <Oracle_Home>\dac\documentation\External_Executors\Javadocs directory.

For more information about the DAC external executor framework, see Chapter 14, "Integrating DAC With Other ETL Tools."

Managing DAC User Accounts

This section contains the following topics:

How DAC Permissions Map to Oracle Business Intelligence Applications Roles

Oracle Business Intelligence Applications includes preconfigured security roles that map to the preconfigured DAC permissions. Table 4-3 shows the mapping between the Oracle BI Applications role and the DAC permissions.

For more information about Oracle BI Applications roles, see the chapter titled "Oracle BI Applications Security" in Oracle Fusion Middleware Reference Guide for Oracle Business Intelligence Applications.

Table 4-3 Oracle BI Applications Roles Mapped to DAC Permissions

Oracle BI Applications Role DAC Permission

BI Administrator

Administrator

BI Author

Developer, Operator

BI Consumer

Guest

BIA_ADMINISTRATOR_DUTY

Administrator

BIA_FUNCTIONAL_DEVELOPER_DUTY

Developer

BIA_IMPLRMENTATION_MANAGER_DUTY

Operator


As shown in Table 4-4, each DAC permission has a set of privileges that determines what DAC functionality the permission can access.

Table 4-4 User Account Permissions and Privileges

Permission Privileges

Administrator

Read and write privileges on all DAC tabs and dialogs, including all administrative tasks.

Developer

Read and write privileges on the following:

  • All Design view tabs

  • All Execute view tabs

  • Export dialog

  • New Source System Container dialog

  • Rename Source System Container dialog

  • Delete Source System Container dialog

  • Purge Run Details

  • All functionality in the Seed Data menu

Operator

Read and write privileges on all Execute view tabs

Guest

Read privileges in all views.


Creating, Deleting, Inactivating User Accounts

In DAC standalone authentication mode, the User Management dialog enables a user with the Administrator permission to manage user accounts. A user account includes a unique identifier, password, and one or more permissions. The Administrator account is created during the initial login to a new DAC installation. See "Logging Into DAC for the First Time as an Administrator" for more information about creating the Administrator account.

After a user account is created, the Administrator must distribute the DAC repository authentication file to users in order for the users to be able to log into the DAC Client (and access the DAC repository) using their own user name and password.

To create a user account:

  1. In the DAC Client, on the File menu, select DAC User Management.

  2. In the User Management dialog, click New.

  3. In the new record field, do the following:

    1. Enter a unique name and password.

    2. Click in the Roles field, and then select the roles you want to associate with this user account.

  4. Click Save.

  5. Click Close to exit the DAC User Management dialog.

  6. Distribute the authentication file for the database where the DAC repository resides to the user.

To delete a user account:

  1. On the File menu, select DAC User Management.

  2. In the DAC User Management dialog, select the user account you want to delete.

  3. Click Delete.

  4. Click Close to exit the DAC User Management dialog.

To inactivate a user account

  1. On the elect File, and then DAC User Management.

  2. In the DAC User Management dialog, select the user account you want to inactivate.

  3. Click the Inactive check box.

  4. Click Save.

  5. Click Close to exit the DAC User Management dialog.

Importing DAC User Account Information From a Text File

You can create multiple DAC user accounts by importing the user account information from a text file.

To import DAC user account information from a text file:

  1. Create and save a text file containing user account information in one of the following formats:

    • <user name>

      The default role is Developer, and the default password is the same as the user name.

    • <user name>, <password>

      The default role is Developer.

    • <user name>, <password>, <role 1>[,<role 2>][...]

    You can use different formats on different lines in the text file.

  2. In the File menu, select DAC User Management.

  3. Right-click in the DAC User Management dialog, and select Read Users From File.

  4. Navigate to the text file, select it, and then click OK.

    The new user account information will appear in the DAC User Management dialog.

Changing the DAC Repository Encryption Key

When you change the DAC repository encryption key, the following changes occur:

To change the DAC encryption key:

  1. In the DAC Client, on the Tools menu, select DAC Repository Management, and then select Change Encryption Key.

  2. Click Generate Random Encryption Key to generate an encryption key. The key is displayed in the Key field. Alternatively, you can enter a key in the Key field. The key must be at least 24 characters long.

  3. Click OK.

  4. Distribute the updated authentication file (cwallet.sso) to all users that connect to this repository.

Modifying an Existing Authentication File

A DAC administrator can change the DAC repository schema owner and password in an existing authentication file.

To modify an existing authentication file:

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

  2. In the Login... dialog, click Configure.

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

  4. Click in the Authentication File field to open the Authentication File dialog.

  5. From the drop-down list, select Modify existing authentication file, and then click OK.

  6. Select the authentication file, and then click OK.

  7. Enter a new table owner name and password, and then click OK.

  8. Click Apply, and then click Finish.

  9. Distribute the updated authentication file (cwallet.sso) to all user accounts that connect to this repository.

Moving a DAC Environment Using Oracle Fusion Middleware Movement Scripts

Oracle Fusion Middleware provides movement scripts that enable you to move components and configurations from a development or test environment to a production environment.

When DAC is installed in an Oracle Fusion Middleware environment and you run the movement scripts, DAC components and configurations are moved along with the other Oracle Fusion Middleware components.

Detailed information about moving an Oracle Fusion Middleware environment is provided in the Oracle Fusion Middleware Administrator's Guide. You should thoroughly review and follow the instructions in the Oracle Fusion Middleware Administrator's Guide when moving an Oracle Fusion Middleware environment (including DAC).

This section describes only the movement of the DAC components and configurations when you use the movement scripts provided by Oracle Fusion Middleware.

This section contains the following topics:

Preconditions for Moving a DAC Environment

The following preconditions must be met before a DAC environment is moved:

  • The source system is installed and properly configured.

  • The source and target systems have equivalent hardware. There will be a one-to-one mapping from each host in the source system to an equivalent host in the target system.

  • The operating system for the source and target systems are the same.

  • Informatica is installed on both the source and target systems.

    For instructions on installing Informatica, use the Informatica PowerCenter installation documentation in conjunction with information about Oracle BI Applications requirements that appears in the section titled "Setting Up Oracle BI Applications," in the Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications.

  • The Informatica Repository has been restored on the target system.

    For instructions on restoring an Informatica Repository, see the section titled, "Restoring the Prebuilt Informatica Repository, in the Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications.

  • The Informatica Repository has been configured on the target system.

    For information about the tasks required to configure the Informatica Repository, see "Setting Up Communication Between DAC and Informatica".

Process for Moving DAC Components

This section describes the process for how DAC components are moved when the Oracle Fusion Middleware movement scripts are used to move an Oracle Fusion Middleware environment.

Detailed instructions for moving an Oracle Fusion Middleware environment are provided in the Oracle Fusion Middleware Administrator's Guide.

Figure 4-3 illustrates the sequence of steps of the DAC movement process. These steps are further described below. The movement scripts described in this section are located in the ORACLE_COMMON_HOME/bin directory.

Figure 4-3 Flow for Moving DAC Components

This graphic is described in the surrounding text.
  1. Move the binary files. In this step, you create an archive of the source Middleware home and use the archive to create a copy of the Middleware home. You first execute copyBinary.cmd/.sh file on the source system, which creates the BI.jar archive. Then, you execute pasteBinary.cmd/sh and specify the target destination.

  2. Copy the source configurations. In this step, you execute copyConfig.cmd/.sh, which creates an archive of the source configurations. This archive is named biConfig.jar.

  3. Create a move plan. In this step, you execute extractMovePlan.cmd/.sh, which extracts from biConfig.jar the configuration settings of the source environment. Table 4-5 lists the DAC-related configuration information that is contained in the movePlan.xml file.

  4. Modify the move plan. In this step, you manually edit the move plan to specify properties for the target environment.

  5. Update the target environment with the settings specified in the move plan. In this step, you execute pasteConfig.cmd/.sh. This step also drops and creates the data warehouse schema and updates the DAC server.properties file and dac_env.cmd/.sh.

Editing DAC Properties in moveplan.xml

Table 4-5 describes the move plan properties related to DAC that appear in the moveplan.xml file.

Note:

You should only modify properties in the moveplan.xml file that have a READ_WRITE scope. Do not modify properties that have a READ_ONLY scope.

Table 4-5 DAC-Related Properties in moveplan.xml

Property Description Example

DAC Server Configuration

The following properties appear under the DAC-Server-Configuration config group.

 

jdbc.url

URL to connect to the DAC repository.

jdbc:oracle:thin:@example.abc.com:1521/abc.com

jdbc.driver

Name of the JDBC driver.

oracle.jdbc.driver.OracleDriver

Username

User name used to connect to the DAC repository.

 

Password File

Absolute path of a secure file containing the password for the user to connect to the DAC repository. You must provide a password file, even if you are not changing the configuration.

/scratch/biplatform/cloning/password_.txt

Email Configuration

The following properties are in the Email-Configuration config group.

 

email_host

Host name of the email server.

examplehost

email_protocol

Protocol for the email server.

smtp

email_address

Email address of the user.

example@example.com

needs_authentication

The flag indicating whether the corporate email server requires authentication. Valid values are true or false.

 

needs_ssl

The flag indicating whether an SSL connection is required. Valid values are true or false.

 

email_host_port

Port where the email server listens.

 

email_user

User name for the email account.

 

email_password

Absolute path of a secure file containing the password for the user of the email server. (Only required if the needs_authentication property is set to true.

/scratch/biplatform/cloning/password_.txt

Data Warehouse Configuration

The following properties are in the Datawarehouse-Configuration config group.

 

jdbc.url

URL to connect to the data warehouse.

jdbc:oracle:thin:@example.com:1521/example.com

jdbc.driver

Name of the JDBC driver.

oracle.jdbc.driver.OracleDriver

Username

User name used to connect to the data warehouse.

 

Password File

Absolute path of a secure file containing the password for the user to connect to the data warehouse. You must provide a password file, even if you are not changing the configuration.

/scratch/biplatform/cloning/password_DW.txt

Informatica Configuration

The following properties are in the Informatica-Configuration config group.

 

Informatica server home

Informatica server home.

scratch/infahome/

Domains infa file location

Location of the domains.infa file.

/scratch/infahome/domains.infa

InformaticaParameterFileLocation

Directory where the Informatica parameter files are stored (or DEFAULT). The default location is dac\Informatica\parameters.

DEFAULT

Data Source Configuration

The following properties are in the Datasources-Connection-Details config group.

 

Type

The physical data source type. Possible values are: Source, Warehouse, Informatica Repository, DAC Repository, and Other.

 

Connection Type

The type of database connection. Possible values are BI Server, Oracle (OCI8), Oracle (Thin), DB2, DB2-390, MSSQL, Teradata, Flat File.

N/A

Connection String

The data source connection string. If you are using:

  • Oracle (OCI8): Use the tnsnames entry.

  • Oracle (Thin): Use the instance name.

  • SQL Server: Use the database name.

  • DB2-UDB/DB2-390: Use the connect string as defined in the DB2 configuration.

  • Teradata: Use the database name.

orcl.example.com

Table Owner

Name of the table owner.

DB_User

Host

Host name of the server where the database resides.

example.com

Port

Port where the database receives requests.

1521

JDBC Driver (Optional)

JDBC driver for the data source connection. The value in this field must conform to the database specifications.

oracle.jdbc.driver.OracleDriver

URL (Optional)

The JDBC URL for the data source connection. The value in this field must conform to the database specifications.

jdbc:oracle:thin:@example.com:1521/orcl.example.com

Password File

 

/scratch/biplatform/cloning/password_ds.txt

Connection Pool Name (BIPool)

Connection pool name.

FSCM_OLTP."Connection Pool"

Database Type (BIPool)

Database type of the transactional data source.

Oracle

External Executors Configuration

The following properties are in the External_Executors config group.

N/A

Execution type

Execution type for the tasks that will be executed by the external executor.

N/A

Name

Name of the property that must be configured to integrate DAC with other ETL tools. There are multiple properties for the external executors. Name is the name of the property. Value is the value that defines the property.

<name>ODIUser</name>

<value>TestUser</value>


Post Movement Steps

The steps below should be followed after moving a DAC environment:

Note:

If you log into the DAC Client and receive an error message stating, "Can't authenticate user" and if the log file states, "INFO: Authentication failed. Cause: Couldn't connect to DAC application," this problem may be caused by an incorrect migration of the DAC Server URL. To correct this issue, you can execute the following SQL statement against the DAC repository:

UPDATE W_ETL_REPOS SET VALUE = 'http://example.abc.com:10217/DACServer'
WHERE NAME = 'DACServerURL';
COMMIT;

Alternatively, if the schema owner and password are unknown but you have the authentication file for the DAC repository, you can manually update the DAC Server URL by logging into the DAC Client using DAC standalone authentication mode. You will then need to restart the DAC Server.

Installing DAC on Windows in Standalone Mode

The Oracle BI Applications Client Installer executable is named biapps_client_install.exe. It installs all the necessary files for the DAC Client and Server.

The DAC Client must be co-located on the same machine as the Informatica PowerCenter Client Tools, or it must have access to the Informatica PowerCenter binaries and the domains.infa file on a shared network location.

Note: If you are running Oracle Fusion Applications, DAC may have already been installed during the provisioning process. In such cases, you do not need to repeat this procedure.

To install DAC using the Oracle BI Applications Client Installer:

  1. Double-click the biapps_client_install.exe file.

  2. In the Introduction screen, click Next to continue.

  3. In the Informatica Server Location page, enter the location of the Informatica PowerCenter Server folder or click Choose... to navigate to the folder location. For example, C:\Informatica\PowerCenter9.1.

    Click Next to continue.

  4. In the Informatica Domain File page, enter the location of the Informatica PowerCenter domains file domains.infa, or click Choose... to navigate to the file. For example, C:\Informatica\PowerCenter9.1\domains.infa.

    Click Next to continue.

  5. In the Choose Install Folder page, enter the absolute path for the folder where you want to install DAC, or click Choose... to navigate to and specify the folder.

    The installation directory path can contain alphanumeric, underscore (_), hyphen (-) or dot (.) characters and must begin with an alphanumeric character.

    The installer installs the DAC Client and Server files in a subfolder named \client in the folder you specify.

    Click Next to continue.

  6. In the Choose Shortcut Folder page, use the radio buttons to specify where you would like to create product icons on the Windows machine. For example, you might want to add a DAC Client icon to the Windows Start Menu.

    Click Next to continue.

  7. In the Pre-Installation Summary page, verify your selections. To return to previous install options, click Previous.

    To continue with the installation, click Install.

  8. When the installation is complete, click Done.

Installing DAC on UNIX in Standalone Mode

You can optionally install the DAC Server in a UNIX environment in standalone mode. Make sure Java 1.6 or higher is installed on the UNIX platform.

Note: If you are running Oracle Fusion Applications, DAC may have already been installed during the provisioning process. In such cases, you do not need to repeat this procedure.

To install the DAC Server on UNIX in standalone mode:

  1. Run the Oracle BI Applications Client Installer (biapps_client_install.exe) in a Windows environment.

    This installer will provide all the necessary files for the DAC Client and Server. For instructions on running the Oracle BI Applications Client Installer, see the "Installing DAC on Windows in Standalone Mode".

    The installer installs the DAC Client and Server files in a subfolder of the installation folder you specify named \client.

    Note: If you installed DAC using an installer earlier than version 11g, the folder containing the DAC files will be named \dac.

  2. Zip the contents of the \client folder.

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

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

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

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

  6. Before using the shell scripts, you may 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.

  7. On the UNIX machine copy the appropriate JDBC drivers to the /client/lib directory.

    Note: DAC uses JDBC drivers for connectivity to source, target and repository databases. 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 the database vendor site and known to be certified for the given database version should be used.

  8. Edit the config.sh file located in the /client directory to set the JAVA_HOME environment variable to point to the directory where the correct version of the Java JDK is installed.

  9. Edit the dac_env.sh file to configure the following variables:

    • INFA_HOME

    • INFA_DOMAINS_FILE

    • INFA_CMD_STYLE