1. Defining a Data Warehouse

A data warehouse is a centralized repository designed specifically to store, manage, and analyze large volumes of historical and current data from various sources within an organization. It is optimized for analytical processing and business intelligence activities.

The following attributes are associated with a data warehouses:

• Designed for analytical tasks using data from various applications.

• Includes current and historical data to provide a historical perspective of information.

• Its usage is read-intensive.

• Designed to handle massive amount of data accumulated over a period (s) of business operations.

• Information is organized in well-defined tables with clear relationships for ease of access by users and analytical tools.

2. Data Warehouse Models/Types

The three main types of data warehouses include:

• Enterprise Data Warehouse (EDW)

The EDW serves as a central or main database to facilitate decision-making through the enterprise. The key benefits of having an EDW include:

• Access to cross-organizational information.

• Ability to run complex queries.

• Access to detailed insights enabling data-driven decisions and early risk assessment.

• Operational Data Store (ODS)

An ODS is a temporary storage area for near real-time data, used for operational reporting and analysis, often reflecting the most up-to-date information. It focusses on current operational data as opposed to historical trends.

• Data Mart

A data mart is a subset of a DWH that supports a particular department, region or business unit. Typically, data marts are designed for specific user groups and their analytical needs.

3. Components of Data Warehouse

A data warehouse components work together to store, manage, and analyze vast amounts of data. The key components are include:

• Operational Source Systems: They provide raw data originating from various internal and external sources, such as operational systems, third-party providers, and web-based applications.

• Load Manager: Manages the ETL (Extract, Transform and Load) processes for data extraction and transformation, ensuring that the data is adequately prepared and meets the required format prior to entry into the warehouse.

• Warehouse Manager: Oversees data storage, aggregation and analysis within the data warehouse, handling tasks like de-normalization, backup, collection and optimization for better performance.

• Query Manager: Handles user queries within the data warehouse, i.e supports querying, reporting and data retrieval, with functionality dependent on the available end-user tools.

• Detailed Data: Stores granular, raw data for complex analysis and reporting, providing comprehensive insights.

• Summarized Data: Stores predefined aggregations of detailed data for faster queries and reports, providing high-level insights for decision making.

• Archive and Backup Data: Ensures data integrity and recovery through regular backups and archival storage.

• Meta Data: Contains information about data structure, source and transformational processes, thereby supporting the ETL processes, warehouse management and querying, by providing essential context for data.

• End-user Access Tools: Include analysis, reporting and data mining tools, enabling uses to access, query and derive insights from the data.

4. Data Warehouse Architecture

Data warehouse architecture refers to the design of an organization’s data collection and storage framework. It consists of planning, designing, constructing, and managing daily operational processes for how data is used for organizational intelligence and decision support.

1) Components of Data warehouse Architecture

• Data Sources: These are operational databases and external systems from which raw data is extracted, such as databases, spreadsheets, XML AND JSON files, emails and images.

• Extract, Transform and Load (ETL) Processes: ETL processes are responsible for extracting data from the source systems, transforming it into a standardized format (thus ensuring data consistency), and loading it into the data warehouse.

​ Further, through data validation, cleansing and standardization, the ETL processes contribute to data integrity by ensuring that the data is accurate, complete and reliable.

• Data Staging Area: This is a temporary storage location that holds the data before it is processed and integrated into the data warehouse.

• Data Warehouse Database: This is the central repository where the cleansed, integrated, and historical data is stored. This database is optimized for analytical queries and reporting.

• Metadata Repository: Metadata, or data about the data, is stored in this repository, providing information about the data warehouse's structure, content, and usage.

• Front-end Tools: Front-end tools (business intelligence tools) enable users to access, analyze, and visualize the data stored in the data warehouse, supporting informed decision-making.

2) Types of Data Warehouse Architecture

• Single-tier Architecture

The data warehouse is built on a single, centralized database that consolidates all data from various sources into one system.

Suitability: Suits small-scale applications and organizations with limited data processing needs.

Advantage: Minimizes the number of layers and simplifies overall design, leading to faster processing and access.

Disadvantage: Lacks the flexibility and modularity of more complex architectures.

• Two-tier Architecture

The data warehouse connects directly to business intelligence (BI) tools, often through an online analytical processing (OLAP) system.

Suitability: Ideal for businesses with moderate data volumes and relatively simple reporting or analytic needs.

Advantage: Provides faster access to data for analytic purposes.

Disadvantage: May face challenges in handling larger data volumes, as scaling becomes difficult due to the direct connection between the warehouse and BI tools.

• Three-tier Architecture

The model separates the system into distinct layers i.e., the data source layer, staging area layer and the analytics layer, thus enabling efficient ETL processes, followed by analytics and reporting.

Suitability: Ideal for large-scale enterprises that require scalability, flexibility and ability to handle large data volumes.

Advantage: Enables businesses to manage data more efficiently and supports advanced analytics and real-time reporting.

3) Data Warehouse Architecture Patterns/Schemas

A data warehouse schema is a blueprint of how data is related logically within a data warehouse.

The basic components of all data warehouse schemas are the Fact and Dimension tables, whose roles are as follows:

• Fact Tables: Aggregates metrics, measurements or facts about business processes. Fact tables store primary keys of dimension tables as foreign keys within the fact table.
• Dimension Tables: Provide descriptive attributes needed to interpret the metrics provided for in the fact tables.

The following are the common schemas used in data warehousing:

• Star Schema

Stores data in a star format, consisting of a central table (fact table) and a number of directly connected tables (dimension tables). The fact table contains information about metrics or measures, while the dimension tables contain information about descriptive attributes.

• Snowflake Schema

The snowflake schema consists of a central table (fact table), and a number of other tables (dimension tables and sub-dimension tables.)

​ Comparison between Star and Snowflake Schemas

5. Real World Applications of Data Warehouses

1) Spotify: Enhanced Customer Insights

Spotify uses its data warehouse in curating customer insights and enables:

• Creation of personalized playlists based on consumers' listening habits.
• Identification of emerging music trends across different demographics.
• Optimization of users' interface based on interaction patterns.

2) Airbnb: Market Analysis and Pricing

Airbnb uses its data warehouse to analyze accommodation markets globally, thus enabling:

• Dynamic pricing recommendations for hosts.

• Identification of underserved market segments.

• Personalized search results based on user preferences.

• Fraud detection and security enhancement.

3) Amazon: Supply Chain Optimization

Amazon’s data warehouse supports its global supply chain operations. Through analysis of historical order data and inventory levels, the company optimizes:

• Inventory placement to minimize shipping times.
• Staffing levels based on predicted order volumes.
• Routing efficiency for delivery networks.
• Procurement decisions for high-demand products.