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

1、一、概述2二、四科室需求31、风险科需求32、市场科需求133、业务管理科需求144、计划资金科需求15三、需求分析231、维表232、事实表233、事务业务处理过程及业务术语234、主键245、外键24四、系统结构图及业务数据流图251、系统结构图252、数据流图26五、源数据表结构271、BCS系统272、CARDPOOL系统343、NAS系统364、BCS系统报表37六、生成表结构39七、码表结构43八、结果表结构50九、数据表创建方法511、BCS系统512、CARDPOOL系统573、NAS系统584、生成表585、码表62十、数据处理过程681、目录结构682、流程说明68十一、问

2、题及处理方法80、概述BillInmon(数据仓库之父)在BuildingtheDataWarehouse(JohnWiley&SonsInc.,1996)书中把数据仓库描述为一个“面向主题的、完整的、非易失的、不同时间的、用于支持决策管理的数据集合”。数据仓库是只用于制作报表的数据库。对我们而言，数据仓库是某个“宽广”的数据仓储。它包括许多的主题领域。而一个数据集市，恰恰相反，它把眼睛盯在商业活动的某个非常有限的部分上。它往往涉及某个单独主题或单个类型的分析。在日常工作中，IT人员经常听到这样的抱怨：“我要求的报表怎么还没出来？”或者是“我要对XX报表做些修改，怎么还没结果？”等等。

3、在IT飞速发展的最近几年里，银行信用卡部先后针对业务上了一些计算机系统。这些系统的特点是：信息量规模小、数据经常实时更新、适用于业务人员快速录入数据、使用模式相对来说是可以预测的、模式很复杂、业务流程难以更改、数据在线保存的时间较短及各系统之间缺乏必要的联系等。这样的系统被称之为OLTP系统。OLTP系统的这些特点也就决定了有如此抱怨。如何解决这些问题呢？我们首先想到的是：把数据集中、完整地存储在中心数据库中。所有的业务处理在中心数据库上进行。所有的报表工作脱离数据库。这听起来难道不是有点像一个数据仓库吗？我们为什么不在OLTP的业务系统数据库的基础上生成报表呢？答案很简单：因为报表经常需要大

4、量的、长时间的数据做依据，然后经过大量的运算，才能得出你想要的结论。这对业务系统的正常运转影响很大，以至于业务系统无法正常运转。当然，不是什么时候都需要一个数据仓库的。正如数据仓库的定义：是用于支持决策管理的数据集合。中国银行北京分行从1986年6月1日发行第一张人民币长城卡到现在拥有将近20万的持卡人。从过去手工处理业务到现在拥有几个OLTP业务系统。信用卡业务有了飞速的发展。但也应看到信用卡市场的激烈竞争。如何给决策者及时提供决策支持信息，是在激烈的市场竞争中立于不败之地的关键。年月日金卡普通卡比例合计自年初累计户数金额户数金额户数金额户数金额户数金额应收透支利息本月实收透支利息透支30天

5、内应收息透支30天内本月实收息透支30-60天内应收息透支30-60天内本月实收息透支60-90天内应收息透支60-90天内本月实收息透支90-180天内应收息透支90-180天内本月实收息透支180-360天内应收息透支180-360天内本月实收息透支360-720天内应收息透支360-720天内本月实收息透支720天以上应收息透支720天以上本月实收息、四科室需求1、风险科需求表1.北京市分行/ （XX支行）信用卡透支利息统计表注：第一列的时间段缺省设置是表中值，但应允许人为修改各时间段的取值及范围表2.北京市分行/（XX支行）信用卡透支还款分析表年月日金卡普通卡比例合计自年初累计户数金额

6、户数金额户数金额户数金额户数金额全辖透支数额本月实收透支额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.XXX

7、X年第X季度关于信用卡透支情况的报告（每季一份）总行：根据人总行银管1997185号文件，关于调整银行卡报表制度的通知精神，现统计数字如下:六个月以上透支余额二年以上透支余额已确认的损失金额已处理的损失金额其中呆帐核销其中保险赔偿其他情况注：第一、二项可由机器系统提供，其它项需手工填入。表4.中国银行人民币长城卡业务统计表风险统计表（月报）填报单位：（0142）北京市分行表号：CCTAB5填报时间：0000年00月00日0000年00月金额单位：人民币、元（需取二个月的数据做统计）金卡普通卡合计机i卜善意透支户数收回器（60天以内增加可普通5000以转出出下金卡2万以金额收回数据下）增加转出恶

8、意透支户数收回转入金额收回转入一年以上恶户数收回息透支转入金额收回转入一年以上恶户数1!息透支收回金额J1内部作案笔数金额内外勾结作笔数机器不可出和案金额遗失卡、被盗笔数卡的冒用金额伪造信闱卡笔数金额涂改信闱卡笔数领据金额假身份办卡笔数金额持卡人丧失笔数1r偿还能力金额本月公司卡止付数：（机器可出此数据）本月个人卡止付数：（机器可出此数据）注：户数收回是指已全部还清的；户数增加是指新增的透支户；“金额增加”是指以往透支户新增透支额和新增透支户全部透支额之和；转出户数是指到本月累计数超出60天的或超过普通卡、金卡限额的透支户；转出金额是转出户数金额之和；转出应与邻近项的转入相对应。5 / 80表

9、5.（按各发卡单位统计）分行及各支行透支状况（全部按户数/金额统计）年月日11 / 80分支行名称透支金额上月透支增长率上月还款（包括还清）自年初累计还款本月新增透支自年初累计增加恶意透支180天以上恶意透支占总透支比例当前有效卡数量交易数量息存数量户数金额户数金额户数金额户数金额户数金额户数金额户数金额户数金额户数金额海淀朝阳西城崇文宣武机场办昌平顺义通县怀柔平谷密云大兴房山门头沟石景山丰台延庆东城前门支行原总行天马天马（吉亚）京分合计注：此表中的恶意透支是指超出180天以上的透支户概念：普通卡透支5000以上；金卡透支20000以上，透支天数超过60天公式：（本月-上月）/上月例：本月透支

10、150万；上月透支100万（150-100）/100=50%个类结果笔数(1)(所有透支户)金额(2)(所有透支金额)正常(3)关注(4)不良资产应计利息(10)笔数金额笔数金额笔数(5)小计土小(6)次级可疑(8)损失(9)卡类笔数金额笔数金额笔数金额单位卡个人卡合计表6.信用卡风险资产分类汇总表填表单位(公章)：填表日期：年月日单位：千元制表人：审核人：负责人:注：(1)为五类风险资产的总笔数。(2)为五类风险资产的总金额，即(2)=(3)+(4)+(7)+(8)+(9)。(5)为(7)、(8)、(9)、的笔数合计。(6)为(7)、(8)、(9)、的金额合计。(10)为透支日至分类日应计利

11、息。正常：金卡2万以内、普通卡5000以内，透支天数不足60天，均属正常范围。关注：透支额在善意透支额以内的，但透支天数在609CK以内的，属关注范围不良资产：无论金额多少，只要天数超出90天的均属不良资产范畴。次级：是指透支天数在9018“以内的不良资产。可疑：是指透支天数在180360K以内的不良资产。损失：是指透支天数超出360天以上的不良资产。长城人民币信用卡持卡人消费/取现/存款/转帐状况统计表（月报）/（季报）年月日月消费50006000元月消费600010000元月消费10000元以上卡号金额笔数平均卡号金额笔数平均卡号金额笔数平均合计：注：卡号栏需按类别分别统计，如个人卡、单位

12、卡、普通卡、金卡等，金额范围应允许人为调整卡号按升序排列金额按降序排列同类卡按金额降序排列表8.长城人民币信用卡持卡人消费状况统计表（年报）消费6万兀7.2力兀消费7.2力兀12万兀消费12万元以上卡号金额笔数平均卡号金额笔数平均卡号金额笔数平均合计：2、市场科需求表9.NAS系统授权业务量统计（月报）（来源于NAS数据、自动授权语音系统，仅对长城卡进行统计）成功交易本行卡笔数外地卡笔数取现点笔数商户笔数发卡行授权笔数总行代授权笔数分代授权笔数转入手工授权联行号笔数联行号笔数联行号笔数合计注：转入手工授权的统计数来源于自动授权语音系统的统计结果(1)每月分别统计我行发展/关闭商户、支行发展/关

13、闭商户、EDC商户、POS商户数量，包括本月新增和月末总数。(2)每月统计设备安装数量，包括POS、EDC台数。(3) 每月统计各家商户交易笔数、交易金额、比上月增加的百分比、比去年同期增加的百分比、自年初累计金额(按卡类、商户类分别统计)。按照长城卡、借记卡、VISA、MASTER卡、AE卡、DC卡、JCB卡分别统计。并按照饭店类、餐饮类、百货类、工艺品类、其它类、交通类、娱乐类各类根据月交易总金额由大到小排列。(4) 各家商户月交易金额包括该家商户各种类型消费交易的总和。例如：北京友谊宾馆信用卡月交易金额应该是贵宾楼、迎宾楼、敬宾楼、悦宾楼、怡宾楼、苏圆、雅圆、友谊宫、各商品部、各餐厅以及

14、商务中心交易量的总和。(5) 每月分别统计各支行发展商户数量及商户月交易量。(6) 每月统计各类商户分卡别的收益情况。其中设立两个参数：人民币长城卡平均回扣率、外卡平均回扣率。(7) 能够随时统计某一类商户或某一家商户某一时期(最小到天)的交易量情况、收益情况(交易量X回扣率)，包括与上期或去年同期比较的动态数据。(8)能够随时统计各商户某一时期EDC交易量及平均每台EDC交易量，并计算EDC设备的投资回报比。(9)每月按商户类分类统计本月无交易商户也叫睡眠户名单。并随时可在系统中查询某家商户未发生交易的起始时间。(10)建立参数监测：如按某类商户(按卡的交易量)增长和降低的百分比打印此类商户

15、。(11)3分钟内同一卡号在同一POSW上的多笔交易清单，1天以内同一卡号在同一POS上的多笔交易清单，EDC要求当天结帐，但已超出2天还未结帐的记录要求打印输出。3、业务管理科需求1）透支分析：每星期二的透支表（1）员工卡透支Y10,000以上（表一）（2）普通卡透支Y20,000以上（表二）（3）金卡透支Y30,000以上（表三）要求显示卡状态（有无止付）、有无保证金及保证金金额、透支天数2）交易分析：（1）月大额授权、取现清单（注：授权按时间/笔数/金额排序）A.取现累计Y100,000以上（要求显示单笔最高金额）（表四）B.取现累计Y1,000,000以上（要求显示单笔最高金额）（表五

16、）C.取现累计Y10,000,000以上（要求显示单笔最高金额）（表六）（2）月大额员工卡交易Y5,000以上清单（分借方、贷方）（表七）（3）月个人卡交易Y50,000以上清单（分借方、贷方）（表八）（4）月公司卡交易Y500,000以上清单（分借方、贷方）（表九）13 / 804、计划资金科需求表1.代理国外信用卡业务量统计表（月报）XXXX年XX月信闱卡种类直接购货（手工）直接购货（EDC）合计自年初累计笔数金额笔数金额笔数金额笔数金额南洋商业银行美国运通公司大来信闱卡公司JCB信闱卡公司注：每一列需增加与去年同期比较（目前无去年数据）。VIP服务也要统计动态，VIP名单和卡号是动态变化

17、的，由手工录入，各种专用卡的存款余额直接购货数据来源于EDC系统，按清算行统计。手工数据无法得到。输入条件：年月。15 / 80表3.中国银行人民币长城卡业务统计表卡帐户情况（月报）卡类个人卡单位卡合计比上月增长（%）比去年同期增长（%）去年同期累计上月本年累计本年累计1.本月新增发卡量A.OO其中：联名卡（各种专闱卡）B.MC电子借记卡其中：联名卡C.VISA电子借记卡其中：联名卡2.本月注销和清户卡数3.月末有效持卡人数A.OO其中：联名卡D.专闱卡其中：联名卡4.月末帐户总数（827）5.本月活动帐户数（827）6.月末透支帐户数（827）7.月末透支金额（827借）8.月末存款余额（8

18、27贷）A.备用金B.保证金9.透支占存款填报单位：（0013）延庆支行填报时间：1999年9月6日表号：CCTAB2金额单位：万元人民币单位负责人：复核人：制表人:注：输入条件：年月17 / 80中国银行人民币长城卡业务统计表特约商户交易、收益明细表（月报）填报单位：填报时间：（0013）延庆支行1999年9月6日表号：CCTAB4金额单位：万元人民币卡交易个人卡单位卡借记卡（MC/VISA）其它卡合计本年累计商小、.交易额回扣额交易额回扣额交易额回扣额交易额回扣额交易额回扣额交易额回扣额宾馆餐饮百货工艺品民航旅游娱乐交通其他合计本月收益明细收入个人卡单位卡借记卡（MC/VISA）其它卡合计

19、本年累计年费手续费透支息其它合计损失单位负责人复核人:制表人：中国银行人民币长城卡业务统计表风险统计表（月报）表4.金卡普通卡合计善意透支户数0收回000：增力口0001转出000：金额0收回000增力口000:转出000:恶意透支户数0收回000转入000金额0收回000：转入000：一年以上恶意透支户数0收回000：转入000金额0收回000转入000一年以上恶意透支收回数户数000金额000内部作案笔数金额内外勾结作案笔数金额遗失卡、被盗卡的冒用笔数金额伪造笔数金额涂改笔数金额假身份办卡笔数金额持人丧失偿还能力笔数金额填报单位：（0013）延庆支行填报时间：1999年9月6日表号：CCT

20、AB5金额单位：人民币元单位负责人:复核人:制表人:# / 80人民币长城卡业务量统计表（增加与去年同期比较）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)注：输入条件：年月19 / 80中国银行人民币长城卡业务统计

21、表商户及设备情况（月报）表6.填报单位：（0013）延庆支行填报时间：1999年9月6日表号：CCTAB3特约商尸银行网点及设备商户类本月增减月末总数设备数目宾馆00受卡网点0餐饮00交ATM0百货00POS0工艺品00易EDC0民航00旅游00娱乐00交通00设其他00备合计00合计0单位负责人：复核人：制表人:注：设备分类无法辨别# / 80中国银行人民币长城卡业务统计表金卡情况统计（月报）个人卡单位卡合计比上月增长%比去年同期增长%本年累计笔数金额笔数金额笔数金额笔数金额笔数金额笔数金额1.金卡交易量直接消费存现取现转帐2.月末有效金卡数3.金卡帐户总数4.金卡本月活动帐户数5.月末金卡

22、透支帐户数6.月末金卡透支金额7.月末金卡存款余额A.备用金B.保证金8.金卡透支占存款单位负责人:复核人:制表人:填报单位：（0013）延庆支行填报时间：1999年9月6日表号：CCTAB9金额单位：万元人民币表8.联名卡及电信业务统计表取现直接购货合计自年初累计笔数金额笔数金额笔数金额笔数金额国航卡香格里拉卡其它290299其它笔数金额笔数金额笔数金额长城卡借记卡21 / 80三、需求分析1、维表维表可以被看成是一个窗口，透过它用户可分析数据。维表中包含有支持商业运作的相关项的文字说明。在本系统中，可以成为维的有：分/支行、帐户、持卡人、商户、POS及时间等。2、事实表事实表可以被看作是有

23、两部分组成的一种表，其中第一部分定义了主键；另一部分包含有关数据仓库的数值型测量值，这些测量值作为每一个衍生键定义和计算，并作为已知的事实或测量值保存。测量值应该具备的属性有：数值型和累积性(通常)。在本系统中，可以成为事实表的有：BCS交易表、BCS授权表、NAS授权表、EDC交易表及透支信息表。3、事务业务处理过程及业务术语数据仓库设计关注的是对事务的理解。需求是与具体事务紧密结合的。在本系统中，可以成为事务的有：(1) 透支：透支利息、透支户数/金额、透支还款、善意透支、恶意透支、收回、增加、转入、转出等。(2) 交易：取现、借方、贷方、单笔最高金额、直接购货、转帐、存款、交易量、笔数、

24、回扣率、收益等。(3) 业务：卡类、备用金、保证金、金卡、普通卡、公司卡、个人卡、员工卡、借记卡、外卡、手续费、有效、授权、商户、设备、活动、有效等。4、主键一个主键有一个表中的一个或多个字段构成，它唯一地标识了该表中的每一行，也既在被定义为主键的一个或多个列上建立一个唯一性索引。在本系统中，可以定义为主键的有：分支行号、帐号、卡号、商户号、POS号、交易类型、卡类别等。5、外键在一个表中被定义为主键的列如果被包含在另一个不同的表中，该列就被称为外键。注意只有在一个表中被定义为主键一部分的列才能被引用为外键。换一种说法，外键就是存储在另一个表中的该表的主键。23 / 80四、系统结构图及业务数

25、据流图1、系统结构图NAS信用卡业务包括四个业务系统，分别是长城卡管理系统一一BCS,商户POS据采集系统一一Cardpool,异地网络授权系统及电话自动语音授权系统一一JetVoice。DWBOS代表数据仓库分析系统。27 / 802、数据流图自动授权/外币、(Cardpool)1总行卡部业务vyvy外卡信息'人民币1,电话授权飞JetVoice/本地卡*1BCS)本地卡f(Oracle外地卡本地卡本地卡'"卜地卡授权信息厂丫NASvZJii外地卡本地卡本地卡，、总行(NASJ外地卡?L)数据库表*(0)报表输出本地卡/本地卡一二d外地发卡)>外地卡1行)五、

26、源数据表结构1、BCS系统表1:DAT_CARD_INFO（持卡人信息）表类型：维表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为.saveKEY值：卡号、卡状态、持卡人职务、单位所有制字段名称当前卡状态卡状态设置日期功效期持卡人姓名持卡人卡面英义名称英文代码Card_noCard_statusStatus_dateExp_dateCardholderEmboss_name数据类型1CharCharDateDateCharChar长度1612020m明1 。2 .领卡3 -4 .补卡5 .止付6 .八0MYYMMDDYYMM字段名称密码性别持卡人证件色证件号码持

27、卡人出生日期持卡人国籍英文代码Pass_offsetSexId_typeId_numBirth_dateNationality数据类型|CharCharCharCharDateChar长度611151说明1 .男2 .女1 .身份证2 .工作证3 .护照4 .临时身份证5 .军人证件YYMMDD1.已婚0.未婚字段名称持卡人职务A位是联名卡代码家庭住址家庭邮编家庭联系电话英文代码Title_codeSignature。Home_addressHome_zip_codeHome_tel_no数据类型CharCharCharCharChar长度2640612说明.01”（接左格）0:无02.职工0

28、8.副处长1:国航卡03.总经理09JK2：香格里拉04.副总经理10.田I局K卡05.经理11科长06.副经理12.副科长07.处长13.其他字段名称单位名称单位所有制单位联系电话单位地址单位邮编ATM功能标志英文代码Employer_nameEmployer_ownershipEmployer_tel_noEmployer_addressEmployer_zip_codeAtm_function数据类型|CharCharCharCharCharChar长度301124061说明1.国营2集体0.无1.有3.外资4.合资5.侨资6.个体9.其他字段名称开卡柜员批准人英文代码TellerSup

29、ervisor”ode数据类型CharJChar长度313说明.表2:DAT_CARD_FINA（帐户信息）表类型：维表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为.saveKEY值：帐号、帐户状态、发卡行号字段名称交易代码交易类型帐户号清算日期卡号的14,15位出帐单日期英文代码Tran_idTran_typeAcct_noPosted_dateCard_sub_noBilling_date数据类型|CharCharCharDateCharChar长度121222说明3 .交单清算4 .批处理5.ATM交易11 .签购（借）12 .签购冲21.取现（借）（接

30、左格）72.费用冲81.保证金YYMMDD00.主卡01-99.付卡本月帐单号050822.取现冲31 .转帐出（借）32 .转帐出冲35 .转帐入（贷）36 .转帐入冲41 .存款（贷）42 .存款冲51 .罚息（借）52 .罚息冲61 .存息（贷）62 .存息冲71.费用（借）（贷）82 .保证金冲83 .保证金取（借）84 .保证金取冲85 .保证金利息（贷）86保证金利息（冲）15182225字段名称交易金额帐户余额处理说明授权号码商户号码交易日期英文代码Tran_amtAcct_balParticularAuth_noMerchant_noInvoice_date数据类型|Numbe

31、rNumberCharCharCharDate长度8,28,210610说明.1.内部处理yymmdd2.商户交单字段名称柜员交易流水号场结流水号码输机柜员号码英文代码Teller_seq_noSeq_noTeller_code数据类型|CharCharChar长度553说明表3:DAT_GUARANTEE_ACCT（保证金帐户信息）表类型：维表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为.saveKEY值：帐号字段名称帐户号保证金状态状态日期开卡日期保证金余额英文代码Acct_noGuarantee_statusStatus_dateIssue_card_d

32、ateGuarantee_bal数据类型|CharChar|DateDateNumber长度1219,2说明0.清户1.后效YYMMDDYYMMDD表4:DAT_HOT_CARD（黑卡信息）表类型：事实表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为.saveKEY值：卡号、扣卡代码字段名称功效期扣卡代码柜员号输入日期英文代码Card_noExp_dateProcess_codeTeller_codeEntry_date数据类型1CharDateCharCharDate长度1613说明YYMM1.扣卡YYMMDD5.拒付6.联系发卡行表5:DAT_VIP_CAR

33、D（贵宾卡授权限额信息）表类型：维表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为.saveKEY值：帐号字段名称|帐户号授权限额英文代码Acct_noAmt数据类型|CharNumber长度128,0说明表6:DAT_MERCHANT_INFO（商户信息）表类型：维表/日表更新类型：新一一添加、有一一修改生存期：每日更新（前一个交易日的表名变为说明.save）KEY值：商户号、商户状态、商户所有制、授权方式字段名称商户号商户状态最后依次状态设置日期中文名称央义名称英文代码Merchant_noMerchant_statusStatus_dateMerchant

34、_name_chnMerchant_nameeng数据类型CharChar|DateCharChar长度1013030说明0.关闭YYMMDD暂无用Sic_codeChar41.功效字段名称I商户所有制地址邮编附属日期功效期J联系人姓名英文代码OwnershipAddressZip_codeAffiliated_dateExp_dateContact_person数据类型1CharCharCharDateDateChar长度1406620说明1.国营2集体3 .外资4 .合资5 .侨资6 .个体7 .其他YYMMDDYYMM字段名称联系人电话授权方式授权电话说明单号人民币授权限额折扣方式英文代码Contact_person_telAuth_modeAuth_telBulletin_numberFloor_a