PowerCenter: Developer, Level 2 (PCDEV2)(10)

Enhance your developer skills with advanced techniques and functions for PowerCenter. This course focuses on additional transformations and transaction controls, as well as, teaches performance tuning and troubleshooting for an optimized PowerCenter environment.
 
Objectives
After successfully completing this course, students should be able to:
  • Determine the structure and use of PowerCenter Parameter Files
  • Implement user-defined and advanced functions.
  • Normalize and Denormalize data using PowerCenter
  • Use the Lookup transformation in Dynamic mode
  • Call a SQL stored procedure from a PowerCenter mapping
  • Create and configure a SQL transformation and its two modes of use
  • Design error handling strategies appropriate for the intended purpose of a workflow
  • Make use of the PowerCenter source-based, target-based, and user-based transaction control
  • Utilize constraint-based loading in databases with referential integrity constraints
  • Use the Transaction Control transformation for data-driven RDBMS transaction control
  • Determine the proper use of built-in and optional, mapping-design recovery capabilities
  • Build batch files that use PMCMD and PMREP command line programs
  • Apply PowerCenter Performance Tuning Methodology
  • Describe the effect of mapping design on performance and apply these design principles to a mapping
  • Calculate how much memory a session uses and tune session-level memory
  • Apply partitions, distribute the data and optimize the CPU memory usage
 
Target Audience
  • Developers
 
Prerequisites
PowerCenter: Data Integration for Developers
 
Show details
Agenda
Module 1: PowerCenter Architecture
  • Informatica PowerCenter 10 architecture and key terms
  • PowerCenter’s optional and built-in high availability features
Module 2: Parameter Files
  • IsExprVar property in a mapping
  • Structure of a parameter file
  • Parameter files in mappings and sessions
  • Parameter files used to build mapping expression logic
  • Date/time mapping variable, in a parameter file for incremental loading
Module 3: User-Defined andAdvanced Functions
  • Advanced functions
  • User-Defined functions
  • Standard name formatting function and implementing UDF in mappings
  • AES_Encrypt and Encode functions
  • Debug the mapping
Module 4: Pivoting Data
  • Normalizer transformation
  • Aggregators
  • Normalization of data into a relational table
  • Denormalization of data into a Fact table
Module 5: Dynamic Lookups
  • Dynamic Lookup Cache
  • Dynamic Lookup to load data into a dimension table
  • Dynamic Lookup used in tandem with an Update Strategy transformation to keep historic data in a dimension table
Module 6: Stored Procedure and SQL Transformations
  • SQL stored procedures
  • SQL transformations in script mode
  • SQL transformations in query mode
  • SQL transformation used to create tables
  • Proper query formatting for SQL transformation
  • Database errors
Module 7: Troubleshooting Methodology and Error Handling
  • Error handling strategies
  • Data errors
  • Update Strategies
Module 8: Transaction Processing
  • Source-based, targetbased, and userbased transaction control with and without the High Availability option
  • Constraint-based loading in databases with referential integrity constraints
  • Table Loading and the RDBMS Primary- Foreign key relationship
Module 9: Transaction Control Transformation
  • Transaction control transformation for data-driven transaction control
  • Data Control
  • Transformation variables
Module 10: Recovery
  • Workflow and task recovery with and without the high availability option
  • Recover tasks and workflows
Module 11: Command-Line Programs
  • PMCMD, PMREP, and INFACMD command line functionality
  • Batch files that use PMCMD and PMREP
  • Command line utilities
Module 12: Performance Tuning Methodology
  • Source, target and engine bottlenecks
  • Performance counters
  • Tuning different types of bottlenecks
  • Benchmark tests
  • Target bottleneck tests
  • Results evaluation
Module 13: Performance Tuning Mapping Design
  • Best practices in mappings
  • Session properties
  • Inspect and edit mappings
  • Inspect and edit transformations
Module 14: Memory Optimization
  • Session-level memory
  • Transformation caches
  • PowerCenter Performance Counters
Module 15: Performance Tuning: Pipeline Partitioning
  • Partition points
  • Data Partitions
  • Memory optimization