Data Reduction is the process of reducing the volume of data while maintaining its integrity and analytical value.

Why Data Reduction is Needed

In data warehousing, datasets are very large. Data reduction helps to:

• Improve query performance
• Reduce storage cost
• Speed up data mining
• Make analysis faster and easier

Goal

To obtain a reduced representation of data that:

• Is smaller in size
• Produces almost the same analytical results

Types of Data Reduction Techniques

1. Data Cube Aggregation

Data is stored at detailed (low-level) form and then aggregated into higher-level summaries.

Examples:

• Daily sales → Monthly sales
• Monthly → Yearly
• City → State → Country

Used in:

• OLAP Cubes
• Roll-up operations
• Summary tables

2. Dimensionality Reduction

Reduces the number of attributes (features) in the dataset.

Techniques:

• PCA (Principal Component Analysis)
• Feature Selection:
  • Correlation
  • Chi-square
  • RFE (Recursive Feature Elimination)
• Wavelet Transform

Benefits:

• Reduces model complexity
• Removes irrelevant/redundant data
• Improves accuracy and speed

3. Data Compression

Stores data in a compact format.

Types:

• Lossless Compression
  • No data loss
  • Examples: ZIP, Huffman Coding
• Lossy Compression
  • Some data loss allowed
  • Used in images, audio, video
• Other Methods
  • Run-Length Encoding
  • String Compression

4. Numerosity Reduction

Replaces the original dataset with a smaller representation.

(a) Parametric Methods

Assume a model and store only its parameters.

Examples:

• Regression Models
• Log-linear Models

 Store model instead of full data.

(b) Non-Parametric Methods

No model assumption.

Examples:

• Histograms
• Clustering (e.g., K-means)
• Sampling

5. Sampling

Selecting a small but representative subset of data.

Types:

• Simple Random Sampling
• Stratified Sampling
• Systematic Sampling

Use:

• When full dataset is too large to process

6. Discretization and Binning

Reduces the number of distinct values by grouping.

Examples:

• Age (1–100) → (0–10, 11–20, …)
• Income → Low, Medium, High

Benefits:

• Reduces data size
• Simplifies analysis
• Improves model performance

A schema in a data warehouse defines how data is organized and how tables are logically connected.

It is based on Dimensional Modeling, which divides data into:

• Fact Tables → Store numeric measures (Sales, Revenue, Quantity)
• Dimension Tables → Store descriptive attributes (Time, Product, Customer)

 Schema design determines how fact and dimension tables are arranged.

Types of Data Warehouse Schemas

1. Star Schema

Definition:
The simplest and most commonly used schema.

Structure:

• One central Fact Table
• Multiple surrounding Dimension Tables
• All dimensions directly connected to the fact table

Diagram (Text):

Features:

• Easy to understand
• Fast query performance
• Denormalized dimension tables (redundancy allowed)

Use Cases:

• Retail sales
• Finance dashboards
• OLAP queries

2. Snowflake Schema

Definition:
A normalized version of the star schema.

Structure:

• One central Fact Table
• Dimension tables further divided into sub-dimensions (normalized)

Diagram (Text):

Features:

• Reduced data redundancy
• More complex joins
• Saves storage space

Use Cases:

• Large and complex data warehouses
• Systems with many attributes

3. Galaxy Schema (Fact Constellation Schema)

Definition:
A schema with multiple fact tables sharing common dimension tables.

Structure:

• Multiple fact tables (e.g., Sales Fact, Inventory Fact)
• Shared dimension tables (Product, Time, Store)

Diagram (Text):

Features:

• Handles complex business scenarios
• Supports multiple data marts
• Flexible but complex

Use Cases:

• Enterprise-level data warehouses
• Large organizations

Components of a Data Warehouse Schema

1. Fact Table

• Stores numerical/measurable data
• Contains foreign keys to dimension tables

Examples:

• Sales Amount
• Quantity
• Profit

2. Dimension Table

• Stores descriptive attributes

Examples:

• Time Dimension
• Product Dimension
• Customer Dimension
• Store / Location Dimension

3. Measures and Metrics

• Calculated values used for analysis

Examples:

• SUM(Sales)
• AVG(Profit)

4. Hierarchies

Used for Drill-Down and Roll-Up operations in OLAP.

Examples:

• Day → Month → Quarter → Year
• City → State → Country

Comparison of Schemas

Partitioning is a physical design technique used in data warehouses to divide large tables (especially fact tables) into smaller, manageable pieces called partitions.

Why Partitioning is Needed

Data warehouses store massive volumes of historical data (often billions of rows). Partitioning helps in:

• Faster Query Processing
  Only relevant partitions are scanned
• Faster ETL & Data Loading
  Only new/recent partitions are updated
• Easy Data Archiving / Purging
  Old partitions can be dropped without affecting others
• Better Indexing & Storage Management
  Smaller, more efficient indexes

Types of Partitioning Strategies

1. Range Partitioning (Most Common)

Data is divided based on a continuous range of values.

Example (Date-based):

• Partition 1 → Jan 2024
• Partition 2 → Feb 2024
• Partition 3 → Mar 2024

Advantages:

• Best for time-based data
• Easy archiving and deletion
• Efficient for range queries (e.g., last 3 months)

2. List Partitioning

Data is divided based on a predefined list of values.

Example (Region-based):

• Partition A → USA, Canada
• Partition B → India, Nepal, Sri Lanka
• Partition C → UK, Germany, France

Advantages:

• Good for categorical data
• Faster queries on specific categories

3. Hash Partitioning

Data is distributed using a hash function.

Example:

Advantages:

• Uniform data distribution
• Avoids data skew
• Useful when no natural partition key exists

4. Composite Partitioning (Hybrid)

Combination of two or more partitioning techniques.

Example (Range + Hash):

• First partition by Month
• Then apply hash on Customer_ID

Advantages:

• Suitable for very large datasets
• Balances performance and distribution

5. Column-based Partitioning (Columnar Storage)

Used in modern data warehouses.

Features:

• Stores data column-wise instead of row-wise
• High compression
• Very fast analytical queries

Examples of systems:

• BigQuery
• Snowflake
• Amazon Redshift

How Partitioning Works (Steps)

Step 1: Identify Large Tables

• Usually fact tables with billions of rows

Step 2: Select Partition Key

Common Keys:

• Date
• Product Category
• Region
• Department
• Customer ID

Step 3: Choose Partition Type

• Range
• List
• Hash
• Composite

Step 4: Create Partitions

• Table is divided into multiple segments on disk

Step 5: Manage Partitions

• Add new partitions (e.g., new month/year)
• Drop old partitions (archival)
• Merge or split partitions

Partition Pruning

Definition:
Query engines automatically skip irrelevant partitions.

Example Query:

 Only January partition is scanned, not the full table.

Result:
 Faster query performance

 Benefits of Partitioning

Example Scenario

Table: Sales_Fact
Partition Key: Sale_Date
Partition Type: Range Partitioning

Operations You Can Perform

• Archive old partition (e.g., SALES_2021)
• Add new partition (e.g., SALES_2025)
• Query only recent data (last 6 months)

A Data Mart is a subset of a data warehouse that focuses on a specific business area such as Sales, Marketing, Finance, HR, or Inventory.

 It is smaller, faster, and easier to manage than a full data warehouse.

Formal Definition

A Data Mart is a subject-oriented, integrated, and optimized collection of data designed to serve the needs of a particular department or business function.

Why Use Data Marts

• Provide quick access to relevant data
• Support department-specific reporting
• Reduce load on the main Data Warehouse
• Improve performance and simplify analytics
• Lower storage and management cost

Types of Data Marts

1. Dependent Data Mart

Definition:
Created from the Enterprise Data Warehouse (EDW).

Structure:

Features:

• Uses centralized and clean data
• Highly consistent and reliable

Example:
Sales Data Mart extracted from EDW

2. Independent Data Mart

Definition:
Created directly from operational systems without a data warehouse.

Structure:

Features:

• Faster to build
• May cause data inconsistency

Example:
Marketing Data Mart built from CRM system

3. Hybrid Data Mart

Definition:
Combination of both dependent and independent approaches.

Features:

• Uses both EDW and operational systems
• Flexible and faster implementation

Example:
Finance Data Mart using EDW + external banking data

Data Mart Architecture

A Data Mart consists of 3 layers:

1. Source Layer

• Operational databases
• Data warehouse
• External data sources

2. ETL Layer (Extract – Transform – Load)

• Data cleaning
• Data integration
• Data transformation
• Loading into data mart

3. Data Mart Storage / Presentation Layer

• Fact tables
• Dimension tables
• OLAP cubes
• Reports and dashboards

Schema Used in Data Marts

Data marts typically use Dimensional Modeling:

• Star Schema
• Snowflake Schema

Data Mart Components

1. Fact Tables

• Store transactional data
• Contain measures

Examples:

• Sales amount
• Revenue
• Profit

2. Dimension Tables

• Store descriptive attributes

Examples:

• Time
• Product
• Customer
• Region

Examples of Data Marts

1. Sales Data Mart

• Measures: Sales amount, Quantity
• Dimensions: Time, Product, Store

2. Finance Data Mart

• Measures: Revenue, Expenses, Profit
• Dimensions: Time, Department, Account

3. HR Data Mart

• Measures: Employee count, Salary
• Dimensions: Department, Location, Job Role

Advantages of Data Marts

Disadvantages of Data Marts