Data Warehousing and ETL Fundamentals

In Informatica PowerCenter, both Lookup and Stored Procedure transformations can be configured as unconnected (not connected to the pipeline, called via expressions). Since both lookup and stored procedure support this mode, selecting either one alone is incomplete, making 'both' the correct choice.

Data warehouses are specifically designed to store and analyze historical data over time, unlike operational databases that handle current transaction processing. This temporal dimension enables trend analysis, reporting, and business intelligence.

Cognos is IBM's business intelligence and reporting platform, used for creating dashboards, reports, and analytics. Informatica and DataStage are ETL (Extract, Transform, Load) tools focused on data integration, not reporting. Ab Initio (misspelled as Abnitio) is also an ETL tool.

Constraint-based load ordering uses primary key and foreign key relationships to determine the correct sequence for loading data into multiple related tables. This ensures parent records are loaded before child records to maintain referential integrity. E-mail is unrelated to data loading, and target load plan is a different concept.

An ETL mapping includes source definitions (where data comes from), target definitions (where data goes), and transformations (how data is modified in between). All three components are essential parts of a complete data flow. None of these alone constitute a complete mapping.

In Informatica PowerCenter (which this question appears to reference), transformations are classified into two main categories: Active transformations (which change the number of rows passing through them, like Filter or Aggregator) and Passive transformations (which don't change the row count, like Expression or Lookup). The 2 count refers to these fundamental types.

A data warehouse is a specialized database designed for query and analysis rather than transaction processing. It consolidates data from multiple sources into a single repository optimized for reporting and analytics. While it uses database technology, 'database' is the most accurate answer among the options given.

A data mart is a targeted, subject-oriented subset of a data warehouse designed to serve a specific business line, department, or user group. It is not a variable, making the other options incorrect.

Datamarts are typically classified into two main types: Independent datamarts (created directly from operational systems without a data warehouse) and Dependent datamarts (created from a central data warehouse). Some sources mention a third type (Hybrid), but the standard answer in most ETL/data warehousing contexts is 2 types.

Data warehouses are specifically designed to store historical data for long-term trend analysis and decision-making. They are optimized for read-heavy analytical queries, not the frequent read-write operations of operational databases. Operational data is stored in OLTP systems, while data warehouses archive the historical snapshots.

Data warehouses use denormalized schemas (star and snowflake schemas) which reduce the number of joins needed for queries. They also create numerous indexes to optimize query performance on large datasets. Both fewer joins and more indexes are key architectural advantages that enable fast analytical queries.

SCD Type 1 (Slowly Changing Dimension) overwrites old data with new data, storing only the current state of the record. No history is maintained - when a value changes, it is simply replaced. This is the simplest SCD method and is appropriate when historical tracking is not needed.

SCD Type 2 maintains both current and historical data by creating new rows for each change, preserving the complete history of changes. Unlike Type 1 which only keeps current data, Type 2 tracks every version of a record with effective dates or version numbers. This allows historical analysis of how values changed over time.

CREATE, ALTER, DROP, and TRUNCATE are DDL (Data Definition Language) commands that define or modify database structure. CREATE makes new objects, ALTER changes structure, DROP removes objects, and TRUNCATE empties a table while keeping its structure. These commands work on the schema, not the data itself.

INSERT, UPDATE, and DELETE are DML (Data Manipulation Language) commands that manipulate the actual data within database tables. INSERT adds new rows, UPDATE modifies existing data, and DELETE removes rows. These operations work on the data content, not the table structure.

SELECT is classified as DRL (Data Retrieval Language) or DQL (Data Query Language) - a category that specifically handles querying and retrieving data from databases. Unlike DML (which modifies data) or DDL (which modifies structure), SELECT only reads data without making any changes to the database.