Rank T/R

RANK TRANSFORMATION

1. WHAT IS A RANK TRANSFORM? 
2. HOW DOES A RANK TRANSFORM DIFFER FROM AGGREGATOR TRANSFORM FUNCTIONS MAX AND MIN? 
3. HOW DOES A RANK CACHE WORKS? 
4. WHAT IS A RANK PORT AND RANKINDEX? 
5. HOW CAN YOU GET RANKS BASED ON DIFFERENT GROUPS? 
6. WHAT HAPPENS IF TWO RANK VALUES MATCH? 
7. WHAT ARE THE RESTRICTIONS OF RANK TRANSFORMATION? 
8. HOW DOES RANK TRANSFORMATION HANDLE STRING VALUES? 
9. WHAT IS DENSE RANK AND DOES INFORMATICA SUPPORTS DENSE RANK? 
10. HOW DO WE ACHIEVE DENSE_RANK IN INFORMATICA? 
11. SOURCE TABLE HAS 5 ROWS. RANK IN RANK TRANSFORMATION IS SET TO 10. HOW MANY ROWS THE RANK TRANSFORMATION
WILL OUTPUT? 
12. HOW YOU WILL LOAD UNIQUE RECORD INTO TARGET FLAT FILE FROM SOURCE FLAT FILES HAS DUPLICATE DATA? 

 

 

76.How does Rank transformation handle string values?

Rank transformation can return the strings at the top or the bottom of a session sort order. When the Integration Service runs in Unicode mode, it sorts character data in the session using the selected sort order associated with the Code Page of IS which may be French, German, etc. When the Integration Service runs in ASCII mode, it ignores this setting and uses a binary sort order to sort character data.

 

77.Can We Do Ranking Using Two Ports? Can We Rank All The Rows Coming From Source, How?

When ETL load the data from source we can declare the rank of the incoming data to pass a rank transformation. We can’t declare two rank on a single source data. We can do rank the row by declaring the rank Transformation and declaring the rank port.

 

78. What is a Rank Transform?

1. This a type of an active T/R which allows you to find out either top performance or bottom performers.
2. Rank T/R is created with the following types of the port:

i. Input Port (I)
ii. Output Port (O)
iii. Rank Port (R)
iv. Variable Port (V)

  • Rank is an Active Connected transformation used to select a set of top or bottom values of data. It basically filters the required number of records from the top or from the bottom.
  • Rank Transformation is Active as well as Connected. It is used to sort and rank a set of records either top or bottom.
  • Rank Transformation is also used to select data with a largest or smallest numeric value based on a specific port.

79. How does a Rank Transform differ from Aggregator Transform functions MAX and MIN?
Like the Aggregator transformation, the Rank transformation also groups information. The Rank Transform allows us to select a group of top or bottom values, not just one value as in case of Aggregator MAX, MIN functions.
80. How does a Rank Cache works?
During a session, the Integration Service compares an input row with rows in the data cache.

If the input row out-ranks a cached row, the Integration Service replaces the cached row with the input row.

If we configure the Rank transformation to rank based on different groups, the Integration Service ranks incrementally for each group it finds. The Integration Service creates an index cache to stores the group information and data cache for the row data.
81. What is a RANK port and RANKINDEX?

  •  Rank Index is assigned by the task designer to each record.
  • The rank index port is used to store ranking position for each row.
  • Rank Transformation identifies each row from the top to bottom and then assigns Rank Index.

Rank port is an input/output port used to specify the column for which we want to rank the source values. By default Informatica creates an output port RANKINDEX for each Rank transformation.It stores the ranking position for each row in a group.
82. How can you get ranks based on different groups?
Rank transformation lets us group information. We can configure one of its input/output ports as a group by port. For each unique value in the group port, the transformation creates a group of rows falling within the rank definition (top or bottom, and a particular number in each rank).

83. What happens if two rank values match?
If two rank values match, they receive the same value in the rank index and the transformation skips the next value.
84. What are the restrictions of Rank Transformation?

  •  We can connect ports from only one transformation to the Rank transformation.
  •  We can select the top or bottom rank.
  •  We need to select the Number of records in each rank.
  •  We can designate only one Rank port in a Rank transformation.

85. How does Rank transformation handle string values?
Rank transformation can return the strings at the top or the bottom of a session sort order.When the Integration Service runs in Unicode mode, it sorts character data in the session using the selected sort order associated with the Code Page of Integration Service which may be French, German, etc. When the Integration Service runs in ASCII mode, it ignores this setting and uses a binary sort order to sort character data.
86. What is Dense Rank and does Informatica supports Dense Rank?
When multiple rows share the same rank the next rank in the sequence is not consecutive. On the other hand DENSE RANK assigns consecutive ranks.
Take the following example: Let’s say we want to see the top 2 highest salary of each department

1819

So the normal RANK will generate the result set where we can miss rank (here RANK = 2 is missing for department 10) for due to sharing of same ranks between multiple records. On the other hand the DENSE RANK will generate all the consecutive ranks.
Informatica RANK transform performs a simple RANK, not DENSE RANK. So using Informatica RANK transform we may miss consecutive ranks.

87.How do we achieve DENSE_RANK in Informatica?
In order to achieve the DENSE RANK functionality in Informatica we will use the combination of Sorter, Expression and Filter transformation. Based on the previous example data set, let’s say we want to get the top 2 highest salary of each department as per DENSE RANK.

  •  Use a SORTER transformation.
  • DEPTNO ASC, SAL DESC
    After the sorter place an EXPRESSION transformation.

 

20

88.Source table has 5 rows. Rank in rank transformation is set to 10. How many rows the rank transformation will output?
5 Rank

89.How you will load unique record into target flat file from source flat files has duplicate data?
In rank transformation using group by port (Group the records) and then set no. of rank 1. Rank transformation returns one value from the group. That value will be a unique one.