IICS Transformations — Reference Guide

Informatica Intelligent Cloud Services (IICS) — Cloud Data Integration (CDI) mapping design

Transformations covered:

• Aggregator
• Router
• Expression
• Sequence Generator
• Update Strategy

1. Aggregator Transformation in IICS

• Type: Active, Connected
• Purpose: Performs aggregate calculations (SUM, AVG, COUNT, MAX, MIN, MEDIAN, VARIANCE, STDDEV, FIRST, LAST) across groups of rows, similar to a GROUP BY in SQL.

How it works

• Fields marked “Group By” define the groups; one output row is produced per unique group — this is why it’s Active, since row counts can change.
• Aggregate functions can only be used inside the Aggregator, never directly in an Expression transformation.
• The Secure Agent caches group values and running calculations in memory (index + data cache); if the cache exceeds its limit, it spills to disk, which slows the mapping task.
• “Sorted Input” tells the engine the data is pre-sorted on the group-by fields, letting it release a group as soon as the key changes — far less memory, much faster. Pair with a Sorter upstream if the source isn’t already sorted.

Key properties

• Sorted Input: Big performance gain when source data is already sorted on group-by fields.
• Aggregator Cache Size: Memory allocated on the Secure Agent before spilling to disk; default is Auto, can be overridden for large volumes.
• Tracing Level: Controls log detail; useful when troubleshooting slow/failing Aggregators.

Typical use case

A common use case is rolling up transaction-level records into daily or monthly sales summaries by region and product — e.g., GROUP BY region, product_id with SUM(sales_amount) and COUNT(order_id) — the kind of summarization needed for reporting and dashboards.

2. Router Transformation in IICS

• Type: Active, Connected
• Purpose: Tests rows against multiple conditions and routes each row down one or more matching output groups in a single pass — a multi-way alternative to chaining several Filters.

How it works

• User-defined Groups each carry a filter condition, plus a built-in Default group that catches rows matching nothing — unlike Filter, which simply drops them.
• A row can match more than one group’s condition; if so, it’s sent down every group it satisfies (rows duplicate across matching branches).
• Each output group connects to its own downstream transformation or target on the mapping canvas.
• Because it can multiply rows across branches, it’s classified Active.

Router vs. Filter vs. Expression’s IIF

Transformation	When to use
Router	Multiple conditions, multiple output branches, one pass through the source.
Filter	One condition; rows pass or are dropped — no alternate path.
Expression + IIF	Row-level conditional value in the same output row; no branch split.

Typical use case

A Router is commonly used to split customer or transaction records by status — e.g., Active, Inactive, Pending — into separate branches with different downstream logic or targets, while a Default group catches malformed records for a rejects table, all from a single source read instead of multiple passes.

3. Expression Transformation in IICS

• Type: Passive, Connected
• Purpose: The general-purpose row-level calculator: cleansing, string/date manipulation, conditional logic (IIF/DECODE), concatenation, type conversion, derived fields — one row in, one row out.

How it works

• Three field types: Input, Output, Variable. Variables build a calculation in stages and can reference earlier variables within the same transformation.
• Fields are evaluated top-to-bottom as listed in the Mapping Designer, so a Variable must sit above any field that references it.
• Cannot use aggregate functions and cannot change row count — hence Passive.
• The expression editor validates syntax inline before saving, which speeds up iterating on cleansing logic.

Common functions

• IIF(condition, true_val, false_val): Inline conditional logic.
• DECODE(value, search1, result1, …, default): Multi-way lookup, like CASE/SWITCH.
• TRIM / UPPER / LOWER / SUBSTR / INSTR: String cleansing.
• TO_DATE / TO_CHAR / TO_DECIMAL / TO_INTEGER: Type conversions between source and target systems.
• ISNULL / IIF(ISNULL(…)): Null handling and default substitution.

Typical use case

Expressions are used across almost every mapping to standardize data type mismatches (date/timestamp formats, VARCHAR trimming) and apply business rule cleansing before rows reach the target — the workhorse transformation in any data integration pipeline.

4. Sequence Generator Transformation in IICS

• Type: Passive, Connected — no input fields
• Purpose: Generates unique, sequential numeric values — most commonly used for surrogate keys in dimension/fact tables.

How it works

• Two predefined output fields: NEXTVAL (next value each row) and CURRVAL (NEXTVAL + Increment, rarely connected directly).
• The only transformation with no editable input fields — output depends solely on its own counter.
• Can be saved as a reusable object and shared across multiple mappings, keeping keys unique across a whole subject area rather than one run.
• The persisted “Current Value” is what keeps the sequence continuous across scheduled mapping task runs.

Key properties

• Start Value: Value the sequence begins at (first run, or after reset).
• Increment By: Step size between values (default 1).
• End Value: Max value before the sequence cycles or errors.
• Current Value: Next value to generate; persists across task runs unless Reset is checked.
• Cycle: If checked, restarts at Start Value after End Value instead of failing.
• Number of Cached Values: Values pre-allocated per run on the Secure Agent; higher reduces lookups but risks gaps on failure.

Typical use case

Surrogate keys for dimension tables in a data warehouse — a shared Sequence Generator avoids key collisions across multiple scheduled mapping task runs loading the same dimension over time.

5. Update Strategy Transformation in IICS

• Type: Active, Connected
• Purpose: Explicitly flags each row INSERT / UPDATE / DELETE / REJECT for the target — essential for slowly changing dimensions (SCD) and CDC-style loads.

How it works

• Uses DD_INSERT / DD_UPDATE / DD_DELETE / DD_REJECT (values 0–3), set in the Update Strategy Expression field, typically via IIF/DECODE.
• Classified Active because rejects effectively change what reaches the target, even though it adds no fields.
• Only takes effect if the target transformation’s advanced properties enable data-driven behavior — otherwise every row is treated the same way. A very common configuration gotcha.
• The target needs correct primary/unique key fields identified so the Secure Agent can match rows for UPDATE/DELETE.

Lookup-driven SCD pattern

A common pattern pairs Lookup (check if the natural key exists in the target) + Expression (compare changed attributes) + Update Strategy (decide INSERT vs. UPDATE vs. no-op) — the backbone of most SCD Type 1/2 implementations.

Typical use case

Update Strategy is commonly used to implement SCD Type 2 history tracking on dimension tables — inserting a new versioned row when a tracked attribute changes, while unchanged rows pass through as no-ops.

Quick Comparison Summary

Transformation	Type	Changes Rows	Purpose
Aggregator	Active, Connected	Yes (grouped)	SUM/AVG/COUNT/etc. by group.
Router	Active, Connected	Yes (can multiply)	Multi-condition branch split.
Expression	Passive, Connected	No	Row-level calculations & cleansing.
Sequence Generator	Passive, Connected	No	Surrogate key generation.
Update Strategy	Active, Connected	Effectively yes (rejects)	INSERT/UPDATE/DELETE/REJECT flagging.