Informatica Interview Questions part 1


What is a source qualifier?
Source qualifier represents the data that the informatica server reads from the source.

What is a surrogate key?

What is difference between Mapplet and reusable transformation?

What is DTM session?

What is a Mapplet?
What is up date strategy and what are the options for update strategy?

What is subject area?

What is the difference between truncate and delete statements?
Truncate is a type of DDL. No commit is needed. It resets the HWM and cleans up all the storage segments. No rollback is possible because of implicit commit. Delete can be rolled back, does not clean up the data segments so the HWM stays intact. Less efficient.

What kind of Update strategies are normally used (Type 1, 2 & 3) & what are the differences?
Type 1 – DD_UPDATE,
Type 2 – DD_UPDATE/DD_INSERT
Type 3 – DD_INSERT.

 What are bitmap indexes and how and why are they used?

What is bulk bind? How does it improve performance?
Bulk bind is used enhance the performance of PL/SQL fetch engine. It binds a variable so that the fetches are in bulk instead of streams of one row at a time.

What are the different ways to filter rows using Informatica transformations?
Source Qualifier (if using a homogeneous database or if the tables are in the same instance),Joiner, Filter, Router.

What is target load order?
 You specify in Informatica which target instance gets loaded first or in order.

What are the different transformations where you can use a SQL override?
Source Qualifier(If Relational)/Lookup (If Relational)

What is the difference between a Bulk and Normal mode and where exactly is it defined?
http://prashanthobiee.blogspot.in/2012/12/difference-between-bulk-and-normal-mode.html

What are data driven sessions?
What are worklets and what is their use?
 Sessions can be bundled up together logically by subject area so that it can go in the workflow as one logical piece.

What is change data capture?
Change Data Capture is a mechanism of capturing changed data since the last load. Striva in mainframes. It involves reading database log files. Subscriber/Prescriber views. Triggers/Date fields in oracle.

What exactly is tracing level?
Tracing level is defined as amount of information which will go in Informatica log files.

How do you validate all the mappings in the repository at once?
In repository manager. Select all the then validate.

How can you join two or more tables without using the source qualifier override SQL or a Joiner transformation?
Using a view also you can use lookup(which is nothing but a outer join).

How many repositories can be created in Informatica?
N(Depends on your licensing agreement with Informatica)

How can you improve performance in an Aggregator transformation?
By checking sorted input and passing a sorted data through it.

How does the Informatica know that the input is sorted?     
Check the sorted port in Source Qualifier.

How many worklets can be defined within a workflow?
N.

If you join two or more tables and then pull out about two columns from each table into the source qualifier and then just pull out one column from the source qualifier into  an Expression transformation and then do a ‘generate SQL’ in the source qualifier how many columns will show up in the generated SQL.
One

In a Type 1 mapping with one source and one target table what is the minimum number of update strategy transformations to be used?
One

For joining three heterogeneous tables how many joiner transformations are required?
Thumb rule for and SQL joins and Informatica joiners. If you want to join n tables minimum number of joins you need is n-1.



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

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

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

To provide support for Main frames source data, which files r used as a source  definitions?
COBOL files

Which transformation do you need while using the Cobol sources as source definitions?
Normalizer transformation which is  used to normalize the data.
Since Cobol sources often consist of De-normalized data.

How can you create or import flat file definition in to the warehouse designer?
You cannot create or import flat file definition into warehouse designer directly.
Instead you must analyze the file in source analyzer, then drag it into the warehouse designer.
When you drag the flat file source definition into warehouse designer  work space, the
warehouse designer creates a relational target definition not a file definition.
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.

What is the Mapplet?

What is a Transformation?
It is a repository object that generates, modifies or passes data.

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

What are the active and passive transformations?

What are the connected or unconnected transformations?

In how many ways can you create ports?
1.Drag the port from another transformation
2.Click the add button on the ports tab.

What are the reusable transformations?

 What are the methods for creating reusable transformations?
 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, you can revert it to the original reusable transformation properties by clicking the revert button.

What are 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 defintions

What are the mapping parameters and mapping variables?

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.

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

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

What  is aggregate cache in aggregator transformation?

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

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.

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

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

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 perform joins.

What is the look up transformation?

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.

What are the types of lookup?
Connected and unconnected

Differences between connected and unconnected lookup?
              
What is meant by lookup caches?

What are the types of lookup caches?

Difference between uncached, static cache and dynamic cache
                                                                    
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 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.

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.

What is the Rank index in Rank transformation?

What is the Router transformation?

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 can not modify or delete default groups.

Can you look up a flat file using Informatica?
 Yes.

While running a session what default files are created?
 Default files are. Log files and Bad files. (For other files like err, target files you have let the Informatica know.

Describe the use of Materialized views and how are they different from a normal view.
 Normal View is not a physical object. It is created at runtime by the select statement. Materialized View is a physical object with real data. It has several use. For more details look into Oracle documentation. It is a major performance booster when you need summary,aggregated or complex joins to be calculated. It pre-calculates it and is ready for use when you need them and it is really fast. Be careful to choose the mechanism you want to refresh it.


40 comments:

  1. Do You Know How To Integrate The Data By Using Informatica ETL Tool?, Learn at

    http://www.dwbiadda.com/course/informatica-online-training/

    ReplyDelete
  2. This is the information that I was looking for.. Thanks for the efforts you put to gather such a nice content and posted here.
    Informatica training | Microsoft Dynamics CRM Training

    ReplyDelete
  3. Thanks for Sharing.This is Verry usefull site.
    Hamsini Technologies is One of the Best Institute to Provide Online IT Trainings.
    We have Real-time Industry experts to provide Online,Classroom & corporate Trainings around the world.

    SAP FICO Online training


    Contact us for more details .


    SAP FICO Online training



    ReplyDelete
  4. serve with the best data analysis with question on informatica you can follow us at :-For all students and individuals eager to learn informatica scenario based questions more about the data warehouse interview questions topic can easily go through the website and face all possible interview questions.

    ReplyDelete
  5. Informatica is one of the most trusted and effective ETL software which is known globally. Informatica is one data warehousing ETL tool. Informatica ha very go opportuities for job across the world.
    informatica training in chennai | informatica training institute in Chennai | informatica classes in Chennai

    ReplyDelete
  6. Very nice collection of information thank you for sharing this info. Know more about OBIEE Online Training

    ReplyDelete
  7. your interview questions are really awesome and i have done got very useful information.This blog is really nice and interesting too.



    Best Informatica Training Institute in Chennai

    ReplyDelete
  8. With this Informatica interview questions, we built strong knowledge on informatica. The Blogger mentioned all important questions of infomatica interview point of view. Informatica beginners set their platforms with this questions Informatica Online Training in Hyderabad
    Informatica Online Training in USA
    Informatica Online Training in INDIA

    ReplyDelete
  9. I’ve been browsing on-line greater than three hours today, but I never discovered any attention-grabbing article like yours. It is beautiful worth sufficient for me. Personally, if all webmasters and bloggers made good content material as you did, the net will be a lot more helpful than ever before.

    Informatica Training in Chennai

    ReplyDelete
  10. Thank you for your great information on informatica online trainings.
    Informatica Online Course!

    ReplyDelete
  11. Very excellent post for more post please visit below for information
    Informatica Online Training

    ReplyDelete
  12. Great and really helpful article! Adding to the conversation, providing more information, or expressing a new point of view...Nice information and updates. Really i like it and everyday am visiting your site..

    Informatica Training in Chennai

    Dataware Housing Training in Chennai

    ReplyDelete
  13. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    SEO Company in India

    ReplyDelete
  14. you’re getting your information, but good topic. I need to spend some time learning more or understanding more. Thanks for fantastic info I was looking for this information for my mission.

    SAP Training in Chennai

    SAP ABAP Training in Chennai

    ReplyDelete
  15. Really i enjoyed very much. And this may helpful for lot of peoples. So you are provided such a nice and great article within this.

    Dataware Housing Training in Chennai | Hadoop Training in Chennai

    ReplyDelete
  16. your blog is very useful for my oracle business development.keep more updates.
    Informatica Training in Chennai


    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Great resource. I would suggest my friends to learn more about informatica question. Thank you.

    Best Informatica Course in Chennai
    Best Informatica Training in Chennai

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.

    PHP Training
    Azure Training
    Cloud Training

    ReplyDelete

  21. Thank you for sharing such great information very useful to us.
    Informatica Training institute in Gurgaon

    ReplyDelete
  22. I think Informatica and its products are the best to solve extreme complex IT problems.On the other hand it is always very useful from the interview point of view.

    Informatica Read Soap Api

    ReplyDelete
  23. Thank you so much for putting up such an informative blog post about Informatica and some of its other aspects.This is the best to solve some complex database operations.

    Informatica Read SOAP API

    ReplyDelete
  24. We offer best Informatica course training at Gurgaon with experienced trainers in Informatica industry. We provide 100% placement assistance and make you work on live projects.
    For More Info: Informatica Course in Gurgaon

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. Hello Sir Thank you so much for sharing this blog this is very helpful for us to check this online training for online
    Informatica training

    ReplyDelete
  27. Very useful post. This is my first time I visit here. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up. I also wrote an article about Informatica online training
    I would be glad If you read and comment. Thanks in advance
    Please check Informatica training
    Informatica online training

    ReplyDelete
  28. This is very interesting, You’re a very skilled blogger.
    informatica online training

    ReplyDelete


  29. Nice information, this is will helpfull a lot, Thank for sharing, Keep do posting i like to follow this

    Interesting blog, here a lot of valuable information is available, it is very useful information Keep do posting i like to follow this informatica online training
    informatica online course
    informatica bdm training
    informatica developer training
    informatica training
    informatica course
    informatica axon training
    informatica developer training
    informatica developer course

    ReplyDelete

  30. Title:
    Best Oracle Training in Chennai | Infycle Technologies

    Description:
    If Oracle is a job that you're dreaming of, then we, Infycle are with you to make your dream into reality. Infycle Technologies offers the best Oracle Training in Chennai, with various levels of Oracle courses such as Oracle PLSQL, Oracle DBA, etc., in 100% hands-on practical training with professional tutors in the field. Along with that, the mock interviews will be done for the candidates, so that, they can face the interviews with full confidence
    Best training in Chennai.

    ReplyDelete
  31. Want to learn Oracle DBA along with the job opportunities? Infycle are with you to make your dream into reality. Infycle Technologies gives the most trustworthy Oracle DBA Training in Chennai, in 100% hands-on practical training with professional tutors in the field. Along with that, the mock interviews will be assigned for the candidates, so that, they can meet the job interviews with full confidence. To transform your career to next level, call 7502633633 to Infycle Technologies and grab a free demo to know more
    Best software training in chennai

    ReplyDelete
  32. Thanks for sharing the informative article. It's very useful for beginners. the info it solution offers the best and affordable SEO services, provider. You can take their services

    digital marketing services

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...