Informatica PowerCenter uses caching mechanisms to improve performance by reducing database lookups and increasing data retrieval speed. Caching is primarily used in Lookup Transformation, Joiner Transformation, and Aggregator Transformation.
The Lookup Transformation uses caching to store lookup data in memory, reducing repeated database calls.
| Caching Type | Description |
|---|---|
| Static Cache | Stores lookup data once and does not update during session execution. Best for reference data. |
| Dynamic Cache | Updates cache when new data is found. Used for slowly changing dimensions (SCD Type 1). |
| Persistent Cache | Saves cache across multiple session runs, avoiding redundant lookups. |
| Shared Cache | Can be shared between multiple lookups in the same mapping. Improves efficiency. |
| Recache | Refreshes cache before every run, ensuring updated data is used. |
Example: If a lookup transformation retrieves customer details from a database, a static cache avoids multiple queries by storing the data in memory.
The Joiner Transformation caches data from the master table to speed up joins.
| Caching Type | Description |
|---|---|
| Cached Join | Stores the master table in memory, reducing repeated reads. |
| Uncached Join | Reads the master table row by row, increasing processing time. |
Example: If a sales dataset (large) is joined with a country dataset (small), the country dataset is cached for faster processing.
The Aggregator Transformation uses index cache and data cache for grouping and performing calculations.
| Cache Type | Purpose |
|---|---|
| Index Cache | Stores group by keys. |
| Data Cache | Stores aggregated values for each group. |
Example: When calculating total sales per region, the index cache stores region names, and the data cache stores aggregated sales.
To optimize performance, Informatica provides cache tuning options: