ETL Tools and Informatica

The Informatica repository is a metadata store that contains information about transformations, mappings, workflows, source definitions, target definitions, and other ETL objects. Metadata describes the structure, properties, and relationships of data throughout the ETL process, rather than containing the actual data itself.

Informatica PowerCenter supports heterogeneous data integration - it can extract data from various source types (databases, flat files, XML, applications, etc.) and load into different target types. This ability to work with diverse data sources and targets simultaneously is a key feature of enterprise ETL tools.

Stored Procedure transformation can execute database procedures after data loading completes, making it ideal for creating indexes on target tables. Filter and Expression transformations operate on row-level data during pipeline flow and cannot execute DDL commands like CREATE INDEX. Aggregator transformation is for summarizing data, not post-load operations.

A repository server must be uniquely identified by its network location. The host name (or IP address) identifies the machine, while the port number identifies the specific service. Server name alone is insufficient without network addressing. The repository server name is just an alias, not required for connectivity.

The Repository Agent is a background service that runs ON the Repository server machine and handles local repository operations - it is not a client. Mapping Designer, Informatica server (Workflow Manager), and PMREP command are all clients that connect TO the Repository server to request repository operations.

PMCMD is the command-line utility in Informatica PowerCenter used to start and control workflows. Repository server admin console is for repository administration, not workflow execution. PMREP is for repository management tasks.

Mapping parameters maintain a fixed value throughout the entire session execution once set. Unlike variables, they cannot change during runtime and are not automatically persisted between runs. They can be used in expressions within transformations.

The Source Qualifier transformation represents the source data and is the mandatory first transformation after any source definition in Informatica mappings. For flat files specifically, it defines how the Integration Service reads the source data. Expression, Filter, and Target transformations would come later in the data flow.

Variable ports within a transformation can be used to calculate intermediate values and assigned to output ports of the same transformation. They cannot be assigned to input ports, which receive data from upstream. Variable ports are internal to the transformation and aren't directly connected to downstream transformations.

In ETL tools like Informatica PowerCenter, a mapping is created within the Designer tool, not inside the Workflow Manager. Workflows contain tasks such as sessions, commands, and email tasks, but mappings themselves are separate objects invoked by session tasks inside a workflow.

The Aggregator transformation is active because it performs calculations like GROUP BY, SUM, AVG which can reduce multiple input rows to fewer output rows. Active transformations change the number of rows passing through. Lookup, Sequence Generator, and Sorter are passive transformations - they process one row in and produce one row out without changing the row count.

SQL override in Source Qualifier allows you to customize the default SQL query generated by Informatica, commonly by adding WHERE clauses to filter rows at extraction time. This improves performance by reducing data transfer. You cannot join heterogeneous sources with Source Qualifier SQL override - each Source Qualifier connects to one source type.

The Informatica Repository Server is capable of managing multiple repositories simultaneously. Each repository is a separate database containing metadata for different projects or environments, all administered by the same Repository Server. This allows organizations to maintain development, testing, and production repositories on the same server instance.

Informatica Integration Server can use native Oracle drivers (OCI-based) for connecting to Oracle databases. Native drivers are optimized for specific database systems and typically offer better performance compared to generic ODBC connections. The choice between native and ODBC drivers depends on configuration and requirements.

JasperETL was an open-source ETL tool (based on Eclipse) before being integrated into Talend's open-source offerings. Informatica, Kalido, and Adeptia are all proprietary, commercial ETL tools requiring licensing. Open-source ETL alternatives include Talend, Pentaho Data Integration, and Apache Airflow.

Ab initio is a Latin phrase meaning from the beginning or from first principles. Ab Initio Software is a company that provides a parallel processing ETL platform used for high-volume data integration and processing. The name reflects the tool's philosophy of building data solutions from foundational principles.

Kettle was the original name of Pentaho Data Integration (PDI), an open-source ETL tool. Spoon is the graphical GUI for designing transformations, Pan is the command-line tool for executing them, and Kitchen is the workflow scheduler. These kitchen-themed names persist as the tool's component names even after the project was renamed to Pentaho Data Integration.

Business Objects Data Integrator was originally developed by Acta Corporation and was known as ActaWorks before Business Objects acquired the company in 2002. The other options are different ETL tools: Talend Open Studio is open-source, IBM Information Server is IBM's data integration platform, and Clover ETL is a Java-based framework.