1.What is a lookup
transformation?
A
lookup transformation is used to look up data in a flat file, relational table,
view, and synonym.
2. What are the
tasks of a lookup transformation?
The
lookup transformation is used to perform the following tasks?
Get a related value: Retrieve a value from the lookup table based on a value in the source.
Get a related value: Retrieve a value from the lookup table based on a value in the source.
Perform a
calculation: Retrieve a value from a lookup table and use it in a calculation.
Update slowly
changing dimension tables: Determine whether rows exist in a target.
3.How do you configure
a lookup transformation?
Configure
the lookup transformation to perform the following types of lookups:
Relational or flat file lookup
Relational or flat file lookup
Pipeline
lookup
Connected
or unconnected lookup
Cached
or uncached lookup
4. What is a pipeline lookup transformation?
A pipeline lookup transformation is used to perform lookup on application
sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source
qualifier as the lookups source.
5. What are the differences between connected and
unconnected lookup transformation?
- Connected lookup transformation receives input values directly from the pipeline. Unconnected lookup transformation receives input values from the result of a :LKP expression in another transformation.
- Connected lookup transformation can be configured as dynamic or static cache. Unconnected lookup transformation can be configured only as static cache.
- Connected lookup transformation can return multiple columns from the same row or insert into the dynamic lookup cache. Unconnected lookup transformation can return one column from each row.
- If there is no match for the lookup condition, connected lookup transformation returns 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 unconnected lookup transformation returns null.
- In a connected lookup transformation, the cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. In an unconnected lookup transformation, the cache includes all lookup/output ports in the lookup condition and the lookup/return port.
- Connected lookup transformation passes multiple output values to another transformation. Unconnected lookup transformation passes one output value to another transformation.
- Connected lookup transformation supports user-defined values. Unconnected lookup transformation does not support user-defined default values.
6. How do you handle multiple matches in lookup transformation? or what is "Lookup Policy on Multiple Match"?
"Lookup Policy on Multiple Match" option is used to determine which
rows that the lookup transformation returns when it finds multiple rows that
match the lookup condition. You can select lookup to return first or last row
or any matching row or to report an error.
7. What is "Output Old Value on Update"?
This option is used when dynamic cache is enabled. When this option is enabled,
the integration service outputs old values out of the lookup/output ports. When
the Integration Service updates a row in the cache, it outputs the value that
existed in the lookup cache before it updated the row based on the input data.
When the Integration Service inserts a new row in the cache, it outputs null
values. When you disable this property, the Integration Service outputs the
same values out of the lookup/output and input/output ports.
8. What is "Insert Else Update" and "Update
Else Insert"?
These options are used when dynamic cache is enabled.
These options are used when dynamic cache is enabled.
·Insert Else Update option applies to rows
entering the lookup transformation with the row type of insert. When this
option is enabled the integration service inserts new rows in the cache and
updates existing rows when disabled, the Integration Service does not update
existing rows.
·Update Else Insert option applies to rows
entering the lookup transformation with the row type of update. When this
option is enabled, the Integration Service updates existing rows, and inserts a
new row if it is new. When disabled, the Integration Service does not insert
new rows.
9. What are the options available to configure a lookup
cache?
The following options can be used to configure a lookup cache:
The following options can be used to configure a lookup cache:
·
Persistent cache
·
Recache from lookup source
· Static cache
· Dynamic cache
· Shared Cache
· Pre-build lookup cache
10. What is a cached lookup transformation and uncached
lookup transformation?
- Cached lookup transformation: The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.
- Uncached lookup transformation: For each row that enters the lookup transformation, the Integration Service queries the lookup source and returns a value. The integration service does not build a cache.
11. How the integration service builds the caches for
connected lookup transformation?
The Integration Service builds the lookup caches for connected lookup
transformation in the following ways:
· Sequential cache: The Integration Service
builds lookup caches sequentially. The Integration Service builds the cache in
memory when it processes the first row of the data in a cached lookup
transformation.
· Concurrent caches: The Integration Service
builds lookup caches concurrently. It does not need to wait for data to reach
the Lookup transformation.
12. How the integration service builds the caches for
unconnected lookup transformation?
The Integration Service builds caches for unconnected Lookup transformations as
sequentially.
13. What is a dynamic cache?
The dynamic cache represents the data in the
target. The Integration Service builds the cache when it processes the first
lookup request. It queries the cache based on the lookup condition for each row
that passes into the transformation. The Integration Service updates the lookup
cache as it passes rows to the target. The integration service either inserts
the row in the cache or updates the row in the cache or makes no change to the
cache.
14. When you use a dynamic cache, do you need to associate
each lookup port with the input port?
Yes. You need to associate each lookup/output port with the input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache.
Yes. You need to associate each lookup/output port with the input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache.
15. What are the different values returned by NewLookupRow
port?
The different values are
The different values are
·0 - Integration Service does not update or
insert the row in the cache.
·1 - Integration Service inserts the row into
the cache.
·2 - Integration Service updates the row in the
cache.
16. What is a persistent cache?
If the lookup source does not change between session runs, then you can improve
the performance by creating a persistent cache for the source. When a session
runs for the first time, the integration service creates the cache files and saves
them to disk instead of deleting them. The next time when the session runs, the
integration service builds the memory from the cache file.
17. What is a shared cache?
You can configure multiple Lookup transformations in a mapping to share a
single lookup cache. The Integration Service builds the cache when it processes
the first Lookup transformation. It uses the same cache to perform lookups for
subsequent Lookup transformations that share the cache.
18. What is unnamed cache and named cache?
· Unnamed cache: When Lookup transformations in a
mapping have compatible caching structures, the Integration Service shares the
cache by default. You can only share static unnamed caches.
·
Named cache: Use a persistent named cache when
you want to share a cache file across mappings or share a dynamic and a static
cache. The caching structures must match or be compatible with a named cache.
You can share static and dynamic named caches.
19. How do you improve the performance of lookup
transformation?
- Create an index on the columns used in the lookup condition
- Place conditions with equality operator first
- Cache small lookup tables.
- Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.
- Use persistent cache for static lookups.
- Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
- For flat file lookups, provide Sorted files as
lookup source.
We can use the Lookup transformation to perform many tasks, including:
1) Get a related value.
2) Perform a calculation.
3) Update slowly changing dimension tables.
20. Did you use unconnected Lookup Transformation ? If yes, then explain.
Yes. An Unconnected Lookup receives input value as a result of :LKP Expression in another
transformation. It is not connected to any other transformation. Instead, it has input ports,
output ports and a Return Port.
An Unconnected Lookup can have ONLY ONE Return PORT.
21. What is Lookup Cache ?
The PowerCenter Server builds a cache in memory when it processes the first row of data in a
cached Lookup transformation.
It allocates the memory based on amount configured in the session. Default is
2M Bytes for Data Cache and 1M bytes for Index Cache.
We can change the default Cache size if needed.
Condition values are stored in Index Cache and output values in Data cache.
22.What happens if the Lookup table is larger than the Lookup Cache ?
If the data does not fit in the memory cache, the PowerCenter Server stores the overflow values
in the cache files.
To avoid writing the overflow values to cache files, we can increase the default cache size.
When the session completes, the PowerCenter Server releases cache memory and deletes the cache files.
If you use a flat file lookup, the PowerCenter Server always caches the lookup source.
23. What is meant by "Lookup caching enabled" ?
By checking "Lookup caching enabled" option, we are instructing Informatica Server to Cache lookup
values during the session.
24. What are the different types of Lookup ?
When configuring a lookup cache, you can specify any of the following options:
a) Persistent cache.You can save the lookup cache files and reuse them the next time the
PowerCenter Server processes a Lookup transformation configured to use the cache.
b) Recache from source. If the persistent cache is not synchronized with the lookup table,
you can configure the Lookup transformation to rebuild the lookup cache.
c) Static cache. You can configure a static, or read-only, cache for any lookup source.
By default, the PowerCenter Server creates a static cache. It caches the lookup file or table
and looks up values in the cache for each row that comes into the transformation.
When the lookup condition is true, the PowerCenter Server returns a value from the lookup
cache. The PowerCenter Server does not update the cache while it processes the Lookup
transformation.
d) Dynamic cache. If you want to cache the target table and insert new rows or update
existing rows in the cache and the target, you can create a Lookup transformation to
use a dynamic cache.
The PowerCenter Server dynamically inserts or updates data in the lookup cache and passes
data to the target table.
You cannot use a dynamic cache with a flat file lookup.
e) 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.
1) Get a related value.
2) Perform a calculation.
3) Update slowly changing dimension tables.
20. Did you use unconnected Lookup Transformation ? If yes, then explain.
Yes. An Unconnected Lookup receives input value as a result of :LKP Expression in another
transformation. It is not connected to any other transformation. Instead, it has input ports,
output ports and a Return Port.
An Unconnected Lookup can have ONLY ONE Return PORT.
21. What is Lookup Cache ?
The PowerCenter Server builds a cache in memory when it processes the first row of data in a
cached Lookup transformation.
It allocates the memory based on amount configured in the session. Default is
2M Bytes for Data Cache and 1M bytes for Index Cache.
We can change the default Cache size if needed.
Condition values are stored in Index Cache and output values in Data cache.
22.What happens if the Lookup table is larger than the Lookup Cache ?
If the data does not fit in the memory cache, the PowerCenter Server stores the overflow values
in the cache files.
To avoid writing the overflow values to cache files, we can increase the default cache size.
When the session completes, the PowerCenter Server releases cache memory and deletes the cache files.
If you use a flat file lookup, the PowerCenter Server always caches the lookup source.
23. What is meant by "Lookup caching enabled" ?
By checking "Lookup caching enabled" option, we are instructing Informatica Server to Cache lookup
values during the session.
24. What are the different types of Lookup ?
When configuring a lookup cache, you can specify any of the following options:
a) Persistent cache.You can save the lookup cache files and reuse them the next time the
PowerCenter Server processes a Lookup transformation configured to use the cache.
b) Recache from source. If the persistent cache is not synchronized with the lookup table,
you can configure the Lookup transformation to rebuild the lookup cache.
c) Static cache. You can configure a static, or read-only, cache for any lookup source.
By default, the PowerCenter Server creates a static cache. It caches the lookup file or table
and looks up values in the cache for each row that comes into the transformation.
When the lookup condition is true, the PowerCenter Server returns a value from the lookup
cache. The PowerCenter Server does not update the cache while it processes the Lookup
transformation.
d) Dynamic cache. If you want to cache the target table and insert new rows or update
existing rows in the cache and the target, you can create a Lookup transformation to
use a dynamic cache.
The PowerCenter Server dynamically inserts or updates data in the lookup cache and passes
data to the target table.
You cannot use a dynamic cache with a flat file lookup.
e) 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.
The differences are illustrated in the
below table:
|
Connected
|
Un-Connected
|
|
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
|
Unconnected Lookup can
return only one column value i.e. output
|
|
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
|
25.
What is the difference between
Static and Dynamic Lookup Cache?
Ans.
We can configure a
Lookup transformation to cache the corresponding 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.
26. 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 used to be a passive transformation.
27.How insert and this row to identify this row for for
update in dynamic lookup cache?
Based on NEW LOOKUP ROW.. Informatica server indicates which
one is insert and which one is update.
Newlookuprow- 0...no change
Newlookuprow- 1...Insert
Newlookuprow- 2...update
0 comments:
Post a Comment