Informatica PowerCenter Transformations Quiz

In Informatica (and similar ETL tools), a sequential batch runs sessions one after another - each session starts only after the previous one completes. This is different from concurrent batches which run multiple sessions simultaneously.

To join n tables, you need n-1 join operations. This is a fundamental database concept - each join combines two result sets, so joining 684 tables requires 683 joins/joiners. Option A (342) would be half, which is incorrect. Option D is wrong because Informatica can handle large numbers of joins through multiple Joiner transformations.

To solve this question, the user needs to know about surrogate keys and how they can be generated in Informatica.

Surrogate keys are system-generated keys used as primary keys in a database. These keys have no business meaning and are used to uniquely identify each record in a table.

Now, let's go through each option and explain why it is right or wrong:

A. Yes: This option is correct. Surrogate keys can be generated in Informatica using various methods other than the sequence generator transformation. For example, we can use the Rank or Expression transformations to generate surrogate keys.

B. No: This option is incorrect as it is possible to generate surrogate keys in Informatica using other transformations besides the sequence generator.

C. May be: This option is incorrect since generating surrogate keys without sequence generator in Informatica is possible, it is not a case of "may be."

D. Surrogate keys cannot be generated in informatica: This option is incorrect. Informatica provides several options to generate surrogate keys, and sequence generator is just one of them.

The Answer is: A

Yes, Informatica allows updating targets without the Update Strategy transformation. You can configure session properties to treat source rows as Update (Insert, Delete, or Update), or use pre-SQL/post-SQL in the target. Option A is incorrect because pmcmd is a command-line utility for executing workflows, not for enabling updates. Option D is partially true but not the direct answer.

In Informatica, a Lookup transformation can be used to filter records. For example, if a lookup finds no match (returning NULL values), subsequent transformations (like a Filter) can drop those records based on the lookup's output. Thus, filtering via lookup is possible.

In Informatica PowerCenter, a Router is an active transformation because it can change the number of rows that pass through it (e.g., dropping rows that do not match any group condition). Passive transformations must preserve the row count and row identity.

Pushdown optimization is a performance improvement technique where Informatica pushes transformation logic to the database (source or target). This reduces data movement and leverages database processing power. Options B and C describe specific transformations or variables, not the concept of pushdown. The key benefit is performance optimization.

The Normalizer transformation processes COBOL files because COBOL data structures often contain OCCURS clauses (repeating groups). Normalizer can denormalize hierarchical COBOL data into relational format. Router is for routing data based on conditions, XML is for XML sources, and Expression is for row-level calculations - none handle COBOL OCCURS.

Data Driven is a valid option in the target settings for Update Strategy transformation. When selected, the Integration Service follows the row-by-row instructions (DD_INSERT, DD_UPDATE, DD_DELETE) coded in the Update Strategy transformation. Options A, B, and C (Merge, Insert into, Mount) are not standard target options in Informatica.

Union is an active transformation because it changes the number of rows - it combines data from multiple inputs and outputs all rows, potentially more than any single input. Active transformations modify the row count. Passive transformations maintain the same row count.

Dynamic cache is not supported for unconnected lookup transformations in Informatica. Only connected lookup transformations can use dynamic cache, which allows the cache to be updated during session execution. Unconnected lookups use static cache only.

The OCCURS attribute in Normalizer transformation specifies how many times a column or group repeats in a single source row. This corresponds to the COBOL OCCURS clause. It's not the column length, group columns, or source column name - it's the repetition count.

Yes, the Rank transformation uses cache to perform ranking operations. It caches rows to compare them based on the rank port and criteria, then outputs the top/bottom N rows. Without caching, it couldn't compare all rows to determine ranks.

Yes, the Union transformation in Informatica allows duplicates. Unlike SQL's UNION which removes duplicate rows by default (use UNION ALL to keep them), Informatica's Union simply concatenates all rows from multiple inputs without any duplicate elimination.

Yes, you can configure sessions to generate a new log file each run. This is done by enabling session log options and configuring the log file name to include timestamps or run instance IDs. Each execution then creates a separate log instead of overwriting.

Cache can be reused across workflows by enabling persistent cache in the lookup transformation properties. When configured as persistent cache files are saved to disk and reused by subsequent workflow runs. This is not automatic - it requires specific configuration settings.

Informatica allows configuring custom delimiters for output flat files in session properties. You can specify comma, tab, pipe, or other characters as field separators in the target file definition.

Informatica Lookup transformation supports flat files as lookup sources. You can configure a flat file as a lookup cache source or use a connected lookup with a flat file source definition.

Filter transformations should be placed near the source to reduce data volume early in the pipeline. This prevents processing unnecessary rows downstream, improving overall mapping performance.

Joiner transformation supports four join types: Inner join (matching rows only), Master outer join (all master rows), Detail outer join (all detail rows), and Full outer join (all rows from both).