You are previewing Learning Informatica PowerCenter 9.x.
O'Reilly logo
Learning Informatica PowerCenter 9.x

Book Description

Learn the art of extracting data from disparate systems, integrating it using the leading data integration platform, and creating intelligent data centers for your business

In Detail

Informatica PowerCenter provides the perfect platform to utilize and leverage business data. It allows you to easily, conveniently, and efficiently work on different types of data.

This book covers functionality such as creating/importing source and target, identifying errors, and debugging your mapping through a series of comprehensive tutorials. Besides learning about types of Slowly Changing Dimensions (SCDs), you will learn to create and link workflows. As you progress, exhaustive knowledge of transformations and techniques to create folders, migrate code, and optimize system performance will be explored in detail.

The step-by-step approach and adoption of real-time scenarios will guide you through effectively accessing all core functionalities offered by Informatica PowerCenter.

What You Will Learn

  • Install the components of the Informatica PowerCenter tool and customize the user account
  • Learn more about various graphical interface screens in Informatica
  • Work on various sections of the tool, such as navigator, toolbar, workspace, control panel, and so on
  • Leverage basic and advanced utilities, such as debugger, target load plan, and incremental aggregation to process data
  • Implement data warehousing concepts such as schemas and SCDs using Informatica
  • Migrate various components such as sources and targets to another region using the Designer and Repository Manager screens
  • Enhance code performance using the tips such as pushdown optimization and partitioning
  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. Learning Informatica PowerCenter 9.x
      1. Table of Contents
      2. Learning Informatica PowerCenter 9.x
      3. Credits
      4. About the Author
      5. Acknowledgments
      6. About the Reviewers
      7. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
          3. Instant updates on new Packt books
      8. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Errata
          2. Piracy
          3. Questions
      9. 1. Starting the Development Phase – Using the Designer Screen Basics
        1. Informatica PowerCenter Designer
        2. The Designer screen's components
          1. Working with sources
            1. Adding a new ODBC data source
            2. Working with relational database tables – the Import option
            3. Working with flat files – the Import option
              1. Working with delimited files
              2. Working with fixed-width files
            4. Working with sources – the Create option
          2. Working with targets
            1. Working with target relational database tables – the Import option
            2. Working with target flat files – the Import option
              1. Working with delimited files
              2. Working with fixed-width files
            3. Working with the target – the Create option
            4. Working with the target – the Copy option
          3. A feel of the data inside the repository – a preview
            1. Previewing the source data – flat files
            2. Previewing the source data – a relational table
          4. Creating a database table
          5. The skeleton – a mapping
        3. Summary
      10. 2. Using the Designer Screen – Advanced
        1. Debug me please – the debugger
        2. Reuse me please – reusable transformation
          1. Using Transformation Developer
          2. Making existing transformation reusable
          3. Mapplet
        3. Managing constraints – the target load plan
        4. I hate hardcoding – parameters and variables
        5. Comparing objects
        6. Summary
      11. 3. Implementing SCD – Using Designer Screen Wizards
        1. SCD1 – I hate history!
        2. SCD2 (version number) – I need my ancestors!
        3. SCD2 (flag) – flag the history
        4. SCD2 (date range) – marking the dates
        5. SCD3 – store something, if not everything!
        6. Summary
      12. 4. Finishing the Development – Using the Workflow Manager Screen Basics
        1. Using Workflow Manager
        2. Creating a workflow
          1. Creating a workflow manually
          2. Creating a workflow automatically
        3. Adding tasks in a workflow
          1. Adding tasks to the workflow directly
          2. Creating nonreusable tasks – Task Developer
          3. Adding tasks to the workflow – Task Developer
        4. Working with the session task – basic properties
        5. Connecting the tasks – links
        6. Assigning Integration Service to a workflow
        7. Deleting a workflow
        8. Trigger – starting a workflow
          1. Running the complete workflow
          2. Running a part of the workflow
          3. Running a task
        9. Working with connection objects
          1. Creating a connection object
          2. Configuring a relational database
        10. Summary
      13. 5. Using the Workflow Manager Screen – Advanced
        1. Working with tasks
        2. Configuring a task
        3. The session task
          1. Tabs of the session task
          2. Creating a session task
        4. The command task
          1. Creating a command task
        5. The e-mail task
          1. Creating an e-mail task
        6. The assignment task
          1. Creating an assignment task
        7. The timer task
          1. Creating a timer task
        8. The control task
          1. Creating a control task
        9. The decision task
          1. Creating a decision task
        10. Event tasks – event wait and event raise
          1. Creating an event (wait/raise) task
        11. The link task
          1. Creating a link task
        12. Worklets – groups of tasks
          1. Creating a worklet
        13. Schedulers
        14. File List – the indirect way
        15. Incremental aggregation
        16. The parameter file – parameters and variables
          1. Defining session-level variables
          2. Defining workflow-level variables
          3. Defining mapping-level variables
          4. Creating the parameter file
          5. Mentioning the parameter file at the workflow level
          6. Mentioning the parameter file at the session level
        17. Summary
      14. 6. Monitoring Your Code – Using the Workflow Monitor Screen
        1. Using Workflow Monitor
        2. Connecting to the Workflow Manager screen
        3. Opening previous workflow runs
        4. Running or recovering workflows or tasks
        5. Stopping or aborting the workflow or task
        6. The status of workflows and tasks
        7. Viewing the session log and workflow log
        8. Working with the workflow log
        9. Working with the session log
        10. Viewing workflow run properties
        11. Viewing session run properties
          1. Properties of task details
          2. Properties of source/target statistics
        12. Common errors
        13. Summary
      15. 7. The Lifeline of Informatica – Transformations
        1. Creating the transformation
          1. Mapping Designer
          2. Mapplet Designer
          3. Transformation Developer
        2. The Expression transformation
        3. Ports in transformations
        4. Using the expression editor
        5. The Aggregator transformation
          1. Using Group By
          2. Using Sorted Input
        6. The Sorter transformation
        7. The Filter transformation
        8. The Router transformation
        9. The Rank transformation
          1. Group by ranking
          2. Rank index
        10. The Sequence Generator transformation
          1. Ports of the Sequence Generator transformation
          2. Properties of the Sequence Generator transformation
        11. The Joiner transformation
          1. Master and detail pipeline
          2. Join condition
          3. Join type
            1. Normal join
            2. Full join
            3. Master outer join
            4. Detail outer join
        12. Union transformation
        13. Source Qualifier transformation
          1. Viewing the default query
          2. Overriding the default query
            1. Using the WHERE clause
            2. Joining the source data
            3. Sorting the data
            4. Selecting distinct records
        14. Classification of transformations
          1. Active and passive
          2. Connected and unconnected
        15. The Lookup transformation
          1. Creating a Lookup transformation
          2. Configuring the Lookup transformation
          3. Setting up the Lookup transformation
          4. Lookup ports
          5. Lookup queries
          6. Unconnected Lookup transformations
          7. Lookup transformation properties
        16. The Update Strategy transformation
        17. The Normalizer transformation
          1. Configuring the Normalizer transformation – ports
        18. The Stored Procedure transformation
          1. Importing Stored Procedure transformations
          2. Creating Stored Procedure transformations
          3. Using Stored Procedure transformations in the mapping
            1. Connected Stored Procedure transformations
            2. Unconnected Stored Procedure transformations
        19. Transaction Control transformations
        20. Types of lookup cache
          1. Building the cache – sequential or concurrent
            1. Sequential cache
            2. Concurrent cache
          2. Persistent cache – the permanent one
          3. Sharing cache – named or unnamed
            1. Sharing unnamed cache
            2. Sharing named cache
          4. Modifying cache – static or dynamic
            1. Static cache
            2. Dynamic cache
        21. Tracing levels
        22. Summary
      16. 8. The Deployment Phase – Using Repository Manager
        1. Using Repository Manager
        2. Configuring the client tools
        3. Take me to next stage – deployment or migration
          1. Export/Import
            1. Migrating from the designer
            2. Migrating from Repository Manager
          2. The Copy/Paste feature
          3. The drag-and-drop feature
        4. Summary
      17. 9. Optimization – Performance Tuning
        1. Bottlenecks
          1. Finding the target bottleneck
            1. Using thread statistics
            2. Configuring the sample target load
          2. Eliminating the target bottleneck
            1. Minimizing the target table deadlocks
            2. Drop indexes and constraints
            3. Increasing the checkpoint interval
            4. Using an external loader
            5. Increasing the network packet size
            6. Using the bulk load
          3. Finding the source bottleneck
            1. Using thread statistics
            2. Test mapping
            3. Using a Filter transformation
            4. Checking the database query
          4. Eliminating the source bottleneck
            1. Increasing the network packet size
            2. Optimizing the database query
          5. Finding the mapping bottleneck
            1. Using thread statistics
            2. Using a Filter transformation
          6. Eliminating the mapping bottleneck
            1. Using a single pass mapping
            2. Avoiding data type conversions
            3. Unchecking unnecessary ports
            4. Processing the numeric data
            5. Using operators instead of functions
            6. Using decode in place of multiple IIF functions
            7. The tracing level
            8. Using variable ports
            9. Optimizing Filter transformations
            10. Optimizing the Aggregator transformation
            11. Optimizing the Joiner transformation
            12. Optimizing Lookup transformations
          7. Eliminating the session bottleneck
            1. Optimizing the commit interval
            2. The buffer memory
            3. Performance data
          8. Eliminating the system bottleneck
        2. Working on partitioning
          1. Partitioning properties
            1. Partition points
            2. The number of partitions
            3. Partition types
        3. Pushdown optimization
        4. Summary
      18. A. Installing Informatica and Using Informatica Administration Console
        1. The Informatica architecture
          1. Domain
          2. Node
          3. Informatica services
          4. The service manager
          5. Repository
          6. Repository services
          7. Integration Services
        2. Informatica installation – the prerequisites
        3. Downloading the Informatica PowerCenter software
        4. Extracting the package – opening the installable
        5. Starting the installation
          1. Installing the server
            1. Configuring the domain and node
          2. Client installation – the graphical interface tools
          3. The administration console – configuration
            1. Repository creation – the first centralized database for Informatica
            2. Creating the Integration Service – the path for data
            3. Informatica user and authentications
            4. Repository Manager – the client configuration
      19. Index