informatica Faq's

Document Sample
informatica Faq's Powered By Docstoc
					      Informatica FAQ
1.   What are Target Types on the Server?
     Target Types are File, Relational and ERP.

2.   What are Target Options on the Servers?
          Answer: Target Options for File Target type are FTP File, Loader and MQ.There are no target
     options for ERP target type Target Options for Relational are Insert, Update (as Update), Update (as
     Insert), Update (else Insert), Delete, and Truncate Table

3.   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.
         You can use a Connected Lookup with dynamic cache on the target.

4. What is Aggregate transformation?
   Aggregator transformation allows you to perform aggregate calculations, such as averages and
   sums. Aggregator transform is much 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 calculated
   aggregate functions like (avg, sum, count, and for each of those groups.From a
   performance perspective if you’re mapping has an AGGREGATOR transform use filters and sorters
   very early in the pipeline if there is any need for them.

5.   What are various types of Aggregation? .
         Answer: Various types of aggregation are SUM, AVG, COUNT, MAX, MIN, FIRST, LAST,

6.   What are Dimensions and various types of Dimensions?
          Answer: 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.

7.   What are 2 modes of data movement in Informatica Server?
      Answer: 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 and uses additional byte for each non-ASCII character
(such as Japanese characters)
b) ASCII - IS holds all data in a single byteThe IS data movement mode can be changed in the
Informatica Server configuration parameters. This comes into effect once you restart the Informatica

      The two types of modes are:
     1) Normal Mode in which for every record a separate DML stmt will be prepared and executed
     2) Bulk Mode in which for multiple records DML stmt will be prepared and executed thus improves

8.   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 loss. One code page can be a subset or superset of another. For accurate
     data movement, the target code page must be a superset of the source code page.
     Superset - A code page is a superset of another code page when it contains the character encoded
     in the other code page; it also contains additional characters not contained in the other code page.
     Subset - A code page is a subset of another code page when all characters in the code page are
     encoded in the other code page.

9.   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, u must select the Japanese code page of source data

10. What is Router transformation?
          Answer: Router transformation allows you to use a condition to test data. It is similar to filter
     transformation. It allows the testing to be done on one or more conditions.

     Informatica FAQ
   Router transformation is use to load data in multiple targets depending on the test condition.
   we can specify multiple conditions and route the data to multiple targets depending upon the
   The best thing of using router transformation over filter transformation is that we can get the
   rejected records if needed

11. What is Load Manager?
Ans: While running a Workflow, the Power Center Server uses the Load Manager process and the Data
Transformation Manager Process (DTM) to run the workflow and carry out workflow tasks. When the
Power Center 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 Power Center 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 run mapping, reader, writer, and transformation threads to extract, transform, and load
9. Runs post-session stored procedures and SQL.
10. Runs post-session shell commands.
11. Sends post-session email.

12. What is Data Transformation Manager?
 Answer: 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 the session and divide it into buffers. This is also known as
buffer memory. It creates the main thread, which is called the master thread. The master thread
creates and manages all other threads.
· If we partition a session, the DTM creates a set of threads for each partition to allow concurrent
processing... When Informatica server writes messages to the session log it includes thread type and
thread ID.

Following are the types of threads that DTM creates:
Master thread - Main thread of the DTM process. Creates and manages all other threads.
Mapping thread - One Thread to Each Session. Fetches Session and Mapping Information.
Pre and Post Session Thread-One Thread each to Perform Pre and Post Session Operations.
Reader thread-One Thread for Each Partition for Each Source Pipeline.
WRITER THREAD-One Thread for Each Partition if target exist in the source pipeline write to the target.
Transformation THREAD - One or More Transformation Thread For Each Partition.

13. What is Session and Batches?
     Answer: 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 For Either Serial Or Parallel Execution By The Informatica Server.
There Are Two Types Of Batches : Sequential - Run Session One after the Other. Concurrent - Run
Session At The Same Time.

14. What is a source qualifier? -
       Informatica FAQ
It represents all data queried from the source.
15.      Why we use lookup transformations?
Answer: Lookup Transformations can access data from relational tables that are not sources in
mapping. With Lookup transformation, we can accomplish the following tasks:
Get a related value-Get the Employee Name from Employee table based on the Employee IDPerform
Update slowly changing dimension tables - We can use unconnected lookup transformation to determine
whether the records already exist in the target or not

16: While importing the relational source definition from database, what are the Meta data of source U
     Source name Database location Column names Data types Key constraints

      How many ways you can update a relational source definition and what r they?
            Two ways 1. Edit the definition 2. Reimport the definition

16.      Where should U place the flat file to import the flat file definition to the designer?
            Place it in local folder
17.      To provide support for Mainframes source data, which files r used as a source definitions?
          COBOL files

18.      Which transformation should u need while using the Cobol sources as source definitions
     Answer: Normalizer transformation which is used to normalize the data. Since Cobol sources r
often consists of Demoralized data.

20. How can U create or import flat file definition in to the warehouse designer?
Answer: U can not create or import flat file definition in to warehouse designer directly. Instead U must
analyze the file in source analyzer, and then drag it into the warehouse designer.
          When U drag the flat file source definition into warehouse designer workspace, the warehouse
designer creates a relational target definition not a file defintion.If u want to load to a file, configure the
session to write to a flat file. When the informatica server runs the session, it creates and loads the flat

21.      What is the maplet?
      Maplet is a set of transformations that you build in the maplet designer and U can use in multiple
22. What is a transformation
It is a repository object that generates, modifies or passes data.

23.      What r the designer tools for creating transformations?
      Answer: Mapping designer
      Transformation developer
      Mapplet designer
      Mapping designer Transformation developer Mapplet designer

24.      What r the active and passive transformations?
         An active transformation can change the number of rows that pass through it. A passive
         transformation does not change the number of rows that pass through it.

25.      What r the connected or unconnected transformations?
    An unconnected transformation is not connected to other transformations in the mapping.
Connected transformation is connected to other transformations in the mapping.
    Connected transformation is a part of your data flow in the pipeline while unconnected
Transformation is not.
    Much like calling a program by name and by reference.
    Use unconnected transforms when you want to call the same transform many times in a single

26.      How many ways u create ports?

       Informatica FAQ
         Two ways 1.Drag the port from another transformation 2.Click the add button on the ports tab.
27.      What r the reusable transformations?
      Answer: Reusable transformations can be used in multiple mappings. When u need to incorporate
      this transformation into mapping add an instance of it to maping.Later if U change the definition of
      the transformation, all instances of it inherit the changes.
                 Since the instance of reusable transformation is a pointer to that transformation u can
      change the transformation in the transformation developer, its instances automatically reflect these
      changes. This feature can save U great deal of work

28. What r the methods for creating reusable transformations?
Answer: Two methods
1.Design it in the transformation developer. By default it’s a reusable transform.
2. Promote a standard transformation from the mapping designer. After U adds a transformation to the
mapping, U can promote it to the status of reusable transformation.
Once U promote a standard transformation to reusable status, U CANNOT demote it to a standard
transformation at any time.
If u change the properties of a reusable transformation in mapping, U can revert it to the original
reusable transformation properties by clicking the revert button.

29. What r the unsupported repository objects for a mapplet?
Answer: COBOL source definition
Joiner transformations
Normalizer transformations
Non reusable sequence generator transformations.
Pre or post session stored procedures
Target definitions
Power mart 3.5 style Look Up functions
XML source definitions
IBM MQ source definitions

30.      What r the mapping parameters and mapping variables?
Answer: Mapping parameter represents a constant value that U can define before running a session. A
mapping parameter retains the same value throughout the entire session.
       When u use the mapping parameter, U declare and use the parameter in a mapping or
maplet.Then define the value of parameter in a parameter file for the session.
        Unlike a mapping parameter, a mapping variable represents a value that can change throughout
the session. The informatica server saves the value of mapping variable to the repository at the end of
session run and uses that value next time U run the session.
       Mapping variables have two identities:
Start value and Current value
Start value = Current value ( when the session starts the execution of the underlying mapping)
Start value <> Current value ( while the session is in progress and the variable value changes in one
ore more occasions)
Current value at the end of the session is nothing but the start value for the subsequent run of the
same session

31. Can U use the mapping parameters or variables created in one mapping into another mapping?
Answer: NO.
We can use mapping parameters or variables in any transformation of the same mapping or mapplet in
which U have created mapping parameters or variables.

32. Can u use the mapping parameters or variables created in one mapping into any other reusable
Yes. Because reusable transformation is not contained with any maplet or mapping.

33.      How can U improve session performance in aggregator transformation?
Use sorted input.
1. use a sorter before the aggregator
2. Donot forget to check the option on the aggregator that tell the aggregator that the input is sorted on

       Informatica FAQ
the same keys as group by.
The key order is also very important.
34. What is aggregate cache in aggregator transformation?
Answer: The aggregator stores data in the aggregate cache until it completes aggregate calculations.
When u run a session that uses an aggregator transformation, the informatica server creates index and
data caches in memory to process the transformation. If the informatica server requires more space, it
stores overflow values in cache files.

35. What r the difference between joiner transformation and source qualifier transformation?
Answer: U can join heterogeneous data sources in joiner transformation which we can not achieve in
source qualifier transformation.
U need matching keys to join two relational sources in source qualifier transformation. Where as u
doesn’t need matching keys to join two sources.
Two relational sources should come from same data source in sourcequalifier.U can join relational
sources which r coming from different sources also.

36. In which conditions we can not use joiner transformation (Limitations of joiner transformation)?
Answer: Both pipelines begin with the same original data source.
Both input pipelines originate from the same Source Qualifier transformation.
Both input pipelines originate from the same Normalizer transformation.
Both input pipelines originate from the same Joiner transformation.
Either input pipelines contains an Update Strategy transformation.
Either input pipelines contains a connected or unconnected Sequence Generator transformation.

37. What r the settings that u use to configure the joiner transformation?
Master and detail source Type of join Condition of the join

38.      What r the join types in joiner transformation?
           Normal (Default) Master outer Detail outer Full outer
      Normal (Default) -- only matching rows from both master and detail
      Master outer -- all detail rows and only matching rows from master
      Detail outer -- all master rows and only matching rows from detail
      Full outer -- all rows from both master and detail ( matching or non matching)

39. What r the joiner caches?
Answer: When a Joiner transformation occurs in a session, the Informatica Server reads all the records
from the master source and builds index and data caches based on the master rows.
After building the caches, the Joiner transformation reads records from the detail source and performs
From a performance perspective. Always makes the smaller of the two joining tables to be the master.

40.      What is the look up transformation?
      Answer: Use lookup transformation in u’r mapping to lookup data in a relational table, view, and
      Informatica server queries the look up table based on the lookup ports in the transformation. It
      compares the lookup transformation port values to lookup table column values based on the look up

41. Why use the lookup transformation?
Answer: To perform the following tasks.
Get a related value. For example, if your source table includes employee ID, but you want to include
the employee name in your target table to make your summary data easier to read.
Perform a calculation. Many normalized tables include values used in a calculation, 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.

     Informatica FAQ

42 What r the types of lookup?
Connected and unconnected
43 Differences between connected and unconnected lookup?

               Connected lookup         Unconnected lookup
         Receives input values directly Receives input values from the result of an lkp
         from the pipe line.            expression in a transformation.
         U can use a dynamic or static
                                       U can use a static cache.
         Cache includes all lookup      Cache includes all lookup out put ports in the
         columns used in the maping     lookup condition and the lookup/return port.
         Support user defined default
                                        Does not support user defined default values

44 What is meant by lookup caches?

Answer: The informatica server builds a cache in memory when it processes the first row of a data in a
cached look up transformation. It allocates memory for the cache based on the amount u configure in
the transformation or session properties. The informatica server stores condition values in the index
cache and output values in the data cache.

45 What r the types of lookup caches?
Answer: Persistent cache: U can save the lookup cache files and reuse them the next time the
informatica server processes a lookup transformation configured to use the cache.
Recache from database: If the persistent cache is not synchronized with he lookup table, U can
configure the lookup transformation to rebuild the lookup cache.
Static cache: U can configure a static or read-only cache for only lookup table. By default informatica
server creates a static cache. It caches the lookup table and lookup values in the cache for each row
that comes into the transformation. When the lookup condition is true, the informatica server does not
update the cache while it processes the lookup transformation.
Dynamic cache: If u want to cache the target table and insert new rows into cache and the target can
create a look up transformation to use dynamic cache. The informatica server dynamically inserts data
to the target table.
Shared cache: U can share the lookup cache between multiple transactions can share unnamed cache
between transformations in the same mapping.

46 Difference between static cache and dynamic cache
                         Static cache                      Dynamic cache
                                                           U can insert rows into the cache as
      U can not insert or update the cache
                                                           u pass to the target
      The informatica server returns a value from the      The informatic server inserts rows
      lookup table or cache when the condition is true.    into cache when the condition is
      When the condition is not true, informatica server   false. This indicates that the the
      returns the default value for connected              row is not in the cache or target
      transformations and null for unconnected             table. U can pass these rows to the
      transformations.                                     target table
47 Which transformation should we use to normalize the COBOL and relational sources?
Normalizer Transformation. When U drags the COBOL source in to the mapping Designer workspace, the
normalizer transformation automatically appears, creating input and output ports for every column in
the source.

48 How the informatica server sorts the string values in Rank transformation?

       Informatica FAQ
Answer: When the informatica server runs in the ASCII data movement mode it sorts session data
using Binary sortorder.If U configure the session to use a binary sort order, the informatica server
calculates the binary value of each string and returns the specified number of rows with the highest
binary values for the string.
49 What r the rank caches?
Answer: During the session, the informatica server compares an in out row with rows in the
datacache.If the input row out-ranks a stored row, the informatica server replaces the stored row with
the input row. The informatica server stores group information in an index cache and row data in a data

50.What is the Rankindex in Ranktransformation?
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.

For example, if you create a Rank transformation that ranks the top 5 salespersons for each quarter,
the rank index numbers the salespeople from 1 to 5:

51.       What is the Router transformation?

      Answer: A Router transformation is similar to a Filter transformation because both transformations
      allow you to use a condition to test data. However, a Filter transformation tests data for one
      condition and drops the rows of data that do not meet the condition.
                A Router transformation tests data for one or more conditions and gives you the option to
      route rows of data that do not meet any of the conditions to a default output group.
      If you need to test the same input data based on multiple conditions, use a Router Transformation
      in a mapping instead of creating multiple Filter transformations to perform the same task.

52. What r the types of groups in Router transformation?
Answer: Input group Output group
The designer copies property information from the input ports of the input group to create a set of
output ports for each output group.
Two types of output groups
User defined groups
Default group
U can not modify or delete default groups.

53. Why we use stored procedure transformation?
Answer: For populating and maintaining data bases.

54What r the types of data that passes between informatica server and stored procedure?
Answer: 3 types of data
Input/Out put parameters
Return Values
Status code.

55. 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.This
value can not seen by the user.It only used by the informatica server to determine whether to continue
running the session or stop.

56. What is source qualifier transformation?
Answer: When U add a relational or a flat file source definition to a maping,U need to connect it to
a source qualifer transformation.The source qualifier transformation represnets the records
that the informatica server reads when it runs a session.

57. What r the tasks that source qualifier performs?
Answer: Join data originating from same source data base.
Filter records when the informatica server reads source data.

      Informatica FAQ
Specify an outer join rather than the default inner join
specify sorted records.
Select only distinct values from the source.
Creating custom query to issue a special SELECT statement for the informatica server to read
source data.
58.   What is the target load order?
Answer: U specify the target loadorder based on source qualifiers in a maping.If u have the multiple
source qualifiers connected to the multiple targets,U can designatethe order in which informatica
server loads data into the targets.

59. What is the default join that source qualifier provides?
Inner equi join.

60. What r the basic needs to join two sources in a source qualifier?
Answer: Two sources should have primary and Foreign key relation ships.
Two sources should have matching data types.

61. what is update strategy transformation ?
Answer: This transformation is used to maintain the history data or just most recent changes in to

62. Describe two levels in which update strategy transformation sets?
Answer: Within a session. When you configure a session, you can instruct the Informatica Server to
either treat all records in the same way (for example, treat all records as inserts), or use instructions
coded into the session mapping to flag records for different database operations.
Within a mapping. Within a mapping, you use the Update Strategy transformation to flag records for
insert, delete, update, or reject.

63. What is the default source option for update stratgey transformation?
Answer: Data driven..

64. What is Datadriven?
Answer: The informatica server follows instructions coded into update strategy transformations with in
the session maping determine how to flag records for insert, update, delete or reject. If u does not
choose data driven option setting, the informatica server ignores all update strategy transformations in
the mapping.

65. What r the options in the target session of update strategy transformation?
Answer: Insert
Update as update
Update as insert
Update else insert
Truncate table

66. What r the types of mapping wizards that r to be provided in Informatica?
Answer: The Designer provides two mapping wizards to help you create mappings quickly and easily.
Both wizards are designed to create mappings for loading and maintaining star schemas, a series of
dimensions related to a central fact table.

Getting Started Wizard. Creates mappings to load static fact and dimension tables, as well as slowly
growing dimension tables.
Slowly Changing Dimensions Wizard. Creates mappings to load slowly changing dimension tables based
on the amount of historical dimension data you want to keep and the method you choose to handle
historical dimension data.

67. What r the types of mapping in Getting Started Wizard?
Answer: Simple Pass through mapping:
Loads a static fact or dimension table by inserting all rows. Use this mapping when you want to drop all

      Informatica FAQ
existing data from your table before loading new data.
Slowly Growing target:
Loads a slowly growing fact or dimension table by inserting new rows. Use this mapping to load new
data when existing data does not require updates.

68. What r the mappings that we use for slowly changing dimension table?
Answer: Type1: Rows containing changes to existing dimensions are updated in the target by
overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current
dimension data.
Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need
to keep any previous versions of dimensions in the table.

Type 2: The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target.
Changes are tracked in the target table by versioning the primary key and creating a version number
for each dimension in the table.
Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you
want to keep a full history of dimension data in the table. Version numbers and versioned primary keys
track the order of changes to each dimension.

Type 3: The Type 3 Dimension mapping filters source rows based on user-defined comparisons and
inserts only those found to be new dimensions to the target. Rows containing changes to existing
dimensions are updated in the target. When updating an existing dimension, the Informatica Server
saves existing data in different columns of the same row and replaces the existing data with the

69 What r the different types of Type2 dimension mapping?
Type2 Dimension/Version Data Mapping: In this mapping the updated dimension in the source will gets
inserted in target along with a new version number. And newly added dimension
in source will inserted into target with a primary key.
Type2 Dimension/Flag current Mapping: This mapping is also used for slowly changing dimensions. In
addition it creates a flag value for changed or new dimension.
Flag indicates the dimension is new or newly updated.Recent dimensions will get saved with current flag
value 1. And updated dimensions r saved with the value 0.

Type2 Dimension/Effective Date Range Mapping: This is also one flavor of Type2 mapping used for
slowly changing dimensions. This mapping also inserts both new and changed dimensions in to the
target. And changes r tracked by the effective date range for each version of each dimension

70. How can u recognize whether or not the newly added rows in the source r gets insert in the target?
Answer: In the Type2 mapping we have three options to recognize the newly added rows
Version number
Flag value
Effective date Range

71. What r two types of processes that informatica runs the session?
Answer: Load manager Process: Starts the session, creates the DTM process, and sends post-session
email when the session completes.
The DTM process. Creates threads to initialize the session, read, write, and transform data, and handle
pre- and post-session operations.

72.   What r the new features of the server manager in the informatica 5.0?
Answer: U can use command line arguments for a session or batch. This allows U to change the values
of session parameters, and mapping parameters and mapping variables.
Parallel data processing: This feature is available for power center only. If we use the informatica
server on a SMP system can use multiple CPU’s to process a session concurrently.
Process session data using threads: Informatica server runs the session in two processes. Explained in
previous question.

      Informatica FAQ
73 Can u generate reports in Informatics?
Answer: Yes. By using Metadata reporter we can generate reports in informatica.

74. 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 can access information about U’r repository with out having knowledge of sql,
transformation language or underlying tables in the repository.
75. Define mapping and sessions?
Answer: Mapping: It is a set of source and target definitions linked by transformation objects that
define the rules for transformation.
Session: It is a set of instructions that describe how and when to move data from source to targets.

76. Which tool U use to create and manage sessions and batches and to monitor and stop the informatica
Answer: Informatica server manager..

77. Why we use partitioning the session in informatica?
Answer: Partitioning achieves the session performance by reducing the time period of reading the
source and loading the data into target.

78.   To achieve the session partition what r the necessary tasks u have to do?
Answer: Configure the session to partition source data.
Install the informatica server on a machine with multiple CPU’s.

79. How the informatica server increases the session performance through partitioning the source?
Answer: For a relational sources informatica server creates multiple connections for each partition of a
single source and extracts separate range of data for each connection.Informatica server reads multiple
partitions of a single source concurently.Similarly for loading also informatica server creates multiple
connections to the target and loads partitions of data concurrently.

For XML and file sources, informatica server reads multiple files concurently.For loading the data
informatica server creates a separate file for each partition (of a source file).U can choose to merge the

80. Why u use repository connectivity?
 Answer: When u edit, schedule the session each time,informatica server directly communicates the
repository to check whether or not the session and users r valid. All the metadata of sessions and
mappings will be stored in repository.

81. What r the tasks that Load manger process will do?
Answer: Manages the session and batch scheduling: When u start the informatica server the load
manager launches and queries the repository for a list of sessions configured to run on the informatica
When u configures the session the load manager maintains list of list of sessions and session start
times. When u start a session load manger fetches the session information from the repository to
perform the validations and verifications prior to starting DTM process.
Locking and reading the session: When the informatica server starts a session load manager locks the
session from the repository. Locking prevents U starting the session again and again.
Reading the parameter file: If the session uses a parameter files, load manager reads the parameter file
and verifies that the session level parameters are declared in the file
Verifies permission and privileges: When the session starts load manger checks whether or not the user
have privileges to run the session.
Creating log files: Load manger creates log file contains the status of session.

82.What is DTM process?
Answer: After the load manger performs validations for session, it creates the DTM process. DTM is to
create and manage the threads that carry out the session tasks. I create the master thread. Master
thread creates and manges all the other threads.

      Informatica FAQ
83. What r the different threads in DTM process?
Master thread: Creates and manages all other threads
Mapping thread: One mapping thread will be creates for each session.Fectchs session and mapping
Pre and post session threads: This will be created to perform pre and post session operations.
Reader thread: One thread will be created for each partition of a source. It reads data from source.
Writer thread: It will be created to load data to the target.
Transformation thread: It will be created to transform data. s.
84. What r the data movement modes in informatcia?
Answer: Data movement modes determines how informatcia server handles the character data.U
choose the data movement in the informatica server configuration settings. Two types of data
movement modes available in informatica.
ASCII mode
Uni code mode.

85.   What r the out put files that the informatica server creates during the session running?
Answer: Informatica server log: Informatica server (on UNIX) creates a log for all status and error
messages (default name: pm.server.log).It also creates an error log for error messages. These files will
be created in informatica home directory.

Session log file: Informatica server creates session log file for each session. It writes information about
session into log files such as initialization process, creation of sol commands for reader and writer
threads, errors encountered and load summary. The amount of detail in session log file depends on the
tracing level that u set.

Session detail file: This file contains load statistics for each target in mapping. Session detail include
information such as table name, number of rows written or rejected can view this file by double clicking
on the session in monitor window
Performance detail file: This file contains information known as session performance details which helps
U where performance can be improved. To generate this file select the performance detail option in the
session property sheet.

Reject file: This file contains the rows of data that the writer does not write to targets.
Control file: Informatica server creates control file and a target file when U runs a session that uses the
external loader. The control file contains the information about the target flat file such as data format
and loading instructions for the external loader.

Post session email: Post session email allows U to automatically communicate information about a
session run to designated recipients can create two different messages. One if the session completed
successfully the other if the session fails.

Indicator file: If u use the flat file as a target can configure the informatica server to create indicator
file. For each target row, the indicator file contains a number to indicate whether the row was marked
for insert, update, delete or reject.

Output file: If session writes to a target file, the informatica server creates the target file based on file
properties entered in the session property sheet.
Cache files: When the informatica server creates memory cache it also creates cache files. For the
following circumstances informatica server creates index and data cache files.

Aggregator transformation
Joiner transformation
Rank transformation
Lookup transformation

86. In which circumstances that informatica server creates Reject files?
Answer: When it encounters the DD_Reject in update strategy transformation.
Violates database constraint
Filed in the rows was truncated or overflowed.

87. What is polling?
Answer: It displays the updated information about the session in the monitor window. The monitor

      Informatica FAQ
window displays the status of each session when U poll the informatica server

88. Can u copy the session to a different folder or repository?
Answer: Yes. By using copy session wizard u can copy a session in a different folder or repository. But
target folder or repository should consists of mapping of that session.
If target folder or repository is not having the mapping of copying session ,
u should have to copy that mapping first before u copy the session
89. What is batch and describe about types of batches?
Answer: Grouping of session is known as batch. Batches r two types

Sequential: Runs sessions one after the other
Concurrent: Runs session at same time.
If u has sessions with source-target dependencies u have to go for sequential batch to start the
sessions one after another. If u have several independent session’s u can use concurrent batches.
Which runs all the sessions at the same time

90. Can u copy the batches?
Answer: NO

91How many number of sessions that u can create in a batch?
Answer: Any number of sessions..

92. 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.

93. What is a command that used to run a batch?
Answer: pmcmd is used to start a batch.

94. What r the different options used to configure the sequential batches?
Answer: Two options
Run the session only if previous session completes successfully. Always runs the session.

95. In a sequential batch can u run the session if previous session fails?
Answer: Yes. By setting the option always runs the session.

96. Can u start batches with in a batch?
Answer: U can not. If u wants to start batch that resides in a batch, create a new independent batch
and copy the necessary sessions into the new batch.

97. Can u start a session inside a batch individually?
Answer: We can start our required session only in case of sequential batch. In case of concurrent batch
we cant do like this.

98. How can u stop a batch?
Answer: By using server manager or pmcmd.

99:What r the session parameters?
Session parameters r like mapping parameters, represent values U might want to change between
sessions such as database connections or source files. Server manager also allows U to create user
defined session parameters. Following r user defined session parameters.

Table 18-1. Naming Conventions for User-Defined Session Parameters

Parameter Type                       Naming Convention

Database Connection                  $DBConnectionName

Source File                          $InputFileName

      Informatica FAQ
 Target File                           $OutputFileName

 Lookup File                           $LookupFileName

 Reject File                           $BadFileName

100: What is parameter file?
Answer: Parameter file is to define the values for parameters and variables used in a session. A
file is a file created by text editor such as word pad or notepad.
U can define the following values in parameter file
mapping parameters
mapping variables
session parameters

101. What r the transformations that restricts the partitioning of sessions?
Answer: Advanced External procedure transformation and External procedure transformation: This
transformation contains a check box on the properties tab to allow partitioning.
Aggregator Transformation: If u use sorted ports u can not partition the associated source
Joiner Transformation: U can not partition the master source for a joiner transformation
Normalizer Transformation
XML targets.

102: What is difference between portioning of relational target and partitioning of file targets?
Answer: If u partition a session with a relational target informatica server creates multiple
connections to the target database to write target data concurently.
          If u partition a session with a file target the informatica server creates one target file for
each partition’s can configure session properties to merge these target files.

103: How can u access the remote source into U’r session?
Answer: Relational source: To access relational source which is situated in a remote place, u need to
configure database connection to the data source.
File Source: To access the remote source file U must configure the FTP connection to the host machine before
u create the session.
Heterogeneous: When U’r mapping contains more than one source type, the server manager creates
a heterogeneous session that displays source options for all types.

104: 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 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 often affect on session performance. So avoid network

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 data sources: Minimize the connections to sources, targets and informatica server to
improve session performance. Moving target database into server system may improve session
Staging areas: If u use staging areas u force informatica server to perform multiple data passes.
Removing of staging areas may improve session performance.

U can run the multiple informatica servers against the same repository.Distibuting the session load
to multiple informatica servers may improve session performance.
Run the informatica server in ASCII data movement mode improves the session performance.
Because ASCII data movement 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
      Informatica FAQ
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 r 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 parallel sessions by using concurrent batches will also reduce the time of loading the
data. So concurrent batches may also increase the session performance.

Partitioning the session improves the session performance by creating multiple connections to
sources and targets and loads data in parallel pipe lines.

In some cases if a session contains a aggregator transformation, u can use incremental aggregation
to improve session performance.

Avoid transformation errors to improve the session performance.

If the session contained lookup transformation u 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 u can use filter condition in source qualifier.

Aggreagator, Rank and joiner transformation may often decrease the session performance .Because
they must group data before processing it. To improve session performance in this case use sorted
ports option.

105: What is difference between maplet and reusable transformation?
Answer: Maplet consists of set of transformations that is reusable. A reusable transformation is a
single transformation that can be reusable.
If u create a variables or parameters in maplet that can not be used in another mapping or
maplet.Unlike the variables that r created in a reusable transformation can be useful in any other
mapping or maplet.
We can not include source definitions in reusable transformations. But we can add sources to a
Whole transformation logic will be hided in case of maplet.But it is transparent in case of reusable
We can’t use COBOL source qualifier, joiner, and normalizer transformations in maplet.Where as we
can make them as reusable transformations.

Q106: Define informatica repository?
Answer: 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.These table’s
stores metadata in specific format the informatica server, client tools use.

Q107: What r the types of metadata that stores in repository?
Following r the types of metadata that stores in the repository
Database connections
Global objects
Multidimensional metadata
      Informatica FAQ
Reusable transformations
Sessions and batches
Short cuts
Source definitions
Target definitions
108) What is power center repository?
Answer: The Power Center repository allows you to share metadata across repositories to create a
data mart domain. In a data mart domain, you 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.

Q109: How can u work with remote database in informatica? Did u work directly by using remote
Answer: To work with remote data source u need to connect it with remote connections. But it is not
preferable to work with that remote source directly by using remote connections .Instead u brings
that source into U r local machine where informatica server resides. If u works directly with remote
source the session performance will decreases by passing less amount of data across the network in
a particular time.

110. What r the new features in Informatica 5.0?
U can Debug U’r mapping in mapping designer
U can view the work space over the entire screen
The designer displays a new icon for a invalid mappings in the navigator window
U can use a dynamic lookup cache in a lookup transformation
Create mapping parameters or mapping variables in a mapping or maplet to make mappings more
U can export objects into repository and import objects from repository. When u exports a
repository object, the designer or server manager creates an XML file to describe the repository
The designer allows u to use Router transformation to test data for multiple conditions. Router
transformation allows u route groups of data to transformation or target.
U can use XML data as a source or target.
Server Enhancements:
U can use the command line program pmcmd to specify a parameter file to run sessions or batches.
This allows you to change the values of session parameters, and mapping parameters and variables
at runtime.
If you run the Informatica Server on a symmetric multi-processing system, you can use multiple
CPUs to process a session concurrently. You configure partitions in the session properties based on
source qualifiers. The Informatica Server reads, transforms, and writes partitions of data in parallel
for a single session. This is available for Power center only.
Informatica server creates two processes like load manager process, DTM process to run the
Metadata Reporter: It is a web based application which is used to run reports against repository
U can copy the session across the folders and repositories using the copy session wizard in the
informatica server manager
With new email variables, you can configure post-session email to include information, such as the
mapping used during the session

 111. What is incremental aggregation?
Answer: When using incremental aggregation, you apply captured changes in the source to
aggregate calculations in a session. If the source changes only incrementally and you can capture
changes, you can configure the session to process only those changes. This allows the Informatics
Server to update your target incrementally, rather than forcing it to process the entire source and
recalculate the same calculations each time you run the session.

        Informatica FAQ
112. What r the scheduling options to run a session?
Answer: U can schedule a session to run at a given time or intervel, or u can manually run the
Different options of scheduling
Run only on demand: server runs the session only when user starts session explicitly
Run once: Informatica server runs the session only once at a specified date and time.
Run every: Informatica server runs the session at regular intervals as u configured.
Customized repeat: Informatica server runs the session at the dates and times specified in the
repeat dialog box.

113.What is tracing level and what r the types of tracing level?
Answer: Tracing level represents the amount of information that informatcia server writes in a log

             PowerCenter Server logs initialization and status information, errors encountered, and
 Normal      skipped rows due to transformation row errors. Summarizes session results, but not at
             the level of individual rows.

             PowerCenter Server logs initialization information as well as error messages and
             notification of rejected data.

 Verbose     In addition to normal tracing, PowerCenter Server logs additional initialization details,
 Init        names of index and data files used, and detailed transformation statistics.

             In addition to verbose initialization tracing, PowerCenter Server logs each row that
             passes into the mapping. Also notes where the PowerCenter Server truncates string
             data to fit the precision of a column and provides detailed transformation statistics.
             Allows the PowerCenter Server to write errors to both the session log and error log
             when you enable row error logging.
             When you configure the tracing level to verbose data, the PowerCenter Server writes
             row data for all rows in a block when it processes a transformation.

114.What is difference between stored procedure transformation and external procedure
Answer: In case of stored procedure transformation procedure will be compiled and executed in a
relational data source’s need data base connection to import the stored procedure in to u’r
maping.Where as in external procedure transformation procedure or function will be executed out
side of data source.Ie u need to make it as a DLL to access in u r maping.No need to have data base
connection in case of external procedure transformation.

115. Explain about Recovering sessions?
Answer: If you stop a session or if an error causes a session to stop, refer to the session and error
logs to determine the cause of failure. Correct the errors, and then complete the session. The
method you use to complete the session depends on the properties of the mapping, session, and
Informatica Server configuration.

Use one of the following methods to complete the session:
· Run the session again if the Informatica Server has not issued a commit.
· Truncate the target tables and run the session again if the session is not recoverable.
· Consider performing recovery if the Informatica Server has issued at least one commit.

116. 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 runs 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.

     Informatica FAQ
117. Explain about Recovering sessions?
If you stop a session or if an error causes a session to stop, refer to the session and error logs to
determine the cause of failure. Correct the errors, and then complete the session. The method you
use to complete the session depends on the properties of the mapping, session, and Informatica
Server configuration.

Use one of the following methods to complete the session:
· Run the session again if the Informatica Server has not issued a commit.
· Truncate the target tables and run the session again if the session is not recoverable.
· Consider performing recovery if the Informatica Server has issued at least one commit.

118. 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 sessions.
To recover sessions using the menu:
1. In the Server Manager, highlight the session you want to recover.
2. Select Server Requests-Stop from the menu.
3. With the failed session highlighted, select Server Requests-Start Session in Recovery Mode from
the menu.
To recover sessions using pmcmd:
1.From the command line, stop the session.
2. From the command line, start recovery.

119. How can u recover the session in sequential batches?
Answer: If you configure a session in a sequential batch to stop on failure, you can run recovery
starting with the failed session. The Informatica Server completes the session and then runs the rest
of the batch. Use the Perform Recovery session property
To recover sessions in sequential batches configured to stop on failure:
1.In the Server Manager, open the session property sheet.
2. On the Log Files tab, select Perform Recovery, and click OK.
3. Run the session.
4. After the batch completes, open the session property sheet.
5. Clear Perform Recovery, and click OK.

If you do not clear Perform Recovery, the next time you run the session, the Informatica Server
attempts to recover the previous session.
If you do not configure a session in a sequential batch to stop on failure, and the remaining sessions
in the batch complete, recover the failed session as a standalone session.

120. How to recover sessions in concurrent batches?
If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the
batch again. However, if a session in a concurrent batch fails and the rest of the sessions complete
successfully, you can recover the session as a standalone session.

To recover a session in a concurrent batch:
1.Copy the failed session using Operations-Copy Session.
2. Drag the copied session outside the batch to be a standalone session.
3. Follow the steps to recover a standalone session.
4. Delete the standalone copy.

121. How can u complete unrecoverable sessions?
Answer: Under certain circumstances, when a session does not complete, you need to truncate the
target tables and run the session from the beginning. Run the session from the beginning when the
Informatica Server cannot run recovery or when running recovery might result in inconsistent data.

122. What r the circumstances that infromatica server results an unrecoverable session?
      Informatica FAQ
Answer: The source qualifier transformation does not use sorted ports.
If u changes 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 concurrent batches contains multiple failed sessions.

123. If I done any modifications for my table in back end do it reflect in informatca warehouse or
maping designer 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, again u have to import from back end to informatica by valid connection. And u has to
replace the existing files with imported files.
124. After dragging the ports of three sources (sol server, oracle, informix) to a single source
qualifier, can u map these three ports directly to target?
Answer: NO. Unless and until u joins those three ports in source qualifier u cannot map them

125. What is Data cleansing..?
Answer: The process of finding and removing or correcting data that is incorrect, out-of-date,
redundant, incomplete, or formatted incorrectly

126. How can we partition a session in Informatica?
The Informatica® Power Center® 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 Power Center Partitioning option
enables hardware and applications to provide outstanding performance and jointly scale to handle
large volumes of data and users.

127. What is a time dimension? Give an example.
In a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup,
and week lookups are not merged as a single table. In a dimensional data modeling (star schema),
these tables would be merged as a single table called TIME DIMENSION for performance and slicing

This dimension helps to find the sales done on date, weekly, monthly and yearly basis. We can
have a trend analysis by comparing this year sales with the previous year or this week sales with
the previous week.

128. Diff between informatica repository server & informatica server
Informatica Repository Server: It’s manages connections to the repository from client application.
Informatica Server: It’s extracts the source data, performs the data transformation, and loads the
transformed data into the target
129. Explain the informatica Architecture in detail

130. Discuss the advantages & Disadvantages of star & snowflake schema?
In a star schema every dimension will have a primary key.
In a star schema, a dimension table will not have any parent table.
Whereas in a snow flake schema, a dimension table will have one or more parent tables.
Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
Whereas hierarchies are broken into separate tables in snow flake schema. These hierarchies help
to drill down the data from topmost hierarchies to the lowermost hierarchies.

131. What are main advantages and purpose of using Normalizer Transformation in Informatica?

      Informatica FAQ
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 single row of data

132. How to read rejected data or bad data from bad file and reload it to target?

133. How do you transfer the data from data warehouse to flat file?

134. At the max how many transformations can be us in a mapping?

135. What is the difference between Normal load and Bulk load?

136. what is a junk dimension

137 wat is perform recovery?
When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table
and notes the row ID of the last row committed to the target database.
The Informatica Server then reads all sources again and starts processing from the next row ID. For
example, if the Informatica Server commits 10,000 rows before the
session fails, when you run recovery, the Informatica Server bypasses the rows up to 10,000 and
starts loading with row 10,001.
By default, Perform Recovery is disabled in the Informatica Server setup. You must enable Recovery
in the Informatica Server setup before you run a session so the
Informatica Server can create and/or write entries in the OPB_SRVR_RECOVERY table.

138.What are the methods for creating reusable transforamtions?
Two methods:-
1.Design it in the transformation developer.
2.Promote a standard transformation from the mapping designer.After you add a transformation to
the mapping , You can promote it to the status of reusable transformation.
Once you promote a standard transformation to reusable status,You can demote it to a standard
transformation at any time.
If you change the properties of a reusable transformation in mapping,You can revert it to the
original reusable transformation properties by clicking the revert button.

139.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.
     Informatica FAQ
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.

140.What is the Rankindex in Ranktransformation?
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. For example, if you create a Rank transformation that ranks the top 5
salespersons for each quarter, the rank index numbers the salespeople from 1
to 5.

1. What is a staging area? Do we need it? What is the purpose of a staging area?
 Staging area is place where you hold temporary tables on data warehouse server. Staging tables
are connected to work area or fact tables. We basically need staging area to hold the data, and
perform data cleansing and merging, before loading the data into warehouse.

Staging area is place where you hold temporary tables on data warehouse server. Staging tables
are connected to work area or fact tables. We basically need staging area to hold the data, and
perform data cleansing and merging, before loading the data into warehouse.

In the absence of a staging area, the data load will have to go from the OLTP system to the OLAP
system directly, which in fact will severely hamper the performance of the OLTP system. This is
the primary reason for the existence of a staging area. In addition, it also offers a platform for
carrying out data cleansing.

 Staging area is a temp schema used to
 1. Do Flat mapping i.e. dumping all the OLTP data in to it without applying any business rules.
 Pushing data into staging will take less time because there are no business rules or transformation
 applied on it.

 2. Used for data cleansing and validation using First Logic

 A staging area is like a large table with data separated from their sources to be loaded into a data
 warehouse in the required format. If we attempt to load data directly from OLTP, it might mess
 up the OLTP because of format changes between a warehouse and OLTP. Keeping the OLTP data
 intact is very important for both the OLTP and the warehouse.

 According to the complexity of the business rule, we may require staging area, the basic need of
 staging area is to clean the OLTP source data and gather in a place. Basically it’s a temporary
 database area. Staging area data is used for the further process and after that they can be

2. RE: What is a three tier data warehouse?
A data warehouse can be thought of as a three-tier system in which a middle system provides usable
data in a secure way to end users. On either side of this middle system are the end users and the back-
end data stores.

Three tier data warehouse contains three tier such as bottom tier, middle tier and top tier.
Bottom tier deals with retrieving related dates or information from various information repositories by
using SQL.
Middle tier contains two types of servers.
1. ROLAP server
2.MOLAP server
Top tier deals with presentation or visualization of the results.

The 3 tiers are:
1. Data tier - bottom tier - consists of the database
2. Application tier - middle tier - consists of the analytical server
      Informatica FAQ
3. Presentation tier - tier that interacts with the end-user

3. RE: What are the various methods of getting incremental records or delta records from the source
getting incremental records from source systems to target can be done
by using incremental aggregation transformation
One foolproof method is to maintain a field called 'Last Extraction Date' and then impose a
condition in the code saying 'current_extraction_date > last_extraction_date'.

 Here is an Oracle specific technique-
 Its called Change Data Capture
4. What are the various tools? - Name a few
A few more ...
- Cognos Decision Stream
- Oracle Warehouse Builder
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Ser
The various ETL tools are as follows.
Business Objects Data Integrator
OLAp tools are as follows.
Business Objects

5.What is the difference between Power Center & Power Mart?
Power Mart is designed for:
 Low range of warehouses
only for local repositories
mainly desktop environment.
Power mart is designed for:
 High-end warehouses
Global as well as local repositories
ERP support.

6. What are active transformation / Passive transformations?
An active transformation can change the number of rows as output after a transformation, while a
passive transformation does not change the number of rows and passes through the same number
of rows that was given to it as input.

7. Can Informatica load heterogeneous targets from heterogeneous sources?
Informatica can load Heterogeneous Targets from heterogeneous Sources
8. How do we call shell scripts from informatica?
You can use a Command task to call the shell scripts, in the following ways:
1. Standalone Command task. You can use a Command task anywhere in the workflow or work let
to run shell commands.
2. Pre- and post-session shell command. You can call a Command task as the pre- or post-session
shell command for a Session task. For more information about specifying pre-session and post-
session shell commands

9. What is Informatica Metadata and where is it stored?
Informatica Metadata contains all the information about the source tables, target tables, the
transformations, so that it will be useful and easy to perform transformations during the ETL
 The Informatica Metadata is stored in Informatica repository.

10. What is a mapping, session, work let, workflow, mapplet?

      Informatica FAQ
Mapping - represents the flow and transformation of data from source to target.
Mapplet - a group of transformations that can be called within a mapping.
Session - a task associated with a mapping to define the connections and other configurations for
that mapping.
Workflow - controls the execution of tasks such as commands, emails and sessions.
Worklet - a workflow that can be called within a workflow.

11. How can we use mapping variables in Informatica? Where do we use them?
  After creating a variable, we can use it in any expression in a mapping or a mapplet. Als they can
be used in source qualifier filter, user defined joins or extract overrides and in expression editor of
reusable transformations.
Their values can change automatically between sessions.
The Informatica server saves the value of mapping variable to the repository at the end of session
run and uses that value next time we run the session
12. Parameter file is any text file where u can define a value for the parameter defined in the
informatica session, this parameter file can be referenced in the session properties, When the
informatica sessions runs the values for the parameter is fetched from the specified file.
You can define the following heading formats:
Workflow variables:
[folder name.WF:workflow name]
Worklet variables:
[folder name.WF:workflow name.WT:worklet name]
Worklet variables in nested worklets:
[folder name.WF:workflow name.WT:worklet name.WT:worklet name...]
Session parameters, plus mapping parameters and variables:
[folder name.WF:workflow name.ST:session name]
[folder name.session name]
[session name]

Below each heading, you define parameter and variable values as follows:
parameter name=value
parameter2 name=value
variable name=value
variable2 name=value

13. Can we override a native sql query within Informatica? Where do we do it? How do we do it?
we can override a sql query in the sql override property of a source qualifier

14. Can we use procedural logic inside Infromatica? If yes how, if now how can we use external
procedural logic in informatica?
We can use External Procedure Transformation to use external procedures. Both COM and
Informatica Procedures are supported using External procedure Transformation

15. Do we need an ETL tool? When do we go for the tools in the market?
     ETL Tools are meant to extract, transform and load the data into Data Warehouse for decision
making. Before the evolution of ETL Tools, the above mentioned ETL process was done manually by
using SQL code created by programmers. This task was tedious and cumbersome in many cases
since it involved many resources, complex coding and more work hours. On top of it, maintaining
the code placed a great challenge among the programmers.
     These difficulties are eliminated by ETL Tools since they are very powerful and they offer many
advantages in all stages of ETL process starting from extraction, data cleansing, data profiling, and
transformation, debugging and loading into data warehouse when compared to the old method.

16. How do we extract SAP data Using Informatica? What is ABAP? What are IDOCS?
 Using power Connect
17. How to determine what records to extract?
When addressing a table some dimension key must reflect the need for a record to get extracted.
Mostly it will be from time dimension (e.g. date >= 1st of current mth) or a transaction flag (e.g.
Order Invoiced Stat). Foolproof would be adding an archive flag to record which gets reset when

     Informatica FAQ
record changes.

18. What is Full load & Incremental or Refresh load?
Full Load is the entire data dump load taking place the very first time.
Gradually to synchronize the target data with source data, there are further 2 techniques:-
Refresh load - Where the existing data is truncated and reloaded completely.
Incremental - Where delta or difference between target and source data is dumped at regular
intervals. Timestamp for previous delta load has to be maintained.

19. Techniques of Error Handling - Ignore, Rejecting bad records to a flat file, loading the records
and reviewing them (default values)
Rejection of records either at the database due to constraint key violation or the informatica server
when writing data into target table. These rejected records we can find in the bad files folder where
a reject file will be created for a session. We can check why a record has been rejected. And this
bad file contains first column a row indicator and second column a column indicator.
These row indicators or of four types
D-valid data,
O-overflowed data,
N-null data,
T- Truncated data,
And depending on these indicators we can changes to load data successfully to target.

20. What are snapshots? What are materialized views & where do we use them? What is a
materialized view log?
A snapshot is a table that contains the
results of a query of one or more tables or views, often located on
a remote database.

21. What is partitioning? What are the types of partitioning?
Partitioning is a part of physical data warehouse design that is carried out to improve performance
and simplify stored-data management. Partitioning is done to break up a large table into smaller,
independently-manageable components because it:
1. reduces work involved with addition of new data.
2. reduces work involved with purging of old data.
Two types of partitioning are:
1. Horizontal partitioning.
2. Vertical partitioning (reduces efficiency in the context of a data warehouse).

If you use PowerCenter, you can increase the number of partitions in a pipeline to improve session
performance. Increasing the number of partitions allows the Informatica Server to create multiple
connections to sources and process partitions of source data concurrently.

       When you create a session, the Workflow Manager validates each pipeline in the mapping for
partitioning. You can specify multiple partitions in a pipeline if the Informatica Server can maintain
data consistency when it processes the partitioned data.
       When you configure the partitioning information for a pipeline, you must specify a partition
type at each partition point in the pipeline. The partition type determines how the Informatica
Server redistributes data across partition points.

The Workflow Manager allows you to specify the following partition types:
Round-robin partitioning. The Informatica Server distributes data evenly among all partitions. Use
round-robin partitioning where you want each partition to process approximately the same number
of rows.
Hash partitioning. The Informatica Server applies a hash function to a partition key to group data
among partitions. If you select hash auto-keys, the Informatica Server uses all grouped or sorted
ports as the partition key. If you select hash user keys, you specify a number of ports to form the
partition key. Use hash partitioning where you want to ensure that the Informatica Server processes
groups of rowswith the same partition key in the same partition.
Key range partitioning. You specify one or more ports to form a compound partition key. The
Informatica Server passes data to each partition depending on the ranges you specify for each port.
Use key range partitioning where the sources or targets in the pipeline are partitioned by key range.
For more information, see Key Range Partitioning.
     Informatica FAQ
Pass-through partitioning. The Informatica Server passes all rows at one partition point to the
next partition point without redistributing them. Choose pass-through partitioning where you want
to create an additional pipeline stage to improve performance, but do not want to change the
distribution of data across partitions.

22. When do we Analyze the tables? How do we do it?
When the data in the data warehouse changes frequently we need to analyze the tables. Analyze
tables will compute/update the table statistics, that will help to boost the performance of your SQL.


Shared By: