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 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 Kettle 4.3.0 on Ubuntu 12.04 LTS

Introduction

Kettle is Pentaho’s ETL tool, which is also called Pentaho Data Integration (PDI).

Installing Kettle is extremely simple.

Install Java

Follow the JDK installation instructions that are listed in the following post: Install Java JDK 6.0 update 31 on Ubuntu 12.04 LTS

Download

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

wget http://downloads.sourceforge.net/project/pentaho/Data%20Integration/4.3.0-stable/pdi-ce-4.3.0-stable.tar.gz

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

Installation

Next, open a terminal and enter the following commands:

cd ~/Downloads
tar -xzf pdi-ce-4.3.0-stable.tar.gz
mv data-integration ~/bin/pdi-ce-4.3.0
cd ~/bin
ln -s pdi-ce-4.3.0 data-integration
cd ~/bin/data-integration

To run Spoon:

./spoon.sh

Additional Reading

There is a lot of good documentation installed with PDI.

cd ~/bin/data-integration/docs/English

Open your favorite PDF viewer, or type:

evince getting_started_with_pdi.pdf

Install Pentaho Metadata Editor (PME) 4.5.0 on Ubuntu 12.04 LTS

Introduction

The instructions below assume that you’ve followed my prior blog posts on how to install Pentaho on Ubuntu Linux 12.04 LTS x64.

Download

To download the Pentaho Metadata Editor (PME) either run the following command, or follow the 3 bulleted steps below.

wget http://downloads.sourceforge.net/project/pentaho/Pentaho%20Metadata/4.5.0-stable/pme-ce-4.5.0-stable.tar.gz

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

Installation

Enter the following commands:

cd ~/Downloads
tar -xzf pme-ce-4.5.0-stable.tar.gz
mv metadata-editor ~/bin/pme-ce-4.5.0
cd ~/bin
ln -s pme-ce-4.5.0 metadata-editor
vi ~/.profile

Append the following to the end of your PATH:

:$HOME/bin/metadata-editor

For example, my PATH now looks like:
PATH="$HOME/bin:$PATH:$HOME/bin/design-studio:$HOME/bin/metadata-editor"

Save the file, then enter the following command:

source ~/.profile
cd ~/bin/metadata-editor
ln -s metadata-editor.sh pme

Start the Pentaho Metadata Editor with the following command:

pme

Note:
You may get an error message such as:

ERROR 24-05 21:45:04,618 – Pentaho Metadata Editor – Unable to load query : java.io.FileNotFoundException: /home/akbar/.pentaho-meta/.query (No such file or directory)

You can safely ignore this error message for now per wgorman in this post:
http://forums.pentaho.com/showthread.php?72313-Error-Messgae-Pentaho-Metadata-Editor-Unable-to-load-query

“Regarding the .query file not being found, you can safely ignore that message. The MQL Query Editor looks for that file for a saved state.” Will

Install Pentaho Design Studio 4.0 on Ubuntu 12.04 LTS Desktop

Introduction

Pentaho Design Studio (PDS) is a BI plugin for Eclipse. I’m going to download the complete package as Pentaho was nice enough to integrate the plugin with Eclipse for us.

Download

To download the Pentaho Design Studio (PDS) either run the following command, or follow the bulleted steps below.

wget http://downloads.sourceforge.net/project/pentaho/Design%20Studio/4.0.0-stable/pds-ce-linux-64-4.0.0-stable.tar.gz

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

Installation

Note:
I am going to assume that you have downloaded the file listed above into the Downloads directory in your Home directory.

Open a terminal and enter the following commands:

cd
mkdir bin
cd ~/Downloads
tar -xzf pds-ce-linux-64-4.0.0-stable.tar.gz
mv design-studio/ ~/bin/pds-ce-linux-64-4.0.0
cd ~/bin
ln -s pds-ce-linux-64-4.0.0 design-studio
vi ~/.profile

Near the bottom of the file you should see the PATH variable. Append :$HOME/bin/design-studio to end of the PATH.

For example, my PATH was:
PATH="$HOME/bin:$PATH"

…which I updated to:
PATH="$HOME/bin:$PATH:$HOME/bin/design-studio"

It’s better to append :$HOME/bin/design-studio to end of the PATH than the beginning so that we don’t accidentally step on another installation of Eclipse. Also, as we created a symlink named pds we are less likely to have PDS inaccessible due to another Eclipse installation that is earlier in the PATH.

Next, we’ll create a symlink named pds so that we can type a shorter command to open Pentaho Design Studio.

cd ~/bin/design-studio
ln -s eclipse pds

Finally, source your profile to update your environment.

source ~/.profile

Now just type pds and press the Enter key:

pds

Install Pentaho BI Server 4.5 on Ubuntu 12.04 LTS Desktop

Overview: What is Pentaho?

Pentaho is an open source Business Intelligence (BI) Suite that comes in with either commercial support (http://www.pentaho.com/) and or community support (http://community.pentaho.com/).

This post provides instructions for the Pentaho community edition suite.

Create a pentaho user and group

Open a terminal and run the following commands:

sudo addgroup pentaho
sudo adduser --system --ingroup pentaho --disabled-login pentaho

Install Java

Follow the JDK installation instructions that are listed in the following post: Install Java JDK 6.0 update 31 on Ubuntu 12.04 LTS

Install the Pentaho BI Server

Now that we have Java installed we can get on with our main task of installing the Pentaho BI Server.

  1. Download the Pentaho BI Server from http://wiki.pentaho.com/display/COM/Latest+Stable+Builds. I’m using the current stable build for x64 Linux which is biserver-ce-4.5.0-stable.tar.gz.
  2. Open a terminal and enter the following commands:
sudo mkdir /opt/pentaho
cd ~/Downloads
gunzip biserver-ce-4.5.0-stable.tar.gz
tar xf biserver-ce-4.5.0-stable.tar
sudo mv biserver-ce /opt/pentaho/biserver-ce-4.5.0
sudo mv administration-console /opt/pentaho/administration-console-ce-4.5.0
cd /opt/pentaho
sudo ln -s biserver-ce-4.5.0 biserver-ce
sudo ln -s administration-console-ce-4.5.0 administration-console
sudo chown -R pentaho:pentaho /opt/pentaho

Start the Pentaho Server

Open a terminal, then enter the following commands:

Note:
The following command is only required if you downloaded a Windows .zip file by accident. If this is the case, then none of the .sh files will be executable.
sudo find /opt/pentaho/ -type f -name '*.sh' -exec chmod 744 '{}' \+

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

Login to the Pentaho User Console

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

Login to the Pentaho Administration Console

Open a terminal, then enter the following commands:

cd /opt/pentaho/administration-console
sudo -u pentaho ./start-pac.sh
  1. Open a web browser to http://localhost:8099.
  2. Enter a User Name of admin.
  3. Enter a Password of password.
  4. Click Log In.

That’s it. The core Pentaho BI server is installed and ready for development. However, a good next step is to change the database that Pentaho uses and install the Pentaho Design Studio (PDS), but we’ll leave that for future posts.

Hadoop also port 8080, so you will either need to use a different port for the Pentaho User Console or change the Hadoop MapReduce ShuffleHandler port.

Install Pentaho BI Server 4.5 on Windows 7 x64

What is Pentaho

Pentaho is an open source Business Intelligence (BI) Suite that comes in with either commercial support (http://www.pentaho.com/) and or community support (http://community.pentaho.com/).

This post provides instructions for the Pentaho community edition suite.

Install Pentaho BI Server 4.5 on Windows 7 x64

Install Java

Your steps may vary slightly as I had an older version of the JDK installed. However, the gist is “just click Next till your done”.

  1. Download the Java JDK from http://www.oracle.com/technetwork/java/javase/downloads/index.html.
  2. After downloading, double-click the installer, which will be named something like: (the name of your file may vary depending on which version you have downloaded)
  3. Click Next.
  4. Click Next. Wait for the installation to finish.
  5. Click Next. Wait for the JRE installation to finish.
  6. Click Continue.
  7. Click Next to start the JavaFX SDK Setup.
  8. Click Next.
  9. Click Close.

Add Java to your PATH

You should now have the Java SDK installed into something like (your exact path may vary based on the version):

C:\Program Files\Java\jdk1.7.0_04\bin

  1. Click Start.
  2. Right-click on Computer, click Properties.
  3. In the left pane, click Advanced system settings.
  4. Select the Advanced tab, then click Environment Variables…
  5. In System Variables, scroll till you find Path.
  6. Select Path, click Edit…
  7. Add the Java bin folder to  your path by appending ;C:\Program Files\Java\jdk1.7.0_04\bin to the end of the Path Variable Value.
  8. Click OK.
  9. Click New…
  10. In Variable name, enter: PENTAHO_JAVA_HOME
  11. In Variable value, enter: C:\Program Files\Java\jdk1.7.0_04
  12. Click OK.
  13. Click OK.
  14. Click OK.

As last step check that your path is set correctly.

  1. Open Powershell.
  2. Type java -version.
  3. Then type javac -version.
  4. For each command above, you should see the correct Java version  (for me its 1.7.0_04)
  5. Open a command prompt.
  6. Type echo %PENTAHO_JAVA_HOME%
  7. You should see the following as output: C:\Program Files\Java\jdk1.7.0_04

Note:

If you want to show the value of PENTAHO_JAVA_HOME in PowerShell, then enter:

Get-Childitem env:PENTAHO_JAVA_HOME

Note:

I had to reboot to get this to work.

Install Pentaho BI Server

Now that we have Java installed we can get on with our main task of installing the Pentaho BI Server.

  1. Download the Pentaho BI Server from http://wiki.pentaho.com/display/COM/Latest+Stable+Builds. I’m using the current stable build which is biserver-ce-4.5.0-stable.zip.
  2. Open Windows Explorer and create new folder: C:\Program Files\Pentaho.
  3. Unzip biserver-ce-4.5.0-stable.zip.
  4. Next, copy the biserver-ce and administration-console folder into C:\Program Files\Pentaho.

Update the Permissions to allow the server to be run as a developer’s regular user (i.e. not Administrator)

  1. In Windows Explorer, navigate to C:\Program Files.
  2. Right-click the Pentaho folder, click Properties.
  3. Select the Security tab, click Edit.
  4. Click Add.
  5. Enter the developer’s username, then click OK.
  6. In the Allow column, check Full control.
  7. Click OK.
  8. Click OK.

Start the Pentaho Server

I am not going to cover automatic startup of the Pentaho Server on Windows because my assumption is that some developers choose to run Pentaho on Windows, but that product deployments

  1. Open Windows Explorer to C:\Program Files\Pentaho\biserver-ce.
  2. Double-click start-pentaho.bat.
  3. If the Windows Security Alert dialog box pops up, then click Allow access.

Hint:

I recommend that you resize the command window that displays the Tomcat messages. This will make it easier to read messages that are output by Tomcat.

  • In the top left corner of the Window, click the Java icon.
  • Click Properties.
  • Select the Layout tab.
  • Set the Width = 120
  • Set the Height = 45
  • Click OK.

Login to the Pentaho User Console

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

Login to the Pentaho Administration Console

  1. Open Windows Explorer to C:\Program Files\Pentaho\administration-console.
  2. Double-click start-pac.bat.
  3. Open a web browser to http://localhost:8099.
  4. Enter a User Name of admin.
  5. Enter a Password of password.
  6. Click Log In.

That’s it. The core Pentaho BI server is installed and ready for development. However, a good next step is to change the database that Pentaho uses, but we’ll leave that for another post.