Review PowerCenter Mapping Templates for Development Insight

Challenge

As the scope of Data Integration expands in an enterprise, it often leads to redundant development and maintenance efforts, resulting in increased costs. Informatica PowerCenter mapping templates demonstrate proven methods and solutions for tackling challenges that commonly occur during data integration development efforts. The mapping templates can be used to make the development phase of a project more efficient.

Description

An array of mapping template examples can be obtained for recent PowerCenter versions from the Informatica Customer Portal. Each of the templates illustrates the transformation logic and steps required to solve specific data integration requirements. These sample templates, however, are meant to be used as examples, not as a means to implement development standards. They are also useful however, for introducing good technique into the mapping development process for both new and experienced developers.

There are often instances where the same mapping logic can be applied to different source data. An example would be logic to determine the average spending of each department in a company, where the mapping states how the incoming data of department spending is determined. The logic can be applied to not just one department, but to every department of a company.

To implement the above scenario, creating a mapping for each department would add to development and testing efforts and result in more maintenance in the future. Simultaneously, presenting a challenge to ensure the same quality and coding standards. As the logic is similar for all scenarios, mapping templates provide ways to reuse the same logic in various places.

The reusability feature of mappings are provided in different ways such as Mapping Templates which, include Mapplets and Reusable Transformations.

Reuse Transformation Logic

Templates can be heavily used in a data integration and warehouse environment either, when loading information from multiple sources into the same target structure or, when a particular method is employed as a standard to load different target instances. Using templates helps to ensure that any transformation logic that is developed and tested correctly once, can then be successfully applied across multiple mappings as needed. In some instances, the process can be further simplified (if the source/target structures have the same attributes) by simply creating multiple instances of the session, each with its own connection and execution attributes, instead of duplicating the mapping.

Implementing Development Techniques

Using a mapping template can help to reproduce transformation techniques. It is most useful when the requirement is more than just replacing source and target transformations and certain logic (for example, a logical group of transformations) is employed across mappings. In many instances this can be further simplified by making use of mapplets. Additionally, reusable transformations and user defined functions can be utilized for expression reuse and building complex expressions using transformation language.

Benefits

Implementing the mapping templates provides the following benefits:

1. Reduced Development Effort: As the templates come with mapping and transformation logic, the development effort required will be to add required sources and targets and minimal changes to the mapping logic. Hence the development effort for creating various similar mappings based on a mapping template is significantly reduced.
2. Increased Consistency: The mapping templates can be reused in different scenarios. The code will be consistent throughout the repository, and be maintained in a single place. This helps achieve high quality and supports code standardization, as the same template is reused.
3. Easier Maintenance:  As the logic in a mapping template is reused across projects or the enterprise, it will reduce the time taken to resolve any critical issues. Enhancing the code and looking at its impact on the current implementation will be easier and faster than before. As the logic is re-used in the mapping templates, the code needs to exist only once.

Transport Mechanism

Once a mapping template has been developed, it can be distributed using the following options:

• Copy the mapping from the development area to the desired repository/folder
• Export the mapping template to XML and import into to the desired repository/folder.
• Using shared folders and shortcuts

Mapping Template Examples

The following Mapping Templates listed by subject area can be downloaded from the Informatica Customer Portal:

Common Data Warehousing Techniques

• Loading Incremental Updates
• Constraint-Based Loading
• Aggregation using Sorted Input
• Tracking Dimension History

Transformation Techniques

• Error Handling Strategy
• Removing Duplicate Source Records
• Creation of Header, Detail, and Trailer Records
• Pipeline Partitioning Mapping Template

Dynamic Caching Advanced Mapping Concepts

• Aggregation Using Expression Transformation
• Transaction Control Transformation

Taking Templates Further

While the templates above are examples, the principle can be applied in any repository by creating a folder with read access and populating it with mappings that implement business or technical logic for a given process. Logic that is expected to be heavily reused should be placed in mapplets for better maintenance efficiency. Once a mapping is designated as a template it can be placed in the templates folder. The mappings should be well described or documented independently. Taking this idea of mapping templates forward in this way can also help support an Integration Competency Center (ICC) that shares best practices.