Week 6: Data Warehousing

Learning Objectives 学习目标

Define Data Warehousing concepts.
Explain the ETL (Extract, Transform, Load) process.
Demonstrate multidimensional OLAP cube operations.

Step-by-Step Guide 分步指南

4.1 Definition and Concepts of Data Warehousing

Data Warehouse (DW) Definition: A subject-oriented, integrated, time-variant, and non-volatile collection of data in support of management's decision-making process.
Key Characteristics:
- Subject-Oriented: Organized around key business subjects (e.g., sales, customers) rather than operational transactions.
- Integrated: Consolidates data from multiple disparate sources, enforcing consistent codes and units.
- Time-Variant: Stores historical data over long periods (5-10 years) for trend analysis.
- Non-Volatile: Read-only after loading; data is not updated or deleted in place.
OLTP vs. OLAP: OLTP (Online Transaction Processing) is optimized for daily read/write operations (e.g., banking ATM transactions), while OLAP (Online Analytical Processing) is optimized for complex queries and decision analysis (e.g., annual sales trends).

Real-world Case: Walmart

Walmart's DW integrates supplier databases, point-of-sale logs, and regional weather feeds.
Analysts query this unified DW to correlate storm warnings with sales surges of specific items (like pop-tarts and flashlights).

Reference: What Walmart Knows About Customer Habits

4.2 Data Warehousing Process

ETL Process:
- Extract: Reading raw data from multiple operational source databases.
- Transform: Cleaning data, removing duplicates, and transforming schemas to match the target.
- Load: Writing the cleaned data into the target Data Warehouse.
OLAP Cube Operations:
- Roll-up: Summarizing data by moving up a concept hierarchy (e.g., from city to country).
- Drill-down: Revealing detailed data by moving down a hierarchy (e.g., from year to month).
- Slice: Selecting one dimension level to create a 2D view (e.g., sales for Year 2026).
- Dice: Selecting a sub-cube across multiple dimensions (e.g., sales of Shirts in USA for Q1).
- Pivot: Rotating the axes of the data view to see new relationships.

Real-world Case: Uber

Uber's ETL pipelines ingest trip records and GPS coordinates continuously.
Analysts perform a Drill-down from global daily trip volume down to specific urban zones per hour to optimize driver incentives.

Reference: Uber Real-Time Data Processing Pipelines

4.3 Data Warehousing Development

Bill Inmon Approach (Top-down): Focuses on building a centralized enterprise-wide data warehouse (EDW) first, then creating departmental data marts from it. Enforces complete integration but is highly expensive.
Ralph Kimball Approach (Bottom-up): Focuses on building subject-specific data marts first, then integrating them using conformed dimensions to create an enterprise view. Offers faster ROI but can lead to integration gaps.

Real-world Case: Capital One

Capital One combines these approaches by organizing financial customer profiles into data marts (Kimball style) for fast marketing analysis, while feeding global risk auditing systems from an integrated central warehouse (Inmon style).

Reference: Capital One Data Systems Case

OLAP Cube Operations

Operation	Action Description	Impact on Detail Level	Business Question Example
Roll-Up	Aggregates data by climbing up a hierarchy	Decreases detail (more summary)	What are the total sales by country instead of cities?
Drill-Down	Navigates from summary to detailed data	Increases detail (more specific)	What are the sales figures for each shop location in Paris?
Slice	Selects a single dimension's coordinate	Filters data to 2D slice	Show me sales for ONLY the month of January 2026.
Dice	Filters across multiple dimensions	Filters data to sub-cube	Show me sales of laptops in the North region for Q2.

The ETL Pipeline

Transactional records are: Extracted from multiple cash registers, Transformed to clean up inconsistencies (formatting currency, resolving missing fields), and Loaded into the central Data Warehouse.

4.1 数据仓库的定义与概念

数据仓库 (DW) 定义： 面向主题的、集成的、时变的、非易失的数据集合，用于支持管理者的决策制定过程。
四大核心特征：
- 面向主题： 围绕核心业务主题（如销售、客户）组织，而非日常业务交易。
- 集成： 合并来自多个不同源头的数据，强制统一编码与单位。
- 时变性： 存储长周期（5-10年）的历史数据以进行趋势分析。
- 非易失性： 加载后为只读状态；数据不会就地进行修改或删除。
OLTP 与 OLAP 对比： OLTP（联机事务处理）优化了每日读写操作（如银行 ATM 交易）；OLAP（联机分析处理）优化了复杂的数据查询与决策分析（如年度销售趋势分析）。

真实企业案例：沃尔玛 (Walmart)

沃尔玛的数据仓库整合了供应商数据库、销售点日志和区域天气预报。
分析师查询此统一的 DW，将风暴预警与特定商品（如泡芙饼干和手电筒）的销售额激增联系起来。

参考链接： 沃尔玛对客户习惯的了解

4.2 数据仓库流程与 OLAP 操作

ETL 过程：
- 提取 (Extract)： 从多个业务源数据库读取原始数据。
- 转换 (Transform)： 清洗数据、去除重复值，并转换架构以匹配目标格式。
- 加载 (Load)： 将清洗后的数据写入目标数据仓库。
OLAP 多维数据集操作：
- 上卷 (Roll-up)： 通过向上移动概念层级来汇总数据（例如，从城市到国家）。
- 下钻 (Drill-down)： 通过向下移动层级来展示详细数据（例如，从年份到月份）。
- 切片 (Slice)： 选择一个维度级别来创建二维视图（例如，2026 年的销售额）。
- 切块 (Dice)： 跨多个维度选择一个子数据集（例如，第一季度美国衬衫的销售额）。
- 旋转 (Pivot)： 旋转数据视图的轴以观察新的关联。

真实企业案例：优步 (Uber)

优步的 ETL 管道持续摄取行程记录和 GPS 坐标。
分析师进行下钻操作，从全球每日总单量下钻到每小时特定城市区域，以优化司机奖励。

参考链接： Uber实时数据处理管道

4.3 数据仓库开发方法

Bill Inmon 方法（自顶向下）： 侧重于首先构建一个集中的企业级数据仓库 (EDW)，然后从中创建部门级的数据集市。强制要求完全集成，但成本极高。
Ralph Kimball 方法（自底向上）： 侧重于首先构建面向特定主题的数据集市，然后使用一致维度将它们集成以创建企业视图。投资回报率 (ROI) 较快，但可能会导致集成缺口。

真实企业案例：第一资本 (Capital One)

Capital One结合了这两种方法：将客户财务画像整理成数据集市（Kimball风格）以便进行快速营销分析，同时从集成的中央仓库（Inmon风格）向全球风险审计系统提供数据。

参考链接： Capital One 数据系统分析

OLAP 立方体多维操作

操作类型	操作具体描述	对细节级别的影响	业务问题示例
上卷 (Roll-Up)	通过在维度层级上升来聚合数据	细节减少（汇总度变高）	全国的总销售额是多少（而不是看各个城市）？
下钻 (Drill-Down)	从汇总数据下探到更详细的数据	细节增加（针对性变强）	巴黎市每个具体零售店门店的销售额是多少？
切片 (Slice)	选择单个维度的一个特定取值过滤	过滤数据为二维切片	仅展示 2026 年 1 月的销售额。
切块 (Dice)	跨两个或多个维度过滤数据	过滤数据为较小的子立方体	展示第二季度华北地区笔记本电脑的销售额。

ETL 流水线流程

交易记录是：从多个独立收银台系统中 提取 (Extract)，然后进行 转换 (Transform) 以消除格式不一致、处理缺失值，最后 加载 (Load) 到中央数据仓库中。

Concept Visualization 概念可视化

Educational Infographic 教学信息图

Visual concept map explaining the key module topic: 解释该模块核心主题的视觉概念图：

📊 Practical Decision Support Suite (Week 6) 📊 实用决策支持套件（第 6 周）

📗 Excel PivotTable OLAP Cube Guide (Roll-up, Drill-down, Slice, Dice)

Perform multi-dimensional data warehouse operations in Excel:

Roll-Up: Group Date by Year/Quarter (Higher level aggregation).
Drill-Down: Double-click cell to expand into individual transactions.
Slice: Insert Slicer for Region = "Kuala Lumpur".
Dice: Insert Slicers for Region = "Kuala Lumpur" AND Product = "Cloud BI".

📊 Power BI Data Warehouse ETL DAX Measure

ETL Error Rate % = DIVIDE(SUM(ETL_Logs[Error_Count]), SUM(ETL_Logs[Records_Extracted]), 0)

Tips 提示

Compare OLTP vs OLAP carefully. In exams, you are often asked to categorize daily operations as OLTP and analytical queries as OLAP.

仔细对比 OLTP 与 OLAP。在考试中，你经常被要求将日常运行归类为 OLTP，将分析查询归类为 OLAP。

Knowledge Check 知识自测

Practical Exercise 实践练习

Task 1: SQL statement to inner join 'Sales' and 'Products' tables on ProductID. 任务 1: SQL 语句：在 ProductID 上内连接 'Sales' and 'Products' 表。

Task 2: Excel formula to count cells in A1:A10 that are greater than 100. 任务 2: Excel 公式：计算 A1:A10 区域中大于 100 的单元格数。

Task 3: DAX query to count total transaction records in 'Transactions' table. 任务 3: DAX 查询：计算 'Transactions' 表中事务记录的总数。

Revision Guide 复习指南

Revision Checklist:

I can define subject-oriented, integrated, time-variant, and non-volatile properties of a DW.
I understand the difference between OLTP (transactional) and OLAP (analytical) databases.
I can perform Roll-Up, Drill-Down, Slice, and Dice operations.

Key Concepts to Remember:

Definition and Concepts of Data Warehousing:
- Data Warehouse (DW) Definition: A subject-oriented, integrated, t...
- Data Warehousing Process:
  - ETL Process:
    - Extract: Reading raw data from m...
    - Data Warehousing Development:
      - Bill Inmon Approach (Top-down): Focuses on building a centralized...
      - 数据仓库的定义与概念:
        数据仓库 (DW) 定义： 面向主题的、集成的、时变的、非易失的数据集合，用于支持管理者的决策制定过程。
        四...
        数据仓库流程与 OLAP 操作:
        ETL 过程：
        
        提取 (Extract)： 从多个业务源数据库读取原始数据。
        ...
        数据仓库开发方法:
        Bill Inmon 方法（自顶向下）： 侧重于首先构建一个集中的企业级数据仓库 (EDW)，然后从中创建部门级的数据集市。强制要...
        
        Study Tips
        
        学习建议
        
        Written Test coverage ends this week. Review OLAP Operations and ETL thoroughly.
        
        书面测试考核范围本周截止。彻底复习 OLAP 操作和 ETL 流程。

Week 6: Data Warehousing 第 6 周：数据仓库