构建混合数据仓库模型

作者：James Madison根据本参考实施的建议，在某些情况下，关系模型与维度模型混合不失为一种恰当的数据仓库设计方法。
2007 年 4 月发布
关系模型与维度模型通常单独使用，但是根据需要，也可将它们混合到一起使用。为此，首先要建立一个规范化的关系模型，然后添加维度结构（主要在物理层上）。结果得到一个兼具两种父级模型优势的单个模型：它表示的实体与关系具有传统关系模型的精度，它按维度处理已过滤的、聚合事实的查询的速度与传统维度模型的速度相差无几。
现实经历促使我进行了这样的分析：在三个独立的数据仓库项目中，我分别担任过程序员、架构师和项目经理。在这些项目中，我发现了一种数据/数据库行为一致的模式，这种行为模式使得维度模型与关系模型混合构成的模型较两者中任何一种单一的模型更适合使用。
本文讨论混合设计，并提供功能齐全的参考实施。系统在 Oracle 数据库 10g 上运行，它包括完成下列任务所需的所有代码：构建数据库模式、生成示例数据、将示例数据加载到数据库模式中、构建索引和物化视图、运行示例查询、捕获运行时间以及提供运行时间的统计信息。
混合模型并不是通用型解决方案。许多项目最好使用一种传统模型或者单独使用两种模型（模型间有馈给）。但是，如果要创建一个既能以规范化格式存储数据又能以良好的性能运行聚合查询的单一数据库，那么混合模型便是一种值得考虑的设计模式。
示例业务领域
示例业务领域是在保险业内，并使用以下实体：
实体 描述
ACCOUNT
有关客户及其与保险公司间的活动的信息
POLICY
代表与客户的具体协议的保险合同
VEHICLE
保险单上提到的属于客户的车辆
COVERAGE
保险单上提到的车辆损失种类
PREMIUM
客户每月为该保险单中的车辆承保项目所支付的费用
 
用于分析系统性能的示例业务问题与现实中的有些类似，但也包含一些极端的行为，例如：扫描事实表的多个行、检索很小一部分事实行、限制为顶部表、限制为每个表、限制为底部表等等。这些是业务用户针对维度模型提及的问题类型，并非针对关系模型提及的常见问题类型。这里不讨论关系模型的问题，因为假设对于关系特性问题（例如“显示该保险单上的所有车辆”），关系模型的性能优于维度模型。本次分析中使用的问题如下：
ID 号 维度特性的业务问题
1 我们最早收取的年度保险费总额是多少？
2 2002 年在新英格兰州收取的保险费是多少？
3 我们最早在康涅狄格所得的的中级巨灾风险保险费是多少？
4 2001 年我们在加利福尼亚所得的时间管理计划类型保险费是多少？
5 2003 年 11 月有多少客车上了撞车险？
6 佛蒙特州具有主要用途（保险扣除额为 1,000 美元）的红色车辆的保险费是多少？按人及事故限额对这些数目进行细分。
7 2000 年，保险扣除额为 1,000 美元，每人限额为 100,000 美元以及事故限额为 800,000 美元的保险项目的保险费是多少？
8 1999 年，750cc 引擎红色汽车每月的保险费是多少？
 
模型
图 1、2 和 3 显示了三种模型。其中，混合模型在关系模型的基础上做了两处维度建模更改：(1) 创建了 PREMIUM 表与层次上部每个表的关系，并 (2) 添加了时间维度。

图 1。关系模型

图 2。维度模型

图 3。混合模型
实施
使用较标准的技术以数据库模式将模型转换为物理实施。使用规范化建模技术创建关系模式，根据 Ralph Kimball 的介绍创建维度模式。创建混合模式意味着，首先复制关系模式，然后在关系模式上搭建维度结构层次。（“文件描述”边栏列出了实施中最重要的文件，包括具有 DDL、系统验证、查询和用于生成示例代码的自动分析的文件。）
因为只使用了三个非关键属性，所以在每个表中添加了 SIZING 属性，类型为 CHAR(100)，这样可使行大小更接近实际。
必须 设置某些数据库参数，以便出现星型连接并使用物化视图。重要参数如下所示：
NAME VALUE------------------------------ --------------------compatible 10.2.0.1.0optimizer_features_enable 10.2.0.1optimizer_mode first_rowspga_aggregate_target 83886080query_rewrite_enabled truequery_rewrite_integrity stale_toleratedsga_target 167772160star_transformation_enabled true
按照 Oracle 文档中的详细描述使用 EXPLAIN PLAN 来验证是否出现星型连接。
三种模式都加载了相同的数据。加载了相同数据的最好证据就是，三种模式都对示例查询生成了相同的回答。
分析所用的数据量如下所示。
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED------ ------------ ---------- ----------- -------------------DIM ACCOUNT_DIM 2000 128 2006-01-14:19-51-56COVERAGE_DIM 900 17 2006-01-14:19-51-57POLICY_DIM 6000 128 2006-01-14:19-51-58PREMIUM_FACT 1371183 23 2006-01-14:19-52-14TIME_DIM3600 21 2006-01-14:19-52-39VEHICLE_DIM 24000 130 2006-01-14:19-52-39HYB ACCOUNT 2000 128 2006-01-14:19-53-42COVERAGE 144000 28 2006-01-14:19-53-47POLICY 6000 142 2006-01-14:19-53-53PREMIUM 1373463 49 2006-01-14:19-54-41TIME_DIM3600 21 2006-01-14:19-55-08VEHICLE 24000 144 2006-01-14:19-55-10REL ACCOUNT 2000 124 2006-01-14:19-39-22COVERAGE 144288 27 2006-01-14:19-39-30POLICY 6000 138 2006-01-14:19-39-31PREMIUM 1389963 29 2006-01-14:19-40-08VEHICLE 24000 139 2006-01-14:19-40-13
目的是为了提供足够大的数据量，以阻止优化程序走捷径，例如阅读整个表时阻止其使用索引及其他此类会破坏分析的优化技术。按照 Oracle 数据库数据仓库指南 10 g 第 2 版 (10.2) — 模式建模技术，如果优化程序发现“太小而不值得转换的表”，则星形转换可能不会发生。
实施的一个相当任意的目标是事实表至少有一百万个行。如果 QUERIES.SQL 生成的所有维度查询计划和混合查询计划都符合星型连接标准，则使用的数据量看来足够进行当前的分析。
由于弱实体必须以维度模式表示，因此维度模式中的 COVERAGE_DIM 行的行数较其他两种模式的 DIMENSION 表中的行数少。
以下为各种模式占用的空间：
OWNER TOTAL_SIZE--------------- ----------------DIM 129,499,136HYB 244,056,064REL 130,023,424
因为混合模式集关系和维度于一体，所以它的大小大概是关系模式和维度模式的大小之和，再除去所有通用元素，则得出以上数字。
运行系统
每个查询运行 21 次，中间的运行时间用作代表值，如下所示。
EVENT WINNER_TIME RNR_UP_TIME LOSER_TIME----- -------------------- -------------------- --------------------1. DIM = 00:00:06.049 REL = 00:00:09.023 HYB = 00:00:09.6442. DIM = 00:00:04.186 HYB = 00:00:07.961 REL = 00:00:08.0923. DIM = 00:00:03.415 HYB = 00:00:04.938 REL = 00:00:05.4284. DIM = 00:00:00.140 HYB = 00:00:00.190 REL = 00:00:06.9905. HYB = 00:00:00.131 DIM = 00:00:00.651 REL = 00:00:05.4186. DIM = 00:00:00.530 HYB = 00:00:01.392 REL = 00:00:05.4787. DIM = 00:00:00.520 HYB = 00:00:01.572 REL = 00:00:07.9718.DIM = 00:00:00.461 HYB = 00:00:00.731 REL = 00:00:01.882
转换为百分比大小以使数值成为相对值而不是绝对值，并通过定义将最快模式强制为百分之百，则得到如下百分比：
EVENT WINNER_OFFSET RNR_UP_OFFSET LOSER_OFFSET----- -------------------- -------------------- --------------------1. DIM = 100% REL = 149% HYB = 159%2. DIM = 100% HYB = 190% REL = 193%3. DIM = 100% HYB = 145% REL = 159%4. DIM = 100% HYB = 136% REL = 4993%5. HYB = 100% DIM = 497% REL = 4136%6. DIM = 100% HYB = 263% REL = 1034%7. DIM = 100% HYB = 302% REL = 1533%8. DIM = 100% HYB = 159% REL = 408%比较关系模式和维度模式
表明维度模式在运行维度查询时的性能优于关系模式是本实验的核心，这也为考虑混合模式的性能提供了基础。从下面的内容可以看出，正如我们所料，维度模式的性能一直优于关系模式。
EVENT WINNER_OFFSET RNR_UP_OFFSET LOSER_OFFSET----- -------------------- -------------------- --------------------1. DIM = 100% REL = 149%2. DIM = 100% REL = 193%3. DIM = 100% REL = 159%4. DIM = 100% REL = 4993%5. DIM = 497% REL = 4136%6. DIM = 100% REL = 1034%7. DIM = 100% REL = 1533%8. DIM = 100% REL = 408%
在查询 4 中，差值接近 50 倍！查询 4是最极端的情况，其中唯一的（非时间）限制是对最顶端的表的属性限制。在关系模式中，这意味着层次结构下所有的表必须连接起来，以便得到数值信息，但这是成本很高的操作。在维度模式中，该连接是从一个维度直接到事实表的连接，这是非常高效的操作。
混合模式与维度模式比较
混合模式的性能是否与维度模式一样卓越是这次分析的核心问题。从下面的内容可以看出，混合模式的性能非常好，但是混合方法没有纯维度方法速度快。
EVENT WINNER_OFFSET RNR_UP_OFFSET LOSER_OFFSET----- -------------------- -------------------- --------------------1. DIM = 100% HYB = 159%2. DIM = 100% HYB = 190%3. DIM = 100% HYB = 145%4. DIM = 100% HYB = 136%5. HYB = 100% DIM = 497%6. DIM = 100% HYB = 263%7. DIM = 100% HYB = 302%8. DIM = 100% HYB = 159%
查询 5 是一个异常情况，但是对于其余所有的查询，混合模式花费的时间是维度模式所用时间的 136% 到 302%。这直接显示出混合模式的性能存在一些局限性，但可以帮助理解为什么需要查询计划分析。查看系统运行过程中捕获的计划可以发现，其中存在三种行为：
维度计划与混合计划相同的查询（查询 1、3、4、8）。
维度计划与混合计划不同的查询（查询 2、6、7）。
查询与混合模式完美吻合（查询 5）。
计划相同。以下是查询 1 的三个计划：
Query #1, relational schema plan:SELECT STATEMENT (rows=195)SORT GROUP BY (rows=195)TABLE ACCESS FULL PREMIUM (rows=1377304)Query #1, dimensional schema plan:SELECT STATEMENT (rows=300)SORT GROUP BY (rows=300)HASH JOIN (rows=1372568)TABLE ACCESS FULL TIME_DIM (rows=3600)TABLE ACCESS FULL PREMIUM_FACT (rows=1372568)Query #1, hybrid schema plan:SELECT STATEMENT (rows=300)SORT GROUP BY (rows=300)HASH JOIN (rows=1360176)TABLE ACCESS FULL TIME_DIM (rows=3600)TABLE ACCESS FULL PREMIUM (rows=1360176)
注意，不出所料，关系计划与维度计划不同。同时请注意，维度计划与混合计划相同。这表明，优化程序能够检测查询的混合模式中维度结构的维度特性，这是我们需要的行为。维度计划和关系计划相同的模式也体现在查询 3、4 和 8 中。
尽管计划相同，由较慢的性能我们可以得出结论：混合模式较慢的原因仅仅在于其大小。如前所述，混合模式可能需要其余任何一种模式的两倍的空间。这就意味着，与维度模式相比，每块的行数少了，对任何给定操作的读取总次数多了，而且动态字节也多了。这些多出来的动态字节很有可能就是性能变慢的原因。
计划不同。现在来看一下查询 7 的三个计划：
Query #7, relational schema plan:SELECT STATEMENT (rows=6)SORT GROUP BY (rows=6)HASH JOIN (rows=77)TABLE ACCESS FULL COVERAGE (rows=800)TABLE ACCESS FULL PREMIUM (rows=13773)Query #7, dimensional schema plan:SELECT STATEMENT (rows=1)SORT GROUP BY (rows=1)HASH JOIN (rows=1)TABLE ACCESS BY INDEX ROWID COVERAGE_DIM (rows=6)BITMAP CONVERSION TO ROWIDS (rows=)BITMAP AND (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_ACCD_LIMIT (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_DEDUCTIBLE (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_PERS_LIMIT (rows=)TABLE ACCESS BY INDEX ROWID PREMIUM_FACT (rows=48)BITMAP CONVERSION TO ROWIDS (rows=)BITMAP AND (rows=)BITMAP MERGE (rows=)BITMAP KEY ITERATION (rows=)TABLE ACCESS FULL TIME_DIM (rows=12)BITMAP INDEX RANGE SCAN BX_PREMIUM_TIME (rows=)BITMAP MERGE (rows=)BITMAP KEY ITERATION (rows=)TABLE ACCESS BY INDEX ROWID COVERAGE_DIM (rows=6)BITMAP CONVERSION TO ROWIDS (rows=)BITMAP AND (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_ACCD_LIMIT (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_DEDUCTIBLE (rows=)BITMAP INDEX SINGLE VALUE BX_COVERAGE_PERS_LIMIT (rows=)BITMAP INDEX RANGE SCAN BX_PREMIUM_COVERAGE (rows=)Query #7, hybrid schema plan:SELECT STATEMENT (rows=1)TEMP TABLE TRANSFORMATION (rows=)LOAD AS SELECT SYS_TEMP_0FD9D697C_1278CF0 (rows=)TABLE ACCESS BY INDEX ROWID COVERAGE (rows=6)INDEX FULL SCAN UX_COVERAGE_COVERAGE_KEY (rows=6)SORT GROUP BY (rows=1)HASH JOIN (rows=1)TABLE ACCESS FULL SYS_TEMP_0FD9D697C_1278CF0 (rows=6)TABLE ACCESS BY INDEX ROWID PREMIUM (rows=47)BITMAP CONVERSION TO ROWIDS (rows=)BITMAP AND (rows=)BITMAP MERGE (rows=)BITMAP KEY ITERATION (rows=)TABLE ACCESS FULL TIME_DIM (rows=12)BITMAP INDEX RANGE SCAN BX_PREMIUM_TIME (rows=)BITMAP MERGE (rows=)BITMAP KEY ITERATION (rows=)TABLE ACCESS FULL SYS_TEMP_0FD9D697C_1278CF0 (rows=1)BITMAP INDEX RANGE SCAN BX_PREMIUM_COVERAGE (rows=)
关系计划与其余两个计划仍然不同，但是这一次，维度计划与混合计划也不同。这显示出了我们不希望出现的优化程序行为，即由于该模式具有所有必需的结构，因此即使维度计划可以存在也不使用维度计划。在混合模式中不生成维度计划的模式也体现在查询 2 和 6 中。
合理的结论是，在优化程序进行维度查询时，关系结构的可用性导致优化程序生成一种计划，而这种计划并没有在模式中只存在维度结构的情况下生成的纯维度查询有效。
值得注意的是，在混合模式下使用维度计划的四种情况其性能都优于在混合模式下使用除纯维度计划之外的其他计划的三种情况。这更加说明了只要可能就使用维度计划的好处。
完美吻合。查询 5 是混合模式唯一一次获胜的情况，原因在于该查询的特性恰好在人工方面很适合混合模式的特性。具体而言，查询 5 使用了VEHICLE、COVERAGE 和 TIME 表。COVERAGE 是一个弱实体，而且在关系模式和混合模式中，它的主键中同样具有所有VEHICLE 标识符。因为维度模式中 VEHICLE 属性与 COVERAGE 分离，所以 COVERAGE 维度成为一个“纯”维度 —纯的含义是，它是独立的，而且它与 VEHICLE的任何关联都要通过事实表完成。虽然这样有利于形成适当的维度模式，但与关系模式和混合模式相比，在维度模式中，这同时也更明显地隔离了VEHICLE 和 COVERAGE 表。
在查询 5 中，当优化程序需要使用 VEHICLE 和COVERAGE 时，如果在维度模式中，优化程序必须重新将它们集中到一起，但是在混合模式与关系模式模式中，VEHICLE 和 COVERAGE一同包含在 COVERAGE表的键中，优化程序很容易即可找到它们。混合模式在优化程序需要时呈现这种结构是它的一个明确的优势，但是从另一方面来说，该优势并不普遍存在。只有在查询和模式恰好完美吻合时，这种优势才会得以体现，如在查询 5 这种情况下。
查询分析结论
我们得出结论：如果混合模式与查询的特性完美吻合，则混合模式的性能远远胜于维度模式，但是这种情况并不普遍（八例中有一例）。在大多数情况下（八例中有四例），混合计划与维度计划相同，但是可能由于动态字节太多，而使得混合模式运行速度较慢。在某些情况下（八例中有三例），混合模式的计划不同，欠优化，结果是运行时间比维度模式的运行时间长很多。
物化视图聚合
维度建模中通常使用聚合来提高性能，而创建聚合通常使用物化视图。为了演示物化视图聚合 (MVA) 在混合模式中的效果，添加了两个 MVA。表 1 显示，查询中有四个可进行查询重写以使用最右侧一列中给出的 MVA。
查询 Account 维度 Policy 维度 Vehicle 维度 Coverage 维度 Time 维度 使用的聚合
1 Agg Agg     Qry/Agg Agg_acct_pol_time
2 Qry     Qry Qry
3 Qry/Agg Qry/Agg     Qry/Agg Agg_acct_pol_time
4 Qry/Agg Agg     Qry/Agg Agg_acct_pol_time
5     Qry Qry Qry
6 Qry/Agg Qry/Agg Qry/Agg Qry/Agg   Agg_acct_pol_veh_cov
7
Qry Qry
8     Qry   Qry
表 1。添加物化视图聚合以优化特定查询
“Qry”表示该查询在其 WHERE 子句中引用了此维度，“Agg”表示该 MVA 保留了对此维度的引用。如果出现重写的情况，“Qry”不会单独出现在单元格中。
MVA 引起的性能改变总体上正如预料的那样是非常积极的。同时，有利的是 MVA 使得混合模式相对于维度模式表现非常卓越，如下所示：
VENT WINNER_TIME RNR_UP_TIME LOSER_TIME----- -------------------- -------------------- --------------------1. HYB = 00:00:00.941 DIM = 00:00:01.382 REL = 00:00:08.9432. DIM = 00:00:04.246 REL = 00:00:08.041 HYB = 00:00:08.1213. HYB = 00:00:00.942 DIM = 00:00:01.262 REL = 00:00:05.3884. HYB = 00:00:00.120 DIM = 00:00:00.180 REL = 00:00:07.3815. HYB = 00:00:00.290 DIM = 00:00:01.222 REL = 00:00:05.9896. HYB = 00:00:00.731 DIM = 00:00:00.912 REL = 00:00:05.4377. DIM = 00:00:00.691 HYB = 00:00:01.993 REL = 00:00:07.9628. DIM = 00:00:00.511 HYB = 00:00:00.801 REL = 00:00:02.063EVENT WINNER_OFFSET RNR_UP_OFFSET LOSER_OFFSET----- -------------------- -------------------- --------------------1. HYB = 100% DIM = 147% REL = 950%2. DIM = 100% REL = 189% HYB = 191%3. HYB = 100% DIM = 134% REL = 572%4. HYB = 100% DIM = 150% REL = 6151%5. HYB = 100% DIM = 421% REL = 2065%6. HYB = 100% DIM = 125% REL = 744%7. DIM = 100% HYB = 288% REL = 1152%8. DIM = 100% HYB = 157% REL = 404%
事实上，在使用聚合的所有情况（八个查询中的四个）中，混合模式的性能最好。这不但说明，使用物化视图既有利于维度模式又有利于混合模式，而且还说明，使用物化视图对混合模式总是有利的。这是一个非常重大的发现，因为它表明在混合模式上使用 MVA 能够在保持所有相关关系的同时获得所有的维度性能。
深入研究以及其他注意事项
“可以”与“应该”。如本文开篇所述，这项研究的动机是本人需要构建一个既能满足类似 OLTP 的业务需要又能满足类似 DSS 的业务需要的系统。然而，如果一个项目只需要这两种行为中的一种或者具有构建两个相互之间有馈给的独立环境的资金和时间，则最好避免采用混和设计。
人员理解。此项分析着重考虑混合设计的实施方面，而维度设计的一个重要价值是，非数据库专家人员也能很轻松地理解它。混合设计不仅不便于理解，而且其设计是所讨论的三种设计中最复杂的一种。对于要赋予高级用户对数据库的直接访问权限的系统，这是一大障碍。
其他物理优化。当前分析中没有使用分区。虽然添加并分析了位图连接索引，但结果显示这样做取得的优势并不明显（详细信息请参见代码）。应该分析这些以及其他物理优化技术，以便确定这些技术给混合设计带来的益处是否如维度设计一样多。
平台注意事项。该系统在 Windows XP 操作系统上运行，使用的是 Intel Pentium 类硬件。测试了三个此类平台。RAM 的范围是 256 MB到 1 GB。其他平台的运行速度可能或快或慢，但是平台更改也可能改变相关模式的性能。这种可能性更可能使 Oracle数据库软件能够检测到平台资源的状态并相应地调整计划。
结论
一般而言，如果有争议的话，这些数字表明合并关系设计和维度设计的想法是可行的。但是要注意，这并不是完美的解决方案。纯关系设计无法获得维度性能，而纯维度设计也无法有效地表示关系。三种设计都有其自身的局限性。鉴于此，如果好处是既能够获得充分的关系表示又能大大提高性能，那么比较大的行引起的轻微的性能降低与物理复杂性的增加所付出的代价相对要少。
James Madison 从二十世纪九十年代早期便开始从事信息技术，而且大部分时间在研究 Oracle 数据库系统。他非常欢迎大家通过发送电子邮件到madjim@bigfoot.com 向他提出反馈。
 
该参考实施有几十个源文件，在运行过程中生成了九个输出文件。这里只列出了重要的或可能混淆的文件和目录。不太重要的文件没有列出，但是通过跟踪代码和输出应该很容易就能理解。
文件/模式 目的
go.cmd
进入系统的主要入口点。它什么都可以运行。有 13 个指导运行行为的环境变量，请仔细查看。除了一些较小的实用程序外，所有系统代码都与此根相连。
*master*.* 代码使用第四种模式“master”来构建并运行三种主要的模式。
build*.* 这些模式的 DDL。
validation.sql 验证模式构建和数据加载正确完成。
queries.sql 运行查询。
q????.sql 跨三种模型的八个查询。以这种方式提供使其可以跨三种模式调用，以便进行运行和计划分析。queries.sql 文件会进行所有所需的参数化和排序操作。
analysis.sql 执行分析。生成图中显示的运行时间输出表。
agg_*.sql 构建聚合。
bitmapjoin.sql
构建位图连接索引。上面没有讨论，但这里仍然给出该文件。
runs 放置生成文件的目录。
runs\base_*.txt 没有物化视图和位图连接索引的模式的验证、查询输出和分析的三个文件。
runs\mv_*.txt 包含了物化视图的与上面相同的三个文件。
runs\bmji_*.txt 包含了位图连接索引的与上面相同的三个文件。
oracle_config 包含可进行某些系统设置的文件的目录。不可重用。您的系统会发生变化。