Mondrian use test

Mondrian is an OLAP engine, unlike OLTP, where OLAP is a logical model built above a physical table, called a cube, which is built by specifying optional dimensions and measures, each dimension has a dimension table, and the dimension has hierarchical information. OLAP lookup is implemented in an MDX table, similar to the OLTP SQL syntax, MDX has its own syntax, but it only regulates the query statement, because the data is not modified in OLAP, so there are two aspects of OLAP execution, the first is the definition of the model, In Mondrian, the definition of the logical model is passed through XML to Mondrian parsing, the query for this model is done through the MDX format statement, the following through an instance call Mondrian interface to see the use of Mondrian.

package Mondriantest;import Java.io.printwriter;import Mondrian.olap.connection;import Mondrian.olap.DriverManager;import  Mondrian.olap.query;import Mondrian.olap.result;public class Testmdx {public void Testquery () {Connection Connection = Drivermanager.getconnection ("Provider=mondrian;" + "jdbc=jdbc:mysql://10.241.20.157:3306/foodmart?user=root &password=root; "+" catalog=c:\\users\\administrator\\desktop\\nrtp\\foodmart.xml; ", NULL, FALSE)       ; Query query = Connection.parsequery ("SELECT {[measures].[       Traffic]} on columns from Msgbusi ");       result = Connection.Execute (query);       PrintWriter pw = new PrintWriter (System.out);       Result.print (PW);   Pw.flush ();       } public static void Main (string[] args) {testmdx a = new testmdx ();       System.out.println ("Call Mondrian API to query");   A.testquery (); }}

This code is down from the web itself, to run it needs to add some of the required in the process of the jar package, at least the following several packages:

then the following error occurred:

Exception in thread "main" Mondrian.olap.MondrianException:Mondrian error:internal error:while parsing catalog C:\Users \administrator\desktop\nrtp\foodmart.xmlat Mondrian.resource.mondrianresource$_def0.ex (MondrianResource.java : 755) at Mondrian.olap.Util.newInternal (util.java:1083) at Mondrian.olap.Util.newError (util.java:1099) at Mondrian.rolap.RolapSchema.load (rolapschema.java:303) at mondrian.rolap.rolapschema.<init> ( rolapschema.java:213) at mondrian.rolap.rolapschema.<init> (rolapschema.java:76) at Mondrian.rolap.rolapschema$pool.get (rolapschema.java:835) at Mondrian.rolap.rolapschema$pool.get ( rolapschema.java:657) at mondrian.rolap.rolapconnection.<init> (rolapconnection.java:148) at Mondrian.rolap.rolapconnection.<init> (rolapconnection.java:79) at Mondrian.olap.DriverManager.getConnection (drivermanager.java:122) at Mondrian.olap.DriverManager.getConnection ( drivermanager.java:87) at Mondrian.olap.DriverManager.getConnection (drivermanager.java:55) at MONDRIANTEST.TESTMDX. Testquery (Testmdx.java:13) at MondrianTest.TestMDX.main (testmdx.java:41) caused by:org.eigenbase.xom.XOMException : In Schema:in cube:in measure:in measureexpression:in sql:value ' infobright ' of attribute ' dialect ' have illegal valu  E ' Infobright '. Legal values: {Generic, Access, DB2, Derby, Firebird, Hsqldb, MSSQL, MySQL, Oracle, Postgres, Sybase, Teradata, Ingres, Lu Ciddb}at mondrian.olap.mondriandef$schema.<init> (mondriandef.java:134) at Mondrian.rolap.RolapSchema.load ( rolapschema.java:289) ... One more

This step is parsing the schema definition of the XML file error, according to the hint, to see what infobright is a solution to the data warehouse, it is possible that the current version of Mondrian does not support it, so will appear his XML tags are commented out, Anyway we only use MySQL here (to make sure that the MySQL connection-driven jar package has been added to the project's classpath) and does not care about the others. Then run again with the following error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failurethe Last packet sent successfully to the server was 0 milliseconds ago. The driver have not received no packets from the server.at Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at Sun.reflect.NativeConstructorAccessorImpl.newInstance (Unknown Source) at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Unknown Source) at Java.lang.reflect.Constructor.newInstance (Unknown Source) at Com.mysql.jdbc.Util.handleNewInstance (util.java:408) At Com.mysql.jdbc.SQLError.createCommunicationsException (sqlerror.java:1137) at Com.mysql.jdbc.mysqlio.<init > (mysqlio.java:355) at Com.mysql.jdbc.ConnectionImpl.coreConnect (connectionimpl.java:2490) at Com.mysql.jdbc.ConnectionImpl.connectOneTryOnly (connectionimpl.java:2527) at Com.mysql.jdbc.ConnectionImpl.createNewIO (connectionimpl.java:2309) at Com.mysql.jdbc.connectionimpl.<init > (connectionimpl.java:834) at COM.MYsql.jdbc.jdbc4connection.<init> (jdbc4connection.java:46) at Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at Sun.reflect.NativeConstructorAccessorImpl.newInstance (Unknown Source) at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Unknown Source) at Java.lang.reflect.Constructor.newInstance (Unknown Source) at Com.mysql.jdbc.Util.handleNewInstance (util.java:408) At Com.mysql.jdbc.ConnectionImpl.getInstance (connectionimpl.java:419) at Com.mysql.jdbc.NonRegisteringDriver.connect (nonregisteringdriver.java:344) at Java.sql.DriverManager.getConnection (Unknown source) at Java.sql.DriverManager.getConnection (Unknown source) at MondrianTest.TestMysql.main (testmysql.java:17) caused by:java.net.ConnectException:Connection refused:connectat Java.net.DualStackPlainSocketImpl.connect0 (Native Method) at Java.net.DualStackPlainSocketImpl.socketConnect ( Unknown source) at Java.net.AbstractPlainSocketImpl.doConnect (Unknown source) at Java.net.AbstractPlainSocketImpl.connectToAddress (Unknown source) at Java.net.AbstractPlainSocketImpl.connect (Unknown source) at Java.net.PlainSocketImpl.connect (Unknown source) at Java.net.SocksSocketImpl.connect (Unknown source) at Java.net.Socket.connect (Unknown source) at Java.net.Socket.connect (Unknown source) at Java.net.socket.<init> ( Unknown source) at java.net.socket.<init> (Unknown source) at Com.mysql.jdbc.StandardSocketFactory.connect ( standardsocketfactory.java:258) at com.mysql.jdbc.mysqlio.<init> (mysqlio.java:305) ... More

This error is caused by connect from the stack, by http://stackoverflow.com/questions/2983248/ Com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai analysis found when the server is not connected to the cause of the But clearly the server's 3306 port has been opened Ah, and on the server using the MySQL client can connect up AH. What this is all about, using the Tcpdump capture packet to discover that each SYN request will be ruthlessly replied to the RST message by the server, which indicates that the port does not open, and that using Netstat to view the port is true:


The original server only receives local connections, no wonder I can't keep up with Windows. The solution is to modify the MySQL configuration file my.conf, the bind-address from the original 127.0.0.1 modified to 0.0.0.0, so you can connect to MySQL remotely. Then run again with this error:

Exception in thread "main" Mondrian.olap.MondrianException:Mondrian error:failed to parse query ' select {[measures].[ Traffic]} on columns from Msgbusi ' at Mondrian.resource.mondrianresource$_def0.ex (mondrianresource.java:755) at Mondrian.olap.ConnectionBase.parseQuery (connectionbase.java:77) at Mondrian.olap.ConnectionBase.parseQuery ( connectionbase.java:59) at MondrianTest.TestMDX.testQuery (testmdx.java:18) at MondrianTest.TestMDX.main ( TESTMDX.JAVA:37) caused By:mondrian.olap.MondrianException:Mondrian error:error while parsing MDX statement ' SELECT {[ Measures]. [Business volume]} On columns from Msgbusi ' at Mondrian.resource.mondrianresource$_def0.ex (mondrianresource.java:755) at Mondrian.olap.Parser.parseInternal (parser.java:760) at Mondrian.olap.ConnectionBase.parseQuery ( connectionbase.java:74) ... 3 morecaused By:mondrian.olap.MondrianException:Mondrian error:mdx cube ' Msgbusi ' not found or not processedat Mondrian. Resource. Mondrianresource$_def0.ex (mondrianresource.java:752) at Mondrian.rolap.RolapSchemA.lookupcube (rolapschema.java:1067) at mondrian.olap.query.<init> (query.java:187) at Mondrian.olap.Parser.makeQuery (parser.java:851) at mondrian.olap.cup$parser$actions. Cup$parser$do_action (parser.java:1700) at Mondrian.olap.Parser.do_action (parser.java:685) at Java_cup.runtime.lr_ Parser.parse (lr_parser.java:569) at mondrian.olap.Parser.parseInternal (parser.java:755) ... 4 more

This is the query MDX error occurred, this means that the previous connection to MySQL, parse the operation of the XML file has been successfully executed, according to the error prompt found that Msgbusi does not exist, to view the XML file found that this msgbusi does not exist, it is only remembered, The original down code is not the same as the one I used to define the logical model, and I'm using a sample FoodMart provided by Mondrian, so I'm supposed to use the cube and dimension defined by my logic model here to query. Therefore, the MDX statement needs to be modified, after many tests, it is found that the statement can run the result in the current configuration: SELECT {[measures].[ Unit Sales]} on columns,{[time].[ Year]. [1997]} on the rows from Sales WHERE ([customers].[ State Province]. [CA]), the result of the run-out is as follows:

Axis #0: {[customers].[ USA]. [CA]} Axis #1: {[measures].[ Unit Sales]}axis #2: {[time].[ 1997]}row #0:74,748

But I found a lot of Mondrian versions, The latest version is already in the 4.0 version, and can be used in the version of the 2.x version, and then found in the newer version of the use of olap4j such an interface to access, this interface is similar to the JDBC Access database engine, is a Java interface Specification for access to the ROLAP engine, using the MDX statement, then replaced by the current stable version 3.9 for testing, and then found that the error:

Exception in thread "main" Java.lang.NoSuchMethodError:mondrian.resource.MondrianResource.getThreadOrDefaultLocale () Ljava/util/locale;at mondrian.resource.MondrianResource.instance (mondrianresource.java:29) at Mondrian.rolap.aggmatcher.aggtablemanager.<clinit> (aggtablemanager.java:54) at Mondrian.rolap.RolapSchema. <init> (rolapschema.java:194) at mondrian.rolap.rolapschema.<init> (rolapschema.java:216) at Mondrian.rolap.RolapSchemaPool.get (rolapschemapool.java:214) at Mondrian.rolap.RolapSchemaPool.get ( rolapschemapool.java:66) at mondrian.rolap.rolapconnection.<init> (rolapconnection.java:160) at Mondrian.rolap.rolapconnection.<init> (rolapconnection.java:90) at Mondrian.olap.DriverManager.getConnection (drivermanager.java:112) at Mondrian.olap.DriverManager.getConnection ( drivermanager.java:68) at Mondrian.olap.DriverManager.getConnection (DRIVERMANAGER.JAVA:50) at MondrianTest.TestMDX.testQuery (testmdx.java:13) at MondrianTest.TestMDX.main (testmdx.java:38)

Mom, can't find this function, it is strange, this package also exists, the package also has this class, why does not have this function?! This is the Java is more annoying place, in C + + in the compilation must be able to find all the function definition, although some of the functions are not found in the implementation, the load will be the first dynamic link, and will not be executed at the time of execution, But Java this kind of only when the use of the load of the way it is likely to have this dynamic error, such as can't find class, find method and so on, this problem checked for a long time did not find fault, finally there is no way to take the final method: view the source code. Fortunately there are grepcode such a site, really is to see the Java source code easy to know how many orders of magnitude,as the saying goes: source code in hand, no secret。 With the source code, what problem is not a problem, Find Mondrian.resource.MondrianResource in the Grepcode class, found in Mondrian only 3.6.7 before the version has, so have to switch back to the 3.6.x version, here I use 3.6.1 version, after changing the jar package found this error     The mistake still exists, OK, look at the code. In the code, it is true that the Getthreadordefaultlocale function is called in the Mondrianresource instance method (29), and the function is not defined in the current class, so it must inherit another class, implemented in the parent class, Sure enough, it inherits the Org.eigenbase.resgen.ShadowResourceBundle class, and then look at this class, I wipe, in the codegrep can not find the source of this class, this time to ask Google it, search out this page: http ://www.java2s.com/code/jar/e/downloadeigenbaseresgenjar.htm, I wipe a click, I downloaded the Eigenbase-resgen.jar file is not here to download it?!     And this jar package clearly has the Org.eigenbase.resgen.ShadowResourceBundle class AH (otherwise my code compiles will not pass), everything again around, this time what to do? At this time I remember, since Mondrian himself can compile through, smooth execution, then either in the Mondrian Maven dependency package, or in Saifu should have this package dependencies, and this package should be available, The previous Eigenbase-resgen.jar could not be used because of a version problem, viewed the Mondrian pom.xml file, and found that it was using the following version:

<dependency>      <groupId>eigenbase</groupId>      <artifactid>eigenbase-resgen</ artifactid>      <version>1.3.1</version>    </dependency>

Viewed Saiku's Pom.xml file, using the following version:

<dependency>               <groupId>eigenbase</groupId>                <artifactid>eigenbase-resgen</ Artifactid>                

Although these two versions are different, but I think it should be any one, so under 1.3.1 version, the previous jar was changed after the test, OK, and got the same result.Although the same results were obtained, the Execute interface before Mondrian was used to execute the MDX statement, rather than through olap4j, the code could see that the execute interface was discarded. View the Mondrian document to see this comment: Deprecated. This method is deprecated and would be removed in mondrian-4.0. IT operates by internally creating a statement. Better to use olap4j and explicitly create a statement. It is said that this interface will be removed after Mondrian 4.0, it is recommended to create OLAP4J using the statement interface instead, So again on the Internet to search a piece of Mondrian tutorial (http://alenzhai.iteye.com/blog/2158953), here is olap4j way to access, copy the code after the change, The connection parameters and MDX statements remain unchanged, and the same results can be obtained after they have been run, except that the access method is more canonical. Attach Source code:

Package Mondriantest;import Java.io.printwriter;import Java.sql.connection;import java.sql.drivermanager;import Java.sql.sqlexception;import Org.olap4j.cell;import Org.olap4j.cellset;import Org.olap4j.OlapConnection;import Org.olap4j.olapexception;import Org.olap4j.olapstatement;import Org.olap4j.olapwrapper;import org.olap4j.Position  Import Org.olap4j.metadata.member;public class Olap4jtest {/** * get connections to OLAP * @param URL to connect OLAP URLs * @return * @throws ClassNotFoundException * @throws SQLException */public static olapconnection getconnection (String url) throws CLASSNOTFO Undexception, Sqlexception{class.forname ("Mondrian.olap4j.MondrianOlap4jDriver"); Connection Connection = drivermanager.getconnection (URL); olapconnection olapconnection = Connection.unwrap ( Olapconnection.class); return olapconnection;} /** * Get structure result set for query * @param MDX MDX query statement * @param conn OLAP connection * @return * @throws olapexception */public static CellSet Getr Esultset (String mdx,olapconnection conn) throws Olapexception{olapStatement Statement = Conn.createstatement (); CellSet CellSet = statement.executeolapquery (MDX); return CellSet;} public void Testquery () {olapconnection connection = null;try {connection = getconnection ("Jdbc:mondrian:" + "Jdb c=jdbc:mysql://10.241.20.157:3306/foodmart?user=root&password=root; "+" catalog=c:\\users\\administrator\\ Desktop\\nrtp\\foodmart.xml; ");} catch (ClassNotFoundException E1) {//TODO auto-generated catch Blocke1.printstacktrace ();} catch (SQLException E1) {//To        Do auto-generated catch Blocke1.printstacktrace ();} String query = "SELECT {[measures].[ Unit Sales]} on columns,{[time].[ Year]. [1997]} on the rows from Sales WHERE ([customers].[ State Province].        [CA]) "; Get query Results CellSet cs = null;try {cs = getresultset (query, connection);} catch (Olapexception e) {//TODO Auto-gene         Rated catch Blocke.printstacktrace ();}        PrintWriter pw = new PrintWriter (System.out); Handles the return data if (cs.getaxes (). Size () >1) {for (Position row : Cs.getaxes (). Get (1)) {for (Position column:cs.getAxes (). Get (0)) {for (Member member:row.getMembers ()) {        System.out.println ("Rows:" +member.getuniquename ());        } for (Member member:column.getMembers ()) {System.out.println ("columns:" +member.getuniquename ());        Final cell cell = Cs.getcell (column, row);        System.out.println ("Values:" +cell.getvalue ());    System.out.println (); }}}else{for (Position column:cs.getAxes (). Get (0)) {for (Member member:column.getMembers ()) {System.out.println (" Columns: "+member.getuniquename ());} Cell cell=cs.getcell (column); System.out.print ("Values:" +cell.getvalue ());    System.out.println ();}}}        public static void Main (string[] args) {Olap4jtest a = new Olap4jtest ();        System.out.println ("Call Mondrian API to query");    A.testquery (); }}

The output of the execution is as follows:

Rows:[time]. [1997]columns:[measures]. [Unit sales]values:74748.0

Finally, take a look at the information for the jar package used:

OK, the test is complete. Although the process is very tortuous, but also learned some new knowledge, here is just a simple demo to Mondrian, prove that through the use of API call OLAP4J can use the backend MySQL for OLAP operations, Next you need to understand Mondrian's support for hive and SQL on HBase, and today we're discovering a new open source OLAP engine: Kylin, an open source for ebay, is a MOLAP engine, Unlike the Mondrian (ROLAP engine), it aggregates the dimensions and measures that need to be presented into a table when the cube is created, and the table is materialized, saved in HBase, and then the query is not done through the MDX standard, but the SQL query for the materialized table. The advantage of this is that the query is very fast, but the materialized process takes a certain amount of time, and the materialized table does not necessarily fully support all cude operations, if not supported, or through ROLAP way to hive table dynamic lookup (similar to Mondrian),      While Mondrian does not need to save any data (except for Mondrian metadata), all MDX query operations are dynamically generated SQL, which is done by the backend database, which is likely to be a multi-table join operation, which is a big problem for hive. I think Mondrian's study mainly consists of two aspects: the definition of Cude and MDX view, so we need to learn the format of an XML file that generates a cube in Mondrian, how to define a cube, MDX syntax, etc. Let's do a back-end connection to hive demo.

Mondrian use test