Informatica Interview Questions
Explain the Informatica architecture in detail
What are 2 modes of data movement in Informatica server?
The data movement mode depends on whether Informatica server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and code page validation in the Informatica client and server.A) unicode - is allows 2 bytes for each character...
Read Best Answer
Editorial / Best Answer
Answered by: purushotham
Answered On : Aug 9th, 2005the two types of modes are:
1) Normal Mode in which for every record a separate DML stmt will be prepared and executed
Bulk Mode in which for multiple records DML stmt will be preapred and executed thus improves performance.
Data Movement Modes
1. ASCII mode :- Single byte of data is processed.
2. UNICODE mode :- Two bytes of data is processed.
Data Loading Modes
1. Normal Mode :- Commit is executed after 10000 records (default).
2. Bulk Mode :- Commit is executed after all records have been loaded.
1. ASCII mode
2. UNICODE mode
Discuss which is better among incremental load, normal load and bulk load
Read Best Answer
Editorial / Best Answer
Answered by: gazulas
View all questions by gazulas View all answers by gazulas
Member Since Jan-2006 | Answered On : Jan 29th, 2006
if supported by the database bulk load can do the loading faster than normal load.(incremental load concept is differnt dont merge with bulk load, mormal load)
It's totally depends upon the requirement. generally bulk load is faster caz it won't write the data to database logs so we can't do session recovery where as in normal load it is very slow comparing to bulk but the advantage is we can do session recovery..
Incremental load: Incremental means suppose today we processed 100 records ,for tomorrow run u need to extract whatever the records inserted newly and updated after ...
Joiner transformation master detail
Suppose you have 2000 records in one table and 12000 in another which one you will consider as master and detail?
Read Best Answer
Editorial / Best Answer
Answered by: ghola
Member Since Jul-2008 | Answered On : Aug 15th, 2008
The joiner transformation compares each row of the master source against the detail source. Hence, fewer number of rows in master means fewer iterations of join comparison.
Secondly It is easier to cache the table with fewer number of rows.
Hence, using the table having the fewer number of rows as a master improves the performance.
The table with 2000 will be considered as a mater table and table with 12000 considered as detail table caz the joiner transformation compares master data against detail so less no of iterations means less joins thus it enhances the speed or performance
The integration service always detects the table with minimum number of records as Master table by default.
Session - a session is a set of instructions that tells the Informatica server how and when to move data from sources to targets. After creating the session, we can use EIther the server manager or the command line program pmcmd to start or stop the session.Batches - it provides a way to group sessions...
Read Best Answer
Editorial / Best Answer
Answered by: sanghala
View all questions by sanghala View all answers by sanghala
Member Since Apr-2006 | Answered On : May 14th, 2007
Session: A session is a set of commands that describes the server to move data to the target.
Batch : A Batch is set of tasks that may include one or more numbar of tasks (sessions, ewent wait, email, command, etc..,)
There are two types of batches in Informatica:
1. Sequential: When Data moves one after another from source to target it is sequential
2. Concurrent: When whole data moves simultaneously from source to target it is Concurrent
Session is nothing, but it tells the integration service, when and how to move the data from sources to target
Group of sessions is called a batch..
session is an object of repository,which instructs the informatica server(integration service)to execute the mapping with given database connection's...
$ & $$ in mapping or parameter file
What is the difference between $ & $$ in mapping or parameter file? In which cases they are generally used?
Read Best Answer
Editorial / Best Answer
Answered by: anshu.gangwar
View all answers by anshu.gangwar
Member Since Sep-2008 | Answered On : Sep 30th, 2008
$ prefixes are used to denote session Parameter and variables and $$ prefixes are used to denote mapping parameters and variables
$-This is d symbol for server o inbuilt variable.
$$-this is the symbol for the variables o parameters which v create
Hi,
$ is session parameter e.g $DBConnection
$$ is mapping parameter/variable e.g $$LASTRunDate
Thanks
What are dimensions and various types of dimensions?
Set of level properties that describe a specific aspect of a business, used for analyzing the factual measures of one or more cubes, which use that dimension. Egs. Geography, time, customer and product.
Read Best Answer
Editorial / Best Answer
Answered by: ManishTewatia
View all answers by ManishTewatia
Member Since Jul-2010 | Answered On : Jul 22nd, 2010
What Md.Rehman is trying to say is catagorizing the SCD which are the Slowly Changing Dimensions which are used to maintain historical data.
Dimension:A dimension is an organized hierarchy of categories, known as levels, that describes data in data warehouse fact tables
The various types of dimensions are :
1) Shared and Private Dimensions: Describes the basic differences between shared and private dimensions and their uses
2) Regular Dimensions: Provides information about regular dimensions and their variations
3) Parent-Child Dimensions: Describes the creation of parent-child dimensions and identifies their advantages and restrictions
4) Data Mining Dimensions: Describes the creation of data mining dimensions and identifies advantages and restrictions to their use
5) Virtual Dimensions:Describes the creation of virtual dimensions and their advantages and restrictions
6) Dependent Dimensions: Describes the creation of dependent dimensions and identifies their advantages and restrictions
7) Write-Enabled Dimensions: Describes the creation of write-enabled dimensions and identifies their advantages and restrictions
Dimensions are of 3 types mainly in case of slowly changing dimensions: Type 1 -> Does not maintain any history and is update only. --> this is normal practice mappings.Type 2-> Maintains full histor...
What Md.Rehman is trying to say is catagorizing the SCD which are the Slowly Changing Dimensions which are used to maintain historical data.Dimension:A dimension is an organized hierarchy of categorie...
Hi everyone ,can u explain in detail what is a join index,with example.And what is its importance in teradata.
Read Best Answer
Editorial / Best Answer
Answered by: rin23
View all questions by rin23 View all answers by rin23
Member Since May-2011 | Answered On : May 12th, 2011
A join Index is a cross between a view and an index. It is like a view in that it is created using a query to specify the structure, composition and source of the contents. It is like an index in the way that it is used automatically by the database system to improve the performance of a query. Join indexes use the "classic" space-time trade off trading disk space for storage of the join index to get improved performance for queries. Unlike many other materialized view implementations, Teradata join indexes are updated immediately and automatically when changes are made to the base tables. There is never a concern that you might be using stale data when the system chooses to use a join index in the query plan. Also, Teradata uses its sophisticated coverage testing algorithm to minimize the cost associated with join index maintenance.Â
A join Index is a cross between a view and an index. It is like a view in that it is created using a query to specify the structure, composition and source of the contents. It is like an index in the ...
Which transformation you need while using the cobol sources as source definitions?
Read Best Answer
Editorial / Best Answer
Answered by: sithusithu
View all answers by sithusithu
Member Since Dec-2005 | Answered On : Jan 20th, 2006
Normalizer transformation.
Normalizer transformation which is used to normalize the data.Since COBOL sources are often consists of De-normalized data.
Normalizer transformaiton which is used to normalize the data
sirajdeen
Normalizer transformation.
Normalizer transformation which is used to normalize the data.Since COBOL sources are often consists of De-normalized data.
Which source definition files are needed to provide support for mainframes ?
Read Best Answer
Editorial / Best Answer
Answered by: deepthi_97
View all answers by deepthi_97
Member Since Sep-2009 | Answered On : Sep 23rd, 2009
Cobol files and we use normalizer transformation
Cobol files and we use normalizer transformation
The mainframe files are Used as VSAM files in Informatica by using the Normaliser transformation
Read Best Answer
Editorial / Best Answer
Answered by: srinvas vadlakonda
Answered On : Sep 28th, 2006Source name
Database location
Column names
Datatypes
Key constraints
ODBC data source, username, owner name, password. Once the connection is established, select the table.
Relational sources are tables, views, synonyms. Source name Database location Column name Datatype Key Constraints. For synonyms you will have to manually create the constraints.
What are various types of aggregation?
Various types of aggregation are sum, avg, count, max, min, first, last, median, percentile, stddev, and variance.
Read Best Answer
Editorial / Best Answer
Answered by: sithusithu
View all answers by sithusithu
Member Since Dec-2005 | Answered On : Jan 19th, 2006
Cheers,
Sithu
Sum, Count, Average, Median
It represents all data queried from the source.
Read Best Answer
Editorial / Best Answer
Answered by: sprajarajan
View all questions by sprajarajan View all answers by sprajarajan
Member Since Mar-2008 | Answered On : Aug 8th, 2008
Source Qualifier Is the default Transformation.
Through The source Qualifier Transformation Informatica Reads The Data.
We can Filter The Data.
We can sort the Data.
Its also Used to Join Homogeneous Source systems.
We can Join Any number of Sources in Singlae Source Qualifier.
We Can't Join the Flatfiles In sourcequalifier Because Flatfiles Are Heterogeneous When we open the Flatfiles At sourcequalifier At the time All The options are Disabled.
1. Source Qualifier is the most important transformation which convert the source data type in to compatible NATIVE datatype of a mapping.2. Without a SQ a mapping can not be created, after extractio...
Source Qualifier is one of the transformations, which converts any type of data to the relational format. So that, can easily be used for any of the operations on the data.
What is data transformation manager?
After the load manager performs validations for the session, it creates the dtm process. The dtm process is the second process associated with the session run. The primary purpose of the dtm process is to create and manage threads that carry out the session tasks.· the dtm allocates process memory for...
Read Best Answer
Editorial / Best Answer
Answered by: Goush
Member Since May-2007 | Answered On : May 15th, 2007
When the workflow reaches a session, the Integration Service process starts
the DTM process. The DTM is the process associated with the session and it
performs the following tasks:
1. Retrieves and validates session information from the repository.
2. Performs pushdown optimization when the session is configured for
pushdown optimization.
3. Adds partitions to the session when the session is configured for dynamic
partitioning.
4. Forms partition groups when the session is configured to run on a grid.
5. Expands the service process variables, session, and mapping variables and
parameters.
6. Creates the session log.
7. Validates source and target code pages.
8. Verifies connection object permissions.
9. Runs pre-session shell commands, stored procedures and SQL.
10. Sends a request to start worker DTM processes on other nodes when the
session is configured to run on a grid.
11. Creates and runs mapping, reader, writer, and transformation threads to
extract, transform, and load data.
12. Runs post-session stored procedures, SQL, and shell commands
13. Sends post-session email.
load manager creates the DTM(data transformation manager)in the work flow manager by the informatica server ,here DTM creates two threads 1.reader thread2.writer threadDTM reads the data from &nb...
Load manager and DTM are the components of Informatica server. Load manager manages the load on the server by maintaining a queue of sessions and release the session based on first come and firs...
Discuss the advantages & disadvantages of star & snowflake schema?
Read Best Answer
Editorial / Best Answer
Answered by: swati
Answered On : Nov 5th, 2005star schema consists of single fact table surrounded by some dimensional table.In snowflake schema the dimension tables are connected with some subdimension table.
In starflake dimensional ables r denormalized,in snowflake dimension tables r normalized.
star schema is used for report generation ,snowflake schema is used for cube.
The advantage of snowflake schema is that the normalized tables r easier to maintain.it also saves the storage space.
The disadvantage of snowflake schema is that it reduces the effectiveness of navigation across the tables due to large no of joins between them.
Snowflakes are an addition to the Kimball Dimensional system to enable that system to handle hierarchial data. When Kimball proposed the dimensional data warehouse it was not first recogonized t...
It depends upon the clients which they are following, whether snowflake or star schema.
What are target types on the server?
Target types are file, relational and erp.
Read Best Answer
Editorial / Best Answer
Answered by: manojkumar_dwh
View all answers by manojkumar_dwh
Member Since Apr-2007 | Answered On : Apr 14th, 2007
PowerCenter can load data into the following targets:
- Relational. Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
- File. Fixed and delimited flat file and XML.
- Application. You can purchase additional PowerCenter Connect products to load data into SAP BW. You can also load data into IBM MQSeries message queues and TIBCO.
- Other. Microsoft Access.
You can load data into targets using ODBC or native drivers, FTP, or external loaders.
It may be flat file or relational table.
The target types on the server are flat-file, xml, ERP, Cobol etc
What are aggregate transformation?
Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.
Read Best Answer
Editorial / Best Answer
Answered by: Praveen vasudev
Answered On : Sep 12th, 2005Aggregator transform is m uch like the Group by clause in traditional SQL.
this particular transform is a connected/active transform which can take the incoming data form the mapping pipeline and group them based on the group by ports specified and can caculated aggregate funtions like ( avg, sum, count, stddev....e.tc) for each of those groups.
From a performanace perspective if your mapping has an AGGREGATOR transform use filters and sorters very early in the pipeline if there is any need for them.
veepee
1. Aggrigator transformation allows to perform aggrigate calculation, such as SUM, MAX, MIN, FIRST, LAST
2. Aggrigator transformation allows to perform aggrigate calculation of group.
To perform Group by calculations we use Aggregator Transformation.It perform calculations similar to Expression Transformation.But difference between both is that Aggregator Transform...
Transformation to load 5 flat files
What is the method of loading 5 flat files of having same structure to a single target and which transformations will you use?
Read Best Answer
Editorial / Best Answer
Answered by: sarun5
View all questions by sarun5 View all answers by sarun5
Member Since Feb-2008 | Answered On : Mar 12th, 2008
Guys I have got the answer for which I asked..here you go
This can be handled by using the file list in informatica. If we have 5 files
in different locations on the server and we need to load in to single target
table. In session properties we need to change the file type as Indirect.
(Direct if the source file contains the source data. Choose Indirect if the
source file contains a list of files.
When you select Indirect, the PowerCenter Server finds the file list then reads
each listed file when it executes the session.)
am taking a notepad and giving following paths and file
names in this notepad and saving this notepad as
emp_source.txt in the directory /ftp_data/webrep/
/ftp_data/webrep/SrcFiles/abc.txt
/ftp_data/webrep/bcd.txt
/ftp_data/webrep/srcfilesforsessions/xyz.txt
/ftp_data/webrep/SrcFiles/uvw.txt
/ftp_data/webrep/pqr.txt
In session properties i give /ftp_data/webrep/ in the
directory path and file name as emp_source.txt and file type as Indirect.
Two Methods.
1. Write all files in one directory then use file repository concept (dont
forget to type source file type as indirect in the session).
2. Use union t/r to combine multiple input files into a single target.
This very easy to load more than one flat files to target. To do this we can save d full path name in a text file; there after in session properties write the full path name of the folder where we hav...
Read Best Answer
Editorial / Best Answer
Answered by: Deb
Answered On : Jun 8th, 2005I am providing the answer which I have taken it from Informatica 7.1.1 manual,
Ans: While running a Workflow,the PowerCenter Server uses the Load Manager process and the Data Transformation Manager Process (DTM) to run the workflow and carry out workflow tasks.When the PowerCenter Server runs a workflow, the Load Manager performs the following tasks:
1. Locks the workflow and reads workflow properties.
2. Reads the parameter file and expands workflow variables.
3. Creates the workflow log file.
4. Runs workflow tasks.
5. Distributes sessions to worker servers.
6. Starts the DTM to run sessions.
7. Runs sessions from master servers.
8. Sends post-session email if the DTM terminates abnormally.
When the PowerCenter Server runs a session, the DTM performs the following tasks:
1. Fetches session and mapping metadata from the repository.
2. Creates and expands session variables.
3. Creates the session log file.
4. Validates session code pages if data code page validation is enabled. Checks query
conversions if data code page validation is disabled.
5. Verifies connection object permissions.
6. Runs pre-session shell commands.
7. Runs pre-session stored procedures and SQL.
8. Creates and runs mapping, reader, writer, and transformation threads to extract,transform, and load data.
9. Runs post-session stored procedures and SQL.
10. Runs post-session shell commands.
11. Sends post-session email.
Load Manager is responsible for dispatching the sessions and maintains Q of sessions based on first in and first out. Load Manager is also known as Master Processor (DTM) Data Transformation Manager.D...
I am providing the answer which I have taken it from Informatica 7.1.1 manual, Ans: While running a Workflow,the PowerCenter Server uses the Load Manager process and the Data Transformation...
How do you identify existing rows of data in the target table using lookup transformation
Can identify existing rows of data using unconnected lookup transformation.
Read Best Answer
Editorial / Best Answer
Answered by: SK
Answered On : Aug 30th, 2007There are two ways to lookup the target table to verify a row exists or not :
1. Use connect dynamic cache lookup and then check the values of NewLookuprow Output port to decide whether the incoming record already exists in the table / cache or not.
2. Use Unconnected lookup and call it from an expression trasformation and check the Lookup condition port value (Null/ Not Null) to decide whether the incoming record already exists in the table or not.
Lookup Transformation is used to cheek weather the data is present in target or not.
This transformation is of 2 types
1. Connected lookup transformation
2. Unconnected lookup transformation.
By using above both we can cheek the target for data .
We can use both connected and unconnected lookups to check the existing records in the target table.
User Panel
Categories
Interview Coaching
Ads
Connect
Ads
Interview Question
Latest Questions
Ads
Interview & Career Tips
Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.
NH
Editorial / Best Answer
Answered by: Rajsuni
View all answers by Rajsuni
Member Since Apr-2011 | Answered On : Apr 1st, 2011
Informatica Architecture :
client tools-
Repositoy Manager-> Pc Designer-> WFManager-> WFMonitor
| | | |
(creat,mdify,del-folders, (src&Tar def, (create tasks (display
privilages& access RS) mapings,maPlets)connect to work flow) reult/output)
---------------------
Powercenter tools--
PC service <--------------------->Repository Database
|
|
<-------> PC Server<---->
here: Pc-PowerCenter.
Informatica Architecture : client tools- Repositoy Manager-> Pc Designer-> WFManager-> WFMonitor | ...
When we install the informatica powercenter, the following components gets ionstalled 1) powercenter clients 2) Integration service 3) powercenter Repository service 4) powercenter Repository 5) power...