Scribd
Upload
54 views12 pages

Informatica Transformations Overview

1. The document describes various Informatica transformations. It provides details about the properties and usage of source qualifier, filter, expression, joiner, sorter, aggregator, router, rank, sequence generator, union, lookup, update strategy, and normalizer transformations. 2. Transformations like source qualifier, filter, joiner, aggregator, rank and router are active while expression, sequence generator, and lookup can be active or passive. 3. The properties discussed include filter conditions, sort keys, group by columns, lookup policies, update strategies, and normalizing row to column format.

Uploaded by

Firoz Nurubhashu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
54 views12 pages

Informatica Transformations Overview

1. The document describes various Informatica transformations. It provides details about the properties and usage of source qualifier, filter, expression, joiner, sorter, aggregator, router, rank, sequence generator, union, lookup, update strategy, and normalizer transformations. 2. Transformations like source qualifier, filter, joiner, aggregator, rank and router are active while expression, sequence generator, and lookup can be active or passive. 3. The properties discussed include filter conditions, sort keys, group by columns, lookup policies, update strategies, and normalizing row to column format.

Uploaded by

Firoz Nurubhashu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Active Transformation:

=====================

A transformation does change the number of records passed through it

Passive Transformation:
======================

A transformation does not change the number of records passed through it

1. Source Qualifier:
======================
-An Active, connected

It converts Source Datatype into Informatica native data type

Properties:
-----------

select all_columns from table_name;

1. SQL query: Sql override, --> by writing sql query, we can filter/restrict the
data from source itself --- 2

2. User defined join: Homogeneous join - tables from same type of data base

3. Source filter: filter condition (salary is not null)

4. select distinct: enable/disable -- full row duplicate

5. Pre-SQL: SQL, before data fetch (e.g index creation in source) ---
1

6. Post-SQL: SQL, After data fetch (e.g index drop in source) ---
3

7. Number of sorted port: 2 (1,2)

Date Format: dd-MON-yy

If source is FF, all of the above properties are disabled

--Core schema create new target table:

create table t_employees_dept as select * from t_employees where 1=2;

alter table t_employees_dept add department_name varchar2(50);

alter table t_employees_dept add location_id number(8);

select * from t_employees_dept;


-----------------------------------------------------------------------------------
-----------------------------------------

2. Filter Transformation:
=========================
--An Active, Connected

Filter transformation is used to filter out record any where in the pipeline

SQ Filter: Filter transformation is used to filter out record from source

Properties:
===========

filter condition: True -- It will pass all the record


False -- It will block all the reord
condition - passes ---> satisfied record

salary:
=======

10000
50000
null not isnull(salary)
80000
6000
12000

I have a ff and it has record for all the countries, i want to load india data to
target

s+sq+filter (country='India' AND salary>50000)

India
india
INDIA

s+sq+filter (lower(country)='india' AND salary>50000)


-----------------------------------------------------------------------------------
--------------------------------

3. Expression Transformation:
=============================

-- Passive, connected

input output variable expression(not a


port)

salary * *

new_salary * salary+
(salary*0.1)
variable: intermediate calculation -- you can't take variable port as output

v_c=a+b;
o_c=v_c
e=d+v_c;

Order of execution:

1. Input
2. Variable
3. Output

Can't filter record in expression transformation -- passive

'+1-('||substr(replacechr(0,PHONE_NUMBER,'.',null),1,3)||')-'||
substr(replacechr(0,PHONE_NUMBER,'.',null),4)

'

first_name uppercase

last_name 100_King (employee_id_last_name)

email -- salary > 10000 email@[Link]


<= 10000 email@[Link]

phone_number 515.123.4567 -- +1-5151234567

job_id from 4th char PRES

comm -- null --> 0

IIF(condition,true,false)

IIF(SALARY>10000,EMAIL||'@[Link]',EMAIL||'@[Link]')

A b
a b

'+1-'||REPLACECHR(0,PHONE_NUMBER,'.','')

'T_FF_EMPLOYEES_'||to_char(SYSTIMESTAMP(),'YYYYMMDDHH24MI')||'.csv'

-----------------------------------------------------------------------------------
--------------------
4. Joiner:
==========
An active, connected

-- heterogeneous join, sources from various data bases

SQ Joiner: Homogeneous sources (sources from same type of data bases)

4 Types of join:

1. Normal join
2. Master Outer
3. Detail Outer
4. Full Outer Join

N number of heterogeneous sources ---> N-1 joiner

Performance in Joiner:

Master -- Smaller table --> less cache, less iteration


partitioning
sorted input
-----------------------------------------------------------------------------------
---------------------
5. Sorter Transformation:
=========================
--An Active, Connected

SQL: Order by

select * from employees order by salary;


select * from employees order by salary desc;
select * from employees order by salary desc, hire_date;

Informatica: Sorter

input output Key


direction

salary * * *
descending
hire_date * * *
ascending

can I select more than one key port in sorter? ---- Yes

properties:

distinct

to remove duplicate record from sorter:

source ff(duplicate) + SQ can't use sq distinct + sorter (use distinct)

case sensitive: Yes

Arun
Babu
Chandra
babu

Null treated as low: enable

null
8000
null
6000
9000

normal order:

null
null
null
9000
8000
6000

Null treated as low: enable

9000
8000
6000
null
null
null '
-----------------------------------------------------------------------------------
--------------------------------
6. Aggregator:
==============
-- Active, connected

min()
max()
sum()
avg()
count()

Irrespective of number of record --> you will get only one record in output
--> last record will get aggregate value

Group by:

sum(salary) group by department_id

Properties:

sorted input: enable

Aggregator ==> Sorting + aggregation


performance:
sorted input
incremental aggregation

-----------------------------------------------------------------------------------
-------------------------------------------------

7: Router Transformation:
=========================
-An Active, connected

-- Single pipeline input --> Multiple pipeline output


-- Mutiple filter condition ---> Multiple group
-- Default
-- exactly opposite to Union transformation

IIF(condition,IIF(condition,true,false),'NON-LEAP')

8. Rank Transformation: NiC IT Academy


=======================
-- An Active, connected

-- Rank on only one column

input output rank group by

salary * * *

department_id *

Properties:
===========

Top/Bottom: Top
Number of Rank: 5

Can't do dense rank by using Rank transformation,


instead we can do by using expression transformation

-------------------------------------------------------------
9. Sequence Generator:
=======================
-- Passive, Connected
-- Auto increment numbers

Properties:
-----------
1. Start value: 10000000
2. increment by: 1
3. End value: 92142342353544454
4. current value: 10000000
5. cycle: enable/disable
6. Reset: enable/disable
7. Number cached values: 1000

10000000
10000100

10000000
10000200

1 4
2 5
3 1
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3

To generate surrogate key, Unique key --> Sequence generator


-------------------------------------------------------------------------------
10: Union Transformation:
=========================
-An Active, connected
same structured table
Heterogeneous sources

Infa ---> Union acts as Union ALL --> it will not remove duplicate
Union ==> Union + Sorter(distinct)

Exactly opposite to router

multiple pipeline input ---> Single pipeline output

-------------------------------------------------------------------------------

11. Lookup Transformation:


==========================
--An active, connected, unconnected

Connected lookup:
=================

It will be in the pipeline

input output lookup return

lookup sql override: by writing sql query we can restrict the data from
lookup table itself

lookup filter:

can take multiple return port:

Lookup policy on multiple match:

Use Any value(default)


Use first value
Use last value
report error - session failed
lookup import ----------- return All values on
multiple match

UnConnected lookup:
===================

only one return port


can take multiple port by using concatenation operation, expression
transformation to call

-- Expression Transformation

:[Link](lookup_field)

why unconnected:
lookup reusablity
Conditional lookup:

IIF(isnull(name),:[Link](ssn),name)

-----------------------------------------------------------------------------------
-

ADD_TO_DATE (TO_DATE('01-JAN-1970','dd-mon-yyyy'),'DD',(1526357743/60/60/24))

-------------------------------------------------------------------------------
12. Update Strategy:
===================

- An Active, Connected

dd - Data Driven

dd_insert 0
dd_update 1
dd_delete 2 (instead of delete, we will soft delete -- Inactive)
dd_reject 3

Target ---> Primary key

e.g SCD Type 1 Implementation:

13. Normalizer:
===============
-- An Active, connected Transformation

row --> column

Transpose the data (SQL: pivot Unpivot)


Cobol source ==> VSAM file ==> Instead of SQ, Normalizer will be used.

GK - generated Key value


GC ID - Generated column value

14: Transaction Control:


==========================
active, connected

commit
rollback

TC_COMMIT_BEFORE
TC_COMMIT_AFTER
TC_ROLLBACK_BEFORE
TC_ROLLBACK_AFTER
TC_CONTINUE_TRANSACTION

Dynamic file in target

1
2
3
4
5
1
2
3
4
5
1
2
3
4
5

india 1
india 0
india 0
uk 1
uk 0
uk 0
usa 1
usa 0

-----------------------------------------------------------------------------------
----

SCD: Slowly Changing Dimensions


dimension table:

customer
product
account
user
employee
region
location
time

Type: 1 - No history will be maintained


Simple update

Type: 2 - History will be maintained


Row level history

Method - 1 - Flag
Method - 2 - Version
Method - 3 - Date

Type: 3 - Recent history will be maintained


Column level history

Surrogate Key:

-----------------------------------------------------------------------------------
-------

Common questions

Powered by AI

The Filter Transformation is preferable when only specific rows need to be passed through the pipeline since it can actively remove rows based on defined conditions, which reduces the workload of subsequent processing steps. Conversely, the Expression Transformation cannot filter rows and is limited to modifying field values, not selecting records based on condition logic .

Sorter transformation sorts data based on specified key fields, handling case sensitivity and nulls as low if enabled, whereas Rank transformation allows sorting and ranking of records based on one column, supporting Top/Bottom N ranks. For large datasets, Sorter can impact performance due to its resource-intensive nature, which is not optimized for dense ranking. Ranking, by itself, only ranks without extensive sorting, thus can be more efficient under certain scenarios .

Active transformations, such as Source Qualifier and Joiner, can change the number of data records that pass through them, either by filtering or joining records, whereas passive transformations like Expression do not alter the number of records; they only modify individual data fields .

The Aggregator Transformation manages duplicates by using aggregate functions like SUM and GROUP BY, effectively consolidating duplicate records into single rows based on aggregation logic. The 'sorted input' property optimizes performance by sorting data beforehand, reducing intermediate storage requirements and CPU processing overhead during aggregation .

The Update Strategy Transformation classifies row operations as inserts, updates, deletes, or rejects by assigning specific codes (e.g., dd_insert, dd_update, dd_delete, and dd_reject). Instead of physical deletions, it allows soft deletes by marking rows as inactive, and uses these operations in conjunction with target table primary keys, suitable for Slowly Changing Dimension tables .

Router Transformation can emulate SQL 'CASE' statements by configuring multiple output groups with conditions that resemble 'WHEN' clauses. For instance, different output groups can be defined for conditions such as "salary > 10000" for high-income and another for "salary <=10000" for low-income categories, similar to routing actions using CASE's conditional logic to different outputs .

The Union Transformation consolidates multiple distinct data inputs into a single output pipeline, effectively merging data flows, whereas the Router Transformation performs the opposite by taking a single input pipeline and directing records into multiple output groups based on complex filter conditions, making them opposites in terms of their operational functions .

Conditional Lookups in unconnected Lookup Transformations allow for dynamic lookups using conditional logic (e.g., IIF statements), thus increasing flexibility by providing customized lookup behaviors. However, they are limited by accepting only one return port per call and requiring external transformations like expressions for invoking them, which may cause performance overhead and complexity in design .

The Sequence Generator transformation generates unique surrogate keys by automatically incrementing numbers starting from a defined value and incrementing by a specified step. It can handle large sequences with configurable start, end, and current values, and options to enable or disable cycling and resetting, thus ensuring uniqueness across sessions .

The Sorter Transformation in Informatica handles nulls as low values if enabled, differing from SQL where nulls by default can appear last depending on the DBMS. Additionally, the Sorter can be case sensitive, impacting both sorting order and duplicate removal, while SQL ORDER BY generally treats nulls according to DB-specific rules and doesn't automatically remove duplicates .

You might also like