Informatica Transformations

Sno	Transformation	I	O	V	R	M	L	Important	PAGE
1	Source Qualifier  SQ A/C	*	*					Distinct	8
2	Filter                  FIL A/C	*	*					Expression	4
3	Expression          EXP P/C	*	*	*				Group by port, Sorted input, Expression
4	Sequence Generator SEQ P/C	*	*					NEXTVAL, CURVAL	10
5	Router                 RTR A/C	*	*					Expression
6	Union                  UN   A/C	*	*
7	Transactional Control  TC A/C	*	*
8	Sorter                 TC   A/C	*	*					Distinct	7
9	Normalizer          NRM A/C	*	*					Sequence, restart
10	Stored Procedure SP    A/C	*	*		*			Bad file created
11	Joiner                 JNR  A/C	*	*			*		Sorted Input
12	Aggregator         AGG A/C	*	*	*				Group by port, Sorted input
13	Rank                  RNK  A/C	*	*	*	*			Group by port, Expression
14	Look-up             LKP  A/C-UC	*	*	*	*
15	XML Source Qualifier XML A/C
16	Custom              CT  A-P/C	*	*
17	External Procedure EP P/C-UC	*	*		*
18	Update Strategy  UPD   A/C	*	*					DD_INSERT, DD_UPDATE, DD_DELETE, DD_REJECT, UP as UP, UP as IN, UP else in, Transc Expression – Reject file created
19	Midstream XML Parser
20	Midstream XML Generator
21	Application Source Qualifier
22	MQ Series Qualifier
23	Application Multi group Qualifier

1. Aggregator Transformation (A/C - AGG)

Definition:
1. The Aggregator transformation is Active and Connected
2. The Aggregator transformation allows to perform aggregate calculation
3. Aggregation transformations are also used to perform calculation on groups
4. The row which meet the condition are passed to target
5. The doesn't meet the condition, rejected row store rejected file or bad file directory
Aggregator Functions:

AVG	MAX	STDDEV
COUNT	MEDIAN	SUM
FIRST	MIN	VARIANCE
LAST	PERCENTILE

Ports:

INPORTS	(I)	each input receive data
OUTPORTS	(O)	pass the data to other transformation
VARIABLE PORTS	(V)	it stores the intermediate result, it can reference input ports. Not to out ports
GROUP BY PORT

Properties:

Cache Directory	$PMCaheDir
Tracing Level	Normal (Terse/Normal/Verbose initialization/verbose data)
Sorted Input
Aggregator Data Cache	2000000 Bytes
Aggregator Index Cache	1000000 Bytes
Transformation Scope	All input (Transaction/All Input)

Component:

Aggregator Expression	Non aggregator Expression/Conditional Class
Aggregator Cache
Group by Port	Which column you want to group by Eg. Dept
Sorted Input	Reduce the amount of data cached

Aggregator Cache:
The PCS stores data in the aggregate cache until it complete the aggregator calculation
Index Cache: It stores the group value, as configured in the group by port
Data Cache: Stores calculation (Row data stores output value) based on group by ports
Optimization
-- Group by simple columns like numbers instead of string or data
-- Use sorted input
-- Use incremental aggregation
-- Minimize the aggregate function
-- Before filter transformation best (Reduce the data)
-- Look up unconnected & stored procedure - we can call

2. Expression Transformation (P/C - EXP)

Definition:
1. Expression Transformations are Passive and connected transformation
2. This can be used to calculate values in a single row before writing to the target
3. It performs row by row calculation
4. Performs any non aggregate function
Expression Functions:
i) Expression Transfomations are used for data cleansing and scrubbing
ii) There are over eighty functions within PowerCenter, such as salary, concatenate, instring, rpad, ltrim and we use many of them in the expression transformation
iii) We ca also create derived columns and variables in the expression Transformation
Component:
Expression - We can call - unconnected Stored Procedure and unconnected look up
Ports:

INPORTS	(I)	Each input receive data
OUTPORTS	(O)	Which provide the value to either target or next transformation in the mapping is called output ports
VARIABLE PORTS	(V)	It stores the intermediate result, it can reference input ports. (which stores the variable information)
GROUP BY PORT

Properties:
Tracing level - Normal (Terse/Normal/Verbose initialization/verbose data)
Optimization:
-- Factoring out common logic
-- Minimizing aggregator function calls. For example use SUM(A+B) instead of using SUM (A) + SUM (B)
-- Replacing common sub expression with local variables
-- Choosing Numeric Vs String operation
-- Choose DECODE function Vs LOOK UP operation
-- Choose CONCAT operation for example use ||'|| instead CONCAT (Fname, Lastname)
-- We can enter multiple expression in a single expression transformation

3. Filter Transformation (A/C - FIL)

Definition:
1) This type of active and connected Transformation which is used to filter of the source rows based on a condition
2) Only the row which meet the condition are pass through to target
3) Any kind of source we can use filter transformation
4) Filter condition drops data that does not match condition
5) We can put one or more condition (more condition means we can use AND, OR operator)
6) Discards rowes don't appear in the session log or reject files
Ports:

INPORTS	(I)	Receives data from source
OUTPORTS	(O)	Pass the data to other transformation

Properties:

Filter Condition	<put Condition>
Tracing Level	Normal (Normal/Terse/Verbose init/Verbose data)

Optimization:
-- Use the filter transformation early in the mapping (or) nearly in SQ
-- The filter condition is case sensitive and queries in some database do not take this into account
Troubleshooting:

Case sensitivity	The filter condition is case sensitive
Appended	Use the RTRIM function to remove additional space

Functions:
i) We can use one or more condition in filter transformation
ii) AND, OR logical operator through

4. Joiner Transformation (A/C - JNR)

Definition:
1. This is an active and connected transfomation
2. It can be used to join two sources coming from two different locations or same location
3. We can use homogeneous and heterogeneous sources
4. It joins a flat file and a relational sources or to join two flat files or to join relational source and a XML source
Condition:
1) At least two sources must have at least one matching ports or columns
2) At least two sources should have primary key and foreign key relationship
Ports:

INPORTS	(I)	Receive data from Source
OUTPORTS	(O)	Pass the data to other Transformation
MASTERPORTS	(M)	If checked master (small) otherwise (large) (to swtich the Master Details relationship for the source)

Properties:

1	Cache sensitive string comparison	(Character data only enable)
2	Cache Directory	$PMCacheDir
3	Join Condition
4	Join Type	Normal (Normal - M.outer - D.outer - Full outer
5	Null ordering in Master	Null is highest value (Null is lowest value)
6	Null ordering in detail	Null is highest value (Null is lowest value)
7	Tracing Level	Normal (Normal/Terse/Verbose init/Verbose data)
8	Joiner Data cache size	2000000
9	Joiner Index cache size	1000000
10	Sorted Input
11	Transformation Scope	All inout (Transaction/All Input)

Component:

1	Case sensitive string comparison	(Character data only enable)
2	Cache directory
3	Join condition
4	Joiner type	(Normal, Master Outer, Detail Outer, Full outer)

Cache:

1	Joiner Data cache size	Output value only
2	Joiner Index cache size	The index cache holds rows from the master source that are in join condition

Index Cache	Data Cache
Stores index values for the master source table as configured in the join condition.	Stores master source rows

Functions:
Above are the types of sources can be used in a joiner
1) Two relational tables existing in seperate databases
2) Two flat files in potentially different file systems
3) Two different ODBC sources
4) Two instances of the same XML sources
5) A relational table and a flat file source
6) A relational table and a XML source
A joiner cannot contain the above types of source
1) Both pipelines begin with the same original data sources
2) Both input pipeline originate from the same source qualifier transformation
3) Both input pipeline originate from the same normalizer transformation
4) Both input pipeline originate from the same joiner transformation
5) Either input pipeline contains an update strategy transformations
6) Either input pipeline contains a connected or unconnected sequence generator transformation
Performance:
1) Use sorted input (flat file, relational data)
2) Minimizing the disk input and output
3) Used in front of sorted transformation
4) For an unsorted joiner transformation, designate as the master source with fewer rows
5) For an sorted joiner trnasformation, designate as the master source the source with fewer duplicate key values
6) Above Transformation we can't use before the joiner transformation
-- Sequence generator transformation directly
-- Update strategy transformation
Tips:
-- Sorted input - improve the session performance
-- Don't use the above transformation sort origin and joiner transformation
         -- Custom, Unsorted aggregator, Normalizer, Rank
-- Sort order from both table (master & detail)
-- Normal or Master outer join perform than a full outer or detail outer join

Normal	Matched rows from master and detail source
Master	All rows data from the detail source and the matching rows from the master source
Detail	All rows data from the master source and the matching rows from the detail source
Full Outer	All rows of data from both master and detail sources

5. Rank Transformation (A/C - RNK)

Definition:
--> It is an active and connected transformation which is used to identify the top or bottom rank of data based on condition
--> Rank transformation is used to return the largest or smallest numeric value in a port or group
--> We can use a rank transformation to return the strings at the top or the bottom of a session sort order
Functions:

ASCI	Binary sort order
UNICODE	Session sort order in session properties (code Page) Binary sort order Binary value string and returns rows with the highest binary values for string

Ports:

INPUT	(I)	Minimum of one
OUTPUT	(O)	Minimum of one
VARIABLE	(V)	Stores values or calculations to use in an expressions
RANK	(R)	Only one (default port-only output return only)
EXPRESSION
GROUP BY PORT		

Properties:

Cache Directory	$PMCacheDir
Top/Bottom	Top
Number of Ranks
Case sensitivity comparison
Tracing Level	Normal
Rank data cache size	2000000
Rank index cache size	1000000
Transformation scope	All input (All input/transformation)

Cache:

Index Cache	Data Cache
Stores group values as configured in the group by ports	Stores ranking information based on the group by ports. Must run on a 64 bit Power Center

Performance:
-- Configure ASCII mode

6. Sorter Transformation (A/C - SRT)

Definition:
1) It allows to sort data either in ascending or descending according to a specific sort key (field)
2) Also used to configure for case sensitive sorting and apecify whether the output rows should be distinct
Functions:
1) Sort data from relational or flat file source
2) The sorter transformation treats the data passing through each successive sort key port as a secondary sort of the previous port
Component:

Direction (V)

Ascending or Descending

Ports:

INPORTS	(I)	Receive data from source
OUTPORTS	(O)	Pass the data to other transformation
KEY	(V)	Which one you want to sort the A/D

Properties:

Sorter cache size	10000000	#input rows +[(∑column size)+16]
Case sensitive		(enable)Uppercase higher than lower case
Work directory	#PMTempDir	(Temp file store-sorting time
Distinct		enable - eliminate duplicate value in output
Tracing level	normal	(Terse?Normal/Verb init/Verb data)
Null treated low		(enable treat null values higher than any other value
Transformation scope	All input	1) Tranction 2) All Input

Sort Data:
Each successive sort key port as a secondary sort of the previous port
Formaula:

#input rows +[(∑column size)+16]
Performance:
1) Sorter transformation to sort data passing through an Aggregator transformation configured to use sorted transformation
2) You should configure sort to PCS applies to all sort key ports

7. Source Qualifier Transformation (A/C -SQ)

Definition:

1) When adding a relational or flat file source definition to a mapping it must to connect it to a Source Qualifier Transformation

2) The Source Qualifier transformation represents the rows that the power Center server reads when it runs a session
Functions and Perform:

Overriding the default SQL query	Only relational
Filtering the record	Only relational
Join the data from two or more tables etc.,	Same source database

Important Topic:

Target load order	Constraint based load
Parameter and variable	$$$ session start time
Default query
SQL Overwrite	Override the default SQL query (user defined join, source filter, no of sorted port, selected distinct setting

Ports:

INPORTS	(I)	Receives data from source
OUTPORTS	(O)	Pass the data to other transformation

Properties:

SQL Query		(custom query replace the default query)
User Defined join		(user defined join)
Source filter		(filter condition)
No of sorted ports	0	(order by includes no of ports-sort order)
Tracing Level	Normal	(Terse/Normal/Verb init/Verb data)
Select distinct		(enable-unique values from source) only enable flat file
Pre SQL		(before reads to the source)
Post SQL		(after it writes to the target)

Optimization:
-- Use the source qualifier to filter. The source qualifier limits the row set extracted from the source where as filter limits the row sent to a target
Performance:
1) Join data originating from the same source database
2) Filter rows when the PCS reads source data
3) Specify an outer rather than the default inner join
4) Specify an outer rather than the default inner join
5) Specify only distinct values from the source
6) Create custom query to issue a special select statement for the PCS to read source data
7) Data type we can't change, if you can change mapping is invalid
Target Load Order:
1) Multiple SQ connected multiple target
2) One SQL provide multiple target you can enable constraint based loading in a session to have the PCS load data based on target table PK and FK relationship
Default Join:
1) PK - FK Relationship
2) Matching Data type
Custom Join:
1) Custom join don't have PK and FK relationship
2) Data type of columns used for the join didn't match
Outer Join support:
1) Default query outer join statement nested query created (left outer, right outer, full outer)

8. Router Transformation (A/C -RTR)

Definition:

1) This is an active and connected transformation

2) Similar to filter transformation

3) Single input multiple target opp to union transformation

4) Processing the incoming data only once and passes the output to multiple groups and routes data to the default O/P group that do not meet the condition

Functions:

1) Router transformation in mapping the PCS process the incoming data only once

2) Router transformation efficient filter transformation

3) Router transformation one input group multiple output group

(user define output (many) & default output group (one only)

Ports:

Input Port	(enable) only input group
Output Port	(not visible) - only output group (because group only findout)

Group:

Input Group	user define group to test a condition based on incoming data
Output Group	1. user defined group 2. Default group     - we can't modify on delete output ports     - only connected target group     - output group of sequential only default created     - If you want the PCS to drop all rows in the default group, don't connect it to a transformation or a Target in a mapping     - If rows meets more than one group filter condition, the PCS passes this rows multiple time

Properties:

Tracing Level

Component:

Input and Output groups
Input and Output ports
Group filter conditions

Performance and Tips:
1) One group can be connected to more than one transformation or target
2) One output ports in a group can be connected to multiple transformation or targets
3) Multiple output ports in one group can be connected to multiple transformations or targets
4) More than one group cannot be connected to one transformation or target
5) We can't connect more than one group to multiple input group transformation, except for joiner transformations, when you connect each output group to different intput group

9. Sequence Generator Transformation (P/C - SEG)

Definition:

Sequence Generator Transformation generates the numeric values

SGT to create unique primary values, cycle through a sequential range of numbers

Common use:     - SG when you perform multiple loads to a single target

                           - Replacing the missing values

We can't connect to more than one transformation

Functions:

CURRVAL	NEXTVAL + INCREMENTAL BY VALUE One row in each block Currval port without connecting the nextval port Power Center server passes a constant value for each row One row in each block
NEXTVAL	Primary key - down stream trnasformation Unique PK values formation to generate the sequential based on the current value

Ports:

INPORTS	Receive data through unconnected transformation
OUTPORTS	Pass the data to other transformation
	Two default output ports 1. NEXTVAL, 2. CURVAL

Properties:

Start value	0	Cycle option (Enter the value complete the cycle value after restart the value)
Increment by	1	D b/w two consecutive values from the nextval port the default value is 1
End value	(1-2147483647)	The maximum values that Power Center generates Sequence is not configured to cycle it fails the session
Current value		Enter you want first value PC server to use in sequence must generate greater than or equal to the start value and less than the end value
Cycle		If selected - sequence range (up to limit) If not selected - session failure with overflow error
Number of cached values	1	number of cached values determine the number of values the PC server caches at one time
Reset		If selected, PC generates values based on the original current value for each session (otherwise)
Tracing level		Level of information

Performance:
Non reusable sequence generator
--> Cache enable limit greater than 0
--> row skipped the value
--> discards the unused values
Reusable sequence generator
--> Cache enable some upto limit Ex: 1000
Optimization:
--> Use reusable sequence generator if the same sequence generator is to be used in more than one sessions
--> Optimize resuable and use it in multiple mapping
Functions:
1) Perform the above tasks with a sequence generator transform
2) Create keys
3) Replace missing values
4) Cycle through a sequential range of numbers

10. Update Strategy Transformation (A/C - UPD)

Definition:

--> It is an active and connected transformation

--> It is used to update data in target table, either to maintain history of data or recent changes

--> It is used to flag the records for insert, update, delete and reject rows in the target database

--> It is used in slowly changing dimension to update the target table

--> This transformation used to SCD-1, SCD-2 and SCD-3 type

Functions:

Set at a 2 levels

1. Within a session - treats all records in the same way

for example, treat all records as

DD_INSERT-0, DD_UPDATE-1, DD_DELETE-2, DD_REJECT-3,

2. Within a Mapping Levels - Flag records for insert, update, delete or reject

1) Insert: Select this option to insert a row into a target table

2) Delete: Select this option to delete a row from a table

3) Update: We have the above options in this situations: 

Update as update: Update each row flagged for update if exists in the target table
Update as insert: Insert each row flagged for update
Update else insert: Insert each row if it exists. Otherwise, insert it
4) Truncate Table: Select this option to truncate the target table before loading data
Ports:

INPORTS	(I)	Receives data from source
OUTPORTS	(O)	Pass the data to other Transformation

Properties:

Update strategy expression	0	(DD_INSERT-0, DD_UPDATE-1, DD_DELETE-2, DD_REJECT-3)
Forward Rejected Rows		Enable – flags the rows for reject and writes them to the session reject file
Tracing Level	Normal	(Terse/Normal/Verb init/Verb data)

Function:

SCD - 1	It keeps the most recent updated values in the target
SCD - 2	It keeps the full historical business information in the target The full history is maintained by inserting the new record in the target
SCD - 3	It keeps previous value and current

Performance:
1. Whenever use Dynamic cache - at the time you must use UPD transformation
2. Dynamic look up use you can select 
    1. Select insert    2. Select update as update   3. Don't select delete
3. UPD --> AGG Use only - Update, Insert, Delete
4. AGG --> UPD Use only - Update, Insert, Delete, Reject
Update Strategy:
Dynamic look up - you must use UPD transformation
Business Logic:
       IFF((current date>Previous date), DD_reject, DD_update)

--> Dynamic look up we can select
1) Select Insert
2) Select Update as update
3) Don't select delete

11. Look up Transformation (P/C & UC - LKP)

Definition:

Look up transformation in look up data in flat file or a relational table, view or synonym

Look up transformation is used to perform the above tasks

-->  Get a related value

--> Perform a calculation

--> Update slowly changing dimension

Functions:

Relational look up	Dynamic cache use
Flat file look up	Must use in static cache We can configure sorted input

Ports:

INPUT	(I)
OUTPUT	(O)
LOOK UP	(L)
RETURN	(R)

Properties:

Look up SQL Override	(R)
Look up table name	(R)
Look up caching enabled	(R/F)
Look up policy on multiple match
Look up condition
Location information
Source type
Re cache if stale
Tracing level
Look up cache directory name
Look up cache initialize

Component:

Look up table
Ports
Properties
Condition
Metadata Extensions

Look up Cache:

Persistent cache
Re cache from database
Static Cache
Dynamic Cache
Shared Cache

Performance:
Cached Look up: By indexing the columns in the look up Order by
Connected Look up:

Static Cache	Returns value from the look up query
Dynamic Cache	Case 1: No rows found in cache – inserts the record Case 2: Row found in cache – updates the records

Unconnected Look up:
1) Common use into update slowly changing dimension - tables
2) Returns one value into the return port of look up transformation
Connected or Unconnected:
1) Receive input and send output in different ways
Relational or flat file look up:
Cached or un-cached:

Dynamic	Relational
Static	Flat file
Cached	Performance (store the value whenever you want look up table refer only)
Un cached	Each time look up the value

Connected Look up Transformation
Unconnected Look up Transformation:

Relational Look ups	Dynamic Cache
Flat file look up	Can use sorted input Can use indirect file Can sort null date high Can use case sensitive string comparison

Look up Components:

Look source	Cached look up – order by
Ports	Un-cached look up – select
Properties
Condition

Ports:

INPUT PORT	(I)
OUTPUT PORT	(O)
LOOK UP	(L)
RETURN	(R)	only in connected look up transformation

Properties:

Look up override	(R)
Look up table name	(R)	Table synonym,view
Look up cache enabled	(R/F)
Look up policy on multiple match	(F/R)	Enable mean (first, last, return an error)
Look condition	(F/R)
Connection Information	(R)
Source Type	(R/F)
Tracing Level	(R/F)
Look up cache Directory name	(F/R)
Look up cache persistent	(F/R)
Look up data cache	(F/R)
Look up index cache size	(F/R)
Dynamic look up cache	(R)	insert (or) updates – (only look up cache enabled)
Output old value on update	(F/R)	use only with dynamic cache enabled
Cache file name prefix	(F/R)	use only with persistent look up cache name prefix to use persistent look up cache file
Re cache from look up source	(F/R)	Rebuild the persistent cache file
Insert else update	(R)	use only with dynamic cache enabled
Update else Insert	(R)	use only with dynamic cache enabled
Date time Format	(F)
Thousand separator	(F)	default no separator (‘,’’,’)
Decimal separator	(F)	default period (,.)
Case sensitive string comparison	(F)
Null ordering	(F)
Sorted input	(F)

Configuring Look up properties in a session:

Flat file look ups	(file name and file type)
Relational Look up	(you can define $source & $Target variable in session)

Configuring Properties Flat file look up in a session:

Look up source file directory	$LookupFileDir (default)
Look up source file name
Look up source file name	Direct

Configuring Properties Flat file look up in a session:
1) Choose any relational connection
2) Connection variable, $DBconnection
3) Specify database connection for $Source and $Target
Look up Query:
Default look up query

SELECT	SQL override
ORDER BY	we can use enabled the cache ( we can't view this)

Overriding the look up query
* Override the ORDER BY statement
* A look up table name (or) columns contain a reserved word - 'reserved word'
* Use mapping parameter & variables
* A look up column name contains a slash (/) character
* Add where statement
* Other
Overriding the ORDER BY Statement
Order by
Reserved words
* look up or column names contain a database reserved word such as Month, Year - session fails
* resword.txt (PC initilization Directory)
Guideline to Overriding the Look up Query:
* SQL override only look up SQL query relational only
* Cache not enable PCS doesn't recognize the override
* Default query or configure override - lookup/output port - add or substract port from the SELECT statement, the session fails.
* Filter before look up using dynamic cache when you add where clause to look up SQL override
* Override the ORDER BY statement - session fail (because doesn't contain condition port). Don't suppress order only
* Reserved word session fail if you want to use quotes ("")
Step overriding the Look up Query:
=> Properties Tab enter SQL override
=> Validate to test
Look up condition:
1. Data type in condition must match
2. Multiple condition - use AND, OR
3. Flat file for sorted input - session fail (condition are not group so you can select group column)
Uncached Static cache:
1. =, >, <, >=, <=, !=
2. Multiple condition - use AND, OR
3. More than one look up condition (first meet all condition after another so you select GROUP columns
Dynamic cache:
1. Only = operator
2. Can't handle for multiple matchs - otherwise PCS fail
Look up cache:

Index Cache		Condition value
Data Cache		Output value

1.	Persistent Cache
2.	Recache from look up source
3.	Static cache
4.	Dynamic cache
5.	Shard cache	

Configuring Unconnected look up transformation:
=> LKP - reference qualifier to call the look up with in another transformation
=> Calling the same look up multiple times in one mapping
=> Syntax:    : LKP.Lookup_transformation_name(argument, argument,...)
Unconnected use the above kinds
=> Add input port => more than one condition
=> Add the look up condition
=> Designate a return value
=> Call the look up from another transformation
Add input port:
Design for source and target

Item_id	out
IN_Item_id	in

Add Look up Condition:
* Item_id = IN_item_id
* return condition is false look up return null
Design a return value:
* Multiple input and single output only
* Update strategy or filter expression
Call the look up through an Expression:
Example: IFF (Isnull (:LKP.lkpitems_dim(item_id, price)), DD_Reject
Creating a look up Transformation:
1. Choose an existing table or file definition
2. Import a definition from a relational or file
3. Skip a create a manual definition
Tips
1. Add an index to the column used in a look up condition
2. Place condition with an equality operator (=) first.

Look up Cache

Index	PCS Condition value
Data Cache	Output value
Default	$PMCacheDir
	Data doesn't fit in the memory cache
	PCS stores the over flow value in the cache file when the session completes
	Flat file look up for sorted input

1. Persistent Cache:
* Save and reuse them for next time
2. Recache from source:
* persistent cache is not synchronized with the look up table
* Rebuild
3. Static Cache:
* Read only
* Default Cache
* PCS doesn't update the cache
4. Dynamic Cache:
* insert new rows or update existing row
* Dynamic insert and update - pass data to target table
* Can't use flat file
5. Shared Cache:
* Can be used for multiple transformation
Explaination:-
1. Persistent Cache:
* PCS save or delete look up cache files after successful session based on the look up cache persistent property
* Look up table doesn't change between session you can configure the look up transformation to use a persistent look up cache
* PCS saves & reuse cache files from session to session so eliminating time required to read the look up table
Non Persistent Cache
* Enable caching in look up transformation the PCS delete the cache files at the end of a session
* Next time you run the session the PCS build the memory cache from the database
Persistent Cache
* If you want to save and reuse the cache files, you can configure the transformation
* Use persistent cache the look up table doesn't change between session runs
* Look up table changes occasionally, can over ride session properties to re caches the look up from the database
* Use persistent cache means you can specify a name for the cache file
PCS server handling of persistent caches 
2. Rebuilding the look up Caches
* Rebuild the look up caches, look up sources changed size the last time the PCS build cache
* When you rebuild the cache the PCS create new cache file over riding existing persistent cache file
* The PCS server write a message to the session log file when if rebuild the caches
* Don't choose to re-cache the look up source PCS automatically re-build the persistent cache
3. Static Cache (or) Uncached Look up
* The PCS builds the cache when it preocess the first lookup request
* PCS doesn't update the cache
* Condition true - connected look up transformation return values represent by look up/output ports
* Condition true - unconnected look up transformation return values represent by return ports
* Condition is not true - connected look up transformation returns values to output port
* Condition is not true - unconnected look up transformation returns null values to returns to port
* Multiple partition means PCS create one memory cache for each partition
4. Dynamic look up cache
* Insert the row into the cache
* Update the row in the cache
* Makes no changes to the cache
=> Some situation when you can use dynamic look up cache
1. Update a master customer table with new & updated customer information

Static look up cache	fact file
Dynamic look up cache	Relational table

2. Loading data into a slowly changing dimension table and fact table
3. Router or filter - use
4. Multiple partition in a pipeline that use a dynamic look up cache the PCS create one memory cache and one disk cache for each transformation
New look up row port:
* Target table synchronized
* Ignore Null input for updates
* Ignore in comparison
      => Ignore Null values
Using the associated Input port:
* you must associate each look up/out port with an input/output port or sequence ID
* The PCS uses the data in the associated port to insert or update rows in the look up cache
Sequence ID generate the above process:
* PCS create dynamic look up cache - tracks the range of value in the cache associated with any port using a sequence ID
* Maximum value for a sequence ID is 2147483647
Working with look up transformation values 
* Associated an input/output ports or a sequence ID with a look up/output port - above value match default

Input value	PCS passes into the Transformation
Look up values	PCS passes insert into the cache

Input/output port value - PCS passes out of the Input/output port
* Out put old value on update - PCS output the value that existed in the cache before it updated the row
* Out put new value on update - PCS output the updated value that it writes in cache
* When the update a dynamic look up cache and Target table
* PCS can handle the null values in the above ways:
=> Insert Null values -
=> Ignore Null values - (Not null values)
* When you run a session that use a dynamic look up cache PCS compares the value in all look up ports with the value
* If compare the value to determine whether or not to update the row in the look up cache
Update strategy Transformation with a Dynamic cache: 
1. Row entering the look up Transformation: (By default) all row type all rows entering a look up transformation is insert
2. Row leaving the look up Transformation : PCS changed the look up cache but it does not change the row type
3. Update Strategy trnasformation & a dynamic look up cache you must define certain session properties
4. PCS result the look up cache & Target table might become unsynchronized
5. Sharing the look up cache:
* You can configure multiple look up Transformation in a mapping to store a single look up cache.
* You can share cache that are unnamed & named

Unnamed Cache	Compatible caching structure The PCS share the cache by default, you can share static unnamed caches
Named Cache	Use a persistent named cache When you want to share cache files acress mapping or share and a static cache

Sharing an unnamed look up cache:
* When two look up transformation share an unnamed cache
* You can share static unnamed cache
Sharing an named look up cache:
* We can share the cache between multiple look up transformation by using persistent
* We can share one cache between look up transformation is the same mapping (or) across mapping
* Named cache - cache directory for cache files with the same files name prefix
* Specify the cache file directory
* PCS rebuild the memory cache from the persisted file
* PCS structure don't match the PCS fails the session
* PCS process multiple session simultaneously when the look up transformation only need to read the cache files
* A named cache created by a dynamic look up transformation with a look up policy
Tip(s)
* PCS then saves & reuses cache files from session to session, eliminating the time required to read the look up table

12. Union Transformation (A/C - UN)

Definition:

* Union Transformation is a multiple input group transformation which is reponsible for merging the data coming from more than one source

* Union Transformation also merges the data hetero geneous source also

* Union trnasformation is newly introduced in Informatica 7.1 version onwards

* Union Transformation to the UNION ALL statement

Union Transformation is developed using the custom Transformation

Function:

* Create multiple input groups but only one output groups

* All input groups and the output groups must have matching port. The precesion, data type and scale must be identical across all groups

* Union Transformation doesn't remove all duplicate rows

* To remove duplicate rows you must add another transformation upstream from a union transformation

* Can't use sequence generator or update strategy transformation

* Union Transformation doesn't generate trasaction

Component:

Transformation Tab	You can re-name the transformation and add a description
Properties Tab	You can specify the Tracing level
Groups	You can create and delete input groups (Design displays groups you create on the ports table
Groups ports tab	You can create and delete ports for the input groups

We can't modify ports, Initilization properties, metadata Extension or port attributes definition Tab
Ports:
Groups & Ports
Multiple input groups and one output groups, design create output groups by default we can't edit or delete the outputs groups
Mapping:
* Union Transformation is non blocking multiple input group transformation
* When you add a union transformation to a mapping you must verify that you connect the same ports in all input groups. If you connect all ports in one input groups, but don't connect a port in another input group the PCS passes Nulls to the unconnected ports
Properties:

		Mapping Level	Session Level
Module identifiers	pmuniontrans
Function identifiers	pmunionfunc
Runtime Location		Enable
Tracing Level	Normal	Enable
Is partition able
Inputs must block
Is Active
Update Strategy Transformation
Transformation scope	Row
Generate Transformation
Output Repeatable	Never

Performance: