DENSE RANK IN INFORMATICA

Using DENSE RANK

We have RANK transformation to calculate RANK based on Some Measure .If we need to calculate Dense rank then we have two approches

1. Using Source Qualifier Override
2. Using Sorter and Expression ( using variable port )

Let us see how to do using Source Qualifier Override

Process :

Step 1: Create Source Table & Data

1. We can use Scott Schema EMP table

2. Import table using source analyzer

Step 2: Create Target Table

1. Create target table in target database

   

 CREATE TABLE TGT_DENSE_RANK

  (    EMPNO NUMBER(4,0),

    ENAME VARCHAR2(10 BYTE),

    SAL NUMBER(7,2),

    RANK NUMBER,

    DENSERANK NUMBER

  )

2. Import table using Target Designer

Step 3: Create mapping with transformations

1. Create a mapping with the name m_dense_rank
2. Drag and drop source and target into work area
3. Double click on source qualifier → Go to PORTS tab → Delete and rename Columns as shown below
4. 
5. Connect Rank and DenseRank ports as shown .Do not worry we are not reall going to pass comm and deptno to rank and denserank
6. 
7. Click on Properties tab → SQL QUERY
8. Provide below sql query

SELECT EMPNO,  ENAME,  SAL,  RANK () OVER (ORDER BY SAL DESC) RANK,  DENSE_RANK () OVER (ORDER BY SAL DESC) DENSERANK FROM EMP

9. Click on Validate → click on OK → Click on OK
10. connect to target

11. Save the Mapping

Step 4: Create Workflow and Session

1. Create workflow and session as shown below

2. Double click on session task provide source and target connection accordingly

3. Save the workflow

Step 5: Run workflow and Observe output

1. Select Start task → click on run
2. observe target data

3. Observe target table output