Friday, February 26, 2016

Informatica Interview Questions And Answers Part -1

One Liners:

1. While importing the relational source definition from database, what are the Meta data of source you    import?
 A. Source name, Database location, Column names, Data types, and Key constraints

2.  How many ways you can update a relational source definition and what are they?
 A.   1. Edit the definition
        2. Re-import the definition

3.   Where should you place the flat file to import the flat file definition to the designer?
  A.  Place it in local folder

4.   To provide support for Main frames source data, which files are used as a source definitions?
 A.   COBOL files

5. Which transformation should you need while using the COBOL sources as source definitions?
 A. Normalizer transformation which is used to normalize the data.
 Since COBOL sources are   often consists of De-normalized data.

6. How can you create or import flat file definition in to the warehouse designer?
 A. you cannot create or import flat file definition in to warehouse designer directly.
 B. Instead you must analyze the file in source analyzer, and then drag it into the warehouse designer.
 C. When you drag the flat file source definition into warehouse designer workspace, the warehouse designer creates a relational target definition not a file definition.
 D. If you 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 file.

7. What is the mapplet?
 A. Mapplet is a set of transformations that you build in the mapplet designer and you can use in multiple mappings.

8. What is a transformation?
 A.  It is a repository object that generates, modifies or passes data.

9. What are the designer tools for creating transformations?
 A.   Mapping designer, Transformation developer, Mapplet designer

10. What are the active and passive transformations?
  A.   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.

11. What are the connected or unconnected transformations?
  A.   An unconnected transformation is not connected to other transformations in the mapping. Connected transformation is connected to other transformations in the mapping.

12. How many ways you create ports?
 A.      1.Drag the port from another transformation
           2. Click the add button on the ports tab.

14. What are the reusable transformations?
 A.  Reusable transformations can be used in multiple mappings.   When you need to
Incorporate this transformation into mapping add an instance of it to Mapping.
Later if you change the definition of the transformation, all instances of it inherit the changes. Since the instance of reusable transformations is a pointer to that transformation can change the transformation in the transformation developer, its instances automatically reflect these changes This feature can save you great deal of work.


 15. What are the methods for creating reusable transformations?

  A.    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 can revert it to the original reusable transformation properties by clicking the revert button.

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

 17. What are the mapping parameters and mapping variables?
       Mapping parameter represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session. When you use the mapping parameter, you declare and use the parameter in a mapping or Mapplet. 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 you run the session.

 18. Can you use the mapping parameters or variables created in one mapping into another mapping?

        NO. We can use mapping parameters or variables in any transformation of the same mapping or mapplet in which you have created mapping parameters or variables.

 19. Can you use the mapping parameters or variables created in one mapping into any other reusable transformation?
     Yes. Because reusable transformation is not contained with any mapplet or mapping.

 20. How can you improve session performance in aggregator transformation?
      Use sorted input.

 21. What is aggregate cache in aggregator transformation?

        The aggregator stores data in the aggregate cache until it completes aggregate calculations. When you 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.

 22. What are the difference between joiner transformation and source qualifier transformation?

        You can join heterogeneous data sources in joiner transformation which we cannot achieve in source qualifier transformation need matching keys to join two relational sources in source qualifier transformation. Whereas you doesn't need matching keys to join two sources. Two relational sources should come from same data source in source qualifier. Whereas you can join relational sources which are coming from different sources in joiner transformation.

 23. In which conditions we cannot use joiner transformation (Limitations of joiner transformation)

 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.

 24. What are the settings that you use to configure the joiner transformation?
       Master and detail source, Type of join and Condition of the join

 25. What are the join types in joiner transformation?
       Normal (Default), Master outer, Detail outer, full outer Master Outer: All rows from Detail and only matching rows from Master Detail outer   : All rows from Master and only matching rows from Detail.
 Full outer join: keeps all rows of data from both the master and detail sources

 26. What are the joiner caches?
      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 joins.

 27. What is the look up transformation?
      Use lookup transformation in your mapping to lookup data in a relational table, view, and synonym. Flat file 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 condition.
 
 28. Why we use the lookup transformation ?
       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.

 29. What are the types of lookup?
         Connected and unconnected

 30. Differences between connected and unconnected lookup?

  Connected Lookup Unconnected Lookup  
 Receives input values directly from the pipeline. Receives input values from the result of a :LKP expression in another transformation.    You can use a dynamic or static cache. You can use a static cache.   Cache includes all lookup columns used in the mapping (that is, lookup source
columns included in the lookup condition and lookup source columns linked as
output ports to other transformations).  Cache includes all lookup/output ports
in the lookup condition and the lookup/return port.  
 Can return multiple columns from the same row or insert into the dynamic lookup
cache. Designate one return port (R). Returns one column from each row.   
 If there is no match for the lookup condition, the Power Center Server returns
the default value for all output ports. If you configure dynamic caching, the
PowerCenter Server inserts rows into the cache or leaves it unchanged.  If there is no match for the lookup condition, the PowerCenter Server returns NULL.    If there is a match for the lookup condition, the PowerCenter Server returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the PowerCenter Server either updates the row the in the cache
or leaves the row unchanged. If there is a match for the lookup condition, the PowerCenter Server returns the result of the lookup condition into the return port.    Pass multiple output values to another transformation. Link lookup/output ports to another transformation. Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling: LKP
Expression. Supports user-defined default values. Does not support user-defined default
Values.  
 
                           

 31. What is meant by lookup caches?

     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 you configure in the transformation or session properties. The Informatica server stores condition values in the index cache and output values in the data cache.


 32. What are the types of lookup caches?
 Persistent cache: you can save the lookup cache files and reuse them the next time the Informatica server processes a lookup transformation configured to use the cache.

 Re-cache from database: If the persistent cache is not synchronized with the lookup table, you can configure the lookup transformation to rebuild the lookup cache.

 Static cache: you 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 you want to cache the target table and insert new rows into cache and the target, you can create a look up transformation to use dynamic cache. The Informatica server dynamically inserts data to the target table.

 Shared cache: you can share the lookup cache between multiple transactions you can share unnamed cache between transformations in the same mapping.



 33. Difference between static cache and dynamic cache
                                                                           

 Uncached Static Cache Dynamic Cache  
 You cannot insert or update the cache. You cannot insert or update the cache. You can insert or update rows in the cache as you pass rows to the
target.  
 You cannot use a flat file lookup. You can use a relational or a flat file lookup. You can use a relational lookup only.  
 When the condition is true, the PowerCenter Server returns a value from the lookup table or cache.
 When the condition is not true, the PowerCenter Server returns the default value for connected transformations and NULL for unconnected transformations.
 For details, see Working with an Uncached Lookup or Static Cache. When the condition is true, the PowerCenter Server returns a value from the lookup table or cache.
 When the condition is not true, the PowerCenter Server returns the default value for connected transformations and NULL for unconnected transformations.
 For details, see Working with an Uncached Lookup or Static Cache. When the condition is true, the PowerCenter Server either updates rows in the cache or leaves the cache unchanged, depending on the row type. This indicates that the row is in the cache and target table. You can pass updated rows to the target table.
 When the condition is not true, the PowerCenter Server either inserts rows into the cache or leaves the cache unchanged, depending on the row type. This indicates that the row is not in the cache or target table. You can pass inserted rows to the target table.

34. Which transformation should we use to normalize the COBOL and relational Sources?

 Normalizer Transformation: When you drag 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.

 35. How the Informatica server sorts the string values in Rank transformation?

      When the Informatica server runs in the ASCII data movement mode it sorts session data using Binary sort order. If you configure the session 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.

 36. What are the rank caches?

     During the session, the Informatica server compares an in out row with rows in the data cache. 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 cache.

 37. What is the Rankindex in Rank transformation?
     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:

 38.  What is the Router transformation?

  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.

 39. What are the types of groups in Router transformation?  
  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
     you cannot modify or delete default groups.

 40. Why we use stored procedure transformation?
 For populating and maintaining data bases

 42. What are the types of data that passes between Informatica server and stored procedure?
      3 types of data
                      Input/output parameters
                      Return Values
                      Status code

 43. What is the status code?
     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 successfully. The user cannot see this value. It only used by the Informatica server to determine whether to continue running the Session or stop.

 44. What is source qualifier transformation?

 Ans. When you add a relational or a flat file source definition to a mappings, you need to connect it to a source qualifier transformation. The source qualifier transformation represents the records that the Informatica server reads when it runs a session.

 45. What are the tasks that source qualifier perform?

      Join data originating from same source database.
      Filter records when the Informatica server reads source data.
      Specify an outer join rather than the default inner join
      Specify sorted records.
      Select only distinct values from the source.
      Create custom query to issue a special SELECT statement for the  server to read Source data.

 46. What is the target load order?

      You specify the target load order based on source qualifiers in a mapping. If you have the multiple Source qualifiers connected to the multiple targets, you can designate the order in which Informatica server loads data into the targets.

 47. What is the default join that source qualifier provides?
      Inner OR equijoin.

 48. What are the basic needs to join two sources in a source qualifier?

        Two sources should have primary and foreign key relationships.
        Two sources should have matching data types.

 49. What is update strategy transformation?
  This transformation is used to maintain the history data or just most recent Changes into target table.

 50. Describe two levels in which update strategy transformation sets?
  
 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.


51. What is the default source option for update strategy transformation?
          Data driven

 52. What is Data driven?
        The Informatica server follows instructions coded into update strategy transformations With in the session mapping determine how to flag records for insert, update, delete or Reject .If you do not choose data driven option setting, the Informatica server ignores all update strategy transformations in the
mapping.

53. What are the options in the target session of update strategy transformation?
       Insert , Delete ,Update , Update as update ,Update as insert  , Update else insert ,Truncate table

54. What are the types of mapping wizards that are to be provided in Informatica?

 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.

Few 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 fact tables?
35. What is Shortcuts, Sessions, and 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.. 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
 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 column 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 characters ? ( 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 Data warehousing?
 78. What are the types of Approach in DWH?
 79. Why need staging area for DWH?

No comments:

Post a Comment