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.
#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.
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.
No comments:
Post a Comment