银行数据仓库系统高层架构工程设计书.doc

银行数据仓库系统高层架构工程设计书1.1 背景 浙商银行是经中国银监会批准设立的全国第十二家股份制商业银行，全称为“浙商银行股份有限公司”，英文全称为“CHINA ZHESHANG BANK CO., LTD.”，英文简称“CZB”。注册资本150073万元，总行设在杭州。2004年8月18日浙商银行正式开业。目前浙商银行有宁波、温州等2家分行，余杭 萧山 宁波鄞州等支行，业务正处于增长期。浙商银行的成立，既是浙江经济实力的综合反映，也是浙江经济发展的客观要求。作为浙商的传人，作为一家以民营资本为主体的新兴股份制商业银行，浙商银行将立足浙江，面向全国，恪守信用，合法经营，以安全为前提，以客户为中心，以发展为主线，以效益为准则，以回报社会、股东和员工为目标，树立规模、安全、效益三者有机统一，全面、协调、可持续的科学发展观，营造特色，培育优势，优质、高效地服务于经济，服务于企业，服务于公众，共同创造社会财富，逐步建设成为经营有方、富有特色、业绩优良、具有一定国际影响的国内一流商业银行。企业数据仓库系统是以业务支撑应用系统的数据以及其他相关数据作为基础数据源，采用科学的数据抽取、整理、存储等方法，建立企业级数据仓库；然后通过丰富的数据分析和挖掘方法找出这些数据内部蕴藏的大量有用信息，对客户、业务、市场、收益、服务、等各方面情况进行科学的分析，从而为市场决策管理者和市场经营工作提供及时、准确、科学的辅助决策依据。目前浙商银行已经有许多业务支撑应用系统，但是各个系统相对独立，信息共享困难，无法从整个企业和单一视图的角度对数据进行深入分析和挖掘，无法为高层管理和决策提供强有力的依据，无法满足快速变化的市场的需求。为了能准确把握市场运行规律和客户需求，以便在激烈竞争中做出正确及时的决策，本工程将建设浙商银行企业数据仓库系统。本工程的建设目的使浙商银行更加充分利用业务支撑应用系统产生的大量宝贵信息资源，对生产经营和业务发展趋势做出科学合理的预测，从而帮助浙商银行及时掌握市场动态，准确地把握市场脉搏，及时制定高效合理的营销策略，更好地适应日趋激烈的市场竞争环境。企业数据仓库系统不但可以为企业制定和调整经营策略提供重要依据，更可成为企业发展的驱动中心，确保浙商银行在日益激烈的市场竞争中确立主导地位。1.2 目的本文件的目的在于简要阐释整个商业智能环境和它的组成部份。这些部份包括硬件，软件和将会被购买或开发的满足需求的知识产权。这份文件提供详细设计和架构的框架，这份文件包括： l 描述目标环境所有技术细节的概要设计和总体结构；l 阐释用于建立系统所购买的厂商设备；l 阐释或叁照在本项目中使用的标准和指导方针;；l 阐释架构和设计原则；l 定义将要用到的术语;l 在它得到认同形成基础后，我们将继续对此文档进行扩充。为了使这份文档成为有效的资源，应该及时更新这份文档。1.3 适用对象这份文件适用对象是本项目团队成员或想对决策分析系统有全局掌握的业务人员、业务分析人员、架构师、部门经理与领导和其它需要使用这份文件了解决策分析商业智能环境提供功能的其他项目组人员。1.4 范围这份文件的重点在于描述所有对浙商银行决策分析环境有用的系统和组成部分。在项目实施过程中，将提供一份关于需要客户化开发的组成部件的详细说明，和现有组成部件的总结性描述。1.5 叁考文档l 浙江商业银行IBM服务工作说明书37概念性体系构架IBM 概念性应用体系架构叙述了将要用于此方案的应用程序及主要组成成分。这个图表提供IBM参考体系架构的概要版本，这是专为非科技业务人员所备的。它使用了许多业务人员所熟知的术语。1.6 数据源数据来源层标识了公司内外所有有效的数据源。1.7 数据仓库本层被分为SSA、SOR和数据集市三部分。这些数据仓库不是操作型数据库的替代品或复制品，而是这些源数据系统的补充，它被改造成一个用于决策和业务管理的数据库。1.8 分析分析层提供了分析软件，支持各种各样的应用，从固定报表到平衡记分卡。为满足特定需要，分析层将由各种各样各种领域最好的软件和工具组成。1.9 交互参考功能从逻辑层到支持具体的环境，我们需要额外的技术流程和交互。这些额外的组成部分使系统拥有动态性和可控性。这些额外因素有硬件，安全保密机制及其实施和系统管理。参考体系架构这是IBM用来描述商业智能环境的主要参考体系架构。它是为科技人员或具有深厚科技背景的商业用户所准备的。为保证每一层易于理解，IBM区分了各层的差异并加入必要的细节，使得此参考体系结构成为概念性体系结构的拓展。技术体系架构 如上图所示，整个数据仓库在技术上大概可以分为5个主要的模块，它们分别是： 1. 源数据和数据接口2. 数据架构3. ETL处理和控制4. 应用架构5. 软硬件架构在接下来的小节中我们将分别对这5个主要的模块进行详细的描述。1.10 源数据和数据接口数据接口架构描述了数据从数据源到到数据仓库过程中所遵循的规范和架构，如下图所示： 1.10.1 数据源数据仓库系统将采用文本文件的方式从源系统获取数据。每个源系统会就与EDW之间就传输数据接口文件（IFF）的格式和方法制定标准，称之为接口规范。每个数据源会首先通过各自的数据导出程序（Extractor）生成接口文件存储在各自的文件缓冲区内。这个Extractor负责各自范围内导出数据的完备性和一致性，包括：1. 依照各自的业务规则确定增量数据的导出方法2. 保证导出文件的格式符合接口规范的要求3. 保证导出文件的传输时间的及时性4. 保证接口文件的数据质量，不错数、不丢数、不多数1.10.1.1 数据源范围在编写本文档的时候，总共有5个系统的数据被确定在范围之内。以下表格概述了将要实施的每一个数据源（用于数据初始加载、数据增量加载或两者均可）的阶段。阶段系统名称数据源类型第1阶段核心业务系统DB2信贷系统DB2资金交易系统SQL Server第2阶段财务系统国际结算系统在选择使用哪个数据源系统来满足数据要求上，数据很可能地来源于一个或多个数据源系统。介于此原因，必须为物理模型的所有数据表选择一个记录系统。当有多个可供选择的数据源时，在选择数据源上必须遵循以下设计原则：1如果有可能就使用最原始的数据来源，不要使用副本或复制本2源数据系统数据应该是完整的。3源数据系统数据应该是最新的。4源数据系统数据应该是高质量的。1.10.2 文件缓冲区文件缓冲区是一块在数据仓库之外的存储区域，它是接口文件的暂存地，可以是在每个源系统上，也可是在独立的接口机上。接口文件先放到文件缓冲区，然后在到数据仓库的接口文件区。此区域不属于数据仓库范围，相应的其存储空间的分配和维护也不属于数据仓库项目范围。1.10.3 接口文件区接口文件区是数据仓库架构内存放接口文件和对接口文件进行处理的地方。数据仓库中的ETL程序负责维护此区域。为了合理的组织接口文件区的内容，我们把接口文件区按如下目录结构组织，假设$IFF_Home代表接口文件区根目录（IFS：Interface File Staging）：目录内容$IFF_Homeinb所有源系统传过来的接口文件$IFF_HomewrkETL过程中的临时文件和ETL文件处理结束后结果文件的存放地$IFF_Homearc备份文件$IFF_HomelogETL处理日志及拒绝文件存放地$IFF_Homeout数据仓库导出文件inb(Inbox)-当前正在处理的接口文件IFF。每个IFF传输到这里后，首先根据文件验证规则进行一致性检查，验证通过后，就表明这个文件是有效的，然后将此文件登记到数据仓库的调度系统内，然后针对这个文件可以进行进一步的处理。wrk(Work)-工作目录。基于文本的ETL程序在此目录内把接口文件转换成可以装载数据仓库的load文本，load成功后，此文件连通原始接口文件可以压缩后放到arc目录下做备份处理。如果ETL过程中发现文件错误，则将错误文件放到log目录下。log-日志目录。ETL运行过程中的日志和发生错误的接口文件都存放在此目录。out-导出目录。需要传给数据仓库以外系统的数据，全部安装目标系统分类放在此目录内。arc-备份目录。存放历史接口文件。接口文件处理完毕后，将按业务类型和日期移动到此目录。然后在此目录完成数据备份。IFF文件以固定的时间间隔由数据仓库从文件缓冲区拉到接口文件区，或者由源数据系统把数据从文件缓冲区推到接口文件区。不管以何种方式，在源数据系统和数据仓库之间都应该有一个握手机制来保证文件传输的完备性，即因为在网络上传输数据文件需要时间，数据仓库系统如何知道文件是否已经传完了。在接口规范上，我们要求源数据系统在传输完每个数据文件后，立刻创建一个与数据文件同名并以“.end”结尾的文件，文件内容为空或包含一些简单的数据文件验证信息，来告诉数据仓库说我这个文件已经传完了。对于inb、wrk、和arc目录，进一步按照数据源和数据类型划分目录：目录内容$Home acms授信系统数据$Home acms daily授信系统日变化数据$Homeacmsmonthly授信系统月变化数据$Homezsrun核心系统数据$Homeopics资金系统数据参考详细目录结构：1.11 数据架构和存储数据架构描述数据在数据仓库系统内如何组织和存储。数据架构的主要模块如下图所示：数据仓库和数据集市都存储在一个DB2数据库内，各个不同的数据在DB2内按不同的schema来区分。数据集市存储在DB2 CubeView内，多维立方体存储在AlphaBlox内。每个应用（1104和绩效考核）有自己独立的数据库。接下来我们分别对每个数据区域做详细介绍。1.11.1 接口文件区接口文件区是存储和处理接口文件的区域，如前面章节所述，接口文件区在Unix系统下按照特定的目录结构组织起来。用Unix的一些系统命令和工具来管理。对每个目录按照其特定的用途设定对不同用户的访问权限，比如谁能读，谁能写，谁能改等。接口文件区数据的处理工具主要是DataStage，附加以Unix脚本和一些自己开发的特定程序。1.11.2 数据仓库1.11.2.1 细节数据暂存区SSA（SOR Staging Area）SSA的主要目的是支持把接口文件的装载到数据库，对其进行验证和处理，然后把数据整合到SOR内。验证的方法主要是将新转载的数据与SOR内已有的数据进行查找和比较。SSA内数据结构的设计原则是最大限度的利用接口文件的数据结构，尽量降低实体的个数，同时很好的支持后续的ETL过程。当然在物理表的设计上一些DB2的特性也要考虑，比如合理的选择表的分区键，以最大限度的发挥DB2的并行特性。SSA里面的表的用途基本都是临时的，每次数据装载都会清空，因此对这些表的处理可以不记日志，以加快处理速度。1.11.2.2 细节数据SOR（System Of Record）SOR是基于BDW开发的一套符合3NF范式规范的表结构。SOR存储了数据仓库内最细节层次的数据，按照不同的主题域进一步分分类组织。此模型是整个数据仓库数据模型的核心，其设计为具有足够的灵活性，以能够应对添加更多的数据源，支持更多分析需求，同时也能够支持BDW进一步升级和更新。为了能够在数据仓库内记录数据的变化以支持历史趋势和变化分析，SOR在一些关键的属性值上会跟踪变化（比如客户的信用度、状态等）。跟踪变化的常见方法就是利用渐变维的Type 2方法来处理记录，在表内增加一条记录变化数据的新记录。同时为了降低不必要的存储空间的浪费（相同数据的重复存储），我们可以把实体中动态变化的属性与静态不变或只需覆盖不需跟踪变化的属性分开。比如对用户，我们可以用一张表存放不变化的用户静态属性，用另一张表存放经常变化的用户行为属性，当跟踪用户行为的变化时我们只需在用户行为表内添加记录就行了，没必要把没有发生变化的用户静态表内的数据也复制一份。SOR显然是整个数据仓库中数据量最大的部分，为了提高此部分数据处理的性能，DB2内一些提供性能的特性必须在这里仔细考虑，比如创建索引，分区键的选择等。但索引同时也会降低数据更新的性能并且占用存储空间，因此索引一定要选择在经常用到的键值或属性上。同时不同索引的类型对性能也会有相当的影响，因此创建索引的时候也需要仔细考虑。数据的分区策略体现在两个方面，一个是存储层面一个是处理层面，处理层面我们在接下来的ETL架构中讨论。就存储层面来说，一是通过分区键数据自然的按照hash算法均匀的分布到DB2数据库的各个分区上，还有就是对于一些数据量特别大的表，我们需要手工的按照数据范围把表分区，比如帐单表之类的，就可以按照帐单时间分成不同的表。手工把表拆开会增加ETL处理过程的复杂性，但相应的也带来处理性能的提高。SOR内另一个性能相关的是代理主键（surrogate key）的生成。代理主键是一个唯一的、单列的、数值类型的属性值，用来代替源数据中自然主键。一方面数值型主键会提高查询、关联和索引的性能，另一方面最关键的是隔离源数据系统数据变化对数据仓库的影响。代理键的生成可以基于DB2内的序列对象，然后此键值在所有引用同一对象的实体内共享。在SOR逻辑模型的物理化时，不同的物理化方法得到的模型对数据库的性能也有较大的影响，为了提高数据装载和访问的性能，保持物理模型的简单性，在物理化SOR模型时我们将权衡使用如下3种方法：1. Lookup合并到父实体2. 子实体合并到父实体3. 父实体合并到子实体1.11.2.3 汇总数据区Summary汇总数据区是为了方便查询和后续多维数据的更新，创建一些常用的中间汇总表，以提高性能和降低后续ETL工作的复杂性。由于SOR是高度规范化的数据，因此要完成一个查询需要大量的关联操作；同时数据集市中的数据粒度往往要比SOR高很多，对要成生数据集市所需数据也需要大量的汇总计算，因此如果我们把常用的数据预先关联和汇总好，并让其尽量多在多个数据集市的计算中共享，就能大幅度的提高整个ETL工作和数据仓库查询的性能。1.11.2.4 反馈数据区（Feedback Area）反馈数据区主要记录的是数据仓库自身生成的结果。比如用户对营销活动的反馈等。数据仓库的特性决定了用户在原则上不能直接修改数据仓库中的数据，因此用户的修改数据和其它生成数据必须单独记录，以便于追踪历史和进行比较。1.11.2.5 元数据存储MDR（Meta Data Repository）元数据存储用来保存关于数据仓库中的过程、数据的信息（日志、数据词典、配置信息等）。由于各个工具和系统都会生成自己的元数据，同时我们还利用元数据管理工具把这些元数据尽可能的集中存储到数据仓库中的MDR内，因此MDR总的来说只是一个共享元数据供用户集中访问的地方，真正元数据的维护地还是在生成这些元数据的系统或工具内。元数据的管理和存储将会用到文件系统、建模工具、数据库等多种途径。在数据仓库内，元数据可以被分成三种类型：业务、技术、和操作型元数据。1. 业务定义（业务元数据）业务元数据在业务层面最终用户感兴趣的元数据，通常包括业务指标的含义、计算规则，数据概念分类，属性的业务含义等。2. 技术规范（技术元数据）技术元数据是指支持数据仓库运行的各种技术定义和规范。它通常是各种配置信息，较少直接被最终用户访问。比如表的定义，ETL Job的配置，调度信息等。3. 操作状态（操作型元数据）操作元数据是指数据仓库运行中各个组成部分的实际状态和日志。它记录了整个数据仓库运行的过程，方便对数据仓库进行跟踪和调试。这类元数据通常存储在用户定义的表内。比如数据仓库的各种统计信息，ETL运行日志等。1.11.3 数据集市和多维立方体1.11.3.1 多维数据存储多维数据存储包含一系列多维数据模型（符合星型模式或雪花模式的关系表）。每个多维数据模型由一个数据表和几个外键表组成，一个称为事实表，英文称为”Fact Table” ，其他的表称为维度表。每个维度表含有单一的主键，这个主键和事实表里一个键相对应。这个类似于星形的结构通常被称作星形连接。一个事实表经常包含一个或多个数字指标，或“事实，英文称为Fact”，定义每个记录的键值组合。在事实表最有用的东西是数字和可以相加的东西。相加是很重要的，因为数据仓库的应用程序不会检索单个的事实表，相反，他们会同时取回上百、上万、甚至上亿条记录，唯一有用的事情是把这些记录相加。通过对比，维度表通常含有描述信息。维度属性被用于在数据仓库查询里大部分有用约束的来源，实际上他们是SQL查询返回结果集的行表头。维表相对事实表来说都很小。这些小表一般都可以Cach在内存内，从而在与大的事实表关联时具有比较好的性能。维表的可以设计为完全反范式化的，这时与一个维相关的所有层次都合并到最底层的维表内，这时的多维模型就体现为一个事实表带很多维表的“星型”结构；同样也可以选择维中不同的层次的数据在各自的表内，这时在结构上体现出来的就是，事实表与很多底层维表关联，然后维中层次结构上的各层维表有与其更高层次维表关联，展现出“雪花”结构。为了保证对DB2内多维数据查询的性能，IBM在DB2内引入的物化查询表MQT（Materialize Query Table）的概念。MQT是基于一个查询（比如关联、汇总等）构造出来的表，表的内容就是这个查询得到的结果集，这个结果集物理的存储在数据库表内。MQT的作用相当于一个DB2查询优化器知道的一个中间表。当用户提交一个对底层细节表的查询时，如果这个查询能等价的转换成对这个MQT中间表的查询，DB2就会自动的从MQT中取得数据返回给用户。由于一般从MQT中取数的代价会比直接从细节数据取数小得多，尤其是对于含有汇总、关联等操作的查询，因此会带来整体性能上的极大提高。MQT在设计中的一个重要考虑是MQT的更新时由系统自动进行还是手工进行。自动更新的好处是降低了MQT维护的复杂性，但由于自动更新MQT的限制比较多，并且对数据仓库应用来说基础表数据的变化完全是可以预知的，因此总的来说，在数据仓库系统内我们一般使用人工维护的MQT。当创建数据集市时，要遵守以下的设计原则：1. 数据将会从企业数据仓库里进入数据集市里，为了具体的分析应用，数据将会被重新转化。2. 用多维建模技术创建数据模型。3. 事实表里的键不能包含空值。4. 数据库将会为查询进行优化。5. 如果有可能，企业数据仓库里的关键值将会被重用。6. ETL的应用程序用来维护参照完整性。7. 这个环境将提供最大的可用性。1.11.3.2 OLAP和多维立方体OLAP即可以是关系型（Relational）OLAP，也即ROLAP，也可以是多维的（Multi-dimensional）OLAP，也即MOLAP。ROLAP通过DB2内的关系表实现，通常具有更高的灵活性和数据存储能力。MOLAP把数据存储在多维数组内，立方体中各个层次的数据都预先计算出来存储在文件内，通常具有更好的性能。在浙商银行数据仓库的总体架构内，ROLAP和MOLAP我们都会用到。其中ROLAP用DB2 CubeView实现，主要是用来建立数据量大结构负责的立方体；MOLAP用AlphaBlox实现，用来建立结构相当简单、数据量也不是太大，但对查询性能和查询功能要求比较多的立方体。具体在实现时我们可以考察每个立方体的数据和访问情况来觉得其用何种方式实现比较好。1. ROLAP利用RDBMS和星型结构，及一系列的表和MQT，DB2来模拟出一个多维的立方体出来。每种维层次的查询组合，DB2都能或实时计算或从MQT中查询得到相应的结果，如果MQT设计的足够多和好，其性能也能于真正的多维立方体想媲美。下图展示的是DB2如何用表和MQT来模拟对立方体的查询的。2. MOLAPMOLAP数据库使用更有效的格式来存储多维数据，通常是多维数组的某种形式，它能把对立方体各种可能的查询全部预先结算出结果存储下来，从而具有更高的查询性能。因为基本上每次查询只是对数据的取出，而没有太多太耗时的计算。当然现今的很多MOLAP引擎也都支持动态计算，也即不是把所有结果全部存储下来，而是保留一部分在查询的时候动态的计算出来，从而降低对存储的消耗。MOLAP数据库的建立一般是一个从下到上的过程。立方体中最底层的数据从DB2数据库中抽取出来装入数据库，然后在各个维的层次上，由底向上逐层开始计算，直到所有的单元的结果都计算出来。计算过程如下图所示：由于MOLAP要安装多维的形式把大部分可能访问到的Cube中的“数据单元”都事先计算并存储下来，因此如果维的个数太多、或维的成员数过多、或维的层次太多都可能导致立方体的体积增大，不仅会极大的增大多维数据库的建立时间，也会占有过多的存储空间。DB2 AlphaBlox的Cube引擎是一个基于内存的OLAP，它会在内存中建立一个多维的数据模型，然后把Cube中的数据存放进去。它的特点是访问很快，但也因一步限制了Cube的大小。因此在具体的实现上，我们会基于这样的原则：访问频繁、需要性能较高、体积较小的Cube放在Alphablox内，大的复杂的Cube直接用CubeView来实现。3. MQT和OLAP对DB2中的ROLAP来说，它就是用MQT来实现的，用户告诉CubeView事实表和维表，然后CubeView会自动的创建相应的MQT来提高ROLAP的访问性能。对MOLAP来说，当向Cube内更新数据时，也可以通过MQT来极大的提高性能。下面这张图说明了MQT在数据仓库的数据访问架构中的作用。OLAP Reports using detail data can use MQTsDue to conformed dimensions and shared facts, a number of the subject areas Star Schemas (and MQTs) will have data that overlaps数据用MQT组织成星型结构，构成ROLAP MOLAP直接从MQT内取得数据来提高性能 Summary report1.11.4 数据仓库应用1104报表和绩效考核在整个数据仓库架构内做为独立的应用存在，它们会有自己独立的存储空间（数据库），其中所有内容由各个应用自己完全控制，对数据仓库内数据的访问通过DB2 II来完成。1.11.5 实现中的一些特别因素1.11.5.1 参照表由于数据仓库中的每个主键都是不同于源数据系统的代理主键，因此在源数据自然主键和DW代理主键之间就应该有一个表来描述二者之间的关系。但这些表又不是DW中对用户有意义的数据，不会出现在SOR的设计中，只是为了ETL的处理而创建和维护的。我们称这些表为参照表。1.11.5.2 MQT的应用DB2是一种基于代价的SQL查询优化系统，SQL编译器会自动分析用户提交的SQL，然后对其进行编译找到一种代价最低的访问路径，然后在计算出查询结果。在DB2里有两个模块对MQT的使用最相关：查询重写模块：此模块分析查询语句，如果可能的话，就将其重写为一种效率更高的形式。DB2的这种能力是用户不必关心SQL写法对性能的影响，只关心程序的逻辑就行了。此模块会检查是否有MQT可以使用来对SQL进行优化，当然这需要检查MQT的状态，并看SQL是否满足MQT的匹配条件。基于代价的优化模块：此模块评估每种访问路径的查询代价，然后选择代价最优的访问路径。1. 状态因素要使用MQT需要考虑下面几个条件：l MQT创建时必须带ENABLE QUERY OPTIMIZATION参数l CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION寄存器必须打开。此寄存器可以设置为：SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION =ALL|NONE|SYSTEM|USERl 对于REFRESH DEFERRED类型的MQT，CURRENT REFRESH AGE寄存器必须设置为ANYSET CURRENT REFRESH AGE ANY|0l REFRESH IMMEDIATE类型的MQT的状态总是最新的，因此不论CURRENT REFRESH AGE是何值都会被引用l 对于动态何静态SQL，QUERY OPTIMIZATION等级必须等于2或大于等于5n 缺省是5，此缺省值可以通过DB2配置参数DFT_QUERYOPT修改n 此寄存器可以通过如下命令修改SET CURRENT QUERY OPTIMIZATION LEVEL 2l MQT不能出现在CHECK PENDING NO ACCESS语句内2. 匹配条件匹配是评估用户的查询语句，判断其是否可以用MQT重写的过程。在所有的状态条件都满足的情况下，查询重新模块会根据下面这些条件来决定是否使用MQT来重写SQL。在大多数情况下，用户提交的SQL语句于创建MQT的查询都不完全一致，这时候DB2需要做一些额外的工作来利用MQT中的数据生成用户需要的结果。这个这个额外的工作就是补偿。允许匹配在下列情况下会使用MQT来进行优化：l 超集谓词且完全匹配这是最简单的情况，用户查询与MQT具有相同的表，相同的表达式，请求的结果完全属于MQT数据集。这里MQT中的谓词必须是用户查询谓词的超集。l 聚合函数和成组列聚合是把相关的记录分组汇总，通常在具有更少的记录数。没必要为每种类型的group定义一个MQT，在特定的情况下，即时用户的group方法与MQT中的不同，DB2也能使用MQT。比如，如果MQT中的GROUP BY的粒度更细，则DB2可以在MQT之上再进行进一步的聚合（也即补偿）。l 多表如果用户查询中存在MQT中没有的表，并且其与基础表的关联谓词能够用MQT与之关联来代替，那DB2也能使用MQTl 少表如果用户查询中的表比MQT中的表少，并且引用完整性存在的话，DB2也能匹配MQTl CASE表达式通常要匹配具有复杂表达式的查询要求这些表达式在MQT中也要以相似的方式使用。通常的情况下，DB2都能识别。比如，如果MQT中的GROUP BY 和SELECT子句都要相应的CASE语句，那么DB2也能匹配具有相同CASE语句的用户查询。抑止匹配下来情况会抑止查询优化器对MQT的使用。l SQL中包含如下内容包含如下内容的SQL不会用MQT优化n MQT的基础部自身是UPDATE的对象。比如，当存在触发器时，查询能从同时是UPDATE的基础表中SELECT数据n 递规或其它复杂语句n 物理属性函数比如NODENUMBERn 外连接n UNIONn XMLAGGn 窗口聚合函数。也即带OVER子句的聚合l 查询中包含MQT中没有的列如果MQT中漏掉了基础表中的一些列，而在用户查询中恰好应用了它们，则不会使用MQTl MQT中的谓词比用户查询更严格如果MQT中缺了一些满足用户查询记录的话，显然不能使用MQTl 查询中的表达式无法从MQT中导出即时MQT中的表达式与查询不同，查询中的表达式也是有可能从MQT中导出的。但对一些特别的情况DB2则无法导出，因此也不能使用MQT1.11.5.3 表和列的命名规则表命名一致和规范的命名规则有助于提高物理模型的可读性和可理解性。下面是数据仓库中建议的命名规范。 SOR. AR_X_RATE_TPThe remaining characters is The first 2 SchemaObject TypeSSA史化_DT._DTAR/DECIMAL工具、数据库工具SORSMYSOR Staging AreaSystem Of RecordSummary模式名：为存储区域名称的缩写每个单词用缩写表示余下部分为实体名称，多个单词之间用下划线 (_) 分割字段命名字段命名的规范与表类似。如下图所示： CST_IDPD_IDMSR_PRD_IDSRO_IDPPN_DTPPN_TMSRC_STM_IDCST_X_PD_SMY以下划线（-）分隔的名称缩写一些统一的缩写：ID: identificationDT: dateTM: time1.11.5.4 代理键的使用基于如下因素考虑，数据源系统中所有的主键和代码在数据仓库中都将转换为新的基于SMALLINT/INT/BIGINT类型的代理键：1. 数据整合：由于数据仓库要对多个数据源系统中的数据进行整合，同一个数据项会在多个数据源系统中出现，并且往往具有不同的代码标识。比如客户号在核心系统和信贷系统中都会有；用户性别在各自的系统中也会有各自的表示方法。为了进行数据整合，这些数值在数据仓库中都会转换为新的数值类型的值来代替，并建立源值到新值之间的对应关系，并通过这个对应关系实现数据整合。2. 性能：由于在数据库的运算过程中，数值类型（SMALLINT/INT/BIGINT）的运算速度要比CHAR/VARCHAR/DECIMAL等数据类型要快的多，而源系统中的键值往往是后者，因此这些键值也会用代理键来代替。1.11.5.5 历史数据的处理依据数据变化的类型，可以将数据仓库内的数据分成如下几类：事务处理型数据：此类数据一旦进入数据仓库就不在变化，对数据不存在Update操作，只有新数据的Insert和历史数据的Delete。比如所有的账户变动记录、资金交易记录等，都属于此类数据。对此类数据历史的处理很简单，只需在数据仓库内保存一定时间窗口的数据（比如3年），对超出此时间窗口的数据导出备份后删除即可。快照数据：此类数据在数据仓库内随时在发生变化，同一个数据项在不停的做Update，但从业务的角度跟踪此数据的每一次变化意义并不大或在数据量上也不可接受，但又要在一定程度上跟踪数据变化的历史。这时我们可以按照一定的时间间隔，定期对数据做快照，只保存做快照时数据的值，而丢弃2次快照之间的变化历史。比如各类账户余额数据，用户的一些行为特征数据（用户总资产，用户信用度等），都可以按照这种方式来处理。信息资料数据：比如各种客户资料、产品资料等，此类数据是数据仓库内最重要的数据，是分析的基础，基本上此类数据的每次变化的历史都要完整的保存下来。对于此类数据我们对每条记录都会额外的加上生效日期/失效日期（EFF_DT/END_DT）。当一条记录中的字段需要更新时，会先更新此条记录的END_DT，然后插入一条新的记录，并设置此记录的EFF_DT。静态代码数据：此类数据主要在数据仓库初始化时设置，然后在整个数据仓库生命周期内则基本不再发生变化，比如各种编码类型的对照（性别编码、教育程度编码等）。对此类数据不需处理历史。1.12 ETL处理架构 ETL架构描述了数据从接口文件到SOR再到多维数据和用户展现的整个流程和框架。浙商银行的ETL架构如下图所示：ETL在架构上可以划分为4个组成部分，ETL调度、ETL监控、数据质量、和ETL任务。他们以元数据为中心进行交互和协作。其中各部分的功能如下：1. ETL调度：控制所有ETL任务的运行。2. ETL监控：跟踪监控ETL任务的运行。3. 数据质量：跟踪数据仓库的数据质量。4. ETL任务：完成具体的数据ETL工作。其中ETL任务按照数据处理的阶段的不同，可以继续归为如下6类：1. ETL0：完成数据的抽取、接口文件的生成，和把数据传输到DW的接口文件区。2. ETL1：对接口文件进行处理，生成DB2装载映像，然后把数据装载到数据库。3. ETL2：SOR数据的生成。转换数据模型，从类接口文件结构到SOR结构，并把数据更新到SOR数据区。4. ETL3：对SOR数据进行汇总，生成各类汇总中间表。5. ETL4：用SOR和汇总区内的数据生成星型结构数据集市 6. ETL5：利用数据集市中的数据计算展示所需的报表和KPI数据。1.12.1 ETL调度ETL调度模块负责所有ETL数据处理任务的调度及顺序逻辑控制，在功能上具有如下功能：1. 任务触发当Job的启动条件满足用户预先设定的条件时，自动加载Job并运行。Job的触发条件包括时间条件和任务条件两种，ETL调度模块应同时具有这两种任务触发机制：a) 时间触发指定Job在特定的时间点开始运行。比如每天的凌晨1点；或每月的5号的凌晨1点。b) 事件触发发生特定的事件后Job自动运行。比如接口处理Job要在其依赖的接口文件全部到达后自动启动；或一个Job其所有前置Job成功运行完成后自动启动。c) 时间和事件的结合多个条件组合都满足后自动运行Job。比如要求月接口文件处理Job在其所有接口文件全部到达并且时间在每月的5号的下午6点之后运行。2. 任务排序对ETL Job运行的先后顺序进行排序，任务排序体现在如下2个方面：a) 依赖关系任务之间具有逻辑上绝对的先后关系，一个任务的启动必须依赖于其前置任务的成功完成。对其前置任务主要考虑与的关系，也即只有当其所有前置任务完成后才启动任务。b) 优先级如果两个或多个任务同时满足启动条件，任务的执行先后顺序可以通过任务的优先级来决定，具有较高优先级的Job将优先运行。3. 全局任务同步对于运行在不同机器和系统上的ETL任务，调度模块可以对其进行同步。比如机器1上的任务1完成后触发机器2上的任务2。4. 并行执行同时允许多个任务并行执行。可以设置各类Job的并行度。并行度有如下2个层次：a) 不同机器任务之间的并行比如要求不同机器上的任务如果没有相关性的话，其运行应该相互不影响。机器1上的任务1和机器2上的任务2可以同时运行。b) 同一机器内部任务的并行对于运行在同一个机器上的任务，如果没有相关性的话可以并行运行。比如数据集市的两个Cube装载任务，没有相关性，并且优先级相同，可以设置为两个任务同时启动。5. 任务管理允许用户灵活的添加新的任务、设置任务的触发条件、和依赖关系。能方便的重置任务状态，重启和结束任务运行等。1.12.2 ETL监控数据仓库系统的维护中最重要的就是随时监控ETL任务的运行情况，因此为了降低系统维护人员的劳动强度，提供友好的ETL监控功能是必要的。在功能上，ETL监控应提供如下功能：l 全程全方位监控n 监控各个环节的ETL任务n 统一监控所有机器所有类型的ETL任务l 多种手段n 任务运行失败n 任务运行结束时间延迟l 多途径自动报价n 浏览界面n 短信通知n 邮件通知1.12.3 数据质量控制数据质量是数据仓库的生命，要保证数据仓库的可用性必须保证数仓库内的数据质量。由于数据仓库内的数据时随着时间的推移、ETL任务的运行动态变化的，因此数据仓库的数据质量也体现在静态和动态两个方面。静态的数据质量是指ETL逻辑和程序的正确性。比如指标的计算规则、数据的清理规则、数据的汇总方法、ETL程序的实现，都必须是正确的。动态的数据质量是指随着ETL程序周而复始的运行，数据仓库内的数据质量必须随时保持可度量和可接受。可度量是指必须能够有定量化的指标随时对当前状态下的数据仓库的数据质量做出评估。可接受是指数据仓库内数据的准确性必须随时保持在可接受的范围内。这就要求在ETL的实现中，不仅在开发阶段要通过完善的数据质量测试来测试计算规则、程序逻辑等来保证数据仓库的静态质量。同时必须建成数据仓库质量保证机制来使得数据仓库内的数据质量成为可跟踪的和可接受的。1.12.4 ETL任务1.12.4.1 ETL0数据抽取此任务原则上由源数据系统厂商负责，生成符合接口规范要求的接口文件，然后把接口文件传输到数据仓库的接口文件区内，供数据仓库进一步处理和使用。1.12.4.2 ETL1处理接口文件对接口文件的处理可以进一步分成2个阶段的工作，文本处理和DB2 Load。文本处理其主要任务包括验证接口文件的完整性，和进行初始的的数据转换工作，具体内容可以参照前面源数据和数据接口部分。我们会通过元数据来跟踪文件的状态，把文件标识和处理任务的标识及处理的结果状态随时登记到数据仓库内。如果文件处理失败，拒绝文件和错误信息都会储存在Reject目录下，然后在数据仓库内记录错误日志，根据相应的触发条件向管理员发送短信、Email等警告信息。问题定位和解决后，把更新后的文件再次传输到Inbox目录下，并重置错误任务的状态，这样此接口文件就可以重新加载到数据仓库内。Load文本处理完成后生成DB2 Load映像，这时候就可以把数据加载到数据仓库内。Load操作同时也要给每条装载的记录添加一条Load任务标识，以方便对表内数据的跟踪。对数据的加载可以直接使用DataStage DB2 Loader，也可以通过命令行调用DB2 Load命令。第一种方式所有操作全在DataStage内进行，可以灵活的处理负责的字段映射情况，比如目标表与装载文件不完全匹配的情况下。第二种方式会调用DB2自己的并行装载工具，在此Load工具内可以调整内存使用、CPU/磁盘并行度等性能参数，以获得最大性能。尤其装载大批量的数据时，建议使用此种数据装载方法以节省时间。装载时如果有错误发生，DB2 Load会生成错误信息，同时在文件和表内记录不能装载或删除的记录。如果问题严重到需要拒绝整个装载文件，我们会在数据仓库内标识此次装载失败，然后操作回滚。数据成功装载到数据仓库后，源数据接口文件和装载映像都转移到备份目录下，视需求情况进行压缩备份。1.12.4.3 ETL2生成SOR由于SOR的数据模型与接口文件的数据模型是完全不同的，在ETL2阶段就是进行这个数据模型的转换，流程如下图所示：在SOR和IFF差异比较大的情况下，我们会把SSA进一步分成SSA1和SSA2两个阶段，其中SSA1结构接近IFF，SSA2结构接近SOR，来简化ETL程序的编写复杂度。如果IFF和SOR差异不大，则可以不经过SSA2阶段。验证阶段数据装载到SSA后，进行的第一步是数据验证工作，基于一些特定的业务规则进行数据有效性和引用完整性的验证，然后为每个自然主键添加一个代理键（SK）。代理键一般通过SOR内的参照表获得，在某些情况下，如果一个自然主键在参照表内没有定义，则利用代理键延展机制创建一个新的代理键。此阶段的ETL处理应以批量的SQL操作为主，尽量降低cursor的使用。Cursor会极大降低DB2的并行处理能力，影响数据处理性能。数据操作更多应该以直接的insert select join为主。代理键延展机制是为了保证数据完整性，以防止数据丢失的机制。此机制的前提是假定接口文件中的数据是正确的，即时其引用的数据目前在DW内还不存在。这时缺失的参照数据将先于实际数据创建（利用接口文件中引用的自然主键），数据中一些必须的非空字段先用特定的假值来代替，来标识此记录是通过代理键延展机制生成，待此自然主键对应的接口数据到达后，再更新SOR中的对应的数据。比如客户表中会引用客户经理信息，如果在客户经理A的数据还没有装到SOR内的客户经理表中，但引用的客户经理A（自然主键AAA）的客户1已经在正在处理的客户接口文件中出现，这时候可以在SOR的客户经理表中先用自然主键AAA生成一条客户经理记录及其代理键，然后用此代理键参照客户表中的客户1。等包含客户经理A的接口数据到达后，在对SOR中的客户经理表做相应的更新。我们可以看到代理键延展机制一定要求接口文件中的数据一定要是引用完整的才行，也即如果表1应用了表2的个数据，那这个数据一定要在表2中出现。代理键延展机制只是一种保证数据完整性的机制，由于其会增大数据数量复杂度，降低系统性能，在大多的时候我们应该避免使用：在数据处理的先后顺序上保证表A依赖的数据要先于表A装载和处理；对于无法满足引用完整性的记录简单的拒绝它。如果选择拒绝不满足引用完整性的记录，我们可以选择拒绝整个接口文件，对数据的修正在源数据接口文件层次上完成；也可以选择保留符合引用完整性的记录，然后把不符合的记录存储在另外的表内，后续的修正直接在SSA的表内进行。更新阶段数据经过验证后SSA内的数据可以更新到SOR的主表内。对于不同的数据可以是update，也可以是insert。当然在此阶段也可能涉及一些数据转换工作，比如通过Join关联不同的表，或生成一些新的指标等。对于非事务类型的数据来说数据的更新根据不同的情况可能有些新的记录要insert，对于已存在的记录要update。这个时候建议使用DB2的UpSert功能来提高效率。数据成功更新到SOR之后，SSA中的数据就可以清空删除了。1.12.4.4 ETL3数据汇总ETL3数据汇总主要是把SOR内3NF最细节层次的数据转换成支持分析需求的星型结构数据，同时为了提高生成星型结构数据的性能，我们会把数据先进行一次较低数据粒度的汇总。由于ETL3主要是大批量数据的join、group by、sum、insert操作，因此此部分工作也主要是通过DB2的SQL语句完成。对汇总表我们可以将其建成手工更新数据的MQT，使得DB2也能对不直接引用中间汇总表的查询进行优化。具体MQT的使用可以参照前面数据架构和存储中实现中一些特别因素部分。1.12.4.5