银行信用卡中心数据仓库建设.docx
《银行信用卡中心数据仓库建设.docx》由会员分享，可在线阅读，更多相关《银行信用卡中心数据仓库建设.docx（83页珍藏版）》请在冰豆网上搜索。
银行信用卡中心数据仓库建设
银行信用卡中心
数据仓库项目建设
目录
1数据仓库4
1.1数据仓库出现的背景4
1.2数据仓库定义4
1.3数据仓库的特性4
1.4分析人员典型的信息需求5
1.5现有数据库系统处理分析型应用存在的问题5
1.6数据仓库要解决的基本问题5
1.7物理数据库结构设计6
1.8逻辑模型设计6
2可行性分析及需求报告6
2.1可行性分析6
2.1.1项目背景6
2.1.2编写目的7
2.1.3项目环境7
2.1.4技术条件方面的可行性评价7
2.1.5法律方面的可行性7
2.1.6用户使用方面的可行性7
2.2需求分析8
2.2.1以下各表是客户提出的报表业务需求，均需满足8
2.2.2数据描述9
3技术缓冲层：
10
3.1定位10
3.2特点10
3.3依据10
3.4具体工作10
3.5创建步骤10
4近源模型层：
11
4.1定位11
4.2特点12
4.3依据12
4.4具体工作12
4.5创建步骤12
5整合模型层13
5.1定位13
5.2特点13
5.3依据14
5.4具体工作14
5.5创建步骤14
5.5.1三大主题14
5.5.2逻辑模型设计15
5.5.3物理模型设计17
5.5.4创建实体表20
5.5.5导入数据21
5.5.6ETL调度设计21
6共性加工层22
6.1定位22
6.2特点22
6.3依据22
6.4具体工作22
7应用集市层23
7.1定位23
7.2特点23
7.3依据23
7.4具体工作23
8报表开发23
6.1卡量分析24
6.2客户量分析35
6.3账户分析45
9总结50
附录1.............................................................................................................错误！
未定义书签。
附录241
摘要：
数据仓库（DW）技术的提出，建立了一种体系化的数据存储环境，将分析决策所需要的大量数据从传统的操作型环境中分离出来，使分散、不一致的操作数据转换成集成、统一的信息。
企业内不同单位、不同角色的成员都可以在此单一的环境下查询数据与信息。
借助数据仓库，企业能够从海量信息中探寻数据与数据之间的关系，用于决策支持（DSS）。
数据仓库以传统的数据库技术作为存储数据和管理资源的基本手段，以联机分析处理（OLAP）技术作为分析数据和提取信息的有效方法，以数据挖掘、人工智能技术作为发现知识和规律的途径。
它是诸多学科相互交叉、综合应用的结果。
本文结合XXX银行信用卡中心数据仓库项目背景介绍了数据仓库的数据模型设计、物理数据库设计、ETL脚本开发、ETL调度设计、报表开发等流程，基本实现了数据仓库的功能。
关键字：
数据仓库；数据模型建设；ETL脚本开发；ETL调度设计；报表
1数据仓库
1.1数据仓库出现的背景
在数据库技术的支持下，一大批成熟的业务信息系统投入运行，为企业发展做出了巨大贡献。
各类信息系统大多属于面向事务处理的OLTP系统，经过多年的运行，积累了大量的数据，而管理决策层对数据分析基础平台的需求却日益强烈。
信息化建设的发展趋势是要以客户为中心，以服务求发展，逐渐实现数据集中化、业务综合化、管理“扁平化”、决策科学化。
而在当今市场竞争日益激烈的环境下，创造竞争优势是当务之急，需要及时、准确地做出科学决策，充分利用现有数据，将它转化为信息。
要优化客户关系，由原有系统以产品为中心的经营管理模式转向以客户为中心，并且强调服务，尤其是个性化服务。
1.2数据仓库定义
数据仓库始于20世纪80年代中期。
由数据仓库之父BillInmon在1991年出版的“BuildingtheDataWarehouse”（《建立数据仓库》）一书中提出了准确而又广泛被大家接受的定义，数据仓库是一个面向主题的（SubjectOriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（TimeVariant）的数据集合，用于支持管理决策和信息的全局共享。
Datawarehousing是构建和使用数据仓库的过程；数据仓库不是一件产品，而是体系结构设计环境的核心和决策支持系统（DSS）处理的基础。
因为数据仓库提供给用户用于决策支持的当前和历史数据，而这些数据在传统的操作型数据库中很难或不能得到，所以与传统数据环境相比，在数据仓库环境中DSS分析员的工作将要容易得多。
1.3数据仓库的特性
数据仓库的特性主要由以下几点：
面向主题的（SubjectOriented）：
数据仓库围绕一些主题，如顾客、供应商、产品和销售组织。
数据仓库关注决策者的数据建模与分析，而不是集中于组织机构的日常操作和事务处理。
因此，数据仓库排除对决策无用的数据，提供特定主题的简明视图。
集成的（Integrated）：
通常，构造数据仓库是将多个异种数据源，如关系数据库、一般文件和联机事务处理记录集成在一起。
使用数据清理和数据集成技术，确保命名约定、编码结构和属性度量等指标的一致性。
不可更新（Non-Volatile）：
数据仓库的数据是有历史保存意义的，数据仓库的数据也只使用添加的方式（不用时间的数据有时间戳来区分），进入了数据仓库的数据一般情况下是不需要更新的，这样就保证了数据的稳定性。
通常，它只需要三种数据访问：
数据的初始化装入、数据的添加和数据查询访问。
随时间变化的（TimeVariant）：
数据存储从历史的角度（例如过去5-10年）提供信息。
数据仓库中的关键结构，隐式或显式地包含时间元素。
1.4分析人员典型的信息需求
覆盖企业内部信息、合作伙伴信息和市场信息，覆盖综合信息和明细信息，覆盖当前数据和历史数据高可用性，高质量的数据（一致性、完整性）支持各种不同的分析方法。
1.5现有数据库系统处理分析型应用存在的问题
数据可信性：
两个部门提供的数据是不一样的，让管理者无所适从
报表的生产率问题：
由于OLTP的单项系统导致数据的分散性和相同元素定义不一致所致不可能把数据转换成信息
数据动态集成问题：
不同的需求，要求将操作型环境和分析型环境相分离
历史数据问题：
单项系统之间保留的历史数据时间范围不一致，无法满足DSS分析的需要数据的综合问题：
非细节数据、多种程度的综合
1.6数据仓库要解决的基本问题
全局范围内的统一数据视图数据内容
数据的完整性数据的准确性数据的一致性数据组织
面向分析决策
需要针对多个数据源的数据集成考虑重要的业务分析问题
选择合适的数据源（内部和外部）数据仓库系统的建设永无止境
数据仓库系统的建设是一项工程，同时也是一个过程
1.7物理数据库结构设计
数据仓库是记录数据在某一时间区间内的状态以及数据在某一时点上的变化的数据存储方式。
数据仓库的特点是面向主题的、集成的、随时间不断变化的、不可更新的。
数据仓库的五层架构分别是技术缓冲层、近源模型层、整合模型层、共性加工层、应用集市层。
经过小组共同工作，我们把完成的每一层次一一列举如下。
1.8逻辑模型设计
数据仓库逻辑数据模型应该具备的特征：
3NF原则
面向主题原则数据整合原则历史保留原则中性原则
2可行性分析及需求报告
2.1可行性分析
2.1.1项目背景
XXX银行是我们公司的客户，是国内知名的股份制商业银行。
在信用卡业务营销方面的水平处于国内商行的前列。
前两天，XXX银行信用卡中心市场部的徐经理打来电话，希望我们帮忙建设卡中心的数据仓库系统，以便了解信用卡的开卡情况和使用情况，为下一年的营销目标提供决策支持。
客户提出的需求如下：
XXX行信用卡中心数据仓库系统的核心功能是提供业务用户对各类数据分析报表的支持。
项目建设至少包括如下部分：
1.数据模型建设
通过本项目的实施，完成数据仓库的基础层模型建设。
本期项目要求至少完成当事人、协议和事件三个主题。
每个主题中具体的实体数目不限。
数据模型要求包括逻辑模型和物理模型。
逻辑模型要求使用ERWIN工具，物理模型的工具不作限制，可以使用ERWIN或者excel提交。
2.物理数据库设计
根据数据仓库的数据架构中不同的区域，设计不同的scheme对数据进行存放，要求对scheme进行相应的说明。
3.ETL脚本开发
1）针对仓库临时层的样本数据，开发加载脚本将样本数据加载到PG数据库中。
要求使用PG的加载工具进行加载。
2）针对仓库的基础层数据模型，开发转换脚本将数据转换到基础层模型中。
要求针对不同
的模型表，选择相应的ETL算法。
要求至少实现三种ETL算法。
脚本要求使用Perl嵌SQL实现。
3）针对报表部分，要求根据报表的加工逻辑开发ETL脚本对数据进行加工，供报表展现使用。
4.ETL调度设计（可选）
本项目中没有采用现成的ETL调度工具，因此需要对本项目中ETL调度系统进行设计。
具体包括：
1）调度知识库设计。
知识库中包含哪些表，以及相关字段的设计；
2）ETL任务定义。
包括命名规范及每个具体任务的命名；
3）触发依赖定义：
针对不同的ETL任务，确认其执行的依赖任务。
5.报表开发
按照业务需求，利用Excel工具开发出相关的报表来展示。
2.1.2编写目的
通过对银行数据仓库需求分析的分析，我们为该仓库分了三个层次，有技术缓冲层、近源模型层、整合模型层。
针对整合模型层我们又进行了逻辑模型设计、物理模型设计、物理数据库设计、ETL调度设计（可选）的设计、报表加工口径说明（写成SQL）的编写。
编写概要设计的目的是为了能够和银行工作人员及组内成员进行有效的沟通，使我们的工作顺利进行下去，设计的产品达到客户的要求。
2.1.3项目环境
题目：
信用卡中心数据仓库系统甲方：
XXX银行
开发环境：
windows7+Erwin+PostgreSQL+CMD
使用语言及工具：
perl、sql、postgres数据库、Erwin数据库建模。
2.1.4技术条件方面的可行性评价
Perl语言负责脚本的开发，SQL语句用于数据库相关的操作，Erwin用于构建逻辑模型和物理模型。
2.1.5法律方面的可行性
系统开发所采用的软件、工具均为xxx行提供，不存在版权问题。
2.1.6用户使用方面的可行性
本次改造项目是后台的改造，对于用户前台界面的影响很小，因此，用户在操作方面感觉仍和以往一样，不需要再次适应。
2.2需求分析
需求分析是为了项目能够顺利进行，减少开发成本，降低开发风险，有利于进一步指定软件开发的细节问题，便于同客户协调工作。
2.2.1以下各表是客户提出的报表业务需求，均需满足
1）卡量分析
功能概述
分析卡量组成结构，提高发卡量实现方式
报表展现报表维度
维度
业务定义
时间
年-月
地区
见维度定义
分析指标
指标
业务定义
总发卡量
所有卡片量，卡表里所有记录不作任何剔除
有效卡
同时满足以下条件：
1、卡片状态为不1，2的;2、卡片在有效期内的;3、剔除开卡半年外未激活卡;4、对应账户的锁定码状态非（R，L，S，D，T类）
注意本指标为时点值。
当月新增发卡量
激活日期在当月的卡片（按2009-08月份为当月）
新客户发卡
该客户在统计周期（即2009-08月之前）之前从未持有过卡
（即持有有效卡的），在统计周期内有符合上述定义的客户新增发卡量
老客户发卡
在统计周期之前已持有卡，在统计周期内（即2009-08月内）有符合上述定义的客户新增发卡量
新增过期失效卡
在统计周期内失效的卡片
当年发卡当年注销
统计年初至统计日期新增发卡量，在同期间注销的卡量
2）客户量分析
功能概述
分析客户量中的有效客户量、注销、过期客户量等，以提高客户量。
实现方式
报表展现报表维度
时间
年-月
地区
见维度定义
性别
见维度定义
分析指标
指标
业务定义
总客户量
所有的唯一客户记录数
有效客户量
统计时点客户至少有一个有效卡的
当月新增客户量
该客户在统计周期之前从未持有过卡的客户，在统计周期之内拥有至少一张有效卡的
当月注销客户量
该客户名下最后一个卡在当期内注销的客户量
累计注销客户
所有卡中，在统计时点已为注销的客户
过期客户
统计时点客户名下所有卡片已过有效期，必须为非注销客户
3）账户分析
功能概述
分析账户中的账户数、有效账户数、余额等内容。
实现方式
报表展现报表维度
维度
业务定义
时间
年-月
地区
见维度定义
分析指标
指标
业务定义
总账户数
所有的账户数量
有效账户数
所有账户中，销户日期为3000-12-31的，且账户状态不为C
当月新增账户数
在统计周期内新增的有效账户
当月注销账户数
在统计周期内销户的账户
2.2.2数据描述
静态数据：
源表数据数据分析总汇见附录1
3技术缓冲层：
3.1定位
服务于数据加载和转换的需要，不对外提供数据服务
3.2特点
数据原样加载，与源系统结构一致；有增量，有全量；可能需要保留数天历史（重加/查数）。
3.3依据
本层将数据源的元数据加载到此存储区域，期间不对数据进行任何转换处理和加工，其目的在于在目标数据库中最大化的呈现源系统数据的面貌，使得后继错误排查及纠正工作不会对源业务系统数据库造成影响。
此存储区域将保留业务系统所有全量及增量数据。
在增量抽取到缓冲层时增加抽取时间字段，可以标示出每日增量情况。
3.4具体工作
根据样本数据对数据进行基础加工，数据类型为varchar，字符长度普遍在20，开发加载脚本将样本数据加载到pgSQL数据库中，放在技术缓冲层模式下面，这里我们将Public模式作为技术缓冲层，共建九张表，加载数据时我们使用了Perl语句进行加载。
3.5创建步骤
（1）打开PostgreSQL数据库，新建一个数据库，名为testdb;
（2）在testdb数据库下的public模式下新建9张表，分别为acct_06_30、acct_07_31、acct_08_31、card_06_30、card_07_31、card_08_31、cust_06_30、cust_07_31、cust_08_31。
由于9张表里面有三类表，分别是账户信息汇总表acct、卡信息汇总表card、客户信息汇总表cust,在这里我们以6月30号的三张表为例，建立表：
1）建立账户信息汇总表acct_06_30
2）建立卡信息汇总表card_06_30
3）建立客户信息汇总表cust_06_30
（3）向9张表中导入数据，此处我们在UltraEdit里面编辑Perl脚本，并在命令控制台执行，perl语句如下：
#!
usr/bin/perlusestrict;
#声明变量，必须用my
my$db='testdb';
my$usr='postgres';
my$host='localhost';my$port='5432';
my$pwd='';
my$connect_string="psql-h$host-d$db-U$usr";close（PSQL）;
#进入数据库，导入数据，最后退出数据库open（PSQL,"|$connect_string"）;
printPSQL<setclient_encoding=GBK;
copyacct_06_30from'E:
\\acct-06-30.txt'withdelimiter'|';
--copycard_06_30from'E:
\\card_06_30.txt'withdelimiter'|';
--类似的，导入哪个数据表就相应的改为那个数据表的表名ENDOFPSQL
close（PSQL）;
执行完成之后，在PostgreSQL数据库中的结果截图如下：
4近源模型层：
4.1定位
尽量保持源系统数据原貌，提供基于业务数据原貌的访问
4.2特点
简单处理；不考虑整合；保留较短期历史（重点考虑保留策略）
4.3依据
该层存储"标准化"后的源系统数据。
所谓"标准化"是指对各个业务系统带有源业务系统个性的数据进行统一的规范化编码，目的是为决策表报数据汇总及展现提供便利。
4.4具体工作
对技术缓冲层的原始数据进行标准加工，按统一标准修改数据类型及字符长度，以节约数据库存储空间，建表后将技术缓冲层的数据导入近源模型层，这里我们创建nearsource模式作为近源模型层，共建九张表，加载数据时我们同样使用了Perl语句进行加载。
4.5创建步骤
经过对技术缓冲层的数据进行分析，我们发现数据类型有不一致的情况，比如在技术缓冲层，大部分是varchar类型，而在近源模型层，我们把一部分数据的数据类型根据从数据库里面查出来的结果做了修改，比如numeric（20,2），小数点以后保留了两位，从而建立了近源模型层的数据表，同样也是9张表：
卡信息汇总表card、账户信息汇总表acct、客户信息汇总表cust各三张，以6月30号的数据为例，建立表的过程如下：
相应SQL语句：
（建在testdb数据库中的（nearsource）模式下）建表：
1）acct表建表2）card表建表3）cust表建表
向近源模型层的9张表中导入数据，在UltraEdit里面编辑Perl脚本，并在命令控制台执行，perl语句如下：
#!
usr/bin/perlusestrict;
#声明变量，必须用mymy$db='testdb';
my$usr='postgres';
my$host='localhost';my$port='5432';
my$pwd='';
my$connect_string="psql-h$host-d$db-U$usr";close（PSQL）;
#进入数据库，导入数据，最后退出数据库open（PSQL,"|$connect_string"）;
printPSQL<setclient_encoding=GBK;
copyacct_06_30from'E:
\\acct-06-30.txt'withdelimiter'|';
--copycard_06_30from'E:
\\card_06_30.txt'withdelimiter'|';
--类似的，倒入哪个表就相应的改为那个表的表名ENDOFPSQL
close（PSQL）;
执行完成之后，在PostgreSQL数据库中的结果截图如下：
5整合模型层
5.1定位
长期的，细节的，整合的数据存储，为各类业务需求提供支持
5.2特点
面向主题，数据整合；提供规范和共享；中性设计，偏范式化，灵活可扩展；细节信息，保留长期历史
5.3依据
整合层是实现数据加工最复杂的环节，所有标准化后的源系统数据经过整合、加工、计算最终按照决策所需分析主题存放在整合层，整合层存储结构以星型模型为主，形成所谓的数据集市。
另外，基于决策支持系统前台用户对数据粒度要求的不同，在整合层储区域建立多重数据粒度的划分。
所以，在ETL过程中在满足决策分析员和公司高层领导等不同类别用户查询需求的同时，减少了数据仓库数据量，提高查询效率。
数据仓库整合层设计方法：
面向主题、数据整合、代码规范。
数据仓库整合层设计规范：
第三范式、稳定、灵活、保存历史。
整合层十大主题域：
当事人、协议、事件、渠道、当事人资产、产品、内部组织机构、财务管理、营销活动、区域位置。
5.4具体工作
1）对系统源数据的表和字段进行表级分析和字段级分析，生成字数据仓库字段级分析文档；
2）根据字段分析文档，将所给字段将字段划分到对应的主题表、主题下实体或者代码表中形成实体表，共建31张表；
3）根据形成的实体表创建模型即ERWin图，之后导出建表语句；
4）在数据库中创建实体表，将近源模型层的数据导入整合模型层，这里我们创建integration模式作为整合模型层，加载数据时使用ETL算法，脚本具体的技术形式使用SQL语句。
5.5创建步骤
由于在整合层，我们小组根据字段分析、把字段划分为协议、当事人、事件三大主题下，其中每个主题下又划分了不同数量的实体，不同的实体又划分了不同数量的代码表。
5.5.1三大主题
协议主题：
协议是金融机构与客户之间针对某种特定产品或服务而建立的契约关系。
包括协议的申请、报价、开立等完整信息，例如：
银行账户、保险公司保单等。
在协议主题下，我们又划分了协议、协议日期历史、协议状态历史、协议额度历史、协议金额历史、协议和卡信息历史、协议当事人关系历史、协议事件关系历史等实体。
其中协议状态历史下设计了协议状态类型代码表、协议逾期次数类型代码表、协议账户状态类型代码表三个代码表。
协议额度历史下设计了一个协议额度类型代码表。
协议金额历史下设计了协议金额类型代码表、协议账户余额类型代码表、协议免费标志类型代码表3个代码表。
协议日期历史下设计了一个协议日期类型代码表。
当事人主题：
是指企业作为一个金融机构所服务的任意对象和感兴趣进行分析的各种个人或团体客户、潜在客户、代理机构、雇员、分行、部门等。
一个当事人可以同时是这当中许多种角色。
例如：
账户的持有人、账户的共同持有人、借款人、执行者、联合签名者、共
同申请人、员工、受益人、特约/联名商户、特约/联名商户、担保人、投资者、咨询顾问、竞争对手、家庭/成员、交易对手等。
在当事人主题下，我们又划分了当事人特征历史、当事人重要信息历史、当事人地址信息历史、当事人评分级别历史、当事人限额历史、当事人业务存量不同的表实体。
其中当事人地址信息历史表对应设计了一个当事人地
升级会员 