ETL --事实表

每一个事实表通过表的粒度来定义。事实表的粒度是事件度量的定义。我们必须至始至终按照度量如何在 现实世界中理解来规定事实表的粒度。

所有的事实表包含了一组关联到维表的外键，而这些维表提供了事实表度量的上下文。大多数的事实表还 包括了一个或者多个数值型的度量字段，我们称之为事实。

每个事实表应该拥有一个主键，即使仅仅是出于管理的需要也应该在事实表设立主键。如果没有主键完整 性，那么事实表中有可能存在两个或者更多一样的记录，如何区分就不得而知了。

事实表的更新在维表之后。

按粒度将事实表分为三种类型：

1 交易粒度事实表

交易粒度表示的是在特定时间、空间点上的一次瞬间的测量。典型的例子是零售交易。交易粒度记录只 有度量事件发生的时候才被记录。这样，交易粒度事实表既可以为空的也可包含成百亿的记录。

2 周期快照事实表

周期快照事实表表现的是一个时间段，或者规律性的重复。这类表非常适合跟踪长期的过程，例如银行 账户和其他形式的财务报表。最常用的财务上的周期快照事实表通常有一个月粒度。在周期快照事实表中 的数据必须符合该粒度（就是说，他们必须量测的是同一个时间段中的活动）。

在周期快照事实表中的日期维度是周期。所以，对于月度周期快照的日期维度是月，周等一段时间的聚 合的维度。

3 聚合快照事实表

聚合快照事实表用于描述那些有明确开始和结束的过程，例如合同履行，保单受理以及常见的工作流。 聚合快照不适合长期连续的处理，如跟踪银行账户或者描述连续的生产制造过程，如造纸。

聚合快照事实表有几个特殊的属性。最明显的是量的日历日期外键。所有的聚合快照事实表都拥有一组 日期，按照表的标准模式来创建。如图：

• 订单日期
• 请求发货日期
• 实际发货日期
• 交付日期
• 上次付款日期
• 退货日期
• 结算日期

 确保参照完整性

确保事实表的参照完整性，无非就考虑两种情况：

1 加载包含了错误外键的事实表记录；

2 删除了维表记录，而其主键在事实表中被使用。

对于不能保证参照完整性的时候，我们需要在设计的时候参照下图的流程进行：

 其中：

1 在将事实数据加载到最终表之前，仔细的审核和准备数据，在删除任何维表记录之前，仔细地审核

从项目角度来讲，这种方法可行，在数据加载到事实表中最后一步就是查找事实表记录中的自然外键， 然后将他们替换成为维表中的代理键。这个过程的核心是一个特殊的查找表，它包含了每一个外来的自然键所使用的代理键的值。如果这张表被正确的维护，那么事实表中的记录将满足参照完整性。同样在维表 记录被删除的时候也需要尝试联结事实表和维表，只有在查询返回 null 的时候才能够删除该记录。

2 在每次事实表插入操作和每次维表删除操作动作发生时，使用数据库自己的参照完整性检查 简单，但是对于非常大的数据量的时候，肯定会影响性能。

3 加载后对事实表进行扫描检查，以发现和更正有问题的外键，解决参照完整性冲突。 这个方法开销超级大。可以作为第一个方法的补充。

Load 事实表的建议：

索引

      我们知道索引在数据查找的时候能够提供很好的速度的提升，但是在数据加载的时候起到的却是相反作用。如果不能很好的处理，那些使用了大量索引的表将导致你的处理变得非常缓慢。在开始加载数据以前， 在前置任务中需要删除所有的索引，然后在后置任务中重建所有的索引。如果在加载过程中包含数据更新 （Update）操作，需要将那些要执行更新的记录和其他仅仅需要简单插入的记录分开，单独处理。简单的说,

请按照下列的步骤来避免索引导致的 ETL 处理瓶颈：

1. 从要插入的记录中分离那些需要执行更新操作的记录

2. 删除那些更新操作不需要的索引

3. 加载那些需要更新的记录

4. 删除剩余的索引

5. 通过批量加载方式执行数据插入

6. 重建所有的索引。

分区

表分区，可以按照时间进行分区，当分区不够的时候我们需要动态的新增分区。这种分区的优点是显而 易见的，当我们需要查找 10 年中某一个月的数据的时候，我们只需要查找这个月所在的分区，而不需要 全表查找。

回滚日志

我们知道回滚日志在一般的数据库中都是有这个功能的，他有他的好处，当进行误操作或其他的问题的 时候我们直接可以 redo。但是这个功能是否需要用在数据仓库中体现吗？答案是不需要！

原因： 1 所有的数据通过受 ETL 系统管理的进程输入；

             2 数据批量的加载；

            3 如果加载过程失败，数据可以很容易的重新加载。

对于 SQL INSERT 也是一样的，使用 SQL INSERT 语句会导致产生一些数据库日志，这些日志的目 的是错误恢复，但是对于数据仓库环境他们完全是多余的。如果加载程序失败了，ETL 工具能够从错误中 恢复，并从错误点开始处理剩下的部分，而不需要使用数据库的日志。

装载（Load）

1 单独处理数据插入。

很多 ETL 工具（以及一些数据库）提供 update else insert 功能（如下图）。 这个功能非常方便，且有着非常简单的数据流程逻辑，但是性能非常的低。ETL 过程对已经存在的数据的 更新逻辑中包括区分那些已经存储在事实表中记录和新数据。当处理大量的数据的任何时候，你想到的是 数据批量加载到数据仓库。但是不幸的是，很多批量导入工具不支持更改已经存在的记录。通过分离需要 更新的记录，你可以先处理更新，然后再执行批量的导入，这样获得最佳的加载性能。

2 利用批量加载工具。

使用批量加载工具，而非使用 SQL 语句加载大量数据可以降低数据库负载， 并极大的提高加载效率。

3 并行的加载。

在加载大量数据的时候，将数据物理上分成不同的逻辑段。如果加载 5 年的数据，你 可以做 5 个数据文件，每个文件中包含一年的数据。一些 ETL 工具允许你根据数据范围进行数据分区。 一旦数据被分成均等的部分，运行 ETL 过程并行的加载所有的分段。

4 最小化物理更新。

在表中更新记录操作需要耗用 DBMS 很多资源，最大的原因是数据库要生成回 滚日志。要最小化对回滚日志的操作，可以采用批量的加载数据。如何处理那些需要更新的数据呢？很多 情况下，最佳的方式是删除要更新的记录，然后批量的加载所有的数据。由于要做更新的数据和总的数据 量的比率会极大的影响优化方式的选择，因此需要一些反复测试来判断针对具体情况的最终加载策略。

5 在数据库外进行聚合。

在数据库之外进行排序，合并和聚合要比在 DBMS 内使用 SQL 语句，使用 COUNT 和 SUM 函数，GROUP BY 和 ORGER BY 关键字高效的多。ETL 过程需要将巨大数量的数 据进行排序、合并放在进入关系型数据库准备区之前完成。很多 ETL 工具提供这些功能，但是专门的工 具在操作系统级别执行排序/合并意味着为处理大数据集进行额外的投资。

更新和纠正事实记录

1 消除事实

这种方法是创建一个完整的错误的资料库，当发生某种错误的时候查找此资料库，从中找出该种错误事 实的纠正信息。

2 删除事实

有两种方法：

1 从物理直接删除，比较不推荐，当然了能够确保事实的参考完整性约束的话，删除最为简单；

2 在事实表中添加一个 flag 字段，初始化此字段，值为：TRUE，FALSE，TRUE 为不删除，FALSE 为删除。

无事实的事实表

每个事实表的粒度是一个事件量测。在某些情况下，事件可以发生，但是没有具体的测量值。例如一个 事实表用来记录交通事故事件。每个事件的发生是无可质疑的，维度设计是强制性且非常直接的。

聚合：

提高大型数据仓库性能最重要的手段是在基础记录上建立一组适当的聚合记录。聚合可以显著的提高性 能，一些情况下，查询的性能可以提高 100 倍，甚至 1000 倍。没有其他的方法可以获得如此的性能提 升。在 BIEE 中可以看到，当我们在逻辑层中给某个字段的聚合方式加入 count 之后，在展现的时候变能 够通过 count 的字段的其他字段进行分组计数了