Informatica Interview Questions Part -8
1) issues while working with flat files in Informatica?
- We cannot use SQL override while using flat files as source.
- We need to specify correct path in the session and have to mention the file is ‘direct’ or ‘indirect’.
- And keep the file in exact path as mentioned in the session.
- Have to convert the date field to date data type since it reads as string.
2) Explain about Informatica server process that how it works relates to mapping variables?
Informatica primary uses load manager and data transformation manager
(dtm) to perform extracting, transformation and loading. Load manager reads
parameters and variables related to session, mapping and server and passes
the mapping parameters and variable information to the DTM.DTM uses this
information to perform the data movement from source to target
3. write a query to retrieve the latest records from the table sorted by version(scd)
Select * from emp_t a
where a.version = (Select max (b.version) from emp_t b where a.empno=b.empno)
4. How do you handle two sessions in Informatica
Using more than one session by linking it is called as batches. It is of two types
where a.version = (Select max (b.version) from emp_t b where a.empno=b.empno)
4. How do you handle two sessions in Informatica
Using more than one session by linking it is called as batches. It is of two types
- 1. Sequential batches.(session runs one after another)
- 2. Concurrent batches.(session runs at same time)
- We can declare dependencies in between session by giving the command ‘ $PrevTaskStatus = SUCCESSFUL’ in sequential batches.
Can’t compare lookup and joiner since each has its own functions. Sometimes joiner gives more performance and sometimes lookups.
In case of Flat file, generally, sorted joiner is more effective than lookup, because sorted joiner uses join conditions and caches less rows. Lookup caches always whole file. If the file is not sorted, it can be comparable.
In case of database, lookup can be effective if the database can return sorted data fast and the amount of data is small, because lookup can create whole cache in memory. If database responses slowly or big amount of data are processed, lookup cache initialization can be really slow .Then it can be better use sorted joiner, which throws data to output as reads them on input.
6. How to partition the Session?
7. How many types of sessions are there in informatica.please explain them.
Totally there are 10 SESSIONS
In case of Flat file, generally, sorted joiner is more effective than lookup, because sorted joiner uses join conditions and caches less rows. Lookup caches always whole file. If the file is not sorted, it can be comparable.
In case of database, lookup can be effective if the database can return sorted data fast and the amount of data is small, because lookup can create whole cache in memory. If database responses slowly or big amount of data are processed, lookup cache initialization can be really slow .Then it can be better use sorted joiner, which throws data to output as reads them on input.
6. How to partition the Session?
7. How many types of sessions are there in informatica.please explain them.
Totally there are 10 SESSIONS
- 1. SESSION : FOR MAPPING EXECUTION
- 2. EMAIL : TO SEND EMAILS
- 3. COMMAND : TO EXECUTE OS COMMANDS
- 4. CONTROL : FAIL, STOP, ABORT
- 5. EVENT WAIT : FOR PRE_DEFINED OR POST_DEFINED EVENTS
- 6 EVENT RAISE : TO RAISE ACTIVE USER_DEFINED EVENT
- 7. DECISSION : CONDITION TO BE EVALUATED FOR CONTROLING FLOW OR PROCESS
- 8. TIMER : TO HALT THE PROCESS FOR SPECIFIC TIME
- 9. WORKLET TASK: REUSABLE TASK
- 10. ASSIGNEMENT: TO ASSIGN VALUES WORKLET OR WORK FLOW VARIABLES
A pipeline will contain the source, transformations and targets. Mapping will be subdivided into many pipelines are called stage pipeline. After enabling partitioning in stage pipeline when we run the integration service it runs faster and we can expect more performance. The integration service will run the partition threads concurrently.
To enable partition
- · Set the partition points in the mapping
- · Set the number of partitions
- · Set the partition types
When we give Partition points it actually creates various stage pipelines inside the mapping. Now we need to decide how many partitions we need to have inside one stage pipeline.
When we add more partitions it increases the performance of Informatica. You can define up to 64 partitions at any partition point in a pipeline. Also note that if we define two Partitions at any partition point, then the remaining partition points will also have two partitions.
9. Explain about cumulative Sum or moving sum?
CUME (PERSONAL_SALES)
PERSONAL_SALES RETURN VALUE
40000 40000
80000 120000
40000 160000
60000 220000
MOVINGSUM (SALES,2)
ROW_NO SALES RETURN VALUE
1 600 NULL
2 504 1104 (600+504)
3 36 540 (504+36)
4 100 136 (36+100)
5 550 650 (100+550)
6 39 589 (550+39)
7 490 529 (39+490)
10. CONVERT MULTIPLE ROWS TO SINGLE ROW (MULTIPLE COLUMNS) IN INFORMATICA
We take for eg:
Source
Sid
|
code
|
value
|
1
|
A
|
ABC
|
1
|
B
|
BCD
|
1
|
C
|
CCD
|
2
|
A
|
SDC
|
2
|
C
|
ASD
|
3
|
D
|
DFG
|
Target
Sid
|
code A
|
code B
|
code C
|
code D
|
1
|
abc
|
bcd
|
ccd
|
<null>
|
2
|
sdc
|
<null>
|
asd
|
<null>
|
3
|
<null>
|
<null>
|
<null>
|
dfg
|
The flow of mapping is
Sq->Agg->Tgt1, tg2, tgt3…
Use Aggregator
Create Four Ports each having
MAX (DECODE (CODE,'A', VALUE)),
MAX (DECODE (CODE,'B', VALUE)),
MAX (DECODE (CODE,'A', VALUE)),
MAX (DECODE (CODE,'D', VALUE))
Group it by SID
Connect those Four Columns to respective Target Columns
11. DEPLOYMENT GROUPS IN INFORMATICA
Deployment group is a global object which consists of objects from one or more folders. Deployment group is used to copy objects from one or more folders to another folder or repository. You can create, edit, or delete deployment groups. You can copy a deployment group and the objects in the deployment group to a target repository.
Deployment Group Tasks:
You can do the following tasks when working with deployment groups:
- Create deployment group: You can create a global object for deploying objects from one or more folders.
- Edit deployment group: You can modify a deployment group. You can change the static deployment group to dynamic deployment group and vice versa.
- Configure privileges and permissions: Configure permissions on a deployment group and the privilege to copy a deployment group.
- View the objects in a static or dynamic deployment group: You can preview the objects that the Repository Service will deploy.
- Add or remove objects in a static deployment group: You can specify what objects belong to a static deployment group. You can add or remove the objects from the deployment group.
- Associate a query with a dynamic deployment group: Assign a query to a deployment to dynamically update the objects that the group contains.
- View the history of a deployment group: View the history of a deployment group, including the source and target repositories, deployment date, and user who ran the deployment.
- Post-deployment validation: Validate the objects in the target repository after you copy a deployment group to verify that the objects and dependent objects are valid.
- Roll back a deployment group: You can roll back a deployment group to purge deployed versions of objects from the target repository.
12. LOAD LAST N RECORDS OF FILE INTO TARGET TABLE - INFORMATICA
SQ->SG->AGG->ROUTER->TGT
AGG-> MAX(SNO)
ROUTER->MAX(SNO)-N The default port in router will give u the last n records.
13. LOAD ALTERNATIVE RECORDS / ROWS INTO MULTIPLE TARGETS - INFORMATICA
AGG-> MAX(SNO)
ROUTER->MAX(SNO)-N The default port in router will give u the last n records.
13. LOAD ALTERNATIVE RECORDS / ROWS INTO MULTIPLE TARGETS - INFORMATICA
For passing alternative rows in to two targets the flow of the mapping is
SQ->sequence generator ->Router->TGT1,TGT2
Router filter condition is ‘(mod (sno, 2) =0)’ give u the even number rows pass it to one target (TGT1) and the default port of the router gives u odd number rows pass it to TGT2.
14. SCHEDULING OPTIONS OF INFORMATICA ETL TOOL WORKFLOWS / JOBS
SQ->sequence generator ->Router->TGT1,TGT2
Router filter condition is ‘(mod (sno, 2) =0)’ give u the even number rows pass it to one target (TGT1) and the default port of the router gives u odd number rows pass it to TGT2.
14. SCHEDULING OPTIONS OF INFORMATICA ETL TOOL WORKFLOWS / JOBS
15. HOW TO CREATE USER DEFINED FUNCTION (UDF) IN INFORMATICA
User-defined functions are extensions to PowerCenter transformation language. It allows you to create complex expressions using the built-in functions and operators and reuse them in PowerCenter mappings. User-defined functions are two types.
Public : Callable from any user-defined function, transformation expression, link condition expression, or task expression.
Private : Callable from another user-defined function. Create a private function when you want the function to be part of a more complex function. The simple function may not be usable independently of the complex function.
Creating User Defined Functions
You can create a user defined function from Informatica PowerCenter Designer tool. You can invoke the User-Defined Function dialog box from main menu Tools > User-Defined Functions > New. If u want a user defined function to convert the phone number into (XXX) XXX-XXXX format.
We can define the function here to convert PHONE_NUMBER into required string formatted in (XXX) XXX-XXXX. The expression editor shows the logic used within the user defined function. User defined functions can be used in any expressions just like any other functions. You can choose the available user defined functions from the functions explorer.
16. PUSHDOWN OPTIMIZATION $PUSHDOWNCONFIG PARAMETER - INFORMATICA
17. PUSHDOWN OPTIMIZATION AND TYPES IN INFORMATICA
18. HOW TO LOAD ROWS INTO FACT TABLE IN DATA WAREHOUSE
19. PMCMD COMMAND USAGE IN INFORMATICA
20. DIFFERENCE BETWEEN STOP AND ABORT IN INFORMATICA
21. GENERATE DATE / TIME DIMENSION IN INFORMATICA
22. INFORMATICA ARCHITECTURE TUTORIAL - VERSION 8 / 9
23. TUNING LOOKUP TRANSFORMATION - INFORMATICA
24. LOOKUP TRANSFORMATION IS ACTIVE - INFORMATICA
25. CREATING LOOKUP TRANSFORMATION - INFORMATICA
26. CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION - INFORAMTICA
27. LOOKUP TRANSFORMATION SOURCE TYPES IN INFORMATICA
28. LOOKUP TRANSFORMATION IN INFORMATICA
29. TARGET UPDATE OVERRIDE - INFORMATICA
30. UPDATE STRATEGY TRANSFORMATION IN INFORMATICA
31. SQL QUERY OVERWRITE IN SOURCE QUALIFIER - INFORMATICA
32. How to generate sequence numbers without using the sequence generator transformation?
33. REUSABLE VS NON REUSABLE & PROPERTIES OF SEQUENCE GENERATOR TRANSFORMATION
34. How to load the data from a flat file into the target where the source flat file name changes daily?
35. DIRECT AND INDIRECT FLAT FILE LOADING (SOURCE FILE TYPE) - INFORMATICA
36. I have a flat file, want to reverse the contents of the flat file
37. Which means the first record should come as last record and last record should come as first record and load into the target file?
38. MAPPING VARIABLE USAGE EXAMPLE IN INFORMATICA
39. TRANSACTION CONTROL TRANSFORMATION IN INFORMATICA
40. How to load the name of the current processing flat file along with the data into the target using informatica mapping?
41. JOINER TRANSFORMATION IN INFORMATICA
42. How to create or implement slowly changing dimension (SCD) Type 2 Effective Date mapping ininformatica/Flagging mapping in informatica/visioning mapping in informatica?
43. RANK TRANSFORMATION IN INFORMATICA
44. How to create different types of slowly changing dimensions (SCD) in informatica using the mapping wizard?
45. CONSTRAINT BASED LOADING IN INFORMATICA
46. SQL TRANSFORMATION IN SCRIPT MODE EXAMPLES - INFORMATICA
47. How to generate or load values in to the target table based on a column value using informatica etl tool.
48. DYNAMIC TARGET FLAT FILE NAME GENERATION IN INFORMATICA
49. INCREMENTAL AGGREGATION IN INFORMATICA
50. WHAT IS INFORMATICA POWERCENTER
51. What is the need of an ETL tool
52. INFORMATICA PERFORMANCE IMPROVEMENT TIPS
53. What is polling?
54. In which circumstances, informatica server creates Reject files?
55. What are the data movement modes in informatica?
56. Define mapping and session?
57. Can u generate reports in Informatica?
58. What is metadata reporter?
59. What is the default source option for update strategy transformation?
60. What is Data driven?
61. What are the tasks that source qualifier perform?
62. What is the default join that source qualifier provides?
63. What are the basic requirements to join two sources in a source qualifier transformation using default join?
64. What are the differences between joiner transformation and source qualifier transformation?
65. What are the limitations of joiner transformation?
66. What are the settings that you use to configure the joiner transformation?
67. What are the join types in joiner transformation?
68. What are the joiner caches?
69. What is the look up transformation?
70. Why use the lookup transformation?
71. What are the types of lookup transformation?
72. What is meant by lookup caches?
73. What are the types of lookup caches?
74. Which transformation should we use to normalise the COBOL and relational sources?
75. In which transformation you cannot drag ports into it?
76. How the informatica server sorts the string values in Rank transformation?
77. What are the rank caches?
78. What is the Rank index port in Rank transformation?
79. What is the Router transformation?
80. What are the types of groups in Router transformation?
81. What are the types of data that passes between informatica server and stored procedure
82. What is the status code in stored procedure transformation?
83. What is the target load order?
84. While importing the relational source definition from the database, what are the metadata of source that will be imported?
85. How many ways a relational source definition can be updated and what are they?
86. To import the flat file definition into the designer where should the flat file be placed?
87. To provide support for Mainframes source data, which files are used as a source definitions?
88. Which transformation is needed while using the Cobol sources as source definitions?
89. How to create or import flat file definition in to the warehouse designer?
90. What is a mapplet?
91. What is a transformation?
92. What are the designer tools for creating transformations?
93. What are active and passive transformations?
94. What are connected or unconnected transformations?
95. How many ways are there to create ports?
96. What are the reusable transformations?
97. What are the methods for creating reusable transformations?
98. What are the unsupported repository objects for a mapplet?
99. What are the mapping parameters and mapping variables?
100. Can we use the mapping parameters or variables created in one mapping into another mapping?
101. Can we use the mapping parameters or variables created in one mapping into any other reusable transformation?
102. How can we improve session performance in aggregator transformation?
103. What is aggregate cache in aggregator transformation?
104. What is an active transformation?
105. What is a passive transformation?
106. What is a connected transformation?
107. What is an unconnected transformation?
108. What are multi-group transformations?
109. List out all the transformations which use cache?
110. What is blocking transformation?
111. How do you promote a non-reusable transformation to reusable transformation?
112. How to create a non-reusable instance of reusable transformations?
113. Which transformation can be created only as reusable transformation but not as non-reusable transformation?
114. As union transformation gives UNION ALL output, how you will get the UNION output?
115. What are the guidelines to be followed while using union transformation?
116. Why union transformation is an active transformation?
117. What is a transaction control transformation?
118. What is the commit type if you have a transaction control transformation in the mapping?
119. What are the different transaction levels available in transaction control transformation?
120. What is a sorter transformation?
121. Why sorter is an active transformation?
122. How to improve the performance of a session using sorter transformation?
123. How to generate sequence numbers using expression transformation?
124. Design a mapping to load the cumulative sum of salaries of employees into target table?
125. Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.
126. Design a mapping to load a target table with the following values from the above source?
127. What is Data Caches size?
128. What is Index Caches size?
129. Difference between Data and Index Caches?
130. Difference between Dynamic and Static Lookup Cache?
17. PUSHDOWN OPTIMIZATION AND TYPES IN INFORMATICA
18. HOW TO LOAD ROWS INTO FACT TABLE IN DATA WAREHOUSE
19. PMCMD COMMAND USAGE IN INFORMATICA
20. DIFFERENCE BETWEEN STOP AND ABORT IN INFORMATICA
21. GENERATE DATE / TIME DIMENSION IN INFORMATICA
22. INFORMATICA ARCHITECTURE TUTORIAL - VERSION 8 / 9
23. TUNING LOOKUP TRANSFORMATION - INFORMATICA
24. LOOKUP TRANSFORMATION IS ACTIVE - INFORMATICA
25. CREATING LOOKUP TRANSFORMATION - INFORMATICA
26. CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION - INFORAMTICA
27. LOOKUP TRANSFORMATION SOURCE TYPES IN INFORMATICA
28. LOOKUP TRANSFORMATION IN INFORMATICA
29. TARGET UPDATE OVERRIDE - INFORMATICA
30. UPDATE STRATEGY TRANSFORMATION IN INFORMATICA
31. SQL QUERY OVERWRITE IN SOURCE QUALIFIER - INFORMATICA
32. How to generate sequence numbers without using the sequence generator transformation?
33. REUSABLE VS NON REUSABLE & PROPERTIES OF SEQUENCE GENERATOR TRANSFORMATION
34. How to load the data from a flat file into the target where the source flat file name changes daily?
35. DIRECT AND INDIRECT FLAT FILE LOADING (SOURCE FILE TYPE) - INFORMATICA
36. I have a flat file, want to reverse the contents of the flat file
37. Which means the first record should come as last record and last record should come as first record and load into the target file?
38. MAPPING VARIABLE USAGE EXAMPLE IN INFORMATICA
39. TRANSACTION CONTROL TRANSFORMATION IN INFORMATICA
40. How to load the name of the current processing flat file along with the data into the target using informatica mapping?
41. JOINER TRANSFORMATION IN INFORMATICA
42. How to create or implement slowly changing dimension (SCD) Type 2 Effective Date mapping ininformatica/Flagging mapping in informatica/visioning mapping in informatica?
43. RANK TRANSFORMATION IN INFORMATICA
44. How to create different types of slowly changing dimensions (SCD) in informatica using the mapping wizard?
45. CONSTRAINT BASED LOADING IN INFORMATICA
46. SQL TRANSFORMATION IN SCRIPT MODE EXAMPLES - INFORMATICA
47. How to generate or load values in to the target table based on a column value using informatica etl tool.
48. DYNAMIC TARGET FLAT FILE NAME GENERATION IN INFORMATICA
49. INCREMENTAL AGGREGATION IN INFORMATICA
50. WHAT IS INFORMATICA POWERCENTER
51. What is the need of an ETL tool
52. INFORMATICA PERFORMANCE IMPROVEMENT TIPS
53. What is polling?
54. In which circumstances, informatica server creates Reject files?
55. What are the data movement modes in informatica?
56. Define mapping and session?
57. Can u generate reports in Informatica?
58. What is metadata reporter?
59. What is the default source option for update strategy transformation?
60. What is Data driven?
61. What are the tasks that source qualifier perform?
62. What is the default join that source qualifier provides?
63. What are the basic requirements to join two sources in a source qualifier transformation using default join?
64. What are the differences between joiner transformation and source qualifier transformation?
65. What are the limitations of joiner transformation?
66. What are the settings that you use to configure the joiner transformation?
67. What are the join types in joiner transformation?
68. What are the joiner caches?
69. What is the look up transformation?
70. Why use the lookup transformation?
71. What are the types of lookup transformation?
72. What is meant by lookup caches?
73. What are the types of lookup caches?
74. Which transformation should we use to normalise the COBOL and relational sources?
75. In which transformation you cannot drag ports into it?
76. How the informatica server sorts the string values in Rank transformation?
77. What are the rank caches?
78. What is the Rank index port in Rank transformation?
79. What is the Router transformation?
80. What are the types of groups in Router transformation?
81. What are the types of data that passes between informatica server and stored procedure
82. What is the status code in stored procedure transformation?
83. What is the target load order?
84. While importing the relational source definition from the database, what are the metadata of source that will be imported?
85. How many ways a relational source definition can be updated and what are they?
86. To import the flat file definition into the designer where should the flat file be placed?
87. To provide support for Mainframes source data, which files are used as a source definitions?
88. Which transformation is needed while using the Cobol sources as source definitions?
89. How to create or import flat file definition in to the warehouse designer?
90. What is a mapplet?
91. What is a transformation?
92. What are the designer tools for creating transformations?
93. What are active and passive transformations?
94. What are connected or unconnected transformations?
95. How many ways are there to create ports?
96. What are the reusable transformations?
97. What are the methods for creating reusable transformations?
98. What are the unsupported repository objects for a mapplet?
99. What are the mapping parameters and mapping variables?
100. Can we use the mapping parameters or variables created in one mapping into another mapping?
101. Can we use the mapping parameters or variables created in one mapping into any other reusable transformation?
102. How can we improve session performance in aggregator transformation?
103. What is aggregate cache in aggregator transformation?
104. What is an active transformation?
105. What is a passive transformation?
106. What is a connected transformation?
107. What is an unconnected transformation?
108. What are multi-group transformations?
109. List out all the transformations which use cache?
110. What is blocking transformation?
111. How do you promote a non-reusable transformation to reusable transformation?
112. How to create a non-reusable instance of reusable transformations?
113. Which transformation can be created only as reusable transformation but not as non-reusable transformation?
114. As union transformation gives UNION ALL output, how you will get the UNION output?
115. What are the guidelines to be followed while using union transformation?
116. Why union transformation is an active transformation?
117. What is a transaction control transformation?
118. What is the commit type if you have a transaction control transformation in the mapping?
119. What are the different transaction levels available in transaction control transformation?
120. What is a sorter transformation?
121. Why sorter is an active transformation?
122. How to improve the performance of a session using sorter transformation?
123. How to generate sequence numbers using expression transformation?
124. Design a mapping to load the cumulative sum of salaries of employees into target table?
125. Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.
126. Design a mapping to load a target table with the following values from the above source?
127. What is Data Caches size?
128. What is Index Caches size?
129. Difference between Data and Index Caches?
130. Difference between Dynamic and Static Lookup Cache?
Informatica Interview Questions Part -1
Informatica Interview Questions Part -2
Informatica Interview Questions Part -3
Informatica Interview Questions Part -4
Informatica Interview Questions Part -5
Informatica Interview Questions Part -6
Informatica Interview Questions Part -7
Informatica Interview Questions Part -8
Informatica Interview Questions Part -9
Informatica Interview Questions Part -10
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Informatica Interview Questions Part -8 "