Data Warehousing and ETL Fundamentals

DSS stands for Decision Support Systems - information systems that support business decision-making activities. These systems analyze large volumes of data to provide insights, reports, and what-if scenarios for managers and executives. Examples include data warehouses, OLAP tools, and business intelligence platforms that help with semi-structured decision making.

OLTP (Online Transaction Processing) systems are designed for transactional operations like order entry, inventory updates, and financial transactions. These systems require high data integrity and fast performance for individual transactions, which is achieved through database normalization. Normalized schemas minimize redundancy and ensure ACID properties, making OLTP systems typically 3NF normalized. In contrast, OLAP systems for analytics use denormalized schemas.

A data warehouse architecture consists of three core components: metadata (technical and business definitions about the data), summary data (aggregated facts and metrics pre-calculated for query performance), and raw data (detailed transaction records from source systems). Metadata includes schemas, transformation rules, and data lineage; summary data contains pre-aggregated metrics like monthly sales totals; raw data holds the granular source records that enable drill-down analysis.

The two primary schema types for data warehouse dimensional modeling are star schema and snowflake schema. Star schema has a central fact table connected directly to dimension tables (denormalized dimensions), while snowflake schema further normalizes dimension tables into sub-dimensions. Snowflake reduces redundancy but adds join complexity; star schema is simpler for queries but has some dimension data duplication.

SCD stands for Slowly Changing Dimension - a dimension in a data warehouse that evolves over time but not with every transaction. Common examples include customer address changes, product price updates, or employee role changes. Different SCD types (1-6) handle these changes differently: Type 1 overwrites old values, Type 2 maintains history with effective dates, Type 3 adds new columns for previous values, and so on.

There are 6 standard types of Slowly Changing Dimensions (SCD 0-5). Type 0 is no change allowed, Type 1 overwrites, Type 2 adds new rows with versioning, Type 3 adds columns for history, Type 4 uses a separate history table, Type 5 combines Type 2 and Type 4 (hybrid approach), and Type 6 is the full history tracking method that tracks all attribute changes separately. Each type addresses different business requirements for handling dimensional attribute changes.

The major ETL (Extract, Transform, Load) tool vendors in the market include IBM with Cognos Data Manager, Informatica PowerCenter, and SAP BusinessObjects Data Services. These platforms provide comprehensive capabilities for data integration from source systems, transformation logic implementation, and loading into target data warehouses or data marts. Each has strengths in different areas like IBM's enterprise integration, Informatica's market-leading position, and SAP's integration with BusinessObjects BI tools.

The two main types of parallelism in ETL processing are pipeline parallelism (breaking data flow into concurrent stages where output from one stage flows to the next) and partition parallelism (splitting data into subsets processed simultaneously on different processors). Pipeline parallelism improves throughput by overlapping different operations like extraction, transformation, and loading. Partition parallelism reduces processing time by handling multiple data partitions concurrently.

DataStage parallel jobs are compiled into Orchestrate Shell (OSH) scripts, which are then executed by the parallel engine. OSH is the native language of the DataStage PX engine. C, C++, and Java are not compilation targets for DataStage jobs.

DataStage provides 8 partitioning methods: Auto, Same, Entire, Round-Robin, Hash, Range, Modulus, and DB2. These are the standard partitioning algorithms available in the parallel framework for distributing data across partitions.

DB2 partitioning uses DB2's own partitioning map. Round-Robin distributes rows sequentially across partitions. Both are valid partitioning methods. Sort Merge and Ordered are collection methods, not partitioning methods.

Sort Merge collects sorted data from multiple partitions. Ordered collection reads all records from one partition before moving to the next. Both are collection methods. Stable Sort is not a standard DataStage collection method.

Same partitioning means data maintains its existing partition arrangement from the input link - no repartitioning occurs. This is efficient when data is already properly distributed. Entire would broadcast to all nodes, Hash uses key-based distribution, and DB2 uses DB2's algorithm.

Entire partitioning sends every row to every partition/node. This is used when all nodes need complete data, such as creating reference lookup files or when every row must be processed everywhere. Same keeps existing partitions, Hash distributes by key, DB2 uses database partitioning.

Range partitioning groups related records together based on key value ranges, ensuring data with similar keys lands in the same partition. Same maintains existing partitions, Entire broadcasts to all, Hash uses hash algorithm. Range is ideal for maintaining data locality.

DB2 partitioning uses the same partitioning algorithm as IBM DB2 database, allowing DataStage to align with DB2's data distribution. This is useful when data comes from or goes to DB2 tables. Same maintains partitions, Entire broadcasts all rows, Hash uses hash function.

Ordered collection reads all records from the first partition completely before moving to the second, and so on sequentially. Same and Entire are partitioning methods. Hash is a partitioning method. Ordered is specifically a collection strategy.

Round-Robin is often considered the most efficient partitioning method because it requires no computation - rows are simply distributed in rotation to each partition. Same requires no movement but keeps existing distribution, Entire is expensive (broadcasts all data), Hash requires hash computation. Round-Robin's simplicity makes it fastest for even distribution.

Hash, Modulus, and Range are all key-based partitioning methods that use key values to determine partition assignment. Hash uses hash function on key, Modulus uses key value modulo partition count, Range uses key value ranges. DB2 uses DB2's algorithm which is also key-based but often treated separately in documentation.

Auto and DB2 are considered keyless partitioning methods. Auto lets the framework decide based on data characteristics and context. DB2 uses database-defined partitioning maps without using data keys. Modulus is explicitly key-based (uses key mod n). Range is also key-based (uses key value ranges).