Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(4 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Concatenation and active transformations in PowerCenter mappings
Content

The goal of this document is to explain how passive and active transformations interact with path concatenation. To understand this topic, we will first discuss how PowerCenter handles data and performs passive-passive path concatenation.
Having developed an understanding of the mechanics of this process, we will then examine how active transformations interfere with concatenation. The transformations that we examine will be filters sorters, and aggregators. Even though active transformations prevent path concatenation, it is still possible to bring the two paths together.
The Joiner Transformation can be used to connect two paths when one is active and the other is passive.

PowerCenter Mapping Rules

To understand how active transformations affect mappings it is first important to understand how Informatica PowerCenter manages passive concatenation of data.
Before we begin with any examples, we'll state several rules that need to be followed to ensure that passive-passive concatenation can take place.

Basic Rules of Concatenation

Records within a mapping can be split and reconnected as long as several rules are adhered to.

  1. If the two parts of a record are to be concatenated they must have the same internal ID number.
  2. The relative order of the records must be maintained in both paths so that records are in the same position as when they left the transformation.
  3. Transformations on either path cannot drop a row from the flow.

Passive-Passive Concatenation

Within a mapping it is possible to separate components of are cord in a pipeline. When the records are split, they carry the same internal ID and at the time of the split, they are in the same relative order in both pipelines.

In figure 1. we can see that the data flow has been split. We note that records in both pipelines have maintained the same ID (rule 1.),the same relative order (rule 2.) and all records are present (rule 3..).
Since the two paths have met the requirements, Passive-Passive Concatenation can take place. The parts of the records can be brought back together and concatenated.

Figure 1. Passive and Passive Concatenation

 


Scenarios

The next set of description and figures describes scenarios where the Designer will not allow a mapping to be constructed because they violate one of the three rules listed.

Drop a Record on 1 Path - Filter

The first scenario shows a mapping in which one of the pipeline paths drops a record. In this case the mapping was built with a filter transformation on one of the pipelines. Since the requirement for concatenation is that both parts of the record are present for concatenation to take place, there is an issue with the mapping. 

In figure 2, we can see that in one of the pipelines, record 103 has disappeared. The filter has removed the row which means that the filter is an active transformation.
In this case, the Designer will not allow this mapping to be built.

Figure 2. Passive-Active Concatenation - Filter

 

 

 

 

The Designer is unable to determine definitively if the Filter Transformation will ever drop any records. The Designer only knows that the Filter Transformation may drop rows if certain data is passed to it, and so the Designer considers this possibility and prevents the concatenation of the paths.

Re-order the Rows - Sort

If a Sorter transformation is employed, the potential exists that the order of the records in the pipeline will change. Since ID numbers are assigned as records leave the transformation, the records that leave the sorter will have new ID numbers.

In figure 3 we can see that the records in the pipelines have different internal IDs from their original values. In this case, the two pipelines cannot be connected.
Once again, the Designer will not allow the mapping to be built.

Figure 3. Passive-Active Concatenation - Sorter Transformation

 

Entirely new records - Aggregator

Some transformations within PowerCenter have the ability to create new records that are wholly distinct from the input records.
The Aggregator Transformation is one such example. All records that enter the transformation are consumed and processed into new records.
Since these records are new and distinct, they cannot be concatenated to the other pipeline.

In figure 4. we can see that the aggregator has produced new records that have no relation to the other pipeline.

Figure 4. Passive-Active Concatenation - Aggregator Transformation

 

The Solution to Passive to Active Concatenation - Joiner Transformation

The Joiner Transformation can be used to reconnect the paths even though an active transformation was used on one of the pipelines. The Joiner Transformation allows the two paths to be pulled together where one or more of the paths are active.
The Joiner Transformation works in a way similar to a database join. It first reads one set of data (from one of the pipelines) and then processes records in a stream from the other pipeline. The transformation is configured with a join condition that tells PowerCenter the fields to compare between the paths (the join criteria).
As records stream to the transformation, new records can be produced by the transformation if the condition is met.

Figure 5. Passive-Active Concatenation Solution - Joiner Transformation

 



More Information


Reference
PowerCenter Designer Guide > 'Mappings" > "Connecting Mapping Objects" > "Rules and Guidelines for Connecting Mapping Objects"


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Last Modified Date: 1/28/2016 12:20 PM ID: 16229
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)