Mondrian Documentation

This package is for Mondrian developers and power users. If you are interested solely in Mondrian's analysis capabilities from a user standpoint, download the Pentaho Business Intelligence Server instead.

1. Introduction
2. Set up test data with a non-embedded database
   1. Microsoft Access
   2. Other database
   • Examples
   1. MySQL on Linux
   2. PostgreSQL on Windows
   3. Optional data sets
3. Deploy and run the web application with a non-embedded database
4. Deploy and run the web application with the embedded test database
5. How to configure Mondrian as an XML/A provider
   1. Describe the data sources in datasources.xml
   2. Configure XmlaServlet in your web.xml
6. Miscellaneous
1. Configuring mondrian.properties
2. Cache setup
7. Database compatibility
1. Infobright
2. Ingres
3. MySQL
4. PostgreSQL
5. Sybase
8. Compatibility issues
1. Weblogic 6.1 and Xerces
2. Log4j and Tomcat

1. Introduction 

The core of Mondrian is a JAR that acts as "JDBC for OLAP": providing connections and executing SQL against underlying relational databases. This can be run within a surrounding application. Mondrian comes to you as binaries packaged in a number of ways:

1. As a WAR which includes JPivot, an OLAP web application framework, and example data that can be loaded into your database of choice.
2. As a WAR with JPivot and an embedded Derby database, which requires no additional set up apart from deployment to an application server.

Both distributions contain the Mondrian source. All necessary JARs for Mondrian are contained in the WAR /WEB-INF/lib directory.

Here is how to install a binary release:

1. Install the Java SDK (1.4.2 or later).
2. Download the latest binary release mondrian-version.zip from SourceForge, and unzip it.
3. For the non-embedded distribution, set up the FoodMart dataset, as described in section 2.
4. Set up and start the web application, as described in section 3 or 4.

If you are a Mondrian developer, there are additional instructions for accessing the Perforce source-control server and building the code in the Developer's Guide.

2. Set up test data in a non-embedded database 

The 'FoodMart' test dataset is part of the non-embedded binary distribution. It is shipped in two formats: a Microsoft Access database (demo/access/MondrianFoodMart.mdb) and in a SQL script of insert statements (demo/FoodMartCreateData.sql).

Under Windows, using the Access database is easier to set up, because all you need to do is define an ODBC data source. If you want to test using a non Access database, or if you are not using Windows, you will need to create your own empty database and load it using the MondrianFoodMartLoader utility.

2.1. Microsoft Access 

If you are using Windows, Microsoft Access is the easiest database to use for the test dataset. You just need to set up an ODBC datasource for the test dataset.

The test dataset needs to be accessed by the unit tests if you are testing with just this database, or loading into another database.

Under the 'Administrative Tools' menu, click on the 'Data Sources (ODBC)' menu item to open the ODBC Data Source Administrator. Next, create a System DSN called MondrianFoodMart pointing to MONDRIAN_HOME/demo/access/MondrianFoodMart.mdb. (This allows Mondrian to access this data source using the JDBC connect string "jdbc:odbc:MondrianFoodMart".

2.2. Other database 

If you are not using Access, you can use the MondrianFoodMartLoader utility to create a database and load data from an SQL script ([-inputFile=<file name>]). But if you have already loaded the data into one JDBC data source (say Microsoft Access), use the -inputJdbcURL and related options to copy the data to another JDBC database.

The loader uses the standard J2SE JDBC, relevant JDBC driver JARs, and some classes out of Mondrian that do not rely on additional JARs. It has been tested against Oracle, PostgreSQL, MySQL, and Microsoft Access.

If the target is a JDBC database, you must create the necessary database or schema first. This is database-specific.

You can safely ignore any log4j warnings:

log4j:WARN No appenders could be found for logger (mondrian.rolap.RolapUtil). 
log4j:WARN Please initialize the log4j system properly.

Syntax

MondrianFoodMartLoader
    [-verbose] [-tables] [-data] [-indexes]
    -jdbcDrivers=<jdbcDrivers>
    -outputJdbcURL=<jdbcURL>
    [ [ [-outputJdbcUser=user] [-outputJdbcPassword=password] [-outputJdbcSchema=schema] [-outputJdbcBatchSize=<batch size>] ]
    | -outputDirectory=<directory name>
    ]
    [ [-inputJdbcURL=<jdbcURL> [-inputJdbcUser=user] [-inputJdbcPassword=password] [-inputJdbcSchema=schema] ]
    | [-inputFile=<file name>]
    ]

Options

Examples 

Here are some examples to give you a general idea how MondrianFoodMartLoader is invoked. The command line you use will vary depending upon your operating system, and intended source and target data sources.

Example 1. Loading MySQL from a file on Linux 

The following example is a Linux command line to create a MySQL schema called 'foodmart', and create FoodMart tables from the test dataset in the SQL scripts. First, create a foodmart database and a foodmart user:

$ mysqladmin create foodmart
$ mysql
mysql> grant all privileges on *.* to 'foodmart'@'localhost' identified by 'foodmart';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Now load the data:

$ java -cp "/mondrian/lib/mondrian.jar:/mondrian/lib/log4j.jar:/mondrian/lib/commons-logging.jar:/mondrian/lib/eigenbase-xom.jar:/mondrian/lib/eigenbase-resgen.jar:/mondrian/lib/eigenbase-properties.jar:/usr/local/mysql/mysql-connector-java-5.0.5-bin.jar"
     mondrian.test.loader.MondrianFoodMartLoader
     -verbose -tables -data -indexes
     -jdbcDrivers=com.mysql.jdbc.Driver
     -inputFile=/mondrian/demo/FoodMartCreateData.sql
     -outputJdbcURL="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart"

Example 2. Loading PostgreSQL from Access on Windows 

The following example is a Windows command line to create FoodMart tables for PostgreSQL from the test dataset in the Access database:

C:\mondrian> java -cp "C:\mondrian\lib\mondrian.jar; C:\mondrian\lib\log4j.jar; C:\mondrian\lib\commons-logging.jar; C:\mondrian\lib\eigenbase-xom.jar; C:\mondrian\lib\eigenbase-resgen.jar; C:\mondrian\lib\eigenbase-properties.jar; C:\mondrian\lib\postgres-jdbc.jar"
     mondrian.test.loader.MondrianFoodMartLoader
     -verbose -tables -data -indexes
     -jdbcDrivers="org.postgresql.Driver,sun.jdbc.odbc.JdbcOdbcDriver"
     -inputJdbcURL="jdbc:odbc:MondrianFoodMart"
     -outputJdbcURL="jdbc:postgresql://localhost/foodmart"
     -outputJdbcUser=postgres
     -outputJdbcPassword=password

2.3. Optional data sets 

The Steel Wheels data set is optional. To install the Steel Wheels data set on MySQL, run demo/mysql/SteelWheels.sql in MySQL:

$ mysql -ufoodmart -pfoodmart < demo/mysql/SteelWheels.sql

There is no supported process to load the data set on other databases. (Contributions welcome!)

Mondrian's test suite assumes that the data set is in a MySQL database called 'steelwheels', and uses the schema definition 'SteelWheels.mondrian.xml'. If Mondrian detects the schema, the test suite will run some additional tests; if not, those tests will trivially succeed.

3. Deploy and run the web application with a non-embedded database 

1. Install Tomcat (version 5.0.25 or later).
2. From the unzipped binary release, explode lib/mondrian.war to TOMCAT_HOME/webapps/mondrian
3. Create or open the mondrian.properties file in TOMCAT_HOME/webapps/mondrian and customize the  mondrian.jdbcDrivers properties for the database you set up from the instructions above.
4. Open the web.xml file in TOMCAT_HOME/webapps/mondrian/WEB-INF and customize the two connect strings there to the same database parameters for the FoodMart database you installed as per the above instructions. That is,

   Provider=mondrian;Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver;

   becomes

   Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;

5. Modify the fourhier.jsp, mondrian.jsp, colors.jsp and arrows.jsp files in the TOMCAT_HOME/webapps/mondrian/WEB-INF/queries folder. Modify the line

   <jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml">

   to the same database parameters for the FoodMart database you installed as per the above instructions. For MySQL, this would be something like the following:

   <jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml">

6. Copy the following files:
   • If needed, copy your JDBC driver JAR to TOMCAT_HOME/common/endorsed
   • Copy xalan.jar to TOMCAT_HOME/common/endorsed
7. Start your database, if needed.
8. Start Tomcat.
9. Hit http://localhost:8080/mondrian.

4. Deploy and run the web application with the embedded test database 

1. From the unzipped embedded database binary release, explode lib/mondrian-embedded.war to TOMCAT_HOME/webapps/mondrian-embedded
2. Start up Tomcat
3. Hit http://localhost:8080/mondrian-embedded

5. How to configure Mondrian as an XML/A provider 

To will setup XMLA service, follow these steps.

1. Describe the data sources in datasources.xml 

In WEB-INF directory of your webapp, create a file called datasources.xml, with content like this:

<?xml version="1.0"?>
<DataSources>
  <DataSource>
    <DataSourceName>MondrianFoodMart</DataSourceName>
    <DataSourceDescription>FoodMart 2000 Data Warehouse From MS Analysis Services</DataSourceDescription>
    <URL>http://localhost:8080/mondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:odbc:MondrianFoodMart; JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver</DataSourceInfo>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="FoodMart">
            <Definition>/WEB-INF/schema/FoodMart.xml</Definition>
        </Catalog>
        <Catalog name="Marketing">
            <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:odbc:MarketingDB; JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver</DataSourceInfo>
            <Definition>/WEB-INF/schema/Marketing.xml</Definition>
        </Catalog>
    </Catalogs>
  </DataSource>

  <DataSource>
    <DataSourceName>PostgreSQLTest</DataSourceName>
    <DataSourceDescription>Test Data Warehouse On PostgreSQL</DataSourceDescription>
    <URL>http://localhost:8080/mondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian; Jdbc=jdbc:postgresql://localhost/olap; JdbcDrivers=org.postgresql.Driver; JdbcUser=pgsql; JdbcPassword=pgsql</DataSourceInfo>
    <ProviderName>Mondrian</ProviderName>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="Test">
            <Definition>/WEB-INF/schema/TestPgsql.xml</Definition>
        <Catalog>
    </Catalogs>
  </DataSource>
</DataSources>

<ProviderType> and <AuthenticationMode> are ignored because the XML/A implementation currently only supports 'MDP' and 'Unauthenticated'.

Multiple data sources and multiple catalogs

If you intend to use Mondrian with Microsoft's ADOMD.NET client library or Simba's O2X bridge, the data sources file must have precisely one <DataSource> element. These clients will ignore all but one data source. Other XML/A clients, such as Rex, work fine with multiple data sources.

Note that each catalog has a name attribute, and the URI (path) of an XML schema file. The name of the catalog must match the name attribute in the schema file (for example <Schema name="FoodMart">).

Whether  you use one data source or several, the catalogs in the datasources.xml file must have unique names.

5.2. Configure XmlaServlet in your web.xml 

For example:

<servlet>
  <servlet-name>MondrianXmlaServlet</servlet-name>
  <servlet-class>mondrian.xmla.impl.DefaultXmlaServlet</servlet-class>
</servlet>

6. Miscellaneous 

6.1. Configuring mondrian.properties 

Properties are described in the Configuration Guide.

6.2. Cache setup 

You will need to specify the amount of memory available to the cache using the -Xms Java VM option, for example -Xms256m for 256 megs of ram for the Java VM.

7. Database compatibility 

Mondrian is known to run on the following databases.

1. Apache Derby (formerly known as Cloudscape)
2. Firebird
3. Greenplum
4. HP Neoview
5. Hypersonic (also known as hsqldb)
6. IBM DB2
7. Infobright
8. Informix
9. Ingres
10. Interbase
11. LucidDB
12. Microsoft Access
13. Microsoft SQL Server
14. MySQL
15. Netezza
16. Oracle
17. PostgreSQL (also known as Postgres)
18. Sybase
19. Teradata

Mondrian can work on almost any JDBC data source, so even if your database doesn't appear in the above list, give it a try. Mondrian can generally figure out the capabilities of the database from the JDBC driver, and generate SQL accordingly.

If you get Mondrian working on another database, drop us a line. Let us know which driver you used, the version of your database, property settings, and any other tweaks which were required. You can find examples of connect strings and driver paths for most database in mondrian.properties.

General database tips

For sample connect strings, look in mondrian.properties. There are are sample connect strings for most databases in there.

Infobright  

Infobright is a variant of the MySQL system. In fact, Infobright uses the regular MySQL driver, and even reports its product name as 'MySQL'! Mondrian detects Infobright by looking in the INFORMATION_SCHEMA.engines table; if there is an engine called "BRIGHTHOUSE", mondrian assumes the Infobright dialect.

First download and install Infobright Community Edition (ICE). To create and populate the FoodMart schema, most steps are the same as for MySQL. ICE does not support the INSERT command, so MondrianFoodMartLoader automatically uses the LOAD DATA INFILE command.

If the JDBC driver gives the error Unknown character set: 'usa7', try adding &characterEncoding=UTF-8 to the end of the JDBC connect string.

Ingres 

Andy Grimm writes:

In addition, I had to run a perl script against the demo sql file:

> perl -pi -e "s/:00\.0/:00/g" FoodMartCreateData.sql

That drops the ".0" off each timestamp to make it a valid DATE. Our next release will support timestamps, so this is a short-term fix that could go into Release Notes.

MySQL  

See the MySQL example.

PostgreSQL 

See PostgreSQL example.

To install PostgreSQL 8.2 and its JDBC driver on Ubuntu, I typed:

$ sudo apt-get install postgresql libpg-java

The JDBC driver can be found at /usr/share/java/postgresql.jar.

Change password:

$ sudo -u postgres psql postgres
# ALTER USER postgres WITH ENCRYPTED PASSWORD ' <***password***> ';
# \q

Create a user and a database:

$ sudo -u postgres createuser -D -A -P foodmart
$ sudo -u postgres createdb -O foodmart foodmart

Sybase 

mizar_sf writes:

I tried some solutions to get mondrian (I used 2.1RC) connected with a Sybase ASE [version 12.5] server. Finally I found this solutions:

• Downloaded a jdbc3 compliant driver like jtds-1.2.jar from sourceforge.
• Placed the driver in TOMCAT_HOME/common/endorsed
• In configuration files like
  • mondrian/WEB-INF/datasources.xml
  • mondrian/WEB-INF/web.xml
  I modified the parameters line in this way:

  Provider=mondrian; JdbcUser=userName; JdbcPassword=userPass; Jdbc=jdbc:jtds:sybase://xxx.xxx.xxx.xxx:port/dbName; JdbcDrivers=net.sourceforge.jtds.jdbc.Driver; Catalog=/WEB-INF/queries/myCatalog.xml

  (The datasources.xml doesn't need to be modified if you are only testing the db, but if you are using something like OPENI you have to.)

8. Compatibility issues 

8.1. Weblogic 6.1 and Xerces 

Weblogic 6.1 ships with an older, incompatible, version of Xerces. The symptom is the error

java.lang.VerifyError: (class: org/eigenbase/xom/wrappers/XercesDOMParser, method: parse signature: (Lorg/xml/sax/InputSource;)Lorg/w3c/dom/Document;) Incompatible object argument for function call
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:120)
at org.eigenbase.xom.XOMUtil.createDefaultParser(XOMUtil.java:165)
at org.eigenbase.resgen.Util.load(Util.java:49)
...

The solution is to place xml-apis.jar and xercesImpl.jar before weblogic.jar on your class-path.

Generally, Mondrian uses whichever JAXP-compliant XML parser is provided by the system. Unfortunately Weblogic's parser cannot be set to non-validating mode, and Mondrian needs this. Therefore, in a Weblogic environment, Mondrian explicitly uses Xerces.  Fyi, this note describes how to change Weblogic's default XML parser.

8.2. Log4j and Tomcat 

The Mondrian WARs come packaged with a version of log4j.jar. This may conflict with your app server, such as JBoss, and cause errors in the log about log4j appenders. The fix is to remove the log4j.jar from the Mondrian WAR.

Author: Julian Hyde and others; last updated March, 2009.
Version: $Id$ (log)
Copyright (C) 2001-2005 Julian Hyde
Copyright (C) 2005-2009 Pentaho and others