Data warehousing is the process of collecting, integrating, storing and managing data from multiple sources in a central repository. It enables organizations to organize large volumes of current and historical data for efficient querying, analysis and reporting.
Note: The main goal of data warehousing is to support decision-making by providing clean, consistent and timely access to data. It ensures fast data retrieval even when working with massive datasets.
Handling Large Data Volumes: Traditional databases store limited data (MBs to GBs), while data warehouses are built to handle huge datasets (up to TBs), making it easier to store and analyze long-term historical data.
Enhanced Analytics: Databases handle transactions; data warehouses are optimized for complex analysis and historical insights.
Centralized Data Storage: A data warehouse combines data from multiple sources, giving a single, unified view for better decision-making.
Trend Analysis: By storing historical data, a data warehouse allows businesses to analyze trends over time, enabling them to make strategic decisions based on past performance and predict future outcomes.
Business Intelligence Support: Data warehouses work with BI tools to give quick access to insights, helping in data-driven decisions and improving efficiency.
Components of Data Warehouse
Data Sources: These are the various operational systems, databases and external data feeds that provide raw data to be stored in the warehouse.
ETL (Extract, Transform, Load) Process: The ETL process is responsible for extracting data from different sources, transforming it into a suitable format and loading it into the data warehouse.
Data Warehouse Database: This is the central repository where cleaned and transformed data is stored. It is typically organized in a multidimensional format for efficient querying and reporting.
Metadata: Metadata describes the structure, source and usage of data within the warehouse, making it easier for users and systems to understand and work with the data.
Data Marts: These are smaller, more focused data repositories derived from the data warehouse, designed to meet the needs of specific business departments or functions.
OLAP (Online Analytical Processing) Tools: OLAP tools allow users to analyze data in multiple dimensions, providing deeper insights and supporting complex analytical queries.
End-User Access Tools: These are reporting and analysis tools, such as dashboards or Business Intelligence (BI) tools, that enable business users to query the data warehouse and generate reports.
Enterprise Data Warehouse (EDW): A centralized warehouse that stores data from across the organization for analysis and reporting.
Operational Data Store (ODS): Stores real-time operational data used for day-to-day operations, not for deep analytics.
Data Mart: A subset of a data warehouse, focusing on a specific business area or department.
Cloud Data Warehouse: A data warehouse hosted in the cloud, offering scalability and flexibility.
Big Data Warehouse: Designed to store vast amounts of unstructured and structured data for big data analysis.
Virtual Data Warehouse: Provides access to data from multiple sources without physically storing it.
Hybrid Data Warehouse: Combines on-premises and cloud-based storage to offer flexibility.
Real-time Data Warehouse: Designed to handle real-time data streaming and analysis for immediate insights.
Data Warehouse vs DBMS
Database
Data Warehouse
A common Database is based on operational or transactional processing. Each operation is an indivisible transaction.
A data Warehouse is based on analytical processing.
Generally, a Database stores current and up-to-date data which is used for daily operations.
A Data Warehouse maintains historical data over time. Historical data is the data kept over years and can be used for trend analysis, make future predictions and decision support.
A database is generally application specific.
A Data Warehouse is integrated generally at the organization level, by combining data from different databases
Example: A database stores related data, such as the student details in a school.
Example: A data warehouse is a centralized repository that integrates data from multiple sources to enable efficient querying, analysis, and reporting, such as the best performing school in a city.
Constructing a Database is not so expensive.
Constructing a Data Warehouse can be expensive.
Issues Occur while Building the Warehouse
1. When and How to Gather Data?
Source-driven: Data sources push updates to the warehouse periodically or continuously.
Destination-driven: The warehouse pulls data on a fixed schedule.
Perfect sync is costly, so data is slightly outdated - acceptable for analysis.
2. What Schema to Use?
Sources have varied formats.
The warehouse stores a cleaned, unified version - not a direct copy, but a consistent snapshot for analysis.
3. Data Transformation and Cleansing
Fixes errors like typos or invalid codes using reference data.
Fuzzy lookup helps match similar but not identical values.
4. How to Propagate Updates?
If warehouse schema = source schema -> easy sync.
If not -> it becomes a view maintenance challenge.
5. What Data to Summarize?
Raw data is large; store summaries (e.g., total sales by category).
Aggregates support efficient querying without full details.
