Data Warehousing and ETL Fundamentals

Slowly Changing Dimensions (SCD) have three standard handling approaches: Type 1 overwrites existing data; Type 2 inserts new records to preserve history; Type 3 adds new fields. 'All of the above' correctly covers all three approaches plus variations. Options A, B, and C represent individual valid techniques.

A 'wrinkle of time' refers to the latency delay between when data changes in operational systems and when those changes appear in the data warehouse. This lag can occur due to ETL batch windows, processing time, or intentional delays. It's a key data freshness metric.

Derived data is data that is calculated from other data and stored in the data warehouse. Examples include totals, ratios, percentages, or any computed values. Unlike raw data which is copied directly, derived data involves computation before storage.

When integrating acquired company data, the primary challenge is data quality - different formats, standards, duplicates, and inconsistencies must be cleansed and transformed before loading into the enterprise warehouse. Network issues, security concerns, and raw data size are secondary to ensuring clean, consistent integration.

Metadata in a data warehouse contains descriptive information about the structure, meaning, and lineage of data fields. This includes field names, data types, source systems, transformation rules, and business definitions. End users access metadata repositories or data dictionaries to understand field details without needing to examine ETL scripts or technical documentation.

Shrunken dimensions are created through the aggregation process in dimensional modeling. When a dimension table is summarized or aggregated at a higher level (such as aggregating from daily to monthly), it becomes a shrunken dimension. This technique reduces dimension size and improves query performance for summary-level analysis.

Stovepipe data marts are built independently for specific departments or business functions without integration with the enterprise data warehouse or other data marts. They are called independent data marts because each mart operates in isolation, creating data silos that can lead to inconsistent definitions and redundant data across the organization.

Bitmapped indexing is highly efficient for data warehouse applications because it uses bitmaps to represent indexing information, making it ideal for low-cardinality columns (like gender, status, yes/no fields) and complex multi-column queries common in analytical processing. B-tree indexing is better suited for OLTP systems with high-cardinality data, while vector indexing and join indexing are not standard indexing techniques used in data warehouses.

Dormant data refers to data that has been loaded into the data warehouse but is never accessed or used by any queries or reports. This wastes storage space and ETL processing resources. Organizations should identify dormant data through usage monitoring and consider archiving or removing it to optimize warehouse performance and reduce costs.

In the Corporate Information Factory (CIF) architecture, Near Line Storage (NLS) holds the highest volume of data. NLS stores archival and historical data that's not actively used but must be retained. Data warehouses contain current and historical data for analysis but have less volume than NLS. ODS (Operational Data Store) holds current operational data. Data staging areas are temporary. Near Line Storage is designed for cost-effective storage of massive historical data volumes.

In top-down data warehouse architecture (Bill Inmon's approach), the enterprise data warehouse is created first as a single, integrated repository. Then, departmental data marts are derived from the warehouse. This ensures consistency across the organization and avoids data silos. Option B is incorrect because it contradicts the top-down principle. Option C describes independent data marts (bottom-up). Option D is wrong because data marts ARE created in top-down architecture.

In bottom-up architecture (Ralph Kimball's approach), data marts are created first to address specific business needs. These data marts can then be integrated to form an enterprise data warehouse. This is faster to implement initially but can lead to consistency challenges. Option A describes top-down, not bottom-up. Option C suggests independent marts with no integration, which isn't a complete architecture. Option D contradicts the bottom-up approach.

MOLAP (Multidimensional OLAP) uses pre-computed cubes for fast query performance. However, scalability is POOR, not good. MOLAP struggles with large datasets because cubes require extensive pre-computation and storage. Processing overhead for large datasets is high because all aggregations must be pre-calculated. Dimensions are often restricted (commonly to 10 or fewer) due to cube explosion. User interface is good because pre-computed results enable instant responses. Option C is actually NOT true about MOLAP - it's the limitation.

ETL (Extract, Transform, Load) typically consumes 60-70% of a data warehousing project's time and resources. This phase involves extracting data from multiple source systems, transforming it (cleaning, standardizing, calculating), and loading it into the warehouse. The complexity comes from diverse source systems, data quality issues, business logic implementation, and ongoing maintenance. Requirements gathering is important but less time-consuming. Design and testing, while critical, don't match ETL's volume.

In data warehouse design, each aggregation level should have its own set of fact and dimension tables (Option C). This is because aggregated data has different granularity than detailed data. For example, monthly sales aggregates have different dimensions and grain than daily transactions. Option A is wrong because mixing aggregate and detail data degrades query performance. Option B is incorrect because aggregate data requires appropriate dimension tables at the same grain. Option D is inefficient - storing all aggregation levels together doesn't optimize for different query patterns.

Source Qualifier transformations use inner join as the default join type. This means only rows that match the join condition from all sources are returned. You must explicitly specify a different join type (left outer, right outer, or full outer) if needed.

In PowerCenter transformation expressions, string literals must be enclosed in single quotes. Double quotes are used for identifiers, and unquoted text would be interpreted as a column name. The || operator correctly concatenates the ports and the string literal in option A.

PowerCenter Lookup transformations primarily support relational sources (tables, views) and flat files. COBOL files are handled through source definitions and aren't direct lookup sources, and XML files require different handling. The core lookup sources are relational databases and flat files.

In PowerCenter, system variables prefixed with $$$ (like $$$SessStartTime) return string values, while unprefixed variables (like SESSSTARTTIME) return date/time datatypes. The string version is useful for display purposes, while the date/time version can be used in date calculations.

In PowerCenter's expression engine, string operations are generally faster than numeric operations due to internal processing architecture. Trimming before comparisons is unnecessary (PowerCenter handles this), || is faster than CONCAT(), and variables improve efficiency by avoiding repeated calculations.