数据仓库设计与物理实现全解析

1. 数据仓库表映射与转换

在数据仓库设计中，首先要对各维度表进行源系统映射和转换操作。以部分表为例，以下是相关信息：
| 列名 | 描述 | 来源 | 转换 |
| — | — | — | — |
| title | 歌曲/电影/书籍标题 | jim.title | 无 |
| artist | 歌手、明星或作者 | jim.artist | 无 |
| product_type | 产品层级的一级分类，如音乐、电影或书籍 | jpt.description | 无 |
| product_category | 产品层级的二级分类，如电影的惊悚、西部、喜剧等类型 | jc.name | 无 |
| media | 媒体格式，如 MP3、MPG、CD 或 DVD | jm.name | 无 |
| unit_price | 单件商品价格 | jim.unit_price | 除以当日 jcr.rate |
| unit_cost | 分配的直接和间接成本 | jim.unit_cost | 除以当日 jcr.rate |
| status | 根据与供应商的合同状态，分为即将推出、活跃、过期 | jps.description | 无 |

完成这些映射和转换后，要对客户和商店维度进行类似操作，即记录源系统表及其缩写，写出源系统表之间的连接条件，将维度列映射到源系统表和列，并确定转换方式。接着对其他事实和维度表重复此过程，最终使设计的维度数据存储（DDS）完全映射到源系统，明确每个列的来源和转换方式。

2. 规范化数据存储（NDS）设计

规范化数据存储（NDS）是位于阶段层和 DDS 之间的规范化数据库，包含主表和事务表。主表存储业务事件涉及的人员或对象，事务表存储业务交易或事件。设计 NDS 的数据模型可按以下步骤进行：
1. 列出实体 ：根据源表以及 DDS 中的事实和维度属性列出所有实体。将源系统映射过程中确定的源系统表和 DDS 事实、维度表规范化后的独立表合并，去除重复表并按不同主题领域分类。
2. 确定实体关系 ：根据实体间的关系进行排列，通过连接父表和子表建立参照完整性。在 NDS 中，DDS 事实表成为子（事务）表，DDS 维度表成为父（主）表。例如，订阅销售事实表在 DDS 中有六个维度（潜在客户、套餐、格式、商店、客户和日期），在 NDS 中订阅销售事务表与潜在客户、套餐、格式、商店和客户表相连，日期直接放在事务表中。同时，将套餐表中的套餐类型列和格式表中的媒体列分别归一化为单独的表。
3. 使用连接表处理多对多关系 ：为实现多对多关系，引入连接表（J 表），也称为桥接表。如客户 - 地址连接表连接客户表和地址表，包含客户地址 ID、客户 ID 和地址 ID 三个主要列，其中客户地址 ID 为该表主键，客户 ID 和地址 ID 分别为客户表和地址表的主键。部分人更倾向将客户 ID 和地址 ID 设为复合主键而不使用客户地址 ID。此外，连接表还有创建时间戳和更新时间戳等标准支持列。
4. 维护数据仓库键 ：为支持多个 DDS 的一致性，需在 NDS 中定义和维护所有数据仓库键。除源系统的自然键外，NDS 表都应有数据仓库键。若只有一个 DDS 或从一个主 DDS 派生二级 DDS，可在 DDS 中生成数据仓库键；若有多个 DDS 且从 NDS 生成，在 NDS 中维护数据仓库键能确保多个 DDS 中代理键的一致性。从数据集成角度看，NDS 中每个表都需数据仓库键，用于映射多个源系统的参考数据。创建映射时，确定源系统表的主键并与 NDS 表中的数据仓库键匹配，在填充 NDS 表时按此映射构建 ETL。
5. 日期表维护 ：NDS 中也维护日期表，其数据来源于财政日历表、假日表和营业时间表，大部分列通过填充脚本内部生成，如周数、最后一天标志、系统日期、儒略日期、工作日名称、月份名称等。
6. 遵循规范化规则 ：设计 NDS 时需遵循规范化规则，目的是去除冗余数据，便于数据维护。但要注意规范化可能影响性能，并非一定要将 NDS 设计到第三范式，某些情况下设计为第二范式更合理，还有些情况需采用 Boyce - Codd 范式（BCNF）或第四范式等。例如，将客户表规范化到第三范式时，性别和状态等可能只有两三种取值的列需按规则拆分为单独的表，通过外键与客户表相连。
7. 列出列信息 ：根据 DDS 列和源系统列确定 NDS 列，只选取相关列，数据类型从源系统获取。若某列来自两个不同源系统，取较长的数据类型；若一个是整数，另一个是 varchar 类型（如订单 ID），则设为 varchar 类型。定义小数和浮点数时要注意不同 RDBMS 的精度差异，处理日期和时间戳数据类型时也要注意不同 RDBMS 的格式和行为差异。以客户表为例：
| 列名 | 数据类型 | 描述 | 示例值 |
| — | — | — | — |
| customer_key | int | 客户表的代理数据仓库键 | 84743 |
| customer_id | varchar(10) | 自然键，WebTower9 和 Jade 客户表的主键，WebTower9 格式为 AA999999，Jade 为 AAA99999 | DH029383 |
| account_number | int | 订阅客户的账号，否则为空 | 342165 |
| customer_type_id | int | 指向存储客户类型的客户类型表的外键 | 2 |
| name | varchar(100) | 客户全名 | Charlotte Thompson |
| gender | char(1) | M 表示男性，F 表示女性 | F |
| date_of_birth | smalldatetime | 客户出生日期 | 04/18/1973 |
| occupation_id | int | 指向存储客户职业的职业 ID 表的外键 | 439 |
| household_income_id | int | 指向存储客户家庭收入的家庭收入表的外键 | 732 |
| date_registered | smalldatetime | 客户首次购买或订阅的日期 | 11/23/2007 |
| status | int | 指向存储客户状态的客户状态表的外键 | Active |
| subscriber_class_id | int | 指向存储订阅者类别的订阅者类别表的外键 | 3 |
| subscriber_band_id | int | 指向存储订阅者分组的订阅者分组表的外键 | 5 |
| effective_timestamp | datetime | 对于 SCD 类型 2，数据行生效时间，默认 1/1/1900 | 1/1/1900 |
| expiry_timestamp | datetime | 对于 SCD 类型 2，数据行失效时间，默认 12/31/2999 | 12/31/2999 |
| is_current | int | 对于 SCD 类型 2，当前记录为 1，否则为 0 | 1 |
| source_system_code | int | 记录来源的源系统键 | 2 |
| create_timestamp | datetime | 记录在 NDS 中创建的时间 | 02/05/2008 |
| update_timestamp | datetime | 记录最后更新的时间 | 02/05/2008 |

完成以上步骤后，NDS 设计完成，它包含创建 DDS 所需的所有表且高度规范化。之后可返回 DDS，重新定义每个 DDS 列在 NDS 中的源，这是构建 NDS 和 DDS 之间 ETL 的必要步骤。具体操作是确定每个 DDS 表在 NDS 中的源表，再找出每个 DDS 列对应的 NDS 列。

3. 物理数据库设计

在完成维度数据存储和规范化数据存储的逻辑模型设计后，接下来要将这些数据存储物理实现为 SQL Server 数据库。在进行物理数据库设计时，需考虑以下几个方面：

3.1 硬件平台

系统架构方面，有一个 ETL 服务器、两个数据库服务器（集群）、两个报告服务器（负载均衡）和两个 OLAP 服务器。SAN 中有 12TB 原始磁盘空间，由 85 个 146GB、15000 RPM 的磁盘组成，所有与 SAN 的网络连接通过光纤网络，并有两个光纤通道交换机以实现高可用性。预计使用数据仓库的客户端 PC 数量在 300 - 500 之间。

由于数据仓库需 24×7 可用，每月停机时间不超过一小时，因此：
- 数据库引擎 ：不能在单台服务器上实现 SQL Server 数据库引擎，需采用故障转移集群。故障转移集群是在多个相同服务器（节点）上安装 SQL Server 的配置，数据库实例在活动节点运行，活动节点不可用时自动切换到备用节点。
- SQL Server 报告服务（SSRS） ：其 Web 或 Windows 服务部分需部署在网络负载均衡（NLB）集群中，报告服务数据库安装在故障转移集群中。这样可确保 SSRS 始终可用，当一台服务器不可用时，系统自动切换到其他服务器。
- 分析服务（SSAS） ：也需安装在故障转移集群中，可与数据库引擎在同一集群或单独集群。若预算允许，建议安装在单独集群，以便分别优化和调整内存及 CPU 使用。
- SQL Server 集成服务（SSIS） ：不是集群感知应用程序，只能安装在单台服务器上。虽然 SSIS 不可用时不影响最终用户对数据仓库数据存储的查询，但会影响数据时效性。

在网络方面，SSIS 服务器与数据库服务器、数据库服务器与 OLAP 服务器之间建议使用千兆网络（1Gbps 吞吐量），以提高分析服务性能，特别是使用关系 OLAP（ROLAP）时，也能提高 SSIS 向数据库服务器加载数据的速率。若 SAN 被多个应用程序使用，可根据网络流量考虑使用 2Gbps 或 4Gbps 的光纤网络，并使用两个冗余光纤通道交换机以匹配 SAN 的 RAID 磁盘、OLAP 和数据库服务器故障转移集群的高可用性。

3.2 服务器技术规格

不同服务器的技术规格需根据多种因素确定，以下是各服务器的相关考虑：
- SSRS 服务器 ：报告服务 Web 农场（扩展部署）对服务器规格要求不高。对于有 300 - 500 用户的情况，两到三个具有两个 CPU 和 2GB 或 4GB RAM 的节点可能就足够，具体取决于报告复杂度。若有 1000 用户，可根据使用情况添加节点。
- ETL 服务器 ：ETL 服务器通常进行大量计算，受益于 CPU 性能，其所需内存取决于转换过程中在线查找的大小。对于相关案例，基于之前的数据模型、业务需求和数据可行性研究，四个 CPU 和 8GB 到 16GB 内存目前足够，且能保证未来两到三年的增长。若使用双核超线程 CPU，可将 CPU 数量调整为两个。同时，要考虑未来新的数据仓库/ETL 项目和增强请求。
- SSAS 服务器 ：OLAP 服务器集群硬件的内存需求受多个因素影响，如运行的大型立方体数量、分区数量、分区处理时是否有 OLAP 查询、大文件系统缓存和处理缓冲区的需求、所需的大型副本数量以及同时运行的大量关系数据库查询和 OLAP 查询数量等。对于当前案例，每个节点四个 CPU 和 8GB RAM 足够，并能适应未来更多立方体和数据的增长。可参考 SQL Server 2005 分析服务性能指南文档进行规模调整。
- 数据库服务器 ：数据库服务器的规模确定较为困难，主要取决于应用程序对数据库的访问频率和复杂度。以下是一些考虑因素：
- 查询负载 ：数据库服务器主要用于查询，约 10% - 20% 的时间用于加载数据，80% - 90% 的时间用于满足用户和应用程序查询。查询越重，所需的内存和 CPU 越多。
- 数据加载方法 ：采用 ELT 方法（将数据以原始格式加载到数据库服务器，然后通过存储过程进行基于集合的操作转换为 NDS 或 ODS 格式）比 ETL 方法需要更强大的数据库服务器。
- 计算和防火墙规则 ：若从阶段层到 NDS/ODS 的计算和防火墙规则在数据库服务器上运行（如存储过程或触发器），会影响数据库服务器性能，规则越复杂，所需 CPU 越多。
- 数据存储数量和大小 ：用户面对的数据存储越多，所需的内存和 CPU 越多，因为会有更多用户连接。数据存储越大，ETL 过程涉及的记录越多，为在相同时间内完成大型 ETL 过程，需要更强大的硬件。
- 物理设计 ：合理的物理数据库设计（如索引、分区等）可在不显著改变服务器硬件的情况下提高加载和查询性能。
- 其他数据库和未来增长 ：数据库服务器可能托管其他数据库，需考虑未来两到三年的增长。对于相关案例，四个 CPU 和 8GB 到 16GB RAM 适合数据库服务器，可参考 Microsoft 的 RDBMS 性能调优指南和 Project REAL。

3.3 操作系统和 SQL Server 版本选择

• 操作系统 ：Windows 2003 R2 企业版（EE）支持最多八个处理器和 64GB RAM，适合相关案例的数据库服务器。由于案例中不需要更高规格，无需使用 Windows 2003 R2 数据中心版。
• SQL Server 版本 ：需确定使用 32 位还是 64 位版本。在 32 位平台上，Analysis Services 只能使用 3GB RAM，因此案例中 SSAS 服务器和数据库服务器都需要 64 位版本。SQL Server 有六个版本，对于企业级数据仓库解决方案，实际可使用标准版或企业版。虽然标准版支持四个 CPU 和无限 RAM，但由于案例的高可用性和性能要求，需要企业版，因为标准版缺少以下重要功能：
  • 表和索引分区：可将表物理分割为更小的块，便于单独加载和查询，处理包含数十亿行的事实表时是必备功能。
  • 报告服务器扩展部署：可在多个 Web 服务器上运行报告服务，同时服务数百或数千用户并实现高可用性。
  • 分析服务分区立方体：将立方体分割为更小的块，减少立方体处理时间并提高查询性能。
  • 半加性聚合函数：可处理在某些维度可求和但在其他维度不可求和的度量。
  • 并行索引操作：可使用多个处理器同时创建或重建索引，处理大型事实表时很有用。
  • 在线索引操作：允许在用户查询或更新表数据时创建或重建索引，可最大程度提高数据存储的可用性。
  • 故障转移集群节点数量：标准版最多支持两个节点，限制了未来扩展，企业版无此限制。

3.4 许可证选择

SQL Server 有两种许可模式：
- 按处理器许可 ：为服务器中的每个处理器购买许可证，与用户数量无关。在美国，企业版每个处理器许可证零售价约为 25000 美元。
- 服务器 + CAL 许可 ：购买服务器许可证和每个访问服务器的客户端的客户端访问许可证（CAL）。在美国，企业版一个服务器许可证零售价约为 14000 美元（包含 25 个 CAL），每个额外客户端许可证约为 162 美元。

对于相关案例，若按处理器许可，需要 16 个处理器许可证，费用为 16×25000 = 400000 美元；若使用服务器 + CAL 许可，对于 500 个用户，需要五个服务器许可证和 375 个额外 CAL，费用为 (5×14000) + (375×162) = 130750 美元。因此，在这种情况下，服务器 + CAL 许可模式更优。

综上所述，在进行数据仓库的物理数据库设计时，要综合考虑硬件平台、服务器技术规格、操作系统和 SQL Server 版本以及许可证等多方面因素，以确保数据库的高效运行和满足业务需求。

数据仓库设计与物理实现全解析

4. 硬件与软件配置的协同优化

4.1 硬件与性能需求的匹配

在确定服务器技术规格时，要紧密结合业务的性能需求。例如，对于数据仓库而言，查询性能是关键指标之一。如果业务中涉及大量复杂的分析查询，如多表关联、聚合计算等，那么数据库服务器的 CPU 和内存配置就需要相应提高。可以通过以下步骤进行硬件与性能需求的匹配：
1. 分析业务查询模式 ：收集业务中常见的查询语句，分析其复杂度、涉及的表和数据量。例如，统计某一时间段内不同地区的销售总额，这可能涉及到销售事实表、地区维度表和时间维度表的关联查询。
2. 进行性能测试 ：使用模拟数据和实际查询语句，在不同硬件配置下进行性能测试。记录查询响应时间、吞吐量等指标，找出满足业务需求的最低硬件配置。
3. 考虑未来增长 ：根据业务的发展规划，预估未来数据量和查询复杂度的增长。在配置硬件时，预留一定的扩展空间，以避免短期内因业务增长而需要再次升级硬件。

4.2 软件版本与功能的适配

选择合适的 SQL Server 版本和操作系统对于数据仓库的性能和功能至关重要。不同版本的 SQL Server 提供了不同的功能特性，需要根据业务需求进行选择。例如，如果业务需要处理大量的实时数据，那么 SQL Server 企业版的实时分析功能就可能是必需的。以下是选择软件版本的步骤：
1. 明确业务功能需求 ：列出业务所需的功能，如数据分区、并行处理、高可用性等。
2. 评估各版本功能支持 ：对比不同版本的 SQL Server 提供的功能，确定哪些版本能够满足业务需求。
3. 考虑成本因素 ：不同版本的 SQL Server 价格不同，需要在满足业务需求的前提下，选择成本效益最高的版本。

5. 数据仓库的维护与管理

5.1 数据仓库的监控

为了确保数据仓库的稳定运行，需要建立有效的监控机制。监控内容包括服务器性能指标、数据库状态、ETL 作业执行情况等。以下是监控的主要方面：
- 服务器性能监控 ：监控 CPU 使用率、内存使用率、磁盘 I/O 等指标，及时发现性能瓶颈。例如，当 CPU 使用率持续超过 80% 时，可能需要考虑升级 CPU 或优化查询语句。
- 数据库状态监控 ：监控数据库的连接数、事务处理情况、锁等待时间等。如果发现大量的锁等待，可能需要优化数据库设计或调整事务处理逻辑。
- ETL 作业监控 ：监控 ETL 作业的执行时间、成功率等。如果 ETL 作业执行时间过长或频繁失败，需要检查 ETL 脚本和数据源的稳定性。

5.2 数据仓库的备份与恢复

数据仓库中的数据是企业的重要资产，需要定期进行备份，以防止数据丢失。备份策略应根据数据的重要性和变化频率进行制定。以下是备份与恢复的步骤：
1. 确定备份策略 ：根据数据的重要性和变化频率，选择全量备份、增量备份或差异备份。例如，对于关键业务数据，建议每天进行全量备份，对于变化较小的数据，可以采用增量备份。
2. 执行备份操作 ：按照备份策略定期执行备份操作，将备份数据存储在安全的位置，如磁带库或异地数据中心。
3. 测试恢复流程 ：定期测试数据恢复流程，确保在需要时能够快速、准确地恢复数据。

6. 数据仓库设计与实现的总结

数据仓库的设计与物理实现是一个复杂的过程，需要综合考虑多个方面的因素。从数据仓库表的映射与转换，到规范化数据存储（NDS）的设计，再到物理数据库的设计和配置，每一个环节都对数据仓库的性能和功能产生影响。

在设计过程中，要遵循规范化规则，去除冗余数据，提高数据的一致性和可维护性。同时，要根据业务需求和性能要求，合理选择硬件平台、服务器技术规格、操作系统和 SQL Server 版本，并制定合适的许可证策略。

在数据仓库的运行过程中，要建立有效的监控机制，及时发现和解决问题，确保数据仓库的稳定运行。定期进行数据备份和恢复测试，保障数据的安全性和可用性。

通过合理的设计和有效的管理，数据仓库能够为企业提供准确、及时的数据分析支持，帮助企业做出更明智的决策。

流程图示例

graph LR
    A[数据仓库设计] --> B[表映射与转换]
    A --> C[NDS 设计]
    A --> D[物理数据库设计]
    B --> E[确定源系统表]
    B --> F[定义连接条件]
    B --> G[映射维度列]
    C --> H[列出实体]
    C --> I[确定实体关系]
    C --> J[处理多对多关系]
    C --> K[维护数据仓库键]
    C --> L[维护日期表]
    C --> M[遵循规范化规则]
    C --> N[列出列信息]
    D --> O[选择硬件平台]
    D --> P[确定服务器规格]
    D --> Q[选择操作系统和版本]
    D --> R[选择许可证模式]

总结表格

阶段	主要任务	关键考虑因素
数据仓库表映射与转换	记录源系统表、连接条件、维度列映射和转换方式	确保每个列的来源和转换清晰
规范化数据存储（NDS）设计	列出实体、确定关系、处理多对多关系、维护键和日期表、遵循规范化规则、列出列信息	去除冗余数据，提高数据一致性和可维护性
物理数据库设计	选择硬件平台、确定服务器规格、选择操作系统和版本、选择许可证模式	满足业务性能需求和成本效益
数据仓库维护与管理	监控服务器性能、数据库状态和 ETL 作业，定期备份和恢复数据	确保数据仓库稳定运行和数据安全