Install Pentaho Analysis Services (Mondrian) 3.4.1 with MySQL on Ubuntu 12.04 LTS

Introduction

Pentaho Analysis Services is called Mondrian, which is the name I’ll use for the remainder of this post. Mondrian is Pentaho’s OLAP server.

In this post I’ll provide step-by-step instructions on how to install Mondrian 3.4.1 Ubuntu Linux 12.04 LTS x64. We’ll use MySQL as the database.

Download

Let’s first create a directory to download Mondrian into.

mkdir -p ~/Downloads/pentaho/analysis
cd ~/Downloads/pentaho/analysis

To download the Mondrian either run the following command, or follow the 3 bulleted steps below.

wget http://hivelocity.dl.sourceforge.net/project/mondrian/mondrian/mondrian-3.4.1/mondrian-3.4.1.zip

Or follow the steps below if you don’t want to use the wget command shown above.

Unzip

cd ~/Downloads/pentaho/analysis
unzip mondrian-3.4.1.zip

Install the Mondrian Server

cd ~/Downloads/pentaho/analysis/mondrian-3.4.1/lib
sudo unzip mondrian.war -d /opt/pentaho/biserver-ce/tomcat/webapps/mondrian
sudo chown -R pentaho:pentaho /opt/pentaho/biserver-ce/tomcat/webapps/mondrian

Create the FoodMart Database

FoodMart is a sample database that can be used to learn Mondrian.

mysql -u root -p

Enter the following commands at the MySQL command prompt.

mysql> CREATE DATABASE foodmart;

Please use a real password by changing pass.word below.

mysql> GRANT ALL ON foodmart.* TO foodmart@localhost IDENTIFIED BY 'pass.word';
mysql> quit;

Import the Foodmart Schema and Data

cd /opt/pentaho/biserver-ce/tomcat/webapps/mondrian

Important
Change akbar to your user name in the inputFile option in the command below.

java -cp "\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/mondrian.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/log4j-1.2.8.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/commons-collections-3.1.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/olap4j.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/commons-logging-1.0.4.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/eigenbase-xom.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/eigenbase-resgen.jar:\
/opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/lib/eigenbase-properties.jar:\
/opt/pentaho/biserver-ce/tomcat/lib/mysql-connector-java-5.1.17.jar" \
mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes \
-jdbcDrivers=com.mysql.jdbc.Driver \
-inputFile=/home/akbar/Downloads/pentaho/analysis/mondrian-3.4.1/demo/FoodMartCreateData.sql \
-outputJdbcURL="jdbc:mysql://localhost/foodmart?user=foodmart&password=pass.word"

If you get an error, there is a 99% probability that the jar files cannot be found on the paths specified in the command above. So, the first thing you should do is check if the jar files can be found in the class path paths that are listed above, then update the paths as necessary.

Update the Sample MDX Query Files

cd /opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF/queries

Edit each of the following files and make the change shown in the From / To instructions below.

Change each of the JSP files from:


<jp:mondrianQuery id="query01" jdbcDriver="org.apache.derby.jdbc.EmbeddedDriver" jdbcUrl="jdbc:derby:classpath:/foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml"
   jdbcUser="sa" jdbcPassword="sa" connectionPooling="false">

To…


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

sudo vi fourhier.jsp
sudo vi mondrian.jsp
sudo vi arrows.jsp
sudo vi colors.jsp
cd /opt/pentaho/biserver-ce/tomcat/bin
sudo ./shutdown.sh
sudo ./startup.sh

I have changed the default Pentaho BI Server port from 8080 to 8585.

Open a web browser to http://localhost:8585/mondrian.

Click each of the links below to view the same cube.

The cube will be populated for the first time when you click the first link below. In other words, the first page will take a long time to load while the cube is populating. On my system, I actually had to reboot and restart Tomcat before the pages displayed.

Mondrian Configuration Files

Nothing needs to be done to setup the FoodMart database, however you will need to edit the following configuration files when you create your own database.

mondrian.properties

cd /opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF
sudo cp mondrian.properties mondrian.properties.org
sudo vi mondrian.properties

datasources.xml

cd /opt/pentaho/biserver-ce/tomcat/webapps/mondrian/WEB-INF
sudo cp datasources.xml datasources.xml.org
sudo vi datasources.xml

Debugging

I was initially using the instructions at http://mondrian.pentaho.com/documentation/installation.php, however, I got the following error when I ran the jar command.

Exception in thread "main" java.lang.NoClassDefFoundError: org/olap4j/mdx/IdentifierSegment
	at mondrian.test.loader.MondrianFoodMartLoader.(MondrianFoodMartLoader.java:98)
Caused by: java.lang.ClassNotFoundException: org.olap4j.mdx.IdentifierSegment
	at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
	... 1 more
Could not find the main class: mondrian.test.loader.MondrianFoodMartLoader.  Program will exit.

The solution was to add the following missing .jar files to the class path:

  • commons-collections-3.1.jar
  • olap4j.jar
Advertisements

Change the default port on Pentaho BI Server

Introduction

There are many reasons for changing the Pentaho BI Server’s default port, but one of the most common is a conflict with another server. I ran into this issue where both the Pentaho User Console and the Hadoop MapReduce ShuffleHandler were trying to use port 8080. I wrote an earlier article on how to change the default port for Hadoop MapReduce ShuffleHandler.

So this got me thinking about how to change the Pentaho BI Server port, which is the focus of this article. Plus, it’s always good to know which ports each server requires so that you can manage your firewall, security, and so on.

Step-by-Step

Open a terminal and enter the commands below.

Update server.xml

cd /opt/pentaho/biserver-ce/tomcat/conf

We’ll make a copy of server.xml before we edit it.

I like to keep an original copy of each configuration file that I edit. My standard is to append ‘.org’ to the end of the file.

sudo cp server.xml server.xml.org

I’m going to change from port 8080 to port 8585. Also, I’m going to use sed so that I can change multiple settings with a single command.

sudo sed -i 's/port="8080/port="8585/g' server.xml

Next, we’ll have to update one of the comments that references port 8080. I like to keep my comments in sync with the configuration.

sudo sed -i 's/port 8080/port 8585/g' server.xml

Let’s check what we changed via sed. This command is a good use of the server.xml.org file that we created above.

diff server.xml server.xml.org

You should see output similar to:

< Define a non-SSL HTTP/1.1 Connector on port 8585
---
> Define a non-SSL HTTP/1.1 Connector on port 8080
69c69
< <Connector URIEncoding="UTF-8" port="8585" protocol="HTTP/1.1"
---
> <Connector URIEncoding="UTF-8" port="8080" protocol="HTTP/1.1"
75c75
< port="8585" protocol="HTTP/1.1"
---
> port="8080" protocol="HTTP/1.1"

Update web.xml

cd /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF
sudo cp web.xml web.xml.org
sudo vi web.xml

Update the fully-qualified-server-url‘s param-value to:

http://localhost:8585/pentaho/

The complete node should look like:


  fully-qualified-server-url
  http://localhost:8585/pentaho/

Restart the Pentaho BI Server

cd /opt/pentaho/biserver-ce
sudo -u pentaho ./stop-pentaho.sh
sudo -u pentaho ./start-pentaho.sh

Login to the Pentaho User Console

  1. Open a web browser to http://localhost:8585.
  2. Click Evaluation Login and select a user type to login as.

Create a DB connection to MySQL in Kettle

Introduction

Creating a DB connection from Kettle to MySQL involves creating a MySQL use who can access the DB in question, installing the JDBC driver, and creating a connection.

Install the MySQL JDBC driver

Download the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/.

Login to mysql.com, then click Download.

cd ~/Downloads
tar -xzf mysql-connector-java-5.1.20.tar.gz
cd mysql-connector-java-5.1.20
cp mysql-connector-java-5.1.20-bin.jar ~/bin/data-integration/libext/

Create a MySQL user

In this post, I am going to create a connection to the Sakila DB.

mysql -u root -p

At the MySQL command prompt, enter the following (replace ‘password’ with your password):

mysql> GRANT ALL ON sakila.* TO akbar@localhost IDENTIFIED BY 'password';

Create the DB connection in Kettle

cd ~/bin/data-integration
./spoon.sh
  1. Click the New in the PDI toolbar.
  2. Click Database connection.
  3. Enter information similar to what’s shown below:
  4. PDI Database Connection

  5. Click Test.
  6. Click OK.

In Explorer in the left pane of PDI, right-click on the Sakila database connection, click Explore.

You should now be able to view the tables in the Sakila database.

Create a Kettle repository

Introduction

Open Kettle

cd ~/bin/data-integration

To run Spoon:

./spoon.sh

Create a new repository

Run the following in a terminal.

mkdir ~/kettle

The steps below are performed within the PDI UI.

    Repository Connection

    Repository Connection

  1. In the Repository Connection dialog box, click the small green plus symbol.
  2. Repository Type

    Repository Type

  3. In the Select the repository type dialog box, select Kettle file repository.
  4. Click OK
  5. In the File repository settings dialog box, enter the following information:
    • Base directory: /home/akbar/kettle
    • Read-only repository?: Leave unchecked
    • Hide hidden folders and files: Leave unchecked
    • ID: kettle-repo
    • Name: Kettle Repository
  6. Click OK.
  7. Click OK.

Create a .bash_aliases file

Introduction

This is my personal .bash_aliases file that is mainly used for Cloudera CDH4 (Hadoop) and Pentaho. As a result, many of my aliases are specific to these software packages.

I plan to update this post as my .bash_aliases file expands. I will also push my .bash_aliases file into Git to make it easier to keep up with changes to the file.

How to create a .bash_aliases file

vi ~/.bash_aliases

Paste the following into the file.


# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Personal: ~/.bash_aliases
# Akbar S. Ahmed
#
# Last modified: 2012.06.25
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# ———————————————–
# General
# ———————————————–

alias c=’clear’ # Clear the screen
alias df=’df -Th’ # Disk free space
alias du=’du -h’ # Disk usage
alias h=’history’ # Bash history
alias j=’jobs -l’ # Current running jobs

# ———————————————–
# ls
# ———————————————–

alias lx=’ls -lXB’ # Sort by extension
alias lk=’ls -lSr’ # Sort by size (small to big)
alias lc=’ls -ltcr’ # Sort by change time (old to new)
alias lu=’ls -ltur’ # Sort by change time (new to old)
alias lt=’ls -ltr’ # Sort by date (old to new)

# ———————————————–
# Hadoop Admin (sudo)
# ———————————————–

alias shcat=’sudo -u hdfs hadoop fs -cat’ # Output a file to standard out
alias shchown=’sudo -u hdfs hadoop fs -chown’ # Change ownership
alias shchmod=’sudo -u hdfs hadoop fs -chmod’ # Change permissions
alias shls=’sudo -u hdfs hadoop fs -ls’ # List files
alias shmkdir=’sudo -u hdfs hadoop fs -mkdir’ # Make a directory

# ———————————————–
# Hadoop (regular user)
# ———————————————–

alias hcat=’hadoop fs -cat’ # Output a file to standard out
alias hchown=’hadoop fs -chown’ # Change ownership
alias hchmod=’hadoop fs -chmod’ # Change permissions
alias hls=’hadoop fs -ls’ # List files
alias hmkdir=’hadoop fs -mkdir’ # Make a directory

source ~/.bash_aliases

Install Java JDK 6.0 update 31 on Ubuntu 12.04 LTS

Introduction

The first question is why are we installing an old JDK. The answer is that Oracle JDK 6.0 update 31 is the JDK recommended by Cloudera when installing CDH4 (Cloudera Distribution Hadoop v4).

This is an update to an older version of this post. Mainly I have changed the JDK from 1.6.0_26 to 1.6.0_31 as this is the recommended JDK for CDH4.

Install Java

I have a 64 bit version of Ubuntu 12.04 LTS installed, so the instructions below only apply to this OS.

  1. Download the Java JDK from http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u31-oth-JPR.
  2. Click Accept License Agreement
  3. Click jdk-6u31-linux-x64.bin
  4. Login to Oracle.com with your Oracle account
  5. Download the JDK to your ~/Downloads directory
  6. After downloading, open a terminal, then enter the following commands.
cd ~/Downloads
chmod +x jdk-6u31-linux-x64.bin
./jdk-6u31-linux-x64.bin

Note:
The jvm directory is used to organize all JDK/JVM versions in a single parent directory.

sudo mkdir /usr/lib/jvm
sudo mv jdk1.6.0_31 /usr/lib/jvm

The next 3 commands are split across 2 lines per command due to width limits in the blog’s theme.

sudo update-alternatives --install "/usr/bin/java" "java" \
"/usr/lib/jvm/jdk1.6.0_31/bin/java" 1
sudo update-alternatives --install "/usr/bin/javac" "javac" \
"/usr/lib/jvm/jdk1.6.0_31/bin/javac" 1
sudo update-alternatives --install "/usr/bin/javaws" "javaws" \
"/usr/lib/jvm/jdk1.6.0_31/bin/javaws" 1
sudo update-alternatives --config java

You will see output similar to the following (although it’ll differ on your system). Read through the list and find the number for the Oracle JDK installation (/usr/lib/jvm/jdk1.6.0_26/bin/java)

There are 2 choices for the alternative java (providing /usr/bin/java).

  Selection    Path                                            Priority   Status
------------------------------------------------------------
* 0            /usr/lib/jvm/java-7-openjdk-amd64/jre/bin/java   1051      auto mode
  1            /usr/lib/jvm/jdk1.6.0_31/bin/java                1         manual mode
  2            /usr/lib/jvm/java-7-openjdk-amd64/jre/bin/java   1051      manual mode

On my system I did the following (change the number that is appropriate for your system):
Press 1 on your keyboard, then press Enter.

sudo update-alternatives --config javac

Follow steps similar to those listed above if you are presented with a list of options. In my case, I had not previously installed the OpenJDK javac binary, so my output looked like the following:

There is only one alternative in link group javac: /usr/lib/jvm/jdk1.6.0_31/bin/javac
Nothing to configure.
sudo update-alternatives --config javaws

As with javac, I did not have the OpenJDK version of javaws installed, so my output was simple. However, if you get a list of options, just type in the number of the path to the Oracle javaws command, and press Enter.

There is only one alternative in link group javaws: /usr/lib/jvm/jdk1.6.0_31/bin/javaws
Nothing to configure.

As a final step, let’s test each of the commands to ensure everything is setup correctly.

java -version

The output should be:
java version "1.6.0_31"
Java(TM) SE Runtime Environment (build 1.6.0_31-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)

javac -version

The output should be:
javac 1.6.0_31

javaws -version

The output should be:
Java(TM) Web Start 1.6.0_31
which is followed by a long usage message.

Create the JAVA_HOME environment variable

Open a terminal, then enter the following commands:

sudo vi /etc/environment

WARNING
WordPress displays the quotes around the JAVA_HOME value below as magic quotes. This will cause problems when you try to use your JVM in certain applications.

Do not copy/paste the JAVA_HOME value below. Or if you do, ensure that you change magic quotes to straight quotes in your editor.

Enter the following at the bottom of the file:
JAVA_HOME="/usr/lib/jvm/jdk1.6.0_31"

Type the following commands to finish the setup and verify that everything is setup correctly.

source /etc/environment
echo $JAVA_HOME

You should see the following output:

/usr/lib/jvm/jdk1.6.0_31

Lastly, verify that JAVA_HOME is set correctly for the sudo user:

sudo env | grep JAVA_HOME

That’s it, the JDK 6.0 update 31 is installed.