数据仓库建模_抽取技巧于心得

 

源系统提供增量:

方案普遍要求:源表主键

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

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

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

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

目标系统 全量对比

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

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

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

                         

两种模型

交易数据

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

 交易数据分级对待

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

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

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

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

统计数据

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

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

Cube刷新频率：

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

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

两类数据的区别

明细与统计，更新频度

索引的区别:

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

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

纬度的建立

缓慢变化纬的优点:

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

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

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

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

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

属性默认值的确定:

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

表间约束的确定:

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

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

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

 

抽取

  抽取时间 

避免数据库资源竞争

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

  抽取效率的提高

        工具和存储过程的利弊

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

 

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

 

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

               ETL工主要有informatic等

 

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

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

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

        索引

            增加索引,提高查询效率

 

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

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

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

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

 日志的必要性

   为所有ETL环节登记日志,优点有3

      1. 便于查找出错环节

      2. 便于分析统计ETL用时

      3. 便于提供必要的证明

 各环节先后的次序保证

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

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

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

各类日志日常监控

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

数据仓库程序测试的特点

无法使用测试库进行测试

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

覆盖测试的必要性

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