Informatica Developer: Tips & Tricks for Mapping Designer

We will cover :

• Top 10 Mapping Development Mistakes
• Designer Tips & Tricks
• PC 8.6 New Features
• General Best Practices

Development Mistakes : you should avoid if possible
1. SQL Overrides :

• Pushes transformation to DBMS instead of Informatica Server – cannot 
use partitioning
• Lose the transformation logic in metadata searches
• Unable to utilize push down optimization

2. Lookups

• Reduce Index and Cache sizes
• Not using Unconnected Lookups when only returning 1 port
• Connected Lookups should only have the ports being returned or in the condition all other ports removed.

3. Improper use of Stored Procedures/Functions

• Used to do all query and transformation logic
• Should only be used for specific needs
• Proprietary Code

4. Too much work in one Process Flow 

• Need to de-couple
• More upfront design and architecture time
• Causes debugging and maintainability issues
• Use of Mapplets to segment mapping
• Use of concurrent or serial sessions

5.  Failure to get volumetric analysis

• How much data are you really working with.
• What is the size of the data.
• Set proper sizing attributes for mapping and transformation objects

6.  Failure to get source data analysis

• What is the condition of the source data
• Lots of NULL’s 
• Does source data match business cases.
    • If gender field should only be M,F but has M,F,NULL,Male, Female
• Cleanse the data before transformation
• Identify and cleanse source data before transformation

7. Too many Filters use Router

• Before Router use of Filters was required
• Now use a Router to simplify mappings

8. Overuse of Custom and Java Transformations

• Only use when required

9. Too many Expression Transformations

• Cause Mapping to become over complex
• No Maintainability
• Do more with less

10. Unsorted data before Aggregator

• Place Sorter Transformation before Aggregator
• Set Aggregator for Sorted Input
• With large volumes of data huge performance gain/loss
• Most want to put order by clause in SQ not to do


Designer Tips & Tricks
1. Propagate Ports

• Propagation bi-directionally for many Attributes
• Name
• Data Type
• Precision
• Description
• Scale

2 . Mapping Template Wizards

• Faster Creation of Mappings
• Type 1 – Type 3 Dim
• Remove Duplicates
• Incremental Loads
• Custom Templates


PC 8.6 : New Features

8.6 New features (Command Line)

• New pmrep command – MassUpdate: Use this command when you want to update a set of 
sessions based on specific conditions

• pmrep>massUpdate -t session_property -n 
"Commit Interval" -v 10 -f Demo

• Oracle subpartition support
• Pushdown Optimization
        • Compatible Connections
        • Subquery Support
        • Netezza Support

• Real-Time
        • JMS Resiliency
        • Streaming XML

• Synchronized Dynamic Cache

Transformations :
• Data Masking
• Expression Masking
• Key Masking
• Parameterized seed values
• Masking Social Security Numbers
• Company Names sample data

• Unstructured Data
    • Streamer Chunk Size
        • When B2B/Data Transformation runs as a Streamer this value will buffer the incoming data 
        from  1 – 1,000,000 KB
        • Dynamic Service Name
                • When checked a Service Input Port will appear allowing each input row to call
                 a different B2B/Data Transformation project

        • Populate from Service
                • Allows access to predefined ports in the B2B/Data Transformation Mapping to be
                 exposed  a directs ports in the Unstructured Data Transformation.
     • Relation Hierarchy
                • To pass row data to relational tables or other targets define groups of ports and define a 
                relational structure for the groups


General Best Practices

• Follow proper Naming conventions
• Modularity
• Avoid implicit data conversion
• Reuse as much as possible
• Only connect source ports which are used 
• Limit reads on source