数据仓库分析系统整体设计方案.doc

目 录
一、概述2
二、四科室需求3
1、风险科需求3
2、市场科需求13
3、业务管理科需求14
4、计划资金科需求15
三、需求分析23
1、维表23
2、事实表23
3、事务-—业务处理过程及业务术语23
4、主键24
5、外键24
四、系统结构图及业务数据流图25
1、系统结构图25
2、数据流图26
五、源数据表结构27
1、BCS系统27
2、Cardpool系统34
3、NAS系统36
4、BCS系统报表37
六、生成表结构39
七、码表结构43
八、结果表结构50
九、数据表创建方法51
1、BCS系统51
2、Cardpool系统57
3、NAS系统58
4、生成表58
5、码表62
十、数据处理过程68
1、目录结构68
2、流程说明68
十一、问题及处理方法80
一、概述
Bill Inmon（数据仓库之父）在Building the Data Warehouse （John Wiley & Sons Inc.， 1996)书中把数据仓库描述为一个“面向主题的、完整的、非易失的、不同时间的、用于支持决策管理的数据集合”。
数据仓库是只用于制作报表的数据库。
对我们而言，数据仓库是某个“宽广"的数据仓储。它包括许多的主题领域。而一个数据集市，恰恰相反,它把眼睛盯在商业活动的某个非常有限的部分上。它往往涉及某个单独主题或单个类型的分析。
在日常工作中，IT人员经常听到这样的抱怨：“我要求的报表怎么还没出来?"或者是“我要对XX报表做些修改，怎么还没结果?”等等.
在IT飞速发展的最近几年里,银行信用卡部先后针对业务上了一些计算机系统。这些系统的特点是：信息量规模小、数据经常实时更新、适用于业务人员快速录入数据、使用模式相对来说是可以预测的、模式很复杂、业务流程难以更改、数据在线保存的时间较短及各系统之间缺乏必要的联系等。这样的系统被称之为OLTP系统.OLTP系统的这些特点也就决定了有如此抱怨。
如何解决这些问题呢?我们首先想到的是：把数据集中、完整地存储在中心数据库中.所有的业务处理在中心数据库上进行。所有的报表工作脱离数据库。这听起来难道不是有点像一个数据仓库吗?我们为什么不在OLTP的业务系统数据库的基础上生成报表呢？答案很简单：因为报表经常需要大量的、长时间的数据做依据，然后经过大量的运算，才能得出你想要的结论.这对业务系统的正常运转影响很大，以至于业务系统无法正常运转.
当然，不是什么时候都需要一个数据仓库的。正如数据仓库的定义：是用于支持决策管理的数据集合。
中国银行北京分行从1986年6月1日发行第一张人民币长城卡到现在拥有将近20万的持卡人.从过去手工处理业务到现在拥有几个OLTP业务系统.信用卡业务有了飞速的发展。但也应看到信用卡市场的激烈竞争。如何给决策者及时提供决策支持信息，是在激烈的市场竞争中立于不败之地的关键。
二、四科室需求
1、风险科需求
表1．北京市分行/（XX支行）信用卡透支利息统计表
年 月 日

金卡
普通卡
比例
合计
自年初累计
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
应收透支利息

本月实收透支利息

透支30天内应收息

透支30天内本月实收息

透支30—60天内应收息

透支30—60天内本月实收息

透支60-90天内应收息

透支60—90天内本月实收息

透支90—180天内应收息

透支90—180天内本月实收息

透支180—360天内应收息

透支180—360天内本月实收息

透支360—720天内应收息

透支360—720天内本月实收息

透支720天以上应收息

透支720天以上本月实收息

注：第一列的时间段缺省设置是表中值，但应允许人为修改各时间段的取值及范围。
表2．北京市分行/（ XX支行）信用卡透支还款分析表
年 月 日

金卡
普通卡
比例
合计
自年初累计
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
全辖透支数额

本月实收透支额

30天内透支额

本月30天内透支户还款数

30-60天内透支额

本月30—60天内透支户还款数

60-90天内透支额

本月60—90天内透支户还款数

90—180天内透支额

本月90—180天内透支户还款数

180—360天内透支额

本月180—360天内透支户还款数

360-720天内透支额

本月360—720天内透支户还款数

720天以上透支额

本月720天以上透支户还款数

注：上月付100，本月付80,虽还欠款，但算还款户.还款数额包括结息数额,由结息带来的实际还款也算还款户.
第一列的时间段应允许人为调整，时间的累计是以某一天开始，倒推统计数据.
表3． XXXX年第X季度关于信用卡透支情况的报告
(每季一份）
总行：
根据人总行银管［1997］185号文件，关于调整银行卡报表制度的通知精神，现统计数字如下：
六个月以上透支余额

二年以上透支余额

已确认的损失金额

已处理的损失金额

其中呆帐核销

其中保险赔偿

其他情况

注：第一、二项可由机器系统提供，其它项需手工填入。
表4．中国银行人民币长城卡业务统计表
风险统计表(月报）
填报单位:（0142)北京市分行 表号:CCTAB5
填报时间：0000年00月00日 0000年00月 金额单位：人民币、元

（需取二个月的数据做统计)
金卡
普通卡
合计
机
器
可
出
数
据
善意透支
(60天以内普通5000以下金卡2万以下)
户数
收回

增加

转出

金额
收回

增加

转出

恶意透支
户数
收回

转入

金额
收回

转入

一年以上恶意透支
户数
收回

转入

金额
收回

转入

一年以上恶意透支收回
户数

金额

机
器
不
可
出
数
据
内部作案
笔数

金额

内外勾结作案
笔数

金额

遗失卡、被盗卡的冒用
笔数

金额

伪造信用卡
笔数

金额

涂改信用卡
笔数

金额

假身份办卡
笔数

金额

持卡人丧失偿还能力
笔数

金额

本月公司卡止付数：（机器可出此数据）本月个人卡止付数：（机器可出此数据）
注：户数收回是指已全部还清的；户数增加是指新增的透支户;“金额增加”是指以往透支户新增透支额和新增透支户全部透支额之和；转出户数是指到本月累计数超出60天的或超过普通卡、金卡限额的透支户;转出金额是转出户数金额之和;转出应与邻近项的转入相对应。
表5．分行及各支行透支状况（全部按户数/金额统计)
(按各发卡单位统计） 年 月 日
分支行名称
透支金额
上月透支
增长率
上月还款（包括还清）
自年初累计还款
本月新增
透支
自年初累计增加
恶意透支
180天以上恶意透支占总透支比例
当前有效卡数量
交易数量
息存数量
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额
户数
金额

海淀

朝阳

西城

崇文

宣武

机场办

昌平

顺义

通县

怀柔

平谷

密云

大兴

房山

门头沟

石景山

丰台

延庆

东城

前门支行

原总行

天马

天马（吉亚)

京分

合计

注：此表中的恶意透支是指超出180天以上的透支户
概念:普通卡透支5000以上;金卡透支20000以上，透支天数超过60天.
公式：（本月–上月）/上月
例：本月透支150万；上月透支100万
（150-100)/100=50％
表6．信用卡风险资产分类汇总表
填表单位（公章）: 填表日期： 年 月 日 单位：千元
分类结果
卡类
笔 数
(1)
（所有透支户）
金 额
（2）
（所有透支金额）
正 常
（3)
关 注
（4）
不良资产
应计
利息(10)
笔数
金额
笔数
金额
笔数(5）
小计金额（6）
次级
(7）
可疑
（8）
损失
（9）
笔数
金额
笔数
金额
笔数
金额
单 位 卡

个 人 卡

合 计

制表人： 审核人： 负责人：
注：（1）为五类风险资产的总笔数.
（2)为五类风险资产的总金额,即（2)=(3）+(4）+（7）+（8)+（9）。
(5)为（7）、（8)、（9）、的笔数合计。
(6）为(7）、（8）、(9）、的金额合计。
（10)为透支日至分类日应计利息。
正常：金卡2万以内、普通卡5000以内，透支天数不足60天，均属正常范围。
关注：透支额在善意透支额以内的，但透支天数在60～90天以内的，属关注范围。
不良资产：无论金额多少,只要天数超出90天的均属不良资产范畴.
次级：是指透支天数在90~180天以内的不良资产。
可疑：是指透支天数在180～360天以内的不良资产.
损失:是指透支天数超出360天以上的不良资产。
表7．长城人民币信用卡持卡人消费/取现/存款/转帐状况统计表（月报)/(季报）
年 月 日
月消费5000～6000元
月消费6000~10000元
月消费10000元以上
卡 号
金额
笔数
平均
卡 号
金额
笔数
平均
卡 号
金额
笔数
平均

合计：

注:卡号栏需按类别分别统计，如个人卡、单位卡、普通卡、金卡等,金额范围应允许人为调整.
卡号按升序排列
金额按降序排列
同类卡按金额降序排列
表8．长城人民币信用卡持卡人消费状况统计表（年报）
年 月 日
消费6万元~7。2 万元
消费7。2万元～12 万元
消费12 万元以上
卡 号
金额
笔数
平均
卡 号
金额
笔数
平均
卡 号
金额
笔数
平均

合计：

表9． NAS系统授权业务量统计（月报)
（来源于NAS数据、自动授权语音系统，仅对长城卡进行统计）
成功交易
本行卡
笔数
外地卡笔数
取现点
笔数
商户笔数
发卡行授权笔数
总行代授权笔数
分代授权笔数

转
入
手
工
授
权
联 行 号
笔 数
联 行 号
笔 数
联 行 号
笔 数

合计

注：转入手工授权的统计数来源于自动授权语音系统的统计结果
2、市场科需求
(1) 每月分别统计我行发展/关闭商户、支行发展/关闭商户、EDC商户、POS商户数量，包括本月新增和月末总数。
(2) 每月统计设备安装数量，包括POS、EDC台数。
(3) 每月统计各家商户交易笔数、交易金额、比上月增加的百分比、比去年同期增加的百分比、自年初累计金额（按卡类、商户类分别统计).按照长城卡、借记卡、VISA、MASTER卡、AE卡、DC卡、JCB卡分别统计。并按照饭店类、餐饮类、百货类、工艺品类、其它类、交通类、娱乐类各类根据月交易总金额由大到小排列.
(4) 各家商户月交易金额包括该家商户各种类型消费交易的总和.例如:北京友谊宾馆信用卡月交易金额应该是贵宾楼、迎宾楼、敬宾楼、悦宾楼、怡宾楼、苏圆、雅圆、友谊宫、各商品部、各餐厅以及商务中心交易量的总和。
(5) 每月分别统计各支行发展商户数量及商户月交易量。
(6) 每月统计各类商户分卡别的收益情况。其中设立两个参数:人民币长城卡平均回扣率、外卡平均回扣率。
(7) 能够随时统计某一类商户或某一家商户某一时期（最小到天）的交易量情况、收益情况(交易量X回扣率)，包括与上期或去年同期比较的动态数据。
(8) 能够随时统计各商户某一时期EDC交易量及平均每台EDC交易量，并计算EDC设备的投资回报比。
(9) 每月按商户类分类统计本月无交易商户也叫睡眠户名单。并随时可在系统中查询某家商户未发生交易的起始时间。
(10) 建立参数监测:如按某类商户（按卡的交易量）增长和降低的百分比打印此类商户.
(11) 3分钟内同一卡号在同一POS机上的多笔交易清单，1天以内同一卡号在同一POS上的多笔交易清单，EDC要求当天结帐，但已超出2天还未结帐的记录要求打印输出。
3、业务管理科需求
1)．透支分析：
每星期二的透支表
(1) 员工卡透支Y10,000以上（表一）
(2) 普通卡透支Y20，000以上(表二）
(3) 金卡透支Y30，000以上（表三）
要求显示卡状态（有无止付）、有无保证金及保证金金额、透支天数
2）．交易分析：
(1) 月大额授权、取现清单（注：授权按时间/笔数/金额排序）
A. 取现累计Y100，000以上(要求显示单笔最高金额）（表四）
B. 取现累计Y1，000,000以上(要求显示单笔最高金额）（表五)
C. 取现累计Y10,000，000以上（要求显示单笔最高金额)(表六）
(2) 月大额员工卡交易Y5，000以上清单（分借方、贷方）（表七）
(3) 月个人卡交易Y50,000以上清单（分借方、贷方）(表八）
(4) 月公司卡交易Y500，000以上清单（分借方、贷方）（表九）
4、计划资金科需求
表1．代理国外信用卡业务量统计表(月报)
XXXX年XX月
信用卡种类
直接购货（手工）
直接购货（EDC）
合计
自年初累计
笔数
金额
笔数
金额
笔数
金额
笔数
金额
南洋商业银行

美国运通公司

大来信用卡公司

JCB信用卡公司

注：每一列需增加与去年同期比较（目前无去年数据).
VIP服务也要统计动态，VIP名单和卡号是动态变化的,由手工录入,各种专用卡的存款余额。
直接购货数据来源于EDC系统，按清算行统计。
手工数据无法得到。
输入条件:年月.
表2．中国银行人民币长城卡业务统计表
卡帐户情况(月报）
填报单位：(0013)延庆支行 表号：CCTAB2
填报时间:1999年9月6日 金额单位:万元人民币
卡类
个人卡
单位卡
合计
比上月增长（%）
比去年同期增长（％）
去年同期累计
上月本年累计
本年累计
1．本月新增发卡量
A．信用卡
其中：联名卡（各种专用卡）
B．MC电子借记卡
其中:联名卡
C．VISA电子借记卡
其中：联名卡

2．本月注销和清户卡数

3．月末有效持卡人数
A．信用卡
其中:联名卡
D．专用卡
其中：联名卡

4．月末帐户总数（827）

5．本月活动帐户数（827）

6．月末透支帐户数（827)

7．月末透支金额（827借）

8．月末存款余额（827贷)

A．备用金

B．保证金

9．透支占存款%

单位负责人: 复核人： 制表人：
注：输入条件：年月.
表3．中国银行人民币长城卡业务统计表
特约商户交易、收益明细表（月报）
填报单位：（0013）延庆支行 表号:CCTAB4
填报时间:1999年9月6日 金额单位：万元人民币
卡交易
商户
个人卡
单位卡
借记卡(MC/VISA)
其它卡
合计
本年累计
交易额
回扣额
交易额
回扣额
交易额
回扣额
交易额
回扣额
交易额
回扣额
交易额
回扣额
宾 馆

餐 饮

百 货

工艺品

民 航

旅 游

娱 乐

交 通

其 他

合 计

本 月 收 益 明 细
收 入
个人卡
单位卡
借记卡(MC/VISA）
其它卡
合计
本年累计
年 费

手续费

透支息

其 它

合 计

损 失

单位负责人： 复核人: 制表人：
表4．中国银行人民币长城卡业务统计表
风险统计表（月报）
填报单位：（0013）延庆支行 表号：CCTAB5
填报时间：1999年9月6日 金额单位：人民币元

金卡
普通卡
合计
善意透支
户数
0
收回
0
0
0
增 加
0
0
0
转 出
0
0
0
金额
0
收 回
0
0
0
增 加
0
0
0
转 出
0
0
0
恶意透支
户数
0
收 回
0
0
0
转 入
0
0
0
金额
0
收 回
0
0
0
转 入
0
0
0
一年以上
恶意透支
户数
0
收 回
0
0
0
转 入
0
0
0
金额
0
收 回
0
0
0
转 入
0
0
0
一年以上
恶意透支收回数
户 数
0
0
0
金 额
0
0
0
内部
笔 数

作案
金 额

内外勾结
作案
笔 数

金 额

遗失卡、被盗卡
的冒用
笔 数

金 额

伪造
信用卡
笔 数

金 额

涂改
信用卡
笔 数

金 额

假身份
办卡
笔 数

金 额

持卡人丧失
偿还能力
笔 数

金 额

单位负责人:复核人: 制表人:
表5．人民币长城卡业务量统计表(增加与去年同期比较）
XXXX年XX月
交易情况
个人卡
公司卡
合计
自年初累计
笔数
金额
笔数
金额
笔数
金额
笔数
金额
直接购货
1．本卡本地

2．本卡外地

3．外卡本地

合计(1+2)

合计（1+3）

合计（1+2+3）

取
现
1．本卡本地

2．本卡外地

3．外卡本地

合计（1+2）

合计（1+3）

合计(1+2+3)

转
帐
1．本卡本地

2．本卡外地

3．外卡本地

合计(1+2）

合计（1+3)

合计(1+2+3）

存
款
1．本卡本地

2．本卡外地

3．外卡本地

合计(1+2)

合计（1+3）

合计（1+2+3）

注：输入条件:年月.
表6．中国银行人民币长城卡业务统计表
商户及设备情况(月报）
填报单位：(0013）延庆支行
填报时间：1999年9月6日 表号:CCTAB3
特 约 商 户
银行网点及设备
商户类
本月增减
月末总数
设 备
数 目
宾 馆
0
0
受卡网点
0
餐 饮
0
0
交
易
设
备
ATM
0
百 货
0
0
POS
0
工艺品
0
0
EDC
0
民 航
0
0

旅 游
0
0
娱 乐
0
0
交 通
0
0
其 他
0
0
合 计
0
0
合计
0
单位负责人: 复核人： 制表人：
注：设备分类无法辨别。
表7．中国银行人民币长城卡业务统计表
金卡情况统计（月报）
填报单位：（0013）延庆支行 表号：CCTAB9
填报时间：1999年9月6日 金额单位：万元人民币
卡类

个人卡
单位卡
合计
比上月增长％
比去年同期增长%
本年累计
笔数
金额
笔数
金额
笔数
金额
笔数
金额
笔数
金额
笔数
金额
1． 金卡交易量
直接消费
存 现
取 现
转 帐

2．月末有效金卡数

3．金卡帐户总数

4．金卡本月活动帐户数

5．月末金卡透支帐户数

6．月末金卡透支金额

7．月末金卡存款余额

A．备用金

B．保证金

8．金卡透支占存款%

单位负责人: 复核人: 制表人：
表8．联名卡及电信业务统计表

取现
直接购货
合计
自年初累计
笔数
金额
笔数
金额
笔数
金额
笔数
金额
国航卡

香格里拉卡

其它

290
299
其它

笔数
金额
笔数
金额
笔数
金额

长城卡

借记卡

三、需求分析
1、维表
维表可以被看成是一个窗口，透过它用户可分析数据。维表中包含有支持商业运作的相关项的文字说明。
在本系统中，可以成为维的有：分/支行、帐户、持卡人、商户、POS及时间等。
2、事实表
事实表可以被看作是有两部分组成的一种表，其中第一部分定义了主键；另一部分包含有关数据仓库的数值型测量值,这些测量值作为每一个衍生键定义和计算，并作为已知的事实或测量值保存。测量值应该具备的属性有：数值型和累积性(通常).
在本系统中，可以成为事实表的有：BCS交易表、BCS授权表、NAS授权表、EDC交易表及透支信息表。
3、事务--业务处理过程及业务术语
数据仓库设计关注的是对事务的理解。需求是与具体事务紧密结合的.
在本系统中,可以成为事务的有：
(1) 透支：透支利息、透支户数/金额、透支还款、善意透支、恶意透支、收回、增加、转入、转出等.
(2) 交易:取现、借方、贷方、单笔最高金额、直接购货、转帐、存款、交易量、笔数、回扣率、收益等。
(3) 业务:卡类、备用金、保证金、金卡、普通卡、公司卡、个人卡、员工卡、借记卡、外卡、手续费、有效、授权、商户、设备、活动、有效等。
4、主键
一个主键有一个表中的一个或多个字段构成，它唯一地标识了该表中的每一行，也既在被定义为主键的一个或多个列上建立一个唯一性索引.
在本系统中,可以定义为主键的有：分支行号、帐号、卡号、商户号、POS号、交易类型、卡类别等.
5、外键
在一个表中被定义为主键的列如果被包含在另一个不同的表中，该列就被称为外键.注意只有在一个表中被定义为主键一部分的列才能被引用为外键.换一种说法,外键就是存储在另一个表中的该表的主键.
四、系统结构图及业务数据流图
1、系统结构图
BCS
信用卡业务包括四个业务系统，分别是长城卡管理系统——BCS，商户POS数据采集系统——Cardpool,异地网络授权系统——NAS及电话自动语音授权系统——JetVoice。DWBOS代表数据仓库分析系统。
NAS
Cardpool
JetVoice

SCO
UNIX
Informix
SCO
UNIX
流式文件
容错系统
容错文件
报表文件
Windows
NT
SQL Server

TCP/IP

Windows NT
COLD
Oracle
服务器

DWBOS

自动授权
2、数据流图
国外
外币
Cardpool
总行

外卡信息

卡部业务

长城卡人民币本地卡

数据库表
人民币

输出
报表
浏览器
BO
Oracle
BCS
JetVoice

本地卡

本地卡
电话授权

外地卡授权信息

本地卡
外地卡

NAS

本地卡

总行
外地卡
外地发卡行
外地卡
本地卡
本地卡
NAS
外地卡
本地卡

五、源数据表结构
1、BCS系统
表1:DAT_CARD_INFO(持卡人信息）
表类型：维表/日表
更新类型：新——添加、有——修改
生存期：每日更新（前一个交易日的表名变为.save）
KEY值:卡号、卡状态、持卡人职务、单位所有制
字段名称
卡号
当前卡状态
卡状态设置日期
有效期
持卡人姓名
持卡人卡面英文名称
英文代码
Card_no
Card_status
Status_date
Exp_date
Cardholder
Emboss_name
数据类型
Char
Char
Date
Date
Char
Char
长度
16
1

20
20
说明

1.开卡
2.领卡
3。挂失
4。补卡
5。止付
6。换卡
0。清卡
YYMMDD
YYMM

字段名称
密码
性别
持卡人证件类型
证件号码
持卡人出生日期
持卡人国籍
英文代码
Pass_offset
Sex
Id_type
Id_num
Birth_date
Nationality
数据类型
Char
Char
Char
Char
Date
Char
长度
6
1
1
15

1
说明

1。男
2.女
1