Informatica PowerCenter Fundamentals

Source Qualifier transformations are versatile readers that support relational databases, flat files, COBOL files, and certified ERP sources including PeopleSoft and SAP/R3. This wide compatibility makes them the primary transformation for reading from diverse source systems.

When the physical file format differs from the source definition, edit the source definition's properties to match the actual file format (delimited vs. fixed-width). This preserves existing mappings while correcting the metadata. No need to delete and recreate the definition.

The Integration Service must be running to execute workflows and sessions. If it's stopped, workflows won't start even though they appear valid in Workflow Monitor. The Workflow Monitor can open without the Integration Service running, but execution requires it.

You can create logical primary key-foreign key relationships in the repository metadata using Source Analyzer. These relationships exist only in PowerCenter's metadata and don't require database changes. They help with join optimization and data lineage tracking.

Each Source Qualifier transformation defines a separate pipeline in a mapping. The pipeline represents the data flow from that Source Qualifier through all transformations to the target(s). Multiple Source Qualifiers in a mapping create multiple independent pipelines.

Custom transformations can be either active or passive based on a user-configurable property. Active transformations change the number of rows passing through them, while passive transformations do not. The transformation type is not determined by algorithms, API functions, nor are they always active.

Mapping variables use the double dollar sign prefix ($$) in Informatica. Single dollar sign ($) is for session parameters, triple dollar sign ($$$) is invalid, and hash (#) is used for comments. The double dollar sign correctly identifies mapping parameters and variables.

Sorter transformation operates in a streaming fashion and doesn't require cache memory. Aggregator and Rank transformations need data cache to store group data, and Lookup needs index and data cache for lookup tables. Sorter processes data row by row without storing intermediate results.

The Service Manager handles authentication, authorization, and domain configuration in Informatica PowerCenter. Domain Manager manages domains but not security, Security Manager handles user security but not domains, and Repository Manager manages repositories. Service Manager is the central service for these core functions.

For a 24-hour clock format, HH returns hours in 12-hour format (1-12), HH12 returns 12-hour format with AM/PM indicator, and HH24 returns 24-hour format (0-23). For 10:14 PM stored in 24-hour format (22:14), any of these would return 22 when the hour is 10 PM, because the stored value is already 22.

Placing the condition with an equality operator first improves lookup performance because Informatica evaluates conditions in order and can quickly match on equality. Caching only large tables doesn't help if conditions are inefficient, matching datatypes prevents implicit conversion but doesn't optimize condition order, and conditional operators should come after equality checks.

When a mapping parameter is defined but its parameter file is deleted, the Integration Service cannot locate the parameter file and the session fails with an error. The session doesn't run with a warning, doesn't become invalid, and certainly doesn't run successfully. The parameter file is required at runtime.

Writing to a file is faster than writing to a database and eliminates database-related bottlenecks. If performance improves with file output, the target writer was the bottleneck. Writing to Oracle, tables with/without indexes, or constraints doesn't isolate the writer bottleneck as cleanly as a file target.

Making the smaller source the master source minimizes cache size because Joiner cache stores the master source data. The smaller dataset requires less cache memory. Making the smaller source the detail source would cache the larger dataset, and sorting either stream doesn't reduce cache size.

In a workflow, when multiple links have no conditions, all linked tasks execute simultaneously. This is the default behavior. The workflow doesn't fail, tasks don't execute in order, and there's no automatic priority - all tasks run in parallel.