Informatica Reference

Aggregator
Sorter
Aggregator transformation performs aggregate calculations, such as averages and sums.
Sorter Transformation is used to sort the data
Aggregator uses aggregate cache  to perform aggregate calculations
Sorter uses Sorter cache to perform the sort operation
Aggregation done by  using aggregate expression with conditional clauses,
Sorting is done based on the sort key and the order of sort key
Aggregator uses Incremental aggregation to perform aggregation calculations incrementally.
There is nothing called incremental aggregation in sorter
Advertisements
Sorter
Rank
Sorter is used to Sort the data either ASC or DSC
Rank is used to arrange data from top or bottom Group by can be done using Rank
Sorter can be used to remove duplicates(Use Distinct ouptut)
We can remove duplicates using Rank
In sorter we cannot assign values to the ports
In rank we can assign variables and write non-aggregate expressions also.
In sorter the Integration Service uses Sorter Cache to perform the sort operation.
In Rank the Integration Service stores group information in an index cache and row data in a data cache.

ERROR:

The ERROR function causes the Informatica Server to skip a record and throws an error message defined by the user. It is available in the Designer.

ERROR( string )

Example : The following example shows how you can reference a mapping that calculates the average salary for employees in all departments of your company, but skips negative values. The following expression nests the ERROR function in an IIF expression so that if the Informatica Server finds a negative salary in the Salary port, it skips the row and displays an error:

IIF( SALARY < 0, ERROR (‘Error. Negative salary found. Row skipped.’, EMP_SALARY )

SALARY RETURN VALUEclip_image004[11]

10000 10000

-15000 ‘Error. Negative salary found. Row skipped.’

The below example combines two special functions, a test Function and a conversion function.

IIF(IS_DATE(DATE_PROMISED,’MM/DD/YY’),TO_DATE(DATE_PROMISED),

ERROR(‘Invalid Date’))

          Drop             Delete            Truncate
1. Whole structure and data will drop.

2. Can’t  Rollback

3. Can’t use where clause

4. All data and structure drop simultaneously.

5. Drop is a DDL Statement

1. Structure will not drop, data will drop.

2. Can Rollback

3. Can use WHERE clause

4. Row by Row data Delete

 

5. Delete is a DML Statement

1. Structure will not drop data will drop.

2. Can’t Rollback

3. Can’t use WHERE clause

4. Only all data will drop, truncate operation drop and recreate the table.

5. Truncate is a DDL Statement

                       Row id                            RowNum
1. Physical address of the rows.

2. Rowid is permanent

3. Rowid is 16-bit hexadecimal

4. Rowid gives address of rows or records

5. Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row.

6. ROWID is the fastest means of accessing data.

7. They are unique identifiers for the any row in a table.

1. Rownum is the sequential number, allocated to each returned row during query execution.

2. Rownum is temporary.

3. Rownum is numeric

4. Rownum gives count of records

5. Rownum is a dynamic value automatically retrieved along with select statement output.

6. It represents the sequential order in which Oracle has retrieved the row.

CURRVAL = NEXTVAL + Increment by Value property

If you connect CURRVAL port without connecting the NEXTVAL, then the CURRVAL will give you constant value.
If you connect CURRVAL, Informatica process one row at a time – can be a performance issue.

NEXTVAL – generates sequence numbers when you connect this port to a transformation.

If you want to load 2 targets with the same sequence number
– Connect the NEXTVAL port to a transformation and then connect that transformation to the 2 targets.

If you want to load 2 targets with the unique sequence number
– Connect the NEXTVAL port to two different targets or two different pipelines which has those 2 targets.