Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Documentation Accessibility
• Audience
• Related Documents
• Conventions

What's New in This Release

• What's New in DAC
• Updates to Revision 1 of This Guide
• Updates to Revision 2 of This Guide
• Updates to Revision 3 of This Guide
• Updates to Revision 4 of This Guide
• System Requirements and Certification

1 About DAC Security

• DAC Security Overview
• DAC Client Installation Requirements
• DAC Authentication in Oracle Fusion Middleware (WebLogic Server) Mode
• DAC Authentication in Standalone Mode
• Recommended DAC Security Setup

2 Before You Begin Using DAC

• DAC Architecture Overview
• Introduction to DAC
  • Task Execution and Queue Management
  • About the DAC Process Life Cycle
• About Source System Containers
• About DAC Repository Objects
• About Object Ownership in DAC
• DAC User Interface Overview
  • Main DAC Window
  • Menu Bar Commands
  • Views Buttons
  • Top Pane Toolbar
  • Right-Click Menus
  • DAC Server Monitor Icons
  • Navigation Tree
  • Editable Lists
  • Font Variations of Objects Displayed in the DAC
  • Using the DAC Query Functionality
    • DAC Query Commands and Operators
    • DAC Query Examples
    • Common DAC Query Procedures
    • Using Flat Views Querying
    • About Query Mode
• DAC Accessibility Keyboard Options
• DAC Installation Scenarios
• DAC Installation Directory Paths

3 Upgrading to DAC 11g

• Before You Upgrade to DAC 11g
• Installing DAC 11g
• Upgrading an Existing DAC Repository
• Performing Post Upgrade Tasks

4 Setup Tasks and Concepts for DAC Administrators

• About DAC Authentication Modes
• About the DAC Authentication File
• Logging Into DAC for the First Time as an Administrator
• Managing the DAC Server
  • Accessing the DAC Server
  • About DAC Server High-Availability
    • DAC Server Migration Approach to Failover
    • How DAC High Availability Relates to Informatica
  • Configuring the DAC Repository to Allow DAC Server Connections
  • Configuring the Connection Between the DAC Server and DAC Repository
  • Starting and Stopping the DAC Server (Standalone Mode)
  • Starting and Stopping the DAC Server (Web Mode)
  • Setting Up Email Notifications in the DAC Client and Server
    • Configuring Email Recipients in the DAC Client
    • Configuring Email in the DAC Server
• Setting Up Physical Data Sources
• Database Connectivity
• Setting Up Communication Between DAC and Informatica
  • Introduction to DAC and Informatica Interaction
    • DAC Session Log File Naming Conventions
    • Informatica Log File Naming Conventions
  • Connectivity Requirements
    • Informatica and DAC Server Connectivity
    • Informatica and DAC Client Connectivity
  • Procedure DAC Follows When Interacting With Informatica
  • Defining the Informatica Domains File Path for the DAC Client and DAC Server
  • Configuring Relational Connections in Informatica Workflow Manager
  • Registering Informatica Services in DAC
  • Determining the Num Parallel Workflows per EP Parameter Setting
  • Setting Informatica Integration Service Relaxed Code Page Validation
  • Setting Informatica Integration Service Custom Properties
  • Creating the Repository Administrator User in the Native Security Domain
  • Setting the Data Movement Mode
  • Encrypting Passwords When DAC Interacts With Informatica
• Integrating DAC and Oracle BI Server
• Setting Up DAC System Properties
• Creating or Upgrading the Data Warehouse Schema
• Distributing DAC Metadata
  • Exporting DAC Metadata
  • Importing DAC Metadata
• Applying Patches
• Integrating DAC With ETL Tools Other Than Informatica
• Managing DAC User Accounts
  • How DAC Permissions Map to Oracle Business Intelligence Applications Roles
  • Creating, Deleting, Inactivating User Accounts
  • Importing DAC User Account Information From a Text File
• Changing the DAC Repository Encryption Key
• Modifying an Existing Authentication File
• Moving a DAC Environment Using Oracle Fusion Middleware Movement Scripts
  • Preconditions for Moving a DAC Environment
  • Process for Moving DAC Components
  • Editing DAC Properties in moveplan.xml
  • Post Movement Steps
• Installing DAC on Windows in Standalone Mode
• Installing DAC on UNIX in Standalone Mode

5 DAC Quick Start

• Logging into DAC for the First Time as a DAC User
• Running an Execution Plan
• Creating or Copying a Source System Container
• Monitoring Execution Plan Processes
  • Generating Run Reports
  • Viewing the Life Span of a Task
  • Identifying Why a Task Failed
  • Restarting an Execution Plan That Failed
  • Using DAC's Hotfix Capability to Handle Failed Tasks While an Execution Plan Is Still Running
    • Requeuing a Failed Task
    • Additional Points to Consider
• Scheduling an Execution Plan
• Unit Testing Execution Plan Tasks
• About Refresh Dates and DAC's Incremental Load Strategy
  • Refresh Date Scenarios
  • Adding or Resetting Refresh Dates
  • How DAC Computes Timestamps for Refresh Dates

6 Building and Running Execution Plans

• Introduction to Execution Plans and Load Processes
• About Single-Source Execution Plans
• About Multi-Source Execution Plans
  • Considerations for Multi-Source Execution Plans
  • Multi-Source Execution Plan Extract and Load Scenarios
• About Micro ETL Execution Plans
  • Why Use a Micro ETL Execution Plan?
  • About Refresh Dates and Micro ETL Execution Plans
  • Important Considerations When Using Micro ETL Execution Plans
  • Designing a Micro ETL Execution Plan
• Execution Plan Build Process Rules
• Building and Running Execution Plans
• Running Execution Plans Concurrently
  • Introduction to Running Execution Plans Concurrently
    • Dependent Execution Plans
    • Execution Plans That Are Eligible to Run Concurrently
  • About Resource Usage
  • Viewing Execution Plan Concurrent Dependencies
  • Configuring DAC to Run Execution Plans Concurrently
  • Enabling Informatica Workflows to Be Shared by Multiple Execution Plans
    • Defining an Execution Plan Prefix
    • Copying Informatica Workflow Folders and Creating New Folder Mappings
  • Explicitly Defining Execution Plans as Independent or Dependent
• Running Multiple Instances of an Execution Plan
• Setting Up Extract Delays, Event Delays and Data Source Notifications
  • Setting Up Extract Delays
  • Setting Up Event Delays
  • Setting Up Data Source Usage Notifications
• How the DAC Server Handles Requests to Start and Stop Execution Plans
• Setting up and Running Deferred ETL Processes
  • Setting Up Deferred ETL Processes
  • Running Deferred ETL Processes
    • Running Deferred Extract Tasks With a Nightly Execution Plan
    • Creating and Running a Separate Deferred Execution Plan

7 Customizing ETL Processes

• Considerations When Defining Repository Objects
  • Container Behavior and Best Practices
  • Task Behavior and Best Practices
  • Task Group Behavior and Best Practices
  • Table Behavior and Best Practices
  • Index Behavior and Best Practices
  • Column Behavior and Best Practices
  • Configuration Tag Behavior and Best Practices
  • Source System Parameter Behavior and Best Practices
  • Subject Area Behavior and Best Practices
  • Execution Plan Behavior and Best Practices
• About Customizing the Data Warehouse
• Adding a New Table and Columns to the Data Warehouse
• Adding an Index to the Data Warehouse
• Importing New Data Warehouse Objects into the Informatica Repository
• Creating Informatica Mappings and Workflows
• Creating Tasks in DAC for New or Modified Informatica Workflows
• Setting a Task Phase Dependency
• Creating a Task Group
• Working with Configuration Tags
• Using Actions to Manage Indexes, Tables and Tasks
  • Defining a SQL Script for an Action
  • Assigning an Action to a Repository Object
  • Functions for Use with Actions
  • Using a DAC Source System Parameter in an Action
  • Using a Task Action to Enable Failure Restarts When Extracting From Multiple Sources
• Mapping Multiple Database-Specific Informatica Workflows to the Same DAC Task

8 Defining and Managing Parameters

• Overview of Parameters
  • What Are Parameters and How Are They Used in DAC?
  • How DAC Handles Parameters at Runtime
  • Types of Parameters Supported by DAC
    • Rules of Precedence
  • Parameter Data Types
  • Static Versus Runtime Parameter Values
  • Predefined Parameters in Oracle BI Applications
  • Nesting Parameters within Other Parameters
  • Parameter Load Type Property
• About DAC Variables
• Defining Parameters
  • Defining a Text Type Parameter
  • Defining a Database-Specific Text Type Parameter
  • Defining a Timestamp Type Parameter
  • Defining a SQL Type Parameter
  • Defining an External Type Parameter
    • Text Interface
    • Timestamp Interface
  • Defining a Multi-Parameter Type Parameter
  • Defining a JavaScript Type Parameter
  • Defining a Global External Parameter

9 Designing Subject Areas

• About Designing a Subject Area
• How DAC Determines Tasks Required for Subject Areas
• Creating a Subject Area
• Modifying an Existing Subject Area
• When to Reassemble a Subject Area

10 Managing Data Warehouse Schemas

• Managing Data Warehouse Schemas
  • Creating, Upgrading or Dropping an Entire Schema
  • Creating, Upgrading or Dropping Subsets of Tables in the Schema
  • Advanced Usage of the Schema Creation and Upgrade Process
    • About the Create Schema SQL Script
    • About the Upgrade Schema SQL Scripts
    • Schema Characteristics You Need to Consider
    • Error Handling
    • Creating or Upgrading the Schema When You Have Multiple Source System Containers
    • Customizing the Schema XML Templates
• Managing Data Warehouse Schemas for DB2-390 Databases
• Parameterizing Default Values for Table Columns

11 Performance Tuning With DAC

• Managing Indexes
  • Index Behavior
  • Specifying Index Spaces for Indexes by Table Type
  • Specifying How Many Indexes Can Be Created in Parallel
  • Defining a During ETL Index
  • Defining Join Indexes on a Teradata Database
• Using Actions to Optimize Indexes and Collect Statistics on Tables
• Using Heuristics to Manage Tasks, Tables and Indexes
  • About DAC Heuristics
    • Using Heuristics to Determine Whether a Task Should Run
    • Using Heuristics to Determine Whether Indexes Are Dropped and Created
    • Using Heuristics to Determine Whether Tables Are Analyzed
  • DAC Heuristics and Task Groups
  • About Heuristics Rules and the Heuristics Dialog
  • Creating a Heuristics Rule
  • Associating a Heuristics Rule With a Task
  • Writing Custom SQL for a Heuristics Rule
• Looping of Workflows
  • Defining a Looping Property
  • Accessing the Loops Properties Using Parameters
  • Parallelizing the Load Process on a Fact Table
  • Creating Logical Partitions on Load Tasks to Increase Performance
• Customizing customsql.xml to Drop and Create Indexes and Analyze Tables
• Performance Tuning the Siebel Change Capture Process
  • Performance Tips for Siebel Sources
    • Performance Tip: Reduce Prune Time Period
    • Performance Tip: Eliminate S_ETL_R_IMG From the Change Capture Process
    • Performance Tip: Omit the Process to Eliminate Duplicate Records
    • Performance Tip: Manage Change Capture Views
    • Performance Tip: Determine Whether Informatica Filters on Additional Attributes
  • SQL for Change Capture and Change Capture Sync Processes
• Performance Tuning the ETL Process Using Tasks by Depth Command

12 Working With DAC Metadata Patches

• DAC Metadata Patching Life Cycle
• Creating a DAC Metadata Patch
  • Creating a Patch
  • About Patch Contents
  • Adding Contents to a Patch
  • Changing the Status of a Patch
• Exporting a DAC Metadata Patch
• Applying a DAC Metadata Patch to the DAC Repository
  • When Does a Patch Fail to be Applied to the DAC Repository?
  • Object Ownership During the Patching Process
• Exporting and Applying Patches Using the Command Line

13 Common Tasks Performed in DAC

• Accessing the DAC Server Using the Command Line
  • Setting Up Command Line Access to the DAC Server
  • Using the Command Line to Access the DAC Server
  • Command Line Status Monitoring Queries
• DAC Repository Command Line Utilities
  • Analyze DAC Schema
  • Apply Distributed Dev Patch
  • Assemble Subject Area
  • Build Execution Plan
  • Change Encryption Key
  • Clear Encrypted Data
  • Command Credentials
  • Create DAC Schema
  • Create DAC User
  • Create Data Warehouse Schema
  • Create Patch of Objects Between Time Stamps
  • Database Credentials
  • Delete Objects Execution Plan
  • Delete Objects Subject Area
  • Drop DAC Schema
  • Drop Data Warehouse Schema Using Schema Definition File
  • Export DAC Metadata by Application
  • Export DAC Metadata by Categories
  • Export Patch
  • JKS Password
  • Generate DW Schema Definition File
  • Import DAC Metadata by Application
  • Import DAC Metadata by Categories
  • Repository XML Snapshot
  • Server Setup
  • Set Password
  • Upgrade DAC Schema
  • Upgrade Data Warehouse Schema Using Schema Definition File
• Running the DAC Server Automatically (Standalone Mode)
• Running Two DAC Servers on the Same Machine
• Accessing Multiple DAC Servers Using One DAC Client
• Pointing Multiple Informatica Integration Services to a Single Informatica Repository
• Resetting the Data Warehouse
• Viewing DAC Metrics Using Fusion Middleware Control MBean Browser
• Monitoring the DAC Server Using WebLogic Server
• Exporting the Logical Data Model in Text or CSV Formats
• Adding Foreign Key Metadata Into DAC
• Copying Data From a Regular Warehouse to an In-Memory Database
  • Creating an External Executor of Type Data Copy
  • Creating Data Copy Tasks
  • Configuring DAC Parameters for Data Copy
  • Running Data Copy Processes
  • How DAC Handles Data Copy Processes

14 Integrating DAC With Other ETL Tools

• Interfaces That Need to Be Implemented for the External Executor Framework
  • DACExecutorDescriptor
  • DACExecutor
  • DACExecutorJob
• External Executor Utilities
  • DACExecutorConnectionHelper
  • DACExecutorLoggingUtils
• Registering an External Executor in DAC

15 Upgrading, Comparing and Merging DAC Repositories

• Major Stages of the Upgrade/Merge Wizard
• Resetting the Upgrade or Merge Process
• Overview of Upgrade and Merge Options
• About the Repository Upgrade (DAC 784) Option
  • Repository Upgrade (784): High-Level Process Flow
  • Repository Upgrade (784): Procedure for Upgrading
• About the Refresh Base Option
  • Refresh Base: High-Level Process Flow
  • Refresh Base: Procedure for Upgrading
• About the Simplified Refresh From Base Option
• About the Replace Base Option
  • Replace Base: High-Level Process Flow
  • Replace Base: Procedure for Upgrading
• About the Peer to Peer Merge Option
  • Peer to Peer Merge: High-Level Process Flow
  • Peer to Peer Merge: Procedure for Merging
• Resolving Object Differences in the View Difference Report
  • Overview of View Difference Report
  • View Difference Report Interface
  • Possible Repository Merge Outcomes Based on Your Decisions

16 DAC Functional Reference

• Menu Bar Commands
  • File Menu Commands
  • Views Menu Commands
  • Tools Menu Commands
    • DAC Repository Management Menu Commands
    • DAC Server Management Menu Commands
    • ETL Management Menu Commands
    • Seed Data Menu Commands
    • UI Styles Menu Commands
    • UI Preferences
  • Help Menu Commands
• Top Pane Toolbar Commands
• Right-Click Menu Commands
  • Common Right-Click Menu Commands
  • Design View Right-Click Menu Commands
  • Setup View Right-Click Menu Commands
  • Execute View Right-Click Menu Commands
• Common Elements of Interface Tabs
• Design View Tabs
  • Configuration Tags Tab
    • Configuration Tags Tab: Subject Areas Subtab
    • Configuration Tags Tab: Tasks Subtab
  • Indices Tab
    • Indices Tab: Actions Subtab
    • Indices Tab: Columns Subtab
  • Container Specific SQLs Tab
  • Source System Folders Tab
  • Source System Parameters Tab
  • Subject Areas Tab
    • Subject Areas Tab: Configuration Tags Subtab
    • Subject Areas Tab: Extended Tables (RO) Subtab
    • Subject Areas Tab: Tables Subtab
    • Subject Areas Tab: Tasks Subtab
    • Subject Areas Tab: Task Source Tables (RO) Subtab
    • Subject Areas Tab: Task Target Tables (RO) Subtab
  • Tables Tab
    • Tables Tab: Actions Subtab
    • Tables Tab: Conditional for Tasks (RO) Subtab
    • Tables Tab: Columns Subtab
    • Tables Tab: Indices (RO) Subtab
    • Tables Tab: Multi-Column Statistics Subtab
    • Tables Tab: Related Tables Subtab
    • Tables Tab: Source for Tasks (RO) Subtab
    • Tables Tab: Target for Tasks (RO) Subtab
  • Task Groups Tab
    • Task Groups Tab: Child Tasks Subtab
    • Task Groups Tab: Source Tables (RO) Subtab
    • Task Groups Tab: Target Tables (RO) Subtab
  • Tasks Tab
    • Tasks Tab: Actions Subtab
    • Tasks Tab: Conditional Tables Subtab
    • Tasks Tab: Configuration Tags Subtab
    • Tasks Tab: Extended Properties Subtab
    • Tasks Tab: During ETL Indices Subtab
    • Tasks Tab: Parameters Subtab
    • Tasks Tab: Phase Dependency Subtab
    • Tasks Tab: Refresh Date Tables Subtab
    • Tasks Tab: Refresh Dates (RO)
    • Tasks Tab: Source Tables Subtab
    • Tasks Tab: Subject Areas (RO) Subtab
    • Tasks Tab: Target Tables Subtab
• Setup View Tabs
  • Applied Patches Tab
    • Applied Patches Tab: Child Patches Subtab
    • Applied Patches Tab: Contents Subtab
    • Applied Patches Tab: Deleted Objects Subtab
  • DAC System Properties Tab
  • Email Recipients Tab
  • External Executors Tab
    • Properties Subtab
  • Informatica Servers Tab
  • Physical Data Sources Tab
    • Physical Data Sources Tab: Analyze Frequencies Subtab
    • Physical Data Sources Tab: Extended Properties Subtab
    • Physical Data Sources Tab: Index Spaces Subtab
    • Physical Data Sources Tab: Parallel Indexes Subtab
    • Physical Data Sources Tab: Refresh Dates Subtab
  • Working Patches Tab
    • Working Patches Tab: Child Patches Subtab
    • Working Patches Tab: Contents Subtab
    • Working Patches Tab: Audit Trails Subtab
• Execute View Tabs
  • Current Runs Tab
    • Current Runs Tab: Audit Trail (RO) Subtab
    • Current Runs Tab: Phase Summary (RO) Subtab
    • Current Runs Tab: Run Type Summary (RO)
    • Current Runs Tab: Tasks Subtab
    • Current Runs Tab: Task Details Subtab
  • Execution Instances Tab
    • Execution Instances Tab: All Dependencies Subtab
    • Execution Instances Tab: Concurrent Dependency Subtab
    • Execution Instances Tab: Connectivity Parameters Subtab
    • Execution Instances Tab: Execution Parameters Subtab
    • Execution Instances Tab: Immediate Dependencies Tab
    • Execution Instances Tab: Micro ETL Refresh Dates Subtab
    • Execution Instances Tab: Ordered Tasks Subtab
    • Execution Instances Tab: Subject Areas Subtab
    • Execution Instances Tab: Tables (RO) Subtab
  • Execution Plans Tab
    • Execution Plans Tab: All Dependencies Subtab
    • Execution Plans Tab: Concurrent Dependency
    • Execution Plans Tab: Connectivity Parameters Subtab
    • Execution Plans Tab: Execution Instances Subtab (RO)
    • Execution Plans Tab: Execution Parameters Subtab
    • Execution Plans Tab: Following Tasks Subtab
    • Execution Plans Tab: Immediate Dependencies Subtab
    • Execution Plans Tab: Ordered Tasks Subtab
    • Execution Plans Tab: Preceding Tasks Subtab
    • Execution Plans Tab: Micro ETL Refresh Dates Subtab
    • Execution Plans Tab: Subject Areas Subtab
    • Execution Plans Tab: Tables (RO) Subtab
  • Run History Tab
    • Run History Tab: Audit Trail (RO) Subtab
    • Run History Tab: Phase Summary (RO) Subtab
    • Run History Tab: Run Type Summary (RO) Subtab
    • Run History Tab: Tasks Subtab
    • Run History Tab: Task Details
  • Scheduler Tab

A Troubleshooting DAC

• Recovering From a Lost Encryption Key
• Restarting an Execution Plan When the DAC Server Fails
• Discarding a Failed Execution Plan
• Failure of Aggregator Transformation Tasks with Sorted Input
• In Case of Abnormal Termination of the DAC Server
• DAC Task Failing on Non-English Operating System
• DAC Task Failing in a Multi-Source Environment
• Restoring the DAC Repository on Unicode Oracle Databases
• Handling Parameter Files with Multi-Line Parameters
• Resetting Refresh Dates at the Task Level
• Error When Using Oracle(OCI8) Connection Type
• Tables Are Not Truncated When More Than One Task Writes to the Same Target Table
• Discrepancy Between DAC Task and Informatica Workflow
• Making the DAC Server Visible to All Clients

B Oracle TimesTen Database Guidelines

• About TimesTen as a Database Type
• Setting Up an ODBC Connection Between DAC and TimesTen Databases
• Pruning Tables and Columns for TimesTen Databases
• About DAC Support for Indexes in TimesTen Databases
• Customizing How Tables Are Analyzed in TimesTen Databases
• TimesTen Data Types

Index