What are the differences between Connected and
Unconnected Lookup?
The differences are illustrated in
the below table
Connected Lookup
|
Unconnected Lookup
|
Connected lookup participates in
dataflow and receives input directly from the pipeline
|
Unconnected lookup receives input
values from the result of a LKP: expression in another transformation
|
Connected lookup can use both
dynamic and static cache
|
Unconnected Lookup cache can NOT be
dynamic
|
Connected lookup can return more
than one column value ( output port )
|
Unconnected Lookup can return only
one column value i.e. output port
|
Connected lookup caches all lookup
columns
|
Unconnected lookup caches only the
lookup output ports in the lookup conditions and the return port
|
Supports user-defined default values
(i.e. value to return when lookup conditions are not satisfied)
|
Does not support user defined
default values
|
What is meant by active and passive transformation?
An active transformation is the one
that performs any of the following actions:
1) Change the number of rows between transformation input and output. Example: Filter transformation. 2) Change the transaction boundary by defining commit or rollback points., example transaction control transformation. 3) Change the row type, example Update strategy is active because it flags the rows for insert, delete, update or reject. |
On the other hand a passive
transformation is the one which does not change the number of rows that pass
through it. Example: Expression transformation.
What is the difference between Router and Filter?
Following differences can be noted,
Router
|
Filter
|
Router transformation divides the
incoming records into multiple groups based on some condition. Such groups
can be mutually inclusive (Different groups may contain same record)
|
Filter transformation restricts or
blocks the incoming record set based on one given condition.
|
Router transformation itself does
not block any record. If a certain record does not match any of the routing
conditions, the record is routed to default group
|
Filter transformation does not have
a default group. If one record does not match filter condition, the record is
blocked
|
Router acts like CASE.. WHEN
statement in SQL (Or Switch().. Case statement in C)
|
Filter acts like WHERE condition is
SQL.
|
What can we do to improve the performance of
Informatica Aggregator Transformation?
Aggregator performance improves
dramatically if records are sorted before passing to the aggregator and
"sorted input" option under aggregator properties is checked. The
record set should be sorted on those columns that are used in Group By
operation.
It is often a good idea to sort the
record set in database level (click here to see why?) e.g. inside a source qualifier
transformation, unless there is a chance that already sorted records from
source qualifier can again become unsorted before reaching aggregator
What are the different lookup cache(s)?
Informatica Lookups can be cached or
un-cached (No cache). And Cached lookup can be either static or dynamic.
A static cache is one which does not modify the cache once it
is built and it remains same during the session run. On the other hand, A dynamic cache is refreshed during the session run
by inserting or updating the records in cache based on the incoming source
data. By default, Informatica cache is static cache.
A lookup cache can also be divided
as persistent or non-persistent based on whether Informatica retains
the cache even after the completion of session run or deletes it
How can we update a record in target table without
using Update strategy?
A target table can be updated without
using 'Update Strategy'. For this, we need to define the key in the target
table in Informatica level and then we need to connect the key and the field we
want to update in the mapping Target. In the session level, we should set the
target property as "Update as Update" and check the
"Update" check-box.
Let's assume we have a target table
"Customer" with fields as "Customer ID", "Customer
Name" and "Customer Address". Suppose we want to update
"Customer Address" without an Update Strategy. Then we have to define
"Customer ID" as primary key in Informatica level and we will have to
connect Customer ID and Customer Address fields in the mapping. If the session
properties are set correctly as described above, then the mapping will only
update the customer address field for all matching customer IDs.
Under what condition selecting Sorted Input in
aggregator may fail the session?
§ If the input data is not sorted
correctly, the session will fail.
§ Also if the input data is properly
sorted, the session may fail if the sort order by ports and the group by ports
of the aggregator are not in the same order.
Why is Sorter an Active Transformation?
This is because we can select the
"distinct" option in the sorter property.
When the Sorter transformation is
configured to treat output rows as distinct, it assigns all ports as part of
the sort key. The Integration Service discards duplicate rows compared during
the sort operation. The number of Input Rows will vary as compared with the
Output rows and hence it is an Active transformation.
Is lookup an active or passive transformation?
From Informatica 9x, Lookup
transformation can be configured as as "Active" transformation.
However, in the older versions of
Informatica, lookup is a passive transformation
What is the difference between Static and Dynamic
Lookup Cache?
We can configure a Lookup
transformation to cache the underlying lookup table. In case of static or
read-only lookup cache the Integration Service caches the lookup table at the
beginning of the session and does not update the lookup cache while it
processes the Lookup transformation.
In case of dynamic lookup cache the
Integration Service dynamically inserts or updates data in the lookup cache and
passes the data to the target. The dynamic cache is synchronized with the
target.
In case you are wondering why do we
need to make lookup cache dynamic, read this article on dynamic lookup
What is the difference between STOP and ABORT
options in Workflow Monitor?
When we issue the STOP command on the
executing session task, the Integration Service stops reading data from source.
It continues processing, writing and committing the data to targets. If the
Integration Service cannot finish processing and committing data, we can issue
the abort command.
In contrast ABORT command has a
timeout period of 60 seconds. If the Integration Service cannot finish
processing and committing data within the timeout period, it kills the DTM
process and terminates the session.
What are the new features of Informatica 9.x in
developer level?
From a developer's perspective, some of
the new features in Informatica 9.x are as follows:
§ Now Lookup can be configured as an
active transformation - it can return multiple rows on successful match
§ Now you can write SQL override on
un-cached lookup also. Previously you could do it only on cached lookup
§ You can control the size of your
session log. In a real-time environment you can control the session log file
size or time
§ Database deadlock resilience feature
- this will ensure that your session does not immediately fail if it encounters
any database deadlock, it will now retry the operation again. You can configure
number of retry attempts.
How to Delete duplicate row using Informatica
Scenario 1: Duplicate rows are present
in relational database
Suppose we have Duplicate records in
Source System and we want to load only the unique records in the Target System
eliminating the duplicate rows. What will be the approach?
Assuming that the source system is
a Relational Database, to eliminate duplicate records, we can check
the Distinctoption of the Source Qualifier of the
source table and load the target accordingly.
No comments:
Post a Comment