One of the coolest features which was missing in Informatica PowerCenter was the capability to dynamically link ports between transformations. Many other ETL tools has already been providing this features in there tools. With Informatica Cloud Designer, you can build mapping, with dynamic rules to connect ports between transformations.
What is Dynamic Field Linking
In the normal PowerCenter mapping, you need to explicitly map the ports to get connected form one transformation to other transformation in the pipeline. But in the Cloud Designer, you can define the rule to dynamically link ports between transformations in the data pipeline. Based on the rules defined, the ports are connected or dropped out between transformations.
This feature provide much flexibility and code reusability from the developer and administrator perspective. We will see the business use case in the further sections.
Field Rules and Type of Rules
Field rules define how data enters a transformation from the upstream transformation. By default, a transformation inherits all incoming fields from the upstream transformation. All transformations except Source transformations include field rule configuration. When you configure more than one field rule, the Mapping Configuration application evaluates the field rules in the specified order. Use the Actions menu to change the order of rules and delete rules.
All Fields :- All Fields rule, includes or excludes all fields from one transformations to downstream transformation. Using the rename option, you can rename the port from one transformation to the other.
Fields by Data Type :- Includes or excludes ports of selected data types from one transformations to downstream transformation. In the Include/Exclude Fields by Data Type dialog box, you can select the data types that you want to include or exclude. If you want to rename the ports, you can do it by choosing the Rename tab.You click on the Configure button to get the below window and choose the port data type, which is required to be passed on to the downstream transformation.
Fields by Text or Pattern :- Includes or excludes fields by prefix, suffix, or pattern. You can use this option to select fields that you renamed earlier in the data flow. On the Select Fields tab, you can select prefix, suffix, or pattern, and define the rule to use. When you select the prefix option or suffix option, you enter the text to use as the prefix or suffix. When you select pattern, you can enter a regular expression.
You click on the Configure button to get the below window and choose the port name pattern, which is required to be passed on to the downstream transformation.
Named Fields :- Includes or excludes the selected fields. Opens the Include/Exclude Named Fields dialog box. On the Select Fields tab, you can review all incoming fields for selection. On the Rename Selected tab, you can rename selected fields individually or in bulk.
You click on the Configure button to get the below window and choose the port, which is required to be passed on to the downstream transformation.
Pros and Cons
All approaches has its own benefits and drawbacks. Here is what we see as the good and bad of dynamic column mapping.
- Better code reusability, You build the mapping once and you can reuse the code for multiple data sources.
- Better flexibility and scalability for development, by providing parametrization and reusability.
- Reduce the number of objects to be maintained in the PowerCenter Repository.
- Loses Metadata about column mapping, hence the data lineage can not be produced.
- Dynamically including all column might lead to processing unwanted columns in the mapping pipeline.
Business Use Case
One of the typical use case would be to build stage table loading mapping building. Since a typical stage table mapping will not include not unique complex transformations, you can create just one mapping and can parametrize the source table, target table, connection details etc. This makes the development effort simple and highly reusable.
Hope you enjoyed this article. Please let us your feedback and questions in the comment section below.