I have always used MS Anylize Service. Recently I have to do it is Java, the small project budget is limited, so I want to find an open source Java's data warehouse solution to use.
I checked it online and found Mondrian. Mondrian is based on Java-based data warehouse engines, which can be integrated into a web project, which is most attractive to me. In addition, there are many options with the performance layer of him, and Jpivot is the veteran, Pentaho, Openi looks later. Anyway, still study MODRIAN
There is relatively small Chinese resources on the Internet, found on 9CBS, only found two more useful
http://dev.9cbs.net/develop/Article/31/31791.SHTM Mondrian - an affected "artist" selected from kswaking blog
Http://dev.9cbs.net/develop/Article/68/68661.SHTM Poor General OLAP Solution III - JPivot Express Layer Selection Self-CalvinXiu BLOG
I did something, I found some problems, and I have some experience.
I. Environmental preparation
1.1 First introduce the environment
Operating system: Linux
Server: Tomcat 5.5
Database: mysql 5.0.21
1.2 Download the program. MONDRIAN can be downloaded at http://mondrian.sourceforge.net, and in the early tutorial of MS Analyze Service as Demo, that is the Access database. Fortunately, now he has the version of Platform-Independent. I downloaded the Mondrian-2.1-derBy.zip to decompress it in the lib directory, put this directly to Tomcat's WebApps directory. I can see Mondrian's Demo. However, behind the test, I put this WAR until IbAPPS, and the directory reflects the name of Mondrian. Start Tomcat, enter http: // localhost / mondiran to see Demo in the browser. It should be explained that MONDRIAN's release contains Jpivot, use it to make a display layer, so you don't have to download Jpivot separately.
1.3 Database Built table, build table in the mysql database, borrowed the KSwaking database structure
In this tiny system, the database has 3 tables TB_EMPLOYEE (staff table), TB_TIME (Timetable), TB_SALARY. The surface structure is as follows:
Drop Table TB_EMPLOYEE
Create Table TB_EMPLOYEE
(
EMPLOYEE_ID NUMBER, - Staff ID
EMPLOYEE_NAME VARCHAR2 (10) - Name of staff
);
Drop table tb_time;
Create Table TB_TIME
(
Time_id Number, - Time ID
THE_YEAR CHAR (4), - year
THE_MONTH CHAR (2) - month
);
DROP TABLE TB_SALARY;
Create Table TB_salary
(
EMPLOYEE_ID NUMBER, - Staff ID
Time_id Number, - Time Idsalary Number (19, 4) - Salary
);
Of course, in order to enable the system to operate, readers need to insert some data to the database table.
Second. Mondrian Test
The MDX language implementation query is the same as MS using MS, which is a good news for people gate from MS Analyze Services.
2.1 Write Schema first.
This schema defines a Cube that contains two Dimension and a MEASURE. It is easy to understand, it will not be explained. The file path is WebApps / Mondrian / Web-INF / QUERIES / MONDRIANTEST.XML.
For the back of the test, I put the file in the queries directory.
Because of the use of MySQL to build a table, the field name inside Schema also used lowercase (I only used uppercase, the result error, I can't find the field), the article of Calvinxiu said if it is an Oracle database, The fields here should be used in uppercase. 2.2 Write JSP
<% @ Page import = "mondrian.olap *."%> <% Connection connection = DriverManager.getConnection ( "Provider = mondrian; Jdbc = jdbc: mysql: // localhost / mondrian; JdbcUser = root; JdbcPassword =; Catalog = File: ///usr/local/apache-tomcat-5.5.12/webapps/mondrian/web-inf/queries/mondriantest.xml; jdbcdriver = com.mysql.jdbc.driver ", NULL, FALSE);
String querystr = "select {[measures]. [Salary]} on columns, {[employee]. [Employeeid] .memete";
Query query = connection.parsequery (querystr); result result = connection.execute (query); Out.println ("get results);%>
You can see that Mondrian also uses JDBC to connect to the database, where special attention is that Catalog is named Schema's location.
File path WebApps / Mondrian / MondriantestMDX.jsp.jsp
2.3 test
Enter http://localhost/mondrian/mondriantestmdx.jsp You can see the result of the result of the result. Get Result.
So far, we only test Mondrian, which is only responsible for data extraction and organization, so there is no data on the screen, the next article will continue to study the display of data - JPivot.
