Choose.. X Home Exams Certifications

Informatica Interview Questions

1 . Define command task?

  • This task permits one or more than one shell commands in UNIX or DOS in windows to run during the workflow.

2 . How can we update a record in target table without using Update strategy?

  • A target table can be updated without using ‘Update Strategy’. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and check the “Update” check-box.Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”.
  • Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

3 . What do you understand by a term domain?

  • Domain is the term in which all interlinked relationship and nodes are under taken by sole organizational point.

4 . Define Enterprise Data Warehousing?

  • When the data of organization is developed at a single point of access it is known as enterprise data warehousing.

5 . What is pre and post session shell command?

  • Command task can be called as the pre or post session shell command for a session task. One can run it as pre session command r post session success command or post session failure command.

6 . Explain the scenario which compels informatica server to reject files?

  • When it faces DD_Reject in update strategy transformation then it sends server to reject files.

7 . Define Informatica?

  • Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Now days Informatica is also being used as an Integration tool.Informatica is an easy to use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for Data extraction transformation and load.
  • These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background Informatica server takes care of fetching data from source, transforming it, & loading it to the target systems/databases.

8 . Write the prerequisite tasks to achieve the session partition?

  • In order to perform session partition one need to configure the session to partition source data and then installing the Informatica server machine in multifold CPU’s.

9 . In one group how many number of sessions can we have?

  • We can have any number of session but it is advisable to have lesser number of session in a batch because it will become easier for migration.

10 . Compare Informatica & DataStage

  • CriteriaInformaticaDataStage
    GUI for development & monitoringPowerDesigner, Repository Manager, Worflow Designer, Workflow Manager.DataStage Designer, Job Sequence Designer and Director.
    Data integration solutionStep-by-step solutionProject based integration solution
    Data transformationGoodExcellent

11 . Name other tools for scheduling purpose other than workflow manager pmcmd?

  • ‘CONTROL M’ is the third party tool for scheduling purpose other than workflow manager.

12 . What is Informatica ETL Tool?

  • Informatica ETL tool is market leader in data integration and data quality services. Informatica is successful ETL and EAI tool with significant industry coverage.ETL refers to extract, transform, load. Data integration tools are different from other software platforms and languages.
  • They have no inbuilt feature to build user interface where end user can see the transformed data. Informatica ETL tool “power center” has capability to manage, integrate and migrate enterprise data.

13 . How we can identify whether mapping is correct or not without connecting session?

  • With the help of debugging option we can identify whether mapping is correct or not without connecting sessions.

14 . What is reusable transformation?

  • This transformation is used various times in mapping. It is divest from other mappings which use the transformation as it is stored as a metadata.

15 . What are the new features of Informatica 9.x Developer?

  • From an Informatica developer’s perspective, some of the new features in Informatica 9.x are as follows:Now Lookup can be configured as an active transformation – it can return multiple rows on successful match
    Now you can write SQL override on un-cached lookup also. Previously you could do it only on cached lookup
    You can control the size of your session log. In a real-time environment you can control the session log file size or time
    Database deadlock resilience feature – this will ensure that your session does not immediately fail if it encounters any database deadlock, it will now retry the operation again. You can configure number of retry attempts.

16 . What do you mean by Lookup transformation?

  • Lookup transformation is used for maintaining data in a relational table through mapping. We can use multiple lookup transformation in a mapping.

17 . What can we do to improve the performance of Informatica Aggregator Transformation?

  • Aggregator performance improves dramatically if records are sorted before passing to the aggregator and “sorted input” option under aggregator properties is checked. The record set should be sorted on those columns that are used in Group By operation.It is often a good idea to sort the record set in database level e.g. inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.

18 . Define sessions in Informatica ETL.

  • Session is a teaching group that requires to be to transform information from source to a target.

19 . Write the use of target designer?

  • With the help of target designer we can create target definition.

20 . How we can create indexes after completing the load process?

  • With the help of command task at session level we can create indexes after the load procedure.

21 . What do you understand by term role playing dimension?

  • The dimensions that are used for playing diversified roles while remaining in the same database domain are known as role playing dimensions.

22 . Define OLAP (On-Line Analytical Processing?

  • It is a process by which multi-dimensional analysis occurs.

23 . Define Aggregator transformation?

  • It is different from expression transformation in which we can do calculations in set but here we can do aggregate calculations such as averages, sum, etc.

24 . What are the advantages of using Informatica as an ETL tool over Teradata?

  • First up, Informatica is a data integration tool, while Teradata is a MPP database with some scripting (BTEQ) and fast data movement (mLoad, FastLoad, Parallel Transporter, etc) capabilities.Informatica over Teradata1) Metadata repository for the organization’s ETL ecosystem.
    Informatica jobs (sessions) can be arranged logically into worklets and workflows in folders.
    Leads to an ecosystem which is easier to maintain and quicker for architects and analysts to analyze and enhance.2) Job monitoring and recovery-
    Easy to monitor jobs using Informatica Workflow Monitor.
    Easier to identify and recover in case of failed jobs or slow running jobs.
    Ability to restart from failure row / step.3) InformaticaMarketPlace- one stop shop for lots of tools and accelerators to make the SDLC faster, and improve application support.4) Plenty of developers in the market with varying skill levels and expertise5) Lots of connectors to various databases, including support for Teradata mLoad, tPump, FastLoad and Parallel Transporter in addition to the regular (and slow) ODBC drivers.Some ‘exotic’ connectors may need to be procured and hence could cost extra.Examples – Power Exchange for Facebook, Twitter, etc which source data from such social media sources.6) Surrogate key generation through shared sequence generators inside Informatica could be faster than generating them inside the database.7) If the company decides to move away from Teradata to another solution, then vendors like Infosys can execute migration projects to move the data, and change the ETL code to work with the new database quickly, accurately and efficiently using automated solutions.8) Pushdown optimization can be used to process the data in the database.9) Ability to code ETL such that processing load is balanced between ETL server and the database box – useful if the database box is ageing and/or in case the ETL server has a fast disk/ large enough memory & CPU to outperform the database in certain tasks.10) Ability to publish processes as web services.Teradata over Informatica
    1. Cheaper (initially) – No initial ETL tool license costs (which can be significant), and lower OPEX costs as one doesn’t need to pay for yearly support from Informatica Corp.
    2. Great choice if all the data to be loaded are available as structured files – which can then be processed inside the database after an initial stage load.
    3. Good choice for a lower complexity ecosystem
    4. Only Teradata developers or resources with good ANSI/Teradata SQL / BTEQ knowledge required to build and enhance the system.

25 . Write the different tools in workflow manager?

  • The different tools in workflow manager are:
    Task Developer
    Task Designer
    Workflow Designer

26 . What is InformaticaPowerCenter?

  • InformaticaPowerCenter is one of the Enterprise Data Integration products developed by Informatica Corporation. InformaticaPowerCenter is an ETL tool used for extracting data from the source, transforming and loading data in to the target.The Extraction part involves understanding, analyzing and cleaning of the source data.
    Transformation part involves cleaning of the data more precisely and modifying the data as per the business requirements.
    The loading part involves assigning the dimensional keys and loading into the warehouse.

27 . 21 How we can confirm all mappings in the repository simultaneously?

  • At a time we can validate only one mapping. Hence mapping cannot be validated simultaneously.

28 . Define a session task?

  • It is a mass of instruction that guides power center server about how and when to move data from sources to targets.

29 . Explain standalone command task?

  • This task can be used anywhere in the workflow to run the shell commands.

30 . Differentiate between a database, and data warehouse?

  • Database have a group of useful information which is brief in size as compared to data warehouse whereas in data warehouse their are set of every kind of data whether it is useful or not and data is extracted as the the requirement of customer.

31 . In informatics server Which files are created during the session rums?

  • Errors log, Bad file, Workflow low and session log namely files are created during the session rums.

32 . Define work flow?

  • The group of directions that communicates server about how to implement tasks is known as work flow.

33 . What are the features of complex mapping?

  • The features of complex mapping are:
    Many numbers of transformations
    tricky needscompound business logic

34 . Can we use mapping parameter or variables developed in one mapping into any other reusable transformation?

  • Yes, we can use mapping parameter or variables into any other reusable transformation because it doesn’t have any mapplet.

35 . How we can access repository reports without SQL or other transformations?

  • We can access repository reports by using metadata reporter. No need of using SQL or other transformation as it is a web app.

36 . Define worklet?

  • Worklet is said when the workflow tasks are collected in a group. It  includes timer, decision, command, event wait, etc.

37 . In Informatica WorkFlow Manager, how many repositories can be created?

  • It mainly depends upon the number of ports we required but as general there can be any number of repositories.

38 . Define Incremental Aggregation?

  • The incremental aggregation is done whenever a session is developed for a mapping aggregate.

39 . What is lookup transformation?

  • The transformation that has entrance right to RDBMS Is known as lookup transformation.

40 . Name the different types of OLAP? Write an example?

  • Different types of OLAP are ROLAP, HOLAP< DOLAP.

41 . Differentiate between a repository server and a powerhouse?

  • Repository server mainly guarantees the repository reliability and uniformity while powerhouse server tackles the execution of many procedures between the factors of server’s database repository.

42 . Write the types of metadata those stores in repository?

  • The types of metadata which is stored in repository are Target definition, Source definition, Mapplet, Mappings, Transformations.

43 . What are the Advantage of Informatica:

  • Its GUI tool, Coding in any graphical tool is generally faster than hand code scripting.
    Can communicate with all major data sources (mainframe/RDBMS/Flat Files/XML/VSM/SAP etc).
    Can handle vary large/huge data very effectively.
    User can apply Mappings, extract rules, cleansing rules, transformation rules, aggregation logic and loading rules are in separate objects in an ETL tool. Any change in any of the object will give minimum impact of other object.
    Reusability of the object (Transformation Rules)
    Informatica has different “adapters” for extracting data from packaged ERP applications (such as SAP or PeopleSoft).
    Availability of resource in the market.
    Can be run on Window and Unix environment.

44 . Define user defied event?

  • User defined event are a flow of tasks in the workflow. Events can be developed and then raised as need comes.

45 . How we can use Union Transformation?

  • It is a different input group transformation that is used to combine data from different sources.

46 . Differentiate between mapping parameter and mapping variable?

  • At the time values alter during the session’s implementation it is known as mapping variable whereas the values that don’t alter within the session implementation is called as mapping parameters.

47 . Define mapplet?

  • A mapplet is a recyclable object that is using mapplet designer.

48 . What are the different lookup cache(s)?

  • Informatica Lookups can be cached or un-cached (No cache). And Cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built and it remains same during the session run. On the other hand, A caches refreshed during the session run by inserting or updating the records in cache based on the incoming source data.
  • By default, Informatica cache is static cache.A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after the completion of session run or deletes it.

49 . Write the advantages of partitioning a session?

  • The main advantage of partitioning a session is to get better server’s process and competence. Other advantage is it implements the solo sequences within the session.

50 . What is the use of aggregator cache file?

  • If extra memory is needed aggregator provides extra cache files for keeping the transformation values. It also keeps the transitional value that are there in local buffer memory.