What is the exact difference between joiner and lookup transformation:-
A joiner is used to join data from different sources and a lookup is used to get a related values from another table or check for updates etc in the target table.
for lookup to work the table may not exist in the mapping but for a joiner to work, the table has to exist in the mapping.
A lookup may be unconnected while a joiner may not
lookup may not participate in mapping
lookup does only non equi join
joiner table must paraticipate in mapping
joiner does only outer join
Joiner :
> It support equiv Join only.
> It may be we can perform outer join only.
> Joiner is used to source only.
> It may be only "=" operator used.
> In joiner may be not present in lookup override.
Lookup :
> It supports Equiv and non equiv join.
> Lookup used to source as well as target.
> It can not perform outer join in lookup.
> It may be = , < , > , <= . >= are used.
> It may be present in lookup override option.
Look up transformation :
a) Look up transformation can be used on a single table or a query to search for records that match incoming records. Matching condition can be specified in the lookup transformation. The result returned can be multiple columns or single column.
b) Lookup transformation can be unconnected or connected transformation. Unconnected transformation can return only single value.
c) Lookup transformation can be static or active. Dynamic lookups are active transformation.
d) Lookup transformation be used with more than one relational operator such as > , =, etc.
b) The join can be left outer join, inner join, right outer join, etc.
c) The joiner returns all the results that match the join condition.
d) The master records in the joiner transformation is cached. The detail records are not cached. Hence, joiner transformation is active transformation.
a) If the table size is not too large then preferable to use lookup.
b) If result from a single matching records needs to be returned then use a lookup. If there is a query that needs to be used in a lookup to find the result for lookup then good to use a lookup.
c) If you are doing lookup on a table that is updated in the session then use a lookup. Joiners are active so not preferable.
d) If look up table data does not change then the table can be made persistent and used in the lookup which gives even better performance.
e) If data from two different source qualifiers need to be joined then use a joiner.
f) If data from two different databases are read and need to be joined with a outer or inner join then use joiner transformation.
A joiner is used to join data from different sources and a lookup is used to get a related values from another table or check for updates etc in the target table.
for lookup to work the table may not exist in the mapping but for a joiner to work, the table has to exist in the mapping.
A lookup may be unconnected while a joiner may not
lookup may not participate in mapping
lookup does only non equi join
joiner table must paraticipate in mapping
joiner does only outer join
Joiner :
> It support equiv Join only.
> It may be we can perform outer join only.
> Joiner is used to source only.
> It may be only "=" operator used.
> In joiner may be not present in lookup override.
Lookup :
> It supports Equiv and non equiv join.
> Lookup used to source as well as target.
> It can not perform outer join in lookup.
> It may be = , < , > , <= . >= are used.
> It may be present in lookup override option.
Difference between Lookup and joiner transformation in Informatica
Look up transformation :
a) Look up transformation can be used on a single table or a query to search for records that match incoming records. Matching condition can be specified in the lookup transformation. The result returned can be multiple columns or single column.
b) Lookup transformation can be unconnected or connected transformation. Unconnected transformation can return only single value.
c) Lookup transformation can be static or active. Dynamic lookups are active transformation.
d) Lookup transformation be used with more than one relational operator such as > , =, etc.
Joiner transformation :
a) Joiner transformation is used to usually to join data coming from two separate tables or source qualifiers.b) The join can be left outer join, inner join, right outer join, etc.
c) The joiner returns all the results that match the join condition.
d) The master records in the joiner transformation is cached. The detail records are not cached. Hence, joiner transformation is active transformation.
When do you use joiner or lookup transformation?
a) If the table size is not too large then preferable to use lookup.
b) If result from a single matching records needs to be returned then use a lookup. If there is a query that needs to be used in a lookup to find the result for lookup then good to use a lookup.
c) If you are doing lookup on a table that is updated in the session then use a lookup. Joiners are active so not preferable.
d) If look up table data does not change then the table can be made persistent and used in the lookup which gives even better performance.
e) If data from two different source qualifiers need to be joined then use a joiner.
f) If data from two different databases are read and need to be joined with a outer or inner join then use joiner transformation.
No comments:
Post a Comment