Informatica Quiestions

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 r they?

A.   1. Edit the definition

       2. Reimport the definition

3.   Where should U 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 r used as a source definition?

A.   COBOL files

5. Which transformation should u need while using the Cobol sources as source definitions?

A.

·         Normalizer transformation which is used to normalize the data.

·         Since Cobol sources r      often consists of Denormailzed data.

6. How can U create or import flat file definition in to the warehouse designer?

A.       U cannot create or import flat file definition in to warehouse designer directly.

B.       Instead U must analyze the file in source analyzer, then drag it into the warehouse designer.

C.       When U drag the flat file source definition into warehouse designer workspace, the warehouse designer creates a relational target definition not a file definition.

D.      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 file.

7.What is the mapplet?

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

8. what is a transformation?

A.  It is a repository object that generates, modifies or passes data.

9. What r the designer tools for creating transformations?

A.   Mapping designer, Transformation developer, Mapplet designer

10. What r 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 r 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 u creates ports?

A.      1.Drag the port from another transformation

          2.Click the add button on the ports tab.

14. What r the reusable transformations?

A.  Reusable transformations can be used in multiple mappings.   When u need to incorporate this transformation into mapping add an instance of it to Mapping. Later if U 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 U great deal of work.

15. What r 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 U add 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 can demote it to a    Standard transformation at any time.  If u 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 r the unsupported repository objects for a mapplet?

·         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

17. What r the mapping parameters and mapping variables?

      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 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 U run the session.

18. Can U 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 U have created mapping parameters or variables.

19.Can u 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 U 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 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.

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

       U 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 u doesn't need matching keys to join two sources. Two relational sources should come from same data source in source qualifier. whereas U can join relational sources which r 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 r the setting that u use to configure the joiner transformation?

      Master and detail source, Type of join and Condition of the join

25. What r 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 r 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 perform joins.

27.what is the look up transformation?

     Use lookup transformation in u'r mapping to lookup data in a relational table, view, 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 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 r 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 PowerCenter 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 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.

32.What r the types of lookup caches?

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 the 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, u 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 U 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.

·         For details, see Updating the Dynamic Lookup Cache.

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

      Normalizer Transformation. When U 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 U configure the session use a biary 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 r 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 Rank index 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 r 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

    U cannot modify or delete default groups.

40.Why we use stored procedure transformation?

For populating and maintaining data bases.

42.What r 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 U add a relational or a flat file source definition to a mapping, U 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 performs?

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

·         Creating custom query to issue a special SELECT statement for the Informatica server to read Source data.

46. What is the target load order?

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

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

     Inner equijoin.

48. What r 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 u do not choose data driven option setting, the Informatica  server ignores all update strategy transformations in the mapping.

53.What r 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 r the types of mapping wizards that r 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.