数据仓库建模—抽取技巧

源系统提供增量：

方案普遍要求:源表主键

1.ORACLE技术:Mv 物化视图，如果提供增量,对源表有存在主键的要求。

2.ORACLE技术:Cdc，提供改动记录,可提供增删改类型。

3.源系统 提供 主键+时间戳+增删改标志<wbr></wbr>由原系统人工加入过程或触发器,对所有目标需要的表,任何变化做记录,提供目标系统分析。

4.源系统提供 增量(全记录+时间戳+增删改标志)，由原系统人工加入过程或触发器,对所有目标需要的表,任何变化做记录,提供目标系统直接使用。

目标系统 全量对比

目标系统将源系统表的所有内容进行比较,分析得到增删改:

优点:源系统保证表含有主键外,对源系统没有特殊要求。

局限:只适合小数据量,耗费系统资源比较大。<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

两种模型

交易数据

<wbr></wbr>数据的分级对待(是否保留数据更新:历史信息)

<wbr></wbr>交易数据分级对待

<wbr>1)</wbr>重要字段: 对于比较敏感,改动时期望留下痕迹的(字段)信息。

<wbr>2)</wbr>非重要字段: 与重要字段对应,对重要字段保留历史信息,一般重要自动有所改动,保留历史记录. 非重要字段有改动一般不保留。

<wbr></wbr>交易数据保留源系统记录中的属性字段.有条件的,同时生成纬度值,以便于成统计数据.通常保留更改时间挫与数据生效/失效时间。

与交易数据对应的一般还有部分中间表.用以存储更新交易数据的增量信息。

统计数据

Cube的建立(业务需求,纬度数量控制)：

按业务需求确立纬度数量,但需要按各种cube工具限制纬度数量,以免产生效率问题.以orcle为例,通常限制在7~8个纬度。

Cube刷新频率：

按需:日数据每日刷新,月数据按月刷新。

为兼容需要临时刷新的情况,如有条件,为每次刷新得到的数据提供时间戳,至少另存版本保存,以便日后需要。<wbr></wbr>统计数据的时效性(多版本保留与管理)，由于业务上可能要求重新统计,所以有必要按统计时间保留版本。

两类数据的区别

明细与统计，更新频度

索引的区别:

数据仓库中,更多使用bitmap索引。

如果数据量比较大,以分区表的方式保存数据,已便分割管理/加快查询速度。

纬度的建立

缓慢变化纬的优点:

杜绝纬度变化后,模型的大幅度改动。

缓慢变化纬变化时的处理:

更改表中属性值与纬度的映射关系,纬度值只加,不删改。

纬度建立后, 保留原属性的必要性:

回溯源表时,方便查询,建议在交易表中同时存放属性对应的纬度.

属性默认值的确定:

与源系统方面沟通,确立各个属性的默认值,以便处理空,非法值的情况.

表间约束的确定:

与源系统方面沟通,确立表间约束

违反约束及属性取值范围/数值空或非正常数据的获取及提交源系统更正.

索引的建立/数据的分区存储.<wbr></wbr>

抽取

抽取时间<wbr></wbr>

避免数据库资源竞争

通常,数据仓库中,数据库的高负荷时间是夜间,夜间etl/统计数据,白天提供客户查询,如无必要,不要在白天etl,以免使客户感觉到数据不稳定。夜间etl之间不要相互冲突,形成资源竞争。也需与存在该数据库中的其他业务过程高发期错峰。

抽取效率的提高

工具和存储过程的利弊

由于手工编制的存储过程,含有人为因素造成的大概率的错误可能性.普遍,工程上采用工具建立etl模型。

ETL工具的优点:可视化,托拽操作。键盘依赖少(减少输入错误),编程效率高(含有部分智能判断,协助开发)。

ETL工具的缺点:etl代码缺少可读性,不利于甚至不支持人工调优。

ETL工具主要有Datastage、Powercenter、Automation、Kettle等。

数据存储分区的好处/数据分快处理

分块处理,可以减少同时操作的数据量,避免系统瓶颈。

通常,大数据量的ETL耗费大量的系统资源,反而使效率降低。在表关联时,关联事务需要处理的数据量过大。此时可以分块etl,降低系统负荷,减少数据关联结果集。

索引

增加索引,提高查询效率

接口设计导致的效率问题(同时涉及到接口负责度和维护成本)

不同种类的数据库间抽取数据通常方法多种多样,规避没有效率的方法,以便减少ETL整体用时。但是很多方法是基于第三方工具。因此ETL时间开销和项目经费间需要权衡。

调度工具的优势(有效利用服务器空闲资源)

通常,在一连串的ETL环节中,各个环节存在先后顺序,但也有可先可后的环节。各环节的数据操作量/系统占用资源都不同,合理利用资源,在某个占用资源较少的环节中并行加入其他环节是个减少ETL总用时的恰当方法。调度工具就提供了这个途径。

日志的必要性

<wbr></wbr>为所有ETL环节登记日志,优点有：

<wbr> 1.</wbr> 便于查找出错环节

<wbr> 2.</wbr> 便于分析统计ETL用时

<wbr> 3.</wbr> 便于提供必要的证明

各环节先后的次序保证

<wbr></wbr>如果ETL的某些环节有依赖其他环节优先完成的条件,需要严格保证

久远历史数据的库外备份(提高仓库效率)

<wbr></wbr>过于久远的历史数据往往没有使用价值。即便偶尔需要使用也是极少数。我们可以将久远的历史数据从仓库的常用表中迁出,有必要时再放入。这样常用表中数据量减少,有利于加快ETL速度,查询速度,和维护成本。

各类日志日常监控

定期监控各类日志,确保ETL错误的即时处理,检查日渐变慢的ETL环节并将其优化,同时形成各阶段的维护报告。

数据仓库程序测试的特点

无法使用测试库进行测试

由于数据仓库的各个环节正确与否,同时取决于源数据的内容,在不用环境中,数据不同,测试结果不同。所以许保证与正式环境完全相同的环境下测试才有意义。

覆盖测试的必要性

由于各个环节用处99%都不同,所以,抽样测试不能保证整个ETL环节没有错误。