银行信用卡中心数据仓库建设.docx

1、银行信用卡中心数据仓库建设银行信用卡中心数据仓库项目建设目录1 数 据 仓库 41.1 数据仓库出现的背景 41.2 数据仓库定义 41.3 数据仓库的特性 41.4 分析人员典型的信息需求 51.5 现有数据库系统处理分析型应用存在的问题 51.6 数据仓库要解决的基本问题 51.7 物理数据库结构设计 61.8 逻辑模型设计 62 可行性分析及需求报告 62.1 可行性分析 62.1.1 项目背景 62.1.2 编写目的 72.1.3 项目环境 72.1.4 技术条件方面的可行性评价 72.1.5 法律方面的可行性 72.1.6 用户使用方面的可行性 72.2 需求分析 82.2.1 以下

2、各表是客户提出的报表业务需求，均需满足 82.2.2 数据描述 93 技术缓冲层： 103 .1 定位 103 .2 特点 103 .3 依据 103.4 具体工作 103.5 创建步骤 104 近源模型层： 114 .1 定位 114 .2 特点 124 .3 依据 124.4 具体工作 124.5 创建步骤 125 整 合 模 型层 135 .1 定位 135 .2 特点 135 .3 依据 145.4 具体工作 145.5 创建步骤 145.5.1 三大主题 145.5.2 逻辑模型设计 155.5.3 物理模型设计 175.5.4 创建实体表 205.5.5 导入数据 215.5.6

3、ETL调度设计 216 共 性 加 工层 226 .1 定位 226 .2 特点 226 .3 依据 226.4 具体工作 227 应 用 集 市层 237 .1 定位 237 .2 特点 237 .3 依据 237.4 具体工作 238 报 表 开发 236.1 卡量分析 246.2 客户量分析 356.3账户分析 459 总结 50附录 1.错误！未定义书签。附 录2 41摘要：数据仓库（DW）技术的提出，建立了一种体系化的数据存储环境，将分析决策所 需要的大量数据从传统的操作型环境中分离出来，使分散、不一致的操作数据转换成集成、 统一的信息。企业内不同单位、不同角色的成员都可以在此单一的

4、环境下查询数据与信息。 借助数据仓库，企业能够从海量信息中探寻数据与数据之间的关系，用于决策支持（DSS）。 数据仓库以传统的数据库技术作为存储数据和管理资源的基本手段，以联机分析处理（OLAP） 技术作为分析数据和提取信息的有效方法，以数据挖掘、人工智能技术作为发现知识和规律 的途径。它是诸多学科相互交叉、综合应用的结果。本文结合XXX银行信用卡中心数据仓库 项目背景介绍了数据仓库的数据模型设计、物理数据库设计、ETL脚本开发、ETL调度设计、 报表开发等流程，基本实现了数据仓库的功能。关键字：数据仓库；数据模型建设；ETL脚本开发；ETL调度设计；报表1 数 据 仓库1.1数据仓库出现的背

5、景在数据库技术的支持下，一大批成熟的业务信息系统投入运行，为企业发展做出了巨大 贡献。各类信息系统大多属于面向事务处理的OLTP系统，经过多年的运行，积累了大量的 数据，而管理决策层对数据分析基础平台的需求却日益强烈。信息化建设的发展趋势是要以客户为中心，以服务求发展，逐渐实现数据集中化、业务 综合化、管理“扁平化”、决策科学化。而在当今市场竞争日益激烈的环境下，创造竞争优 势是当务之急，需要及时、准确地做出科学决策，充分利用现有数据，将它转化为信息。要 优化客户关系，由原有系统以产品为中心的经营管理模式转向以客户为中心，并且强调服务， 尤其是个性化服务。1 .2 数 据 仓 库 定义数 据

6、仓 库 始 于 20 世 纪 80 年 代 中 期 。 由 数 据 仓 库 之 父 Bi l l I nmon 在 1991 年 出 版 的 “BuildingtheData Warehouse”（建立数据仓库）一书中提出了准确而又广泛被大 家接受的定义，数据仓库是一个面向主题的（SubjectOr iented）、集成的（Integr ated）、 相对稳定的（Non-Volatile）、反映历史变化（TimeVariant）的数据集合，用于支持管理 决策和信息的全局共享。Datawar ehousing是构建和使用数据仓库的过程；数据仓库不是一件产品，而是体系 结构设计环境的核心和决策支持

7、系统（DSS）处理的基础。因为数据仓库提供给用户用于决 策支持的当前和历史数据，而这些数据在传统的操作型数据库中很难或不能得到，所以与传 统数据环境相比，在数据仓库环境中DSS分析员的工作将要容易得多。1 .3 数 据 仓 库 的 特性数据仓库的特性主要由以下几点：面向主题的（SubjectOr iented）：数据仓库围绕一些主题，如顾客、供应商、产品和 销售组织。数据仓库关注决策者的数据建模与分析，而不是集中于组织机构的日常操作和事 务处理。因此，数据仓库排除对决策无用的数据，提供特定主题的简明视图。集成的（Integr ated）：通常，构造数据仓库是将多个异种数据源，如关系数据库、一

8、般文件和联机事务处理记录集成在一起。使用数据清理和数据集成技术，确保命名约定、编 码结构和属性度量等指标的一致性。不可更新（Non-Volatile）：数据仓库的数据是有历史保存意义的，数据仓库的数据也 只使用添加的方式(不用时间的数据有时间戳来区分)，进入了数据仓库的数据一般情况下是 不需要更新的，这样就保证了数据的稳定性。通常，它只需要三种数据访问：数据的初始化 装入、数据的添加和数据查询访问。随时间变化的（TimeVar iant）：数据存储从历史的角度（例如过去5-10年）提供信 息。数据仓库中的关键结构，隐式或显式地包含时间元素。1.4分析人员典型的信息需求覆盖企业内部信息、合作伙伴

9、信息和市场信息，覆盖综合信息和明细信息，覆盖当前 数据和历史数据高可用性， 高质量的数据（一致性、完整性） 支持各种不同的分析方法。1.5现有数据库系统处理分析型应用存在的问题数据可信性：两个部门提供的数据是不一样的，让管理者无所适从报表的生产率问题：由于OLTP的单项系统导致数据的分散性和相同元素定义不一致所 致不可能把数据转换成信息数据动态集成问题：不同的需求，要求将操作型环境和分析型环境相分离历史数据问题：单项系统之间保留的历史数据时间范围不一致，无法满足DSS分析的 需要数据的综合问题：非细节数据、多种程度的综合1.6数据仓库要解决的基本问题全局范围内的统一数据视图 数据内容数据的完整

10、性 数据的准确性 数据的一致性 数据组织面向分析决策需要针对多个数据源的数据集成 考虑重要的业务分析问题选择合适的数据源（内部和外部） 数据仓库系统的建设永无止境数据仓库系统的建设是一项工程，同时也是一个过程1.7物理数据库结构设计数据仓库是记录数据在某一时间区间内的状态以及数据在某一时点上的变化的数据存 储方式。数据仓库的特点是面向主题的、集成的、随时间不断变化的、不可更新的。数据仓库的五层架构分别是技术缓冲层、近源模型层、整合模型层、共性加工层、应用 集市层。经过小组共同工作，我们把完成的每一层次一一列举如下。1 .8 逻 辑 模 型 设计数据仓库逻辑数据模型应该具备的特征： 3NF 原则

11、面向主题原则 数据整合原则 历史保留原则 中性原则2可行性分析及需求报告2 .1 可 行 性 分析2.1.1项目背景XXX银行是我们公司的客户，是国内知名的股份制商业银行。在信用卡业务营销方面的 水平处于国内商行的前列。前两天，XXX银行信用卡中心市场部的徐经理打来电话，希望我 们帮忙建设卡中心的数据仓库系统，以便了解信用卡的开卡情况和使用情况，为下一年的营 销目标提供决策支持。客户提出的需求如下：XXX行信用卡中心数据仓库系统的核心功能是提供业务用户对各类数据分析报表的支持。项 目建设至少包括如下部分：1. 数据模型建设通过本项目的实施，完成数据仓库的基础层模型建设。本期项目要求至少完成当事

12、人、协议 和事件三个主题。每个主题中具体的实体数目不限。数据模型要求包括逻辑模型和物理模型。逻辑模型要求使用ERWIN工具，物理模型的工具不 作限制，可以使用ERWIN或者excel 提交。2. 物理数据库设计根据数据仓库的数据架构中不同的区域，设计不同的scheme对数据进行存放，要求对scheme 进行相应的说明。3. ETL 脚 本 开发1）针对仓库临时层的样本数据，开发加载脚本将样本数据加载到PG数据库中。 要求使用PG的加载工具进行加载。2）针对仓库的基础层数据模型，开发转换脚本将数据转换到基础层模型中。要求针对不同的模型表，选择相应的ETL算法。要求至少实现三种ETL算法。 脚 本

13、 要 求 使 用 Pe r l 嵌 S QL 实 现。3）针对报表部分，要求根据报表的加工逻辑开发ETL脚本对数据进行加工，供报表展现使 用。4. ETL调度设计（可选）本项目中没有采用现成的ETL调度工具，因此需要对本项目中ETL调度系统进行设计。具体 包 括：1）调度知识库设计。知识库中包含哪些表，以及相关字段的设计；2）ETL任务定义。包括命名规范及每个具体任务的命名；3）触发依赖定义：针对不同的ETL任务，确认其执行的依赖任务。5. 报表开发按照业务需求，利用Excel工具开发出相关的报表来展示。 2.1.2编写目的通过对银行数据仓库需求分析的分析，我们为该仓库分了三个层次，有技术缓冲

14、层、近 源模型层、整合模型层。针对整合模型层我们又进行了逻辑模型设计、物理模型设计、物理 数据库设计、ETL调度设计（可选）的设计、报表加工口径说明（写成SQL）的编写。编写 概要设计的目的是为了能够和银行工作人员及组内成员进行有效的沟通，使我们的工作顺利 进行下去，设计的产品达到客户的要求。2.1.3项目环境题目：信用卡中心数据仓库系统 甲方：XXX银行开发环境：windows7+Erwin+Postgr eSQL+CMD使用语言及工具：per l、sql、postgres数据库、Erwin数据库建模。 2.1.4技术条件方面的可行性评价Per l语言负责脚本的开发，SQL语句用于数据库相关

15、的操作，Er win用于构建逻辑模型 和物理模型。2.1.5法律方面的可行性系统开发所采用的软件、工具均为xxx行提供，不存在版权问题。 2.1.6用户使用方面的可行性本次改造项目是后台的改造，对于用户前台界面的影响很小，因此，用户在操作方面感 觉仍和以往一样，不需要再次适应。2.2 需求分析需求分析是为了项目能够顺利进行，减少开发成本，降低开发风险，有利于进一步指定 软件开发的细节问题，便于同客户协调工作。2.2.1 以下各表是客户提出的报表业务需求，均需满足1）卡量分析功能概述分析卡量组成结构，提高发卡量 实现方式报表展现 报表维度维度业务定义时间年 -月地区见维度定义分析指标指标业务定义

16、总发卡量所有卡片量，卡表里所有记录不作任何剔除有 效卡同时满足以下条件：1、卡片状态为不1，2的;2、卡片在有 效期内的;3、剔除开卡半年外未激活卡;4、对应账户的锁定 码状态非（R，L，S，D，T类）注意本指标为时点值。当月新增发卡量激活日期在当月的卡片（按2009-08月份为当月）新客户发卡该客户在统计周期（即2009-08月之前）之前从未持有过卡（即持有有效卡的），在统计周期内有符合上述定义的客户 新增发卡量老客户发卡在统计周期之前已持有卡，在统计周期内（即2009-08月内） 有符合上述定义的客户新增发卡量新增过期失效卡在统计周期内失效的卡片当年发卡当年注销统计年初至统计日期新增发卡量

17、，在同期间注销的卡量2）客户量分析功能概述分析客户量中的有效客户量、注销、过期客户量等，以提高客户量。 实现方式报表展现 报表维度时间年 -月地区见维度定义性别见维度定义分析指标指标业务定义总客户量所有的唯一客户记录数有效客户量统计时点客户至少有一个有效卡的当月新增客户量该客户在统计周期之前从未持有过卡的客户，在统计周期之 内拥有至少一张有效卡的当月注销客户量该客户名下最后一个卡在当期内注销的客户量累计注销客户所有卡中，在统计时点已为注销的客户过期客户统计时点客户名下所有卡片已过有效期，必须为非注销客户3）账户分析功能概述分析账户中的账户数、有效账户数、余额等内容。 实现方式报表展现 报表维度

18、维度业务定义时间年 -月地区见维度定义分析指标指标业务定义总账户数所有的账户数量有效账户数所有账户中，销户日期为3000-12-31的，且账户状态不为C当月新增账户数在统计周期内新增的有效账户当月注销账户数在统计周期内销户的账户2.2.2数据描述静态数据：源表数据 数据分析总汇见附录13技术缓冲层：3 .1 定位服务于数据加载和转换的需要，不对外提供数据服务3 .2 特点数据原样加载，与源系统结构一致；有增量，有全量；可能需要保留数天历史(重加/ 查 数 )。3 .3 依据本层将数据源的元数据加载到此存储区域，期间不对数据进行任何转换处理和加工，其 目的在于在目标数据库中最大化的呈现源系统数据

19、的面貌，使得后继错误排查及纠正工作不 会对源业务系统数据库造成影响。此存储区域将保留业务系统所有全量及增量数据。在增量 抽取到缓冲层时增加抽取时间字段，可以标示出每日增量情况。3 .4 具 体 工作根据样本数据对数据进行基础加工，数据类型为varchar，字符长度普遍在20，开发加 载脚本将样本数据加载到pgSQL数据库中，放在技术缓冲层模式下面，这里我们将Public 模式作为技术缓冲层，共建九张表，加载数据时我们使用了Per l 语句进行加载。3 .5 创 建 步骤（1）打开Postgr eSQL数据库，新建一个数据库，名为test db;（2）在testdb数据库下的 public 模式

20、下新建 9张表，分别为 acct_06_30、acct_07_31、 acct_08_31、card_06_30、car d_07_31、car d_08_31、cust_06_30、cust_07_31、cust_08_31。 由于9张表里面有三类表，分别是账户信息汇总表acct、卡信息汇总表car d、客户信息汇 总表cust,在这里我们以6月30号的三张表为例，建立表：1) 建立账户信息汇总表acct_06_302) 建立卡信息汇总表card_06_303) 建立客户信息汇总表cust_06_30（3）向9张表中导入数据，此处我们在Ultr aEdit里面编辑Per l脚本，并在命令控制

21、台执 行，per l 语句如下：#! us r /bi n/pe rl us e s t r i c t;#声明变量，必须用mymy $ db = t e s t db;my $ us r = pos t g r e s ;my $ hos t = l oc a l hos t ; my $ por t = 5432 ;my $ pwd = ;my $ c onne c t _ s t r i ng = ps ql - h $ hos t - d $ db - U $ us r ; close(PSQL);#进入数据库，导入数据，最后退出数据库 open(PSQL,|$connect_str

22、ing);pr i nt PS QLENDOFPSQL;s e t c l i e nt _ e nc odi ng =GBK;c opy a c c t _06 _ 30 f r om E: a c c t - 06 - 30 . t x t wi t h de l i mi t e r |;- - c opy c a r d_ 06 _ 30 f r om E: c a r d_06 _ 30 . t x t wi t h de l i mi t e r |;-类似的，导入哪个数据表就相应的改为那个数据表的表名 ENDOFPSQLclose(PSQL);执行完成之后，在PostgreSQL

23、数据库中的结果截图如下：4近源模型层：4 .1 定位尽量保持源系统数据原貌，提供基于业务数据原貌的访问4 .2 特点简单处理；不考虑整合；保留较短期历史（重点考虑保留策略）4 .3 依据该层存储标准化后的源系统数据。所谓标准化是指对各个业务系统带有源业务系统 个性的数据进行统一的规范化编码，目的是为决策表报数据汇总及展现提供便利。4 .4 具 体 工作对技术缓冲层的原始数据进行标准加工，按统一标准修改数据类型及字符长度，以节约 数据库存储空间，建表后将技术缓冲层的数据导入近源模型层，这里我们创建near sour ce 模式作为近源模型层，共建九张表，加载数据时我们同样使用了Per l 语句进

24、行加载。4 .5 创 建 步骤经过对技术缓冲层的数据进行分析，我们发现数据类型有不一致的情况，比如在技术缓 冲层，大部分是varchar类型，而在近源模型层，我们把一部分数据的数据类型根据从数据 库里面查出来的结果做了修改，比如nume r ic(20,2)，小数点以后保留了两位，从而建立了 近源模型层的数据表，同样也是9张表：卡信息汇总表car d、账户信息汇总表acct、客户 信息汇总表cust 各三张，以6月30号的数据为例，建立表的过程如下：相应SQL语句：（建在testdb数据库中的（near sour ce）模式下） 建 表：1）acct 表建表 2）card表建表 3) cust

25、 表建表向近源模型层的9张表中导入数据，在UltraEdit里面编辑Perl脚本，并在命令控制 台执行，per l 语句如下：#! us r /bi n/pe rl us e s t r i c t;#声明变量，必须用mymy $ db = t e s t db;my $ us r = pos t g r e s ;my $ hos t = l oc a l hos t ; my $ por t = 5432 ;my $ pwd = ;my $ c onne c t _ s t r i ng = ps ql - h $ hos t - d $ db - U $ us r ; close(PSQ

26、L);#进入数据库，导入数据，最后退出数据库 open(PSQL,|$connect_str ing);pr i nt PS QLENDOFPSQL;s e t c l i e nt _ e nc odi ng =GBK;c opy a c c t _06 _ 30 f r om E: a c c t - 06 - 30 . t x t wi t h de l i mi t e r |;- - c opy c a r d_ 06 _ 30 f r om E: c a r d_06 _ 30 . t x t wi t h de l i mi t e r |;-类似的，倒入哪个表就相应的改为那个表

27、的表名 ENDOFPSQLclose(PSQL);执行完成之后，在PostgreSQL数据库中的结果截图如下：5 整 合 模 型层5 .1 定位长期的，细节的，整合的数据存储，为各类业务需求提供支持5 .2 特点面向主题，数据整合；提供规范和共享；中性设计，偏范式化，灵活可扩展；细节信息， 保留长期历史5 .3 依据整合层是实现数据加工最复杂的环节，所有标准化后的源系统数据经过整合、加工、计 算最终按照决策所需分析主题存放在整合层，整合层存储结构以星型模型为主，形成所谓的 数据集市。另外，基于决策支持系统前台用户对数据粒度要求的不同，在整合层储区域建立 多重数据粒度的划分。所以，在ETL过程中

28、在满足决策分析员和公司高层领导等不同类别 用户查询需求的同时，减少了数据仓库数据量，提高查询效率。数据仓库整合层设计方法：面向主题、数据整合、代码规范。数据仓库整合层设计规范：第三范式、稳定、灵活、保存历史。整合层十大主题域：当事人、协议、事件、渠道、当事人资产、产品、内部组织机构、 财务管理、营销活动、区域位置。5.4 具体工作1）对系统源数据的表和字段进行表级分析和字段级分析，生成字数据仓库字段级分析 文 档；2）根据字段分析文档，将所给字段将字段划分到对应的主题表、主题下实体或者代码 表中形成实体表，共建31张表；3）根据形成的实体表创建模型即ERWi n图，之后导出建表语句；4）在数据

29、库中创建实体表，将近源模型层的数据导入整合模型层，这里我们创建 integration模式作为整合模型层，加载数据时使用ETL算法，脚本具体的技术形式使用SQL 语 句。5 .5 创 建 步骤由于在整合层，我们小组根据字段分析、把字段划分为协议、当事人、事件三大主题下， 其中每个主题下又划分了不同数量的实体，不同的实体又划分了不同数量的代码表。5.5.1三大主题协议主题：协议是金融机构与客户之间针对某种特定产品或服务而建立的契约关系。包 括协议的申请、报价、开立等完整信息，例如：银行账户、保险公司保单等。在协议主题下，我们又划分了协议、协议日期历史、协议状态历史、协议额度历史、协 议金额历史、

30、协议和卡信息历史、协议当事人关系历史、协议事件关系历史等实体。其中协 议状态历史下设计了协议状态类型代码表、协议逾期次数类型代码表、协议账户状态类型代 码表三个代码表。协议额度历史下设计了一个协议额度类型代码表。协议金额历史下设计了 协议金额类型代码表、协议账户余额类型代码表、协议免费标志类型代码表3个代码表。协 议日期历史下设计了一个协议日期类型代码表。当事人主题：是指企业作为一个金融机构所服务的任意对象和感兴趣进行分析的各种个 人或团体客户、潜在客户、代理机构、雇员、分行、部门等。一个当事人可以同时是这当中 许多种角色。例如：账户的持有人、账户的共同持有人、借款人、执行者、联合签名者、共同申请人、员工、受益人、特约/联名商户、特约/联名商户、担保人、投资者、咨询顾问、 竞争对手、家庭/成员、交易对手等。在当事人主题下，我们又划分了当事人特征历史、当事人重要信息历史、当事人地址信 息历史、当事人评分级别历史、当事人限额历史、当事人业务存量不同的表实体。其中当事 人地址信息历史表对应设计了一个当事人地