Sequence Generator

Sequence Generator  The SG transformation generates numeric values. We can use the SG to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.  NEXTVAL:      We can use the NEXTVAL port to generate sequence numbers by connecting it to downstream transformation or target.  CURRVAL:      CURRVAL is the NEXTVAL plus Increment By value. We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation. When a row enters a transformation connected...

Lookup Transformation

Lookup Transformation  We can use lookup transformation to lookup data in a flat file, relational table , view or synonym.  Tasks:  à Get a related value: We can retrieve a value from lookup table based on value in the source.  à Perform a calculation: We can retrieve a value from lookup table and use it in calculation.  à Update slowly changing dimension table: Using lookup we can check whether rows exist in a target or not.      Connected lookup      à Receives input values directly from the pipeline.      à Use a dynamic...

Router Transformation

Router Transformation Router transformation tests same input data based on multiple conditions and gives the option to route rows of data that do not meet any of the conditions to a default output group. Working with Groups Router has the following types of groups. Input Group : The designer copies property information from the input ports of the input group to create a set of output ports for each output group.  Output Group : There are two types of output groups.    è User-defined Groups    è Default Group We cannot delete or modify output ports or properties. User...

Rank Transformation

Rank Transformation The Rank transformation allows us to select the top or bottom rank of the data.  When the IS runs in the ASCII data movement mode, it sorts session data using a binary sort order. For Unicode data movement mode, it uses the sort order configured for the session. Rank Caches The IS stores group information in an index cache and row data in a data cache. During a workflow, the IS compares an input row with rows in the data cache. If the input row out-ranks a cached row, the IS replaces a cached row with input row. For multiple partitions, the IS creates separate caches for each partition. Rank...

Joiner Transformation

Joiner Transformation Joiner transformation joins two related heterogeneous sources residing in different locations or file systems.  We use the joiner to join two sources with at least one matching port.   Joiner typically combine information from two different sources that do not have matching keys such as flat file sources.  Joiner allows to use join sources that contain binary data.  There are some limitations on the pipelines we connect to the joiner. We cannot use a joiner in the following situations. Both input pipelines originate from the same Source Qualifier transformation. Both input...

Mapping Parameters and Mapping Variables

Mapping Parameters and Mapping Variables Mapping parameters and variables represent values in mappings and mapplets.  Mapping Parameter  A mapping parameter represents a value that we can define before running a session. A mapping parameter retains the same value throughout the entire session.   For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract...

Normalizer Transformation

Normalizer Transformation The Normalizer transformation receives a row that contains multiple-occurring columns and returns a row for each instance of the multiple-occurring data. The transformation processes multiple-occurring columns or multiple-occurring groups of columns in each source row. The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources.  For example, you might have a relational table that stores four quarters of sales by store. You need to create a row for each sales occurrence. You can configure a Normalizer transformation to return...

Transaction Control Transformation

Transaction Control Transformation We can control commit and roll back transactions based on a set of rows that pass through using Transaction Control transformation. A transaction is the set of rows bound by commit or roll back rows.  Use the Transaction Control transformation to define conditions to commit and roll back transactions from transactional targets.  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. You define transactions using an expression in a Transaction Control...

TRANSFORMATION 3

Target Load Order  We specify a target load order based on the Source Qualifier transformations in a mapping. If you have multiple Source Qualifier transformations connected to multiple targets, you can designate the order in which the Integration Service loads data into the targets. constraint-based loading If one Source Qualifier transformation provides data for multiple targets, you can enable constraint-based loading in a session to have the Integration Service load data based on target table primary and foreign key relationships.   Stored Procedure Transformation A stored procedure...