Network
Data Engineering
Data Engineering Integration
Enterprise Data Catalog
Enterprise Data Preparation
Cloud Integration
Cloud Application Integration
Cloud Data Integration
Cloud Customer 360
DiscoveryIQ
Cloud Data Wizard
Informatica for AWS
Informatica for Microsoft
Cloud Integration Hub
Complex Event Processing
Proactive Healthcare Decision Management
Proactive Monitoring
Real-Time Alert Manager
Rule Point
Data Integration
B2B Data Exchange
B2B Data Transformation
Data Integration Hub
Data Replication
Data Services
Data Validation Option
Fast Clone
Informatica Platform
Metadata Manager
PowerCenter
PowerCenter Express
PowerExchange
PowerExchange Adapters
Data Quality
Axon Data Governance
Data as a Service
Data Explorer
Data Quality
Data Security Group (Formerly ILM)
Data Archive
Data Centric Security
Secure@Source
Secure Testing
Master Data Management
Identity Resolution
MDM - Relate 360
Multidomain MDM
MDM Registry Edition
Process Automation
ActiveVOS
Process Automation
Product Information Management
Informatica Procurement
MDM - Product 360
Ultra Messaging
Ultra Messaging Options
Ultra Messaging Persistence Edition
Ultra Messaging Queuing Edition
Ultra Messaging Streaming Edition
Edge Data Streaming
Knowledge Base
Resources
PAM (Product Availability Matrices)
Support TV
Velocity (Best Practices)
Mapping Templates
Debugging Tools
User Groups
Documentation
English
English
English
Español
Spanish
Deutsch
German
Français
French
日本語
Japanese
한국어
Korean
Português
Portuguese
中文
Chinese
Log Out
Log In
Sign Up
PowerCenter
10.2
H2L
10.4.1
10.4.0
10.2 HotFix 2
10.2 HotFix 1
10.2
10.1.1 HotFix 1
10.1.1 HotFix 2
10.1.1
10.1
10.0
Designer Guide
PowerCenter
All Products
Table of Contents
Search
No Results
Preface
Using the Designer
Using the Designer Overview
Designer Tools
Designer Windows
Configuring Designer Options
Configuring General Options
Configuring Tables Options
Configuring Format Options
Using Color Themes
Configuring Debug Options
Configuring Web Service Options
Configuring Miscellaneous Options
Using Toolbars
Designer Toolbars
Workflow Manager Toolbars
Workflow Monitor Toolbars
Repository Manager Toolbars
Displaying a Toolbar
Creating a Toolbar
Configuring a Toolbar
Adding Custom Tools
Navigating the Workspace
Search Tools
Find Next
Find in Workspace
Iconizing and Restoring Workspace Objects
Iconizing Workspace Objects
Restoring Workspace Objects
Arranging Workspace Objects
Zooming the Workspace
Viewing the Workspace
Designer Tasks
Adding a Repository
Printing the Workspace
Setting up the Workspace Printout
Previewing the Workspace Print Out
Viewing the Last Saved Date/Time
Opening and Closing a Folder
Creating Shortcuts
Checking Out and In Versioned Objects
Searching for Objects
Entering Descriptions for Repository Objects
Viewing and Comparing Versioned Repository Objects
Rules and Guidelines for Viewing and Comparing Versioned Repository Objects
Viewing an Older Version of a Repository Object
Comparing Versions of a Repository Object
Reverting to a Previous Object Version
Copying Designer Objects
Copying Mapping Segments
Copying Objects as an Image
Exporting and Importing Objects
Working with Multiple Ports or Columns
Renaming Ports
Using Shortcut Keys
Previewing Data
Previewing Relational Data
Previewing Flat File Data
Previewing XML Data
Working with Metadata Extensions
Creating Metadata Extensions
Editing Metadata Extensions
Editing Reusable Metadata Extensions
Editing Non-Reusable Metadata Extensions
Deleting Metadata Extensions
Using Business Names
Adding Business Names to Sources or Targets
Displaying Business Names in the Navigator
Displaying Business Names as Column Names
Using Business Names as Port Names in Source Qualifiers
Using Business Documentation
Specifying the Documentation Path
Creating Links to Documentation Files
Viewing Business Documentation
Viewing Mapplet and Mapping Reports (Deprecated)
Viewing a Mapplet Composite Report
Viewing a Mapping Composite Report
Working with Sources
Working with Sources Overview
Oracle Sources
Special Character Handling in Source Definitions
Updating Source Definitions
Creating Sessions
Working with Relational Sources
Relational Source Definitions
Connectivity for Relational Sources
Configuring a Third-Party ODBC Data Source
Importing Relational Source Definitions
Updating a Relational Source Definition
Editing Relational Source Definitions
Reimporting a Source Definition
Creating a Source Definition from a Target Definition
Working with COBOL Sources
Importing COBOL Sources
Working with COBOL Copybooks
Steps to Import a COBOL Source Structure
Components in a COBOL Source File
FD Section
Fields
OCCURS
REDEFINES
Configuring COBOL Source Definitions
Configuring the Table Tab
Configuring Advanced Properties
Configuring the Columns Tab
OCCURS
FD Section
Field Attributes
Importing a Microsoft Excel Source Definition
Defining Ranges
Formatting Columns of Numeric Data
Steps to Import a Microsoft Excel Source Definition
Manually Creating a Source Definition
Troubleshooting Sources
Working with Flat Files
Working with Flat Files Overview
Creating Sessions with Flat File Sources and Targets
Importing Flat Files
Special Character Handling
Selecting Code Pages
Changing Display Fonts
Resetting Designer Formats
Installing a Language
Importing Fixed-Width Flat Files
Importing Delimited Flat Files
Editing Flat File Definitions
Editing Table Options
Editing Columns
Updating Fixed-Width File Properties
Null Character Handling
Updating Delimited File Properties
Rules and Guidelines for Delimited File Settings
Formatting Flat File Columns
Formatting Numeric Columns
Numeric Data
Field Width
Format Column
Formatting Datetime Columns
Format String
Field Width
Format Column
Defining Default Datetime and Numeric Formats
Working with File Lists
Working with Shift-Sensitive Flat Files
Importing Flat Files with Shift Keys
Requirements for Shift-Sensitive Flat Files
Importing Flat Files without Shift Keys
Working with Multibyte Data in Fixed-Width Targets
Troubleshooting Flat Files
Working with Targets
Working with Targets Overview
Creating Target Definitions
Maintaining Targets and Target Definitions
Oracle Targets
Target Code Pages
Special Character Handling in Target Definitions
Importing a Target Definition
Relational Target Definitions
Connectivity for Relational Targets
Bulk Upsert for SAP HANA Targets
Configuring a Third-Party ODBC Data Source
Importing Relational Target Definitions
Creating a Target Definition from a Source Definition
Creating a Target Definition from a Relational Source
Creating a Target Definition from a Flat File Source
Creating a Normalized Target from a COBOL Source
Steps to Create a Target Definition from a Source Definition
Creating a Target Definition from a Transformation
Creating a Target from a Transformation with One Output Group
Creating a Target from a Transformation with Multiple Output Groups
Creating a Target from a Normalizer Transformation
Creating a Target from a Mapplet
Transformation and Target Datatypes
Steps to Create a Target
Steps to Create a Target in the Target Designer
Steps to Create a Target in the Mapping Designer
Manually Creating a Target Definition
Maintaining Relational Target Definitions
Reimporting a Relational Target Definition
Creating a Primary Key-Foreign Key Relationship
Editing Table Options
Editing Columns
Defining Indexes
Creating a Target Table
SQL DDL Commands in the Designer
Dropping and Re-creating Indexes
Re-creating Targets
Troubleshooting Targets
Mappings
Mappings Overview
Object Dependency
Developing a Mapping
Working with Mappings
Creating a Mapping
Opening a Mapping
Copying a Mapping
Copying Mapping Segments
Using the Copy As Command
Copying Mapping Objects
Exporting and Importing a Mapping
Editing a Mapping
Reverting to a Previously Saved Mapping
Renaming and Adding Comments to a Mapping
Invalidating Sessions
Debugging a Mapping
Deleting a Mapping
Viewing Link Paths to a Port
Viewing Source Column Dependencies
Connecting Mapping Objects
Options for Linking Ports
Linking One to Many
Linking Many to One
Rules and Guidelines for Connecting Mapping Objects
Linking Ports
Manually Linking Ports
Linking Ports by Position
Linking Ports using the Autolink Dialog Box
Linking Ports Using the Autolink by Position Command
Linking Ports by Name
Linking Ports by Name using the Autolink Dialog Box
Linking Ports by Name and Prefix/Suffix using the Autolink Dialog Box
Linking Ports by Name Using the Autolink by Name Command
Propagating Port Attributes
Understanding Dependency Types
Propagating Dependencies in a Link Path
Example
Propagating Implicit Dependencies
Example
Propagated Attributes by Transformation
Rules and Guidelines for Propagating Ports and Attributes
Steps to Propagate Port Attributes
Working with Sources in a Mapping
Working with Relational Sources in a Mapping
Working with Transformations in a Mapping
Working with Mapplets in a Mapping
Working with Targets in a Mapping
Configuring Relational Targets in a Mapping
Configuring Flat File Targets in a Mapping
Configuring XML Targets in a Mapping
Setting the Target Load Order
Creating Target Files by Transaction
Configuring the Target
Configuring the Mapping
Running the Session
Rules and Guidelines for Creating Target Files by Transaction
Example
Working with Relational Targets in a Mapping
Rejecting Truncated and Overflow Data
Configuring the Target Update Override
Overriding the WHERE Clause
Rules and Guidelines for Configuring the Target Update Override
Steps to Enter a Target Update Statement
Configuring the Table Name Prefix
Adding Pre- and Post-Session SQL Commands
Rules and Guidelines for Adding Pre- and Post-Session SQL Commands
Overriding the Target Table Name
Validating a Mapping
Connection Validation
Expression Validation
Object Validation
Data Flow Validation
Steps to Validate a Mapping
Validating Multiple Mappings
Using the Workflow Generation Wizard
Workflow Generation Wizard Steps
Troubleshooting Mappings
Mapplets
Mapplets Overview
Understanding Mapplet Input and Output
Mapplet Input
Using Source Definitions for Mapplet Input
Using Input Transformations for Mapplet Input
Mapplet Output
Viewing Mapplet Input and Output
Using the Mapplet Designer
Creating a Mapplet
Validating Mapplets
Editing Mapplets
Mapplets and Mappings
Using Mapplets in Mappings
Creating and Configuring Mapplet Ports
Connecting to Mapplet Input Ports
Connecting to Mapplet Output Groups
Viewing the Mapplet
Setting the Target Load Plan
Pipeline Partitioning
Rules and Guidelines for Mapplets
Tips for Mapplets
Mapping Parameters and Variables
Mapping Parameters and Variables Overview
Mapping Parameters
Mapping Variables
Using Mapping Parameters and Variables
Initial and Default Values
Using String Parameters and Variables
Using Datetime Parameters and Variables
Code Page Relaxation
Mapping Parameters
Step 1. Create a Mapping Parameter
Step 2. Use a Mapping Parameter
Step 3. Define a Parameter Value
Mapping Variables
Variable Values
Start Value
Current Value
Variable Datatype and Aggregation Type
Variable Functions
Mapping Variables in Mapplets
Using Mapping Variables
Step 1. Create a Mapping Variable
Step 2. Set a Mapping Variable Value
Step 3. Override or Clear Saved Values
Defining Expression Strings in Parameter Files
Tips for Mapping Parameters and Variables
Troubleshooting Mapping Parameters and Variables
Working with User-Defined Functions
Working with User-Defined Functions Overview
Example
Creating User-Defined Functions
Configuring the Function Type
Configuring Public Functions that Contain Private Functions
Steps to Create a User-Defined Function
Managing User-Defined Functions
Editing User-Defined Functions
Deleting User-Defined Functions
Exporting User-Defined Functions
Validating User-Defined Functions
Copying and Deploying User-Defined Functions
Creating Expressions with User-Defined Functions
Using the Debugger
Using the Debugger Overview
Debugger Session Types
Debug Process
Creating Breakpoints
Selecting the Instance Name
Creating Error Breakpoints
Evaluating Error Breakpoints
Creating Data Breakpoints
Entering the Data Breakpoint Condition
Entering a Transformation Data Condition
Entering a Global Data Condition
Using ISNULL and ISDEFAULT
Steps to Enter Breakpoints
Editing a Breakpoint
Configuring the Debugger
Step 1. Debugger Introduction
Step 2. Select Integration Service and Session Type
Step 3. Select Session Information
Running an Existing Session in Debug Mode
Running a Debug Session
Step 4. Set Session Configuration
Step 5. Set Target Options
Running the Debugger
Initializing State
Running State
Paused State
Debugger Tasks
Working with Persisted Values
Designer Behavior
Monitoring the Debugger
Monitoring Debug Indicators
Monitoring Transformation Data
Continuing the Debugger
Monitoring Target Data
Monitoring the Debug Log
Using the Workflow Monitor
Modifying Data
Restrictions
Evaluating Expressions
Evaluating Expressions Using Mapping Variables
Steps to Evaluate Expressions
Copying Breakpoint Information and Configuration
Transferring Breakpoints and Configuration
Troubleshooting the Debugger
Viewing Data Lineage
Viewing Data Lineage Overview
Configuring Data Lineage Access
Running Data Lineage from the Designer
Comparing Objects
Comparing Objects Overview
Comparing Sources, Targets, and Transformations
Comparing Mappings and Mapplets
Managing Business Components
Managing Business Components Overview
Business Component Locking
Creating Links to Business Component Documentation
Managing Business Components and Directories
Creating and Editing a Directory
Editing a Directory
Creating a Business Component
Creating Business Components from a Copy
Creating Business Components using Shortcuts
Deleting a Directory or Business Component
Deleting a Business Component
Copying a Directory or Business Component
Creating Cubes and Dimensions
Creating Cubes and Dimensions Overview
Understanding Multi-Dimensional Metadata
Key Elements of Multi-Dimensional Metadata
Creating a Dimension
Step 1. Create a Dimension
Step 2. Add Levels to the Dimension
Step 3. Add Hierarchies to the Dimension
Step 4. Add Levels to the Hierarchy
Creating a Cube
Editing a Cube
Editing a Dimension
Deleting a Cube or Dimension
Opening and Closing a Cube
Viewing Metadata for Cubes and Dimensions
Tips for Cubes and Dimensions
Using the Mapping Wizards
Maintaining Star Schemas
Understanding the Mapping Wizards
Using the Getting Started Wizard
Using the Slowly Changing Dimensions Wizard
Choosing Sources for the Mappings
Creating a Pass-Through Mapping
Understanding the Mapping
Understanding the Transformations
Steps to Create a Pass-Through Mapping
Customizing the Mapping
Configuring a Pass-Through Session
Creating a Slowly Growing Target Mapping
Handling Keys
Understanding the Mapping
Understanding the Transformations
Steps to Create a Slowly Growing Target Mapping
Configuring a Slowly Growing Target Session
Creating a Type 1 Dimension Mapping
Handling Keys
Understanding the Mapping
Understanding the Transformations
Data Flow for New Rows
Data Flow for Changed Rows
Steps to Create a Type 1 Dimension Mapping
Configuring a Type 1 Dimension Session
Creating a Type 2 Dimension/Version Data Mapping
Handling Keys
Numbering Versions
Understanding the Mapping
Understanding the Transformations
Data Flow for New Rows
Data Flow for Changed Rows
Steps to Create a Type 2 Dimension/Version Data Mapping
Customizing the Mapping
Configuring a Type 2 Dimension/Version Data Session
Creating a Type 2 Dimension/Flag Current Mapping
Flagging the Current Value
Handling Keys
Understanding the Mapping
Understanding the Transformations
Data Flow for New Rows
Data Flow for Changed Rows
Data Flow to Update Existing Rows
Steps to Create a Type 2 Dimension/Flag Current Mapping
Configuring a Type 2 Dimension/Flag Current Session
Creating a Type 2 Dimension/Effective Date Range Mapping
Maintaining the Effective Date Range
Handling Keys
Understanding the Mapping
Understanding the Transformations
Data Flow for New Rows
Data Flow for Changed Rows
Data Flow to Update Existing Rows
Steps to Create a Type 2 Dimension/Effective Date Range Mapping
Configuring a Type 2 Dimension/Effective Date Range Session
Creating a Type 3 Dimension Mapping
Saving Previous Values
Handling Keys
Marking the Effective Date
Understanding the Mapping
Understanding the Transformations
Data Flow for New Rows
Data Flow to Update Existing Rows
Steps to Create a Type 3 Dimension Mapping
Configuring a Type 3 Dimension Session
Creating Targets in the Target Database
Scheduling Sessions and Workflows
Creating a Mapping from the Informatica Mapping Templates
Step 1. Select the Mapping Template
Step 2. Specify Mapping Details and Parameter Values
Step 3. Create Mappings and Save Parameter Values
Step 4. Import Mappings into the Repository
Datatype Reference
Datatype Reference Overview
Transformation Data Types
Integer Data Types
Integer Values in Calculations
Integer Constants in Expressions
NaN Values
Convert String Values to Integer Values
Write Integer Values to Flat Files
Binary Data Type
Date/Time Data Type
Decimal and Double Data Types
Decimal and Double Values in Calculations
Rounding Methods for Double Values
String Data Types
IBM DB2 and Transformation Datatypes
Informix and Transformation Datatypes
Datatype Synonyms
Microsoft SQL Server and Transformation Datatypes
Datatype Synonyms
Uniqueidentifier Datatype
Oracle and Transformation Datatypes
Number(P,S) Datatype
Char, Varchar, Clob Datatypes
SAP HANA and Transformation Datatypes
Sybase and Transformation Datatypes
Datatype Synonyms
Binary and Varbinary Datatypes for Sybase IQ
Teradata and Transformation Datatypes
Datatype Synonyms
ODBC and Transformation Datatypes
COBOL and Transformation Datatypes
Flat File and Transformation Datatypes
Number Datatype
XML and Transformation Datatypes
Converting Data
Port-to-Port Data Conversion
Converting Strings to Dates
Converting Strings to Numbers
Configure the Web Browser
Configure the Web Browser
Designer Guide
Designer Guide
10.2
10.4.1
10.4.0
10.2 HotFix 2
10.2 HotFix 1
10.1.1
10.1
10.0
Back
Next
Handling Keys
Handling Keys
In the Type 3 Dimension mapping, the Integration Service generates a primary key value for each new row written to the target, incrementing key values by one. Updated rows retain their original key values.
Creating a Type 3 Dimension Mapping
Updated July 02, 2020
Download this guide
Explore Informatica Network
Communities
Knowledge Base
Success Portal
Back to Top
Back
Next
