| Dynamic Lookup Cache | Static Lookup Cache |
| In dynamic lookup the cache memory will get refreshed as soon as the record get inserted or updated/deleted in the lookup table. | In static lookup the cache memory will not get refreshed even though record inserted or updated in the lookup table it will refresh only in the next session run. |
| When we configure a lookup transformation to use a dynamic lookup cache, you can only use the equality operator in the lookup condition. NewLookupRow port will enable automatically. | It is a default cache. |
| Best example where we need to use dynamic cache is if suppose first record and last record both are same but there is a change in the address. What informatica mapping has to do here is first record needs to get insert and last record should get update in the target table. | If we use static lookup first record it will go to lookup and check in the lookup cache based on the condition it will not find the match so it will return null value then in the router it will send that record to insert flow. But still this record dose not available in the cache memory so when the last record comes to lookup it will check in the cache it will not find the match so it returns null value again it will go to insert flow through router but it is suppose to go to update flow because cache didn’t get refreshed when the first record get inserted into target table. |
Saturday, 11 June 2011
Differences between dynamic lookup and static lookup ?
What are the Lookup Caches ?
When configuring a lookup cache, you can specify any of the following options:
Dynamic cache: When you use a dynamic cache, the PowerCenter Server updates the lookup cache as it passes rows to the target.
If you configure a Lookup transformation to use a dynamic cache, you can only use the equality operator (=) in the lookup condition.
NewLookupRow Port will enable automatically.
Static cache: It is a default cache; the PowerCenter Server doesn’t update the lookup cache as it passes rows to the target.
Persistent cache: If the lookup table does not change between sessions, configure the Lookup transformation to use a persistent lookup cache. The PowerCenter Server then saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
Differences between connected lookup and unconnected lookup ?
| Connected Lookup | Unconnected Lookup |
| This is connected to pipleline and receives the input values from pipleline. | Which is not connected to pipeline and receives input values from the result of a: LKP expression in another transformation via arguments. |
| We cannot use this lookup more than once in a mapping. | We can use this transformation more than once within the mapping |
| We can return multiple columns from the same row. | Designate one return port (R), returns one column from each row. |
| We can configure to use dynamic cache. | We cannot configure to use dynamic cache. |
| 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. |
| Use a dynamic or static cache | Use a static cache |
| Supports user defined default values. | Does not support user defined default values. |
| Cache includes the lookup source column in the lookup condition and the lookup source columns that are output ports. | Cache includes all lookup/output ports in the lookup condition and the lookup/return port. |
What is Incremental Aggregation?
A. Whenever a session is created for a mapping Aggregate Transformation, the session option for Incremental Aggregation can be enabled. When PowerCenter performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.
Lookup: Lookup transformation is used in a mapping to look up data in a flat file or a relational table, view, or synonym.
Two types of lookups:
1) Connected
2) Unconnected
Informatica Basics
System Variables:
$$$SessStartTime returns the initial system date value on the machine hosting the Integration Service when the server initializes a session. $$$SessStartTime returns the session start time as a string value. The format of the string depends on the database you are using.
Session: A session is a set of instructions that tells informatica Server how to move data from sources to targets.
WorkFlow: A workflow is a set of instructions that tells Informatica Server how to execute tasks such as sessions, email notifications and commands. In a workflow multiple sessions can be included to run in parallel or sequential manner.
Source Definition: The Source Definition is used to logically represent database table or Flat files.
Target Definition: The Target Definition is used to logically represent a database table or file in the Data Warehouse / Data Mart.
Aggregator: The Aggregator transformation is used to perform Aggregate calculations on group basis.
Expression: The Expression transformation is used to perform the arithmetic calculation on row by row basis and also used to convert string to integer vis and concatenate two columns.
Filter: The Filter transformation is used to filter the data based on single condition and pass through next transformation.
Router: The router transformation is used to route the data based on multiple conditions and pass through next transformations.
It has three groups
1) Input group
2) User defined group
3) Default group
Joiner: The Joiner transformation is used to join two sources residing in different databases or different locations like flat file and oracle sources or two relational tables existing in different databases.
Source Qualifier: The Source Qualifier transformation is used to describe in SQL the method by which data is to be retrieved from a source application system and also
used to join two relational sources residing in same databases.
Informatica Transformations
Mapping: Mapping is the Informatica Object which contains set of transformations including source and target. Its look like pipeline.
Mapplet:
Mapplet is a set of reusable transformations. We can use this mapplet in any mapping within the Folder.
A mapplet can be active or passive depending on the transformations in the mapplet. Active mapplets contain one or more active transformations. Passive mapplets contain only passive transformations.
When you add transformations to a mapplet, keep the following restrictions in mind:
- If you use a Sequence Generator transformation, you must use a reusable Sequence Generator transformation.
- If you use a Stored Procedure transformation, you must configure the Stored Procedure Type to be Normal.
- You cannot include the following objects in a mapplet:
- Normalizer transformations
- COBOL sources
- XML Source Qualifier transformations
- XML sources
- Target definitions
- Other mapplets
- The mapplet contains Input transformations and/or source definitions with at least one port connected to a transformation in the mapplet.
- The mapplet contains at least one Output transformation with at least one port connected to a transformation in the mapplet.
Input Transformation: Input transformations are used to create a logical interface to a mapplet in order to allow data to pass into the mapplet.
Output Transformation: Output transformations are used to create a logical interface from a mapplet in order to allow data to pass out of a mapplet.
Informatica is a powerful Extraction, Transformation, and Loading tool and is been deployed for data warehouse development in the Business Intelligence Team. Informatica comes with the following clients to perform various tasks.
· Designer – used to develop transformations/mappings
· Workflow Manager / Workflow Monitor replace the Server Manager - used to create sessions / workflows/ worklets to run, schedule, and monitor mappings for data movement
· Repository Manager – used to maintain folders, users, permissions, locks, and repositories.
· Integration Services – the “workhorse” of the domain. Informatica Server is the component responsible for the actual work of moving data according to the mappings developed and placed into operation. It contains several distinct parts such as the Load Manager, Data Transformation Manager, Reader and Writer.
· Repository Services- Informatica client tools and Informatica Server connect to the repository database over the network through the Repository Server.
Subscribe to:
Posts (Atom)