MAPPING 

1. SCENARIO IMPLEMENTATION 1 
2. WHAT ARE MAPPING PARAMETERS AND VARIABLES? 
4. WHAT ARE THE DEFAULT VALUES FOR VARIABLES? 
5. WHAT DOES FIRST COLUMN OF BAD FILE (REJECTED ROWS) INDICATES? 
6. OUT OF 100000 SOURCE ROWS SOME ROWS GET DISCARD AT TARGET, HOW WILL YOU TRACE THEM AND WHERE IT GETS LOADED?

7. WHAT IS REJECT LOADING? 
8. WHY INFORMATICA WRITER THREAD MAY REJECT A RECORD? 
9. WHY TARGET DATABASE CAN REJECT A RECORD? 
10. DESCRIBE VARIOUS STEPS FOR LOADING REJECT FILE? 
11. VARIABLE V1 HAS VALUES SET AS 5 IN DESIGNER (DEFAULT), 10 IN PARAMETER FILE, AND 15 IN REPOSITORY. WHILE RUNNING
SESSION WHICH VALUE INFORMATICA WILL READ? 
12. WHAT ARE SHORTCUTS? WHERE IT CAN BE USED? WHAT ARE THE ADVANTAGES? 
13. CAN WE HAVE AN INFORMATICA MAPPING WITH TWO PIPELINES, WHERE ONE FLOW IS HAVING A TRANSACTION CONTROL
TRANSFORMATION AND ANOTHER NOT. EXPLAIN WHY? 
14. HOW CAN WE IMPLEMENT REVERSE PIVOTING USING INFORMATICA TRANSFORMATIONS? 
15. IS IT POSSIBLE TO UPDATE A TARGET TABLE WITHOUT ANY KEY COLUMN IN TARGET? 

151.Mapping development best practices

• Source Qualifier – use shortcuts, extract only the necessary data, limit read of columns and rows on source. Try to use the default query options (User Defined Join, Filter) instead of using SQL Query override which may impact database resources and make unable to use partitioning and push-down.
• Expressions – use local variables to limit the amount of redundant calculations, avoid datatype conversions, reduce invoking external scripts (coding outside of Informatica), provide comments, use operators (||, +, /) instead of functions. Keep in mind that numeric operations are generally faster than string operations.
• Filter – use the Filter transformation as close to the source as possible. If multiple filters need to be applied, usually it’s more efficient to replace them with Router.
• Aggregator – use sorted input, also use as early (close to the source) as possible and filter the data before aggregating.
• Joiner – try to join the data in Source Qualifier wherever possible, and avoid outer joins. It is good practice to use a source with fewer rows, such as a Master source.
• Lookup – relational lookup should only return ports that meet the condition. Call Unconnected Lookup in expression (IIF). Replace large lookup tables with joins whenever possible. Review the database objects and add indexes to database columns when possible. Use Cache Calculator in session to eliminate paging in lookup cache.

152.At The Max How Many Transformations Can Be Us In A Mapping?In a mapping we can use any number of transformations depending on the project, and the included transformations in the particular related transformations.

.

 156.What is the difference between Mapping and Mapplet?

 

157.What is complex mapping?

Following are the features of complex mapping.

• Difficult requirements
• Many numbers of transformations
• Complex business logic

158. How can one identify whether mapping is correct or not without connecting session?

One can find whether the session is correct or not without connecting the session is with the help of debugging option.

159. Can one use mapping parameter or variables created in one mapping into any other reusable transformation?

Yes, One can do because reusable transformation does not contain any mapplet or mapping.

160. How can you validate all mappings in the repository simultaneously?

All the mappings cannot be validated simultaneously because each time only one mapping can be validated.

161.How Do You Configure Mapping In Informatica?

• Import src from database.
• Check if target table already exists in database.
• If it exists make sure u delete data from it and import into designer.
• Or else create it with create target wizard.
• Now you can drag needed transformations into the workspace.
• Use them according to your purpose.

For improved performance follow these tips:-

• Use sorter before aggregator
• If filter is used keep it as near to the source as possible.
• If possible use an extra expression tr before target to make corrections in future.
• Enable sorted input option if sorter is used before agg tr.
• If more filters are needed use router tr.
• you can use source filter option of SQ if filter tr is immediately after source.
• In case of router if not needed do not connect default group to any target.

162. Scenario Implementation 1
Suppose we have a source port called ename with data type varchar(20) and the corresponding target port as ename with varchar(20). The data type is now altered to varchar(50) in both source and target database.
Describe the changes required to modify the mapping.

Reimport the source and target definition. Next open the mapping and Right click on the source port ename and use “Propagate Attribute” option. This option allows us to change the properties of one port across multiple transformations without manually modifying the port in each and every transformation. We can choose the direction of propagation (forward / backward / both) and can also select attributes of propagation e.g.
data type, scale, precision etc.
163. What are mapping parameters and variables?

• A mapping parameter is a user definable constant that takes up a value before running a session.
• It can be used in SQ expressions, Expression transformation etc.
• A mapping variable is also defined similar to the parameter except that the value of the variable is subjected to change. It picks up the value in the following order.
  •  From the Session parameter file
  •  As stored in the repository object in the previous run
  •  As defined in the initial values in the designer
  •  Data type Default values

 

When values change during the session’s execution it’s called a mapping variable. Upon completion the Informatica server stores the end value of a variable and is reused when session restarts. Moreover those values that do not change during the sessions execution are called mapping parameters. Mapping procedure explains mapping parameters and their usage. Values are allocated to these parameters before starting the session.
164. Which type of variables or parameters can be declared in parameter file?
$, $$, $$$ – Can all be declared or not.

There is a difference between variable and parameter.
Variable, as the name suggests, is like a variable value which can change within a session run.
Parameters are fixed and their values don’t change during session run.
$ – for session level parameters which can be declared in parameter files.
$$ – for mapping level parameters which can be declared in parameter files.
 $$$- Inbuilt Informatica system variables that cannot be declared in parameter files
E.g. $$$SessStartTime these are constant throughout the mapping and cannot be chnaged

165. What are the default values for variables?

String = Null
Number = 0
Date = 1/1/1753
166. What does first column of bad file (rejected rows) indicates?

First Column – Row indicator (0, 1, 2, 3)
Second Column – Column Indicator (D, O, N, T)
167. Out of 100000 source rows some rows get discard at target, how will you trace them and where it gets loaded?

•  Rejected records are loaded into bad files. It has record indicator and column indicator.
• Record indicator identified by (0-insert,1-update,2-delete,3-reject) and
• Column indicator identified by (D-valid,O-overflow,N-null,T-truncated).
•  Normally data may get rejected in different reason due to transformation logic

168. What is Reject loading?
During a session, the Informatica server creates a reject file for each target instance in the mapping. If the writer or the target rejects data, the Informatica server writes the rejected row into reject file. The reject file and session log contain information that helps you determine the cause of the reject. You can correct reject files and load them to relational targets using the Informatica reject load utility. The reject loader also creates
another reject file for the data that the writer or target reject during the reject loading.
Reject Loading During a session, the server creates a reject file for each target instance in the mapping. If the writer of the target rejects data, the server writers the rejected rows into the reject file. You can correct those rejected data and re-load them to relational targets, using the reject loading utility. (You cannot load rejected data into
a flat file target) Each time, you run a session, the server appends a rejected data to the reject file.
Locating the BadFiles
$PMBadFileDir / Filename.bad
When you run a partitioned session, the server creates a separate reject file for each partition.
Reading Rejected data
Ex: 3,D,1,D,D,0,D,1094345609,D,0,0.00
To help us in finding the reason for rejecting, there are two main things.
Row indicator – Row indicator tells the writer, what to do with the row of wrong data.
Row indicator Meaning Rejected By
o 0 Insert Writer or target
o 1 Update Writer or target
o 2 Delete Writer or target
o 3 Reject Writer
If a row indicator is 3, the writer rejected the row because an update strategy expression marked it for reject.
Column indicator – Column indicator is followed by the first column of data, and another column indicator.
They appears after every column of data and define the type of data preceding it
Column Indicator Meaning Writer Treats as
o D Valid Data Good Data. The target accepts it unless a database error occurs, such as finding duplicate key.
o Overflow Bad Data.
o N Null Bad Data.
o T Truncated Bad Data
NOTE: NULL columns appear in the reject file with commas marking their column.
Correcting Reject File Use the reject file and the session log to determine the cause for rejected data. Keep in mind that correcting the reject file does not necessarily correct the source of the reject. Correct the mapping and target database to eliminate some of the rejected data when you run the session again. Trying to correct target rejected
rows before correcting writer rejected rows is not recommended since they may contain misleading column indicator. For example, a series of “N” indicator might lead you to believe the target database does not accept NULL values, so you decide to change those NULL values to Zero. However, if those rows also had a 3 in row indicator. Column, the row was rejected b the writer because of an update strategy expression, not
because of a target database restriction. If you try to load the corrected file to target, the writer will again reject those rows, and they will contain inaccurate 0 values, in place of NULL values.

169. Why Informatica writer thread may reject a record?

• Data overflowed column constraints
•  An update strategy expression

170. Why target database can reject a record?

• Data contains a NULL column
•  Database errors, such as key violations

171.Describe various steps for loading reject file?

•  After correcting the rejected data, rename the rejected file to reject_file.in
•  The rejloader used the data movement mode configured for the server.

It also used the code page of server/OS. Hence do not change the above, in middle of the reject loading  Use the reject loader utility Pmrejldr pmserver.cfg [folder name] [session name]
172.Variable v1 has values set as 5 in designer (default), 10 in parameter file, and 15 in repository.While running session which value Informatica will read?

Informatica read value 15 from repository
173.What are shortcuts? Where it can be used? What are the advantages?

• There are 2 shortcuts (Local and global) Local used in local repository and global used in global repository.
• The advantage is reusing an object without creating multiple objects. Say for example a source definition want to use in 10 mappings in 10 different folders without creating 10 multiple source you create 10 shortcuts.

 

174.Can we have an Informatica mapping with two pipelines, where one flow is having a
Transaction Control transformation and another not. Explain why?

• No it is not possible. Whenever we have a Transaction Control transformation in a mapping, the session commit type is ‘User Defined’.
• Whereas for a pipeline without the Transaction Control transform, the session expects the commit type to be either Source based or Target based.
  Hence we cannot have both the pipelines in a single mapping; rather we have to develop single mappings for each of the pipelines.

175.How can we implement Reverse Pivoting using Informatica transformations?

Pivoting can be done using Normalizer transformation. For reverse-pivoting we will need to use an aggregator transformation like below:

176.Is it possible to update a Target table without any key column in target?

Yes it is possible to update the target table either by defining keys at Informatica level in Warehouse designer or by using Update Override.