SQL Server 数据仓库分区、索引与数据提取

1. 分区表创建与维护

在 SQL Server 中进行表分区时，对于 2008 年的记录，其日期键范围是 10365 到 10730。在分区函数中，需指定边界值，例如 2008 年 1 月 1 日（日期键 10365）、2008 年 2 月 1 日（日期键 10396）等。之后，通过将分区分配给文件组来创建分区方案。

创建表时，在数据定义语言（DDL）末尾的括号中指定分区方案和分区列，以此告知 SQL Server 使用该分区方案对表进行分区。常见的错误是为聚集主键指定文件组，例如 constraint pk_tablename primary key clustered(PK columns) on dds_fg4 ，这会导致 SQL Server 将表和聚集索引放在 dds_fg4 上，而不是分区方案中指定的 dds_fg2 、 dds_fg3 和 dds_fg5 。因为聚集索引的叶节点就是物理表页，当创建分区表时，如果约束文件组与表文件组不同，SQL Server 会按约束文件组的指定操作，忽略表文件组。

分区索引最好与分区表放在同一文件组，这样能使 SQL Server 高效地切换分区，即索引与表对齐。在实现滑动窗口进行分区存档以及向表中添加新分区时，索引与表对齐很有帮助。移动或转移分区时，实际只是更改数据所在位置的元数据，而非移动数据。

分区维护是管理分区表，使其准备好进行数据加载和查询。若使用每月滑动窗口进行分区，可每月或每年进行分区维护。每月可删除最旧的分区（并存档），添加本月的分区；也可每年 12 月删除最旧年份的 12 个分区，添加新一年的 12 个分区。

以下是分区表创建与维护的流程：

graph TD;
    A[确定分区边界值] --> B[创建分区方案];
    B --> C[创建表并指定分区方案];
    C --> D[避免错误指定文件组];
    D --> E[确保索引与表对齐];
    E --> F[进行分区维护];

2. 索引的选择与创建

索引能显著提高数据仓库的查询和加载性能。在数据仓库中，维度表和事实表需要不同的索引和主键。

2.1 维度表

每个维度表都有一个代理键列，它是自增列（identity (1,1)），值唯一。将此代理键列设为维度表的主键和聚集索引，因为在数据仓库中，维度表通过代理键列与事实表连接。在 SQL Server 中，聚集索引决定了行在磁盘上的物理存储顺序，且每张表只能有一个聚集索引。

维度表中的属性列（通常为字符数据类型），若在查询的 where 子句中经常使用且选择性高，可设为非聚集索引；若属性列有很多重复值，则可能不值得索引。

2.2 事实表

在 SQL Server 数据仓库中，确定事实表的主键和聚集索引有两种方法：
- 第一种方法 ：创建事实表代理键列，它是自增列（identity (1,1)），作为事实表行的单列唯一标识符。将该列设为事实表的主键和聚集索引。此方法加载速度快两倍，非聚集索引比第二种方法小 4 - 5 倍。
- 第二种方法 ：不创建事实表代理键列，选择使行唯一的最小列组合作为主键。例如，订阅销售事实表中，每行代表每个客户每天的订阅， date_key 和 customer_key 必须是主键的一部分，若客户订阅两个套餐，还需加入 subscription_id 使主键唯一。将表按这些主键列聚集，可使查询在 where 子句包含日期和客户时更快，但加载速度可能是第一种方法的一半，不过需要维护的索引更少。

以下是不同事实表的主键示例：
| 事实表名称 | 粒度 | 唯一列组合（主键） |
| — | — | — |
| 通信订阅事实表 | 每个订阅一行 | customer_key, communication_key, start_date_key |
| 供应商绩效事实表 | 每个产品每周一行 | week_key, supplier_key, product_key |
| 营销活动结果事实表 | 每个预期收件人一行 | campaign_key, customer_key, send_date_key |
| 产品销售事实表 | 每个销售项目一行 | order_id, line_number |

2.3 小表索引

对于小表（如 NDS 上的一些维度表和属性表），索引维护开销可能超过查询性能提升的好处。一般行数少于 1000 时不索引，行数在 1000 - 10000 之间时，需谨慎考虑，可先查看执行计划。例如，Amadeus 娱乐商店表中，查询 where city = value1 有无非聚集索引耗时均为 0 毫秒，但插入数据时，无索引耗时 0 毫秒，仅对 city 列索引耗时 93 毫秒，对 17 列索引耗时 2560 毫秒。

2.4 索引创建步骤

索引创建分两步：
1. 第一步 ：根据应用和业务知识确定要索引的列。在数据建模和物理数据库设计时进行，例如在客户维度表中，因业务用户需按收入分析产品和订阅销售，可在 occupation 列创建非聚集索引；因 CRM 细分流程需获取已授权客户列表，需对 permission 列索引。
2. 第二步 ：在将数据加载到 NDS 和 DDS 后，在开发环境中，使用查询执行计划和数据库引擎调优顾问根据查询工作负载微调索引。通过 SQL Server Profiler 捕获 SQL 语句执行时长到表中分析，找出慢查询，查看其执行计划，若发现查询的 where 子句中使用了未索引的列，可创建非聚集索引改进。

以下是在维度表和事实表创建索引的示例代码：

-- 维度表索引示例
if exists 
(select * from sys.indexes 
where name = 'dim_date_day_of_the_week'
and object_id = object_id('dim_date'))
drop index dim_date.dim_date_day_of_the_week
go
create index dim_date_day_of_the_week
on dim_date(day_of_the_week)
on dds_fg6
go

-- 事实表索引交集示例
create index fact_product_sales_sales_date_key
on fact_product_sales(sales_date_key)
on dds_fg4
go
create index fact_product_sales_customer_key
on fact_product_sales(customer_key)
on dds_fg4
go
create index fact_product_sales_product_key
on fact_product_sales(product_key)
on dds_fg4
go
create index fact_product_sales_store_key
on fact_product_sales(store_key)
on dds_fg4
go

为帮助索引交集和加速某些查询，可创建覆盖索引。覆盖索引包含查询中引用的所有列，SQL Server 查询引擎无需访问表本身即可获取所需数据。但要注意覆盖索引中列的顺序，第一列应选择最常用的维度键，第二列选择次常用的维度键。例如，产品销售事实表可设置以下三个覆盖索引：
- Date, customer, store, product
- Date, store, product, customer
- Date, product, store, customer

在分区表中创建索引，需提及分区方案而非普通文件组，示例如下：

create index date_key
on fact_subscription_sales(date_key)
on ps_subscription_sales(date_key)
go
create index package_key
on fact_subscription_sales(package_key)
on ps_subscription_sales(package_key)
go
create index customer_key
on fact_subscription_sales(customer_key)
on ps_subscription_sales(customer_key)
go

索引列的数据类型必须与分区函数的数据类型相同，这也是所有维度键列数据类型设为 int 的原因之一。

3. 数据提取与 ETL 基础

在完成 NDS 和 DDS 中的表创建后，接下来要从源系统中提取数据并填充这些表，这就涉及到数据提取和 ETL（Extract, Transform, and Load）过程。

3.1 ETL 概述

ETL 即提取、转换和加载，是从源系统检索和转换数据并将其放入数据仓库的过程。它已经发展了数十年，自诞生以来有了很大的改进。

3.2 数据提取原则

从源系统提取数据以填充数据仓库时，有几个基本原则需要理解：
- 数据量 ：提取的数据量通常很大，可能达到数百兆字节甚至数十吉字节。而 OLTP 系统设计为少量数据检索，因此要注意避免过度减慢源系统。
- 速度 ：希望提取尽可能快，例如能在五分钟内完成，而不是三小时。
- 数据大小 ：希望提取的数据量尽可能小，例如每天 10MB，而不是 1GB。
- 频率 ：希望提取频率尽可能低，例如每天一次，而不是每五分钟一次。
- 对源系统的影响 ：希望对源系统的更改尽可能小，最好不做任何更改，而不是在每个表中创建触发器来捕获数据更改。

简单来说，从源系统提取数据时，要尽量减少对源系统的干扰。

提取数据后，应尽快将其放入数据仓库，理想情况下直接进行，不经过磁盘存储（即不临时存储在数据库或文件中）。还需要对源系统的数据进行一些转换，使其符合 NDS 和 DDS 中数据的格式和结构。转换可能包括格式化和标准化（如转换为特定的数字或日期格式、去除尾随空格或前导零）、查找（如将客户状态 2 转换为“Active”、将产品类别“Pop music”转换为 54）等。

以下是数据提取的关键原则表格：
| 原则 | 要求 |
| — | — |
| 数据量 | 大但避免影响源系统 |
| 速度 | 尽量快，如五分钟内 |
| 数据大小 | 尽量小，如每天 10MB |
| 频率 | 尽量低，如每天一次 |
| 对源系统影响 | 尽量小，最好无更改 |

数据提取和处理的流程如下：

graph TD;
    A[从源系统提取数据] --> B[进行数据转换];
    B --> C[加载到数据仓库];

3.3 SQL Server Integration Services

为了完成数据提取任务，可以使用 SQL Server Integration Services 工具。后续将展示如何使用该工具从案例研究中的源系统（Jade、WebTower 和 Jupiter）提取数据。虽然这里没有详细的操作步骤，但一般使用该工具进行数据提取的大致步骤如下：
1. 打开 SQL Server Integration Services 项目。
2. 配置源连接，连接到源系统（如 Jade、WebTower、Jupiter）。
3. 配置目标连接，连接到 NDS 或 DDS 数据库。
4. 设计数据转换流程，包括上述提到的格式化、查找等操作。
5. 运行数据提取和加载任务。

总结

数据库设计是数据仓库的基石，我们在其上构建 ETL 和应用程序，因此必须确保设计正确。本文讨论了硬件平台、系统架构、磁盘空间计算、数据库创建、表和视图创建等细节，还涵盖了提高数据仓库性能的三个关键因素：汇总表、分区和索引。这些设置应从创建数据库时就正确完成，而不是在出现性能问题后再处理。

完成数据库构建后，接下来就是从源系统提取数据并填充 NDS 和 DDS 数据库的 ETL 过程。通过遵循数据提取原则和使用合适的工具（如 SQL Server Integration Services），可以高效地完成数据提取和加载任务，为数据仓库的后续使用奠定基础。