工行山东分行综合数据集成平台的分析与设计
摘要
随着工商银行数据大集中的逐步深入,各种业务不断进行整合,各种系统的数据逐步集中到了总行,形成了上海、北京两大数据中心,各分行基本上不再保留任何主机的数据。在这种模式下,各行对于经营管理分析基本上依赖于报表返传数据以及总行开发的CS2002等系统。这种模式在数据大集中初期并没有任何问题,但是随着业务的发展和公司的上市,工商银行开始了精细化运作,对客户评介、员工考核越来越细致,经营决策需要考虑的因素越来越多,对数据的要求越来越高,简单的利用总行的CS2002等系统以及每天返传的报表数据进行统计分析无论是从数据质量的要求还是数据范围的要求以及数据的时效性要求等方面都远远不够,急需建立一套完整的数据平台为管理决策、客户评价、员工考核提供数据支持。
本文以员工综合业绩评价、客户评价两个具体的需求为突破口,对员工业绩评价、客户评价所需要的数据需求进行了详细的分析,确定了数据的来源,描述了系统的用例图、顺序图,设计了系统的网络架构、体系架构和逻辑架构,对数据库的表进行了ER表示,参照以往系统的开发经验和工行总行的要求选择了搭建平台所需要的软硬件环境。对Oracle数据库的表空间技术、表和索引分区机制、外部表技术进行了详细的研究,通过对各种ETL工具的比较,设计了一套适合工行山东分行的数据抽取导入(ETL)方案,实现了数据从数据源到数据仓库的抽取、清洗和转换的过程,为综合数据集成平台建立了数据池。通过对Oracle数据库的参数进行优化,设计了符合要求的数据老化、数据备份策略。
最后,建立了一个以SUSE Linux Enterprise 9为操作系统,ORACLE10G为后台数据库,使用WebSphere6.0作为应用服务器中间件的综合数据集成平台,实现对当前主机返传数据、国际卡业务数据、CM2002下载数据、PCM2003下载数据、网银下载数据、电话银行下载数据的加工、利用与存储,为工行山东分行的特色系统开发提供基础数据和开发环境,以满足我行目前客户评价、员工考核、经营数据分析的要求。
With the gradual deepening of concentration in data of Industrial and Commercial Bank of China, continuous conformity of various businesses, all kinds of systems' data are concentrating into headquarters gradually to form two data centers of Shanghai and Beijing, every branch generally can't keep data of host computer any more. Under this pattern, business and management analysis of various banks basically relies on feedback data from report forms and CS2002 system developed by headquarters and so on. In the beginning of data concentration, this kind of pattern doesn't exist any problems, but with the development of business and company listing, Industrial and Commercial Bank of China begins to particular operation, the assessment to employees and customers is becoming more and more specific, the business and decision-making need to consider more and more factors, with much higher requirements to data, simply applying CS2002 system developed by headquarters and feedback data from report forms everyday to analyze is not enough not only for requirements of data quality but also for the requirements of data degree as well as the time effective requirements of data, it is urgent to build up a suit of compete data platform to provide data support for management decision-making, customer assessment and employee examination.
This paper starts from the two specific demands of employee integrated performance assessment and customer assessment, and specifically analyzes the data needed by employee integrated performance assessment and customer assessment, and determines the source of data, and describes the example picture of system, order picture, designs the network structure, system structure and logic structure of this system, and makes ER indication to the database forms, and setups the software and hardware environments required by selecting platform according to the previous development experiences and the requirements of Industrial and Commercial Bank of China headquarters. This paper makes specific study on form space technology, form, index sub area mechanism and outside form technology of Oracle data, designs a suit of ETL scheme suitable for Shandong branch of Industrial and Commercial Bank of China which realizes the process of taking out data, cleaning data and transfer data from data source to database which builds data pool for integrated data platform. It designs the data outdate and backup strategy meeting demands by parameter optimization to Oracle database.
At last, it builds up a integrated data integration platform treating SUSE Linux Enterprise 9 as operation system, ORACLE10G as background database and WebSphere6.0 as application server middle components which can realize host computer returning data, international card business data, CM2002 downloading data, PCM2003 downloading data, electronic bank downloading data, the processing, application and storage of telephone bank downloading data, which develops basic data and development environment for the feature system development of Shandong branch of Industrial and Commercial Bank of China to meet the demands of current customer assessment, employee examination and business data analysis in our bank.
本文以员工综合业绩评价、客户评价两个具体的需求为突破口,对员工业绩评价、客户评价所需要的数据需求进行了详细的分析,确定了数据的来源,描述了系统的用例图、顺序图,设计了系统的网络架构、体系架构和逻辑架构,对数据库的表进行了ER表示,参照以往系统的开发经验和工行总行的要求选择了搭建平台所需要的软硬件环境。对Oracle数据库的表空间技术、表和索引分区机制、外部表技术进行了详细的研究,通过对各种ETL工具的比较,设计了一套适合工行山东分行的数据抽取导入(ETL)方案,实现了数据从数据源到数据仓库的抽取、清洗和转换的过程,为综合数据集成平台建立了数据池。通过对Oracle数据库的参数进行优化,设计了符合要求的数据老化、数据备份策略。
最后,建立了一个以SUSE Linux Enterprise 9为操作系统,ORACLE10G为后台数据库,使用WebSphere6.0作为应用服务器中间件的综合数据集成平台,实现对当前主机返传数据、国际卡业务数据、CM2002下载数据、PCM2003下载数据、网银下载数据、电话银行下载数据的加工、利用与存储,为工行山东分行的特色系统开发提供基础数据和开发环境,以满足我行目前客户评价、员工考核、经营数据分析的要求。
With the gradual deepening of concentration in data of Industrial and Commercial Bank of China, continuous conformity of various businesses, all kinds of systems' data are concentrating into headquarters gradually to form two data centers of Shanghai and Beijing, every branch generally can't keep data of host computer any more. Under this pattern, business and management analysis of various banks basically relies on feedback data from report forms and CS2002 system developed by headquarters and so on. In the beginning of data concentration, this kind of pattern doesn't exist any problems, but with the development of business and company listing, Industrial and Commercial Bank of China begins to particular operation, the assessment to employees and customers is becoming more and more specific, the business and decision-making need to consider more and more factors, with much higher requirements to data, simply applying CS2002 system developed by headquarters and feedback data from report forms everyday to analyze is not enough not only for requirements of data quality but also for the requirements of data degree as well as the time effective requirements of data, it is urgent to build up a suit of compete data platform to provide data support for management decision-making, customer assessment and employee examination.
This paper starts from the two specific demands of employee integrated performance assessment and customer assessment, and specifically analyzes the data needed by employee integrated performance assessment and customer assessment, and determines the source of data, and describes the example picture of system, order picture, designs the network structure, system structure and logic structure of this system, and makes ER indication to the database forms, and setups the software and hardware environments required by selecting platform according to the previous development experiences and the requirements of Industrial and Commercial Bank of China headquarters. This paper makes specific study on form space technology, form, index sub area mechanism and outside form technology of Oracle data, designs a suit of ETL scheme suitable for Shandong branch of Industrial and Commercial Bank of China which realizes the process of taking out data, cleaning data and transfer data from data source to database which builds data pool for integrated data platform. It designs the data outdate and backup strategy meeting demands by parameter optimization to Oracle database.
At last, it builds up a integrated data integration platform treating SUSE Linux Enterprise 9 as operation system, ORACLE10G as background database and WebSphere6.0 as application server middle components which can realize host computer returning data, international card business data, CM2002 downloading data, PCM2003 downloading data, electronic bank downloading data, the processing, application and storage of telephone bank downloading data, which develops basic data and development environment for the feature system development of Shandong branch of Industrial and Commercial Bank of China to meet the demands of current customer assessment, employee examination and business data analysis in our bank.
引文
[1]吴蔚.跨世纪的战略工程-中国工商银行数据大集中历程回顾.中国城市金融,2007年,03期,6+23-25
[2]于力.ETL关键技术研究:[学位论文].东南大学:东南大学,2005
[3]罗兵.基于元数据控制的ETL系统应用研究:[学位论文]西南大学:西南大学,2006
[4]吕洪敏.基于Oracle数据仓库应用技术的研究与实现:[学位论文].武汉科技大学:武汉科技大学,2007
[5]周宏广,周继承.数据 ETL 工具通用框架设计.计算机应用,2003(12):96-98
[6]李志辉.ETL实施的数据质量问题研究.电脑知识与技术(学术交流),2006年,26期:25+121
[7]张寿文,娄燕飞.海量数据并行加载策略同步方法的设计与实现.空军雷达学院学报.2008年3月:48-51
[8]周春梅.数据备份方法及灾难恢复探讨.通讯世界,2005年,07期:33-34
[9]周俊辉.工行大连分行信息资源整合研究:[学位论文].大连理工大学:大连理工大学,2006
[10]王智.科技创新铸就工商银行核心竞争力.经济日报,2008年01月31日
[11]毛彧.银行数据仓库系统中ETL的总体设计与实现.信息与电子工程,2007,(04),56-59
[12]H.Inmon,Building the Data Warehouse,Third Edition.Manhattan:John Wiley&Sons,2002,24-27
[13]程跟上.基于公共仓库模型的ETL系统的研究和应用.[学位论文]南京航空航天大学:南京航空航天大学,2005
[14]Gary Craig,Peter Jakab.IBM WebSphere Web应用开发[M].北京:机械工业出版社,2004.
[15]李春艳.基于数据仓库的工商银行客户关系管理研究:[学位论文].哈尔滨工业大学:哈尔滨工业大学,2003
[16]孔维强.基于数据仓库的数据挖掘在银行业务中的应用研究:[学位论文].武汉大学:武汉大学,2003
[17]田芳,刘震.数据仓库清洗技术讨论.青海师范大学学报(自然科学版),2005(4):50-53.
[18]彭银桥,甘元驹等.数据ETL过程中实体识别方法[J].信息技术,2005(2):22-24.
[19]谭立球,费耀平,李建华.一个异构数据源集成系统的实现[J].计算机科学,2004,31(9A):130-133.
[19]中科永联高级技术培训中心,www.itisedu.com
[20]Oracle.Oracle Business Intelligence Discoverer Desktop User' s Guide10g(10.2)[M].Oracle,2005,8
[21]丁景德著,基于数据仓库的银行商务智能系统的研究和数据挖掘工具的开发:[学位论文].北京理工大学:北京理工大学,2002
[22]丁坷,王志坚.基于JZEE服务器的企业级应用解决方案[1].微计算机信息,2006,12(043):133-135
[23]鲍玉斌,孙焕良,冷芳玲等著.数据仓库环境下以用户为中心的数据清洗过程模型[J],计算机科学,2004,31(5):52-55
[24]武剑.数据集成平台中ETL的研究与设计:[学位论文].华北电力大学:华北电力大学,2007
[25]张宁,贾自艳.数据仓库中ETL技术的研究.计算机工程与应用,2002,38(24):213-216
[26].Eri,SPerley著,陈武译.企业数据仓库规划建立与实现,北京:人民邮电出版社,2000
[27].佘春红.数据仓库及其相关技术综述.长江大学学报(自然科学版),2004,1:23-27
[28]朱焱.浅论数据抽取、净化和转换工具.计算机应用,2000,20(4):1-3
[29]江放,李海刚,高国安.基于数据仓库的数据挖掘及其在决策系统中的应用[J],现代计算机,1999,22(1):32-35
[30]Michael Lipton.http://www.ibm.com/developerworks/cn/java/j-quartz/. 2006.12.18
[31]Time K.ShenandE.J.DelP,A SPatial TemPoral Parallel APproaeh For Real- MPEG Video ComPression,Proeeedings of the 25th International Confereneeon Parallel Proeessing,August13-15,200
[32]RonBen-Natan,ori Sasson.IBM Websphere applications server.北京:清华大学出版社,2003
[2]于力.ETL关键技术研究:[学位论文].东南大学:东南大学,2005
[3]罗兵.基于元数据控制的ETL系统应用研究:[学位论文]西南大学:西南大学,2006
[4]吕洪敏.基于Oracle数据仓库应用技术的研究与实现:[学位论文].武汉科技大学:武汉科技大学,2007
[5]周宏广,周继承.数据 ETL 工具通用框架设计.计算机应用,2003(12):96-98
[6]李志辉.ETL实施的数据质量问题研究.电脑知识与技术(学术交流),2006年,26期:25+121
[7]张寿文,娄燕飞.海量数据并行加载策略同步方法的设计与实现.空军雷达学院学报.2008年3月:48-51
[8]周春梅.数据备份方法及灾难恢复探讨.通讯世界,2005年,07期:33-34
[9]周俊辉.工行大连分行信息资源整合研究:[学位论文].大连理工大学:大连理工大学,2006
[10]王智.科技创新铸就工商银行核心竞争力.经济日报,2008年01月31日
[11]毛彧.银行数据仓库系统中ETL的总体设计与实现.信息与电子工程,2007,(04),56-59
[12]H.Inmon,Building the Data Warehouse,Third Edition.Manhattan:John Wiley&Sons,2002,24-27
[13]程跟上.基于公共仓库模型的ETL系统的研究和应用.[学位论文]南京航空航天大学:南京航空航天大学,2005
[14]Gary Craig,Peter Jakab.IBM WebSphere Web应用开发[M].北京:机械工业出版社,2004.
[15]李春艳.基于数据仓库的工商银行客户关系管理研究:[学位论文].哈尔滨工业大学:哈尔滨工业大学,2003
[16]孔维强.基于数据仓库的数据挖掘在银行业务中的应用研究:[学位论文].武汉大学:武汉大学,2003
[17]田芳,刘震.数据仓库清洗技术讨论.青海师范大学学报(自然科学版),2005(4):50-53.
[18]彭银桥,甘元驹等.数据ETL过程中实体识别方法[J].信息技术,2005(2):22-24.
[19]谭立球,费耀平,李建华.一个异构数据源集成系统的实现[J].计算机科学,2004,31(9A):130-133.
[19]中科永联高级技术培训中心,www.itisedu.com
[20]Oracle.Oracle Business Intelligence Discoverer Desktop User' s Guide10g(10.2)[M].Oracle,2005,8
[21]丁景德著,基于数据仓库的银行商务智能系统的研究和数据挖掘工具的开发:[学位论文].北京理工大学:北京理工大学,2002
[22]丁坷,王志坚.基于JZEE服务器的企业级应用解决方案[1].微计算机信息,2006,12(043):133-135
[23]鲍玉斌,孙焕良,冷芳玲等著.数据仓库环境下以用户为中心的数据清洗过程模型[J],计算机科学,2004,31(5):52-55
[24]武剑.数据集成平台中ETL的研究与设计:[学位论文].华北电力大学:华北电力大学,2007
[25]张宁,贾自艳.数据仓库中ETL技术的研究.计算机工程与应用,2002,38(24):213-216
[26].Eri,SPerley著,陈武译.企业数据仓库规划建立与实现,北京:人民邮电出版社,2000
[27].佘春红.数据仓库及其相关技术综述.长江大学学报(自然科学版),2004,1:23-27
[28]朱焱.浅论数据抽取、净化和转换工具.计算机应用,2000,20(4):1-3
[29]江放,李海刚,高国安.基于数据仓库的数据挖掘及其在决策系统中的应用[J],现代计算机,1999,22(1):32-35
[30]Michael Lipton.http://www.ibm.com/developerworks/cn/java/j-quartz/. 2006.12.18
[31]Time K.ShenandE.J.DelP,A SPatial TemPoral Parallel APproaeh For Real- MPEG Video ComPression,Proeeedings of the 25th International Confereneeon Parallel Proeessing,August13-15,200
[32]RonBen-Natan,ori Sasson.IBM Websphere applications server.北京:清华大学出版社,2003