This article aims to list some of the best practices when it comes to conducting developments in Informatica PowerCenter.
However, if we look more broadly at these recommendations, it is easy to conclude that these practices can be used in other tools, even in our everyday developments.
Let me give you some of these good practices then:
1. Use of Shared Folder for common/shared objects through different integrations
In each of repositories, there is a DW_COMMON_OBJECTS folder. In this folder, it is possible to place all the Sources, Targets and Transformations which are common or for sharing between different integrations. For use these objects in other repository folders we just need to create a shortcut based on the original.
Fig. 1 – Folder with common objects
Fig. 2 – Integration folder shortcuts
Fig. 3 – Use of shortcuts in mapping
Therefore, we can ensure that if we need to change one of these objects, we can do it in DW_COMMON_OBJECTS, which automatically gets reflected in all mappings where the shortcut for the object exists.
2. Stop on Errors in sessions
In sessions configurations, the parameter “Stop on Errors” must always have the value 1.
Fig. 4 – Parameter location in sessions proprieties
3. Source Qualifiers and Lookups without SQL Override.
Native PWC transformations should always be used instead of Source Qualifier SQL override. Exceptions to using it will be occasional instances where hints need to be used:
I. Using Native PWC transformations allows to make use of PWC map partitioning.
II. Using Native PWC transformations allows to make PWC pushdown optimization.
The same logic must be applied when we use Lookups.
4. Data types and port accuracy
Linking between ports must always ensure the same type of data and accuracy. If data type and precision transformation are required, PWC Expression transformations should be used.
Fig. 5 – Expression Transformation Object
Fig. 6 – Expression Transformation Location on the PWC Designer Toolbar
5. Complex Mappings
Mappings with more than 40 transformations must be broken within the same mapping or made into more than one mapping.
6. Comments and Descriptions
In mappings, sessions, mapllets and workflows, comments or descriptions must be filled in regarding the purpose of the object as well as alerts or particulars. The same logic must apply to objects within a mapping.
7. Rename the transformations
All transformations in a mapping must be assigned the default name assigned by the PWC.
8. Commit Interval of Sessions
The Commit Interval should not be the default of 10000. The developer should take some time to realise if this value should be incremented or not. To do this, some mapping runs have to be done to understand its behaviour (execution time increases or decreases).
9. Session Memory Parameters
Session Memory Parameters should never have the maximum memory value parameter to use at 0. The following image shows the default, and an adjustment to the memory value to be used should be made only when the mapping execution performance is not satisfying.
10. Lookup Ports and Expression Transformation
Only ports that are needed to proceed with the mapping should be marked as the output. Ports that are marked with output when they aren´t, they occupy cache, thus slowing the mapping in their execution.
11. Pushdown Optimization
This option should be used whenever possible. Its use implies best practice mapping design, namely, using PowerCenter objects instead of Source Qualifier SQL Override (should be used in specific and very well justified cases), as well as using procedures.
When there are already a considerable number of mappings to make use of this option, DW support should request from DBAS to monitor the use of DB resources, in case it is necessary to redefine DB priorities and map execution orders concurrently.
To enable this option, it is mandatory to perform the following steps in the session:
Fig.7 – Required options to enable pushdown optimization.
Sequences are optional depending on whether mappings make use of it.
Fig. 8 – Check the execution plan
Fig. 9 – A well-implemented mapping is when the pushdown execution plan is full.
The execution plan (figure above), if a full pushdown is not possible, indicates the reasons for this breakdown, thus helping the developer with what to change in their mapping or session settings.
To conclude this article, I hope that as you look at each of these 11 points, you have realised that in addition to performance, these points aim to improve developments scalability and adaptability, as well as the organization, better analysis and understanding of all developments.