数据仓库技术在ERP中的应用研究
|
摘要
随着信息技术的快速发展，数据库系统的应用己迅速从传统的数据处理发展到空间的、时间的信息处理，多媒体信息处理，办公信息库，科学的、面向应用的数据库和基于Internet的数据库应用。随着数据库技术的广泛应用，信息化程度的深入，各个行业数据库中存储的数据量急剧增长。如果没有对海量数据进行科学分析的能力，美国零售业巨人沃尔玛的老板再精明，也绝对想不到“啤酒和尿布”这两个风马牛不相及的东西之间还有着千丝万缕的联系。而将它们放在一起竟然增加了啤酒的销量，可见对数据仓库进行数据分析的巨大威力。2000年全球前500个大企业中有50％的企业已经实施数据仓库或部门级数据集市。
数据仓库技术是信息技术领域的一门新兴技术。采用数据仓库技术，可以充分利用企业内部已有的海量数据资源，从中挖掘出有价值的知识和规则来支持企业决策，将客观详实的经验数据与决策者自身的宝贵经验有效的结合起来，提高企业的市场竞争力。
ERP作为企业信息化的重要工具，在我国企业得到越来越广泛的应用。但是，当前国内ERP系统的数据分析能力很多都不够强大，但其数据库的架构与数据分析能力是决定其性能的重要指标。面向ERP的软件数据分析功能被称为“商业智能”，其主要功能是对ERP系统积累的数据进行分析处理，形象地说就是帮助用户发现ERP系统积累的数据的潜在价值。
本文以河南华晶超硬材料有限公司(简称河南华晶)ERP项目为依托，首先阐述了在具体的软件实现过程中所要用到的数据仓库技术，根据对河南华晶公司的实际调研，规划了华晶ERP数据仓库的需求分析和总体设计。然后通过实际代码实现和图例说明华晶ERP物理数据库的建立过程以及华晶ERP数据仓库的OLAP的设计方法与实现过程。本文研究的内容主要包括下面几点：
1．针对ERP和数据仓库各自的特点，提出了ERP和数据仓库集成的模型，并把数据仓库技术应用到ERP系统中。
2．根据对华晶公司的实际调研充分了解公司的生产、销售、采购和财务等方面的业务流程，总结了华晶ERP数据仓库的需求分析并进行总体设计。
3．根据企业具体的需求，设计和实现了华晶ERP物理数据库，同时通过系统表、触发器和存储过程的具体分析用MS SQL SERVER来实现ERP系统。
4．把分析型数据从事务处理环境中提取出来，按照决策支持系统处理的需要进行重新组织，建立单独的分析处理环境。
5．通过OLAP工具和MDX语言在华晶ERP数据仓库上建立了以生产、销售和库存为主题域的多维立方体分析，建立了在ERP系统基础上的决策分析系统。
With the rapid development of information technology, the application of the database system has rapidly developed from the traditional data processing to the spacial and temporal information processing, multimedia information processing, information library of the office, scientific and application-oriented database and the database application based on the Internet. With the abroad application of the database and information, the amount of the data stored in the database increased very quickly. Without the ability to analyse the large quantities of data scientifically, the boss of Wal-Mart could not consider the fact that there are much relations between the beer and the napkin although he is smart enough. But when the two kinds of goods were placed together to sell, the amount of the sale of the beer was improved. So we can see that Data Warehouse can do much help to the data analysis.
Data Warehouse is a new technology in the field of information. Using the Data Warehouse, we can make use of the abundant data resources in the enterprise to support the decision about the enterprise. It can also combine the experience from the objective data and from the person who make the decisions.
As the important tools of management in the enterprise, ERP is applied more widely step by step. But the ability of data analysis in ERP is not stronger enough. Furthermore the structure and ability in data analysis of database is the key parameter to the capability. The ability of data analysis of software for ERP is called Business Intelligence (BI) whose function is to deal with and analyse the data stored in ERP system, i.e. to help the user to find the potential value of the data stored in ERP.
This thesis is based on the ERP project of Huajing Co., ltd. in Henan Province. Firstly, the technology of Data Warehouse that is used in the process of programming of HJ-ERP is introduced; secondly, the integration of ERP and Data Warehouse is discussed. Furthermore research and requirements analysis of the Data Warehouse in HJ-ERP based on the practical investigantion is described. At last, the process of establishing the physical database and the methods of designing the OLAP in Data Warehouse of HJ-ERP is introduced through the software code and diagram. The main contents of this thesis are outlined as follows,
1. The model of the integration about ERP and Data Warehouse is put forward based on the each character of ERP and Data Warehouse. And the Data Warehouse is applied to ERP system.
2. The requirement analysis and the whole designation of Data Warehouse in
HJ-ERP is introduced which is based on the practical investigation on the manufacture, sale, purchase and finance etc. in the company.
3. The physical database of HJ-ERP is designed which is based on the practical demand of the company. And the ERP system is realized through MS SQL Server that is based on the analysis of the system table, trigger and the stored procedure.
4. The data of analysis type is extracted from environment of transaction processing and is reorganized according to the requirement of Data Decision Support (DSS). And at the same time the environment of analysis processing is established separately.
5. The analysis of multidimensional cube which is established on the subject domain of manufacture, sale and stock is applied to the Data Warehouse in HJ-ERP through the tools of OLAP and the MDX language. Furthermore the DSS is established based on HJ-ERP.
数据仓库技术是信息技术领域的一门新兴技术。采用数据仓库技术，可以充分利用企业内部已有的海量数据资源，从中挖掘出有价值的知识和规则来支持企业决策，将客观详实的经验数据与决策者自身的宝贵经验有效的结合起来，提高企业的市场竞争力。
ERP作为企业信息化的重要工具，在我国企业得到越来越广泛的应用。但是，当前国内ERP系统的数据分析能力很多都不够强大，但其数据库的架构与数据分析能力是决定其性能的重要指标。面向ERP的软件数据分析功能被称为“商业智能”，其主要功能是对ERP系统积累的数据进行分析处理，形象地说就是帮助用户发现ERP系统积累的数据的潜在价值。
本文以河南华晶超硬材料有限公司(简称河南华晶)ERP项目为依托，首先阐述了在具体的软件实现过程中所要用到的数据仓库技术，根据对河南华晶公司的实际调研，规划了华晶ERP数据仓库的需求分析和总体设计。然后通过实际代码实现和图例说明华晶ERP物理数据库的建立过程以及华晶ERP数据仓库的OLAP的设计方法与实现过程。本文研究的内容主要包括下面几点：
1．针对ERP和数据仓库各自的特点，提出了ERP和数据仓库集成的模型，并把数据仓库技术应用到ERP系统中。
2．根据对华晶公司的实际调研充分了解公司的生产、销售、采购和财务等方面的业务流程，总结了华晶ERP数据仓库的需求分析并进行总体设计。
3．根据企业具体的需求，设计和实现了华晶ERP物理数据库，同时通过系统表、触发器和存储过程的具体分析用MS SQL SERVER来实现ERP系统。
4．把分析型数据从事务处理环境中提取出来，按照决策支持系统处理的需要进行重新组织，建立单独的分析处理环境。
5．通过OLAP工具和MDX语言在华晶ERP数据仓库上建立了以生产、销售和库存为主题域的多维立方体分析，建立了在ERP系统基础上的决策分析系统。
With the rapid development of information technology, the application of the database system has rapidly developed from the traditional data processing to the spacial and temporal information processing, multimedia information processing, information library of the office, scientific and application-oriented database and the database application based on the Internet. With the abroad application of the database and information, the amount of the data stored in the database increased very quickly. Without the ability to analyse the large quantities of data scientifically, the boss of Wal-Mart could not consider the fact that there are much relations between the beer and the napkin although he is smart enough. But when the two kinds of goods were placed together to sell, the amount of the sale of the beer was improved. So we can see that Data Warehouse can do much help to the data analysis.
Data Warehouse is a new technology in the field of information. Using the Data Warehouse, we can make use of the abundant data resources in the enterprise to support the decision about the enterprise. It can also combine the experience from the objective data and from the person who make the decisions.
As the important tools of management in the enterprise, ERP is applied more widely step by step. But the ability of data analysis in ERP is not stronger enough. Furthermore the structure and ability in data analysis of database is the key parameter to the capability. The ability of data analysis of software for ERP is called Business Intelligence (BI) whose function is to deal with and analyse the data stored in ERP system, i.e. to help the user to find the potential value of the data stored in ERP.
This thesis is based on the ERP project of Huajing Co., ltd. in Henan Province. Firstly, the technology of Data Warehouse that is used in the process of programming of HJ-ERP is introduced; secondly, the integration of ERP and Data Warehouse is discussed. Furthermore research and requirements analysis of the Data Warehouse in HJ-ERP based on the practical investigantion is described. At last, the process of establishing the physical database and the methods of designing the OLAP in Data Warehouse of HJ-ERP is introduced through the software code and diagram. The main contents of this thesis are outlined as follows,
1. The model of the integration about ERP and Data Warehouse is put forward based on the each character of ERP and Data Warehouse. And the Data Warehouse is applied to ERP system.
2. The requirement analysis and the whole designation of Data Warehouse in
HJ-ERP is introduced which is based on the practical investigation on the manufacture, sale, purchase and finance etc. in the company.
3. The physical database of HJ-ERP is designed which is based on the practical demand of the company. And the ERP system is realized through MS SQL Server that is based on the analysis of the system table, trigger and the stored procedure.
4. The data of analysis type is extracted from environment of transaction processing and is reorganized according to the requirement of Data Decision Support (DSS). And at the same time the environment of analysis processing is established separately.
5. The analysis of multidimensional cube which is established on the subject domain of manufacture, sale and stock is applied to the Data Warehouse in HJ-ERP through the tools of OLAP and the MDX language. Furthermore the DSS is established based on HJ-ERP.
引文
[1]W.H.Inmon.Building the Data Warehouse，机械工业出版社，2000
[2]吴澄．《现代集成制造系统导论—概念、方法、技术和应用》，清华大学出版社，2002
[3]吉训仁．ERP的理念与线性规划．计算机世界．2000(39)
[4]胡家齐．黎小平．ERP在企业应用中存在的问题与对策．计算机周刊．1999(27)
[5]杨俊锋，董不明．商业智能系统的设计与实现．大连铁道学院学报．2002(3)
[6]Mike Gunderloy．《SQL Server 2000从入门到精通》，电子工业出版社，2001
[7]宋凯．张士峰．瞿兆荣．数据仓库及联机分析处理．计算机工程．1998(10)
[8]杨光．张雷．艾波．OLAP技术及其发展．计算机应用研究．1997(7)
[9]黄若波．左春．孙玉芳．基于Web环境下的OLAP技术的研究和实现．计算机工程．2000(10)
[10]叶德谦，马勤勇．优化MDX查询语句提高OLAP系统性能的研究．微处理机．2002(8)
[11]俞勇，茅维华．基于分布式数据库的OLTP应用程序开发平台．计算机工程．1997(9)
[12]Paul Gray Hugh J．Watson．Decision Support in the Data Warehouse．陈朔鹰，周志速等译北京理工大学出版社，2000．
[13]王珊等编著．数据仓库技术与联机分析处理．科学出版社，1998．
[14]沈镇林，罗伟其．姚国祥．基于WebOLTP的应用信息系统设计．华南理工大学学报(自然科学版)．1998(10)
[15]袁虹，何厚存．联机分析及数据仓库的建模技术．计算机应用研究．1999(12)
[16]高洪深．决策支持系统(DSS)理论．方法．案例(第二版)，清华大学出版社，2000．9
[17]刘震，王淑文，姜占华．DSS体系结构．吉林工业大学学报．1994(3)
[18]田增平，瞿兆荣．多数据源DSS的数据提取模型．计算机工程．1994(4)
[19]姚宇蓉，何厚存．数据仓库中的信息存取分析技术的研究．计算机应用研．1999(8)
[20]张忠能，尤毅，程伟宁等．设计数据仓库．上海交通大学学报．1998(10)
[21]程治刚，赵立军．数据仓库及其应用．现代计算机．1998(8)
[22]楼伟进，孔繁胜等．数据仓库与知识发现．计算机工程与应用．2000(10)
[23]杨晓，任清珍，苏灵．数据仓库、数据集市和数据挖掘．现代计算机．1998(1)
[24]李敏强，潘振江，寇纪淞．基于数据仓库技术的决策支持系统的研究与应用．系统工程理论与实践．1998(3)
[25]许向东，张全寿．数据仓库与数据发掘的应用．计算机系统应用．1998(4)
[26]任锦鸾，张闽，曾珍香．一种新型的数据管理系统——数据仓库．河北工业大学学报．1999(3)
[27]陈元，陈文伟．基于数据抽取器实现数据挖掘．计算机工程．2000(26)
[28]陶宏才．数据库原理与应用设计．西南交通大学出版社．2001
[29]宋凯，张士峰，瞿兆荣．数据仓库及联机分析处理．计算机工程．1999(10)
[30]何新贵．特种数据库技术．科学出版社．2000．
[31]马垣．关系数据库理论．清华大学出版社．1999
[32]刘惟一，田雯编著．数据模型．科学出版社．2001．
[33]Morse D.Isaac.Parallel Systems in the Data Warehouse .Prentic-Hall,1998
[34]许兆新．决策支持系统相关技术综述．计算机应用研究．2001(2)
[35]张宁，贾自艳，史忠植．数据仓库中ETL技术的研究．计算机工程与应用．2002(24)
[36]袁长河．基于数据仓库的决策支持系统研究与建设．计算机工程与应用．2001(16)
[37]刘瑶，孙玉芳．通用财务OLAP分析器的实现。计算机科学，2000(12)
[38]邓文华，梅志红．基于数据仓库的铁路物资管理决策支持系统的研究．华东交通大学学报．2000(2)
[39]戴超凡，邓苏，黄宏斌．刘青宝．DSS中数据管理新技术研究．计算机工程与应用．2000(12)
[40]Jesus Mena.Data Ming from the Ground up.Database Programming and Design, 1997,8(2):63—67
[41]杨光，张雷，艾波．OLAP技术及其发展．计算机应用研究．1999(7)
[42]李建中，李英姝．Efficient Aggregation Algorithms on Very Large Compressed Data Warehouses．Journal of Computer Science and Technology．2000(3)
[43]袁虹，何厚存．联机分析及数据仓库的建模技术．计算机应用研究．1999(12)
[44]刘杰，王永利．ERP系统中实现决策支持的方法研究．应用科技．2003(4)
[45]张禾．DCOM应用开发．现代计算机．1998(6)
[46]张晓东，李敬．C++Builder5程序设计数据库应用实务篇．中国铁道出版社．2001
[47]飞思科技产品研发中心。SQL Server 2000 OLAP服务设计与应用．电子工业出版社．2002
[48]骆文俊．黄上腾．MTS的资源管理模型及其应用．计算及工程．2000(7)
[49]张珺，缪逸，谢维平．基于DCOM的分布式应用建构．计算及工程1999(6)
[50]陆亦斌，王金中．DCOM技术在数据库系统中的应用．计算机应用．2000(5)
[51]叶德谦，马勤勇，叶柠．优化MDX查询语句提高OLAP系统性能的研究．微处理机．2002(3)
[52]陈卫华，符艳丽，罗亮，朱仲英．用ASP与MDX实现数据仓库的远程查询．微型电脑应用．2001(11)
[53]Hugh.J.Waston.The benefits of data warehousing why some organizations realize exceptional payoffs.Information & Management.2002(39) 491-502
[54]Scott.J.Mason.Integrating the warehousing and transportation functions of the supply chain.Transportation Research Part E.2003 (39) 141-151
[55]ED O'Donell.How information systems influence user decisions a research framework and literature review .Accounting Information System.2000(1) 178-203
[56]K.W.Chau.Application of data warehouse and Decision Support System in construction
management.Automaton in Construction.2002(12)213-224
[57]Jiawei Han,Micheline Kamber．数据挖掘概念与技术．机械工业出版社．2001．
[58]Bingi P., M.Shara, Godla K.Critical issues affecting an ERP implementation.Information systems management.1999:7-14.
[59]Leslie W.,S.Richard.The role of the CIO and IT function in ERE Communications of the ACM.2000(43):32-38.
[60]徐峰，陈铁军，王志霞．基于Mobile Agent的分布式PDM文档管理系统．中国制造业信息化．2003(9)
[61]Celeste See Pui Ng,Taizan Chan.A client-benefits oriented taxonomy of ERP maintenance.Information Systems Management Research Centre.2001(10):1-10
[62]Hsin Hsin Chang.A model of computerization of manufacturing systems an international study.Information&Management.39 (2002) 605-624
[63]D.Toney Liu,X.William Xu.A review of web-based product data management systems.Computers in Industry.44 (2001) 251-262
[64]Jan Olhager, Eric Selldin.Enterprise resource planning survey of Swedish manufacturing firms.European Journal of Operational Research.146 (2003) 365-373
[65]Daniel E.O'Leary.Knowledge management across the enterprise resource planning systems life cycle.Accouting Information System.3 (2002) 99-110
[2]吴澄．《现代集成制造系统导论—概念、方法、技术和应用》，清华大学出版社，2002
[3]吉训仁．ERP的理念与线性规划．计算机世界．2000(39)
[4]胡家齐．黎小平．ERP在企业应用中存在的问题与对策．计算机周刊．1999(27)
[5]杨俊锋，董不明．商业智能系统的设计与实现．大连铁道学院学报．2002(3)
[6]Mike Gunderloy．《SQL Server 2000从入门到精通》，电子工业出版社，2001
[7]宋凯．张士峰．瞿兆荣．数据仓库及联机分析处理．计算机工程．1998(10)
[8]杨光．张雷．艾波．OLAP技术及其发展．计算机应用研究．1997(7)
[9]黄若波．左春．孙玉芳．基于Web环境下的OLAP技术的研究和实现．计算机工程．2000(10)
[10]叶德谦，马勤勇．优化MDX查询语句提高OLAP系统性能的研究．微处理机．2002(8)
[11]俞勇，茅维华．基于分布式数据库的OLTP应用程序开发平台．计算机工程．1997(9)
[12]Paul Gray Hugh J．Watson．Decision Support in the Data Warehouse．陈朔鹰，周志速等译北京理工大学出版社，2000．
[13]王珊等编著．数据仓库技术与联机分析处理．科学出版社，1998．
[14]沈镇林，罗伟其．姚国祥．基于WebOLTP的应用信息系统设计．华南理工大学学报(自然科学版)．1998(10)
[15]袁虹，何厚存．联机分析及数据仓库的建模技术．计算机应用研究．1999(12)
[16]高洪深．决策支持系统(DSS)理论．方法．案例(第二版)，清华大学出版社，2000．9
[17]刘震，王淑文，姜占华．DSS体系结构．吉林工业大学学报．1994(3)
[18]田增平，瞿兆荣．多数据源DSS的数据提取模型．计算机工程．1994(4)
[19]姚宇蓉，何厚存．数据仓库中的信息存取分析技术的研究．计算机应用研．1999(8)
[20]张忠能，尤毅，程伟宁等．设计数据仓库．上海交通大学学报．1998(10)
[21]程治刚，赵立军．数据仓库及其应用．现代计算机．1998(8)
[22]楼伟进，孔繁胜等．数据仓库与知识发现．计算机工程与应用．2000(10)
[23]杨晓，任清珍，苏灵．数据仓库、数据集市和数据挖掘．现代计算机．1998(1)
[24]李敏强，潘振江，寇纪淞．基于数据仓库技术的决策支持系统的研究与应用．系统工程理论与实践．1998(3)
[25]许向东，张全寿．数据仓库与数据发掘的应用．计算机系统应用．1998(4)
[26]任锦鸾，张闽，曾珍香．一种新型的数据管理系统——数据仓库．河北工业大学学报．1999(3)
[27]陈元，陈文伟．基于数据抽取器实现数据挖掘．计算机工程．2000(26)
[28]陶宏才．数据库原理与应用设计．西南交通大学出版社．2001
[29]宋凯，张士峰，瞿兆荣．数据仓库及联机分析处理．计算机工程．1999(10)
[30]何新贵．特种数据库技术．科学出版社．2000．
[31]马垣．关系数据库理论．清华大学出版社．1999
[32]刘惟一，田雯编著．数据模型．科学出版社．2001．
[33]Morse D.Isaac.Parallel Systems in the Data Warehouse .Prentic-Hall,1998
[34]许兆新．决策支持系统相关技术综述．计算机应用研究．2001(2)
[35]张宁，贾自艳，史忠植．数据仓库中ETL技术的研究．计算机工程与应用．2002(24)
[36]袁长河．基于数据仓库的决策支持系统研究与建设．计算机工程与应用．2001(16)
[37]刘瑶，孙玉芳．通用财务OLAP分析器的实现。计算机科学，2000(12)
[38]邓文华，梅志红．基于数据仓库的铁路物资管理决策支持系统的研究．华东交通大学学报．2000(2)
[39]戴超凡，邓苏，黄宏斌．刘青宝．DSS中数据管理新技术研究．计算机工程与应用．2000(12)
[40]Jesus Mena.Data Ming from the Ground up.Database Programming and Design, 1997,8(2):63—67
[41]杨光，张雷，艾波．OLAP技术及其发展．计算机应用研究．1999(7)
[42]李建中，李英姝．Efficient Aggregation Algorithms on Very Large Compressed Data Warehouses．Journal of Computer Science and Technology．2000(3)
[43]袁虹，何厚存．联机分析及数据仓库的建模技术．计算机应用研究．1999(12)
[44]刘杰，王永利．ERP系统中实现决策支持的方法研究．应用科技．2003(4)
[45]张禾．DCOM应用开发．现代计算机．1998(6)
[46]张晓东，李敬．C++Builder5程序设计数据库应用实务篇．中国铁道出版社．2001
[47]飞思科技产品研发中心。SQL Server 2000 OLAP服务设计与应用．电子工业出版社．2002
[48]骆文俊．黄上腾．MTS的资源管理模型及其应用．计算及工程．2000(7)
[49]张珺，缪逸，谢维平．基于DCOM的分布式应用建构．计算及工程1999(6)
[50]陆亦斌，王金中．DCOM技术在数据库系统中的应用．计算机应用．2000(5)
[51]叶德谦，马勤勇，叶柠．优化MDX查询语句提高OLAP系统性能的研究．微处理机．2002(3)
[52]陈卫华，符艳丽，罗亮，朱仲英．用ASP与MDX实现数据仓库的远程查询．微型电脑应用．2001(11)
[53]Hugh.J.Waston.The benefits of data warehousing why some organizations realize exceptional payoffs.Information & Management.2002(39) 491-502
[54]Scott.J.Mason.Integrating the warehousing and transportation functions of the supply chain.Transportation Research Part E.2003 (39) 141-151
[55]ED O'Donell.How information systems influence user decisions a research framework and literature review .Accounting Information System.2000(1) 178-203
[56]K.W.Chau.Application of data warehouse and Decision Support System in construction
management.Automaton in Construction.2002(12)213-224
[57]Jiawei Han,Micheline Kamber．数据挖掘概念与技术．机械工业出版社．2001．
[58]Bingi P., M.Shara, Godla K.Critical issues affecting an ERP implementation.Information systems management.1999:7-14.
[59]Leslie W.,S.Richard.The role of the CIO and IT function in ERE Communications of the ACM.2000(43):32-38.
[60]徐峰，陈铁军，王志霞．基于Mobile Agent的分布式PDM文档管理系统．中国制造业信息化．2003(9)
[61]Celeste See Pui Ng,Taizan Chan.A client-benefits oriented taxonomy of ERP maintenance.Information Systems Management Research Centre.2001(10):1-10
[62]Hsin Hsin Chang.A model of computerization of manufacturing systems an international study.Information&Management.39 (2002) 605-624
[63]D.Toney Liu,X.William Xu.A review of web-based product data management systems.Computers in Industry.44 (2001) 251-262
[64]Jan Olhager, Eric Selldin.Enterprise resource planning survey of Swedish manufacturing firms.European Journal of Operational Research.146 (2003) 365-373
[65]Daniel E.O'Leary.Knowledge management across the enterprise resource planning systems life cycle.Accouting Information System.3 (2002) 99-110