Friday, November 14, 2008

Informatica Interview Questions And Answers I

How to use the unconnected lookup i.e., from where the input has to be taken and the output is linked?
What condition is to be given?
The unconnected lookup is used just like a function call. in an expression output/variable port or any place where an expression is accepted(like condition in update strategy etc..), call the unconnected lookup...something like :LKP.lkp_abc(input_port).......(lkp_abc is the name of the unconnected lookup...(plz check the exact syntax)).....give the input value just like we pass parameters to functions, and it'll return the output after looking up.

What r the circumstances that infromatica server results an unreciverable session?
The source qualifier transformation does not use sorted ports.
If u change the partition information after the initial session fails.
Perform recovery is disabled in the informatica server configuration.
If the sources or targets changes after initial session fails.
If the maping consists of sequence generator or normalizer transformation.
If a concuurent batche contains multiple failed sessions.

What is power center repository?
The PowerCenter repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, yoYou can create a single global
repository to store metadata used across an enterprise, and a number of local repositories to share the global metadata as needed.
Two relational tables are connected to SQ Trans,what are the possible errors it will be thrown?
The only two possibilities as of I know is
Both the table should have primary key/foreign key relation ship
Both the table should be available in the same schema or same database

Which transformation should we use to normalize the COBOL and relational sources?
The Normalizer transformation normalizes records from COBOL and relational sources, allowing you to organize the data according to your own needs. A Normalizer transformation can appear anywhere in a data flow when you normalize a relational source. Use a Normalizer transformation instead of the Source Qualifier transformation when you normalize a COBOL source. When you drag a COBOL source into the Mapping Designer workspace, the Normalizer transformation automatically appears, creating input and output ports for every column in the source

What are the Differences between Informatica Power Center versions 6.2 and 7.1, also between Versions 6.2 and 5.1?
The main difference between informatica 5.1 and 6.1 is that in 6.1 they introduce a new thing called repository server and in place of server manager(5.1), they introduce workflow manager and workflow monitor.

What is Load Manager?
The load Manager is the Primary Informatica Server Process. It performs the following tasks:-
Manages session and batch scheduling.
Locks the session and read session properties.
Reads the parameter file.
Expand the server and session variables and parameters.
Verify permissions and privileges.
Validate source and target code pages.
Create the session log file.
Create the Data Transformation Manager which execute the session.

What is the default join that source qualifier provides?
The Joiner transformation supports the following join types, which you set in the Properties tab:
Normal (Default)
Master Outer
Detail Outer
Full Outer

How can we partition a session in Informatica?
he Informatica® PowerCenter® Partitioning option optimizes parallel processing on multiprocessor hardware by providing a thread-based architecture and built-in data partitioning.
GUI-based tools reduce the development effort necessary to create data partitions and streamline ongoing troubleshooting and performance tuning tasks, while ensuring data integrity throughout the execution process. As the amount of data within an organization expands and real-time demand for information grows, the PowerCenter Partitioning option
enables hardware and applications to provide outstanding performance and jointly scale to handle large volumes of data and users.

What is Datadriven?
The Informatica Server follows instructions coded into Update Strategy transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject.
If the mapping for the session contains an Update Strategy transformation, this field is marked Data Driven by default.

What is meant by lookup caches?
The informatica server builds a cache in memory when it processes the first row af a data in a cached look up transformation.It allocates memory for the cache based on the
amount you configure in the transformation or session properties.The informatica server stores condition values in the index cache and output values in the data cache.

Define Informatica Repository?
The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information
such as mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings
for sources and targets. The repository also stores administrative information such as usernames and passwords, permissions and privileges, and product version. Use repository manager to create the repository.The Repository Manager connects to the repository database and runs the code needed to create the repository tables.Thsea tables stores metadata in specific format the informatica server,client tools use.

Performance tuning in Informatica?
The goal of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server.Increase the session
performance by following.

The performance of the Informatica Server is related to network connections. Data generally moves across a network at less than 1 MB per second, whereas a local disk
moves data five to twenty times faster. Thus network connections ofteny affect on session performance.So aviod netwrok connections. Flat files: If u’r flat files stored on a machine other than the informatca server, move those files to the machine that consists of informatica server.
Relational datasources: Minimize the connections to sources ,targets and informatica server to improve session performance.Moving target database into server system may improve session performance. Staging areas: If u use staging areas u force informatica server to perform multiple datapasses. Removing of staging areas may improve session performance.
You can run the multiple informatica servers againist the same repository.Distibuting the session load to multiple informatica servers may improve session performance.
Run the informatica server in ASCII datamovement mode improves the session performance.Because ASCII datamovement mode stores a character value in one
byte.Unicode mode takes 2 bytes to store a character.
If a session joins multiple source tables in one Source Qualifier, optimizing the query may improve performance. Also, single table select statements with an ORDER BY or
GROUP BY clause may benefit from optimization such as adding indexes.
We can improve the session performance by configuring the network packet size,which allows data to cross the network at one time.To do this go to server manger ,choose server configure database connections. If u are target consists key constraints and indexes u slow the loading of data.To improve the session performance in this case drop constraints and indexes before u run the session and rebuild them after completion of session. Running a parallel sessions by using concurrent batches will also reduce the time of loading the data.So concurent batches may also increase the session performance.
Partittionig the session improves the session performance by creating multiple connections to sources and targets and loads data in paralel pipe lines. In some cases if a session contains a aggregator transformation ,You can use incremental aggregation to improve session performance. Aviod transformation errors to improve the session performance. If the sessioin containd lookup transformation You can improve the session performance by enabling the look up cache. If U’r session contains filter transformation ,create that filter transformation nearer to the sources or You can use filter condition in source qualifier. Aggreagator,Rank and joiner transformation may oftenly decrease the session performance .Because they must group data before processing it.To improve session performance in this case use sorted ports option.

What is the procedure to write the query to list the highest salary of three employees?
The following is the query to find out the top three salaries
in ORACLE:--(take emp table)
select * from emp e where 3>(select count (*) from emp where
e.sal>emp.sal) order by sal desc.
in SQL Server:-(take emp table)
select top 10 sal from emp

Can i run the mapping with out starting the informatica server?
The difference between cache and uncacheed lookup iswhen you configure the lookup transformation cache lookup it stores all the lookup table data in the cache when the first input record enter into the lookup transformation, in cache lookup the select statement executes only once and compares the values of the input record with the values in the cachebut in uncache lookup the the select statement executes for each input record entering into the lookup transformation and it has to connect to database each time entering the new record

No comments: