Understanding Data Warehousing

Introduction

Data warehouse works like a relational database designed for data analytical needs. It functions on the basis of OLAP (Online Analytical Processing). It is a central location where consolidated data from multiple locations (databases) are stored.

Prerequisites

In this tutorial, we are going to learn the following concepts in Data Warehousing.

• What is Data Warehousing.
• Data Warehouse Process.
• Data Warehousing Architecture.
• Data Warehouse Characteristics.
• Modern Data Warehousing Examples.

1. What is Data Warehousing

Data warehousing is the act of organizing & storing data in a way so as to make its retrieval efficient and insightful. It is also called as the process of transforming data into information for future business intelligence.

2. Data Warehouse Process.

The data warehousing process refers to the sequence of steps involved in collecting, preparing, storing, and delivering data for analytics and business intelligence.

The process of data warehousing can involve the following tools:
i) Airbyte
ii) Fivetran
iii) Talend
iv) Informatica
v) custom ETL scripts (Python/Spark)

3. Architecture

Data Sources

In data warehousing architecture, data sources are key to the preparation of data warehouse. The goal of data extraction is to collect data from multiple sources like:

i) Transactional databases (e.g., MySQL, PostgreSQL)
ii) ERP/CRM systems (e.g., SAP, Salesforce)
iii) Flat files (CSV, Excel)
iv) APIs or web services
v) Logs and IoT devices

ETL / ELT Process

ETL = Extract → Transform → Load.

• Extract data from sources
• Transform (clean, merge, format, validate)
• Load into the warehouse.

Storage Layers

• Staging area: temporary holding zone before processing
• Data warehouse: structured, cleaned, integrated data
• Data marts: subsets of warehouse data for specific teams (e.g. sales, finance)

BI Tools and Analytics

• Power BI
• Tableau
• Looker
• Superset
• Grafana
• Metabase

4. Data Warehouse Characteristics.

For decision making process to happen, data warehouse must be a subject-oriented, integrated, time variant and non-volatile collection of data in support of management’s goals and business improvement.

Subject - Oriented.

A Data warehouse can be used to analyze a particular subject area target oriented business analysis for example: “Sales” can be particular subject-oriented type of analysis.