ETL工具箱 5提交维表

维度的基础框架

主键是指包含一个无意义的，唯一标识数字的字段，数据仓库拥有这些代理键值但并不把他赋给任何实体。维度的主键主要用于连接事实表。因为所有事实表必须保持表的参照完整性，因此维表中的主键连接的字段就称为事实表的外键。（大多数关系型数据库维表和事实表通过单一字段连接获得最大性能，当外键是数字类型的时候事实表是最为紧凑的）

维表将其他的一个或者多个字段组成维表的自然键（natural key），自然键是源系统抽取的有意义的字段。比如employeeID。

当维表是静态的，不随时间而变化，那么代理键和自然键就是一一对应的关系。后续有缓慢变化的维度，那么每个自然键就有多个代理键，以记录维度信息的历史变化。维度除了主键，自然键，还有描述属性，描述属性一般是文本类型或者数值类型。  数据仓库架构中对于周期性出现的指标量不应该出现在维表中，这些指标量通常出现在事实表中，而非描述属性。描述属性一般是静态或者变化很慢，偶尔发生变化，需要比较明显的参数来判断建模的时候到底是作为事实还是维度属性。

 

生成维度的代理键，通过关系型数据库创建代理键是最普遍的方法，但是使用数据库产生代理键基本上不能保证产生的键值在数据仓库的各个环节保持同步，容易导致问题，一般采用三方etl工具来维护代理键。一种常见的手段是使用源系统的自然键加上时间戳，但也容易出问题，比如定义，性能（查询中varchar比integer慢的多），数据类型不匹配。源系统依赖程度，异构数据源。

 

维度的粒度（grain）抽取的过程可能带来数据的冗余，比如重复的配送号码等，要创建维度模型，必须通过select distinct操作来建立维表。

 

维度的基本加载计划

1真正重要的维度抽取是从一个或者多个数据源开始的。对于比较大的，复杂的维度数据，例如客户，供应商，产品等往往从多个数据源多次抽取的，这需要注意识别跨数据源的相同的维度实体，解决不同描述之间的冲突，以及在不同的时间点的维度更新。2数据清洗包含清洗，解决冲突，应用业务规则以建立数据一致性。简单的维度不行哟呵，大的维度，数据清洗模块就很重要，比如列的有效性校验，跨列的值检验，行的去重复等。3数据的规范化，包含了整理数据仓库不同部分的相同或者相似字段的过程，比如大型企业，计费客户维表和客户支持客户维表数据结构不一致，因此需要规范化的步骤来整理两个客户维表的字段。4最后是管理缓慢变化维。之后写入磁盘。

 

扁平（flat）和雪花（snowflaked）维度

维表是非规范化的扁平表，所有的层系和规范化结构在最后都应该扁平化，维度的实体必须有唯一的值与维表主键对应。

在集中式数据仓库环境中，维度的代理键可能是从单一的数据源生成而来，这种情况下，主要的元数据包含了维表同事使用的最高的键值，但是即便是集中式的数据仓库，如果有足够多的ETL作业同时运行，也可能引起对元数据的读写冲突。因此推荐对每个维表单独建立代理键计数器。

日期与时间维度

纯关键日历日期维度最好的办法就是花上一个下午手工建立一个日期表，十年的数据也不超过4000行。即使主代理键是无意义的整数，我们也建议对日期代理键按照顺序赋值，使日期维表的开始日期键值以0开始，这使得事实表基于日期维表能够按照日期分区。尽管日期维度是最终要的时间维度，我们仍然需要月维度，因为事实表的时间大都是基于月的，月维度应当是一个物理独立的表，从日期维度中选择创建，一般不推荐创建视图来实现月维表，这样的视图会比生成物理表导致更大的查询，同时，这种视图能应用于日期维表，但却不能应用与客户或产品这样的维度。我们不能建立包含秒的时间维表，这样的话数据就到达了3100万记录。但我们又想保留日期维表同事支持对分钟秒的查询。因此我们可以精确的计算一下事实表记录的时间间隔，精确时间的日作为外键关联到我们熟悉的日期维度表中，但在事实表中直接加入了一个时间戳作为查询所需额外的精度。这样设计事实表，而非维表，这样依赖，维表中不使用分秒

 

大维度

数据仓库最有意思的是维度是一个非常大的维度，比如客户产品。真正的大维度总是多个数据源衍生而成，大的企业，比如银行，客户可以来自抵押，信用卡，支票等多个部门，如果想要创建所有部门的客户维表，那么就需要对这些各自独立的客户列表进行剔重（数据清洗的一部分），规范化合并，将不同的数据源下的属性统一起来，形成大的，广泛的维度记录。

小维度

尽管小维度可能出现在很多数据集市中，但是他们不可能对所有的事实表都相同，比如交易类型对每个生产系统是不一样的。有些情况下，多个小维度可以合并成一个大一些的维表，这样就可以减少关联到事实表外键的数量。冗余维的记录可以是在整个数据时候创建，而不是在此之前直接将各个独立代码表合并，这是因为增量建立的冗余维度比笛卡尔积合并代码要小的多。

角色维度

数据仓库架构常会为相同的事实表制定一个维度，我们把它叫做角色维度，最常见的就是日期维度。在角色维度中，我们推荐建立一个统一的维表，然后基于此统一的维表建立不同的角色视图。例如：如果一个订单交易局和快照事实表中我们有一个订单日期，配送日期，支付日期，我们首先建立一个通用的日期维度，然后创建四个对应的视图。建议创建不同的字段名字以避免混淆。

不要将角色维度技术作为创建超大维的借口。比如，在电信行业，几乎每一项记录都有一个地址，如果我们把每一个可能的地址都加入到一个统一的地址维表，那么该维表可能包含数百万记录。在这样大的维表上建立视图对性能无疑是个极大的灾难。这种情况下，为各个子集创建物理的表可能效果要好得多。

其他维度的子维度

通常，我们将事实表中对维度的引用看作是事实表的外建，但是对维度的引用有时候也出现在其他的维度中，那么正确的外建就应该是存储在父维表和事实表中。许多维度都有内嵌的日期，客户维度记录经常就有首次购买日期的属性，这应当是以外建方式对日期维度的引用，而不是日期时间戳。由于日期维有一定的持续期，请注意并非所有的日期都会是对日期维度的引用，像客户这样的属性可能会先于引用日期维度，如果出现了这样的情况，客户生日属性必须是一个sql日期时间戳，而非外建。

 

另一个常见的例子就是客户维度与银行帐号关联，一个帐号有多个所有者，但也会指定一个主所有人，这个主所有人是账户维度对客户维度的外建引用。在银行的例子中，我们没有处理多个客户关联一个帐号的问题，只处理一个主所有人的情况。

 

退化维度

一旦维表模型出现了父子关系，涉及过程比晚面临父维度的自然建问题。比如事实表的力度到达订单一级，事实表的维度包含的全部都是订单本身的信息，订单维度。如果我们关联客户，订单日期以及其他设计的维度，那么就只剩下了最初的订单号，我们把最初的订单号直接插入到事实表中，就像是它的维度主建。我们可以为订单号单建一个维度，但那样一来只是包含订单号而没有别的，基于此原因，我们把这种情况称为退化维度或者空维度。

有一个风险，一个综合性企业中各源系统产生的单据号可能被不同业务单位的ERP实例使用。针对这种情况，在基础订单号或销售单据号加上一个机构ID，生成智能退化键值是一个不错的方法。

 

缓慢变化维度

类型1  缓慢变化维度（覆盖）

第一种类型SCD是对已经存在的维度记录一个或者多个属性的复写。当数据被校正而不需要保存历史记录，或者不需要执行以前的报表，可以选择第一种处理方式。尽管向第一种缓慢变化维度插入新记录需要依赖新的维度建值的生成，类型1的处理变化不会影响维度表建值或者事实表建值，对数据的影响是这三种装载类型中最小的。一些ETL工具包含更新和插入功能。这些功能对于开发者很方便，但大大降低了性能。为了提高性能，在ETL处理中要将更新现有数据的过程和插入新数据的过程区分开，并分别向数据仓库回馈。在第一种类型环境中，可能会不知道引入的数据是由更新而来的还是新插入的。在插入占主导地位时，一些开发者会区分缓慢变化维或快速变化维，他们在缓慢变换维上使用INSERT else UPDATE逻辑，在快速变换维度上使用UPDATE else INSERT逻辑。我们希望这种做法还不是很普遍。

 类型2  缓慢变化维度（分区历史）

 第二类缓慢变化维度是在维度实体中追踪变化，并关联到事实表的标准基本技术。当数据仓库被告知一条现有的维度记录要更改，而不是重写的时候，数据仓库会在变化的时候发布一条新的维度记录。新的维度记录还会分配一个新的代理主建。在此之前，所有的维度的事实表中，代理主见被用作外建。只要在发生改变的时候代理建分配的得当，那么不会有任何一个已有的事实表中的建值需要改变或者更新，已有的聚合事实表也不会需要重新计算。

之所以说第二种缓慢变化维度完美的区分了历史表，是因为维度实体的每个细节版本都正确的连接到事实表记录。比如某个员工的职位改变，但是自然建市不能更改的唯一业务逻辑，而其他的员工属性记录可以发生改变。

如果一个维度的自然键可以变化，从数据仓库的观点上，它不是真正含义上的 自然键。在信用卡处理上，信用卡号有可能会被设为自然键。我们都知道信用卡号码在业 务处理上是可以发生变化的；因此，数据仓库要求使用更加符合基本原理的代理键。在这 个例子里，一直使用最初的客户卡号码作为自然键是可行的，虽然这个客户号码后来有可 能发生变化。在这样的情况下，客户目前的卡号将会是一个单独的字段，而不会被设计成 为一个键值。

第二种缓慢变化维度需要在ETL 环境中有一个良好的变化数据捕获系统。一旦源数据发生变化就要探测到，这样数据仓库就会生成新的维度纪录。

 

对于只有十几个字段，几千条纪录的小维度来说，比如说一个简单产品列表，在图 中所展示的那种变化的监测可以通过强制执行完成。今天所下载所有字段的所有纪录与昨天的所有字段的所有纪录相比较。增加的，变化的，删除的都需要被检测到。但是对于大维度，比如说一千万条加入医疗保险的患者名单，每名患者还有一百个描述字段，那么刚才所提到的那种对比所有纪录的方法效率就显得十分低下。如果是大维度的情况下，可以使用一种特殊编码作为循环冗余码校验。在这种情况下，使用一种被称为CRC 的特殊编码计算并且关联到昨天的每条记录。CRC 的数据类型是长整形，大约20 位，对于每条纪录的信息内容非常敏感。如果记录中有一个字母发生了变化，那么这条纪录的CRC 码会变得与以前完全不同。这样我们捕获变化数据就会十分方便。我们只需把整条记录看作一个单独字符串，计算每条新建纪录的CRC 码，然后对比CRC 码。如果CRC 码相同，我们立即转向下条纪录。如果不同，则停止对比纪录的每个字段，找出发生的变化。通过第10 种因数使用CRC 码技术可以提高变化数据捕获的效率。在本书写作之时，所有主要ETL 产品都可以应用CRC 码计算模型。

 

 第二种缓慢变化维度的精确时间戳

以前都是关于存在记录变化被探测到之后，ETL系统生成新的维度记录，新的维度记录与事实表正确自动关联，这是因为在变化发生之后，新的代理建被迅速的用与所有事实表的装载，没有时间戳来配合相应的工作。我们在维度表中增加几个字段来处理：日立日期外建，行有效时间日期，行结束时间日期，变化理由。

类型3  缓慢变化维度

当纬度记录发生变化但是原先的值仍然保持有效属性的时候，要使用第三种缓慢变化维度。在第三种缓慢变化的维度中，当变化发生的时候，生成新的一列，而不是以行，在新的主值复写之前，原来的值就被放置在这个列中。