Informatica MCQ

Sol: B) Extract, Transform, Load

Explanation: ETL is a tool for data extraction, transformation, and loading into the target database/ warehouse.

Solution: B) 1993

Explanation: In Febuary1993, the Informatica tool stirred the IT industry, and Gaurav Dhillon and Diaz Nesamoney founded a tool that can do multiple functions in handling data over different data formats.

Solution: an ETL tool

Explanation: Informatica is a tool that does data extraction, transform and loading into the target database. This is used to handle a large amount of data in business.

Solution: All of above

Explanation: Informatica provides various components:

1. Repository manager
2. Informatica workflow manager
3. Informatica administrator control
4. Informatica power center designer

Solution:  Both a and b

Explanation: Data cleansing is the process of finding and cleaning the data from unwanted errors, duplicity in corrected and corrupted data.

Data scrubbing is a process of cleaning the data before it is saved to the data warehouse.

Solution: a) Yes

Explanation: Informatica is a software company that focuses on data integration, masking, extraction, transformation, data loading, master data management, data virtualization, data quality, data replication, etc. Informatica company has launched various versions of Informatica for handling the data.

Solution: C) Normalizer

Explanation: In Informatica, transformation is of two types:

1. Active – Inactive transformation number of rows change form source to target.
2. Passive – In this transformation number of rows remain the same from source to target.

Solution: C) Both

Explanation: In Informatica, transformation can be of two kinds:

1. Connected – In this case, transformations are connected or with the target directly.
2. Unconnected – In this case, transformations are not connected physically. Instead, they called inside another transformation.

Solution: C) Repository server administrator Console

Explanation: The repository server administration console is one of the client components used for creating a repository because it contains all the data needed in the database.

Solution: C) Sorter

Explanation:  Sorter transformation is an active transformation that sorts the data in ascending or descending order, and it is connected to the data flow. It is also configured for the case-sensitive sorting as it specifies that output rows must be distinct.

Solution: A) Source qualifier

Explanation: Source qualifier is not a reusable transformation as any changes made in this transformation cannot be reflected in mapping transformations.

Solution: D) Dynamic cache, Re cache from DB, Static cache, Persistence cache

Explanation:

The lookup cache file can be saved in the following caches:

Dynamic Cache: A lookup transformation is created to use a dynamic cache to cache the target table and insert new tables in the cache. The Informatica server dynamically inserts data to the target tables.

Persistent cache: This cache is used to save lookup cache filed and reuse them next time. A lookup transformation is processed by the Informatica server configured to use the cache.

Static cache: Informatica server creates a static cache by default, and it can be considered only by looking up tables. Static cache catches the lookup values and tables for each row that comes in transformation. Informatica server doesn't update the cache when the lookup condition is true.

Re cache from the database: Lookup transformation is configured to rebuild the cache when the persistent cache is not synchronized with the lookup table.

Solution: A) Integer

Explanation: Integer data type is used in comparing filter and aggregate transformation as the only integer data type is entered in the tables.

Solution: D) All the above

Explanation: There are various tasks supported in the control task of workflow manager:-

• Abort parent
• Stop parent
• Fail parent
• Fail me
• Stop top-level workflow
• Abort top-level workflow
• Fail top-level workflow

Solution: B and C (master and full outer)

Explanation: Types of joins supported in Informatica

• Master outer – In Informatica, master outer join returns all the records form detail rows and only matching rows form the master table.
• Normal outer – It is a default join in Informatica that returns only matching rows from detail and master tables.
• Detail outer – This type of join simply shows all master rows from the master table and matching rows from the detail table.
• Full outer – This joins shows all the matching and non-matching rows from both detail and master table.

Solution: D) All of them

Explanation: All the users, user groups, privileges, and roles are managed on the security page of the administrator tool in Informatica as a security task creates the users and groups in the repository. The repository services stored all the permission, role, and privilege assignments with the list of groups and users in the repository.

Solution: D) All of them

Explanation: The security manager plays an important role in the administrator tool. It stores users and groups in the domain configuration database and synchronizes the list of users and groups in the repository with the domain configuration database. The service manager copies the list of users and groups to the power center repository.

Solution: 2) a, b, d.

Explanation: Designer is a graphical user interface in Informatica that manage task like mapping, mapplet, transformation, and import/ export of objects.

Solution: A) Session fails.

Explanation: In Informatica, when a maximum number of sessions run and the database log file is full, that session fails. It doesn’t work out, and the user has to start a new session with space in the database for files.

Solution: B) Dimensions

Explanation: Dimensions are the entries kept by the enterprise as a record. Dimensions tables contain the information related to a subject like subject keys, values, and attributes.

Solution: A) Fact table

Explanation: The fact table and dimension table both are different as a fact table consists of the measurements of a subject, and a dimension table consists of information related to dimensions of the subject, such as attributes, keys, and values about the subject.

Solution: A) Intersect operator.

Explanation: When the intersect operator is applied in tables, only the common or matching rows between the two result sets.

Solution: B) Cache

Explanation: Overflow data is stored in cache files as the cache is a partial memory that stores the relatable files when files don't find space in the database.

Solution: B) Round- Robin portioning

Explanation: Informatica uses round-robin portioning where the number of rows to process is distributed in evenly equal proportion.

Informatica server uses hash portioning to ensure the process groups of rows with the same portioning keys in the same partition.

Solution: B) Surrogate key

Explanation: A surrogate key can be declared a primary key instead of a natural or real key. The primary key is the column's constraint that ensures that the column doesn't have any NULL value, and every value must be unique.

Solution: C) A-2, B-3, C-4, D-1

Explanation: A session is the set of information that makes data move from source to target.

Mapping is the method of representing how data flows from source to target.

Workflow is the set of instructions used to execute the task in Informatica by the Informatica server.

Mapplet – Creates or configure the set of transformations to execute the task.

Solution: E) All of them

Explanation: During the testing of ETL tools, various bugs can occur that create an error in entering the valid values for testing. Types of bugs are:

• Race condition bug hangs and crashes the system.
• Cosmetic bug relates to the application's graphical user interface that includes font size, font style, navigation, spelling mistake, etc.
• Input/ output bug controls the input and output values. It does not accept the valid values (only accept invalid values).
• A calculation Bug is a mathematical bug that does mistakes in calculating.
• Hardware bug reflects that the application is not supported by hardware.
• Other bugs are:- Help source bug, version control bug, Load condition bug, boundary analysis related bug, Equivalence class partitioning related bug.

Solution: A) Staging area

Explanation:  In the ETL process, data is initially held by the staging area, where data cleansing and merging are performed before loading it to the data warehouse.

Solution: B) 5

Explanation:  There are five stages in ETL testing in Informatica.

• Identifying the requirements and data sources
• Data acquisition
• Implementation of dimensional modeling and business logic on the acquired data.
• All the data is populated, and new data is formed,
• Build reports from the newly formed data.

Solution: B) Metadata testing

Explanation: Metadata testing is the type of  ETL testing that includes testing of data length check, Index/ constraint check, and data type check.

Data accuracy testing is carried out to check whether the data is accurately loaded and transformed as expected.

Data completeness testing ensures that all the data is loaded to the target from the source completely. Tests are run to compare and validate counts, aggregates, and actual data between the source and target for columns with or without specific or simple transformations.