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.