Informatica PowerCenter Developer Specialist cheat sheet

Repository metadata is stored in tables prefixed OPB_ in the repository database (for example OPB_MAPPING, OPB_SESSION, OPB_WORKFLOW); the Repository Service is the only supported writer, and direct SQL updates corrupt objects.

Folder permissions are Read, Write, Execute and Administer; Administer exists only on shared folders and controls folder properties plus other users' permissions.

A local shortcut references an object in the same folder; a global shortcut references an object in a shared folder and is the only path by which the referring folder reaches that object.

"Save as Version" stores a snapshot in the object's version history; versions can be compared and restored, and only one version of an object can be checked out at a time.

View Dependencies lists objects the selected object uses; View Dependents lists objects that use it; deletion is blocked while dependents remain.

Scripted deployment uses pmrep with XML: objectexport, objectimport, deployfile, createFolder, listobjectdependencies, and validate are the commands used in promotion scripts.

Repository Manager queries can be saved and re-run; a query may target one folder or the whole repository and returns a result list from which objects are opened or edited.

Labels mark an object state for deployment tracking; checking out an object makes it writable to the checking-out user and read-only to everyone else, and Undo Check Out discards local edits.

Copies and shortcuts differ in maintenance cost: a copy is an independent object that must be re-edited separately, while a shortcut reflects every change to the original.

Relational source and target definitions are created by importing metadata over ODBC; flat file definitions are created by specifying file location, code page, delimited or fixed-width layout, text qualifier, header/footer line counts and the column-name row.

Dragging a relational or file source into a mapping adds a Source Qualifier automatically; the ports kept in the Source Qualifier are exactly the columns the Integration Service extracts.

Source Qualifier properties that change extraction: SQL Query (full override), User Defined Join, Source Filter, Select Distinct, Number of Sorted Ports, Pre-SQL and Post-SQL.

Using the same source definition twice in one mapping creates two separate source instances; each instance is read as its own pipeline and can be tuned independently in the session.

The Target Load Plan orders targets that have no dependency between them; targets linked through primary-key/foreign-key constraints are always loaded in dependency order regardless of the plan.

"Treat source rows as" is a session property with values Insert, Update, Delete, Upsert and Data Driven; it must be Data Driven for Update Strategy codes from the mapping to be honored.

Target Load Type Normal issues row-level SQL; Target Load Type Bulk hands rows to the database's external loader and is faster for large volumes but changes error reporting granularity.

A source or target definition may be marked reusable and used by many mappings; a change to a reusable definition affects every mapping that instantiates it.

Expression transformation ports evaluate top-down in three blocks: input ports, then variable ports in listed order, then output ports; a variable cannot reference a port defined below it.

Filter transformation passes a row only when its condition returns TRUE; a condition that evaluates to NULL or FALSE drops the row.

Router transformation sends a row that matches no group condition to the default group, so the default group is the mechanism for capturing otherwise-unrouted rows.

Aggregator transformation requires explicit Group By ports; enabling Sorted Input removes the internal sort but only if incoming rows are already ordered on the group-by ports.

Joiner transformation accepts exactly two inputs (master and detail), supports Normal, Master Outer, Detail Outer and Full Outer joins, and requires both inputs sorted on the join keys.

Union transformation merges multiple input groups into one output stream without eliminating duplicate rows, so it behaves as UNION ALL rather than UNION.

Update Strategy constants are DD_INSERT = 0, DD_UPDATE = 1, DD_DELETE = 2 and DD_REJECT = 3, returned in a numeric output port that the session reads when Treat Source Rows As is Data Driven.

Sequence Generator: NEXTVAL returns the next value for each call, CURRVAL returns the last value generated in the same session, and Number of Cached Values (default 1000) allocates values in blocks per session.

Lookup Policy on Multiple Match options are Report Error, Use First Value, Use Last Value and Use Any Value; a Default Value is returned when the lookup condition finds no match.

Lookup cache modes are uncached, static cache and dynamic cache; a dynamic cache adds the NewLookupRow port, which returns 1 for a new row, 2 for a changed row and 0 for an unchanged row.

Dynamic lookup supports Update Else Insert and Insert Else Update only when the lookup table is also the mapping target, so cache inserts can be written out.

A persistent lookup cache is written to the file named in the Lookup cache file name property and reused on later runs; it is only safe while the lookup table is unchanged.

Rank transformation selects Top or Bottom N rows per group using the Rank port, Number of ranks and Group By ports, and requires sorted input on the group-by ports; the Rank index port exposes the position.

Aggregator Transformation Scope values are Row, Transaction and All Input; only All Input lets the transformation aggregate across the whole input instead of per transaction block.

Stored Procedure Execution Option values include Run on Every Row, Run Once, Run on First Row, Source Pre Load, Source Post Load, Target Pre Load and Target Post Load.

An unconnected lookup is invoked from an expression as :LKP.LOOKUP_NAME(arg1, arg2, ...) and returns one output port value per call; a connected lookup instead receives rows through the pipeline.

Transaction Control transformation issues COMMIT or ROLLBACK per condition and is only meaningful when the target supports commits; the session commit interval still sets the default boundaries.

SQL transformation types are Query, Insert, Update, Delete and Stored Procedure; Static SQL is parsed at design time while Dynamic SQL is parsed at run time.

A mapplet may contain transformations, source definitions and Mapplet Input/Output transformations, but it cannot contain target definitions and cannot contain another mapplet.

Mapplet Input transformation receives ports from the mapping; Mapplet Output transformation returns ports to the mapping, and a mapplet needs at least one input port to be valid inside a mapping.

Mapplets are built in the Mapplet Designer and reusable transformations in the Transformation Developer; both are folder objects and can be shared to other folders with global shortcuts.

A reusable transformation is edited outside the mapping; changing it affects every mapping that instances it, and those mappings must be revalidated before they run.

Mapping parameters and variables ($$) can be defined on a mapplet and given values from the mapping's parameter file or from mapping parameter values at run time.

If a mapplet's ports are added, removed or retyped, mappings that use it fail validation until the mapplet instance is refreshed and the mapping revalidated.

A shortcut to a mapplet keeps a single definition across folders; copying a mapplet instead creates an independent object that must be maintained separately.

View Dependencies / View Dependents on a mapplet show the mappings that reference it, which is the impact list for any planned change.

Workflow Manager task types: Start, Session, Command, Decision, Assignment, Timer, Event Wait, Event Raise, Email, Link, Control, Stop, End, and Worklet.

A worklet is a reusable workflow containing tasks but no Start task; it is created in the Worklet Designer and inserted into workflows like any other task.

Link conditions use built-in and user-defined variables such as $SessionName.Status = SUCCEEDED or $TaskName.Status = FAILED; when several outgoing links are true, the downstream paths run in parallel.

Decision task evaluates a condition on workflow variables and follows the TRUE or FALSE link; Assignment task sets a workflow variable from an expression for later links and tasks.

Timer task delays the outgoing path by a relative wait or until an absolute time, and Event Wait suspends the workflow until a predefined or user-defined event is raised.

Session properties that control execution behavior include Treat source rows as, Commit interval, Error limit, Recovery strategy, Target load type, Enable, and Override tracing.

Session partitions use Database partitioning, Hash Auto-Keys, Hash User Keys, Key Range, Round-Robin or Pass-through; partition count and partition keys are set on the source qualifier in the session.

A workflow can be run on demand or scheduled on the Integration Service with time-based or repeat settings; a scheduled instance uses the Integration Service clock, not the client machine clock.

Recovery requires the workflow recovery option to be enabled and a session Recovery strategy of Resume or Restart; Resume continues from the last commit point while Restart begins again.

Pushdown optimization is set per session as Source, Target or Full; it converts compatible mapping logic to database SQL, and only transformations the database can execute are pushed.

Enabling Sorted Input on Aggregator, Sorter or Joiner removes the internal sort only when the data is physically ordered on the keys, typically by setting Number of Sorted Ports in the Source Qualifier to match.

Select Distinct in the Source Qualifier and removal of unused ports reduce row count and row width, cutting network traffic, buffer turnover and transformation work.

Lookup tuning: enable caching, index the columns used in the lookup condition, size the caches from expected row count, and use a persistent cache for repeated runs over static data.

Session partitioning parallelizes a pipeline; the partition count should not exceed available CPU threads or database partitions, and partition keys should spread rows evenly to avoid skew.

DTM Buffer Size (session property, default 12,000,000 bytes) sets total pipeline memory; increasing it reduces disk spill when transformations like sorter or aggregator overflow.

Commit interval trades throughput against recovery cost: a larger interval reduces commit overhead but increases the rows redone after a failure when the session resumes.

Additional Concurrent Pipelines lets several source pipelines inside one session run at the same time when the Integration Service has spare resources.

Cost-based optimization lets the Integration Service use database statistics to build source SQL; turning it off forces the PowerCenter-generated plan when the database optimizer picks a poor one.

Session Error limit: 0 makes the session fail on the first row error, while a positive value allows that many row errors before the session fails; the count is cumulative for the session.

Rows rejected by a target or by the Update Strategy are written to a reject (.bad) file in the directory set on the session, and the rejection reason is recorded in the session log.

Update Strategy DD_REJECT = 3 marks a row for rejection; a Router or Filter that diverts bad rows to an error table is the mapping-level alternative to target rejection.

Recovery strategy Restart reprocesses the session from the beginning, Resume continues from the last commit point, and Fail leaves the session failed with no automatic recovery.

Recovery requires the workflow recovery option to be enabled; with Resume, the Integration Service skips rows already committed, so commit interval determines how much work is redone.

Workflow Suspend on error pauses the workflow after a task failure so the cause can be corrected and the workflow recovered instead of rerun from the Start task.

Fail parent if this task fails propagates a task failure up the workflow; without it, downstream behavior is decided purely by link conditions.

Tracing levels are Terse, Normal, Verbose Initialization and Verbose Data; session-level Override tracing changes the level for one session without touching the Integration Service setting.

Failure alerting is built from link conditions: a link with $SessionName.Status = FAILED to an Email or Command task notifies operators without stopping the remaining branches.