Simple things about Lookup Transformation

Simple things about Lookup Transformation

Q Define lookup transformation?

A lookup transformation is used to lookup data in a ‘data-pool’. This data-pool may be a flat-file, relational table, view or a synonym. You can also create a lookup definition from a source qualifier. The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation.

Lookups are generally used to get a related value, to perform a calculation using the derived related value or to update a slowly changing dimension. 

When you configure a flat file Lookup transformation for sorted input, the condition columns must be grouped. If the condition columns are not grouped, the Integration Service cannot cache the lookup and fails the session. For optimal caching performance, sort the condition columns. The Integration Service always caches flat file and pipeline lookups. If you configure a Lookup transformation to use a dynamic cache, you can use only the equality operator (=) in the lookup condition.

Q What are the differences between connected and unconnected lookups?

1. Connected Lokkup uses a dynamic or static cache while unconnected lookup uses only static cache.

2. Connected lookup can return multiple columns from the same row or insert into the dynamic lookup cache while unconnected lookup returns one column from each row.

3. Connected lookup supports user-defined default values while unconnected lookup does not supports user-defined default values.

Q How can you return multiple ports from an unconnected lookup transformation?

Unconnected lookup transformation returns only 1 port. To return multiple ports, concatenate all those ports in the overwritten lookup query and return the concatenated port. Now separate out those columns in an expression transformation.

Q How can you optimize a lookup transformation?

1. If you have privileges to modify the database containing a lookup table, you can improve lookup initialization time by adding an index to the lookup table.

2. You can improve performance by indexing the columns in the lookup ORDER BY.

3. By default, the Integration Service generates an ORDER BY clause for a cached lookup. The ORDER BY clause contains all lookup ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns. Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.

4. If you include more than one lookup condition, place the conditions in the following order to optimize lookup performance:

- Equal to (=)

- Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)

- Not equal to (!=)

5. Improve session performance by caching small lookup tables.

6. If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

Informatica Basics

Informatica Basics and Interview FAQs

Q1 What is Informatica Powercenter?

 

Ans Powercenter is a data integration software of Informatica Corporation which provides an environment that allows to load data into a centralized location such as data warehouse. Data can be extracted from multiple sources , can be transformed according to the business logic and can be loaded into files and relation targets. It has following components:
PowerCentre Domain
PowerCenter Repositiory
Administration Console
PowerCenter Client
Repository Service
Integration service
Web Services Hub
Data Analyzer
Metadata Manager
PowerCenter Repository Reports

Q2 What is Data Integration?

 

Ans Data Integration is the process of combining data residing at different sources and providing the user with a unified view of these data.

Q3 Explain PowerCenter Repository?

 

Ans Repository consist of database tables that store metadata. Metadata describes different types of objects , such as mappings or transformations , that you can create using PowerCenter Client tools. The interation service uses repository objects to extract , transform and load data. The repository also stores administrative information such as user names, passwords , permissions and previleges. When any task is performed through PowerCenter Client application such as creating users, analyzing sources , developing mapping or mapplets or creating workflows , Metadata is added to repository tables.

.

Q4. What is a Mapping?

Ans A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.

.

Q5. What is a mapplet?

Ans A mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.

.

Q6. What is Transformation?

Ans Transformation is a repository object that generates,modifies or passes data.Transformations in a mapping represent the operations the Integration Service performs on the data. Data passes through transformation ports that are linked in a mapping or mapplet.

Q7. What are Mapping Parameters and Variables? Whats the difference between them?

Mapping parameters and variables are used to make mappings more flexible.

A mapping parameter represents a constant value that can be defined before running a session. It retains the same value throughout the session. Using a parameter file, this value can be changed for subsequent sessions.

A mapping variable represents a value that can change through sessions. The Integration Service saves the value of a mapping variable to the repository at the end of each successful run and uses that value in the next run of the session.

Q8 What happens when you do not group values in an aggregator transformation?

When the values are not grouped in aggregator transformation, Integration service returns 1 row for all input rows. It typically returns the last row of each group (or the last row recieved) with the result of the aggregation. However, if you specify a particular row to be returned (e.g through FIRST function), then that row is returned.

Q9 How does using sorted input improves the performance of Aggregator Transformation?

When sorted input is used, the Integration Service assumes that all data is sorted by group and it performs aggregate calculations as it reads rows for a group. It doesnot wait for the whole data and hence this reduces the amont of data cached during the session and improves session performance. While when unsorted input is used, Integration service waits for the whole data and only then performs aggregation.

Sorted Input should not be used when either of the following conditions are true:

•  The aggregate expression uses nested aggregate functions
•  The session uses incremental aggregation

Q10 How does a join in Joiner transformation different from normal SQL join?

The joiner transformation join can be done on hetrogeneous sources but SQL join can be done only on tables.

Q11 What are the criteria for deciding Master and Detail sources for a joiner transformation?

• The master pipeline ends at the joiner transformation while the detail pipeline continues to the target. So, accordingly the sources should be decided as master or detail
• For optimal performance, if an unsorted joiner transformation is used, then designate the source with fewer rows as the master source. During a session run, the joiner transformation compares each row of the master source against the detail source.
• For a sorted joiner transformation, designate the source with fewer duplicate key values as master.

Processing of incremental aggregation

Processing of incremental aggregation

The first time u run an incremental aggregation session the power center server process the entire source.At the end of the session the power center server stores aggregate data from the session runs in two files, the index file and the data file .The power center server creates the files in a local directory.