基于XMLA标准的数据仓库统一访问接口的研究与实现
|
摘要
随着数据库技术的发展和数据库系统的广泛使用,各种各样的数据库已经积累海量数据。联机分析处理(Online Analytical Processing以下简称OLAP)能从这些海量数据中寻找关联规律和发现潜在的有用信息作为企业决策依据,它主要在数据仓库基础上进行数据分析以求获得有用信息。从另一方面来看,支持高级决策OLAP技术可以深入分析和研究数据、发现趋势、看到异常情况、得到重要细节以便更好地理解商业行为的涨落变迁,已经得到广泛应用。但是,很多OLAP厂商都有内置数据访问标准而没有统一的数据访问标准,这给数据仓库统一访问甚至集成带来很大挑战。
为了达到异构数据仓库之间能够通讯甚至集成的目标,很多生产OLAP工具的厂商努力开发能够访问所有OLAP服务器的统一OLAP API访问接口。可以从三个角度分析开发访问OLAP服务器统一OLAP API标准的必要性：(1)从数据仓库开发者角度,单一工具很少能完全满足用户不断变化的需求,又很难对各种产品进行集成。(2)从数据仓库用户角度,面对海量数据,无法轻易找到自己真正需要的信息,而且把这些信息完整正确地表示出来也是个挑战。(3)从数据仓库供应商角度,如果信息共享没有标准格式,元数据集成代价太大。
本文首先简单介绍国内外研究现状以及数据仓库、OLAP技术相关理论,接着了解微软OLAP服务器元数据模型、Mondrian元数据模型和Oracle OLAP服务器元数据模型为实现各种数据仓库元数据标准的统一访问奠定理论基础。着重研究数据仓库三种访问标准以及MDX查询语言并对XMLA标准和MDX查询语言进行详细分析,因为Oracle OLAP服务器不支持XMLA标准和MDX语言。若实现符合XMLA标准的数据仓库统一访问的目标,必须首先实现Oracle OLAP元数据模型与XMLA数据模型的映射,然后根据MDX语言特点开发MDX解析器来实现对MDX语言的解析,根据以上理论知识设计出系统总体架构,该架构采用B/S三层结构,前端展示层使用开源JPivot产品,该产品是一个自定义JSP标签库,基本涵盖所有OLAP功能和操作,并且支持以XMLA标准访问数据源：控制层利用开发的符合XMLA标准的引擎(以下简称XMLA引擎)；数据层利用Oracle11g数据库。其次,设计并实现Oracle多维数据库为XMLA引擎提供数据来源。最后,实现系统并对系统进行测试,前台JPivot通过XMLA引擎对Oracle OLAP服务器、微软OLAP服务器和Mondrian多维分析引擎提供符合XMLA标准的统一访问。
With the development of the database technology and the wide application of the database system,there have been a great deal of history data accumulated in all kind of database system. Online Analysis Processing(short for OLAP) which can find the regulation and refine the useful information from those existing data has been used as the base of enterprise decision-making,and it also emphasized on taking useful information by data analysis based on the DW (Data Warehouse).From another aspect,OLAP,which is an advanced DSS tool,has been more and more popularly used to make deep investigation of business data,find the business tendency and abnomrality,get important details and make better understanding of rules of their business behaviors.However,a majority of OLAP manufacturers have native data access standards instead of uniformed data access standards,therefore, there is a great challenge to the unified access to the data warehouse.
In order to achieve the target of uniformly access to heterogeneous data warehouse even realize the integration with them, a majority of OLAP tool manufactures take great efforts to develop the unified OLAP API interface which can access all OLAP server.The necessary to develop unified OLAP API standard which can acesses to all OLAP server can be analyzed from three respects:(1)from the respect of the data warehouse developer, the single tool is rarely able to fully meet the changing needs of the users,and it is also difficult to integrate with various product.(2)from the respect of the data warehouse user, in face of a great deal of data,it can not easily find the really needed information,and it is beyond challenge to completely and correctly express that information.(3)from the respect of the data warehousing vendors,metadata integration is too costly because of lacking of the standard format of the share information.
This paper,in the first place.briefly introduces the research status home and abroad along with the related theory of data warehouse,OLAP technology. And then the metadata model of Microsoft Analysis Services.Modrian and Oracle OLAP Server come to understand in order to realize the implementation of the uniformly access to metadata from all kinds of data warehouse.Three standards of access to data warehouse as well as MDX query language is extremely studied.and especially, the XMLA standard and MDX query language is analysised in detail.due to the Oracle OLAP Server does not support the XMLA standard and MDX query language.To achive the target that the realization of uniformly access to data warehouse in accordance with XMLA standard, the mapping between Oracle OLAP metadata model and XMLA data model is firtly implemented.and then the MDX parser which can parse the MDX language based on MDX character must be developed,the overall system architecture is designed in accordance with the above theoretical knowledge.the architecture takes adventage of Brower/Server three-tier structure,front-end presentation layer makes use of the open source JPivot product.which is a custom JSP tag libraries and covers all OLAP functions and operations, especially supporting for accessing the data source via XMLA standard;the control layer takes adventage of the developed engine in accordance with the XMLA standard(short for XMLA Engine);the data layer takes adventage of an Oracle11g database. In the second place, the design and implementation of the Oracle OLAP so as to provide the data source for XMLA Engine.Finally, in order to the realization of the system and test the system, the front-end JPivot can be uniformly access to Oracle OLAP, Microsoft Analysis Services and Mondrian in accordance with the XMLA standard through the XMLA Engine, thus, achieving the target.
为了达到异构数据仓库之间能够通讯甚至集成的目标,很多生产OLAP工具的厂商努力开发能够访问所有OLAP服务器的统一OLAP API访问接口。可以从三个角度分析开发访问OLAP服务器统一OLAP API标准的必要性：(1)从数据仓库开发者角度,单一工具很少能完全满足用户不断变化的需求,又很难对各种产品进行集成。(2)从数据仓库用户角度,面对海量数据,无法轻易找到自己真正需要的信息,而且把这些信息完整正确地表示出来也是个挑战。(3)从数据仓库供应商角度,如果信息共享没有标准格式,元数据集成代价太大。
本文首先简单介绍国内外研究现状以及数据仓库、OLAP技术相关理论,接着了解微软OLAP服务器元数据模型、Mondrian元数据模型和Oracle OLAP服务器元数据模型为实现各种数据仓库元数据标准的统一访问奠定理论基础。着重研究数据仓库三种访问标准以及MDX查询语言并对XMLA标准和MDX查询语言进行详细分析,因为Oracle OLAP服务器不支持XMLA标准和MDX语言。若实现符合XMLA标准的数据仓库统一访问的目标,必须首先实现Oracle OLAP元数据模型与XMLA数据模型的映射,然后根据MDX语言特点开发MDX解析器来实现对MDX语言的解析,根据以上理论知识设计出系统总体架构,该架构采用B/S三层结构,前端展示层使用开源JPivot产品,该产品是一个自定义JSP标签库,基本涵盖所有OLAP功能和操作,并且支持以XMLA标准访问数据源：控制层利用开发的符合XMLA标准的引擎(以下简称XMLA引擎)；数据层利用Oracle11g数据库。其次,设计并实现Oracle多维数据库为XMLA引擎提供数据来源。最后,实现系统并对系统进行测试,前台JPivot通过XMLA引擎对Oracle OLAP服务器、微软OLAP服务器和Mondrian多维分析引擎提供符合XMLA标准的统一访问。
With the development of the database technology and the wide application of the database system,there have been a great deal of history data accumulated in all kind of database system. Online Analysis Processing(short for OLAP) which can find the regulation and refine the useful information from those existing data has been used as the base of enterprise decision-making,and it also emphasized on taking useful information by data analysis based on the DW (Data Warehouse).From another aspect,OLAP,which is an advanced DSS tool,has been more and more popularly used to make deep investigation of business data,find the business tendency and abnomrality,get important details and make better understanding of rules of their business behaviors.However,a majority of OLAP manufacturers have native data access standards instead of uniformed data access standards,therefore, there is a great challenge to the unified access to the data warehouse.
In order to achieve the target of uniformly access to heterogeneous data warehouse even realize the integration with them, a majority of OLAP tool manufactures take great efforts to develop the unified OLAP API interface which can access all OLAP server.The necessary to develop unified OLAP API standard which can acesses to all OLAP server can be analyzed from three respects:(1)from the respect of the data warehouse developer, the single tool is rarely able to fully meet the changing needs of the users,and it is also difficult to integrate with various product.(2)from the respect of the data warehouse user, in face of a great deal of data,it can not easily find the really needed information,and it is beyond challenge to completely and correctly express that information.(3)from the respect of the data warehousing vendors,metadata integration is too costly because of lacking of the standard format of the share information.
This paper,in the first place.briefly introduces the research status home and abroad along with the related theory of data warehouse,OLAP technology. And then the metadata model of Microsoft Analysis Services.Modrian and Oracle OLAP Server come to understand in order to realize the implementation of the uniformly access to metadata from all kinds of data warehouse.Three standards of access to data warehouse as well as MDX query language is extremely studied.and especially, the XMLA standard and MDX query language is analysised in detail.due to the Oracle OLAP Server does not support the XMLA standard and MDX query language.To achive the target that the realization of uniformly access to data warehouse in accordance with XMLA standard, the mapping between Oracle OLAP metadata model and XMLA data model is firtly implemented.and then the MDX parser which can parse the MDX language based on MDX character must be developed,the overall system architecture is designed in accordance with the above theoretical knowledge.the architecture takes adventage of Brower/Server three-tier structure,front-end presentation layer makes use of the open source JPivot product.which is a custom JSP tag libraries and covers all OLAP functions and operations, especially supporting for accessing the data source via XMLA standard;the control layer takes adventage of the developed engine in accordance with the XMLA standard(short for XMLA Engine);the data layer takes adventage of an Oracle11g database. In the second place, the design and implementation of the Oracle OLAP so as to provide the data source for XMLA Engine.Finally, in order to the realization of the system and test the system, the front-end JPivot can be uniformly access to Oracle OLAP, Microsoft Analysis Services and Mondrian in accordance with the XMLA standard through the XMLA Engine, thus, achieving the target.
引文
[1]JOLAP发展前[EB/OL].http://lemire.me/blog/archives/2005/11/24/java-olap-interface-Jolap-is-dead.
[2]W.H. Inmon. Building the Data Warehouse[M]. Third Edition. John Wiley & Sons, Inc.2002.
[3]R. Kimbal,l. Reeves,M. Ross,W.T. Waite. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing,Developing.and Deploying Data Warehouse[M]. John Wiley & Sons,1998.
[4]国际数据中心官方网站[EB/OL]. http://www.idc.com.cn/home.jsp.
[5]姚家奕.数据仓库与数据挖掘技术原理及应用[M].北京：电子工业出版社,2009.
[6]王强,刘东波,王建.数据仓库元数据标准研究[J].计算机工程.2002,28(12).123-125.
[7]李大志,张阿红.数据仓库与联机分析处理技术综述[J].科技信息.2010,(35).739-742.
[8]商业智能之路[EB/OL]. http://www.bi-professional.com/
[9]吴刚等译,(美)MichaeL. Gonzales. IBM数据仓库及IBM商务智能工具[M].电子工业出版社,2004-7.
[10]刘晶炜,闰健卓,朱青.IBM信息集成技术原理及应用[M].IBM软件学院,电子工业出版,2004-05.
[11]琪昌,李育龙IBM DB2高级管理指南[M].IBM软件学院.电子工业出版,2004-07.
[12]Seungrahn Hahn,Ashvin Amin.Klenmens Dickhover,et al.Getting Started with DB2 OLAP Server for OS/390.International Business Machines Corporation.2000,1-18.
[13]数据库开发技术宝典丛书编委会Sybase数据仓库结构用户开发指南[M].北京希望电子出版社.200-04.
[14]Sybase官方网站[EB/OL]. http://www.sybase.com.cn/gvswse/site/china/ content.jsp?_doc_id=1731
[15]沈兆阳SQL Server 2000 OLAP解决方案：数据仓库与Analysis Services[M].清华大学出版社,,2001-09.
[16]罗运模SQL Server 2000数据仓库应用与开发[M].人民邮电出版社,2001-07.
[17]Shirinne Alison.Kavita Nayar, et al.Oracle9i OLAP User's Guide.Release 2(9.2.0.2).Oracle Corporation.2002,1-40.
[18]Mondrian官方文档Pentaho[EB/OL]. http://mondrian.pentaho.com/ documentation/
[19]JPivot官方网站[EB/OL]. http://jpivot.sourceforge.net/
[20]博易智软BI-pilot介绍网站[EB/OL]. http://www.bi-soft.com/products. htm,2005-10-01.
[21]明基逐鹿Analyzer介绍网站[EB/OL]. http://www.benqguru.com/products /ERP/analyzer/index.asp,2005-10-01.
[22]OLAP委员会发布互操作开放标准[EB/OL]. http://www.olapcouncil.org/ news/apir2co.htm
[23]XMLA1.1技术规范[EB/OL]. http://news.xmlforanalysis.com/
[24]XMLA技术参考[EB/OL]. http://technet.microsoft.com/zh-cn/library/ ms186604.aspx.
[25]XML for Analysis(XMLA)开发详解[EB/OL]. http://www.360doc.com/ content/10/0726/09/1973531 41499373.shtml.
[26]Simba技术官方网站[EB/OL]. http://www.simba.com/
[27]刘红艳.基于OLAP的高校就业管理系统研究与实现[D].西安：西安科技大学,2010.
[28]林宇.数据仓库原理与实践[M].北京：人民邮电出版社.2003.
[29]WH.Inmon,王志海等译.数据仓库(第三版)[M].北京：机械工业出版社,2003.
[30]田隆.基于财务数据中心的商业智能平台设计与实现[D].北京：北京信息控制研究所.2010.
[31]何玉洁.数据库仓库与OLAP实践教程[M].北京：清华大学出版.2008.
[32]Codd E F,Codd S B,Salley C T. Porviding OLAP to User analysts:An ITMandate.White PaPer Abor Sotfware Coproration,1993.
[33]王丽珍,周丽华,陈红梅,等.数据仓库与数据挖掘原理及应用[M].科学出版社.2005.
[34]CWM标准介绍[EB/OL]. http://searchoracle.bitpipe.com/whitepapers.
[35]CWM标准相关文档[EB/OL]. http://www.cwmforum.org/paperpresent.htm.
[36]OLAP官方网站[EB/OL]. http://searchoracle.techtarget.com/definition/ JOLAP
[37]张幸儿.编译原理：编译程序构造与实践[M].北京：机械工业出版社,2007.61-82.
[38]Apple现代编译器的Java实现(第二版)[M].北京：电子工业出版社,2004.27-41
[39]李仁见,董霖等译,(美)George Spofford. MDX解决方案[M].清华大学出版社,2009-09.
[40]MDX查询语句[EB/OL]. http://msdn2.microsoft.com/en-us/library/ms145506 .aspx
[41]何林糠,李建,麻荣誉.B/S结构下基于关系数据库的OLAP的研究与应用[J].信息技术,2011,(1).130-132.
[42]Oracle OLAP官方网站[EB/OL]. http://www.oracle.com/technetwork/ database/options/olap/index.html.
[43]Oracle OLAP介绍[EB/OL]. http://www.orafaq.com/wiki/Oracle_OLAP.
[44]Microsoft SQL Server官方网站[EB/OL]. http://www.microsoft.com/zh-cn/ SQLServer/default.aspx
[45]Erich Gamma,Richard Helm.Ralph Johnson.Design Patterns Elements of Reusable Objeet-oriented Software [M]北京：机械工业出版社.2003-09.
[46]Ival Jacobson.Grady Booch,James Rumbaugh.The Unified Software evelopment Proeess.
[47]MaryShaw.David Garlan.Software Arehitecture Perspectives Emerging Discipline.
[48]李海龙,张维明,肖卫东等.通用标准SQL语法分析模型[J]小型微型计算机系统,2003,24(11)：1969-1972.
[49]龚斌,陈昊鹏.用CUP构造编译器的方法[J].计算机应用与软件.2007,24(12)： 88-90.
[50]张昱,张磊LALR(1)解析器的再工程YACC和CUP剖析[J].计算机工程,2005.31(15)：50-52.
[51]吴江.SQL语言预编译器的架构-基于Linux操作系统[M].武汉：华中科技大学,2002.
[52]Java_CUPLALR语法分析工具[EB/OL]. http://www2.cs.tum.edu/projects/ cup/
[53]JLex自动化编译工具[EB/OL]. http://www.cs.princeton.edu/-appel/modern/ java/JLex/
[54]Java_CUP解析产生器[EB/OL]. http://www.cs.princeton.edu/-appel/modern/ java/CUP/
[55]OLAP白皮书[EB/OL]. http://www.inet.uni-jena.de/dbis/lehre/ss2005/sem_dwh/ lit/Cod93.pdf
[56]曹莎莎.数据仓库前端展示工具的研究与应用[D].四川：西南石油大学,2007.
[2]W.H. Inmon. Building the Data Warehouse[M]. Third Edition. John Wiley & Sons, Inc.2002.
[3]R. Kimbal,l. Reeves,M. Ross,W.T. Waite. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing,Developing.and Deploying Data Warehouse[M]. John Wiley & Sons,1998.
[4]国际数据中心官方网站[EB/OL]. http://www.idc.com.cn/home.jsp.
[5]姚家奕.数据仓库与数据挖掘技术原理及应用[M].北京：电子工业出版社,2009.
[6]王强,刘东波,王建.数据仓库元数据标准研究[J].计算机工程.2002,28(12).123-125.
[7]李大志,张阿红.数据仓库与联机分析处理技术综述[J].科技信息.2010,(35).739-742.
[8]商业智能之路[EB/OL]. http://www.bi-professional.com/
[9]吴刚等译,(美)MichaeL. Gonzales. IBM数据仓库及IBM商务智能工具[M].电子工业出版社,2004-7.
[10]刘晶炜,闰健卓,朱青.IBM信息集成技术原理及应用[M].IBM软件学院,电子工业出版,2004-05.
[11]琪昌,李育龙IBM DB2高级管理指南[M].IBM软件学院.电子工业出版,2004-07.
[12]Seungrahn Hahn,Ashvin Amin.Klenmens Dickhover,et al.Getting Started with DB2 OLAP Server for OS/390.International Business Machines Corporation.2000,1-18.
[13]数据库开发技术宝典丛书编委会Sybase数据仓库结构用户开发指南[M].北京希望电子出版社.200-04.
[14]Sybase官方网站[EB/OL]. http://www.sybase.com.cn/gvswse/site/china/ content.jsp?_doc_id=1731
[15]沈兆阳SQL Server 2000 OLAP解决方案：数据仓库与Analysis Services[M].清华大学出版社,,2001-09.
[16]罗运模SQL Server 2000数据仓库应用与开发[M].人民邮电出版社,2001-07.
[17]Shirinne Alison.Kavita Nayar, et al.Oracle9i OLAP User's Guide.Release 2(9.2.0.2).Oracle Corporation.2002,1-40.
[18]Mondrian官方文档Pentaho[EB/OL]. http://mondrian.pentaho.com/ documentation/
[19]JPivot官方网站[EB/OL]. http://jpivot.sourceforge.net/
[20]博易智软BI-pilot介绍网站[EB/OL]. http://www.bi-soft.com/products. htm,2005-10-01.
[21]明基逐鹿Analyzer介绍网站[EB/OL]. http://www.benqguru.com/products /ERP/analyzer/index.asp,2005-10-01.
[22]OLAP委员会发布互操作开放标准[EB/OL]. http://www.olapcouncil.org/ news/apir2co.htm
[23]XMLA1.1技术规范[EB/OL]. http://news.xmlforanalysis.com/
[24]XMLA技术参考[EB/OL]. http://technet.microsoft.com/zh-cn/library/ ms186604.aspx.
[25]XML for Analysis(XMLA)开发详解[EB/OL]. http://www.360doc.com/ content/10/0726/09/1973531 41499373.shtml.
[26]Simba技术官方网站[EB/OL]. http://www.simba.com/
[27]刘红艳.基于OLAP的高校就业管理系统研究与实现[D].西安：西安科技大学,2010.
[28]林宇.数据仓库原理与实践[M].北京：人民邮电出版社.2003.
[29]WH.Inmon,王志海等译.数据仓库(第三版)[M].北京：机械工业出版社,2003.
[30]田隆.基于财务数据中心的商业智能平台设计与实现[D].北京：北京信息控制研究所.2010.
[31]何玉洁.数据库仓库与OLAP实践教程[M].北京：清华大学出版.2008.
[32]Codd E F,Codd S B,Salley C T. Porviding OLAP to User analysts:An ITMandate.White PaPer Abor Sotfware Coproration,1993.
[33]王丽珍,周丽华,陈红梅,等.数据仓库与数据挖掘原理及应用[M].科学出版社.2005.
[34]CWM标准介绍[EB/OL]. http://searchoracle.bitpipe.com/whitepapers.
[35]CWM标准相关文档[EB/OL]. http://www.cwmforum.org/paperpresent.htm.
[36]OLAP官方网站[EB/OL]. http://searchoracle.techtarget.com/definition/ JOLAP
[37]张幸儿.编译原理：编译程序构造与实践[M].北京：机械工业出版社,2007.61-82.
[38]Apple现代编译器的Java实现(第二版)[M].北京：电子工业出版社,2004.27-41
[39]李仁见,董霖等译,(美)George Spofford. MDX解决方案[M].清华大学出版社,2009-09.
[40]MDX查询语句[EB/OL]. http://msdn2.microsoft.com/en-us/library/ms145506 .aspx
[41]何林糠,李建,麻荣誉.B/S结构下基于关系数据库的OLAP的研究与应用[J].信息技术,2011,(1).130-132.
[42]Oracle OLAP官方网站[EB/OL]. http://www.oracle.com/technetwork/ database/options/olap/index.html.
[43]Oracle OLAP介绍[EB/OL]. http://www.orafaq.com/wiki/Oracle_OLAP.
[44]Microsoft SQL Server官方网站[EB/OL]. http://www.microsoft.com/zh-cn/ SQLServer/default.aspx
[45]Erich Gamma,Richard Helm.Ralph Johnson.Design Patterns Elements of Reusable Objeet-oriented Software [M]北京：机械工业出版社.2003-09.
[46]Ival Jacobson.Grady Booch,James Rumbaugh.The Unified Software evelopment Proeess.
[47]MaryShaw.David Garlan.Software Arehitecture Perspectives Emerging Discipline.
[48]李海龙,张维明,肖卫东等.通用标准SQL语法分析模型[J]小型微型计算机系统,2003,24(11)：1969-1972.
[49]龚斌,陈昊鹏.用CUP构造编译器的方法[J].计算机应用与软件.2007,24(12)： 88-90.
[50]张昱,张磊LALR(1)解析器的再工程YACC和CUP剖析[J].计算机工程,2005.31(15)：50-52.
[51]吴江.SQL语言预编译器的架构-基于Linux操作系统[M].武汉：华中科技大学,2002.
[52]Java_CUPLALR语法分析工具[EB/OL]. http://www2.cs.tum.edu/projects/ cup/
[53]JLex自动化编译工具[EB/OL]. http://www.cs.princeton.edu/-appel/modern/ java/JLex/
[54]Java_CUP解析产生器[EB/OL]. http://www.cs.princeton.edu/-appel/modern/ java/CUP/
[55]OLAP白皮书[EB/OL]. http://www.inet.uni-jena.de/dbis/lehre/ss2005/sem_dwh/ lit/Cod93.pdf
[56]曹莎莎.数据仓库前端展示工具的研究与应用[D].四川：西南石油大学,2007.