Following are the list of some Informatica Interview Questions and their answers.
Q1. In warehouses how many schemas are there?
There are four Schema’s They are:
- Star Schema
- Snow Flake Schema
- Galaxy Schema
- Fact Constellation Schema
Q2.Could you explain what is Enterprise data warehouse?
In enterprise data warehousing all organization data is created at a single point of access .data can be provided a global view to the server through a single source storage.we can do periodic analysis on the same source.it takes time but it gives better results.
Q3.What is different between the data warehouse and data mart?
A Data Mart Is a simple form of a data warehouse that is focused on a single subject or functional areas such as sales or finance or marketing.
A Data warehouse is a collection of multiple functional areas.it is the central unit which is made by combining all the data marts
Q4.How to Delete duplicate row using Informatica?
We can delete the duplicate rows in two ways which are coming from the source. They are
- Using Source Qualifier, under properties tab select Distinct
- Using Sorter, under properties tab select distinct
First one is the better option when the source is relational but when a source is a file then you need to use the second option only.
Q5.How can we handle two sessions in Informatica?
In Informatica Linking more than one session called batches, there are two types batches
- Sequential batches: sequential batches session runs one by one
- Concurrent batch: concurrent batches session runs at same time
6.Do you find any difficulty while working with flat files as source and target?
Yes, there are difficulties I found while working with flat file some of them are:
- We can’t use SQL override in flat files instead of that we have to use transformations.
- testing the flat files is very uninteresting job
- We need to specify the correct path in the session and mention either that file is ‘direct’ or ‘indirect. Keep that file in the exact path which we have specified in the session and we have to keep the file in exact path as mentioned in the session.
- If we miss the link to any column of the target, then all the data will be placed in wrong fields. That missed column won’t exist in the target data file.
Q7.What is the meaning of Decode in Informatica?
Decode is a special function which searches a port for the specified value.It is a better option to use DECODE function when the number of conditions is large because it is less costly compared to IIF function.and it can use in a select statement whereas we can not use IIF Function In select Statement.
Q8.what do think which one is the better joiner or look up?
We can’t compare which one is a better joiner or look up because both have their own functionalities .its all depends on table size and source.If we are using flat file sorted joiner is more effective than look up because of joiner caches fewer rows .lookup caches who file in always.If we are using database lookup is effective when the database can return sorted data fast and amount of data is small.
Q9.What is Domain in terms of Informatica?
Domains are primary component Of Informatica power center, it is used for managing and administrating various services in Informatica power center
Q10.What is a Mapplet/worklet in Informatica?
Mapplet is used for creation and configuration of a group of transformation.A worklet is an object by combining the set of tasks to build a workflow logic.It can be reusable in multiple workflows, which can be configured to run concurrently.
Q11.describe an Informatica PowerCenter?
In terms os Informatica power center is a service-oriented architecture which provides the capability to share service & resource across multiple machines.
Q12.What is the cumulative sum and moving sum?
A. Cumulative sum is partial sums of a given column that can calculate by using CUME(COLUMN NAME).
moving sum returns the sum of a specifies set of rows.It ignores null values when calculating the moving sum so that if all values are NULL, Then the function Returns NULL
Q13.Explain sessions and how many types of sessions are their?
A session is a set of instructions which explains the movement of data from source to target.
There are two type of sessions in Informatica
- Reusable Sessions
- Non-Reusable Sessions
Q14. explain what transformation and how many are there in Informatica?
A Transformation is a repository object which generates ,updates or sends the source data according to the requirements of target system. It is mainly classified into 4 types
Q15.What is a surrogate key?
A surrogate key is a unique Identification key and acts as natural primary key .it is unique for each row of the table that why it is very useful to identify each unique row.
Q16.What is PMCMD COMMAND?
PMCMD(power Mart Command) is a built-in command line program or utility that is used to interact with the Informatica server .this Command mainly used to perform the following tasks.
- Scheduling a workflow
- Start or stop workflow
- Start workflow from a task
- Stopping / Aborting specific task
- Aborting workflow
Q17.Is STOP AND ABORT are same IN INFORMATICA?
No both kill the process but the way is different, the process timeout period is the main difference in Stop and Abort.
Stop will Stop reading from the source and release the memory blocks that are occupied by the session at a time but it will continue updating and committing changes in target whereas abort also stop reading but it has the timeout period of 60 sec.If the session fails to update or commit the changes within 60 sec then the session will be aborted by terminating the DTM process thread and it will not release the memory immediately.
To ensure Data Consistency Stop Will try to Rollback data but in Abort kill the process immediately so that can’t be rolled back.
Q18.What are batches?
A group of sessions executed either Serial Or-Parallel Execution By The Informatica Server is called a batch.
There are two types of batches are theirs.
batch run session one after another is called Sequential batch and batch which run a session at the same is concurrent.
Q19.Can you generate sequence numbers without using the sequence generator transformation?
Yes, we can generate sequence numbers with expression transformation instead of sequence generator
Q20.What are the static cache and dynamic cache in Informatica?
In the static cache, the cache memory will not refresh even though the record is inserted or updated in the lookup table it will refresh when in next session run.
In dynamic caches, cache memory will refresh as soon as the record is inserted or updated in the look-up table.
Q21.What is the need of ETL tools?
ETL(Extract-Transform-Load) itself tell that is extract, transform and load the data to the source to destination for better decision making.if we don’t use ETL tools then we have to do all these things by manually by creating SQL codes that are not possible for an end user that can do only expert programmers.this process was very tedious and cumbersome in many cases because it involved many resources and complex coding.these difficulties are eliminated by ETL Tool because they are very comfortable to use and many other advantages in all stages like visual flow, structured system design, operational resilience, impact analysis, data profiling and cleansing and excellent performance.
Q22.What is fact table?
Fact table is the centralized table in a star schema that’s why it is also called central table. Fact table mainly contains the facts nothing measurement (values)that is related to the data in the dimension table. it typically has two types of columns one contains facts(values) and another contains foreign keys to dimension tables. a composite key that is made by all of its foreign keys is acts as a primary key of a fact table.
Q23.What is polling?
Polling means displaying the updated data about the session while it is running in a window. The monitor window displays the status of each session when you poll the Informatica server.
Q24.How many dimensions are there in Informatica?
Dimension table consists of attributes about the facts and it stores the textual descriptions of the business. dimensions are very important to measure the facts. and they are mainly 4 types.
Q25.how Can u generate reports in Informatica?
Not directly we could not generate reports, but we can generate metadata report by using Informatica Metadata driven reporting Tool.
Q26.What is the Rank index port in Rank transformation?
Rank transformation is an active and Connected transformation. it is used to select bottom or top rank of data this means to select the smallest or largest numeric value in a group or port. rank index port used by Informatica server to store the rank position for each record A Designer can create a rank index port automatically for each rank transformation.For Example, if you create a Rank Transformation that ranks the top 10 products for each quarter, then the rank index numbers products from 1-10.
Q27.What is the target load order?
Target load plan Target load order (or) is used to specify the order in which the integration service loads the targets. We can specify a target load order based on the Source Qualifier transformations in a mapping. if we are working with multiple source qualifier transformations connected to multiple targets then we can designate the order in which the integration service loads data into targets
Q28.what is data transformation manager Process?
The Integration Service process starts the data Transformation Manager (DTM)process when the workflow reaches a session.DTM process is also known as the PMDTM process. The main purpose of the DTM process is to create and manage threads that carry out the session tasks.
Q29.How to generate sequence numbers?
We can generate sequence numbers by using sequence generator or expression transformation.
Q30.Which transformation is needed while using the Cobol sources as source definitions?
Normalizer transformation because COBOL sources consist of denormalized data, normalizer transformation is essential to get normalized data.
Q31. What are active and passive transformations?
The Active and passive transformations transform the records in two different ways.In active transformation changes the number of rows number of rows thatpasss through the mapping .In case of Passive transformation do’t change the number of rows .
Q32.In how many ways we can create ports?
We can create ports in two ways by Dragging the port from another transformation and another by clicking the add port button on the ports tab
Q33. What is Data Caches/ Index Caches?
Index cache contains all of the port values which satisfies the given condition those are stored in index cache data cache stores port values which are not satisfies the given condition is stored in a data cache.
Q34. What do you mean by blocking transformation?
When the integration service waits for a row from a different input group. Some multiple input group transformations require the integration service to block data at an input group. A blocking transformation is a multiple input group transformation that blocks incoming data. Custom transformation and Joiner transformation comes into this category.
Q35.What you know about transaction control transformation?
Transaction Control is connected and an active transformation and it is used to control the rollback and commit of transactions .we can specify the transaction depending on varying number of input rows .it is used in two levels mapping and session levels
Q36. How do you change a non-reusable transformation to reusable transformation?
We can change a non reusable transformation to reusable transformation by selecting the reusable transformation in the navigation bar and drag it to the mapping and hold the control key just before releasing the transformation then release into mapping.