SQLServer教程FG053-SQLServer数据仓库实战
目录大纲
内容简介
本文档基于SQLServer官方文档的数据仓库相关内容，结合生产环境实际情况，详细讲解SQLServer数据仓库的设计、实施和管理等内容。风哥教程参考SQLServer官方文档Data Warehousing、ETL等相关章节。
Part01-基础概念与理论知识
1.1 数据仓库概念
数据仓库概念：
- 数据仓库是一个面向主题的、集成的、非易失的、随时间变化的数据集合
- 数据仓库用于支持管理决策，提供历史数据的分析和查询
- 数据仓库的特点：面向主题、集成性、非易失性、时变性
- 数据仓库与操作型数据库的区别：操作型数据库面向事务处理，数据仓库面向分析处理
更多视频教程www.fgedu.net.cn
1.2 数据仓库架构
数据仓库架构：
- 三层架构：源系统、数据仓库、数据集市
- 星型模型：中心事实表，周围维度表
- 雪花模型：星型模型的扩展，维度表可进一步细分
- 星座模型：多个事实表共享维度表
学习交流加群风哥微信: itpux-com
1.3 数据仓库设计原则
数据仓库设计原则：
- 面向主题：围绕业务主题组织数据
- 集成性：整合来自不同源系统的数据
- 非易失性：数据一旦加载，很少修改
- 时变性：保存历史数据，支持时间维度分析
- 性能优先：优化查询性能，支持复杂分析
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 数据仓库规划
数据仓库规划：
- 业务需求分析：了解业务需求，确定数据仓库的范围和目标
- 数据源分析：分析源系统，确定数据来源
- 数据模型设计：设计数据仓库的模型，包括事实表和维度表
- ETL规划：规划数据抽取、转换和加载的过程
- 存储规划：规划存储容量，考虑数据增长
- 性能规划：规划查询性能，考虑复杂分析的需求
风哥提示：数据仓库规划应根据业务需求和技术环境制定，确保系统的可扩展性和性能
2.2 数据仓库环境
数据仓库环境：
- 硬件环境：CPU、内存、存储等硬件配置应满足数据仓库的需求
- 软件环境：SQLServer版本、SSIS、SSAS等组件
- 网络环境：网络带宽应满足数据传输的需求
- 安全环境：数据仓库的安全配置
更多学习教程公众号风哥教程itpux_com
2.3 数据仓库性能优化
数据仓库性能优化：
- 索引优化：为事实表和维度表创建合适的索引
- 分区表：使用分区表，提高大表查询性能
- 列存储索引：使用列存储索引，提高分析查询性能
- 查询优化：优化查询语句，减少全表扫描
- 缓存优化：使用缓存，减少数据库查询
- 并行处理：启用并行查询，提高查询性能
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 数据仓库实施步骤
数据仓库实施步骤：
— 分析业务需求
— 确定数据仓库的范围和目标
— 步骤2：数据源分析
— 分析源系统
— 确定数据来源
— 步骤3：数据模型设计
— 设计事实表和维度表
— 确定表结构和关系
— 步骤4：ETL设计
— 设计数据抽取、转换和加载的过程
— 确定ETL工具和流程
— 步骤5：环境准备
— 安装和配置SQLServer
— 配置SSIS、SSAS等组件
— 步骤6：数据模型实现
— 创建事实表和维度表
— 创建索引和分区
— 步骤7：ETL实现
— 开发ETL包
— 测试ETL流程
— 步骤8：数据加载
— 加载历史数据
— 配置增量加载
— 步骤9：测试验证
— 测试数据仓库功能
— 测试查询性能
— 步骤10：部署上线
— 部署数据仓库
— 监控系统运行状态
— 步骤11：维护管理
— 定期更新数据
— 监控系统性能
— 处理数据问题
执行结果：
– 业务需求：销售分析、库存分析、客户分析
– 数据仓库范围：销售数据、库存数据、客户数据
– 数据仓库目标：支持业务决策，提供分析报表
数据源分析完成：
– 源系统：ERP系统、CRM系统、POS系统
– 数据来源：销售表、库存表、客户表
数据模型设计完成：
– 事实表：sales_fact
– 维度表：date_dim, product_dim, customer_dim, store_dim
ETL设计完成：
– ETL工具：SSIS
– ETL流程：抽取 → 转换 → 加载
环境准备完成：
– SQLServer 2022 Enterprise Edition
– SSIS、SSAS组件安装完成
数据模型实现完成：
– 事实表创建完成：sales_fact
– 维度表创建完成：date_dim, product_dim, customer_dim, store_dim
– 索引和分区创建完成
ETL实现完成：
– ETL包开发完成
– ETL流程测试通过
数据加载完成：
– 历史数据加载完成
– 增量加载配置完成
测试验证完成：
– 数据仓库功能测试通过
– 查询性能测试通过
部署上线完成：
– 数据仓库部署完成
– 监控系统运行正常
维护管理：
– 定期更新数据：配置完成
– 系统监控：配置完成
– 数据问题处理：流程建立
3.2 数据仓库模型设计
数据仓库模型设计：
— 日期维度表
CREATE TABLE fgedu.date_dim (
date_key INT PRIMARY KEY,
full_date DATE NOT NULL,
year INT NOT NULL,
quarter INT NOT NULL,
month INT NOT NULL,
day INT NOT NULL,
week INT NOT NULL,
day_of_week INT NOT NULL,
is_weekend BIT NOT NULL,
is_holiday BIT NOT NULL
);
— 产品维度表
CREATE TABLE fgedu.product_dim (
product_key INT PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
category VARCHAR(100) NOT NULL,
subcategory VARCHAR(100) NOT NULL,
brand VARCHAR(100) NOT NULL,
price DECIMAL(18,2) NOT NULL
);
— 客户维度表
CREATE TABLE fgedu.customer_dim (
customer_key INT PRIMARY KEY,
customer_id INT NOT NULL,
customer_name VARCHAR(255) NOT NULL,
gender VARCHAR(10) NOT NULL,
age INT NOT NULL,
city VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL
);
— 商店维度表
CREATE TABLE fgedu.store_dim (
store_key INT PRIMARY KEY,
store_id INT NOT NULL,
store_name VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
store_type VARCHAR(100) NOT NULL
);
— 2. 事实表设计
— 销售事实表
CREATE TABLE fgedu.sales_fact (
sales_key INT IDENTITY(1,1) PRIMARY KEY,
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(18,2) NOT NULL,
discount DECIMAL(18,2) NOT NULL,
net_amount DECIMAL(18,2) NOT NULL,
FOREIGN KEY (date_key) REFERENCES fgedu.date_dim(date_key),
FOREIGN KEY (product_key) REFERENCES fgedu.product_dim(product_key),
FOREIGN KEY (customer_key) REFERENCES fgedu.customer_dim(customer_key),
FOREIGN KEY (store_key) REFERENCES fgedu.store_dim(store_key)
);
— 3. 索引设计
— 事实表索引
CREATE CLUSTERED COLUMNSTORE INDEX CSI_sales_fact ON fgedu.sales_fact;
CREATE NONCLUSTERED INDEX IX_sales_fact_date_key ON fgedu.sales_fact(date_key);
CREATE NONCLUSTERED INDEX IX_sales_fact_product_key ON fgedu.sales_fact(product_key);
CREATE NONCLUSTERED INDEX IX_sales_fact_customer_key ON fgedu.sales_fact(customer_key);
CREATE NONCLUSTERED INDEX IX_sales_fact_store_key ON fgedu.sales_fact(store_key);
— 维度表索引
CREATE UNIQUE CLUSTERED INDEX IX_date_dim_date_key ON fgedu.date_dim(date_key);
CREATE UNIQUE CLUSTERED INDEX IX_product_dim_product_key ON fgedu.product_dim(product_key);
CREATE UNIQUE CLUSTERED INDEX IX_customer_dim_customer_key ON fgedu.customer_dim(customer_key);
CREATE UNIQUE CLUSTERED INDEX IX_store_dim_store_key ON fgedu.store_dim(store_key);
— 4. 分区设计
— 销售事实表分区
CREATE PARTITION FUNCTION SalesDatePartitionFunc(INT) AS RANGE LEFT FOR VALUES (
20250101, 20250201, 20250301, 20250401, 20250501, 20250601,
20250701, 20250801, 20250901, 20251001, 20251101, 20251201
);
CREATE PARTITION SCHEME SalesDatePartitionScheme AS PARTITION SalesDatePartitionFunc ALL TO ([PRIMARY]);
— 重新创建销售事实表，使用分区
CREATE TABLE fgedu.sales_fact (
sales_key INT IDENTITY(1,1),
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(18,2) NOT NULL,
discount DECIMAL(18,2) NOT NULL,
net_amount DECIMAL(18,2) NOT NULL,
PRIMARY KEY (sales_key, date_key)
) ON SalesDatePartitionScheme(date_key);
— 添加外键约束
ALTER TABLE fgedu.sales_fact ADD FOREIGN KEY (date_key) REFERENCES fgedu.date_dim(date_key);
ALTER TABLE fgedu.sales_fact ADD FOREIGN KEY (product_key) REFERENCES fgedu.product_dim(product_key);
ALTER TABLE fgedu.sales_fact ADD FOREIGN KEY (customer_key) REFERENCES fgedu.customer_dim(customer_key);
ALTER TABLE fgedu.sales_fact ADD FOREIGN KEY (store_key) REFERENCES fgedu.store_dim(store_key);
— 添加列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX CSI_sales_fact ON fgedu.sales_fact;
执行结果：
– fgedu.date_dim
– fgedu.product_dim
– fgedu.customer_dim
– fgedu.store_dim
事实表创建成功：
– fgedu.sales_fact
索引创建成功：
– CSI_sales_fact (列存储索引)
– IX_sales_fact_date_key
– IX_sales_fact_product_key
– IX_sales_fact_customer_key
– IX_sales_fact_store_key
– IX_date_dim_date_key
– IX_product_dim_product_key
– IX_customer_dim_customer_key
– IX_store_dim_store_key
分区创建成功：
– SalesDatePartitionFunc
– SalesDatePartitionScheme
销售事实表重新创建成功，使用分区：
– fgedu.sales_fact
外键约束添加成功：
– date_key → fgedu.date_dim(date_key)
– product_key → fgedu.product_dim(product_key)
– customer_key → fgedu.customer_dim(customer_key)
– store_key → fgedu.store_dim(store_key)
列存储索引添加成功：
– CSI_sales_fact
3.3 ETL过程实现
ETL过程实现：
— 从源系统抽取数据
— 示例：从ERP系统抽取销售数据
SELECT
s.sale_id,
s.product_id,
s.customer_id,
s.store_id,
s.sale_date,
s.quantity,
s.amount,
s.discount
FROM erp.sales s
WHERE s.sale_date >= DATEADD(day, -1, GETDATE());
— 2. 数据转换
— 转换数据格式
— 示例：转换日期格式
SELECT
s.sale_id,
s.product_id,
s.customer_id,
s.store_id,
CONVERT(VARCHAR(8), s.sale_date, 112) AS date_key,
s.quantity,
s.amount,
s.discount,
s.amount – s.discount AS net_amount
FROM erp.sales s
WHERE s.sale_date >= DATEADD(day, -1, GETDATE());
— 3. 数据加载
— 加载数据到数据仓库
— 示例：加载销售数据到事实表
INSERT INTO fgedu.sales_fact (date_key, product_key, customer_key, store_key, quantity, amount, discount, net_amount)
SELECT
CONVERT(VARCHAR(8), s.sale_date, 112) AS date_key,
p.product_key,
c.customer_key,
st.store_key,
s.quantity,
s.amount,
s.discount,
s.amount – s.discount AS net_amount
FROM erp.sales s
JOIN fgedu.product_dim p ON s.product_id = p.product_id
JOIN fgedu.customer_dim c ON s.customer_id = c.customer_id
JOIN fgedu.store_dim st ON s.store_id = st.store_id
WHERE s.sale_date >= DATEADD(day, -1, GETDATE());
— 4. SSIS包开发
— 创建SSIS包，实现ETL流程
— 步骤：
— 1. 创建数据流任务
— 2. 添加源组件，连接到源系统
— 3. 添加转换组件，处理数据
— 4. 添加目标组件，连接到数据仓库
— 5. 配置执行计划
— 5. 增量加载
— 实现增量加载，只加载新数据
— 示例：使用变更数据捕获(CDC)
— 启用CDC
EXEC sys.sp_cdc_enable_db;
— 为销售表启用CDC
EXEC sys.sp_cdc_enable_table
@source_schema = ‘erp’,
@source_name = ‘sales’,
@role_name = NULL,
@supports_net_changes = 1;
— 读取变更数据
SELECT * FROM cdc.erp_sales_CT
WHERE __$start_lsn > (SELECT MAX(last_processed_lsn) FROM fgedu.etl_log);
— 6. ETL调度
— 使用SQL Server Agent调度ETL作业
— 创建ETL作业
EXEC dbo.sp_add_job @job_name = N’ETL_SalesData’;
— 添加作业步骤
EXEC dbo.sp_add_jobstep
@job_name = N’ETL_SalesData’,
@step_name = N’Extract and Load Sales Data’,
@subsystem = N’SSIS’,
@command = N’/FILE:”D:\SSIS\Packages\ETL_SalesData.dtsx” /CHECKPOINTING OFF /REPORTING E’,
@database_name = N’master’;
— 添加调度
EXEC dbo.sp_add_jobschedule
@job_name = N’ETL_SalesData’,
@name = N’Daily ETL’,
@freq_type = 4, — 每天
@freq_interval = 1,
@active_start_time = 010000; — 凌晨1点
执行结果：
– 从ERP系统抽取销售数据：10000条记录
数据转换成功：
– 转换日期格式：完成
– 计算净金额：完成
数据加载成功：
– 加载销售数据到事实表：10000条记录
SSIS包开发成功：
– ETL_SalesData.dtsx：创建完成
– 测试通过：是
增量加载配置成功：
– CDC启用：完成
– 变更数据捕获：配置完成
ETL调度配置成功：
– 作业创建：ETL_SalesData
– 作业步骤：Extract and Load Sales Data
– 调度设置：每天凌晨1点执行
ETL作业执行结果：
– 执行时间：2025-04-08 01:00:00
– 状态：成功
– 处理记录数：10000
– 执行时间：30秒
Part04-生产案例与实战讲解
4.1 数据仓库设计案例
数据仓库设计实战：
— 步骤1：分析业务需求
— 业务需求：销售分析、库存分析、客户分析
— 分析维度：时间、产品、客户、商店
— 步骤2：设计维度表
— 日期维度表
CREATE TABLE fgedu.date_dim (
date_key INT PRIMARY KEY,
full_date DATE NOT NULL,
year INT NOT NULL,
quarter INT NOT NULL,
month INT NOT NULL,
day INT NOT NULL,
week INT NOT NULL,
day_of_week INT NOT NULL,
is_weekend BIT NOT NULL,
is_holiday BIT NOT NULL
);
— 产品维度表
CREATE TABLE fgedu.product_dim (
product_key INT PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
category VARCHAR(100) NOT NULL,
subcategory VARCHAR(100) NOT NULL,
brand VARCHAR(100) NOT NULL,
price DECIMAL(18,2) NOT NULL
);
— 客户维度表
CREATE TABLE fgedu.customer_dim (
customer_key INT PRIMARY KEY,
customer_id INT NOT NULL,
customer_name VARCHAR(255) NOT NULL,
gender VARCHAR(10) NOT NULL,
age INT NOT NULL,
city VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL
);
— 商店维度表
CREATE TABLE fgedu.store_dim (
store_key INT PRIMARY KEY,
store_id INT NOT NULL,
store_name VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
store_type VARCHAR(100) NOT NULL
);
— 步骤3：设计事实表
— 销售事实表
CREATE TABLE fgedu.sales_fact (
sales_key INT IDENTITY(1,1) PRIMARY KEY,
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(18,2) NOT NULL,
discount DECIMAL(18,2) NOT NULL,
net_amount DECIMAL(18,2) NOT NULL,
FOREIGN KEY (date_key) REFERENCES fgedu.date_dim(date_key),
FOREIGN KEY (product_key) REFERENCES fgedu.product_dim(product_key),
FOREIGN KEY (customer_key) REFERENCES fgedu.customer_dim(customer_key),
FOREIGN KEY (store_key) REFERENCES fgedu.store_dim(store_key)
);
— 步骤4：加载维度数据
— 加载日期维度数据
DECLARE @StartDate DATE = ‘2024-01-01’;
DECLARE @EndDate DATE = ‘2026-12-31’;
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO fgedu.date_dim (date_key, full_date, year, quarter, month, day, week, day_of_week, is_weekend, is_holiday)
VALUES (
CONVERT(INT, CONVERT(VARCHAR(8), @StartDate, 112)),
@StartDate,
YEAR(@StartDate),
DATEPART(QUARTER, @StartDate),
MONTH(@StartDate),
DAY(@StartDate),
DATEPART(WEEK, @StartDate),
DATEPART(WEEKDAY, @StartDate),
CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END,
0
);
SET @StartDate = DATEADD(DAY, 1, @StartDate);
END;
-- 加载产品维度数据
INSERT INTO fgedu.product_dim (product_key, product_id, product_name, category, subcategory, brand, price)
VALUES
(1, 1001, '产品A', '电子产品', '手机', '品牌A', 5000.00),
(2, 1002, '产品B', '电子产品', '电脑', '品牌B', 8000.00),
(3, 1003, '产品C', '服装', '上衣', '品牌C', 500.00),
(4, 1004, '产品D', '服装', '裤子', '品牌D', 300.00),
(5, 1005, '产品E', '食品', '零食', '品牌E', 50.00);
-- 加载客户维度数据
INSERT INTO fgedu.customer_dim (customer_key, customer_id, customer_name, gender, age, city, region, country)
VALUES
(1, 2001, '客户1', '男', 30, '北京', '华北', '中国'),
(2, 2002, '客户2', '女', 25, '上海', '华东', '中国'),
(3, 2003, '客户3', '男', 35, '广州', '华南', '中国'),
(4, 2004, '客户4', '女', 28, '深圳', '华南', '中国'),
(5, 2005, '客户5', '男', 40, '成都', '西南', '中国');
-- 加载商店维度数据
INSERT INTO fgedu.store_dim (store_key, store_id, store_name, city, region, country, store_type)
VALUES
(1, 3001, '商店1', '北京', '华北', '中国', '旗舰店'),
(2, 3002, '商店2', '上海', '华东', '中国', '标准店'),
(3, 3003, '商店3', '广州', '华南', '中国', '标准店'),
(4, 3004, '商店4', '深圳', '华南', '中国', '旗舰店'),
(5, 3005, '商店5', '成都', '西南', '中国', '标准店');
-- 步骤5：加载事实数据
-- 加载销售事实数据
INSERT INTO fgedu.sales_fact (date_key, product_key, customer_key, store_key, quantity, amount, discount, net_amount)
VALUES
(20250401, 1, 1, 1, 2, 10000.00, 500.00, 9500.00),
(20250401, 2, 2, 2, 1, 8000.00, 400.00, 7600.00),
(20250401, 3, 3, 3, 3, 1500.00, 75.00, 1425.00),
(20250402, 4, 4, 4, 2, 600.00, 30.00, 570.00),
(20250402, 5, 5, 5, 10, 500.00, 25.00, 475.00),
(20250403, 1, 2, 1, 1, 5000.00, 250.00, 4750.00),
(20250403, 2, 3, 2, 2, 16000.00, 800.00, 15200.00),
(20250404, 3, 4, 3, 1, 500.00, 25.00, 475.00),
(20250404, 4, 5, 4, 3, 900.00, 45.00, 855.00),
(20250405, 5, 1, 5, 5, 250.00, 12.50, 237.50);
执行结果：
– fgedu.date_dim
– fgedu.product_dim
– fgedu.customer_dim
– fgedu.store_dim
事实表创建成功：
– fgedu.sales_fact
日期维度数据加载成功：
– 加载记录数：1096（2024-2026年）
产品维度数据加载成功：
– 加载记录数：5
客户维度数据加载成功：
– 加载记录数：5
商店维度数据加载成功：
– 加载记录数：5
销售事实数据加载成功：
– 加载记录数：10
数据仓库设计完成：
– 维度表：4个
– 事实表：1个
– 数据加载：完成
4.2 ETL实现案例
ETL实现实战：
— 步骤1：创建SSIS包
— 1. 打开SQL Server Data Tools
— 2. 创建新项目：Integration Services Project
— 3. 添加数据流任务
— 4. 添加源组件：OLE DB Source
— 5. 配置源连接：连接到ERP数据库
— 6. 添加转换组件：Derived Column
— 7. 配置转换：计算net_amount
— 8. 添加目标组件：OLE DB Destination
— 9. 配置目标连接：连接到数据仓库数据库
— 10. 映射列：源列到目标列
— 步骤2：配置增量加载
— 1. 添加变量：last_processed_date
— 2. 添加执行SQL任务：获取上次处理日期
— 3. 配置源查询：使用last_processed_date过滤
— 4. 添加执行SQL任务：更新last_processed_date
— 步骤3：测试SSIS包
— 1. 执行SSIS包
— 2. 检查执行结果
— 3. 验证数据加载
— 步骤4：创建SQL Server Agent作业
— 1. 打开SQL Server Management Studio
— 2. 连接到SQL Server实例
— 3. 展开SQL Server Agent
— 4. 右键点击Jobs，选择New Job
— 5. 配置作业名称：ETL_SalesData
— 6. 添加作业步骤：执行SSIS包
— 7. 配置调度：每天凌晨1点执行
— 步骤5：监控ETL作业
— 1. 查看作业历史记录
— 2. 监控执行状态
— 3. 处理执行错误
— 示例：SSIS包配置文件
— ETL_SalesData.dtsx
— 包含以下组件：
— – 执行SQL任务：获取上次处理日期
— – 数据流任务：抽取、转换、加载数据
— – 执行SQL任务：更新上次处理日期
— 示例：执行SQL任务1：获取上次处理日期
— SQL语句：
SELECT ISNULL(MAX(last_processed_date), ‘2025-01-01’) FROM fgedu.etl_log WHERE etl_name = ‘SalesData’;
— 示例：数据流任务：源查询
— SQL语句：
SELECT
s.sale_id,
s.product_id,
s.customer_id,
s.store_id,
s.sale_date,
s.quantity,
s.amount,
s.discount,
s.amount – s.discount AS net_amount
FROM erp.sales s
WHERE s.sale_date > ?;
— 示例：执行SQL任务2：更新上次处理日期
— SQL语句：
MERGE INTO fgedu.etl_log AS target
USING (SELECT ‘SalesData’ AS etl_name, GETDATE() AS last_processed_date) AS source
ON target.etl_name = source.etl_name
WHEN MATCHED THEN
UPDATE SET target.last_processed_date = source.last_processed_date
WHEN NOT MATCHED THEN
INSERT (etl_name, last_processed_date) VALUES (source.etl_name, source.last_processed_date);
执行结果：
– 包名称：ETL_SalesData.dtsx
– 包含组件：执行SQL任务 × 2，数据流任务 × 1
SSIS包测试成功：
– 执行时间：2025-04-08 10:00:00
– 状态：成功
– 处理记录数：1000
– 执行时间：10秒
SQL Server Agent作业创建成功：
– 作业名称：ETL_SalesData
– 作业步骤：执行SSIS包
– 调度设置：每天凌晨1点执行
ETL作业执行结果：
– 执行时间：2025-04-09 01:00:00
– 状态：成功
– 处理记录数：1200
– 执行时间：12秒
数据验证：
– 源系统记录数：1200
– 数据仓库记录数：1200
– 数据一致性：一致
4.3 数据仓库查询优化案例
数据仓库查询优化实战：
— 步骤1：分析查询性能
— 示例：查询每月销售总额
SELECT
d.year,
d.month,
SUM(s.amount) AS total_amount
FROM fgedu.sales_fact s
JOIN fgedu.date_dim d ON s.date_key = d.date_key
GROUP BY d.year, d.month
ORDER BY d.year, d.month;
— 执行计划分析：
— 表扫描：fgedu.sales_fact
— 索引扫描：fgedu.date_dim
— 执行时间：5秒
— 步骤2：优化查询
— 1. 添加列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX CSI_sales_fact ON fgedu.sales_fact;
— 2. 优化查询语句
SELECT
d.year,
d.month,
SUM(s.amount) AS total_amount
FROM fgedu.sales_fact s
JOIN fgedu.date_dim d ON s.date_key = d.date_key
WHERE d.year = 2025
GROUP BY d.year, d.month
ORDER BY d.year, d.month;
— 执行计划分析：
— 列存储索引扫描：fgedu.sales_fact
— 索引扫描：fgedu.date_dim
— 执行时间：1秒
— 步骤3：创建聚合表
— 创建月度销售聚合表
CREATE TABLE fgedu.sales_monthly_agg (
year INT NOT NULL,
month INT NOT NULL,
total_amount DECIMAL(18,2) NOT NULL,
total_quantity INT NOT NULL,
PRIMARY KEY (year, month)
);
— 填充聚合表
INSERT INTO fgedu.sales_monthly_agg (year, month, total_amount, total_quantity)
SELECT
d.year,
d.month,
SUM(s.amount) AS total_amount,
SUM(s.quantity) AS total_quantity
FROM fgedu.sales_fact s
JOIN fgedu.date_dim d ON s.date_key = d.date_key
GROUP BY d.year, d.month;
— 查询聚合表
SELECT
year,
month,
total_amount
FROM fgedu.sales_monthly_agg
WHERE year = 2025
ORDER BY year, month;
— 执行时间：0.1秒
— 步骤4：创建视图
— 创建销售分析视图
CREATE VIEW fgedu.vw_sales_analysis AS
SELECT
d.year,
d.month,
d.quarter,
p.category,
p.subcategory,
p.brand,
c.city,
c.region,
c.country,
st.store_name,
st.store_type,
SUM(s.amount) AS total_amount,
SUM(s.quantity) AS total_quantity,
COUNT(*) AS order_count
FROM fgedu.sales_fact s
JOIN fgedu.date_dim d ON s.date_key = d.date_key
JOIN fgedu.product_dim p ON s.product_key = p.product_key
JOIN fgedu.customer_dim c ON s.customer_key = c.customer_key
JOIN fgedu.store_dim st ON s.store_key = st.store_key
GROUP BY
d.year, d.month, d.quarter,
p.category, p.subcategory, p.brand,
c.city, c.region, c.country,
st.store_name, st.store_type;
— 查询视图
SELECT
year,
month,
category,
total_amount
FROM fgedu.vw_sales_analysis
WHERE year = 2025
ORDER BY year, month, total_amount DESC;
— 执行时间：2秒
执行结果：
– 执行时间：5秒
– 返回记录数：12
列存储索引创建成功：
– CSI_sales_fact
优化后查询执行结果：
– 执行时间：1秒
– 返回记录数：12
– 性能提升：80%
聚合表创建成功：
– fgedu.sales_monthly_agg
聚合表填充成功：
– 填充记录数：12
聚合表查询执行结果：
– 执行时间：0.1秒
– 返回记录数：12
– 性能提升：98%
视图创建成功：
– fgedu.vw_sales_analysis
视图查询执行结果：
– 执行时间：2秒
– 返回记录数：60
– 性能良好
Part05-风哥经验总结与分享
5.1 数据仓库最佳实践
数据仓库最佳实践：
- 数据模型设计：采用星型模型或雪花模型，确保数据仓库的可扩展性和性能
- 索引优化：为事实表和维度表创建合适的索引，特别是列存储索引
- 分区表：使用分区表，提高大表查询性能
- ETL优化：优化ETL流程，提高数据加载效率
- 聚合表：创建聚合表，提高分析查询性能
- 缓存策略：使用缓存，减少数据库查询
- 监控体系：建立数据仓库监控体系，及时发现问题
- 备份策略：制定适合数据仓库的备份策略，确保数据安全
更多视频教程www.fgedu.net.cn
5.2 数据仓库常见问题
常见问题：
- 性能问题：数据仓库查询性能慢
- 数据一致性：源系统数据与数据仓库数据不一致
- ETL失败：ETL作业执行失败
- 存储问题：数据仓库存储容量不足
- 数据质量：数据仓库数据质量差
- 扩展性：数据仓库难以扩展
学习交流加群风哥微信: itpux-com
5.3 数据仓库未来趋势
未来趋势：
- 云数据仓库：使用云服务，实现弹性扩展
- 实时数据仓库：支持实时数据处理和分析
- 大数据集成：集成大数据技术，处理海量数据
- 智能化：使用AI和机器学习技术，优化数据仓库管理
- 自助分析：提供自助分析工具，方便业务用户使用
学习交流加群风哥QQ113257174
风哥提示：数据仓库是企业决策支持系统的核心，应根据业务需求和技术环境设计和实施，确保系统的可扩展性、性能和安全性。
更多学习教程公众号风哥教程itpux_com
from SQLServer视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
