IT603(B) • Data Mining • Unit I

Data Warehousing and OLAP Technology

Complete RGPV exam-oriented notes on data warehouse fundamentals, architecture, ETL, multidimensional schemas, OLAP servers, data cubes, query processing and indexing.

Start Unit 1 Notes
Home › Data Mining › Unit 1

1. Data Warehouse 14 Marks

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data used to support management decision-making.

Simple Meaning

A data warehouse collects data from different operational systems, cleans and integrates it, and stores it in a form suitable for analysis and reporting.

Operational Database vs Data Warehouse

Operational Systems (Sales, Accounts, HR, CRM) | v ETL Process | v Data Warehouse | v Reports, OLAP and Data Mining

Main Purpose

  • Support strategic decisions.
  • Analyze historical trends.
  • Combine data from different sources.
  • Provide consistent reporting.
  • Support OLAP and data mining.
Example: A retail company may combine billing, inventory, customer and supplier data to analyze yearly sales, product performance and regional trends.

2. Need for Data Warehousing 14 Marks

  • Operational databases are designed for daily transactions, not complex analysis.
  • Organizations store data in many separate systems.
  • Managers need historical and summarized information.
  • Business users require fast multidimensional analysis.
  • Data quality and consistency must be improved.
  • Decision support requires a single integrated view.

Business Benefits

  • Better decision-making
  • Faster reporting
  • Historical trend analysis
  • Improved data quality
  • Customer and market analysis
  • Reduced workload on transaction systems
Conclusion: Data warehousing separates analytical work from transaction processing and provides reliable historical data for management decisions.

3. Characteristics of a Data Warehouse 14 Marks

Subject-Oriented

Data is organized around major subjects such as customers, products, sales and suppliers.

Integrated

Data from heterogeneous sources is converted into consistent names, formats and codes.

Time-Variant

Historical data is stored with a time dimension, allowing analysis across months, quarters and years.

Non-Volatile

Warehouse data is mainly loaded and read. It is not continuously updated like operational transaction data.

Data Warehouse = Subject-Oriented + Integrated + Time-Variant + Non-Volatile

4. Basic Elements of Data Warehousing 14 Marks

  • Source systems: operational databases, files, APIs and external sources.
  • Staging area: temporary area for extracted data.
  • ETL tools: extract, clean, transform and load data.
  • Warehouse database: central integrated repository.
  • Metadata repository: information about data definitions, mappings and rules.
  • Data marts: department-specific subsets.
  • OLAP server: supports multidimensional analysis.
  • Front-end tools: dashboards, reports and mining applications.
Sources → Staging → ETL → Warehouse → OLAP/Data Marts → Users

5. Data Mart 14 Marks

A data mart is a smaller subject-oriented repository designed for a particular department or business function.

Examples

  • Sales data mart
  • Finance data mart
  • Human resource data mart
  • Marketing data mart

Types

  • Dependent data mart: created from an enterprise data warehouse.
  • Independent data mart: created directly from source systems.
  • Hybrid data mart: combines warehouse and external sources.

Advantages

  • Lower cost
  • Faster implementation
  • Focused analysis
  • Improved query performance

6. Data Warehouse Architecture 14 Marks

Three-Tier Architecture

Top Tier +----------------------------------+ | Reports, Dashboards, Data Mining | +----------------------------------+ | Middle Tier +----------------------------------+ | OLAP Server: ROLAP / MOLAP | +----------------------------------+ | Bottom Tier +----------------------------------+ | Warehouse DB, ETL, Data Sources | +----------------------------------+

Bottom Tier

Contains source systems, staging area, ETL tools and warehouse database.

Middle Tier

Contains OLAP servers that provide multidimensional views and operations.

Top Tier

Contains reporting, query, dashboard, visualization and data-mining tools.

Metadata

Metadata describes table structures, dimensions, measures, extraction rules, transformations and refresh schedules.

7. Extract, Transform and Load Process 14 Marks

ETL is the process of extracting data from sources, transforming it into a consistent form and loading it into a data warehouse.

1. Extract

  • Read data from databases, files and applications.
  • Capture full or incremental changes.
  • Move data to a staging area.

2. Clean and Transform

  • Remove duplicates.
  • Handle missing values.
  • Correct inconsistent codes.
  • Standardize date, currency and unit formats.
  • Integrate related fields.
  • Calculate derived values.

3. Load

  • Initial loading
  • Periodic refresh
  • Incremental loading
  • Validation and reconciliation
Source Data | Extract | Staging Area | Clean + Transform | Load | Data Warehouse

Example

Customer gender values such as M, Male and 1 may be transformed into one common value, Male, before loading.

8. Star, Snowflake and Galaxy Schemas 14 Marks

Star Schema

A star schema contains one central fact table connected directly to denormalized dimension tables.
Time Dimension | Customer ---- Sales Fact ---- Product | Location

Advantages

  • Simple design
  • Fast queries
  • Easy for users

Snowflake Schema

A snowflake schema is a normalized form of the star schema in which dimension tables are divided into sub-dimension tables.
Category ← Product | Time ← Sales Fact → Customer → City → State | Store

Advantages

  • Reduced redundancy
  • Better structural consistency

Galaxy Schema

A galaxy or fact constellation schema contains multiple fact tables sharing dimension tables.
Time / \ Sales Shipment Fact Fact / \ / \ Product Customer Store Supplier

Use

Suitable for complex organizations with multiple related business processes.

9. Fact and Dimension Data 14 Marks

Fact Table

A fact table stores numeric measures and foreign keys to dimensions.

  • Sales amount
  • Quantity sold
  • Profit
  • Discount

Dimension Table

A dimension table stores descriptive attributes used for filtering, grouping and labeling.

  • Product name and category
  • Customer name and city
  • Date, month and year
  • Store and region

Types of Facts

  • Additive: can be summed across all dimensions.
  • Semi-additive: can be summed across some dimensions.
  • Non-additive: cannot be meaningfully summed.
Fact TableDimension Table
Contains measuresContains descriptive attributes
Usually very largeUsually smaller
Contains foreign keysContains primary key
Used for calculationsUsed for grouping and filtering

10. Horizontal and Vertical Partitioning 14 Marks

Horizontal Partitioning

Rows are divided into separate partitions according to a condition such as date or region.

Sales Table ├── Sales_2024 ├── Sales_2025 └── Sales_2026

Vertical Partitioning

Columns are divided into separate tables, while a common key maintains the relationship.

Customer Table ├── Customer_Basic(ID, Name, City) └── Customer_Details(ID, Phone, Email, Address)

Benefits

  • Faster queries
  • Easier maintenance
  • Improved parallel processing
  • Efficient backup and loading

11. Data Warehouse and OLAP Technology 14 Marks

OLAP stands for Online Analytical Processing. It enables fast, interactive and multidimensional analysis of warehouse data.

Multidimensional Data Model

Data is viewed as a cube with dimensions and measures.

Dimensions: Product, Time, Location Measure: Sales Amount Time / / +--------------+ / /| +--------------+ | | Sales | | Product | Cube |/ +--------------+ Location

Key Features

  • Multidimensional view
  • Fast aggregation
  • Interactive exploration
  • Trend and comparison analysis
  • Support for complex calculations

12. OLAP Operations 14 Marks

Roll-Up

Summarizes data by moving upward in a hierarchy.

City → State → Country

Drill-Down

Moves from summarized data to detailed data.

Year → Quarter → Month → Day

Slice

Selects one value of a dimension and creates a smaller sub-cube.

Select Year = 2026

Dice

Selects a range or set of values from multiple dimensions.

Pivot or Rotate

Changes the orientation of the cube for a different data view.

Drill-Through

Moves from summarized cube data to detailed source records.

Exam Tip: Draw a cube and explain each operation using Product, Time and Location dimensions.

13. OLAP Servers: ROLAP and MOLAP 14 Marks

ROLAP

Relational OLAP stores warehouse data in relational tables and uses SQL for analysis.
  • Handles large data volumes
  • Uses relational DBMS
  • Scalable
  • May be slower for complex aggregations

MOLAP

Multidimensional OLAP stores data in specialized multidimensional cube structures.
  • Fast query performance
  • Precomputed aggregates
  • Efficient cube operations
  • May require more storage

HOLAP

Hybrid OLAP combines relational storage for detailed data with multidimensional storage for summarized data.

FeatureROLAPMOLAP
StorageRelational tablesMultidimensional cubes
Query speedModerateHigh
ScalabilityHighModerate
AggregationComputed using SQLOften precomputed
Best forVery large detailed dataFast interactive analysis

14. Data Warehouse Implementation 14 Marks

Main Steps

  1. Define business objectives.
  2. Identify data sources.
  3. Design dimensional model.
  4. Select architecture and tools.
  5. Build staging and ETL processes.
  6. Load fact and dimension tables.
  7. Create aggregates and indexes.
  8. Develop reports and OLAP cubes.
  9. Test quality and performance.
  10. Deploy, refresh and monitor.

Top-Down Approach

Build an enterprise data warehouse first and then create dependent data marts.

Bottom-Up Approach

Build individual dimensional data marts and integrate them through conformed dimensions.

Challenges

  • Poor source-data quality
  • Complex integration
  • Changing business requirements
  • Large storage needs
  • Slow ETL and query performance

15. Efficient Computation of Data Cubes 14 Marks

A data cube contains aggregations for different combinations of dimensions.

Cuboid

Each group-by combination of dimensions is called a cuboid.

Dimensions: Product, Time, Location Base Cuboid: (Product, Time, Location) 2-D Cuboids: (Product, Time) (Product, Location) (Time, Location) 1-D Cuboids: (Product) (Time) (Location) Apex Cuboid: ALL

Materialization Strategies

  • No materialization: compute aggregates when needed.
  • Full materialization: precompute all cuboids.
  • Partial materialization: precompute selected useful cuboids.

Efficient Techniques

  • Shared computation
  • Sorting and hashing
  • Parallel processing
  • Bitmap representation
  • Iceberg cube computation

16. Processing of OLAP Queries 14 Marks

Query Processing Steps

  1. Parse the analytical query.
  2. Identify required dimensions and measures.
  3. Select appropriate cuboid or aggregate table.
  4. Apply filters and grouping.
  5. Use indexes and partitions.
  6. Compute missing aggregates.
  7. Return multidimensional result.

Optimization Techniques

  • Query rewriting
  • Aggregate navigation
  • Partition pruning
  • Join optimization
  • Materialized views
  • Caching
  • Parallel execution
Example: A query asking total yearly sales by state can use a precomputed State-Year aggregate instead of scanning all transaction rows.

17. Indexing Data in a Warehouse 14 Marks

An index is an auxiliary structure that speeds up data retrieval at the cost of additional storage and maintenance.

Bitmap Index

Uses bit vectors for columns having a small number of distinct values.

Gender Column: Male → 1 0 1 1 0 Female → 0 1 0 0 1

Join Index

Stores relationships between fact-table rows and dimension-table values to speed up joins.

B-Tree Index

Useful for high-cardinality columns, ranges and ordered searches.

Advantages

  • Faster filtering
  • Faster joins
  • Improved group-by performance
  • Efficient multidimensional queries

Limitations

  • Extra storage
  • Load and refresh overhead
  • Too many indexes can reduce update performance

18. Important Comparisons 14 Marks

OLTP vs OLAP

FeatureOLTPOLAP
PurposeDaily transactionsAnalysis and decisions
DataCurrent and detailedHistorical and summarized
QueriesSimple and frequentComplex and analytical
UsersOperational staffManagers and analysts

Star vs Snowflake Schema

FeatureStarSnowflake
Dimension designDenormalizedNormalized
Query complexityLowerHigher
RedundancyMoreLess
PerformanceGenerally fasterMay need more joins

Warehouse vs Data Mart

Data WarehouseData Mart
Enterprise-wideDepartment-specific
Large and comprehensiveSmaller and focused
Higher implementation costLower implementation cost

Unit 1 Quick Revision

  • A data warehouse is subject-oriented, integrated, time-variant and non-volatile.
  • ETL means Extract, Transform and Load.
  • A data mart serves one department or subject area.
  • A star schema has one fact table and denormalized dimensions.
  • A snowflake schema normalizes dimensions.
  • A galaxy schema contains multiple fact tables.
  • Fact tables store measures; dimensions store descriptive attributes.
  • OLAP supports multidimensional analysis.
  • Main OLAP operations are roll-up, drill-down, slice, dice and pivot.
  • ROLAP uses relational tables; MOLAP uses multidimensional cubes.
  • Partial materialization balances speed and storage.
  • Bitmap and join indexes are important in warehouses.

Important RGPV Exam Questions

Long Answer Questions

  1. Define a data warehouse and explain its characteristics.
  2. Explain the need and basic elements of data warehousing.
  3. What is a data mart? Explain its types.
  4. Draw and explain three-tier data warehouse architecture.
  5. Explain ETL and data cleaning process.
  6. Explain star, snowflake and galaxy schemas with diagrams.
  7. Differentiate fact and dimension tables.
  8. Explain horizontal and vertical partitioning.
  9. Explain multidimensional data model and OLAP technology.
  10. Explain different OLAP operations with examples.
  11. Compare ROLAP, MOLAP and HOLAP.
  12. Explain data warehouse implementation steps.
  13. Explain efficient computation of data cubes.
  14. Explain OLAP query processing.
  15. Explain bitmap and join indexing.

Short Answer Questions

  1. What is a data warehouse?
  2. Define data mart.
  3. What is metadata?
  4. Define ETL.
  5. What is a fact table?
  6. What is a dimension table?
  7. Define roll-up and drill-down.
  8. What is ROLAP?
  9. What is a cuboid?
  10. Define bitmap index.
Exam Strategy: Draw diagrams for architecture, ETL, schemas and data cube. Use comparison tables for OLTP vs OLAP, ROLAP vs MOLAP and Star vs Snowflake.

Download Study Resources

Unit 1 PDF

Printable notes will be uploaded soon.

Coming Soon

Important Diagrams

Warehouse and schema diagrams coming soon.

Coming Soon

PYQ Analysis

Repeated Unit 1 questions coming soon.

Coming Soon

Frequently Asked Questions

Subject-oriented, integrated, time-variant and non-volatile.
A fact stores numeric measures, while a dimension stores descriptive information used to analyze those measures.
The star schema is generally simplest because its dimensions connect directly to the fact table.
ROLAP stores data in relational tables, while MOLAP stores data in multidimensional cube structures.
They efficiently filter low-cardinality columns and combine multiple analytical conditions using bit operations.