Top 20 Informatica Interview Questions with Answers-Part-5

1]    HOW CAN YOU STOP A BATCH?
Answer: By using server manager or pmcmd.

2]    WHAT IS A COMMAND THAT USED TO RUN A BATCH?
Answer: pmcmd is used to start a batch.

3]    WHAT IS DIMENSION TABLE EXACTLY?
Answer: Dimension tables gives description about something. for eg. If we take Student as a dimention table, we have various attributes like college name, age, gender,etc which gives some description about it.
4]    WHEN THE INFORMATICA SERVER MARKS THAT A BATCH IS FAILED?
Answer: If one of session is configured to "run if previous completes" and that previous session fails.
5]  What is Batch in Informatica & types?
  Ans:  Batches provide a way to group sessions for either serial or parallel execution by the
     Informatica  Server. There are two types of batches:
   a) Sequential: Runs sessions one after the other.
    b) Concurrent: Runs sessions at the same time.
 You might create a sequential batch if you have sessions with source-target dependencies that you   want to run in a specific order. You might also create a concurrent batch if you have several independent sessions you need scheduled at the same time. You can place them all in one batch, then schedule the batch as needed instead of scheduling each individual session.
You can create, edit, start, schedule, and stop batches with the Server Manager. However, you cannot copy or abort batches. With pmcmd, you can start and stop batches.

6]    HOW DOES THE SERVER RECOGNISE THE SOURCE AND TARGET DATABASES?
Answer: By using ODBC connection.if it is relational.if is flat file FTP connection..see we can make sure with connection in the properties of session both sources && targets.
7]    WHAT IS THE LIMIT TO THE NUMBER OF SOURCES AND TARGETS YOU CAN HAVE IN A MAPPING
Answer: There is no such restriction to use this number of sources or targets inside a mapping.

8] Briefly explain the Aggregator transformation?
Ans: It allows one to do aggregate calculations such as sums, averages etc. It is unlike expression transformation in which  one can do calculations in groups.

9] Describe Expression transformation?
Ans: Values can be calculated in single row before writing on the target in this form of transformation. It can be used to perform non aggregate calculations. Conditional statements can also be tested before output results go to target tables.

10]  What do you mean by filter transformation?
Ans: It is a medium of filtering rows in a mapping. Data needs to be transformed through filter transformation and then filter condition is applied.  Filter transformation contains all ports of input/output, and the rows which meet  the condition can only pass through that filter.

11] What is Joiner transformation?
Ans: Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.
12] What is Lookup transformation?
 Ans: It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.

13]  What are the types of metadata that stores in repository?
Ans: The types of metadata includes Source definition, Target definition, Mappings, Mapplet, Transformations.

14]  What the difference is between a database, a data warehouse and a data mart?
Ans: Database includes a set of sensibly affiliated data which is normally small in size as compared to data warehouse. While in data warehouse there are assortments of all sorts of data and data is taken out only according to the customer’s needs. On the other hand datamart is also a set of data which is designed to  cater the needs of different domains. For instance an organization having different chunk of data for its different departments i.e. sales, finance, marketing etc.

15]  What is meant by a domain?
Ans: When all related relationships and nodes are covered by a sole organizational point,  its called domain. Through this data management can be improved.

16]  What is the difference between a repository server and a powerhouse?
Ans: Repository  server controls the complete repository which includes tables, charts, and various procedures etc. Its main function is to assure the repository  integrity and consistency. While a powerhouse server governs the implementation of various processes among the factors of server’s database repository.

17]  How can one identify whether mapping is correct or not without connecting session?
Ans: One can find whether the session is correct or not without connecting the session is with the help of debugging option.

Top 20 Informatica Interview Questions with Answers-Part-4

1]    CAN U TELL ME HOW TO GO FOR SCD'S AND ITS TYPES.WHERE DO WE USE THEM MOSTLY
Answer: The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time.

2]    WHAT IS MEANT BY EDW?
Answer: EDW is Enterprise Datawarehouse which means that its a centralised DW for the whole organization. This apporach is the apporach on Imon which relies on the point of having a single warehouse/centra..

3]    IF YOU WANT TO CREATE INDEXES AFTER THE LOAD PROCESS WHICH TRANSFORMATION YOU CHOOSE?
Answer: Its usually not done in the mapping(transformation) level. Its done in session level. Create a command task which will execute a shell script (if Unix) or any other scripts which contains the create index.

4]    WHAT IS WORKLET AND WHAT USE OF WORKLET AND IN WHICH SITUATION WE CAN USE IT
Answer: A set of worlflow tasks is called worklet, Workflow tasks means 1)timer 2)decision 3)command 4)eventwait 5)eventrise 6)mail etc...... But we r use diffrent situations by using this only.

5]    CAN YOU COPY THE BATCHES?
Answer: NO.

6]    WHAT ARE COST BASED AND RULE BASED APPROACHES AND THE DIFFERENCE
Answer: Cost based and rule based approaches are the optimization techniques which are used in related to databases, where we need to optimize a sql query. Basically Oracle provides Two types of Optimizer .

7]    HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINTION AND WHAT R THEY?
Answer: Two ways 1. Edit the definition 2. Reimport the definition .
8]    HOW MANY WAYS YOU CREATE PORTS?
Answer: Two ways:- 1.Drag the port from another transforamtion 2.Click the add buttion on the ports tab.

9]    HOW MANY WAYS YOU CAN UPDATE A RELATIONAL SOURCE DEFINITION AND WHAT ARE THEY?
Answer: joinercondition exciuted with in the infoematica staging layer source qualifier condition exciuted with in the database level.

10]    WHAT IS A VIEW? HOW IT IS RELATED TO DATA INDEPENDENCE? AND WHAT ARE THE DIFFERENT TYPES OF VIEWS, AND WHAT IS MATERIALIZE VIEW
Answer: views view is a combination of one or more table.view does not stores the data,it just store the query in file format.If we excutes the query the query will fetch the data from the tables.

11]    WHAT IS MEANT BY JUNK ATTRIBUTE IN INFORMATICA?
Answer: Junk Dimension A Dimension is called junk dimension if it contains attribute which are rarely changed ormodified. example In Banking Domain , we can fetch four attributes accounting to a junk dimension.

12]    WHAT IS THE BEST WAY TO SHOW METADATA (NUMBER OF ROWS AT SOURCE, TARGET AND EACH TRANSFORMATION LEVEL, ERROR RELATED DATA) IN A REPORT FORMAT?
Answer: You can select these details from the repository table. you can use the view REP_SESS_LOG to get these data.

13]    HOW TO JOIN TWO TABLES WITHOUT USING THE JOINER TRANSFORMATION.
Answer: It’s possible to join the two or more tables by using source qualifier.But provided the tables should have relationship. When u drag n drop the tables u will getting the source qualifier for each transformation.

14]    HOW CAN WE ELIMINATE DUPLICATE ROWS FROM FLAT FILE?
Answer: Use Sorter Transformation. When you configure the Sorter Transformation to treat output rows as distinct, it configures all ports as part of the sort key.

15]    HOW TO GET TWO TARGETS T1 CONTAINING DISTINCT VALUES AND T2 CONTAINING DUPLICATE VALUES FROM ONE SOURCE S1.
Answer: Use filter transformation for loading the target with no duplicates. and for the other transformation load it directly from source.

16]    WHAT IS THE DIFF B/W STOP & ABORT IN INFORMATICA SESS LEVEL ?
Answer: Stop:We can Restart the session Abort:WE cant restart the session.We should truncate all the pipeline after that start the session.

17]    CAN YOU GENERATE REPORTS IN INFORMATCIA?
Answer: Yes. By using Metadata reporter we can generate reports in informatica.

18]    HOW DO WE ANALYSE THE DATA AT DATABASE LEVEL?
Answer: Data can be viewed using Informatica's designer tool. If you want to view the data on source/target we can preview the data but with some limitations. We can use data profiling too.

19]    HOW TO RETRIEVE THE RECORDS FROM A REJECTED FILE. EXPLAIN WITH SYNTAX OR EXAMPLE
Answer: During the execution of workflow all the rejected rows will be stored in bad files(where your informatica server get installed;C:Program FilesInformatica PowerCenter 7.1Server) These bad files can be..

20]    WHAT IS MEANT BY COMPLEX MAPPING?
Answer: Complex maping means involved in more logic and more business rules. Actually in my project complex mapping is In my bank project, I involved in construct a 1 dataware house Meny .

Top 20 Informatica Interview Questions with Answers-Part-3

1]    HOW TO EXPORT MAPPINGS TO THE PRODUCTION ENVIRONMENT?
Answer: In the designer go to the main menu and one can see the export/import options. Import the exported mapping in to the production repository with replace options.

2]    HOW TO GENERATE THE METADATA REPORTS IN INFORMATICA?
Answer: You can generate PowerCenter Metadata Reporter from a browser on any workstation, even a workstation that does not have PowerCenter tools installed.

3]    IN MY SOURCE TABLE 1000 REC'S R THERE.I WANT TO LOAD 501 REC TO 1000 REC INTO MY TARGET TABLE ? HOW CAN U DO THIS ?
Answer: You can overide the sql Query in Wofkflow Manager. LIke select * from tab_name where rownum<=1000 minus select * from tab_name where rownum<=500; This will work fine. Try it and get back .

4]    CAN U GENERATE REPORTS IN INFORMATCIA?
Answer: It is a ETL tool, you could not make reports from here, but you can generate metadata report, that is not going to be used for business analysis.

5]    IF I DONE ANY MODIFICATIONS FOR MY TABLE IN BACK END DOES IT REFLECT IN INFORMATCA WAREHOUSE OR MAPING DESGINER OR SOURCE ANALYZER?
Answer: NO. Informatica is not at all concern with back end data base.It displays u all the information that is to be stored in repository.If want to reflect back end changes to informatica screens.

6]    HOW TO RECOVER THE STANDALONE SESSION?
Answer: A standalone session is a session that is not nested in a batch. If a standalone session fails, you can run recovery using a menu command or pmcmd. These options are not available for batched session.

7]    CAN U COPY THE SESSION TO A DIFFERENT FOLDER OR REPOSITORY?
Answer: In addition, you can copy the workflow from the Repository manager. This will automatically copy the mapping, associated source,targets and session to the target folder.

8]    WHAT IS DATA CLEANSING..?
Answer: Data cleansing is a two-step process including DETECTION and then CORRECTION of errors in a data set.

9]    WHAT ARE THE ACTIVE AND PASSIVE TRANSFORAMTIONS?
Answer: An active transforamtion can change the number of rows that pass through it.A passive transformation does not change the number of rows that pass through it.

10]    THE DESIGNER INCLUDES A "FIND" SEARCH TOOL AS PART OF THE STANDARD TOOL BAR. WHAT CAN IT BE USED TO FIND?
Answer: This is used for finding source columns in workspace.

11]    CAN U USE THE MAPING PARAMETERS OR VARIABLES CREATED IN ONE MAPING INTO ANOTHER MAPING?
Answer: NO. You might want to use a workflow parameter/variable if you want it to be visible with other mappings/sessions.

12]    WHERE SHOULD YOU PLACE THE FLAT FILE TO IMPORT THE FLAT FILE DEFINITION TO THE DESIGNER?
Answer: Place it in local folder.

13]    IF YOU ARE WORKFLOW IS RUNNING SLOW IN INFORMATICA. WHERE DO YOU START TROUBLE SHOOTING AND WHAT ARE THE STEPS YOU FOLLOW?
Answer: When the work flow is running slowly u have to find out the bottlenecks in this order target source mapping session system.

14]    WHAT DOES THE EXPRESSION N FILTER TRANSFORMATIONS DO IN INFORMATICA SLOWLY GROWING TARGET WIZARD?
Answer: EXPESSION transformation detects and flags the rows from source. Filter transformation filters the rows that are not flagged and passes the flagged rows to the Update strategy transformation.

15]    HOW CAN WE JOIN THE TABLES IF THE TABLES HAVE NO PRIMARY AND FORIEN KEY RELATION AND NO MATCHIG PORT TO JOIN?
Answer: without common column or common data type we can join two sources using dummy ports. 1.Add one dummy port in two sources. 2.In the expression trans assing '1' to each port. 2.Use Joiner transformation.

16]    WHAT IS POLLING?
Answer: It displays the updated information about the session in the monitor window. The monitor window displays the status of each session when you poll the informatica server.

17]    WHY YOU USE REPOSITORY CONNECTIVITY?
Answer: When you edit,schedule the sesion each time,informatica server directly communicates the repository to check whether or not the session and users are valid.All the metadata of sessions and mappings.

18]    WHAT IS CODE PAGE USED FOR?
Answer: Code Page is used to identify characters that might be in different languages. If you are importing Japanese data into mapping, you must select the Japanese code page of source data.

19]    WHAT ARE VARIOUS TYPES OF AGGREGATION?
Answer: Various types of aggregation are SUM, AVG, COUNT, MAX, MIN, FIRST, LAST, MEDIAN, PERCENTILE, STDDEV, and VARIANCE.

20]    WHAT IS CHANGE DATA CAPTURE?
Answer: Change data capture (CDC) is a set of software design patterns used to determine the data that has changed in a database so that action can be taken using the changed data.

Top 20 Informatica Interview Questions with Answers-Part-2

1]     HOW DO U CHECK THE SOURCE FOR THE LATEST RECORDS THAT ARE TO BE LOADED INTO THE TARGET. I.E. I HAVE LOADED SOME RECORDS YESTERDAY, TODAY AGAIN THE FILE HAS BEEN POPULATED WITH SOME MORE RECORDS TODAY, SO HOW DO I FIND THE RECORDS POPULATED TODAY.
Answer: a) Create a lookup to target table from Source Qualifier based on primary Key. b) Use and expression to evaluate primary key from target look-up. ( If a new source record look-up primary key port f  .

2]     HOW U WILL CREATE HEADER AND FOOTER IN TARGET USING INFORMATICA?
Answer: If you are focus is about the flat files then one can set it in file properties while creating a mapping or at the session level in session properties .

3]     HOW CAN YOU SAY THAT UNION TRANSFORMATION IS ACTIVE TRANSFORMATION?
Answer: By Definition, Active transformation is the transformation that changes the number of rows that pass through it...in union transformation the number of rows resulting from union can be (are) different.

4]     HOW CAN I TRANSFORM ROW TO COLUMN?
Answer: Through Normalizer Transformation we can do this.

5]     WHAT IS TRANSACTION?
Answer: Transaction is a logical unit of work that comprises one or more sql statements executed by a single user .

6]     WHAT IS THE DIFFERENCE BETWEEN NORMAL LOAD AND BULK LOAD?
Answer: Normal Load: Normal load will write information to the database log file so that if any recorvery is needed it is will be helpful. when the source file is a text file and loading data to a table,in su..

7]    WHAT IS THE STATUS CODE?
Answer: Status code provides error handling for the informatica server during the session. The stored procedure issues a status code that notifies whether or not stored procedure completed sucessfully.

8]    WHAT IS THE RANK INDEX IN RANK TRANSFORMATION?
Answer: The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group.

9]    WHAT IS DATADRIVEN?
Answer: 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.

10]    TO PROVIDE SUPPORT FOR MAINFRAMES SOURCE DATA, WHICH FILES ARE USED AS A SOURCE DEFINITIONS?
Answer: COBOL files.

11]    CAN INFORMATICA LOAD HETEROGENEOUS TARGETS FROM HETEROGENEOUS SOURCES?
Answer: Yes it can. For example...Flat File and Relations sources are joined in the mapping, and later, Flat File and relational targets are loaded.

12]    CAN WE ELIMINATE DUPLICATE ROWS BY USING FILTER AND ROUTER TRANSFORMATION? IF SO EXPLAIN ME IN DETAIL.
Answer: U can use SQL query for uniqness if the source is Relational But if the source is Flat file then u should use Shorter or Aggregatot transformation.

13]    WHAT IS PARAMETER FILE?
Answer: Parameter file is to define the values for parameters and variables used in a session. A parameter file is a file created by text editor such as word pad or notepad.

14]    HOW DO YOU HANDLE DECIMAL PLACES WHILE IMPORTING A FLATFILE INTO INFORMATICA?
Answer: While importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer).

15]    WHAT IS METADATA REPORTER?
Answer: It is a web based application that enables you to run reports against repository metadata. With a meta data reporter, You can access information about u r repository with out having knowledge of sql.

16]    WHAT IS CODE PAGE COMPATIBILITY?
Answer: Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data.


17]    WHY WE USE PARTITIONING THE SESSION IN INFORMATICA?
Answer: Performance can be improved by processing data in parallel in a single session by creating multiple partitions of the pipeline. Informatica server can achieve high performance by partitioning the pipeline.

18]    HOW DO YOU LOAD THE TIME DIMENSION?
Answer: Time Dimension will generally load manually by using PL/SQL , shell scripts, proc C etc......

19]    HOW CAN WE STORE PREVIOUS SESSION LOGS
Answer: Just run the session in time stamp mode then automatically session log will not overwrite current session log.

20]    HOW CAN WE JOIN 3 DATABASE LIKE FLAT FILE, ORACLE, DB2 IN INFORMATRICA..
Answer: You have to use two joiner transformations.fIRST one will join two tables and the next one will join the third with the resultant of the first joiner.

Top 20 Informatica Interview Questions with Answers:Part-1

1]       HOW CAN YOU RECOGNIZE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE ARE GETS INSERT IN THE TARGET?
Answer: You just have to check with sql's or you can check by making the changes in mappings in sessions for target and also depends on the mapping which you are goiing to use the transformations.

2]       WHAT IS THE DIFFERENCE BETWEEN INFORMATICA 7.0 AND 8.0?
Answer: The basic diff b/w in between informatica 8.0 and informatica7.0 is that in 8.0series informatica corp has introduces powerexchnage concept.

3]       PERFORMANCE TUNING IN INFORMATICA?
Answer: 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 performa.


4]       WHAT IS THE TARGET LOAD ORDER?
Answer: You specify the target loadorder based on source qualifiers in a maping.If you have the multiple source qualifiers connected to the multiple targets,You can designatethe order in which informatica ser.

5]       DIFF BETWEEN STATIC AND DYNAMIC CACHE? AND PLEASE EXPLAIN WITH ONE EXAMPLE?
Answer: Difference between static and dynamic cache- Static- Once the data is cached , it will not change. example unconnected lookup uses static cache. Dynamic- The cache is updated as to reflect the upda.

6]       HOW DO WE DO UNIT TESTING IN INFORMATICA? HOW DO WE LOAD DATA IN INFORMATICA?
Answer: Unit testing are of two types 1. Quantitaive testing 2.Qualitative testing Steps. 1.First validate the mapping 2.Create session on themapping and then run workflow. Once the session.

7]     IS SORTER AN ACTIVE OR PASSIVE TRANSFORMATION? WHAT HAPPENS IF WE UNCHECK THE DISTINCT OPTION IN SORTER? WILL IT BE UNDER ACTIVE OR PASSIVE TRANSFORMATION?
Answer: Sorter is an active transformation. if you don't check the distinct option it is considered as a passive transformation. becos this distinct option eliminates the duplicate records from the table.

8]     WHAT IS THE DIFFERENCE BETWEEN STOP AND ABORT?
Answer: stop: _______If the session u want to stop is a part of batch you must stop the batch, if the batch is part of nested batch, Stop the outer most batch Abort:---- You can issue the abort comma.

9]     HOW MANY TYPES OF DIMENSIONS ARE AVAILABLE IN INFORMATICA?
Answer: There r 3 types of dimensions
 1.star schema
2.snowflake schema
3.glaxy schema

10]     WHAT IS SOURCE QUALIFIER TRANSFORMATION?
Answer: SQ transformation is a transformation which is automatically generated to read data from source tables into informatica designer.

11]     CAN ANYONE EXPLAIN ERROR HANDLING IN INFORMATICA WITH EXAMPLES SO THAT IT WILL BE EASY TO EXPLAIN THE SAME IN THE INTERVIEW?
Answer: Go to the session log file there we will find the information regarding to the session initiation process, errors encountered. load summary. so by seeing the errors encountered during the.

12]     DIFFERENCE BETWEEN RANK AND DENSE RANK?
Answer: Rank: 1 2<--2nd position 2<--3rd position 4 5 Same Rank is assigned to same totals/numbers. Rank is followed by the Position. Golf game ususally Ranks this way. This is usually a Gold Ranking.

13]     WHAT IS THE DIFFERENCE BETWEEN CONNECTED AND UNCONNECTED STORED PROCEDURES?
Answer: Unconnected: The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another.

14]     WHAT ARE MAIN ADVANTAGES AND PURPOSE OF USING NORMALIZER TRANSFORMATION IN INFORMATICA?
Answer: Narmalizer Transformation is used mainly with COBOL sources where most of the time data is stored in de-normalized format. Also, Normalizer transformation can be used to create multiple rows from a si.


15]     WHEN DO U WE USE DYNAMIC CACHE AND WHEN DO WE USE STATIC CACHE IN AN CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION
Answer: We use dynamic cache only for connected lookup. We use dynamic cache to check whether the record already exists in the target table are not. And depending on that, we insert,update or delete the recor
16]     ON A DAY, I LOAD 10 ROWS IN MY TARGET AND ON NEXT DAY IF I GET 10 MORE ROWS TO BE ADDED TO MY TARGET OUT OF WHICH 5 ARE UPDATED ROWS HOW CAN I SEND THEM TO TARGET? HOW CAN I INSERT AND UPDATE THE RECORD?
Answer: We can use do this by identifying the granularity of the target table . We can use CRC external procedure after that to compare newly generated CRC no. with the old one and if they do not match then.


17]     IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET. HOW CAN U LOAD THE RECORDS FROM 10001 THE RECORD WHEN U RUN THE SESSION NEXT TIME?
Answer: As explained above informatcia server has 3 methods to recovering the sessions.Use performing recovery to load the records from where the session fails.

18]     WHY DID YOU USE STORED PROCEDURE IN YOUR ETL APPLICATION?
Answer: Usage of stored procedure has the following advantages 1 checks the status of the target database 2 drops and recreates indexes 3 determines if enough space exists in the database 4 perfor.

19]     WHAT ARE THE BASIC NEEDS TO JOIN TWO SOURCES IN A SOURCE QUALIFIER?
Answer: Basic need to join two sources using source qualifier: 1) Both sources should be in same database 2) The should have at least one column in common with same data types.

20]     HOW TO MOVE THE MAPPING FROM ONE DATABASE TO ANOTHER?

Answer: 1. Open the mapping you want to migrate. Go to File Menu - Select 'Export Objects' and give a name - an XML file will be generated. Connect to the repository where you want to migrate and then select.

explain index cache and data cache?

The informatica server stores conditions values in the index cache and output values in the data cache.

what is look up transformation ?

look up transformation can be used in a table view based on condition by default lookup is left outer join

65)  why use the lookup transformation?

To perform the following tasks.

Get a related value.For example if your table includes employee ID,but you want to include such as gross sales per invoice or sales tax but not the calculated value(such as net sales)

Update slowly changing dimension tables. You can use a lookup transformation to determine whether records already exist in the target.

66)  what are the types of lookup?

Connected and unconnected