PushDown Optimization

PDO Overview:

http://dwbi.org/etl/informatica/162-pushdown-optimization-in-informatica

https://www.youtube.com/watch?v=zUouLglsJ4A
https://www.youtube.com/watch?v=qCqCwOYkCDk

PDO limitations:

1.     very much dependent on the database like, in case of full push down source and target should be in same database.

2.     Partitions you create in session for performance improvement may not work as thought since pushdown queries will overwrite those. 

3.     Some functions in exp transform returns unexpected output, add_to_date in teradata,Date_diff and ROUND not done properly. 

4.    When we configure session for full pushdown and all other criterions are matched, IS creates very complex queries to execute on database, this may in turn impact the performance. So blindly enabling pushdown may not improve performance always. 

5.     Transformation like rank and aggregate transformation cannot be pushed down. 

6.     In optimization viewer all the transformation which can be pushed down will be marked, looking at the queries generated and transformation which is excluded, we can redesign mapping wisely.

7.       When we implement PDO, the integration services send logic to databases. Because of this we cannot capture the rejected records in reject file.

Informatica: Types of Caches

static cache

is one in which data when changed in the target table cannot be incorporated in
the look up while the session is running.

when you use a dynamic cache any changes to the target table gets reflected in the lookup
even if the record is being inserted or updated in the same mapping run priorly

Informatica: performance - Lookup

Lookup performance 

Lookup is an important and a useful transformation when used effectively.
What is a lookup transformation? It is just not another transformation which fetches you data to look against the source data. It is a transformation when used improperly, makes your flow run for ages.

I now try to explain different scenarios where you can face problems with Lookup and also how to tackle them.

Unwanted columns:

By default, when you create a lookup on a table, PowerCenter gives you all the columns in the table, but be sure to delete the unwanted columns from the lookup as they affect the lookup cache very much. You only need columns that are to be used in lookup condition and the ones that have to get returned from the lookup.

SQL query:

We will start from the database. Find the execution plan of the SQL override and see if you can add some indexes or hints to the query to make it fetch data faster. You may have to take the help of a database developer to accomplish this if you, yourself are not an SQLer.

Size of the source versus size of lookup:

Let us say, you have 10 rows in the source and one of the columns has to be checked against a big table (1 million rows). Then PowerCenter builds the cache for the lookup table and then checks the 10 source rows against the cache. It takes more time to build the cache of 1 million rows than going to the database 10 times and lookup against the table directly.

Use uncached lookup instead of building the static cache, as the number of source rows is quite less than that of the lookup.

Conditional call of lookup:
Instead of going for connected lookups with filters for a conditional lookup call, go for unconnected lookup. Is the single column return bothering for this? Go ahead and change the SQL override to concatenate the required columns into one big column. Break them at the calling side into individual columns again.

JOIN instead of Lookup:

In the same context as above, if the Lookup transformation is after the source qualifier and there is no active transformation in-between, you can as well go for the SQL over ride of source qualifier and join traditionally to the lookup table using database joins, if both the tables are in the same database and schema.

Increase cache:

If none of the above seems to be working, then the problem is certainly with the cache. The cache that you assigned for the lookup is not sufficient to hold the data or index of the lookup. Whatever data that doesn't fit into the cache is spilt into the cache files designated in $PMCacheDir. When the PowerCenter doesn't find the data you are lookingup in the cache, it swaps the data from the file to the cache and keeps doing this until it finds the data. This is quite expensive for obvious reasons being an I/O operation. Increase the cache so that the whole data resides in the memory.

What if your data is huge and your whole system cache is less than that? Don't promise PowerCenter the amount of cache that it can't be allotted during the runtime. If you promise 10 MB and during runtime, your system on which flow is running runs out of cache and can only assign 5MB. Then PowerCenter fails the session with an error.

Cachefile file-system:

In many cases, if you have cache directory in a different file-system than that of the hosting server, the cache file piling up may take time and result in latency. So with the help of your system administrator try to look into this aspect as well.

Useful cache utilities:

If the same lookup SQL is being used in someother lookup, then you have to go for shared cache or reuse the lookup. Also, if you have a table that doesn't get data updated or inserted quite often, then use the persistent cache because the consecutive runs of the flow don't have to build the cache and waste time.

After all these are tried out, sit and wait for the session to get completed - there is nothing else you can do about the lookup.

Source:Click Here

Informatica: Error handling Logic

Bad files contains column indicator and row indicator.
Row indicator: It generally happens when working with update strategy transformation. The writer/target rejects the rows going to the target

Columnindicator:
D -valid
o - overflow
n - null
t - truncate
When the data is with nulls or overflow it will be rejected to write the data to the target
The reject data is stored on reject files. You can check the data and reload the data in to the target using reject reload utility 


Example of data:



0,D,,N,16103,D,19,D,S,D,SPECIALTY,D,All Others,D,0000X,D,Unmapped,D
0,D,01,D,20555,D,17,D,T,D,PRVDR TYPE,D, name,D,261QU0200X,D,Urgent Care,D
0,D,AA,D,20555,D,17,D,T,D,PRVDR TYPE,D,name,D,0000X,D,Unmapped,D

Informatica: Parameter Vs Variable

Parameter - Represents a constant value that cannot be changed throughout the session run.

Variable - Represents a value that can be changed during session run.There are some functions available to change the variable value like setvariable() setmaxvariable() ..

Informatica: Use of Data Driven option

The informatica server follows instructions coded into update strategy transformations within the session mapping which determine how to flag records for insert, update, delete or reject. If we do not choose data driven option setting, the informatica server ignores all update strategy transformations in the mapping.

Informatica: Interview questions

1. Sorter Transformation : Is it active or Passive

if we check the box distinct means u are eliminating the

duplicate record.so here if we check the distinct while

sorting its eliminating the duplicate records and after its

sorts so the number of records in target are less compared

to source for that it is calles as active. if we uncheck

this all the records are transforms into target table so it

is passive.

Informatica: DWH concepts

refer:
http://it.toolbox.com/wiki/index.php/Data_Warehouse_Concepts#Passive_Transformation

http://www.slideshare.net/dwiner/informatica-training-244742

Informatica: Active and passive

Active Transformation An active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not meet the condition in transformation.

Passive Transformation A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.

Informatica: Caches

Types of Caches;

1.Static Cache

2. Dynamic Cache

3. persistence cache


Definitions;

Static cache: U can configure a static or readonly cache for only lookup table.By default informatica server creates a static cache.It caches the lookup table and lookup values in the cache for each row that comes into the transformation.when the lookup condition is true,the informatica server does not update the cache while it prosesses the lookup transformation. 

Dynamic cache: If u want to cache the target table and insert new rows into cache and the target,u can create a look up transformation to use dynamic cache.The informatica server dynamically inerts data to the targettable. 

Persistence cache: If you want to save and reuse the cache files, you can configure the transformation to use a persistent cache. Use a persistent cache when you know the lookup table does not change between session runs.

The first time the Integration Service runs a session using a persistent lookup cache, it saves the cache files to disk instead of deleting them. The next time the Integration Service runs the session, it builds the memory cache from the cache files. If the lookup table changes occasionally, you can override session properties to recache the lookup from the database. 
Shared cache. You can share the lookup cache between multiple transformations. You can share an unnamed cache between transformations in the same mapping. You can share a named cache between transformations in the same or different mappings.

static cache is one in which data when changed in the target table cannot be incorporated in the look up while the session is running.

when you use a dynamic cache any changes to the target table gets reflected in the lookup even if the record is being inserted or updated in the same mapping run priorly

By default lookup is static.

Dynamic is used whn the records coming frm the source to target in multiple times. i.e if

1)emp_id=101...city=hyd...age=25...                          (first this is inserted to target)

2)at second time when 101 employee is changing his city frm hyd to chennai here we need to update the target table with city name as chennai. thn how lookup cache knows tht a

record is updated...

3) if it is static lookup, after updating tht record it will not refresh the lookup cache.... where as if it is dynamic cache it will refresh the cache....

4) Based on our requirement we r going to use.....

Use dynamic cache, if target itself is a lookup table.

tell me the scenario where I should use dynamic cache. 
We use dynamic cache when the target table is also the lookup table. In this scenario we need the data just inserted into the target to be reflected in the cache also , so we go for dynamic caching. This dynamic caching will enable us to flag rows for insert,update or no change.

We use static cache when the lookup is not a target or there is no need for the lookup cache to be in sync with the Target table.


difference between static cache and dynamic cache:
In case of dynamic cache, when we are inserting a new row it checks the lookup cache to see if it exists, if not inserts it into the target as well as the cache but in case of static cache the new row is written only in the target and not the lookup cache.

The lookup cache remains static and does not change during the session but incase of dynamic cache the server inserts, updates in the cache during session.

For a cached lookup the entire rows (lookup table) will be put in the buffer, and compare these rows with the incomming rows.

where as uncached lookup, for every input row the lookup will query the lookup table and get the rows.

In Cache Lookup a cache will be created of that Lookup table and IS query once for mapping rows for Uncache lookup No cache will be build and IS query for each Mapping rows.
So for performance Go for Cache lookup if Lookup table size< Mapping rows
Go for UnCache lookup if Lookup table size> Mapping rows

Informatica: Mapplets

Mapping is a set of transformations used to Map source tables to target data tables.

Mapplet is nothing but a reusable Mapping.. In Mapplet you won't specify the actual targets..

There is one more difference, if a variable and paramater is defined in a mapping cannot be used in a mapplet.

The following should not include in a mapplet.

• Normalizer transformations
• Cobol sources
• XML Source Qualifier transformations
• XML sources
• Target definitions
• Pre- and post- session stored procedures
• Other mapplets

The exact reason i why these should not used i dont know.

Informatica: connected and unconnected

There should be no performance difference between the two. However with an unconnected lookup you can use logic to determine on a row by row basis whether or not to call it. A connected lookup will be called on each row on that mapping stream. So if you don't need to do the lookup on every row AND the your session bottleneck is in the dtm you may see a performance improvement by using an unconnected lookup. Differences Between Connected and Unconnected Lookups

Connected Lookup	Unconnected Lookup
Receives input values directly from the pipeline.	Receives input values from the result of a :LKP expression in another transformation.
Use a dynamic or static cache.	Use a static cache.
Cache includes all lookup columns used in the mapping (that is, lookup source columns included in the lookup condition and lookup source columns linked as output ports to other transformations).	Cache includes all lookup/output ports in the lookup condition and the lookup/return port.
Can return multiple columns from the same row or insert into the dynamic lookup cache.	Designate one return port (R). Returns one column from each row.
If there is no match for the lookup condition, the Integration Service returns the default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged.	If there is no match for the lookup condition, the Integration Service returns NULL.
If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the Integration Service either updates the row the in the cache or leaves the row unchanged.	If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition into the return port.
Pass multiple output values to another transformation. Link lookup/output ports to another transformation.	Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling :LKP expression.
Supports user-defined default values.	Does not support user-defined default values.