数据仓库填充：NDS 与 DDS 的实践指南

1. 数据防火墙保障数据质量

数据防火墙在数据仓库加载过程中扮演着至关重要的角色，它能够确保数据质量。每当数据防火墙捕获到不符合数据质量规则的不良数据时，这些数据会连同捕获规则、采取的操作以及发生时间一同被存储在数据质量数据库中。基于此数据库，我们可以生成相关报告，还能设置数据质量系统，在特定数据质量规则被违反时通知相关人员。

与网络防火墙不同，数据防火墙不仅能检测不良数据，还能对其进行修复。当检测到不良数据时，可设置以下三种操作：
- 拒绝数据 ：不将数据加载到数据仓库。
- 允许数据 ：将数据加载到数据仓库。
- 修复数据 ：在将数据加载到数据仓库之前进行修正。

在将数据加载到规范化数据存储（NDS）之前，我们会让数据通过防火墙规则进行检查，以此确保数据质量。

2. 填充 NDS 的考虑因素

在 NDS + DDS 架构中，需要先填充 NDS 中的表，再填充 DDS 中的维度和事实表，因为 DDS 是基于 NDS 数据进行填充的。填充 NDS 与填充阶段表有所不同，填充 NDS 时需要对数据进行规范化处理，而填充阶段表则无需如此。我们从阶段表或源系统中提取数据，然后加载到 NDS 数据库。若记录在 NDS 中不存在，则进行插入操作；若已存在，则进行更新操作。

填充 NDS 时，需要考虑以下几个方面：

2.1 规范化

NDS 是规范化存储，但源系统可能并非如此，因此需要对数据进行规范化处理，以适应 NDS 的结构。例如，源系统中的商店表可能是非规范化的，如下所示：
| store_number | store_name | store_type |
| — | — | — |
| 1805 | Perth | Online |
| 3409 | Frankfurt | Full Outlet |
| 1014 | Strasbourg | Mini Outlet |
| 2236 | Leeds | Full Outlet |
| 1808 | Los Angeles | Full Outlet |
| 2903 | Delhi | Online |

需要将其转换为 NDS 中的规范化表：
| store_number | store_name | store_type_key |
| — | — | — |
| 1805 | Perth | 1 |
| 3409 | Frankfurt | 2 |
| 1014 | Strasbourg | 3 |
| 2236 | Leeds | 2 |
| 1808 | Los Angeles | 2 |
| 2903 | Delhi | 1 |

同时，源系统中的 store_type 列被规范化为单独的表：
| store_type_key | store_type |
| — | — |
| 1 | Online |
| 2 | Full Outlet |
| 3 | Mini Outlet |

如果源系统中没有 store_type 表，则需要根据商店表中的数据来填充 NDS 中的 store_type 表。若商店表中包含新的商店类型，则需将其插入到 store_type 表中。

2.2 外部数据

在填充 NDS 时，可能会引入外部数据，如 ISO 国家表。由于外部数据可能与源系统数据不匹配，因此在加载数据时可能需要进行数据转换。例如，源系统中的客户记录可能使用不同的国家名称，如“Congo (Zaire)”，而外部数据为“Congo”。为避免在国家表中创建重复条目，可在数据质量例程中应用规则，将“Congo (Zaire)”替换为“Congo”。

2.3 键管理

在 NDS 中，需要创建和维护内部数据仓库键，这些键也将在 DDS 中使用。数据仓库使用代理键（SK），源系统使用自然键（NK），SK 有助于集成多个源系统，因为可以将 NK 与 SK 进行映射。例如，不同源系统中的产品状态表可以通过 SK 进行关联。

以下是不同源系统和 NDS 中产品状态表的示例：
- Jade 中的产品状态表
| status_code | description |
| — | — |
| AC | Active |
| QC | Quality Control |
| IN | Inactive |
| WD | Withdrawn |
- NDS 中的产品状态表
| product_status_key | product_status_code | product_status | other columns |
| — | — | — | — |
| 0 | UN | Unknown | … |
| 1 | AC | Active | … |
| 2 | QC | Quality Control | … |
| 3 | IN | Inactive | … |
| 4 | WD | Withdrawn | … |
- Jupiter 中的产品状态表
| status_code | description |
| — | — |
| N | Normal |
| OH | On Hold |
| SR | Supplier Reject |
| C | Closed |

通过 SK，可将 Jupiter 中的“Normal”与 Jade 中的“Active”关联，并映射到 SK 1；“Inactive”和“Closed”映射到 SK 3。

2.4 关联表

关联表用于实现多对多关系。在数据建模时，NDS 中创建了五个关联表，如 address_junction 、 phone_number_junction 等。以 phone_number_junction 为例，源系统中的客户表包含客户的电话号码，我们需要将这些信息进行拆分和关联。

源系统中的客户表：
| customer_id | customer_name | home_phone_number | office_phone_number | cell_phone_number |
| — | — | — | — | — |
| 56 | Brian Cable | (904) 402 - 8294 | (682) 605 - 8463 | N/A |
| 57 | Valerie Arindra | (572) 312 - 2048 | N/A | (843) 874 - 1029 |
| 58 | Albert Amadeus | N/A | (948) 493 - 7472 | (690) 557 - 3382 |

NDS 中的相关表如下：
- NDS 客户表
| customer_key | customer_id | customer_name |
| — | — | — |
| 801 | 56 | Brian Cable |
| 802 | 57 | Valerie Arindra |
| 803 | 58 | Albert Amadeus |
- NDS 电话号码关联表
| phone_number_junction_key | customer_key | phone_number_key | phone_number_type_key |
| — | — | — | — |
| 101 | 801 | 501 | 1 |
| 102 | 801 | 502 | 2 |
| 103 | 802 | 503 | 1 |
| 104 | 802 | 504 | 3 |
| 105 | 803 | 505 | 2 |
| 106 | 803 | 506 | 3 |
- NDS 电话号码表
| phone_number_key | phone_number |
| — | — |
| 0 | unknown |
| 501 | (904) 402 - 8294 |
| 502 | (682) 605 - 8463 |
| 503 | (572) 312 - 2048 |
| 504 | (843) 874 - 1029 |
| 505 | (948) 493 - 7472 |
| 506 | (690) 557 - 3382 |
- NDS 电话号码类型表
| phone_number_type_key | phone_number_type |
| — | — |
| 0 | Unknown |
| 1 | Home phone |
| 2 | Office phone |
| 3 | Cell phone |

NDS 表是规范化的，这样可以避免数据冗余，但在 ETL 过程中进行这种转换可能会比较复杂。在 ETL 中，需要先填充客户表和电话号码表，然后通过查找客户键和电话号码键来填充关联表。电话号码类型表是静态的，在初始化数据仓库时进行填充。

3. 使用 SSIS 填充 NDS

可以使用 SSIS 中的缓慢变化维度向导来填充 NDS 中的表。以下是使用 SSIS 填充国家表到 NDS 的具体步骤：
1. 创建新包 ：在解决方案资源管理器中，右键单击 SSIS 包，选择“新建 SSIS 包”。
2. 创建数据流任务 ：在设计界面创建一个数据流任务，并将其重命名为“Country”。
3. 创建 OLE DB 源 ：双击数据流任务进行编辑，创建一个 OLE DB 源，命名为“Stage Country”，连接到 servername.Stage.ETL OLE DB 连接，将数据访问模式设置为“表或视图”，表名设置为“country”。
4. 预览数据 ：点击“预览”查看数据，然后点击“关闭”，再点击“确定”关闭 OLE DB 源编辑器窗口。
5. 添加派生列转换 ：在工具箱中找到“派生列”转换，拖到设计界面，将阶段表的“Country”列的绿色箭头连接到“派生列”。双击“派生列”进行编辑。
6. 设置派生列 ：在“派生列名称”下的单元格中输入 source_system_code ，将表达式设置为 2，数据类型设置为单字节无符号整数。再创建两个列 create_timestamp 和 update_timstamp ，将表达式都设置为 getdate() ，数据类型设置为数据库时间戳。点击“确定”关闭派生列转换编辑器对话框。
7. 添加缓慢变化维度转换 ：在工具箱中找到“缓慢变化维度”，拖到设计界面，放在“派生列”框下方，将“派生列”框的绿色箭头连接到“缓慢变化维度”框。
8. 配置缓慢变化维度向导 ：双击“缓慢变化维度”框，打开缓慢变化维度向导。点击“下一步”，点击“新建”按钮将连接管理器设置为 servername.NDS.ETL ，将“表或视图”下拉列表设置为“country”。将 country_code 的“非键列”改为“业务键”。
9. 设置维度列 ：点击“下一步”，在“维度列”下的空白单元格中选择 country_name ，将“更改类型”设置为“更改属性”。对 source_system_code 和 update_timestamp 做同样的设置。不更新 create_timestamp 列，因为它在记录创建时只设置一次。
10. 选择更改类型选项 ：“更改属性”选项适用于 SCD 类型 1（覆盖现有值），“历史属性”适用于 SCD 类型 2（通过将新值写入新记录来保留历史）。点击“下一步”，不勾选“更改属性”复选框，因为国家维度中没有过时的记录。
11. 禁用推断成员支持 ：点击“下一步”，取消勾选“启用推断成员支持”。推断维度成员是指事实表引用尚未加载的维度行的情况，后续会将事实表中的未知维度值映射到未知维度记录。
12. 完成向导 ：点击“下一步”，然后点击“完成”完成缓慢变化维度向导。将 OLE DB 命令框重命名为“Update Existing Rows”，将插入目标框重命名为“Insert New Rows”。

完成上述步骤后，点击“调试”菜单，选择“开始调试”运行包。若包运行成功，所有框应变为绿色。使用 SQL Server Management Studio 查询阶段表和 NDS 数据库中的国家表，确保国家记录已成功加载到 NDS。

此外，在 NDS 的每个主表中都需要添加未知记录，可使用以下 Transact SQL 创建未知记录：

set identity_insert country on
insert into country
( country_key, country_code, country_name, source_system_code,
create_timestamp, update_timestamp )
values
( 0, 'UN', 'Unknown', 0,
'1900-01-01', '1900-01-01' )
set identity_insert country off

对于状态表，也可使用相同的过程进行填充。OLE DB 源的 SQL 命令如下：

select state_code, state_name, formal_name,
admission_to_statehood, population,
capital, largest_city,
cast(2 as tinyint) as source_system_code,
getdate() as create_timestamp,
getdate() as update_timestamp
from state

状态表的未知记录初始化 SQL 语句如下：

set identity_insert state on
insert into state
( state_key, state_code, state_name, formal_name,
admission_to_statehood, population,
capital, largest_city, source_system_code,
create_timestamp, update_timestamp )
values
( 0, 'UN', 'Unknown', 'Unknown',
'1900-01-01', 0,
'Unknown', 'Unknown', 0,
'1900-01-01', '1900-01-01' )
set identity_insert state off

首次执行包时，会将记录插入到目标表；再次运行时，会更新目标表中的记录。

综上所述，通过数据防火墙保障数据质量，考虑填充 NDS 的多个因素，并使用 SSIS 进行填充操作，可以有效地完成数据仓库中 NDS 的填充工作。

4. 填充 NDS 过程中的数据处理流程总结

为了更清晰地展示填充 NDS 的整个过程，我们可以用 mermaid 流程图来表示：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px;
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px;

    A([开始]):::startend --> B(数据通过防火墙规则检查):::process
    B --> C{数据是否符合规则}:::decision
    C -->|是| D(数据进入规范化流程):::process
    C -->|否| E(根据规则处理不良数据):::process
    E --> D
    D --> F{是否为外部数据}:::decision
    F -->|是| G(进行数据转换):::process
    F -->|否| H(直接进行规范化操作):::process
    G --> H
    H --> I(处理键管理):::process
    I --> J(填充关联表):::process
    J --> K(使用 SSIS 填充 NDS 表):::process
    K --> L(添加未知记录):::process
    L --> M([结束]):::startend

这个流程图展示了从数据进入系统，经过防火墙检查，到最终填充 NDS 表并添加未知记录的整个过程。在每个环节都有相应的处理逻辑，确保数据的质量和规范性。

5. 填充过程中的注意事项

5.1 数据一致性

在处理多个源系统和外部数据时，要特别注意数据的一致性。例如，在处理国家名称时，由于不同数据源可能使用不同的名称，需要通过规则进行统一，避免出现重复条目。可以建立一个数据字典，记录不同数据源中相同含义的数据的不同表示形式，以便在数据处理过程中进行映射和转换。

5.2 键管理的稳定性

键管理是填充 NDS 的重要环节，代理键（SK）的使用有助于集成多个源系统。但在实际操作中，要确保键的稳定性，避免因为源系统的键变化而导致数据关联出现问题。可以定期检查源系统的键变化情况，及时更新 NDS 中的映射关系。

5.3 缓慢变化维度的处理

在使用 SSIS 的缓慢变化维度向导时，要根据实际需求选择合适的变化类型。SCD 类型 1 适用于覆盖现有值的情况，而 SCD 类型 2 适用于保留历史记录的情况。在设置过程中，要仔细考虑每个维度列的变化特性，确保数据的准确性和完整性。

5.4 未知记录的设置

未知记录的设置对于维护 NDS 的引用完整性非常重要。要确保未知记录的键值在整个系统中保持一致，一般建议使用 0 或 -1 作为键值。同时，未知记录的其他列值也要根据数据类型进行合理设置，避免使用 NULL 值，以便与真正没有值的行进行区分。

6. 填充 NDS 的优势与挑战

6.1 优势

• 数据质量保障 ：通过数据防火墙和规范化处理，能够有效提高数据的质量，减少数据错误和不一致性。
• 数据集成与关联 ：NDS 的规范化结构和键管理机制有助于集成多个源系统的数据，并实现数据之间的关联，为后续的数据分析提供更全面的信息。
• 数据维护方便 ：由于 NDS 表的规范化，数据冗余减少，当数据发生变化时，只需要在一个地方进行更新，提高了数据维护的效率。

6.2 挑战

• 数据转换复杂 ：在填充 NDS 时，需要对数据进行规范化处理和数据转换，尤其是涉及到外部数据时，可能需要进行复杂的映射和规则处理。
• ETL 实现难度大 ：NDS 的规范化结构在 ETL 过程中需要进行复杂的操作，如关联表的填充和缓慢变化维度的处理，对 ETL 开发人员的技术要求较高。
• 性能问题 ：在处理大量数据时，数据防火墙的检查和规范化处理可能会导致性能下降，需要进行性能优化。

7. 性能优化建议

7.1 数据分区

对于大型表，可以采用数据分区的方式，将数据按照一定的规则划分成多个分区。例如，按照时间、地域等维度进行分区。这样在查询和处理数据时，可以只针对特定的分区进行操作，提高查询性能。

7.2 索引优化

合理创建索引可以提高数据的查询速度。在 NDS 中，可以根据经常用于查询的列创建索引。但要注意，过多的索引会增加数据插入、更新和删除的开销，因此需要根据实际情况进行权衡。

7.3 批量操作

在进行数据插入、更新和删除操作时，尽量采用批量操作的方式，减少与数据库的交互次数。例如，使用 SQL 的批量插入语句 INSERT INTO...VALUES (...) 一次性插入多条记录。

7.4 并行处理

对于复杂的 ETL 任务，可以采用并行处理的方式，将任务分解成多个子任务并行执行。例如，使用多线程或分布式计算框架，提高数据处理的效率。

8. 总结

填充 NDS 是数据仓库建设中的重要环节，它涉及到数据质量保障、规范化处理、键管理、关联表填充等多个方面。通过使用数据防火墙和 SSIS 等工具，可以有效地完成 NDS 的填充工作。但在实际操作中，也会面临数据一致性、ETL 实现难度、性能等挑战。我们需要充分认识到这些挑战，并采取相应的优化措施，如数据分区、索引优化、批量操作和并行处理等，以确保 NDS 的高效运行和数据质量。同时，在整个过程中要注意未知记录的设置和缓慢变化维度的处理，维护 NDS 的引用完整性和数据的历史记录。通过合理的规划和实施，NDS 能够为数据仓库提供高质量、规范化的数据，为后续的数据分析和决策支持奠定坚实的基础。