FilterTransformation
The Filter transformation allows you to filter rows in a mapping. You pass all the rows from a source transformation through the Filter transformation, and then enter a filter condition for the transformation. All ports in a Filter transformation are input/output, and only rows that meet the condition pass through the Filter transformation.
JoinerTransformation
You can use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. Or, you can join data from the same
source.
The Joiner transformation joins two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources. If you need to join more than two sources, you can add more Joiner transformations to the mapping.
Lookup Transformation
Use a Lookup transformation in a mapping to look up data in a flat file or a relational table, view, or synonym. You can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. You can use multiple Lookup transformations in a mapping.The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations
Lookup Caches
You can configure a Lookup transformation to cache the lookup table. The PowerCenter Server builds a cache in memory when it processes the first row of data in a cached Lookup transformation. It allocates memory for the cache based on the amount you configure in the transformation or session properties. The PowerCenter Server stores condition values in the index cache and output values in the data cache. The PowerCenter Server queries the cache for each row that enters the transformation.
The PowerCenter Server also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the PowerCenter Server stores the overflow values in the cache files. When the session completes, the PowerCenter Server releases cache memory and deletes the cache files unless you configure the Lookup transformation to use a persistent cache.
Normalizer Transformation
Normalization is the process of organizing data. In database terms, this includes creating normalized tables and establishing relationships between those tables according to rules designed to both protect the data and make the database more flexible by eliminating redundancy and inconsistent dependencies. The Normalizer transformation normalizes records from COBOL and relational sources, allowing you to organize the data according to your own needs.You can also use the Normalizer transformation with relational sources to create multiple rows from a single row of data.
Rank Transformation
The Rank transformation allows you to select only the top or bottom rank of data. You can use a Rank transformation to return the largest or smallest numeric value in a port or group. You can also use a Rank transformation to return the strings at the top or the bottom of a session sort order. During the session, the PowerCenter Server caches input data until it can perform the rank calculations.The Rank transformation differs from the transformation functions MAX and MIN, in that it allows you to select a group of top or bottom values, not just one value.
Sorter Transformation
The Sorter transformation allows you to sort data. You can sort data in ascending or descending order according to a specified sort key. You can also configure the Sorter transformation for case-sensitive sorting, and specify whether the output rows should be distinct. The Sorter transformation is an active transformation.
Router Transformation
A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition. However, a Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output group.
Sequence Generator Transformation
The Sequence Generator transformation generates numeric values. You can use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
The Sequence Generator transformation is a connected transformation. It contains two output ports that you can connect to one or more transformations. The PowerCenter Server generates a value each time a row enters a connected transformation, even if that value is not
used. When NEXTVAL is connected to the input port of another transformation, the PowerCenter Server generates a sequence of numbers. When CURRVAL is connected to the
input port of another transformation, the PowerCenter Server generates the NEXTVAL value plus one.
Source Qualifier Transformation
When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier transformation represents the rows that the PowerCenter Server reads when it runs a session.
You can use the Source Qualifier transformation to perform the following tasks:
♦ Join data originating from the same source database.
♦ Filter rows when the PowerCenter Server reads source data.
♦ Specify an outer join rather than the default inner join.
♦ Specify sorted ports.
♦ Select only distinct values from the source.
♦ Create a custom query to issue a special SELECT statement for the PowerCenter Server to read source data.
Stored Procedure Transformation
A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.
A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script.
You might use stored procedures to do the following tasks:
♦ Check the status of a target database before loading data into it.
♦ Determine if enough space exists in a database.
♦ Perform a specialized calculation.
♦ Drop and recreate indexes.
Transaction Control Transformation
PowerCenter allows you to control commit and rollback transactions based on a set of rows that pass through a Transaction Control transformation. A transaction is the set of rows bound by commit or rollback rows. You can define a transaction based on a varying number of input rows. You might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.
In PowerCenter, you define transaction control at two levels:
♦ Within a mapping. Within a mapping, you use the Transaction Control transformation to
define a transaction.
♦ Within a session. When you configure a session, you configure it for user-defined commit.
Union Transformation
The Union transformation is a multiple input group transformation that you can use to merge data from multiple pipelines or pipeline branches into one pipeline branch. Using the Union transformation to merge data from multiple sources is similar to using the UNION ALL SQL statement to combine the results from two or more SQL statements. Similar to the UNION ALL statement, the Union transformation does not remove duplicate rows.
You can connect heterogeneous sources to a Union transformation. The Union transformation merges sources with matching ports and outputs the data from one output group with the same ports as the input groups.
Update Strategy Transformation
When you design your data warehouse, you need to decide what type of information to store in targets. As part of your target table design, you need to determine whether to maintain all the historic data or just the most recent changes.
In PowerCenter, you set your update strategy at two different levels:
♦ Within a session. When you configure a session, you can instruct the PowerCenter Server to either treat all rows in the same way (for example, treat all rows as inserts), or use
instructions coded into the session mapping to flag rows for different database operations.
♦ Within a mapping. Within a mapping, you use the Update Strategy transformation to flag rows for insert, delete, update, or reject.
XML Transformations
When you add an XML source definition to a mapping, you need to connect it to an XML Source Qualifier transformation. The XML Source Qualifier transformation defines the data elements that the PowerCenter Server reads when it executes a session.It determines how the PowerCenter reads the source data.An XML Source Qualifier transformation always has one input or output port for every column in the XML source. When you create an XML Source Qualifier transformation for a source definition, the Designer links each port in the XML source definition to a port in the XML Source Qualifier transformation.
You can use an XML Parser transformation to extract XML inside a pipeline. The XML Parser transformation enables you to extract XML data from messaging systems, such as TIBCO or MQ Series, and from other sources, such as files or databases.
You can use an XML Generator transformation to create XML inside a pipeline. The XML Generator transformation enables you to read data from messaging systems, such as TIBCO and MQ Series, or from other sources, such as files or databases.
