INFORMATICA SCENARIOS

INFORMATICA SCENARIOS

1. How to generate sequence numbers using expression transformation?

2. Design a mapping to load the first 3 rows from a flat file into a target?

3. Design a mapping to load the last 3 rows from a flat file into a target?

4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

5.Get top 5 records to target without using rank

6.SEPARATE ROWS ON GROUP BASIS

7.How do you perform incremental logic or Delta or CDC?

8.How to find Date Difference in Hours

9.CURRENCY CONVERTOR

10.CONVERT NUMERIC VALUE TO DATE FORMAT

11.Retrieving first and last record from a table/file

12.Sending first half record to target

13.Sending alternate record to target

14.Separate the original records in target

15.Segregating rows on group count basis

16.Extracting every nth row

17.Sending records to target tables in cyclic order

18.Concatenation of duplicate value by comma separation

19.Target table rows , with each row as sum of all previous rows from source table.

20.Produce files as target with dynamic names

21.Validating all mapping in repository

22.Using mapping parameter and variable in mapping

23.Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

24.Insert and reject records using update strategy.

25.How to change a string to decimal with 2 decimal places in informatica?

26.Differences between dynamic lookup cache and static lookup cache? 

27.What is the difference between joiner and lookup?

28.How do you identify existing rows of data in the target table using lookup transformation? 

29.What is Slowly Changing Dimensions (SCD) ? 

30.What is Dimension Table? 

31.What is Fact Table? 

32.Steps in designing Star Schema 

33.What is aggregator transformation? & what is Incremental Aggregation? 

34.What are the different types of locks? 

35.What is Shortcuts, Sessions, Batches, mapplets, mappings, Worklet & workflow? 

36.If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, the   target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this? 

37.Incremental Aggregation 

38.Difference between data mart and data warehouse 

39.What is the difference between snow flake and star schema

40.Differences between rowid and rownum 

41.Differences between stored procedure and functions 

42.Differences between where clause and having clause

43.What is the difference between view and materialized view?

45.Using Informatica Normalizer Transformation

46.Use of Java Transformation in Informatica

47.How to remove header from a file ?

48.A source table contains emp_name and salary columns. Develop an Informatica  mapping to load all records with 5th highest salary into the target table.

49.We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:

50.Q. How do you improve the performance of lookup transformation?

51.Loading Multiple Flat Files

52. What is the difference between Source Qualifier transformations Source Filter to Filter transformation?

53. What is a Source Qualifier? What are the tasks we can perform using a SQ and why it is an ACTIVE transformation?

54. Suppose we have used the Select Distinct and the Number Of Sorted Ports property in the SQ and then we add Custom SQL Query. Explain what will happen.

55. Where we will use the Source Filter, Select Distinct and Number Of Sorted Ports properties of Source Qualifier transformation.

56. Suppose we have two Source Qualifier transformations SQ1 and SQ2 connected to Target tables TGT1 and TGT2 respectively. How do you ensure TGT2 is loaded after TGT1?

57. State the limitations where we cannot use Joiner in the mapping pipeline.

58.Out of the two input pipelines of a joiner, which one will you set as the master pipeline?

59. Suppose we have 100 records coming from the source. Now for a target column population we used a Sequence generator. Suppose the Current Value is 0 and End Value of Sequence generator is set to 80. What will happen

60. Insert the records of those employees who have joined in current month and Reject other rows.

61. Count the no of vowels present in emp_name column of EMP table as shown bellow.

62. The Emp table contains the salary and commission in USD, in the target the com and sal will converted to a given currency prefix ex: Rs.

63. Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format 'YYYYMMDD'.

64. Suppose in flat_file some special symbols like @,%,$,#,& has added in empno column along with the actual data. How to remove those special charcters ? ( see article , on how to remove $ from salary )

65. Insert the records of those employees who have joined in current month and Reject other rows.

66. Suppose the source table has values like this 10 A 10 B 10 C 20 X 20 Y 20 Z 30 D 30 E 30 F

67. A source table contains emp_name and salary columns. Develop an Informatica mapping to load all records with 5th highest salary into the target table.

68. Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.

69. How to Change the Session Log Name in Informatica Workflow?

70.Difference between Decode and IIF in Informatica?

71. Difference between Joiner and Union Transformation in Informatica?

72. Difference between Sorter and Aggregator in Informatica?

73. Difference between Sorter and Rank transformation in Informatica?

74. Difference Between DROP, DELETE & TRUNCATE

75. Difference between Trigger and Stored Procedure?

76. Different Between OLTP and OLAP

77. How many stages in Datawarehousing?

78. What are the types of Approach in DWH?

79. Why need staging area for DWH?