Informatica IICS / IDMC — Study Materials

Introduction to Informatica IICS / IDMC

Informatica® Intelligent Data Management Cloud (IDMC) is a comprehensive cloud-native, AI-powered platform designed to help organisations manage complex data challenges across multiple platforms and cloud environments.

Key capabilities

• Data Catalog — intelligently scans metadata to discover and understand enterprise data.
• Data Integration — accesses and integrates data at scale with elastic and serverless computing.
• API and Application Integration — connects applications and automates business processes.
• Data Quality — automates insights and delivers consistent, trusted, governed data.
• Master Data Management (MDM) — provides 360-degree views of business data with intelligent insights.
• Governance and Privacy — automates data intelligence to govern, protect and democratise data.
• Data Marketplace — cloud-native, AI-powered solution for data sharing.

"The platform empowers organisations to reimagine and redefine how they manage and innovate with their data" across virtually any platform, cloud, multi-cloud and hybrid environments.

Filter Transformation

"Filter transformation is an active and connected transformation in Informatica Cloud IDMC. It is used to filter data out of the data flow based on a specified filter condition."

Key characteristics

• The filter condition returns either TRUE or FALSE.
• Default return value is TRUE.
• Setting a condition to FALSE stops the flow of records.
• Filter conditions can be defined as Simple, Advanced or Completely Parameterised.

Condition types

Simple filter condition — multiple simple conditions are evaluated using the AND logical operator in the order specified. Example: LOCATION_ID > 10 and CITY = 'Bangalore'.

Advanced filter condition — accommodates complex conditions using AND or OR logical operators. The system automatically converts simple conditions to advanced format when needed.

Example filter conditions

• SALARY > 30000
• SALARY > 30000 AND SALARY < 100000
• IIF(ISNULL(FIRST_NAME), FALSE, TRUE)
• DEPARTMENT_ID = 90
• (DEPARTMENT_ID = 60 OR DEPARTMENT_ID = 100) AND SALARY > 8000
• NOT ISNULL(STATE) AND NOT ISNULL(POSTAL_CODE)

Numeric equivalents

• TRUE — any non-zero value (allows records through)
• FALSE — zero (discards all records)

Router Transformation

The Router transformation is "an active and connected transformation in Informatica Cloud IDMC." It functions similarly to a filter transformation but with a key distinction — it allows multiple filter conditions within a single transformation, whereas filter transformations support only one condition.

Key characteristics

Output Groups — the Router transformation enables creation of multiple output groups, each with its own filter condition. This approach creates separate data flows for downstream transformations based on defined criteria.

Default Group — a default group automatically handles rows that don't match any specified filter conditions. In the example provided, records with Country_id values other than 'US', 'UK' or 'JP' would route through the default group.

Multiple Matches — unlike if-else logic, rows satisfying multiple group conditions will pass through all matching output groups.

Router vs Filter

Expression Transformation

"Expression transformation is a passive and connected transformation in Informatica Cloud IDMC" used for row-wise data manipulation and non-aggregate calculations.

Primary functions

The transformation processes incoming row data and performs:

• Field-value concatenation or splitting
• New field creation
• Hard-coded value insertion
• Non-aggregate mathematical operations

Field types available

• Output Field — passes data downstream
• Variable Field — internal calculations only
• Input Macro Field
• Output Macro Field

Performance optimisation tips

• Prioritise operators over functions
• Use the || operator instead of concat()
• Favour numerical over string operations
• Leverage variable ports to eliminate repetitive calculations

Expression transformations include predefined system variables for use within expressions, along with access to Informatica's pre-built function library.

Joiner Transformation

A Joiner transformation enables combining data from two related sources, whether heterogeneous or homogeneous. The sources must share common data for the join operation.

Key capabilities

• Joins flat files with relational tables
• Supports multiple join conditions
• Limited to equality operators only
• Requires connecting smaller datasets to the Master group for optimal performance

Join types

1. Normal Join — returns matching records from both sources.
2. Master Outer Join — "All Records from Detail source, Matching records from Master source" (equivalent to right outer join).
3. Detail Outer Join — "All records from Master source, Matching records from Detail source" (equivalent to left outer join).
4. Full Outer Join — "All records from Master source and Detail source."

Performance optimisation

• Route smaller datasets to the master group
• Apply sorted input using a sorter transformation
• Join numerical columns preferentially
• Use Normal Join when feasible
• Use Source Transformation instead for homogeneous table joins

Handling multiple sources

"To join N heterogeneous sources, N-1 Joiner Transformations are required." Multiple joiner instances can be chained together in mappings.

Caching structure

Two cache types exist: Index Cache (storing join condition ports) and Data Cache (storing remaining ports).

Aggregator Transformation

The Aggregator Transformation is "Active and Connected Transformation in Informatica Cloud IICS – IDMC." It performs aggregate calculations such as averages and sums against groups of data.

Primary uses

The transformation enables calculations like computing "the average salary for employees in each department of an organisation" by grouping data by department number and configuring expressions accordingly.

Key functions

• Sum()
• Max()
• Min()
• Count()
• Avg()

Group By fields

"Configure group by fields on the Group By tab of the Properties panel" to define data grouping for aggregate expressions. Multiple fields can be selected to create groups for "each unique combination."

Performance optimisation

• Enable the Sorted Input option to reduce cache usage
• Use Sorter or Source Transformation before the aggregator
• Apply filters before aggregation
• Group by numeric rather than string fields
• Limit the number of ports

Cache types

1. Index Cache — contains group by ports
2. Data Cache — contains non-group by ports

Special features

• Nested aggregate functions — allow aggregate functions within other aggregate functions (e.g. MAX(SUM(SALES))).
• Conditional clauses — reduce aggregation row counts using TRUE / FALSE evaluations.

Sorter Transformation

The Sorter Transformation is a "Passive and Connected Transformation in Informatica Cloud IICS – IDMC." It serves to arrange data in ascending or descending order based on specified columns, supporting numerical, string and date types. The transformation can also eliminate duplicate records.

Key functionality

When multiple sort conditions are configured, the system applies them sequentially. The documentation explains: "The mapping task treats each successive sort condition as a secondary sort of the previous sort condition." For example, OrderID could sort in descending order first, then ItemID would sort within each OrderID group.

Properties and features

The Sorter includes an advanced property called "distinct" that removes duplicate data when enabled.

Sorter Cache characteristics:

• Uses only one cache type (Sorter Cache)
• Processes all incoming data before sorting
• Relies on cache memory allocation
• Task fails if insufficient memory is available

Performance optimisation

Place a Filter transformation before the Sorter to avoid unnecessary sorting operations. Additionally, sorted data can enhance performance when used with Lookup or Aggregator transformations configured for sorted incoming fields.