Informatica PowerCenter Transformations Quiz

Data warehouses must integrate data from disparate sources (integrated), accommodate new requirements and sources (extensible), and remain maintainable as scope grows (manageable). JAD (Joint Application Development) sessions are useful but not mandatory.

Data warehouses use neither purely normalized nor purely denormalized schemas. They employ dimensional modeling (star/snowflake schemas) which selectively denormalizes dimensions for query performance while maintaining some normalization in fact tables. Different schemas serve different purposes: dimensional modeling is the standard.

Active transformations (active Informatica transformations) can change the row count passing through them. For example, a Filter transformation removes rows that don't meet criteria, while an Aggregator transforms multiple input rows into fewer output rows. Passive transformations maintain the same row count.

OLAP systems organize data in multidimensional cubes (also called hypercubes), enabling fast analysis across multiple dimensions like time, geography, and product. This structure allows users to drill down, slice, and dice data efficiently. The alternative flat relational model would be much slower for complex analytical queries.

In ETL tools like Informatica, connected lookups receive input directly from the pipeline and can return multiple values/columns back to the pipeline. Unconnected lookups are called from expressions using the :LKP reference and can only return a single value, making them suitable for simple lookups within transformations.

A staging area in ETL processes serves multiple purposes: it stores metadata about the data, provides a workspace to apply complex business rules and transformations before loading to the target, and holds intermediate data for calculations and validation. Distractor D refers to a database schema element, not a staging area function.

The Union transformation merges multiple input groups into a single output group but does NOT remove duplicate rows - all rows from all sources pass through to the output. This distinguishes it from other transformations that might perform deduplication. While ports must match across input and output groups by name and datatype, the precision, datatype, and scale can vary as long as they are compatible. The Union transformation is passive, not active, as it doesn't change the number of rows.

When using Update Strategy with dynamic lookup cache, the session must be configured as data-driven to allow the Integration Service to interpret update strategy flags. The correct combination includes: treat source rows as data driven (enables update strategy processing), select insert (allows new rows to be added), select update as update (ensures updates use UPDATE not INSERT), and do not select delete (prevents deletion). This configuration supports both inserts and updates through the dynamic cache while maintaining cache-target synchronization.

The update strategy expression uses IIF(condition, value_if_true, value_if_false) where the numeric codes are: 0=DD_INSERT, 1=DD_UPDATE, 2=DD_DELETE, 3=DD_REJECT. The expression iif(a=1,1,0) means: if a equals 1, return 1 (update); otherwise return 0 (insert). This implements update-else-insert logic - update when the condition is true, insert when it's false. Option A would do delete-else-insert, Option B would do insert-else-insert (always insert), and Option D would do update-else-delete.

The claimed answer is correct. Joiner transformation is specifically designed to join heterogeneous sources (data from different database types, flat files, or different connections) in the mapping pipeline after data has been extracted. Source Qualifier joins can only operate within a single homogeneous source (same database connection) because they push down SQL to the source. A Source Qualifier cannot join data from different source types, while the Joiner transformation operates in memory after data is extracted from heterogeneous sources.

The Sorter transformation can be configured with the 'Distinct' output option to treat output rows as distinct, removing duplicates during the sort operation. When enabled, the Sorter uses the sort key columns to identify and eliminate duplicate rows, passing only unique rows to downstream transformations. This is more efficient than using an Aggregator or Expression transformation after sorting to remove duplicates. The other options are incorrect: duplicates are not rejected, they can be eliminated; there's no case-based duplicate sorting behavior.

Router transformation splits data into multiple output groups based on group conditions. You can connect multiple output groups to the same downstream target or transformation (e.g., send multiple groups to one target table). This is different from other rules - Router's unique capability is that multiple groups can route to the same destination. The other options are incorrect: you cannot connect multiple groups to a multiple-input transformation except Joiner; you cannot connect multiple output ports in one group to multiple transformations in the same connection; and you're not limited to one group per transformation.

In Informatica transformations, a Filter transformation drops rows that don't meet the filter condition (only matching rows pass through). A Router transformation passes ALL rows through different output groups based on conditions - it doesn't drop any rows. Thus C is correct.

To handle flat file duplicate rows as distinct rows, you need to sort the data first to group duplicates together. Sorter transformation alone is sufficient to organize the data so duplicates can be identified. Other options (source qualifier with sequence generator or expression) don't directly address the duplicate detection problem.

Lookup transformation has cache files for storing reference data during session execution. Sorter transformation uses cache/sort files for sorting operations. Joiner transformation can cache the master/source table for better performance. Thus option B (lookup, sorter, joiner) is correct.

In Informatica Aggregator transformation, aggregate operations (SUM, AVG, COUNT, etc.) are performed on the output port. The input ports receive raw data, variable ports can perform intermediate calculations, but only the output port defines the aggregate result that gets passed to the next transformation.