Informatica Certification Exams General Practice Test

The correct answer is Option 1 & Option 3. In a data warehouse, there are two types of loading available: Full Load and Incremental Load. Full Load refers to loading all the data from source systems into the data warehouse, while Incremental Load involves loading only the changes or updates since the last load. Direct Load is not a type of loading in a data warehouse, so Option 2 is incorrect. Therefore, the correct answer is Option 1 & Option 3.

The default repository server connection timeout limit is 60 seconds.

When we export a mapping, session, or workflow in Informatica, it creates an XML file. XML (Extensible Markup Language) is a widely used format for storing and transporting data. It is a structured and self-describing format that allows for easy interpretation and manipulation of data. Informatica uses XML files to store metadata and configuration information of mappings, sessions, and workflows, making it easier to import and use them in different environments or instances.

By executing the read query directly against the source database, it can help identify any bottlenecks that may be present in the source database. This means that any performance issues or limitations within the source database can be identified and addressed. This information is important for optimizing the performance of the database and ensuring efficient data retrieval.

When checking in an object, the repository version number is increased. This is because each time a change is made to an object and it is checked in, a new version of the object is created. By increasing the version number, it allows for tracking and managing different versions of the object in the repository.

There are three permission types available for folders. This means that users can be granted different levels of access to folders, such as read-only, read and write, or full control. These permissions help to control and manage the access and actions that users can perform on folders, ensuring the security and integrity of the stored data.

The Update Strategy transformation is used to load the changed records in the target table. It allows you to specify how to handle different types of data changes, such as insert, update, delete, or reject. By configuring the Update Strategy transformation, you can control the flow of data to the target table based on the type of change. This transformation is commonly used in data integration processes to efficiently load and update target tables with only the necessary changes.

The PowerCenter Server does not save multiple logs for a single workflow by default. This means that when a workflow is executed, only one log file is generated and saved. If multiple logs are required for a single workflow, additional configurations or customizations need to be made.

The row & column indicator is added to reject files by the server to help determine which column caused the row to be rejected. This indicator provides information about the specific column that caused the rejection, allowing for easier identification and troubleshooting of errors in the data.

The correct answer is Option 1 & Option 3. The Informatica Server and Informatica Repository Server are available in Informatica. The Informatica Server is responsible for executing the workflows and tasks, while the Informatica Repository Server stores and manages metadata related to the workflows, mappings, and other objects used in the Informatica environment. Therefore, both Option 1 (Informatica Server) and Option 3 (Informatica Repository Server) are the correct servers available in Informatica.

There are 18 types of transformations available in Informatica 9.0.

The valid call for a lookup transformation from Expression transformation is ":LKP.lookup_transformation_name(argument, argument, ...)". This syntax indicates that the lookup transformation is being called using the LKP function with the specified transformation name and arguments.

The Repository administration console is used for New server registration in Informatica. This component allows users to register a new server in the Informatica repository. It provides a graphical user interface that allows administrators to manage and configure the Informatica server. The Repository administration console is specifically designed for administrative tasks and provides a centralized platform for managing and maintaining the Informatica environment.

The PowerCenter server uses the DTM (Data Transformation Manager) Process for executing a workflow. The DTM Process is responsible for reading, transforming, and loading the data as defined in the workflow. It coordinates the execution of tasks, manages data flow, and ensures that the workflow runs smoothly. The DTM Process plays a crucial role in the overall workflow execution in PowerCenter.

The correct answer is "TWO" because in order to have a simple valid mapping, there must be at least two transformations. A simple valid mapping requires a minimum of two transformations to map one set of elements to another set of elements. Therefore, the answer is "TWO".

The first step in performance tuning is to identify the performance bottleneck. This means identifying the specific component or process that is causing the performance issue or slowdown. Once the bottleneck is identified, further analysis and tuning can be done to improve the performance. The session log, workflow log, and cache file are not necessarily related to performance tuning, so they are not the correct answer. "I don't know" is not a valid option for the first step in performance tuning.

The correct answer is PowerCenter Server because pmcmd is a command line utility provided by Informatica PowerCenter to communicate with the PowerCenter Server. It allows users to perform various tasks such as starting and stopping workflows, checking workflow status, and managing sessions on the PowerCenter Server.

In the Mapping tab of a session, the connection details of the source and targets are configured. This tab allows the user to define the mapping between the source and target objects, specify transformation logic, and set various session properties. By configuring the connection details in the Mapping tab, the user can ensure that the session can read data from the source and write it to the target according to the defined mapping rules.

In order to successfully recover data, it is important for the source data to be sorted. Sorting the data helps in organizing it in a specific order, making it easier to locate and retrieve the required information. This ensures that the data recovery process is efficient and accurate.

The Navigation window in the designer is used to access the repositories. This window allows users to navigate through the different repositories and access their contents. It provides a hierarchical view of the repositories, making it easier for users to locate and access the desired repository.

The Normalizer Transformation is used to handle COBOL sources. COBOL files often contain repeating groups of data, and the Normalizer Transformation is specifically designed to handle this type of data structure. It allows the user to define the repeating groups and normalize the data into a relational format that can be easily processed by downstream transformations.

Round Robin partitioning distributes data evenly among all partitions by assigning each row of data to the next available partition in a rotating fashion. This means that each partition receives an equal number of rows, ensuring a balanced distribution of data across all partitions.

The given statement describes a quantifiable value such as sales, budget, or revenue, which is typically stored in a fact table in a data warehouse. A fact table contains the measurements or metrics of a business process and is used for analysis and reporting. Therefore, the correct answer is "Fact."

Staging is used to store data and perform data cleansing and merging before loading it into a data warehousing system. This allows for the transformation and preparation of the data before it is loaded into the data warehouse, ensuring that it is accurate and consistent. Staging serves as an intermediary step in the ETL (Extract, Transform, Load) process, where data from various sources is collected, cleaned, and consolidated before being loaded into the data warehouse for analysis and reporting.

In Informatica, the available types of sources include flat files and relational databases, both homogeneous and heterogeneous. Informatica also supports working with XML data sources. "Cobol" sources are not typically considered a distinct category in Informatica, and "None" is an incorrect option as there are indeed various source types available for use in Informatica.

In Informatica PowerCenter, the session property 'Data Driven' is enabled when the update strategy transformation is used in a mapping. This property allows the Informatica Server to determine the appropriate operation (insert, update, delete, or reject) for each row based on the conditions defined in the update strategy transformation.

The other options are incorrect because they represent specific operations within the update strategy, not the overall session property that governs the data-driven behavior.

The metadata in the Informatica Repository is stored in database tables. These tables store information about the structure, relationships, and properties of the data objects in the repository. Storing the metadata in database tables allows for efficient storage, retrieval, and management of the metadata within the repository.

When using pmcmd in command line mode, each command must include the connection information of the PowerCenter Server. This is because pmcmd is a command line utility used to control and manage PowerCenter workflows and sessions. In order to execute commands successfully, pmcmd needs to know the connection details of the PowerCenter Server, such as the server name, port number, and login credentials. Without this information, pmcmd would not be able to establish a connection with the PowerCenter Server and execute the desired commands.

The Sorter Transformation is used to load distinct records in a table. It sorts the input data based on specified sort keys and eliminates duplicate records, ensuring that only distinct records are loaded into the table. This transformation is commonly used in data integration and ETL (Extract, Transform, Load) processes to ensure data quality and eliminate redundancy.

A worklet is an object that represents a set of tasks. It is used in workflow management systems to define a collection of related tasks that need to be executed together. The tasks within a worklet can be organized and scheduled in a specific order to ensure smooth workflow execution. By selecting "Tasks" as the correct answer, it indicates that a worklet represents a set of tasks rather than mappings, tables, synonyms, or web reports.

The correct answer is 5 because Informatica has five important client components that are used for various purposes. These components include PowerCenter Client, PowerCenter Repository Manager, PowerCenter Designer, PowerCenter Workflow Manager, and PowerCenter Workflow Monitor. Each component serves a specific function in the Informatica environment, such as designing and managing workflows, creating and editing mappings, and monitoring workflow execution.

To compare two folders, the mandatory permission a user should have is "Read". This permission allows the user to view the contents of the folders and compare them. Without the Read permission, the user would not be able to access the folder's data and therefore cannot compare them effectively. The other permissions listed, such as Write, Execute, Super User, and Administrator, are not necessary for simply comparing folders.

The task used for running a session based on the success of another session is the Decision task. This task allows the workflow to make a decision based on the outcome of a previous session. If the previous session is successful, the workflow can proceed with the next session. If the previous session fails, the workflow can take a different path or stop altogether. The Decision task provides a way to control the flow of the session based on specific conditions or criteria.

When the error tracing level is set to "Verbose data," the Informatica Server logs each row that passes into the mapping. This means that every row of data that enters the mapping will be recorded in the server logs. This level of logging provides detailed information about the data flow and can be useful for troubleshooting and debugging purposes.

In Commandline mode of pmcmd, the username, password, and server information are required for each command. This mode allows users to run commands directly from the command line without the need for an interactive session. It is useful for automating tasks and integrating with other systems.

Commandline mode allows users to write scripts for scheduling workflows. In this mode, users can use command line tools and utilities to create and manage workflows. This mode is typically used by advanced users who prefer to automate and schedule tasks using scripts. It provides more flexibility and control over the scheduling process compared to other modes.

To see the performance of each transformation, the option "Collect performance data" should be enabled in session properties. This option allows the session to collect data on the performance of each transformation during execution. This data can then be used for analysis and optimization purposes.

To override the default order by clause in a lookup query, you need to add the required ORDER BY clause and include -- (double hyphen) at the end of the lookup query override. This will ensure that the query is properly overridden and the desired order is applied to the results.

To debug a mapping, you need to configure and run the Debugger from Designer. Designer is the tool used for designing and developing mappings in Informatica. The Debugger allows users to step through the mapping logic, view data values at each step, and identify any issues or errors in the mapping. Workflow Manager is used for managing and running workflows, Workflow Monitor is used for monitoring the execution of workflows, and Repository Manager is used for managing the repository.

A deployment group is used to group versioned objects for deploying. In this context, "versioned objects" refers to software or application updates that are stored in repositories. Therefore, the correct answer is "Different Repository" because a deployment group allows you to group and deploy versioned objects from different repositories.

The primary PowerCenter server process is known as Load Manager. The Load Manager is responsible for managing the execution of workflows and sessions. It determines the order in which sessions are run, monitors the progress of sessions, and handles session recovery in case of failures. It also manages the distribution of tasks across the nodes in a grid environment. The Load Manager plays a crucial role in ensuring the efficient and reliable execution of data integration processes in PowerCenter.

The correct answer is Repository Manager because it is the tool used for managing users, user groups, and privileges in a repository. The Repository Manager allows administrators to create and manage user accounts, assign users to user groups, and set permissions and privileges for different users and user groups. It provides a centralized and efficient way to control access and manage security in the repository.

To create shortcuts for folders, the option that needs to be checked is "Allow shortcut". This option allows users to create shortcuts for folders, which are convenient links to access the original folder from a different location. By checking this option, users can easily create shortcuts for folders and access them without navigating through the entire directory structure.

The Router Transformation is used to load the rejected records. This transformation allows you to route data rows to different targets based on specified conditions. It provides a way to filter out records that do not meet certain criteria and direct them to a separate target for further analysis or processing. The Source Qualifier transformation is used to extract data from a source, and the Filter transformation is used to filter records based on specified conditions, but they are not specifically designed for loading rejected records.

When you check out an object in the workspace, the repository obtains a Write Intent lock. This lock ensures that no other user can modify the object while it is being checked out, allowing the user who checked it out to make changes without conflicts. This lock indicates the intention to write to the object and prevents any other user from obtaining a conflicting lock.

Transformation refers to the process of converting and summarizing operational data into a consistent, business-oriented format. This involves manipulating and restructuring the data to make it more useful and relevant for analysis and decision-making purposes. Extraction and loading are also important steps in the data transformation process, where data is extracted from various sources and loaded into a central repository or data warehouse. Therefore, the correct answer is Transformation, as it encompasses all of these activities.

The correct answer is 5 because the question is asking how many tools are available in designer. Out of the given options, 5 is the only number that represents the number of tools available in designer.

The PowerCenter Server sets the number of partitions to 1 by default. This means that when a task is executed, it will be processed by a single partition. This default setting is suitable for smaller tasks or tasks that do not require parallel processing. If there is a need for parallel processing or if the task is larger and can benefit from being divided into multiple partitions, the number of partitions can be manually increased.

If the username and password are incorrect in a command, a non-zero return code will be returned. This indicates that there was an error or failure in the authentication process. The specific value of 7 may be used to represent this particular type of error, but it could vary depending on the system or command being used.

The source qualifier cannot be used to filter COBOL source. The source qualifier is used in Informatica PowerCenter to filter or transform data from the source before it is loaded into the target. However, COBOL source code is a programming language used for writing applications, and it does not have a built-in source qualifier functionality. Therefore, the correct answer is No.

The actual business rules will be implemented in the Mapping Designer. This is where the transformation logic is defined and applied to the data. The Mapping Designer allows the user to create mappings between source and target objects, and apply various transformations and rules to the data during the mapping process. It provides a graphical interface to design the flow of data and apply the necessary business rules to achieve the desired output.

Interactive mode is used for handling sessions and workflows because it allows the user to interact with the system in real-time. In this mode, the user can input commands and receive immediate feedback or results. This mode is commonly used for tasks that require user input or for troubleshooting purposes.

The repository server creates and updates the repository tables through the repository agent process. This process is responsible for managing the communication between the repository server and the database where the repository tables are stored. It handles tasks such as creating new tables, updating existing tables, and ensuring data integrity within the repository. The repository agent process is specifically designed to handle these operations efficiently and securely.

The tool used to create a reusable transformation is the Transformation Developer. This tool allows developers to create transformations that can be used across multiple mappings, increasing efficiency and promoting reusability. The other options listed are not specifically designed for creating reusable transformations.

The privilege of a "Super User" allows them to execute all tasks across all folders in the repository and perform any tasks. This privilege grants the user unrestricted access and control over all actions and operations within the repository.

It is not possible to join two flat files using the source qualifier in Informatica PowerCenter. The source qualifier is used to transform and filter data from a single source, such as a database table or a flat file. To join two flat files, you would need to use a Joiner transformation or a Lookup transformation in Informatica PowerCenter.

When the Designer validates a mapping variable in a reusable transformation, it treats the variable as an Integer datatype. This means that the variable is expected to contain whole numbers without decimal places.

The entire repository can be backed up in the format of a binary file. Binary files store data in a format that is not easily readable by humans, but it is efficient for storing large amounts of data. This format is commonly used for backing up and restoring repositories because it preserves the integrity of the data and allows for easy transfer and storage.

The Joiner Transformation is used to combine data from a flat file and an Oracle source. It allows for joining rows from two different sources based on a common key. This transformation is commonly used in ETL processes to merge data from multiple sources into a single output.

When we want to improve the performance of a session, we can use the "Test load option." This option allows us to simulate a real load on the session by running it with a large number of concurrent users or transactions. By doing so, we can identify any performance bottlenecks or issues that may arise under heavy load and make the necessary optimizations to improve the session's performance. The other options mentioned, such as Test session, Test Log, and Test Monitor, are not specifically related to performance tuning and may serve different purposes.

The correct answer is ODS because an Operational Data Store (ODS) is a database designed specifically for queries on transactional data. It is used to support real-time operational reporting and analysis. ODS stores current and frequently updated data from various operational systems, making it suitable for transactional queries. On the other hand, OLTP (Online Transaction Processing) databases are used for transactional processing, OLAP (Online Analytical Processing) databases are used for complex analysis and reporting, OLCP (Online Collaborative Processing) is not a recognized term, and DSS (Decision Support System) is a broader term that encompasses various types of databases used for decision-making.

Yes, it is necessary to have the Informatica service up and running before accessing the Repository. The Repository Service manages the PowerCenter repository, which is a relational database that stores metadata. All repository clients access the repository database tables through the Repository Service. Therefore, the Informatica server, which includes the Repository Service, must be running to access the repository. Additionally, the database service must also be up. 

The correct answer is 1,2&3. This means that all three transformations - Source Qualifier, Filter Transformation, and Router Transformation - can be used for processing the records holding deptno 10 in an employee table. The Source Qualifier transformation is used to extract data from the source table, the Filter Transformation is used to filter out records based on a condition (in this case, deptno = 10), and the Router Transformation is used to route the filtered records to different target tables based on specified conditions. Therefore, all three transformations are applicable for processing the records holding deptno 10 in the employee table.

The correct answer is Option 1 & Option 2. The joiner transformation is used to merge data from multiple sources based on a common key. It combines rows from different sources into a single row. The union transformation is used to merge data from multiple sources by appending rows together. It does not remove duplicates, but simply combines all rows from different sources. Therefore, using both the joiner and union transformations together would allow merging data from multiple sources without removing duplicates.

The load manager is responsible for reading the parameter file and expanding the flow variable values for the workflow. It manages the loading of data into the workflow and ensures that the necessary resources are allocated for the workflow execution. It plays a crucial role in coordinating and managing the flow of data within the workflow.

Workflow Manager is the component of Informatica used to configure connection details. It allows users to create, edit, and manage workflows, which are a set of instructions for data integration tasks. Within the Workflow Manager, users can define the connections to various data sources and targets, specifying the necessary connection details such as server name, database name, username, and password. This allows the workflows to access and manipulate data from different sources and load it into the desired target systems.

The correct answer is Option 1 & Option 2. The question states that the salary of all employees in an employee table needs to be added with the commission. In order to achieve this, we would need to use both the Source Qualifier and Expression transformation. The Source Qualifier is used to extract data from the source table, while the Expression transformation is used to perform calculations and transformations on the data, such as adding the commission to the salary. Therefore, both Option 1 (Source Qualifier) and Option 2 (Expression transformation) are required in this scenario.

When the incremental aggregation option is checked in session properties, a new cache is not created during every run. Instead, the existing cache is updated with the incremental changes that have occurred since the last run. This allows for faster processing and reduces the amount of data that needs to be processed during each run.

Before running a workflow, the Informatica server needs to be registered with Workflow Manager. This is because Workflow Manager is responsible for designing, scheduling, and monitoring workflows. It allows users to create and manage workflows, which are a set of instructions that define the tasks and dependencies required to complete a data integration process. By registering the Informatica server with Workflow Manager, users can ensure that the server is properly configured and ready to execute workflows.

The correct answer is "Only at some times" because Informatica PowerCenter and PowerMart are both data integration tools developed by Informatica, but they have different features and capabilities. PowerCenter is a more advanced and comprehensive tool that offers a wide range of data integration and management functionalities, while PowerMart is a simplified version with limited features. Therefore, depending on the specific requirements and needs of the user, either PowerCenter or PowerMart may be suitable for certain situations.