Sorter Transformation In Informatica

Sorter Transformation In Informatica

Type: Active & Connected

#Allows us to sort data either Ascending or Descending according to selected Sort key/s. We can sort data using multiple keys, in this case Integration Service will sort data either ASC or DESC according to Ports order given in Port tab of transformation. We can sort data either from relational source or flat files.

#Sort key is nothing but one or more ports that you want to use for Sort operation. Sorter transformation contains only two ports, input and output.

#We can also sort data using all ports that are available in source data. If you select Distinct option present in properties tab of transformation then Integration Service select (checks) all ports available & rejects duplicate rows.

 

Sorter Transformation Properties:

 

 

1. Sorter cache size:
     Power Center Server uses sorter cache to sort the data. Before sorting it brings all data into Cache memory and then sort it. We can configure cache size according our need. If sort operation requires more size then it stores additional data to disk drive in temporary files.
     If it cannot allocate enough memory, the Power Center Server fails the Session. For best performance, configure Sorter cache size with a value less than or equal to the amount of available physical RAM on the Power Center Server machine. Informatica recommends allocating at least 8 MB of physical memory to sort data using the Sorter transformation.

2. Case Sensitive:
     This option allows us to perform Case sensitive sort. PC Server sorts upper case characters higher than lower case characters.

3. Distinct:
     Allows us remove duplicate rows. If checked this option, server uses all ports as sort key.

4. Work Directory:
    Directory used by power Center Server to store data in temporary files while sorting operation. We can configure this directory path but there should be enough disk space available to store these temp files.

5. Treat NULL low:
    Enabling this, integration service will treat NULL as lower values otherwise it considers NULL as highest values.

#For better performance, configure sort cache size larger than input data size. Also, passing sorted data to joiner transformation on which join conditions are applied will improve performance.

#Also, passing sorted data to aggregator transformation will improve session performance as Integration service will read and group the result as it reads incoming data.

Thanks for reading.
-Nitesh.

 

Rank Transformation In Informatica

Rank Transformation In Informatica

 

Type: Active & Connected.

#Allows us to select Top or Bottom values from a set of data or select Top or Bottom values in different groups of data.
For ex: if we need to get top or bottom 5 values according to salary range OR if we need to get top or bottom 2 values in each department according to salary range, we can use Rank transformation.


#Ports in Rank transformation:
Input: At least 1 required

Output: At least 1 required
Variable: can be used to store temporary values
Rank: Only 1 port can act as rank port. This port can either be Input, Output or Variable port. Rank is calculated according to this port.

#Rank Index:
    PC Designer automatically creates a RANKINDEX port which holds ranking values in a group.
For ex: if we need to get top 5 salaried employees then RANKINDEX will hold values from 1 to 5.
    This port can only be used as Output port, we can not edit or delete it. We can also pass this port to other transformation or target.

#Defining Groups:
     Rank transformation allows us to rank or select top or bottom values from each group we define.
For ex: if we need to get top 3 salary records in each department then we can select department column as Group By column.
     By defining groups, we get one set of ranked values for each group. We can not define group by port which is already a rank port.

 

#Properties Of Rank Transformation:

• Cache directory: where cached values are stored (within temp files).
• Top/Bottom
• Number Of Ranks
• Case sensitive string comparison
• Rank Index Cache Size: It holds values for different groups that are selected in group by clause. For ex: if department is selected as group by columns then cache will have values like 10,20,30 etc. for example. Also all the group by columns will present in Index cache.
• Rank Data Cache Size: It holds raw data PC server completes ranking and is generally larger than Index cache.  To reduce data cache size, connects only required ports to transformation. Ports going out from rank transformation, variable port if any and Rank port are stored in data cache.

 

 

Thanks for reading...

Nitesh