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

目 录一、概述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十一、问题及处理方法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，虽还欠款，但算还款户。还款数额包括结息数额，由结息带来的实际还款也算还款户。第一列的时间段应允许人为调整，时间的累计是以某一天开始，倒推统计数据。表3XXXX年第X季度关于信用卡透支情况的报告（每季一份）总行：根据人总行银管1997185号文件，关于调整银行卡报表制度的通知精神，现统计数字如下：六个月以上透支余额二年以上透支余额已确认的损失金额已处理的损失金额其中呆帐核销其中保险赔偿其他情况注：第一、二项可由机器系统提供，其它项需手工填入。表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天，均属正常范围。关注：透支额在善意透支额以内的，但透支天数在6090天以内的，属关注范围。不良资产：无论金额多少，只要天数超出90天的均属不良资产范畴。次级：是指透支天数在90180天以内的不良资产。可疑：是指透支天数在180360天以内的不良资产。损失：是指透支天数超出360天以上的不良资产。表7长城人民币信用卡持卡人消费/取现/存款/转帐状况统计表（月报）/（季报） 年 月 日月消费50006000元月消费600010000元月消费10000元以上卡 号金额笔数平均卡 号金额笔数平均卡 号金额笔数平均合计：注：卡号栏需按类别分别统计，如个人卡、单位卡、普通卡、金卡等，金额范围应允许人为调整。卡号按升序排列金额按降序排列同类卡按金额降序排列表8长城人民币信用卡持卡人消费状况统计表（年报） 年 月 日消费6万元7.2 万元消费7.2万元12 万元消费12 万元以上卡 号金额笔数平均卡 号金额笔数平均卡 号金额笔数平均合计：表9NAS系统授权业务量统计（月报）（来源于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信用卡 其中：联名卡（各种专用卡）BMC电子借记卡 其中：联名卡CVISA电子借记卡 其中：联名卡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收回000增 加000转 出000金额0收 回000增 加000转 出000恶意透支户数0收 回000转 入000金额0收 回000转 入000一年以上恶意透支户数0收 回000转 入000金额0收 回000转 入000一年以上恶意透支收回数户 数000金 额000内部笔 数作案金 额内外勾结作案笔 数金 额遗失卡、被盗卡的冒用笔 数金 额伪造信用卡笔 数金 额涂改信用卡笔 数金 额假身份办卡笔 数金 额持卡人丧失偿还能力笔 数金 额单位负责人：复核人：制表人：表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特 约 商 户银行网点及设备商户类本月增减月末总数设 备数 目宾 馆00受卡网点0餐 饮00交易设备ATM0百 货00POS0工艺品00EDC0民 航00旅 游00娱 乐00交 通00其 他00合 计00合计0单位负责人：复核人：制表人：注：设备分类无法辨别。表7中国银行人民币长城卡业务统计表金卡情况统计（月报）填报单位：（0013）延庆支行表号：CCTAB9填报时间：1999年9月6日金额单位：万元人民币卡类个人卡单位卡合计比上月增长%比去年同期增长%本年累计笔数金额笔数金额笔数金额笔数金额笔数金额笔数金额1 金卡交易量 直接消费 存 现 取 现 转 帐2月末有效金卡数3金卡帐户总数4金卡本月活动帐户数5月末金卡透支帐户数6月末金卡透支金额7月末金卡存款余额 A备用金 B保证金8金卡透支占存款%单位负责人：复核人：制表人：表8联名卡及电信业务统计表取现直接购货合计自年初累计笔数金额笔数金额笔数金额笔数金额国航卡香格里拉卡其它290299其它笔数金额笔数金额笔数金额长城卡借记卡三、需求分析1、维表维表可以被看成是一个窗口，透过它用户可分析数据。维表中包含有支持商业运作的相关项的文字说明。在本系统中，可以成为维的有：分/支行、帐户、持卡人、商户、POS及时间等。2、事实表事实表可以被看作是有两部分组成的一种表，其中第一部分定义了主键；另一部分包含有关数据仓库的数值型测量值，这些测量值作为每一个衍生键定义和计算，并作为已知的事实或测量值保存。测量值应该具备的属性有：数值型和累积性（通常）。在本系统中，可以成为事实表的有：BCS交易表、BCS授权表、NAS授权表、EDC交易表及透支信息表。3、事务业务处理过程及业务术语数据仓库设计关注的是对事务的理解。需求是与具体事务紧密结合的。在本系统中，可以成为事务的有：(1) 透支：透支利息、透支户数/金额、透支还款、善意透支、恶意透支、收回、增加、转入、转出等。(2) 交易：取现、借方、贷方、单笔最高金额、直接购货、转帐、存款、交易量、笔数、回扣率、收益等。(3) 业务：卡类、备用金、保证金、金卡、普通卡、公司卡、个人卡、员工卡、借记卡、外卡、手续费、有效、授权、商户、设备、活动、有效等。4、主键一个主键有一个表中的一个或多个字段构成，它唯一地标识了该表中的每一行，也既在被定义为主键的一个或多个列上建立一个唯一性索引。在本系统中，可以定义为主键的有：分支行号、帐号、卡号、商户号、POS号、交易类型、卡类别等。5、外键在一个表中被定义为主键的列如果被包含在另一个不同的表中，该列就被称为外键。注意只有在一个表中被定义为主键一部分的列才能被引用为外键。换一种说法，外键就是存储在另一个表中的该表的主键。四、系统结构图及业务数据流图1、系统结构图BCS信用卡业务包括四个业务系统，分别是长城卡管理系统BCS，商户POS数据采集系统Cardpool，异地网络授权系统NAS及电话自动语音授权系统JetVoice。DWBOS代表数据仓库分析系统。NASCardpoolJetVoiceSCOUNIXInformixSCOUNIX流式文件容错系统容错文件报表文件WindowsNTSQL ServerTCP/IPWindows NTCOLDOracle服务器DWBOS自动授权2、数据流图 国外 外币Cardpool 总行外卡信息卡部业务长城卡人民币本地卡数据库表人民币 输出报表浏览器 BOOracle BCSJetVoice本地卡本地卡电话授权外地卡授权信息本地卡外地卡NAS本地卡总行外地卡外地发卡行外地卡本地卡本地卡NAS外地卡本地卡五、源数据表结构1、BCS系统表1：DAT_CARD_INFO（持卡人信息）表类型：维表/日表更新类型：新添加、有修改生存期：每日更新（前一个交易日的表名变为.save）KEY值：卡号、卡状态、持卡人职务、单位所有制字段名称卡号当前卡状态卡状态设置日期有效期持卡人姓名持卡人卡面英文名称英文代码Card_noCard_statusStatus_dateExp_dateCardholderEmboss_name数据类型CharCharDateDateCharChar长度1612020说明1.开卡2.领卡3.挂失4.补卡5.止付6.换卡0.清卡YYMMDD