适用轨道交通AFC系统数据仓库技术的研究及应用
|
摘要
数据仓库是面向主题的、集成的、稳定的、随时间变化的数据集合,旨在支持管理者决策。数据仓库技术在国外已经取得广泛的应用,而在我国的应用属起步阶段。轨道交通AFC(自动售检票)系统随着我国城市轨道交通事业的发展,得到了相当大的发展,并且己经建立了较完善的联机事务处理系统。多年的应用也使得轨道交通运营公司积累了大量的数据,其中隐含着大量有价值的信息。如何利用这些数据,深层次地挖掘信息,为运营决策服务,己成为轨道交通运营公司的当务之急。
作者通过查阅资料了解了国内轨道交通行业在数据仓库技术和数据挖掘分析应用方面的现状,发现该行业信息化基础建设己经做得比较好,AFC系统也正在广泛应用,其数据库中存储了大量的数据。但由于各种原因,在数据仓库技术应用和数据分析方面一直处在观望状态,没有真正开展起来。
本文分析了这种现状,针对应用上的问题,较为详细介绍了数据仓库方面的知识,如数据仓库的定义、结构,ETL(抽取、转换和装载)的定义、工具和应用过程,以北京和南京轨道交通AFC系统为基础,研究分析在该业务系统上建立数据仓库的一些过程和技术。
本文介绍了数据仓库技术的发展现状和基本原理以及构建数据仓库的相关技术。根据AFC的特殊情况解决了建立数据仓库系统的基本问题,设计建立了符合DSS(Decision Support System)分析需求的数据仓库模型,重点提出并完成了两种针对AFC系统的ETL程序,实现了在数据仓库多维模型上的在线分析和数据分析报表展现,为数据仓库在轨道交通运营公司的应用提供了基本理论依据和解决方案。
最后对系统进行了测试,系统运行平稳,性能良好,表明设计的ETL工具可较好的完成数据仓库的ETL需求,且具有易于使用,灵活性强的特点。
Data warehouse is a data set which is subject-oriented, integrated, steady and time-variant, it is suitable for manager to support decisions. Data warehouse technology has been abroad made a wide range of applications, and its application in China just belongs to start. Automatic fare collection (AFC) system of rail transit has been considerably developedt with city's rail transit development in China, and the on line transaction processing system more completed has been builded. Large amounts of data have been accumulated of AFC operation company of rail transit after application in many years, which implied a large number of valuable information. How to use these data to mine informations in depth for servicing the operation decisions is becoming the important things at present of the operation company of rail transit.
Through refering to the materials to understand the application status at present of the data warehouse technology and data mining analysis of rail transit industry in China, finding the infrastructure of the information system has been doing well of the industry, AFC system is also widely used, and its database has stored a large number of data. But for a variety of reasons, the status is still in a wait-and-see in the aspects of technology applications of data warehouse and data analysis, and without really carrying out.
This paper analyses the status and makes a detailed introduction of the knowledges on data warehouse, such as the definition of the data warehouse, structure, ETL (extract, transform, loading), ETL tools and application process. In terms of the basis of the AFC systems in Beijing and Nanjing rail transit, the author had researched of and analysed some process and relative techniques to building a data warehouse on the business systems.
The paper introduces the technique development status at present and basic principles of data warehouse as well as the relative thchniques to construct a data warehouse. In terms of the special circumstances of AFC, the author resolves the basic problems of constructing data warehouse, designs and establishes a data warehouse model that is suitable for the analysis demands of Decision Support System (DSS), proposes and completes in highlight two sets of ETL program aimed at the AFC systems, implements the on-line analysis and data analysis report displays on multi-dimensional data warehouse model, provids basic theorys and solutions on the application of data warehouse for operation company of rail transit.
Finally, the system has been tested. The system runs smoothly, and has good performance. It proves that the ETL tools designed can complete ETL requirements of the data warehouse very well, and has features of easy-to-use and good flexibility.
作者通过查阅资料了解了国内轨道交通行业在数据仓库技术和数据挖掘分析应用方面的现状,发现该行业信息化基础建设己经做得比较好,AFC系统也正在广泛应用,其数据库中存储了大量的数据。但由于各种原因,在数据仓库技术应用和数据分析方面一直处在观望状态,没有真正开展起来。
本文分析了这种现状,针对应用上的问题,较为详细介绍了数据仓库方面的知识,如数据仓库的定义、结构,ETL(抽取、转换和装载)的定义、工具和应用过程,以北京和南京轨道交通AFC系统为基础,研究分析在该业务系统上建立数据仓库的一些过程和技术。
本文介绍了数据仓库技术的发展现状和基本原理以及构建数据仓库的相关技术。根据AFC的特殊情况解决了建立数据仓库系统的基本问题,设计建立了符合DSS(Decision Support System)分析需求的数据仓库模型,重点提出并完成了两种针对AFC系统的ETL程序,实现了在数据仓库多维模型上的在线分析和数据分析报表展现,为数据仓库在轨道交通运营公司的应用提供了基本理论依据和解决方案。
最后对系统进行了测试,系统运行平稳,性能良好,表明设计的ETL工具可较好的完成数据仓库的ETL需求,且具有易于使用,灵活性强的特点。
Data warehouse is a data set which is subject-oriented, integrated, steady and time-variant, it is suitable for manager to support decisions. Data warehouse technology has been abroad made a wide range of applications, and its application in China just belongs to start. Automatic fare collection (AFC) system of rail transit has been considerably developedt with city's rail transit development in China, and the on line transaction processing system more completed has been builded. Large amounts of data have been accumulated of AFC operation company of rail transit after application in many years, which implied a large number of valuable information. How to use these data to mine informations in depth for servicing the operation decisions is becoming the important things at present of the operation company of rail transit.
Through refering to the materials to understand the application status at present of the data warehouse technology and data mining analysis of rail transit industry in China, finding the infrastructure of the information system has been doing well of the industry, AFC system is also widely used, and its database has stored a large number of data. But for a variety of reasons, the status is still in a wait-and-see in the aspects of technology applications of data warehouse and data analysis, and without really carrying out.
This paper analyses the status and makes a detailed introduction of the knowledges on data warehouse, such as the definition of the data warehouse, structure, ETL (extract, transform, loading), ETL tools and application process. In terms of the basis of the AFC systems in Beijing and Nanjing rail transit, the author had researched of and analysed some process and relative techniques to building a data warehouse on the business systems.
The paper introduces the technique development status at present and basic principles of data warehouse as well as the relative thchniques to construct a data warehouse. In terms of the special circumstances of AFC, the author resolves the basic problems of constructing data warehouse, designs and establishes a data warehouse model that is suitable for the analysis demands of Decision Support System (DSS), proposes and completes in highlight two sets of ETL program aimed at the AFC systems, implements the on-line analysis and data analysis report displays on multi-dimensional data warehouse model, provids basic theorys and solutions on the application of data warehouse for operation company of rail transit.
Finally, the system has been tested. The system runs smoothly, and has good performance. It proves that the ETL tools designed can complete ETL requirements of the data warehouse very well, and has features of easy-to-use and good flexibility.
引文
[1]赵时旻,王绍银,苏厚勤等编著.轨道交通自动售检票系统.同济大学出版社,2007年5月第一版
[2]W.H.Inmon.Building the Data Warehouse.John Wiley & Sons,Inc.2002
[3]刘翔.数据仓库与数据挖掘技术.上海交通大学出版社2005
[4[美]保罗.克莱门茨等著.孙学涛等译.软件架构实践,清华大学出版社,2003
[5]王珊等编著.数据仓库技术与联机分析处理.科学出版社,1998
[6]Jiawei Han.OLAP mining:an integration of OLAP with data mining,The 1997 IFIP Conf on Data Semantics(DS-7),Leysin,Switzerland,1997
[7]彭木根.数据仓库技术与实现.电子工业出版社,2002
[8]高洪深.决策支持系统(DSS)理论,方法,案例(第二版).清华大学出版社,2000
[9]Z.Bellahsene.Schema evolution in data warehouse,Knowledge and Information Systems,Springer-Verlag London Ltd.April 2002
[10]刘东波.数据仓库技术的现状与未来.微型机与应用,2000,7
[11]邵峰晶.数据挖掘原理与算法.中国水利水电出版社2003
[12]Efraim Turban,Jay E.Aronson.Decision Support System and Intelligent Systems.清华大学出版社,2000,4
[13]Larissa T.Moss,Shaku Atre.Business Intelligence Roadmap:The complete project Life cycle for Decision-support Applications,New York:Addison Wesley,2003
[14]杨光等.OLAP技术及其发展.计算机应用研究,1999(7)
[15]Panos Vassiliedi.Data Warehouse Process Management,Information systems 26(2001)
[16]W.H.Inmon.Data Warehouse Architecture.http://www.billinmon.Cona,1999
[17]康博创作室编著.SQL Server 2000数据仓库设计和使用指南.清华大学出版社,2001,4
[18]Ralph Kimball Laura Reeves,Margy Ross Warren Thornthwaite著.肖红,王永红等译.数据仓库生命周期工具箱:设计、开发和部置数据仓库的专家方法.电子工业出版社
[19]Kimball R.The Data Warehouse Toolkit:The Complete Guide to Dimensional Modeling,2~(nd)Edition,John Wiley & Sons,Inc,2002
[20]M.Golfarelli,D.Maio,S.Rizzi.Conceptual design of data warehouse from E/R schemes[J],In Proc,HICCSS-31,V Ⅱ,Kona,Hawaii,1998,3
[21]Jiawei,Han.Micheline Kamber著.范明,孟小峰等译.数据挖掘概念与技术.机械工业出版社,2001,8
[22]熊忠阳,张玉芳,吴中福.数据仓库数据加载技术.重庆大学学报(自然科学版),2002,25(2)
[23]朱焱.浅论数据抽取、净化和转换工具.计算机应用,2000
[24]W.H.Inmon.Integration and Transformation.http://www.Billinmon.com1999
[25]Panos Vassiliadis,Conceptual Modeling for ETL Process,In Proc,5sth International workshop on Data warehousing and OLAP(DOLAP 2002),McLean,VA,USA Novembers,2000
[26]苏厚勤,苏金泉.三层计算构架报表系统的技术实现.扬州职业大学学报,2006,10(1).
[27]闪四清.Microsoft SQL Server 2000实用教程.人民邮电出版社,2000,12
[28]Bulusu Lakshman.Oracle 9i PL/SQL开发人员指南.清华大学出版社,2004
[29]王能斌,数据库系统原理.电子工业出版社,2000
[30]冯娟,赵时曼,苏厚勤,苏金泉.AFC系统数据库元数据信息中文描述的研究及应用.计算机科学,2006,33(7)
[31]陈弦,陈松乔.基于数据仓库的通ET工具的设计与实现.计算机应用研究,2004,8
[32]王海亮,等.精通Oracle 10g Pro~*C/c++编程.中国水利水电出版社,2005
[33]SQL Server 2005在线帮助
[34]Brian Knight.Professional SQL Server 2005 Integration Service.Wiley Publishing,Inc.2006.
[35]Paul Nielsen著,刘瑞,陈微等译.Micosoft SQL Server 2000宝典.中国铁道出版社.2004.3
[36]http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/consyscourse/SQLServer2005.aspx
[37]喻钢,周定康.联机分析处理(OLAP)技术的研究.计算机应用,2001,11
[38]杨光,张雷,艾波.OLAP技术与其发展.计算机应用研究,1999,7.
[39]李泽海.数据仓库中多维数据处理与查询相关技术的研究.吉林大学博士学位论文,2006
[40]Chaudhuri S,Dayal U.An over view of data warehouse and OLAP technology.ACM SIGMOD Record,1997,26(1)
[41]王晓林.运用数据仓库技术建设银行的MIS.计算机系统应用,1998
[42]Dava Steams.OFFICE 2000 WEB COMPONENTS编程技术内幕,希望图书创 作室译.希望电子出版社,2000
[43]Paul C.Jorgcnsen著.韩柯,杜旭涛译.软件测试.机械工业出版设,2002
[44]柳纯录主编.软件评测师教程.清华大学出版社.2005
[2]W.H.Inmon.Building the Data Warehouse.John Wiley & Sons,Inc.2002
[3]刘翔.数据仓库与数据挖掘技术.上海交通大学出版社2005
[4[美]保罗.克莱门茨等著.孙学涛等译.软件架构实践,清华大学出版社,2003
[5]王珊等编著.数据仓库技术与联机分析处理.科学出版社,1998
[6]Jiawei Han.OLAP mining:an integration of OLAP with data mining,The 1997 IFIP Conf on Data Semantics(DS-7),Leysin,Switzerland,1997
[7]彭木根.数据仓库技术与实现.电子工业出版社,2002
[8]高洪深.决策支持系统(DSS)理论,方法,案例(第二版).清华大学出版社,2000
[9]Z.Bellahsene.Schema evolution in data warehouse,Knowledge and Information Systems,Springer-Verlag London Ltd.April 2002
[10]刘东波.数据仓库技术的现状与未来.微型机与应用,2000,7
[11]邵峰晶.数据挖掘原理与算法.中国水利水电出版社2003
[12]Efraim Turban,Jay E.Aronson.Decision Support System and Intelligent Systems.清华大学出版社,2000,4
[13]Larissa T.Moss,Shaku Atre.Business Intelligence Roadmap:The complete project Life cycle for Decision-support Applications,New York:Addison Wesley,2003
[14]杨光等.OLAP技术及其发展.计算机应用研究,1999(7)
[15]Panos Vassiliedi.Data Warehouse Process Management,Information systems 26(2001)
[16]W.H.Inmon.Data Warehouse Architecture.http://www.billinmon.Cona,1999
[17]康博创作室编著.SQL Server 2000数据仓库设计和使用指南.清华大学出版社,2001,4
[18]Ralph Kimball Laura Reeves,Margy Ross Warren Thornthwaite著.肖红,王永红等译.数据仓库生命周期工具箱:设计、开发和部置数据仓库的专家方法.电子工业出版社
[19]Kimball R.The Data Warehouse Toolkit:The Complete Guide to Dimensional Modeling,2~(nd)Edition,John Wiley & Sons,Inc,2002
[20]M.Golfarelli,D.Maio,S.Rizzi.Conceptual design of data warehouse from E/R schemes[J],In Proc,HICCSS-31,V Ⅱ,Kona,Hawaii,1998,3
[21]Jiawei,Han.Micheline Kamber著.范明,孟小峰等译.数据挖掘概念与技术.机械工业出版社,2001,8
[22]熊忠阳,张玉芳,吴中福.数据仓库数据加载技术.重庆大学学报(自然科学版),2002,25(2)
[23]朱焱.浅论数据抽取、净化和转换工具.计算机应用,2000
[24]W.H.Inmon.Integration and Transformation.http://www.Billinmon.com1999
[25]Panos Vassiliadis,Conceptual Modeling for ETL Process,In Proc,5sth International workshop on Data warehousing and OLAP(DOLAP 2002),McLean,VA,USA Novembers,2000
[26]苏厚勤,苏金泉.三层计算构架报表系统的技术实现.扬州职业大学学报,2006,10(1).
[27]闪四清.Microsoft SQL Server 2000实用教程.人民邮电出版社,2000,12
[28]Bulusu Lakshman.Oracle 9i PL/SQL开发人员指南.清华大学出版社,2004
[29]王能斌,数据库系统原理.电子工业出版社,2000
[30]冯娟,赵时曼,苏厚勤,苏金泉.AFC系统数据库元数据信息中文描述的研究及应用.计算机科学,2006,33(7)
[31]陈弦,陈松乔.基于数据仓库的通ET工具的设计与实现.计算机应用研究,2004,8
[32]王海亮,等.精通Oracle 10g Pro~*C/c++编程.中国水利水电出版社,2005
[33]SQL Server 2005在线帮助
[34]Brian Knight.Professional SQL Server 2005 Integration Service.Wiley Publishing,Inc.2006.
[35]Paul Nielsen著,刘瑞,陈微等译.Micosoft SQL Server 2000宝典.中国铁道出版社.2004.3
[36]http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/consyscourse/SQLServer2005.aspx
[37]喻钢,周定康.联机分析处理(OLAP)技术的研究.计算机应用,2001,11
[38]杨光,张雷,艾波.OLAP技术与其发展.计算机应用研究,1999,7.
[39]李泽海.数据仓库中多维数据处理与查询相关技术的研究.吉林大学博士学位论文,2006
[40]Chaudhuri S,Dayal U.An over view of data warehouse and OLAP technology.ACM SIGMOD Record,1997,26(1)
[41]王晓林.运用数据仓库技术建设银行的MIS.计算机系统应用,1998
[42]Dava Steams.OFFICE 2000 WEB COMPONENTS编程技术内幕,希望图书创 作室译.希望电子出版社,2000
[43]Paul C.Jorgcnsen著.韩柯,杜旭涛译.软件测试.机械工业出版设,2002
[44]柳纯录主编.软件评测师教程.清华大学出版社.2005