Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Sunday, 8 September 2013

Rank Transformation in Informatica


By on 02:10:00


Active Transformation.

Used to seek the position (at which position the data is).

We can see top or bottom positions.

By default, rank index port name is created along with the transformation object (To carry the rankings – rank index is prepared).


Ports:

Input
Output
Expression
Variable
Rank
Group-By

Steps:
1. Define the Source Definition (EMP).

2. Define the Target Definition.

How to Define the Target Table?



1. Go to Ware House Designer create a target table same as the source (EMP) by dragging and dropping the source definition.

2. Edit EMP source definition. Go to the Table tab of Edit and rename it to DIM_RANK.


Now go to the Columns tab and add a new column RANKINDEX and click Apply, then OK.



Till now the new table Dim_RANK is logically present in the WareHouse. 


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DIM_EMP                        TABLE
DIM_EMP1                      TABLE

To make it available physically go to the Targets Menu and click on Generate/Execute SQL.




Click on Connect button.
Give the DSN, Username and Password of the Target.

Click the radio button Generate from Selected Tables , check the box Create Table.
Click Generate SQL file and the Close.



SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DIM_EMP                        TABLE
DIM_EMP1                       TABLE
DIM_RANK                     TABLE

Note that DIM_RANK now physically belongs to the Schema DWH_MEEN.

Define the mapping by dragging the source and target. Create a RANK Transformation T_RANK.



By default a RANKINDEX column is there in the blank transformation object.



Project the required ports from the Source Qualifier to the transformation object.
Edit Rank Transformation.


Go to the Ports Tab and apply RANK on Salary Check the R box. Apply Group By on the department no. 


Click on Apply and then OK.

Now in the Properties Tab 

Set Top/Botton to Top and Number of Ranks to 2. 

(It will fetch us the top 2 salaries)

RANK also uses CACHE




Now we have selected department wise top two salaries, but our objective is to fetch the second highest salary so we will apply a fliter with the condition RANKINDEX = 2.

Create a Filter Transformation. Project the required ports from RANK to FILTER.
Edit Filter – In the Properties Tab apply the condition RANKINDEX = 2.
Apply -> OK

Project the required ports from the Filter to the Target.
SQ -> T_RANK -> T_FIL -> DIM_RANK.
(To increase the performance, use SORTER Transformation before T_RANK)


Create a Session
Create a Workflow
Repository _> Save
Start Workflow
 
We encounter an error.




Edit the Session.

In the Mappings Tab, select the target DIM_RANK. In the Properties, Set Target Load Type as Normal.
Repository -> Save

Start the Workflow Again.

Now the load is successful.

1 comments:

  1. how to remove the duplicate record by using rank transformation

    ReplyDelete

Blog Archive

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (46) Informatica Online Training (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (1) OBIEE 10G (8) OBIEE 11G (137) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)