Data Warehousing
A data warehouse is a centralized repository that stores integrated data from multiple sources, organized and optimized for analytical queries and business...
Introduction to Data Warehousing: Concepts and Architecture
A data warehouse is a centralized repository that stores integrated data from multiple sources, organized and optimized for analytical queries and business intelligence rather than for day-to-day transaction processing. The distinction between a data warehouse and a conventional operational database is fundamental: operational databases, also called OLTP (Online Transaction Processing) systems, are optimized for fast, frequent, small transactions such as recording a sale or updating a customer record. Data warehouses, also called OLAP (Online Analytical Processing) systems, are optimized for complex queries that aggregate and summarize large amounts of historical data.
The defining characteristics of a data warehouse were articulated by Bill Inmon, who is often called the father of data warehousing. He described a data warehouse as subject-oriented, meaning it organizes data by business subjects like sales, customers, and products rather than by application function. It is integrated, meaning data from disparate sources is cleaned, transformed, and made consistent. It is time-variant, meaning it stores historical data across long time periods rather than only the current state. And it is non-volatile, meaning data once loaded is not updated or deleted in the normal course of operations.
The architecture of a typical data warehousing system consists of several layers. Source systems are the operational databases and external data sources. The extraction, transformation, and loading process, commonly called ETL, extracts data from source systems, transforms it by cleaning, validating, and restructuring it, and loads it into the data warehouse. The staging area is a temporary storage used during ETL processing. The data warehouse itself stores the integrated historical data. Data marts are smaller, department-specific subsets of the warehouse, focused on a particular business area. Finally, front-end tools such as OLAP cubes, reporting tools, and dashboards allow business users to query and visualize the data.
The multidimensional data model is the conceptual model used in data warehousing. It represents business data as a cube with dimensions representing different perspectives of analysis, such as time, geography, and product, and measures representing the quantities to be analyzed, such as sales revenue and units sold. This model makes it intuitive to formulate and answer analytical questions across multiple dimensions simultaneously.
For GATE CS aspirants, Data Warehousing appears as part of the DBMS syllabus and typically contributes 1 to 2 marks per year. Questions focus on conceptual definitions, schema types, and OLAP operation types.
Data Warehouse Schemas: Star, Snowflake, and Galaxy
The physical organization of a data warehouse is typically described using schemas that are adaptations of the relational model to the multidimensional nature of warehouse data. The three main schema types are the star schema, the snowflake schema, and the galaxy schema. Understanding the differences between these schemas and the trade-offs they involve is essential for GATE and for practical data warehousing work.
The star schema is the simplest and most widely used data warehouse schema. It consists of a central fact table surrounded by dimension tables. The fact table contains the measures, which are the numerical values to be analyzed such as sales amount, quantity sold, and profit, along with foreign keys that reference each dimension table. Each dimension table contains descriptive attributes of one dimension of analysis. For example, a sales data warehouse might have a fact table with columns for date key, product key, store key, and sales amount, and separate dimension tables for date (with day, month, quarter, year), product (with name, category, brand), and store (with city, region, country). The schema resembles a star with the fact table at the center, giving the schema its name.
The star schema is optimized for query performance. Analytical queries typically join the fact table with one or more dimension tables to aggregate measures by dimension attributes. With a star schema, this requires a small number of joins. The dimension tables are denormalized, meaning they may contain redundant data, which avoids additional joins but increases storage requirements.
The snowflake schema is an extension of the star schema where dimension tables are normalized into multiple related tables. For example, instead of a single store dimension with columns for store name, city, and country, a snowflake schema might have a store table that references a city table that references a country table. This normalization reduces redundancy and storage but requires more joins in queries, potentially reducing query performance.
The galaxy schema, also called a fact constellation schema, contains multiple fact tables that share some dimension tables. This is used when a data warehouse must support multiple related business processes, such as both sales and inventory, that share dimensions like product and time but have different measures.
For GATE, typical questions ask you to identify which schema type a given diagram represents, or to identify the fact table and dimension tables from a description of a business scenario. Know that the fact table contains foreign keys to dimension tables and numerical measures, while dimension tables contain descriptive text attributes. Distinguishing a star from a snowflake requires checking whether dimension tables are normalized.
OLAP Operations and Their Role in Analytical Queries
Online Analytical Processing defines the class of query operations performed on data warehouses. OLAP systems allow analysts to explore multidimensional data interactively, slicing and dicing across dimensions to uncover patterns and trends. The core OLAP operations have precise definitions that GATE tests directly.
The roll-up operation aggregates data by moving up a dimension hierarchy. For example, if you are viewing sales data at the city level, rolling up to the region level computes total sales for each region by summing across all cities within each region. Rolling up further to the country level aggregates across all regions. Roll-up corresponds to increasing the level of granularity.
The drill-down operation is the reverse of roll-up. It decomposes aggregated data into more detailed levels by moving down a dimension hierarchy. If you are viewing quarterly sales, drilling down shows monthly or weekly sales within each quarter. Drill-down reveals the details that roll-up has summarized.
The slice operation selects a single value for one dimension, producing a subset of the multidimensional data. For example, slicing the sales cube on the dimension of time by selecting only the year 2023 produces a two-dimensional view of sales by product and region for that year. A slice reduces the number of dimensions by one.
The dice operation selects a range or set of values for two or more dimensions, producing a smaller sub-cube. For example, dicing to select years 2022 and 2023, regions East and West, and products A, B, and C produces a cube containing only the data for those specific dimension value combinations.
The pivot operation, also called rotate, reorients the multidimensional view by rotating the data axes to provide an alternative presentation of the data. For example, pivoting a table that shows products in rows and years in columns to show years in rows and products in columns provides the same data from a different perspective.
OLAP systems are implemented in three main ways. MOLAP (Multidimensional OLAP) stores data in a multidimensional array structure, providing fast query response but potentially large storage for sparse data. ROLAP (Relational OLAP) stores data in a relational database and translates OLAP queries to SQL, trading some query speed for better scalability. HOLAP (Hybrid OLAP) combines both approaches, storing summary data in multidimensional form and detailed data in relational form.
For GATE, memorize the definitions and examples of the five OLAP operations and the three OLAP implementation types. Questions in this area are definitional and require precise vocabulary.