Mondrian with Oracle connection


Load the data into Oracle database


The easiest way to load the test data into an Oracle database is to use the MondrianFoodMartLoader application provided with Pentaho. MondrianFoodMartLoader will automatically create necessary database objects and will load the FoodMart data.
Below the script used to load the data to Oracle which was used during the creation of that tutorial: 

java -cp "D:\mondrian\lib\mondrian.jar;
	  D:\mondrian\lib\log4j-1.2.9.jar; 
	  D:\mondrian\lib\eigenbase-xom.jar; 
	  D:\mondrian\lib\eigenbase-resgen.jar; 
	  D:\mondrian\lib\eigenbase-properties.jar; 
	  D:\mondrian\lib\ojdbc14.jar" 
      mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes 
      -jdbcDrivers="oracle.jdbc.driver.OracleDriver,sun.jdbc.odbc.JdbcOdbcDriver" 
      -inputJdbcURL="jdbc:odbc:MondrianFoodMart" 
      -outputJdbcURL="jdbc:oracle:thin:foodmart/foodmart@127.0.0.1:1521:dwdev"
Script execution options which are worth looking at:
  • The java libraries required to run the application are in the D:\mondrian\lib\ folder
  • The -jdbcDrivers parameter indicates which java connection drivers should be used to connect to the database. oracle.jdbc.driver.OracleDriver is a JDBC connector for Oracle and jdbc.odbc.JdbcOdbcDriver is an ODBC connector - which can be used for example to access a MSAccess database
  • -inputJdbcURL="jdbc:odbc:MondrianFoodMart" indicates an OBDC datasource for the source data. ODBC connection nameis MondrianFoodMart in that case
  • -outputJdbcURL="jdbc:oracle:thin:foodmart/foodmart@127.0.0.1:1521:dwdev" points to the target Oracle database. In the example we use a foodmart user (password: foodmart), the database is installed locally (127.0.0.1), the listener is set up on port 1521 and the Oracle database SID is dwdev.

    Apache Tomcat configuration

  • mondrian.properties file - (it's in the TOMCAT/webapps/mondrian/WEB-INF folder). Oracle connection parameters need to be added: 
    #oracle
mondrian.test.connectString=Provider=mondrian;jdbc:oracle:thin:foodmart/foodmart@127.0.0.1:1521:dwdev;
JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver,oracle.jdbc.driver.OracleDriver;Catalog=/WEB-INF/queries/FoodMart.xml;
  • web.xml - modify the value of a param-value in the connectString section and a MDXQueryServlet in the same way as above: 
    <param-name>connectString</param-name>
<param-value>Provider=mondrian;Jdbc=jdbc:oracle:thin:foodmart/foodmart@127.0.0.1:1521:dwdev;
		Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=oracle.jdbc.driver.OracleDriver;
</param-value>
  • Edit the jsp files and change connection parameters accordingly (jdbcDriver and jdbcUrl). The files are in the TOMCAT/webapps/mondrian/WEB-INF/queries folder.

    The Oracle database is set up correctly now and Mondrian is ready to access the data from oracle.

    <-- 1. Mondrian installationPentaho Tutorial3. Define cube with Pentaho Cube Designer -->